Friday, July 30, 2010

Recovering the database after a disaster

Using RMAN and RMAN recovery catalog.

These steps will restore and recovery the database after losing some or all of the critical database components. i.e. controlfiles, spfile, system.dbf, etc…

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

Once you have determined you need to restore the database login to sqlplus and issue the shutdown abort command.
SQL> shutdown abort

Restore the server files (Oracle Base and Home directories) from a backup if necessary.
Restore the Oracle directories.

Connect to rman and rman catalog.

# rman target / catalog rcvcat_login/rcvcat_passwd@rcvcat;
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 27 15:46:20 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: HRPROD (not mounted)
connected to recovery catalog database

set the DBID
see setting the DBID thread

RMAN> SET DBID 3800958841
executing command: SET DBID
database name is "HRPROD" and DBID is 3800958841

Startup the database in nomount
RMAN> startup nomount;
Oracle instance started

In order to restore the spfile the current spfile cannot be intact. You cannot restore over a current spfile. Either rename it or delete it if it currently exists before executing the 'restore spfile from autobackup' command.

Restore the SPFILE



RMAN> restore spfile from autobackup;

Starting restore at ...
.
.
.


Shutdown the database.
RMAN> shutdown immediate;
Oracle instance shut down

Restart the instance with the restored server parameter file.
RMAN> startup force nomount;

Determine the last archive log sequence backed up or available on disk.
Since I am using a RAID 5 configuration, if I lose more than one disk at a time I will lose everything, including all archive logs files on disk. I have an output from the last backup in my email that I can reference, but this information can also be obtained from your RMAN catalog.

RMAN> list backup of archivelog from scn=1;
List of Archived Logs in backup set 205213164
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 4221565 20-JUL-10 4421777 22-JUL-10

Some other useful RMAN commands are the following as well:
RMAN> list backup;
RMAN> list backup of database completed after '19-JUL-10';

Now that you know the Archive Log Seq you want to restore to, you can issue the following commands in the RUN block.

RMAN> RUN
{
# Restore autobackup of the control file.
# This example assumes that you have

# accepted the default format
# for the autobackup name.

RESTORE CONTROLFILE FROM AUTOBACKUP;
# The set until command is used in case
# the database
structure has changed in
# the most recent backups, and you want to

# recover to that point-in-time.
# In this way RMAN restores the database

# to the same structure that the
# database had at the specified time.

ALTER DATABASE MOUNT;
SET UNTIL SEQUENCE 34 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Starting restore at ...
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=98 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=114 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=146 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 6.5 (2010011113)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
.
.
.
Finished restore at ...

Starting recover at ...
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_DISK_1

starting media recovery
.
.
.
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Hopefully all your restores are tests and if you are ever faced with a production restore it all goes well.

A DBA can do everything else in the world wrong -- EVERYTHING. We can fix that, we can resolve those issues. But if they mess up backup and recovery – forget about it. You are hosed.
~ Unknown author and DBA

No comments:

Post a Comment

Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.

How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...