The Case of Missing Utility

Recently, I’ve asked to help a client who was having doubts about their backup strategy. The client was considering image backup of database which would be constantly recovered and backed up to tape on the daily basis, by using “backup recovery area” command. However, there was a problem: there is no “restore recovery area” command. The utility to restore the recovery area seems to be missing, thus the name of this article became “The Case of Missing Utility”.

So, let’s see some definitions. This is what the client has envisioned:

RMAN> backup incremental level 1 for recover of copy database;

 

Starting backup at 24-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oracle/oradata/ORCL/datafile/o1_mf_system_8kb9mnwc_.dbf

input datafile file number=00002 name=/oracle/oradata/ORCL/datafile/o1_mf_sysaux_8kb9mnyc_.dbf

input datafile file number=00003 name=/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8kb9mnz2_.dbf

input datafile file number=00005 name=/oracle/oradata/ORCL/datafile/o1_mf_example_8kb9pkg2_.dbf

input datafile file number=00004 name=/oracle/oradata/ORCL/datafile/o1_mf_users_8kb9mo0w_.dbf

input datafile file number=00006 name=/oracle/oradata/ORCL/datafile/trace_analyze_01.dbf

channel ORA_DISK_1: starting piece 1 at 24-FEB-13

channel ORA_DISK_1: finished piece 1 at 24-FEB-13

piece handle=/oracle/fast_recovery_area/ORCL/backupset/2013_02_24/o1_mf_nnnd1_TAG20130224T170756_8lo3sx4b_.bkp tag=TAG20130224T170756 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36

Finished backup at 24-FEB-13

This is, essentially, “backup as copy”. That can be proven by inspecting the copy of datafile:

RMAN> list copy of datafile 1;

 

List of Datafile Copies

=======================

 

Key File S Completion Time Ckp SCN Ckp Time

——- —- – ————— ———- —————

22 1 A 24-FEB-13 1117374 24-FEB-13

Name: /oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_8lo3m1p1_.dbf

Tag: TAG20130224T170417

Now, we can recover the copy of the database:

RMAN> recover copy of database;

 

Starting recover at 24-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_8lo3m1p1_.dbf

recovering datafile copy file number=00002 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_sysaux_8lo3o8tm_.dbf

recovering datafile copy file number=00003 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_undotbs1_8lo3qn31_.dbf

recovering datafile copy file number=00004 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_users_8lo3s6k6_.dbf

recovering datafile copy file number=00005 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8lo3rf8w_.dbf

recovering datafile copy file number=00006 name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_trcanlzr_8lo3sfrx_.dbf

channel ORA_DISK_1: reading from backup piece /oracle/fast_recovery_area/ORCL/backupset/2013_02_24/o1_mf_nnnd1_TAG20130224T172038_8lo4kplk_.bkp

channel ORA_DISK_1: piece handle=/oracle/fast_recovery_area/ORCL/backupset/2013_02_24/o1_mf_nnnd1_TAG20130224T172038_8lo4kplk_.bkp tag=TAG20130224T172038

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 24-FEB-13

Starting Control File and SPFILE Autobackup at 24-FEB-13

piece handle=/oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808248235_8lo4qvr2_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 24-FEB-13

 

RMAN>

Now we have a current copy of the database which can be used to immediately repair the problem tablespace:

SQL> select file_name from dba_data_files where file_id=5;

FILE_NAME

————————————————————

/oracle/oradata/ORCL/datafile/o1_mf_example_8kb9pkg2_.dbf

Elapsed: 00:00:00.03

SQL> alter tablespace example offline;

Tablespace altered.

Elapsed: 00:00:00.30

SQL>

 

Now it is possible to switch the file to copy:

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy “/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8lo3rf8w_.dbf”

RMAN> recover datafile 5;

Starting recover at 24-FEB-13

using channel ORA_DISK_1

 

starting media recovery

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

Finished recover at 24-FEB-13

RMAN> sql ‘alter tablespace example online';

sql statement: alter tablespace example online

RMAN>

The new file name can be verified by querying DBA_DATA_FILES:

