RMAN RESTORE VALIDATE is the new feature introduced in Oracle 11g to check and verify the integrity of the backups which are stored in the Tape or Disk. It is used to detect any corrupted block in the database. It is used to check the corruption at database, tablespace, datafile or datafile block level.
The VALIDATE command for backup and restore is very useful to check and confirm that the backup and the restoration is possible and it is a valid one without actually backing up or restoring datafiles.
Apart from the database, you can also validate the below files,
BACKUPSET
CONTROLFILECOPY
CURRENT CONTROLFILE
DB_RECOVERY_FILE_DEST
SPFILE
RESTORE DATABASE VALIDATE:
This command will check for the last level 0 or FULL backup taken in Disk or Tape.
We can issue the command to validate the whole database using the command RESTORE DATABASE VALIDATE. But the point in concern is, it validates only the datafiles backup and not either the controlfile or archivelog or spfile.
For this we need to issue additional commands like,
RESTORE CONTROLFILE VALIDATE;
RESTORE ARCHIVELOG VALIDATE;
RESTORE SPFILE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE:
This command will check for all the archivelog files catalogued based on the retention policy we set/configure in the RMAN. Keeping this in mind, we should first check for the existence
of the archivelog files in the Disk or Tape which have been generated since the last FULL or LEVEL 0 backup. Else it will end up with error like RMAN-03002, RMAN-06026 and RMAN-06025.
VALIDATE CHECK LOGICAL:
By default RMAN validate command checks only for the Physical Corruption and not the Logical Corruption. For this we need to issue the command
VALIDATE CHECK LOGICAL;
The below example is to just validate the database and not restore.
RMAN> validate database;
Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_GRP/proddb/datafile/system.256.848660835
input datafile file number=00002 name=+DATA_GRP/proddb/datafile/sysaux.257.848660835
input datafile file number=00006 name=+DATA_GRP/proddb/datafile/tbsrman.267.848663633
input datafile file number=00005 name=+DATA_GRP/proddb/datafile/example.265.848660933
input datafile file number=00003 name=+DATA_GRP/proddb/datafile/undotbs1.258.848660835
input datafile file number=00004 name=+DATA_GRP/proddb/datafile/users.259.848660835
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
1 OK 0 14959 92200 1160396
File Name: +DATA_GRP/proddb/datafile/system.256.848660835
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 61210
Index 0 12900
Other 0 3091
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
2 OK 0 23037 70407 1160394
File Name: +DATA_GRP/proddb/datafile/sysaux.257.848660835
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 11483
Index 0 8380
Other 0 27500
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
3 OK 0 1 8960 1160427
File Name: +DATA_GRP/proddb/datafile/undotbs1.258.848660835
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 8959
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
4 OK 0 18 667 1130722
File Name: +DATA_GRP/proddb/datafile/users.259.848660835
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 91
Index 0 39
Other 0 492
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 33684 44240 1130675
File Name: +DATA_GRP/proddb/datafile/example.265.848660933
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 6604
Index 0 1148
Other 0 2804
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
6 OK 0 63357 64000 1160427
File Name: +DATA_GRP/proddb/datafile/tbsrman.267.848663633
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 163
Index 0 85
Other 0 395
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
———— —— ————– —————
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 30-JUN-14
RMAN>
From the above result, this is like an health check reports listing all the datafiles. Then it gives us the detail about the total number of blocks, number of empty blocks and the corrupt blocks per datafile.
It also give the details of the objects inside the datafiles and finally it completes by checking the status of the spfile and controlfile.
As said earlier the below example is to check and confirm that the backup and the restoration is possible and it is a valid one without actually backing up or restoring datafiles.
RMAN> restore database validate;
Starting restore at 30-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/bkup_0kpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0kpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/bkup_0jpbok52_1_1
channel ORA_DISK_1: piece handle=/rman/bkup_0jpbok52_1_1 tag=TAG20140625T210329
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
Finished restore at 30-JUN-14
RMAN> restore controlfile validate;
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /rman/controlfile/ctl_c-588708646-20140625-01
channel ORA_DISK_1: piece handle=/rman/controlfile/ctl_c-588708646-20140625-01 tag=TAG20140625T210428
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 30-JUN-14
VALIDATING a Particular TABLESPACE:
RMAN> validate tablespace system;
Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA_GRP/proddb/datafile/system.256.848660835
channel ORA_DISK_1: validation complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
1 OK 0 14959 92200 1160705
File Name: +DATA_GRP/proddb/datafile/system.256.848660835
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 61210
Index 0 12900
Other 0 3091
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
———— —— ————– —————
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 30-JUN-14
VALIDATE ARCHIVELOG ALL:
RMAN> validate archivelog all;
Starting validate at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of archived log
channel ORA_DISK_1: specifying archived log(s) for validation
input archived log thread=1 sequence=10 RECID=7 STAMP=851200648
input archived log thread=1 sequence=11 RECID=8 STAMP=851201836
input archived log thread=1 sequence=12 RECID=9 STAMP=851201936
input archived log thread=1 sequence=13 RECID=10 STAMP=851202206
input archived log thread=1 sequence=14 RECID=11 STAMP=851202266
input archived log thread=1 sequence=15 RECID=12 STAMP=851205645
input archived log thread=1 sequence=16 RECID=13 STAMP=851599672
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Archived Logs
=====================
Thrd Seq Status Blocks Failing Blocks Examined Name
—- ——- —— ————– ————— —————
1 10 OK 0 11946 /arch/proddb/1_10_848660904.dbf
1 11 OK 0 6023 /arch/proddb/1_11_848660904.dbf
1 12 OK 0 448 /arch/proddb/1_12_848660904.dbf
1 13 OK 0 2086 /arch/proddb/1_13_848660904.dbf
1 14 OK 0 411 /arch/proddb/1_14_848660904.dbf
1 15 OK 0 77739 /arch/proddb/1_15_848660904.dbf
1 16 OK 0 41867 /arch/proddb/1_16_848660904.dbf
Finished validate at 30-JUN-14
RMAN> restore archivelog all validate;
Starting restore at 30-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: scanning archived log /arch/proddb/1_10_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_11_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_12_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_13_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_14_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_15_848660904.dbf
channel ORA_DISK_1: scanning archived log /arch/proddb/1_16_848660904.dbf
Finished restore at 30-JUN-14
As mentioned earlier, while validating the archivelog, we should first check for the existence of the archivelog files in the Disk or Tape which have been generated since the last FULL or LEVEL 0 backup.
Else it will end up with error like RMAN-03002, RMAN-06026 and RMAN-06025.
Now I moved or renamed one of the archivelog file as below.,
[oracle@localhost proddb]$ ls -l
total 70276
-rw-r—– 1 oracle oinstall 6116864 Jun 25 20:37 1_10_848660904.dbf
-rw-r—– 1 oracle oinstall 3084288 Jun 25 20:57 1_11_848660904.dbf
-rw-r—– 1 oracle oinstall 229888 Jun 25 20:58 1_12_848660904.dbf
-rw-r—– 1 oracle oinstall 1068544 Jun 25 21:03 1_13_848660904.dbf
-rw-r—– 1 oracle oinstall 210944 Jun 25 21:04 1_14_848660904.dbf
-rw-r—– 1 oracle oinstall 39802880 Jun 25 22:00 1_15_848660904.dbf
-rw-r—– 1 oracle oinstall 21436416 Jun 30 11:27 1_16_848660904.dbf
[oracle@localhost proddb]$
[oracle@localhost proddb]$
[oracle@localhost proddb]$ mv 1_16_848660904.dbf 1_16_848660904.dbf_old
[oracle@localhost proddb]$ ls -l
total 70276
-rw-r—– 1 oracle oinstall 6116864 Jun 25 20:37 1_10_848660904.dbf
-rw-r—– 1 oracle oinstall 3084288 Jun 25 20:57 1_11_848660904.dbf
-rw-r—– 1 oracle oinstall 229888 Jun 25 20:58 1_12_848660904.dbf
-rw-r—– 1 oracle oinstall 1068544 Jun 25 21:03 1_13_848660904.dbf
-rw-r—– 1 oracle oinstall 210944 Jun 25 21:04 1_14_848660904.dbf
-rw-r—– 1 oracle oinstall 39802880 Jun 25 22:00 1_15_848660904.dbf
-rw-r—– 1 oracle oinstall 21436416 Jun 30 11:27 1_16_848660904.dbf_old
[oracle@localhost proddb]$
RMAN> restore archivelog all validate;
Starting restore at 30-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/30/2014 12:00:20
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 16 and starting SCN of 1134214 found to restore
RMAN> validate archivelog all;
Starting validate at 30-JUN-14
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 06/30/2014 12:00:47
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /arch/proddb/1_16_848660904.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
From the above example we can clearly see the failure of the validation of the archivelog.
This RESTORE VALIDATE feature can be safely run at anytime when needed. Because it does not affects an open database even if you simply forget to enter the keyword VALIDATE like below.,
RMAN > restore database;
An error similar to the one below will be received if you try to overwrite files of an open database.
RMAN-03002: failure of restore command at 06/30/2014 12:04:53
ORA-19870: error while restoring backup piece /rman/bkup_0kpbok52_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
RMAN will never restore if the database is in the OPEN State.
Loading ...
Total Users : 1822635
Very nice article Veera …