Thursday, February 21, 2008

11G Example "VALIDATE" command-line on RMAN

In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes this operation trivial by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output that is partially shown below:

$ /oracle/11g/bin/rman target=/

Recovery Manager: Release 11.1.0.6.0 - Production on Thu Feb 21 11:46:09 2008

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

connected to target database: TESTDB (DBID=2420756419)

RMAN> validate database;

Starting validate at 21-FEB-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=+DATA/testdb/datafile/sysaux.265.637428335
input datafile file number=00001 name=+DATA/testdb/datafile/system.264.637428313
input datafile file number=00003 name=+DATA/testdb/datafile/undotbs1.266.637428341
input datafile file number=00005 name=+DATA/testdb/datafile/develop.277.637516907
input datafile file number=00004 name=+DATA/testdb/datafile/users.268.637428369
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 12321 96000 16346495
File Name: +DATA/testdb/datafile/system.264.637428313
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 68870
Index 0 12086
Other 0 2723

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 29584 117152 16346530
File Name: +DATA/testdb/datafile/sysaux.265.637428335
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 27252
Index 0 30144
Other 0 30172

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 32 24960 16346530
File Name: +DATA/testdb/datafile/undotbs1.266.637428341
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 24928

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 402 1440 13163571
File Name: +DATA/testdb/datafile/users.268.637428369
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 808
Index 0 131
Other 0 99

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 2137 3840 16198246
File Name: +DATA/testdb/datafile/develop.277.637516907
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1284
Index 0 183
Other 0 236

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 624
Finished validate at 21-FEB-08

You can also validate a specific tablespace:

RMAN> validate tablespace users;

Starting validate at 21-FEB-08
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=+DATA/testdb/datafile/users.268.637428369
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 402 1440 13163571
File Name: +DATA/testdb/datafile/users.268.637428369
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 808
Index 0 131
Other 0 99

Finished validate at 21-FEB-08

Or, datafile:

RMAN> validate datafile 1;

Starting validate at 21-FEB-08
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/testdb/datafile/system.264.637428313
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12316 96000 16363368
File Name: +DATA/testdb/datafile/system.264.637428313
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 68875
Index 0 12086
Other 0 2723

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 624
Finished validate at 21-FEB-08

Or, even a block in a datafile:

RMAN> validate datafile 1 block 10;

Starting validate at 21-FEB-08
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/testdb/datafile/system.264.637428313
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 0 1 3987690
File Name: +DATA/testdb/datafile/system.264.637428313
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 1

Finished validate at 21-FEB-08
..............................................................................

No comments: