11g oracle controlfile 查询,Oracle 11g 的健康监视(Health Monitor)
数据库健康检查：
oracle 11g提供一个新的监控组件叫健康监视。它检查数据库组件比如文件系统、内存、事务完整性等方面。
当数据库有一个严重的错误时，数据库会自动运行Health Monitor诊断问题。这会产生健康监视日志放在ADR中。
data recovery advisor能够访问这些数据以产生报告或这修正问题。你也可以手工运行健康监视。
健康检查的类型：
可是执行不同种类的健康检查，包括检查数据文件坏块，redo检查，验证数据字典完整性，例如tab$和col$。视图v$hm_check描述了所有类
型的健康检查：
SQL> select name,description from v$hm_check;
NAME DESCRIPTION
------------------------------ -------------------------------------------------
HM Test Check Check for HM Functionality
DB Structure Integrity Check Checks integrity of all database files
Data Block Integrity Check Checks integrity of a datafile block
Redo Integrity Check Checks integrity of redo log content
Logical Block Check Checks logical content of a block
Transaction Integrity Check Checks a transaction for corruptions
Undo Segment Integrity Check Checks integrity of an undo segment
All Control Files Check Checks all control files in the database
CF Member Check Checks a multiplexed copy of the control file
All Datafiles Check Check for all datafiles in the database
NAME DESCRIPTION
------------------------------ -------------------------------------------------
Single Datafile Check Checks a datafile
Log Group Check Checks all members of a log group
Log Group Member Check Checks a particular member of a log group
Archived Log Check Checks an archived log
Redo Revalidation Check Checks redo log content
IO Revalidation Check Checks file accessability
Block IO Revalidation Check Checks file accessability
Txn Revalidation Check Revalidate corrupted txn
Failure Simulation Check Creates dummy failures
Dictionary Integrity Check Checks dictionary integrity
21 rows selected.
你可以显示输入参数，通过视图v$check_param.
可以以两种模式运行健康检查：
在线模式和离线模式。
虽然数据库会因为某些条件触发自动进行健康检查，但是有时候你最好进行主动的健康检查，比如当数据文件出现坏块时，如果用户不防问
坏块数据，那么不会触发这种健康检查，因此手工主动检查还是很有必要的。
我们看一下如何进行手工健康检查：
手工检查需要执行包dbms_hm中的run_check过程来执行的。
看一下过程run_check结构：
PROCEDURE RUN_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CHECK_NAME VARCHAR2 IN
RUN_NAME VARCHAR2 IN DEFAULT
TIMEOUT NUMBER IN DEFAULT
INPUT_PARAMS VARCHAR2 IN DEFAULT
.check_name:数据库检查名，这是一个强制参数必须被指定。可以在视图v$hm_check中查询到该参数的值。共21个值。
.run_name:可选参数，指定一个检查名。
.timeout:可选参数，可以设置老化时间。
.params:输入参数，用于控制检查的执行。可以在视图v$hm_check_param视图中查看。
以下是一个检查的例子：
SQL> exec dbms_hm.run_check('DB Structure Integrity Check','testrun1');
PL/SQL procedure successfully completed.
SQL>
这个健康检查会存储报告到ADR数据库实例的home目录中。通过adrci的show hm_run命令可以显示这些信息：
adrci> show hm_run
ADR Home = /home/oracle/diag/rdbms/rac/rac:
*************************************************************************
**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 1
RUN_NAME HM_RUN_1
CHECK_NAME DB Structure Integrity Check
NAME_ID 2
MODE 2
START_TIME 2012-03-29 20:56:28.225787 +08:00
RESUME_TIME END_TIME 2012-03-29 20:56:32.883387 +08:00
MODIFIED_TIME 2012-03-29 20:56:32.883387 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE
**********************************************************
HM RUN RECORD 2
**********************************************************
RUN_ID 81
RUN_NAME testrun1
CHECK_NAME DB Structure Integrity Check
NAME_ID 2
MODE 0
START_TIME 2012-04-05 14:06:49.402438 +08:00
RESUME_TIME END_TIME 2012-04-05 14:06:49.635498 +08:00
MODIFIED_TIME 2012-04-05 14:06:49.635498 +08:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE 2 rows fetched
也可以显示详细结果：
adrci> show report hm_run testrun1
HM Report: HM_RUN_7
Data Block Check
Multiple corrupted blocks
datafile 2 contains corrupt blocks
tablespace SYSAUX is unavailable
block 66578 in datafile 2 is corrupt
adrci>
上面的报告显示数据文件2上有坏块，该文件属于sysaux表空间。
所有的健康检查报告存储在v$hm_run视图中，你也可以通过dbms_hm包来获取报告：
SQL> var v_output clob
SQL> begin
2 :v_output := dbms_hm.get_run_report ('testrun1');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> set long 1000000
SQL> set pages 0
SQL> print :v_output
Basic Run Information
Run Name : testrun1
Run Id : 81
Check Name : DB Structure Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2012-04-05 14:06:49.402438 +08:00
End Time : 2012-04-05 14:06:49.635498 +08:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
Input Paramters for the Run
Run Findings And Recommendations
也可以直接在视图中查询：
SQL> select name,check_name,run_mode,status from v$hm_run;
NAME CHECK_NAME RUN_MODE STATUS
---------- -------------------------------- -------- -----------
testrun1 DB Structure Integrity Check MANUAL COMPLETED
HM_RUN_1 DB Structure Integrity Check REACTIVE COMPLETED
上面的查询中run_mode列指明是不是某一个健康检查是手工还是反应式触发。
使用rman验证命令手工检查：
11g之间的版本中，可使用backup ... validate命令验证备份。
在oracle 11g里，一个新的命令validate，功能更加强大：
backup ... validate只能用于数据库级别。但是validate命令能执行相同的工作在备份集，表空间，数据文件，甚至是数据块级别。还可以检查闪回区的完整性。
RMAN> validate database;
Starting validate at 05-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 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=/home/oracle/oradata/rac/system01.dbf
input datafile file number=00002 name=/home/oracle/oradata/rac/sysaux01.dbf
input datafile file number=00005 name=/home/oracle/oradata/rac/example01.dbf
input datafile file number=00003 name=/home/oracle/oradata/rac/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/oradata/rac/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:03:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 12205 89600 773677
File Name: /home/oracle/oradata/rac/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 63231
Index 0 11306
Other 0 2858
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 28709 77112 773675
File Name: /home/oracle/oradata/rac/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 10934
Index 0 9084
Other 0 28385
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 56 10240 773677
File Name: /home/oracle/oradata/rac/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 10184
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 383 640 654038
File Name: /home/oracle/oradata/rac/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 33
Other 0 133
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1711 12800 663146
File Name: /home/oracle/oradata/rac/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 4455
Index 0 1271
Other 0 5363
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:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 05-APR-12