Wednesday, 10 July 2013

Oracle 11G RMAN Restore Example

There are many ways to restore a database using an RMAN backup - this example assumes you are performing a Disaster-Recovery restore of all data and recovering the entire database with the same SID and the same disk/tablespace layout.
You will need the following information:
Database SID: ________
Database SYS password: ________
Disk layout and sizes: ________
Database ID (DBID): ________

There are 5 steps to recover the database:

1) Create a new (empty) database instance
2) Mount the instance
3) Restore the datafiles
4) Recover the database
5) Reset the logs

1) Create a new (empty) database instance
Configure the new server with same disk layout as the original database - if necessary use Symbolic Links (or in Windows use disk manager to re-assign drive letters.)
Ensure you have enough disk space for both the backup files plus the restored database files.
Create a new database with the database configuration assistant (DBCA) and set the SYS password and global database_name to the same as the original database.
If the database to be restored is in archive log mode, set the LOG_ARCHIVE_FORMAT parameter to match the setting in the original database.
The ORAPWD utility can also be used to change the SYS password.
Set the environment variable NLS_LANG for your character set -

2)  Mount the empty instance

SQL> Shutdown immediate;
SQL> Startup mount;
or specifying the pfile explicitly:
SQL> CREATE PFILE='C:\oracle\Database\initashok.ora' FROM SPFILE;
SQL> Shutdown immediate;
SQL> Startup mount pfile=C:\oracle\Database\initashok.ora

3) Restore the datafiles

In this case we have copied the RMAN backup files and archive logs to R:\Rman\
Change the dbid to match that of the database being restored
RMAN> SET dbid = 777777774;
RMAN> run {
restore database;
At this point the datafiles and tablespaces will be re-created. For a large database it can take a long time to restore each tablespace - for better performance during a restore place the RMAN backup files on a separate disk to the Oracle datafiles to reduce disk contention.

4) Recover the database

SQL> Recover from 'D:\oradata\ashok' database until cancel using backup controlfile;
SQL> cancel

5) Reset the logs

SQL> alter database open resetlogs;
This will update all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.