Chinaunix首页 | 论坛 | 博客
  • 博客访问: 95742
  • 博文数量: 5
  • 博客积分: 194
  • 博客等级: 入伍新兵
  • 技术积分: 135
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-14 11:00
文章分类
文章存档

2012年(5)

我的朋友

分类: Oracle

2012-04-05 15:43:05

数据库健康检查:
 
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
阅读(5169) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~