Toad World

Validating The Backup before using

Avatar photo
Post author:Written By

How to know if I will be able to use successfully my RMAN Backup? that's is a question performed for many DBA's, RMAN also provide functions to validate corrupted blocks in the database, but also it provides a function to validate the Backup sets.

The sintaxis is "VALIDATE BACKUPSET [#BackupSet]“.

Let's do an example about how to use the validate backupset option:

 

The list of my current Backups:

RMAN> list backup of database summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
27 B 0 A DISK 29-NOV-14 1 1 NO L0
29 B 1 A DISK 29-NOV-14 1 1 NO ICUMULATIVE
31 B 1 A DISK 29-NOV-14 1 1 NO IDIFFERENTIAL

 

Let's validate every backupset:

RMAN> validate backupset 27;

Starting validate at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp tag=L0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished validate at 29-NOV-14


RMAN> validate backupset 29;

Starting validate at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
    /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_ICUMULATIVE_b7mpg1gl_.bkp
channel ORA_DISK_1: piece
    handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_ICUMULATIVE_b7mpg1gl_.bkp tag=ICUMULATIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
Finished validate at 29-NOV-14

RMAN> validate backupset 31;

Starting validate at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
    /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_IDIFFERENTIAL_b7mphq7m_.bkp
channel ORA_DISK_1: piece
    handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_IDIFFERENTIAL_b7mphq7m_.bkp tag=IDIFFERENTIAL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
Finished validate at 29-NOV-14

I also have a backup of controlfile:

RMAN> list backup of controlfile summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
32 B F A DISK 29-NOV-14 1 1 NO TAG20141129T093639

Now let's validate the backupset where the controlfile is located:

RMAN> validate backupset 32;

Starting validate at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece
    /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_s_864898599_b7mphqrx_.bkp
channel ORA_DISK_1: piece
    handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_s_864898599_b7mphqrx_.bkp tag=TAG20141129T093639
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished validate at 29-NOV-14

Well, as per RMAN I should be able to recover my database (controlfile and datafiles). Let's see if this is true:

 

Simulating a disaster:

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

ASMCMD> pwd
+data/orcl/datafile
ASMCMD> rm -rf *
ASMCMD> pwd
+data/orcl/controlfile
ASMCMD> rm -rf *
ASMCMD>

 

Restoring the controlfile:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 268437400 bytes
Database Buffers 1862270976 bytes
Redo Buffers 4923392 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 29-NOV-14
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP
 /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_s_864898599_b7mphqrx_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP
    /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_s_864898599_b7mphqrx_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATA/orcl/controlfile/current.256.850528655
Finished restore at 29-NOV-14

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

I was able to restore the controlfile using backupset 32. So far all good.

 

Restoring the database:

RMAN> restore database;

Starting restore at 29-NOV-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.263.864897473
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.261.864897473
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.260.864897473
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.259.864897473
channel ORA_DISK_1: reading from backup piece/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp tag=L0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 29-NOV-14

I was able to restore the datafiles using backupset 27. So far all good.

 

Recovering the database:

RMAN> recover database;

Starting recover at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/orcl/datafile/system.354.864907351
destination for restore of datafile 00002: +DATA/orcl/datafile/sysaux.353.864907351
destination for restore of datafile 00003: +DATA/orcl/datafile/undotbs1.437.864907351
destination for restore of datafile 00004: +DATA/orcl/datafile/users.438.864907351
channel ORA_DISK_1: reading from backup
    piece/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_ICUMULATIVE_b7mpg1gl_.bkp
channel ORA_DISK_1: piece
    handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_ICUMULATIVE_b7mpg1gl_.bkp tag=ICUMULATIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/orcl/datafile/system.354.864907351
destination for restore of datafile 00002: +DATA/orcl/datafile/sysaux.353.864907351
destination for restore of datafile 00003: +DATA/orcl/datafile/undotbs1.437.864907351
destination for restore of datafile 00004: +DATA/orcl/datafile/users.438.864907351
channel ORA_DISK_1: reading from backup
    piece/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_IDIFFERENTIAL_b7mphq7m_.bkp
channel ORA_DISK_1: piece
    handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd1_IDIFFERENTIAL_b7mphq7m_.bkp tag=IDIFFERENTIAL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 187 is already on disk as file +DATA/orcl/onlinelog/group_2.258.850528657
archived log for thread 1 with sequence 188 is already on disk as file +DATA/orcl/onlinelog/group_1.257.850528657
archived log file name=+DATA/orcl/onlinelog/group_2.258.850528657 thread=1 sequence=187
archived log file name=+DATA/orcl/onlinelog/group_1.257.850528657 thread=1 sequence=188
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-NOV-14

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

I was able to recover the database using backupsets 29 and 31. Then, our conclusion is that RMAN was right about the validation.

Now let's corrupt our backup and let's see if RMAN is able to see that:

 

Backup corrupted example:

The backupsets:

[oracle@a1 2014_11_29]$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29
[oracle@a1 2014_11_29]$ ls -ltr
total 617728
-rw-r----- 1 oracle asmadmin 631169024 Nov 29 09:34 o1_mf_nnnd0_L0_b7mpc393_.bkp
-rw-r----- 1 oracle asmadmin 540672 Nov 29 09:35 o1_mf_nnnd1_ICUMULATIVE_b7mpg1gl_.bkp
-rw-r----- 1 oracle asmadmin 212992 Nov 29 09:36 o1_mf_nnnd1_IDIFFERENTIAL_b7mphq7m_.bkp
[oracle@a1 2014_11_29]$

we will corrupt our backupset 27 (/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp), but first let's see if RMAN says the backupset is valid:

RMAN> validate backupset 27;

Starting validate at 29-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp tag=L0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished validate at 29-NOV-14

 

Let's corrupt the backupset:

[oracle@a1 ORCL]$
 dd if=/dev/zero of=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp oflag=direct bs=1024
 count=1
1+0 records in
1+0 records out
1024 bytes (1.0 kB) copied, 0.000298291 seconds, 3.4 MB/s
[oracle@a1 ORCL]$

Now let's validate again the backupset:

RMAN> validate backupset 27;

Starting validate at 29-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 11/29/2014 13:37:52
ORA-19870: error while restoring backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
ORA-19505: failed to identify file "/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp"
ORA-27048: skgfifi: file header information is invalid
Additional information: 8

Well, RMAN says there is something wrong with the backupset. it is better don't use that backup. What about if we try to use that backupset?

RMAN> restore database;

Starting restore at 29-NOV-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.263.864897473
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.261.864897473
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.260.864897473
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.259.864897473
channel ORA_DISK_1: reading from backup piece
    /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece
    /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp
ORA-19505: failed to identify file "/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_11_29/o1_mf_nnnd0_L0_b7mpc393_.bkp"
ORA-27048: skgfifi: file header information is invalid
Additional information: 10

failover to previous backup

creating datafile file number=1 name=+DATA/orcl/datafile/system.263.864897473
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/29/2014 13:38:21
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/orcl/datafile/system.263.864897473'

RMAN>

Yes, there were errors, so RMAN was right about the validation.

Tags:

About the Author

Deiby Gomez

Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.

Start the discussion at forums.toadworld.com