It helps us to sleep better at night knowing that the database is healthy and has no bad blocks. But how can we ensure that ? Bad blocks show themselves only when they are accessed so we want to identify them early and hopefully repair them using simple commands before the users get an error. The tool dbverify can do the job but it might be a little inconvenient to use because it requires writing a script file contaning all datafiles and a lot of parameters. The output also needs scanning and interpretation.
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:
C:\Users\Administrator> rman target sys/xxxx
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 31 15:03:47 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PIKE (DBID=1619611654)
RMAN> validate database;
Starting validate at 31-AUG-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00005 name=D:\APP\ORADATA\PIKE\CC_DICT_01.DBF
input datafile file number=00006 name=D:\APP\ORADATA\PIKE\CC_PIKE_01.DBF
input datafile file number=00007 name=D:\APP\ORADATA\PIKE\CC_PIKE_02.DBF
input datafile file number=00008 name=D:\APP\ORADATA\PIKE\CC_PIKE_03.DBF
input datafile file number=00009 name=D:\APP\ORADATA\PIKE\CC_PIKE_04.DBF
input datafile file number=00010 name=D:\APP\ORADATA\PIKE\CC_PIKE_05.DBF
input datafile file number=00001 name=D:\APP\ORADATA\PIKE\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\PIKE\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\ORADATA\PIKE\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\ORADATA\PIKE\USERS01.DBF
channel ORA_DISK_1: validation complete, elapsed time: 00:01:45
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 14358 92192 3135230
File Name: D:\APP\ORADATA\PIKE\SYSTEM01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 60862
Index 0 13160
Other 0 3780
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 19066 79368 3135229
File Name: D:\APP\ORADATA\PIKE\SYSAUX01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 19106
Index 0 14603
Other 0 26585
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 18560 3135230
File Name: D:\APP\ORADATA\PIKE\UNDOTBS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 18559
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 481 640 935664
File Name: D:\APP\ORADATA\PIKE\USERS01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 15
Index 0 2
Other 0 142
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 248051 256000 1325211
File Name: D:\APP\ORADATA\PIKE\CC_DICT_01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 1074
Index 0 1113
Other 0 5762
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 119022 256000 3099996
File Name: D:\APP\ORADATA\PIKE\CC_PIKE_01.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 93382
Index 0 29835
Other 0 13761
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 124364 256000 3099672
File Name: D:\APP\ORADATA\PIKE\CC_PIKE_02.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 89229
Index 0 28893
Other 0 13514
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 128858 256000 3099896
File Name: D:\APP\ORADATA\PIKE\CC_PIKE_03.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 81211
Index 0 33860
Other 0 12071
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 68787 256000 3099998
File Name: D:\APP\ORADATA\PIKE\CC_PIKE_04.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 137858
Index 0 32218
Other 0 17137
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 OK 0 97469 256000 3099998
File Name: D:\APP\ORADATA\PIKE\CC_PIKE_05.DBF
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 115137
Index 0 30373
Other 0 13021
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 614
Finished validate at 31-AUG-11
We can also validate a specific tablespace:
RMAN> validate tablespace users;
Or, datafile:
RMAN> validate datafile 1;
Or, even a block in a datafile:
RMAN> validate datafile 4 block 56;
The VALIDATE command extends much beyond datafiles however. we can validate spfile, controlfilecopy, recovery files, Flash Recovery Area, and so on.
Enjoy :-)
No comments:
Post a Comment