RMAN -- COLD Backup and Restore

TEST CASE

1. Here I have a database named test (which is the database I'm goin' to take a backup and then restore it).

2. I've a database called rman, which is my rman repository.

3. OS Version: Windows XP (All the rman commands are similar in unix too).

4. Fist I take a cold backp of this database connecting to rman, then delete the datafiles, controlfiles, redo log files ( meaning... i'm deleting all my physical files in the database) and then restore these files.

5. During the restore I assume that our init(db_name).ora or spfile(db_name).ora and orapw(db_name) are in place. In case you don't have them you can always copy init.ora file from some other database, edit the db name, file systems etc.. to configure for this database. You can also create the orapw file using orapwd utility that is available.

6. I configure auto controlfile backup on so that i don't have to do "backup current controlfile" everytime i do a database backup.

7. You will observe that all the commands issued are in "BLACK", the output of the commands are in "BLUE", my comments in "RED"

TAKING A COLD BACKUP OF THE DATABASE

C:\Documents and Settings\vish>set oracle_sid=test

C:\Documents and Settings\vish>rman rcvcat rman/rman@rman
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 13 02:05:41 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Here the database is registered in rman catalog

RMAN> list backup;

Here, i don't see any backups listed, 'cos i don't have a backup yet.

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

DB shutdown, now lets start the backup process

RMAN>
For all cold backups, the db should be in mount state for RMAN.

RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 104857600 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 2945024 bytes

RMAN>

Show all command gives all the rman configuration parameters

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

etc... etc...

As you can see above, our controlfile autobackup is off, i'm goin' to set it to ON now.

RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Now, lets start the backup

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> backup database;
5> release channel ch1;
6> }

The output of this will be something as shown below.

allocated channel: ch1
channel ch1: sid=156 devtype=DISK
Starting backup at 13-SEP-06
channel ch1: starting full datafile backupset
channel ch1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
channel ch1: starting piece 1 at 13-SEP-06
channel ch1: finished piece 1 at 13-SEP-06
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2006_09_13\O1_MF_NNNDF_TAG20060913T021312_2JH8CTMZ_.BKP tag=TAG20060913T021312 comment=NONE
channel ch1: backup set complete, elapsed time: 00:02:11
Finished backup at 13-SEP-06 \

Starting Control File and SPFILE Autobackup at 13-SEP-06
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2006_09_13\O1_MF_S_601006129_2JH8J0L1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-06
released channel: ch1

As you can see above, the controlfile is being backuped up automatically though I did not mention , backup current controlfile, ‘cos of setting up of the congifuration parameters above.

RMAN>

Now that the backup is done, I’m starting up the db. You can do this via sqlplus too. Does not make any difference.

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> sql 'alter database open';
5> release channel ch1;
6> }

allocated channel: ch1
channel ch1: sid=156 devtype=DISK
sql statement: alter database open
released channel: ch1

RMAN>

Now the db is up and running. So, now we have a consistent backup, lets shutdown the db, delete all the controlfiles, datafiles, redologs and then try to restore the db.

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

At this point, I have deleted all my datafiles, controlfiles, redologs and then started the restore.

NOTE : You can get the list of datafiles, controlfiles, redo log files by executing the set of commands below.

sql > select name from v$controlfile;

sql > select member from v$logfile;

sql > select name from v$datafile;

All of my files are in C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST as you can see from the output of our cold backup above. So, i've deleted all my *.dbf, *.ctl, *.log files in this directory. In production environments they are usually located in different mount points.

NOW OUR DATABASE IS DELETED !! WE NEED TO RESTORE IT !!

RESTORING THE DATABASE

Steps in restore/recovery: since we’ve deleted the controlfile also, we do a startup NOMOUNT, then restore the controlfile, do a startup mount, restore database and then do, alter database open resetlogs. And that’s it, we are done with the cold backup and restore of a db. Lets see how it works.

RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 104857600 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 2945024 bytes

Since our controlfile is lost, we just do a startup nomount.

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> restore controlfile;
5> sql 'alter database mount';
6> restore database;
7> sql 'alter database open resetlogs';
8> release channel ch1;
9> }

In the above script, we have restored the controlfile and then did a "alter database mount" since our controlfile is in place now. Once the controlfile is in place and the database is mounted, i have restored the database using "restore database". This will get all your datafiles into the directory. Once the restore of the datafiles is done, i did a 'alter database open resetlogs", which is needed if you lose all your redo logs also. If you have your redo logs, then you can just say, recover database and then alter database open.

allocated channel: ch1
channel ch1: sid=157 devtype=DISK
Starting restore at 13-SEP-06
channel ch1: starting datafile backupset restore
channel ch1: restoring control file
channel ch1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2006_09_13\O1_MF_S_601006129_2JH8J0L1_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2006_09_13\O1_MF_S_601006129_2JH8J0L1_.BKP tag=TAG20060913T021527
channel ch1: restore complete, elapsed time: 00:00:09
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\CONTROL03.CTL
Finished restore at 13-SEP-06
sql statement: alter database mount
Starting restore at 13-SEP-06
Starting implicit crosscheck backup at 13-SEP-06
Crosschecked 1 objects
Finished implicit crosscheck backup at 13-SEP-06
Starting implicit crosscheck copy at 13-SEP-06
Finished implicit crosscheck copy at 13-SEP-06
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2006_09_13\O1_MF_S_601006129_2JH8J0L1_.BKP
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF
channel ch1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2006_09_13\O1_MF_NNNDF_TAG20060913T021312_2JH8CTMZ_.BKP
channel ch1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2006_09_13\O1_MF_NNNDF_TAG20060913T021312_2JH8CTMZ_.BKP tag=TAG20060913T021312
channel ch1: restore complete, elapsed time: 00:01:15
Finished restore at 13-SEP-06
sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
released channel: ch1
RMAN>
RMAN> exit
Recovery Manager complete.

Now that the recovery manager has restored controlfiles, datafiles, recreated redologs (since we said 'alter database open resetlogs' , lets see if the db is up and running fine.

C:\Documents and Settings\vish>set oracle_sid
oracle_sid=test
C:\Documents and Settings\vish>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 13 02:21:50 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- ----------
TEST READ WRITE
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

YEP !, the db is up and running fine. So we have successfully done a cold backup, deleted the datafiles, controlfiles, redologs and then restored/recovered from the cold backup.

NOTE : when you do a 'alter database open resetlogs', your redologs are recreated and also a new incarnation is registered into your rman catalog. So, if you have to restore again from the same backup we have taken above, you have to do

rman> reset database to incarnation old_incarnation_id;

(you can get the incarnation id using rman> list incarnation of database;)

and only then rman will be able to find that backup. By default the incarnation is set to the current incarnation.

Any questions, Leave a comment and i'll reply you back.

Disclaimer : Implement this procedure at your own risk. I'm not responsible for any loss of data/other things.