Administration
Validate online physical and logical consistency of the Oracle Database
User Rating: / 3
- Details
-
Parent Category: Articles
-
Created on Wednesday, 24 March 2010 22:44
-
Last Updated on Thursday, 03 October 2013 11:50
-
Published on Wednesday, 24 March 2010 22:44
-
Written by Guy Lambregts
-
Hits: 13402
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
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