SQL> select file_name from dba_data_files where file_id=5;

FILE_NAME

——————————————————————————–

/oracle/fast_recovery_area/ORCL/datafile/o1_mf_example_8lo3rf8w_.dbf

Elapsed: 00:00:00.01

 

That is a well known procedure for switching the data file to a copy, but now comes the twist. First, a backup of recovery area is taken

RMAN> run {

2> allocate channel c0 device type SBT;

3> backup recovery area;

4> }

 

released channel: ORA_DISK_1

allocated channel: c0

channel c0: SID=36 device type=SBT_TAPE

channel c0: CommVault Systems for Oracle: Version 9.0.0(BUILD84)

Starting backup at 24-FEB-13

channel c0: starting full datafile backup set

channel c0: including datafile copy of datafile 00001 in backup set

input file name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_8lo3m1p1_.dbf

channel c0: including datafile copy of datafile 00002 in backup set

input file name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_sysaux_8lo3o8tm_.dbf

channel c0: including datafile copy of datafile 00003 in backup set

input file name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_undotbs1_8lo3qn31_.dbf

channel c0: including datafile copy of datafile 00004 in backup set

input file name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_users_8lo3s6k6_.dbf

channel c0: including datafile copy of datafile 00006 in backup set

input file name=/oracle/fast_recovery_area/ORCL/datafile/o1_mf_trcanlzr_8lo3sfrx_.dbf

channel c0: starting piece 1 at 24-FEB-13

channel c0: finished piece 1 at 24-FEB-13

piece handle=2qo2pumr_1_1 tag=TAG20130224T190251 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: backup set complete, elapsed time: 00:02:15

channel c0: starting archived log backup set

channel c0: specifying archived log(s) in backup set

input archived log thread=1 sequence=23 RECID=21 STAMP=808248006

channel c0: starting piece 1 at 24-FEB-13

channel c0: finished piece 1 at 24-FEB-13

piece handle=2ro2pur2_1_1 tag=TAG20130224T190251 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: backup set complete, elapsed time: 00:00:07

channel c0: input backup set: count=46, stamp=806870968, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_08/o1_mf_s_806870968_8kc3r95r_.bkp

piece handle=c-1334625008-20130208-07 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:08

channel c0: input backup set: count=80, stamp=808247268, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808247268_8lo3sopq_.bkp

piece handle=c-1334625008-20130224-00 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=81, stamp=808247276, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/backupset/2013_02_24/o1_mf_nnnd1_TAG20130224T170756_8lo3sx4b_.bkp

piece handle=2ho2pnvc_1_2 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=82, stamp=808247372, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808247372_8lo3wwvs_.bkp

piece handle=c-1334625008-20130224-01 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=83, stamp=808247507, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808247507_8lo414h9_.bkp

piece handle=c-1334625008-20130224-02 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=84, stamp=808248024, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808248024_8lo4k964_.bkp

piece handle=c-1334625008-20130224-03 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=85, stamp=808248038, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/backupset/2013_02_24/o1_mf_nnnd1_TAG20130224T172038_8lo4kplk_.bkp

piece handle=2lo2pon6_1_2 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=86, stamp=808248133, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808248133_8lo4npb0_.bkp

piece handle=c-1334625008-20130224-04 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=87, stamp=808248235, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808248235_8lo4qvr2_.bkp

piece handle=c-1334625008-20130224-05 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=88, stamp=808248752, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808248752_8lo5811k_.bkp

piece handle=c-1334625008-20130224-06 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

channel c0: input backup set: count=89, stamp=808251755, piece=1

channel c0: starting piece 1 at 24-FEB-13

channel c0: backup piece /oracle/fast_recovery_area/ORCL/autobackup/2013_02_24/o1_mf_s_808251755_8lo85wf6_.bkp

piece handle=c-1334625008-20130224-07 comment=API Version 2.0,MMS Version 9.0.0.84

channel c0: finished piece 1 at 24-FEB-13

channel c0: backup piece complete, elapsed time: 00:00:07

