RMAN RESTORE VALIDATE – A Proactive Health Check

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.

 

 

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *