04 | 11 | 2024
Latest Articles
Popular Articles

Administration

Validate online physical and logical consistency of the Oracle Database

User Rating:  / 3
PoorBest 

Validate online physical and logical consistency of the Oracle Database

 

See also Detect block corruption 

Oracle 11G comes with some additional checks for integrity checking. Can be done at database, tablespace, datafile, controlfile and block level.

Some commands are 

RMAN > validate database
RMAN > validate check logical database 
RMAN > validate tablespace users
RMAN > validate check logical tablespace users
RMAN > validate archivelog all
RMAN > validate check logical current controlfile
RMAN > validate check spfile
RMAN > validate check logical datafile n block x 
 

Some examples

RMAN> validate check logical current controlfile;

Starting validate at 03-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              676

Finished validate at 03-OCT-13

RMAN> validate check logical tablespace users;

Starting validate at 03-OCT-13
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=/u01/oradata/SILVER/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              21603        336384          41455048

File Name: /u01/oradata/SILVER/users01.dbf

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              190051
Index      0              121930
Other      0                2800

Finished validate at 03-OCT-13

 

RMAN> validate check logical datafile 1 block 100;
 
Starting validate at 03-OCT-13
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=/u01/oradata/SILVER/system01.dbf
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               207
 
File Name: /u01/oradata/SILVER/system01.dbf
 
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0              0
Index      0              0
Other      0              1
 
Finished validate at 03-OCT-13
 
 
You can also benefit from RMAN parallel channel processing
 

RMAN> run {
2> allocate channel ch1 device type DISK;
3> allocate channel ch2 device type DISK;
4> validate check logical tablespace USERS;
5> }

allocated channel: ch1
channel ch1: SID=490 device type=DISK

allocated channel: ch2
channel ch2: SID=419 device type=DISK

 
 

The Database Health Monitor has been introduced in Oracle 11G and monitors the health of the database ( sounds logic ) and can run

 

1. Reactively ( automatically when a critical error is detected )

2. Manually    ( by the DBA )

 

If the DBA decides to run it manually the below options can be chosen

1. DB Structure Integrity Check
2. Data Block Integrity Check
3. Redo Integrity Check
4. Transaction Integrity Check
5. Undo Segment Integrity Check
6. Dictionary Integrity Check

 

When it is automatically launched in response to a critical error its output can also be retrieved using the Oracle Enterprise Manager Support Workbench GUI

 

Examples

 
SQL> set long 20000
SQL> set numwidth 16
SQL> set lines 200
SQL> set pages 1000
SQL> begin
2 dbms_hm.run_check(check_name => 'DB Structure Integrity Check', run_name => 'DB_INTEGRITY_RUN');
3 end;
4 /
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_hm.get_run_report('DB_INTEGRITY_RUN') from dual;
 
DBMS_HM.GET_RUN_REPORT('DB_INTEGRITY_RUN')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : DB_INTEGRITY_RUN
Run Id : 105001
Check Name : DB Structure Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-10-03 10:32:13.236664 +02:00
End Time : 2013-10-03 10:32:13.414989 +02:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
Input Paramters for the Run
Run Findings And Recommendations

 
SQL> begin
2 dbms_hm.run_check(check_name => 'Redo Integrity Check', run_name => 'DB_REDO_CHECK');
3 end;
4 /
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_hm.get_run_report('DB_REDO_CHECK') from dual;
 
DBMS_HM.GET_RUN_REPORT('DB_REDO_CHECK')
--------------------------------------------------------------------------------
Basic Run Information
Run Name : DB_REDO_CHECK
Run Id : 105021
Check Name : Redo Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2013-10-03 10:34:44.366433 +02:00
End Time : 2013-10-03 10:34:46.925067 +02:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
Input Paramters for the Run
SCN_TEXT=0
 
Run Findings And Recommendations

 

We can also retrieve the output from health monitor using the Automatic Diagnostic Repository

 

adrci> show HM_RUN
 
ADR Home = /u01/app/oracle/diag/rdbms/silver/SILVER:
*************************************************************************
**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 105001
RUN_NAME DB_INTEGRITY_RUN
CHECK_NAME DB Structure Integrity Check
NAME_ID 2
MODE 0
START_TIME 2013-10-03 10:32:13.236664 +02:00
RESUME_TIME <NULL>
END_TIME 2013-10-03 10:32:13.414989 +02:00
MODIFIED_TIME 2013-10-03 10:32:46.202354 +02:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /u01/app/oracle/diag/rdbms/silver/SILVER/hm/HMREPORT_DB_INTEGRITY_RUN.hm
 
**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID 105021
RUN_NAME DB_REDO_CHECK
CHECK_NAME Redo Integrity Check
NAME_ID 4
MODE 0
START_TIME 2013-10-03 10:34:44.366433 +02:00
RESUME_TIME <NULL>
END_TIME 2013-10-03 10:34:46.925067 +02:00
MODIFIED_TIME 2013-10-03 10:35:04.381369 +02:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /u01/app/oracle/diag/rdbms/silver/SILVER/hm/HMREPORT_DB_REDO_CHECK.hm

2 rows fetched