Wednesday, August 31, 2011

Proactive Health Checks using Validate Command

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: