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:


[oracle@primaryDB ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Sep 14 11:08:22 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORAHDFC1 (DBID=227875232)

RMAN> validate database;

Starting validate at 14-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u04/ORAHDFC1/system01.dbf
input datafile file number=00002 name=/u04/ORAHDFC1/sysaux01.dbf
input datafile file number=00004 name=/u04/ORAHDFC1/users01.dbf
input datafile file number=00006 name=/u04/ORAHDFC1/undo2.dbf
input datafile file number=00005 name=/u04/ORAHDFC1/newtbs01.dbf
input datafile file number=00003 name=/u04/ORAHDFC1/newtbs02.bf

channel ORA_DISK_1: validation complete, elapsed time: 00:01:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              15154        131081          2622467
  File Name: /u04/ORAHDFC1/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              64195
  Index      0              13451
  Other      0              38272

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              21657        92166           2622479
  File Name: /u04/ORAHDFC1/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              23817
  Index      0              20037
  Other      0              26649

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            257             1549234
  File Name: /u04/ORAHDFC1/newtbs02.bf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              255

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              21           14403           2430429
  File Name: /u04/ORAHDFC1/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              13188
  Index      0              9
  Other      0              1182

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              1            513             956797
  File Name: /u04/ORAHDFC1/newtbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              511

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              1            2560            2622479
  File Name: /u04/ORAHDFC1/undo2.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              2559

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              600

Finished validate at 14-SEP-19



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   :-) 

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more