in Oracle

RMAN — 10 : VALIDATE

There are two different VALIDATE commands for Backups. (These are different from RESTORE VALIDATE which I’d blogged about earlier in 10.2 here)

The first is BACKUP VALIDATE which is useful to validate Datafiles to check for corruption.

The second is VALIDATE which can be used to check BackupSets. (Although it, too, can be run against the DATABASE)

Here I use the first form to check datafiles without actually creating a BackupSet :

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

Starting backup at 13-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35c9q_.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cd7_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cgr_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cht_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cj2_.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:26:18
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 14288 107649 14218763
File Name: /home/oracle/app/oracle/oradata/orcl/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 73392
Index 0 16290
Other 0 3678

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 15266 161256 14218883
File Name: /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 40232
Index 0 22741
Other 0 82913

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 21769 14217218
File Name: /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 21759

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 5349 35154 14211780
File Name: /home/oracle/app/oracle/oradata/orcl/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 10430
Index 0 2188
Other 0 17073

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1580 10500 5843748
File Name: /home/oracle/app/oracle/oradata/orcl/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 3947
Index 0 1110
Other 0 3859

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 2 12801 14126777
File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35c9q_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6730
Index 0 0
Other 0 6068

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 2 12801 14085691
File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cd7_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6738
Index 0 0
Other 0 6060

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 2 12801 14126777
File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cgr_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6738
Index 0 0
Other 0 6060

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 2 12801 14085691
File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cht_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6739
Index 0 0
Other 0 6059

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 OK 0 277 896 13902238
File Name: /home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 154
Index 0 92
Other 0 373

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11 OK 0 371 12801 14126777
File Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bwk35cj2_.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 7263
Index 0 0
Other 0 5166

channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK 0 628
Finished backup at 13-SEP-15

RMAN>
RMAN> list backup completed after "trunc(sysdate)";

specification does not match any backup in the repository

RMAN>
RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 13 19:32:43 2015

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select * from v$database_block_corruption;

no rows selected

SYS>

The BACKUP VALIDATE command doesn’t actually create a BackupSet. It simulates running a backup but does the additional job of checking the database blocks for corruption. The CHECK LOGICAL additionally checks for Logical Corruption. The SQL query on V$DATABASE_BLOCK_CORRUPTION can be used after RMAN completes execution as it would be populated with information about the blocks found corrupt.

On the other hand, the VALIDATE command can be used to check BackupSets.

RMAN> validate backupset 278;

Starting validate at 13-SEP-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_09_06/o1_mf_nnndf_TAG20150906T212547_byrhpx32_.bkp tag=TAG20150906T212547
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:06:57
Finished validate at 13-SEP-15

RMAN>

SYS>select * from v$backup_corruption;

no rows selected

SYS>

As with the BACKUP VALIDATE not actually creating a BackupSet, the VALIDATE BACKUPSET doesn’t actually restore a BackupSet (one or more BackupPieces from it) — thus the messages “restored backup piece 1” is misleading.

.
.
.