Finished backup at 24-FEB-13

Starting Control File and SPFILE Autobackup at 24-FEB-13

piece handle=c-1334625008-20130224-08 comment=API Version 2.0,MMS Version 9.0.0.84

Finished Control File and SPFILE Autobackup at 24-FEB-13

released channel: c0

 

RMAN>

The question is how to restore the recovery area? There is no “restore recovery area” command. Trying to execute one will produce a syntax error:

RMAN> restore recovery area;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found “recovery”: expecting one of: “archivelog, channel, check, controlfile, database, datafile, device, force, from, high, preview, primary, skip readonly, spfile, standby, tablespace, to restore point, until restore point, until, validate, (”

RMAN-01007: at line 1 column 9 file: standard input

RMAN>

Is it an omission by Oracle Corp? Nope, the case of the “missing utility” is just a misunderstanding of the recovery area principles: backup of recovery area is just a backup of database. RMAN will see the backup of the recovery area as a backup of database. Look at the last backup in the output of “list backup of database summary” command:

 

RMAN> list backup of database summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

——- — — – ———– ————— ——- ——- ———- —

51 B 1 A * 24-FEB-13 1 2 NO TAG20130224T170756

55 B 1 A * 24-FEB-13 1 2 NO TAG20130224T172038

60 B F A SBT_TAPE 24-FEB-13 1 1 NO TAG20130224T190251

 

The last backup is stored on device type SBT, according to the listing above. That can also be verified by performing a full-fledged “restore database validate” from tape (device type SBT).

RMAN> run {

2> allocate channel c0 device type SBT;

3> restore database validate;

4> }

allocated channel: c0

channel c0: SID=36 device type=SBT_TAPE

channel c0: CommVault Systems for Oracle: Version 9.0.0(BUILD84)

Starting restore at 24-FEB-13

channel c0: starting validation of datafile backup set

channel c0: reading from backup piece 2qo2pumr_1_1

channel c0: piece handle=2qo2pumr_1_1 tag=TAG20130224T190251

channel c0: restored backup piece 1

channel c0: validation complete, elapsed time: 00:02:15

channel c0: starting validation of datafile backup set

channel c0: reading from backup piece 2to2pvh8_1_1

channel c0: piece handle=2to2pvh8_1_1 tag=TAG20130224T191656

channel c0: restored backup piece 1

channel c0: validation complete, elapsed time: 00:00:15

Finished restore at 24-FEB-13

released channel: c0

RMAN>

 

Now that there is basic understanding of the fact that recovery area protects database and that a backup of recovery area is essentially just a database backup, the sought for backup strategy becomes very simple. The strategy is to maintain the backup of the database on disk, using “backup incremental level 1 for recover of copy database” and “recover copy of database” is only the first step which provides an immediate ability to recover, on the datafile level. Backups of recovery area are simply database backups. There is no “missing utility”, the database is fully protected, the only downside is that the storage consumption is doubled. There is another copy of database in the recovery area, which cannot be used for anything else. If that is unacceptable, there is always data guard and standby databases. But that’s an entirely different story.

About these ads

About mgogala

I am a long time Oracle DBA, who has worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Miscellaneous Oracle and tagged , , . Bookmark the permalink.

3 Responses to The Case of Missing Utility

  1. Justin says:

    Hi,
    Intereresting post!

    So, after the next ‘backup incremental for recover….’ & ‘recover copy….’, what will happen when you do the next ‘backup recovery area’? Will it do a complete backup of the datafile-copies again or will it somehow handle the delta between the first backup and the second?
    Or is the ‘backup of recovery area’ to sbt_tape handling the backup of datafile-copies as a ‘normal’ backup of the database?

    /J

    • mgogala says:

      Justin, Oracle cannot do incremental cumulative backups of the recovery area. The only thing that makes sense is a full backup. The block change tracking file can only be used for data files themselves.
      As for the normal backup of database, yes, that was the point of the post. Backup to SBT is handling backup of datafile copies as a normal backup of database.

  2. Justin says:

    Ok, thank you!

    /J

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s