Rman Validate commands

 

- The “Validate” commands used to confirm the all database files exist, are in their correct location, and are free of physical corruption.

- It’s used to check   the “logical block corruption”

- It used to do determine Rman backup restore

  1)Validate ... Database

  2)Backup...Validate

  3)Restore...Validate

 

Note: if we not mentioned check logical by default it ll check only physical corruption

To Validate Database :

  To find only Physical corruption:

RMAN> validate database;


Starting validate at 08-MAY-17

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 validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB

F

input datafile file number=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1

.DBF

input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.D

BF

input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.D

BF

input datafile file number=00005 name=D:\REC_CATALOG\REC_CATALOG.DBF

input datafile file number=00006 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\VR.DBF

channel ORA_DISK_1: validation complete, elapsed time: 00:01:05

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

1    OK     0              12337        47360           4981512

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              25000

  Index      0              6938

  Other      0              3085


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

2    OK     0              26238        85760           4981480

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              21075

  Index      0              13184

  Other      0              25263


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

3    OK     0              935          55040           4981512

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              0

  Index      0              0

  Other      0              54105


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              27820        417280          3386468

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              388230

  Index      0              53

  Other      0              1177


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

5    OK     0              17813        19072           2100260

  File Name: D:\REC_CATALOG\REC_CATALOG.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              476

  Index      0              96

  Other      0              687


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

6    OK     0              6264         6400            3415143

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\VR.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              0

  Index      0              0

  Other      0              136


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              616

Finished validate at 08-MAY-17


   To find Physical and logical corruption:

    

RMAN> validate check logical database;

Starting validate at 09-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB
F
input datafile file number=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1
.DBF
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.D
BF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.D
BF
input datafile file number=00005 name=D:\REC_CATALOG\REC_CATALOG.DBF
input datafile file number=00006 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\VR.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:05
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12337        47360           5012068
  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              25000
  Index      0              6938
  Other      0              3085

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              26238        85760           5012068
  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              21083
  Index      0              13179
  Other      0              25260

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              935          55040           5012068
  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              54105

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              27820        417280          3386468
  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              388230
  Index      0              53
  Other      0              1177

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              17813        19072           2100260
  File Name: D:\REC_CATALOG\REC_CATALOG.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              476
  Index      0              96
  Other      0              687

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              6264         6400            3415143
  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\VR.DBF
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              136

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              616
Finished validate at 09-MAY-17

 

To Backup Validate:

This command will helps you to validate all database files and archived redo logs from physical and logical corruptions

 

To find only Physical corruption:

RMAN>Backup  validate database plus archivelog;

To find Physical and logical corruption:

RMAN> backup validate check logical database archivelog all;

 

Note: we can see the  same output  during the backup also but here  no backup was created

To validate Tablespace physical and logical corruption:


RMAN> validate tablespace users;


Starting validate at 08-MAY-17

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=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB

F

channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              27820        417280          3386468

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              388230

  Index      0              53

  Other      0              1177

 

RMAN> validate check logical tablespace users;


Starting validate at 08-MAY-17

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=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB

F

channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles

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

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              27820        417280          3386468

  File Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              388230

  Index      0              53

  Other      0              1177


Finished validate at 08-MAY-17


 

To  validate backupset  physical and logical corruption:

RMAN> validate check logical backupset 93;


Starting validate at 08-MAY-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece C:\ORACLEXE\APP\ORACLE\PRODUCT\11.

2.0\SERVER\DATABASE\DH_3GS38CF7_1_1_DHONI_942944743_112_1

channel ORA_DISK_1: piece handle=C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DA

TABASE\DH_3GS38CF7_1_1_DHONI_942944743_112_1 tag=INCRE_CULMUL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

Finished validate at 08-MAY-17


 

 

?

RMAN> validate backupset 93;


Starting validate at 08-MAY-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set

channel ORA_DISK_1: reading from backup piece C:\ORACLEXE\APP\ORACLE\PRODUCT\11.

2.0\SERVER\DATABASE\DH_3GS38CF7_1_1_DHONI_942944743_112_1

channel ORA_DISK_1: piece handle=C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DA

TABASE\DH_3GS38CF7_1_1_DHONI_942944743_112_1 tag=INCRE_CULMUL

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

Finished validate at 08-MAY-17

?

Note: to view backupsert use list backup commands

To Restore Validate:

RMAN> RESTORE DATABASE VALIDATE;


RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

 

Note: In Prior 11g validate command used for  only backup related files but oracle 11g onwards we can use the validate commands for backupset,datafiles,restore and tablespace.

 

Checksum and Block Corruption

 

What is checksum?

- A number is calculated by database  from all the bytes stored in a data or redo block.

 

- If we enabled  this parameter “DB_BLOCK_CHECKSUM”  our database will calculates the checksum  for every data file or redo log blocks and its stores on the block header when  writing to disk

 

What is corruption block?

- A corruption block is defined as  the block has been changed so it’s different from what oracle expects to find.

- It can happens because of this reasons

   * Faulty disks and disk controllers

   * Faulty memory

   * Oracle Database software defects

SQL> show parameter db_block_checksum


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_checksum                    string      TYPICAL

Note: By default it is in “typical” Then the database calculates the checksum for each block to normal operations and stores on the block header before the block was written into disk, In later the database  reads the block from disk the checksum will cross the stored value in disk ,If it’s mismatch it called as “Block corruption”

 

Benefits of Validate:


 - It used to check the database files, backup, tablespace and restore for  physical and logical corruption


 - Ensures the incremental backup are applied into correct manner


 - Block access to  datafiles  when they are recovering or restore


 - It permits only one restore operation for each datafile at a time

 

 

Source: Oracle  Website