Reduce Recovery Time By Using Datafile Backup Copy

One of the strategy to reduce the recovery time or downtime of database is to use backup copy of datafile. Backup copy helps by eliminating the process of restoring datafile from backup set, thus no restore time. The recovery is done by switching the database to use the backup copy.

Here are the general steps of restoring & recovering datafile using backupset:

1. Making the datafile offline
2. Restore datafile from backup set
3. Applying incremental backup
4. Recover datafile using the archived log
5. Make datafile online

And here are the steps using the switch approach (using the backup copy):

1. Making datafile offline
2. Switch to copy
3. Recover datafile
4. Making Datafile online

The reduction of downtime is achieved by skipping the datafile restoration, which could take a very long time depending on the datafile size.

The following example simulate the recovery of datafile 6.

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name LASTEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1500 SYSTEM *** /oradata/LASTEST/dbf/dbfsystem01.dbf
2 1500 SYSAUX *** /oradata/LASTEST/dbf/dbfsysaux01.dbf
3 365 UNDOTBS1 *** /oradata/LASTEST/dbf/dbfundotbs01.dbf
4 480 USERS *** /oradata/LASTEST/dbf/dbfusers01.dbf
5 20 DRSYS *** /oradata/LASTEST/dbf/drsys01.dbf
6 0 TEST *** /oradata/LASTEST/dbf/test.dbf
8 10 TOOLS *** /oradata/LASTEST/dbf/tools01.dbf
9 70 XDB *** /oradata/LASTEST/dbf/xdb01.dbf
19 250 LOGMNRTS *** /oradata/LASTEST/dbf/logmnrts_01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 328 TEMP 32767 /oradata/LASTEST/dbf/dbftemp01.dbf

RMAN> list copy of datafile 6;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
82 6 A 26-AUG-10 23408928202 26-AUG-10
Name: /orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf
Tag: TAG20100826T093002


RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf"

RMAN> recover datafile 6;

Starting recover at 26-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

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

Finished recover at 26-AUG-10

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> exit

Now that we have quickly recover datafile 6, we could find a suitable time to switch the datafile back to the original location.

r-bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 26 09:38:28 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: LASTEST (DBID=2460905176)

RMAN> backup as copy datafile 6 format ='/oradata/LASTEST/dbf/test01.dbf';

Starting backup at 26-AUG-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/orabackup/flash_recovery_area/LASTEST/datafile/o1_mf_test_67cjwvcc_.dbf
output file name=/oradata/LASTEST/dbf/test01.dbf tag=TAG20100826T093907 RECID=83 STAMP=728041148
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-AUG-10

Starting Control File and SPFILE Autobackup at 26-AUG-10
piece handle=/orabackup/flash_recovery_area/LASTEST/autobackup/2010_08_26/o1_mf_s_728041149_67ckfy19_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-10

RMAN>

RMAN>

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "/oradata/LASTEST/dbf/test01.dbf"

RMAN> recover datafile 6;

Starting recover at 26-AUG-10
using channel ORA_DISK_1

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

Finished recover at 26-AUG-10

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>