RMAN Recovery using the SWITCH DATABASE TO COPY command

By using Optimized Incremental Backup to disk backup strategy, an up to date copy of the database is always available on disk. In the event of a failure, the SWITCH command will point the controlfile to the backup of the datafiles that are present on disk.

In this way, we can significantly reduce the downtime related to restoring large datafiles from tape in the event of a media failure.

The downside of this is that we need to consider the disk space (and cost) of additional disks which will be required to hold the recovered incremental copies of backups on disk. Also as we will see, the SWITCH DATABASE TO COPY command will alter the names of all the datafiles and there is manual work involved in renaming them back to their original datafile names.

The following RMAN command can be used to take an optimized incremental backup to disk. Note in the first run, since no copy of any of the datafiles are found since this is the first backup, no recovery is performed.

But subsequent backups will find that the backup copies of the datafiles will undergo recovery as changes which have occurred since the last incremental backup are applied to them making them ‘current’.

 run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=143 devtype=DISK

Starting recover at 31-JUL-09
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
no copy of datafile 6 found to recover
no copy of datafile 7 found to recover
no copy of datafile 8 found to recover
no copy of datafile 9 found to recover
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 8 found
no parent backup or copy of datafile 6 found
.......
.......

RMAN>  run {
allocate channel c1 device type disk format '/u02/oradata/backup/bkp.%u';
recover copy of database with tag 'LEVEL0_BKP';
backup incremental level 1 for recover of copy with tag 'LEVEL0_BKP' database;
}2> 3> 4> 5>

allocated channel: c1
channel c1: sid=140 devtype=DISK

Starting recover at 31-JUL-09
channel c1: starting incremental datafile backupset restore
channel c1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=/u02/oradata/backup/bkp.2qklgmt6
recovering datafile copy fno=00002 name=/u02/oradata/backup/bkp.2pklgmpt
recovering datafile copy fno=00003 name=/u02/oradata/backup/bkp.2rklgmvr
recovering datafile copy fno=00004 name=/u02/oradata/backup/bkp.2sklgn0u
recovering datafile copy fno=00005 name=/u02/oradata/backup/bkp.2vklgn30
recovering datafile copy fno=00006 name=/u02/oradata/backup/bkp.2uklgn2h
recovering datafile copy fno=00007 name=/u02/oradata/backup/bkp.31klgn3f
recovering datafile copy fno=00008 name=/u02/oradata/backup/bkp.2tklgn1o
recovering datafile copy fno=00009 name=/u02/oradata/backup/bkp.30klgn37
channel c1: reading from backup piece /u02/oradata/backup/bkp.33klgnda
channel c1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.33klgnda tag=TAG20090731T103002
channel c1: restore complete, elapsed time: 00:00:08
Finished recover at 31-JUL-09

Starting backup at 31-JUL-09
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u02/oradata/testdb/undotbs01.dbf
input datafile fno=00001 name=/u02/oradata/testdb/system01.dbf
input datafile fno=00003 name=/u02/oradata/testdb/sysaux01.dbf
input datafile fno=00004 name=/u02/oradata/testdb/users01.dbf
input datafile fno=00008 name=/u02/oradata/testdb/rman10.dbf
input datafile fno=00006 name=/u02/oradata/testdb/users02.dbf
input datafile fno=00005 name=/u02/oradata/testdb/example01.dbf
input datafile fno=00009 name=/u02/oradata/testdb/users03.dbf
......
......

Let us simulate a database failure by removing all the datafiles at the OS level.

We will then use the SWITCH DATABASE TO COPY command to point the database to the backups which are available on disk and note how the recovery is performed in a few seconds without any any backups having to be restored from tape and no archive log files also being applied.

testdb:/u02/oradata> cd /u02/oradata/testdb/
testdb:/u02/oradata/testdb> rm users*.dbf

We then shutdown and mount the database

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 2083304 bytes
Variable Size 146802200 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "/u02/oradata/backup/bkp.2qklgmt6"
datafile 2 switched to datafile copy "/u02/oradata/backup/bkp.2pklgmpt"
datafile 3 switched to datafile copy "/u02/oradata/backup/bkp.2rklgmvr"
datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.2sklgn0u"
datafile 5 switched to datafile copy "/u02/oradata/backup/bkp.2vklgn30"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.2uklgn2h"
datafile 7 switched to datafile copy "/u02/oradata/backup/bkp.31klgn3f"
datafile 8 switched to datafile copy "/u02/oradata/backup/bkp.2tklgn1o"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.30klgn37"

RMAN> recover database;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=156 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/backup/bkp.2qklgmt6
destination for restore of datafile 00002: /u02/oradata/backup/bkp.2pklgmpt
destination for restore of datafile 00003: /u02/oradata/backup/bkp.2rklgmvr
destination for restore of datafile 00004: /u02/oradata/backup/bkp.2sklgn0u
destination for restore of datafile 00005: /u02/oradata/backup/bkp.2vklgn30
destination for restore of datafile 00006: /u02/oradata/backup/bkp.2uklgn2h
destination for restore of datafile 00007: /u02/oradata/backup/bkp.31klgn3f
destination for restore of datafile 00008: /u02/oradata/backup/bkp.2tklgn1o
destination for restore of datafile 00009: /u02/oradata/backup/bkp.30klgn37
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/bkp.35klgnj6
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/bkp.35klgnj6 tag=TAG20090731T103309
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 31-JUL-09

RMAN> alter database open;

database opened

After the SWITCH command has completed, we can now see that the datafile names have changed.

I have not been able to identify an easy way to rename them back to their original names other than recreating the controlfile and renaming all the datafiles at the OS level as well. This could be an issue when we have hundreds of datafiles to rename.

 1* select name,status from v$datafile
SQL> /

NAME                                     STATUS
---------------------------------------- -------
/u02/oradata/backup/bkp.2qklgmt6         SYSTEM
/u02/oradata/backup/bkp.2pklgmpt         ONLINE
/u02/oradata/backup/bkp.2rklgmvr         ONLINE
/u02/oradata/backup/bkp.2sklgn0u         ONLINE
/u02/oradata/backup/bkp.2vklgn30         ONLINE
/u02/oradata/backup/bkp.2uklgn2h         ONLINE
/u02/oradata/backup/bkp.31klgn3f         ONLINE
/u02/oradata/backup/bkp.2tklgn1o         ONLINE
/u02/oradata/backup/bkp.30klgn37         ONLINE

Similarly, we can also use the SWITCH TABLEPACE TO COPY command to quickly recover from the loss of all datafiless at the tablespace level.

We simulate this by removing all the datafiles for the USERS tablespace at the OS level.

testdb:/u02/oradata/testdb> rm users*.dbf

SQL> conn scott/tiger
Connected.
SQL> create table emp2
2 tablespace users
3 as select * from emp;
as select * from emp
*
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u02/oradata/testdb/users01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

RMAN>  run {
2> sql 'alter tablespace users offline immediate';3>
}

sql statement: alter tablespace users offline immediate

RMAN> switch tablespace users to copy;

datafile 4 switched to datafile copy "/u02/oradata/backup/bkp.04klgv2b"
datafile 6 switched to datafile copy "/u02/oradata/backup/bkp.06klgv3k"
datafile 9 switched to datafile copy "/u02/oradata/backup/bkp.08klgv4i"


RMAN> recover tablespace users;

Starting recover at 31-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=143 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 31-JUL-09

RMAN> sql 'alter tablespace users online';

sql statement: alter tablespace users online


SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/backup/bkp.08klgv4i
/u02/oradata/backup/bkp.06klgv3k
/u02/oradata/backup/bkp.04klgv2b