Disaster recovery

How to perform a disaster recovery of an Oracle server using Manual Backup Method

1.Pre-requisites:
The following are the pre-requisites to fully recover an Oracle database server in the event a disaster occurs:

A FULL Oracle database backup (all data file, control file and redo log file) using copy command. When making this backup, make sure the Oracle database is shut down. This backup set will contain a FULL CLOSED Oracle Database backup. A FULL Oracle database backup should be performed every time any changes are made to the physical and/or logical structure of the Oracle database and forms the base for recovering the database server to a fully working order.

Archive log file backup up to the time of the server failure.

Control file up to the time of the server failures.

Scenario

if your database is running in archive log mode and every Sunday you take full/cold backup of database (all data file , control file and redolog file ) and every day Monday to Saturday you take only archive log file backup. If a situation in which your database server has been destroyed at Saturday , how will u recover data up to Saturday

Steps

1. Build the server

You need a server to host the database, so the first step is to acquire or build the new machine. This is not strictly a DBA task, so we won't delve into details here. The main point to keep in mind is that the replacement server should, as far as possible, be identical to the old one. In particular, pay attention to the following areas:
Disk layout and capacity: Ideally the server should have the same number of disks as the original. This avoids messy renaming of files during recovery. Obviously, the new disks should also have enough space to hold all software and data that was on the original server.

Operating system, service pack and patches: The operating system environment should be the same as the original, right up to service pack and patch level.

Memory: The new server must have enough memory to cater to Oracle and operating system / other software requirements. Oracle memory structures (Shared pool, db buffer caches etc) will be sized identically to the original database instance. Use of the backup server parameter file will ensure this.

4. Install Oracle Software

Now we get to the meat of the database recovery process. The next step is to install Oracle software on the machine. The following points should be kept in mind when installing the software:
Install the same version of Oracle as was on the destroyed server. The version number should match right down to the patch level, so this may be a multi-step process involving installation followed by the application of one or more patchsets and patches.

Do not create a new database at this stage.

Create a listener using the Network Configuration Assistant. Ensure that it has the same name and listening ports as the original listener. Relevant listener configuration information can be found in the backed up listener.ora file.

4. Create directory structure for database files

After software installation is completed, create all directories required for datafiles, (online and archived) logs, control files and backups. All directory paths should match those on the original server. This, though not mandatory, saves additional steps associated with renaming files during recovery.

Don't worry if you do not know where the database files should be located. You can obtain the required information from the backup spfile and control file at a later stage. Continue reading - we'll come back to this later.

5. Create Oracle service

An Oracle service must be exist before a database is created. The service is created using the oradim utility, which must be run from the command line. The following commands show how to create and modify a service (comments in italics, typed commands in bold):

--create a new service with auto startup

C:\>oradim -new -sid ORCL -intpwd ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the service exists via the Services administrative panel. The service has been configured to start automatically when the computer is powered up. Note that oradim offers options to delete, startup and shutdown a service. See the documentation for details.

3. Restore backup from tape

The next step is to get your backup from tape on to disk.

6. Restore and recover database

If an Oracle database server experienced a disaster such as a hard disk failure, use this procedure to recover the server and the Oracle databases:
Shutdown database

SQL> SHUTDOWN IMMEDIATE
Restore all data file, log file from cold backup and restore all archive log file from Cold backup to disaster.

Restore current control file at the time of Disaster recovery.

When the restore operation completes, move to the Oracle database server

Start server manager, connect as Internal and start the database but only mount it by typing:

SQL> STARTUP MOUNT
When the database is mounted, type:

RECOVER DATABASE USING BACKUP CONTROLFILE

Note :Oracle will respond to this command by returning the following message, suggesting a log sequence to apply.

ORA-00279: Change 36579 generated at needed for thread 1

ORA-00289: Suggestion : \Oracle_Home\Oradata\\%SID%T00036579.ARC

ORA-00280: {=Suggested | filename | AUTO | FROM logsource | CANCEL}
At the prompt, type:

AUTO

Then press

This will automatically apply all archived log sequences required to recover the database (assuming all archived redo logs are available in the location specified in the init.ora parameter and that the format corresponds to the format specified).

It is possible that a final non-archived log sequence is requested to complete the recovery. This will only hold one System Change Number (SCN) and no transactions relating to the database, up to, and including the time of the FULL ONLINE Oracle backup. If this is the case, the following message will be returned by Oracle:

ORA-00308: cannot open archived log

'E:\ORACLE\ORADATA\KIMSTAD\ARCHIVE\KIMSTADT00036949.ARC'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.
To finish the recovery, stay in server manager with the database mounted, and type:

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

Then press

9. When Oracle requests this final sequence again, type:

CANCEL

Then press

10. Oracle will return the following message:

Media recovery canceled

The media recovery of the database is complete.

11. To open the database and to synchronize the log sequence, type:

ALTER DATABASE OPEN RESETLOGS

Then press

The Oracle database server is now restored to full working order up to the time of the latest full online Oracle backup.