How Recover an Oracle Database Backup in Windows When Everything is Lost

GOAL

Which steps have to follow in order to recover a backup of a database in a windows platform when everything is lost?

SOLUTION

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the

Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual

Environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Database and SID Name: YOURDB

************

First of all we need to install the Oracle Database Software, we need to install the same database release and the same patch set level. But we must not create any database, in the installer window you

Must select the option of installing software only.

Once the Oracle Database software is installed, to recover from an OS backup of your database in a windows platform, it's necessary:

1: Create an Oracle Password File

-------------------------------------------------------------

For full details on how to create a password file please refer to Oracle9i Database

Administrator's Guide.

For example: orapwd file=oraYOURDB.pwd password=<password> entries=10

2: Create an Initialization Parameter File

----------------------------------------------------------------------

Restore the init.ora file from the backup, and if you don't have the init.ora

You can use an init.ora from another database and make the necessary changes

You need setup the required parameters e.g DB_NAME, CONTROL_FILES and

Directories for bdump, udump, cdump etc...

Parameter file '<ORACLE_HOME>\DATABASE\initYOURDB.ORA'

3: Restore all the database files

----------------------------------------------------------------------

Restore all the database files to the same location that they were in production database

You must restore

-> control files <to the location indicated in control_files parameter in the init...ora>

-> database files

-> Archivelog files <to the log_archive_dest directory in the init...ora>

Make sure that you have the necessary backups of database and archived redo logs

4: Create the Oracle services

--------------------------------

Create a new NT service for the duplicate database YOURDB using oradim.

C:\>oradim -new -sid YOURDB -intpwd <password> -maxusers 10 -startmode auto -pfile '<your pfile location>'

If you don't have at least one control file, you will need to recreate the control file.

But must be careful, you need to be sure that all the datafiles are included in

The CREATE CONTROLFILE command and that are all in the right location. Also must

Assure that the redolog files can be created in the indicated location.

To recreate the control file

C:\> set ORACLE_SID=YOURDB

C:\> sqlplus "sys/<password> as sysdba"

SQL> startup nomount
SQL> CREATE CONTROLFILE REUSE DATABASE YOURDB RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 454

LOGFILE

GROUP 1 '<log_file_name_and_location>' SIZE <size>M,

GROUP 2 '<log_file_name_and_location>' SIZE <size>M,

GROUP 3 '<log_file_name_and_location>' SIZE <size>M

DATAFILE

'<datafile_1_name_and_location>',

.....

'<datafile_1_name_and_location>'

CHARACTER SET <your_db_charset>;

You can change the CREATE control file options if you want

* CREATE CONTROLFILE SYNTAX

This information is fully documented in the Oracle SQL Reference Manual.

CREATE CONTROLFILE [REUSE]

DATABASE name

[LOGFILE filespec [, filespec] ...]

RESETLOGS | NORESETLOGS

[MAXLOGFILES integer]

[DATAFILE filespec [, filespec] ...]

[MAXDATAFILES integer]

[MAXINSTANCES integer]

[ARCHIVELOG | NOARCHIVELOG]

[SHARED | EXCLUSIVE]

5: Recover and Open database

-------------------------------------

C:\> set ORACLE_SID=YOURDB

C:\> sqlplus "/ as sysdba"

SQL> startup mount
SQL> recover database until cancel using backup control file;

===> apply all the archivelogs available and when you won't have

More available then type CANCEL

SQL> alter database open resetlogs;

DBRECOVER Recovery Options

For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.

When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.