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