RMAN – Check Oracle Database Consistency

What is RMAN?

RMAN stands for Oracle recovery Manager.
RMAN is a common term, famous among Oracle DBA/Basis consultants to perform admin tasks like:

• Verifying database, tablespaces, datafiles
• Performing restore, recovery & backup of an oracle database etc.

Today we will discuss about how we can check the consistency of database objects with Oracle recovery manager.

Oracle Recovery manager can be used to check consistency of below objects:

• Datafiles
• Redo logs
• Archived logs
• LOB data & UNDO segments (within datafile)
• Oracle parameter File.

Let us discuss each object seperately.

Check The Datafiles

In Oracle database, data is physically stored in Datafiles and logically stored in tablespaces.
From the operating system, login to RMAN using rman-nocatalog.

RMAN> connect target /
RMAN> validate datafile <file1>,<file2>,<file3>;

Alternatively we can also use below command:

RMAN> connect target /
RMAN> backup check logical validate (<file1>,<file2>,<file3>);

Check the consistency of single table:

From the operating system, login to RMAN using rman-nocatalog.

RMAN> connect target /
RMAN> backup check logical validate (tablespace 'PSAPPRD' );

OR use below syntax:

RMAN> connect target /
RMAN> validate tablespace PSAPPRD;

Here PSAPPRD= tablespace name

Check Single block within a data file:

Block is the smallest element in oracle database, also known as logical blocks,oracle blocks or pages which is used to store the data.

Use below command to check the consistency of single bloack within data file.

RMAN> connect target /
RMAN> validate <datafile n> <block n>; 

Check consistency of redo log file:

RMAN> connect target /
RMAN> validate archivelog sequence <n>;

Check Database for physical corruption:

We can use below command to check database for physical corruption :

RMAN> connect target /
RMAN> backup validate database; 

Check Database For Logical Corruption:

Use below command to check the logical corruption in the database.

RMAN> connect target /
RMAN> backup validate check logical database;

We can evaluate the output by RAMN using below ways:

Using BRBACKUP

Here, system automatically prints output and saves it in the brbackup log file.
Use below command to evaluate the list of corrupt blocks via BRCONNECT:

brconnect -u / -c -f lscorr

View V$DATABASE_BLOCK_CORRUPTION

Oracle recovery manager stores the information about identified corrupt blocks in control file of the database.
We can use the view: V$DATABASE_BLOCK_CORRUPTION to access the information.

We can use query like below to identify the objects containing a corrupt block. Block corruption will be visible in the view : V$DATABASE_BLOCK_CORRUPTION

COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30

SELECT DISTINCT owner, segment_name
FROM   v$database_block_corruption dbc
       JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;