转载

OCP课程50：管理II之诊断数据库

课程目标：

检测和修复数据库损坏
处理块损坏
配置ADR（Automatic Diagnostic Repository）
运行健康检查

1、数据恢复顾问

OCP课程50：管理II之诊断数据库

数据恢复顾问：

当遇到错误时，数据恢复顾问自动收集数据问题信息，检查分析问题。

数据问题有时会很严重，比如丢失日志文件导致不能启动数据库。有时不严重（比如数据文件块损坏），不需要关闭数据库或者不能启动实例。故数据恢复顾问可以用于以下2种情况：一是不能启动数据库（由于数据库文件丢失、不一致或者损坏），一是运行时文件损坏。

用户接口：

可以通过EM访问数据恢复顾问，从数据库实例主页开始有如下方式访问：

Availability tabbed page > Perform Recovery > Advise and Recover
Active Incidents link > on the Support Workbench “Problems” page: Checker Findings tabbed page > Launch Recovery Advisor
Database Instance Health > click the specific link, for example, ORA 1578 in the Incidents section > Support Workbench, Problems Detail page > Data Recovery Advisor
Database Instance Health > Related Links section: Support Workbench > Checker Findings tabbed page: Launch Recovery Advisor
Related Link: Advisor Central > Advisors tabbed page: Data Recovery Advisor
Related Link: Advisor Central > Checkers tabbed page: Details > Run Detail tabbed page: Launch Recovery Advisor

也可以通过RMAN访问：

rman target / nocatalog

rman> list failure all;

支持的数据库配置（11gR2）：

只支持单实例，不支持RAC。

不能使用备库的块或者文件去修复主库的问题，也不能使用数据恢复顾问去诊断和修复备库的问题，但数据恢复顾问会把切换到备库作为一个修复选项。

2、数据恢复顾问

OCP课程50：管理II之诊断数据库

在11g中使用数据恢复顾问进行自动诊断的流程如下：

（1）健康监测自动检查并记录问题到ADR。

（2）列出级别为critical或者high的问题。

（3）进行修复建议，包括自动和手动修复选项，并检查可行性。

（4）可以选择手动执行修复或者使用数据恢复顾问执行修复。

（5）使用VALIDATE命令进行检查。

3、数据问题

OCP课程50：管理II之诊断数据库

检查数据问题，评估数据库及其组件健康情况，诊断问题。

可以执行VALIDATE DATABASE命令主动检查问题，当数据库出现错误，会自动进行被动检查。

在EM，使用可用性>执行恢复，如果数据库关闭或者挂载状态而不能打开，点击“执行恢复”进行问题修复。

4、数据问题：示例

OCP课程50：管理II之诊断数据库

数据恢复顾问可以分析以上问题并提出修复建议。

5、数据恢复顾问：RMAN命令行接口

OCP课程50：管理II之诊断数据库

如果怀疑数据库出现问题，使用LIST FAILURE命令列出问题信息。

使用ADVISE FAILURE命令为问题列出推荐的修复建议，隐式关闭已经修复的问题。如果不加选项，默认动作是为记录在ADR中所有CRITICAL和HIGH级别错误提出建议。

在同一个会话中，使用ADVISE FAILURE命令后，使用REPAIR FAILURE命令进行修复。修复完成后，关闭问题。

使用CHANGE FAILURE命令修改问题优先级或者关闭问题。只能修改级别为HIGH或者LOW的问题优先级。问题修复后会隐式关闭，也可以显示关闭。

6、列出数据问题

OCP课程50：管理II之诊断数据库

LIST FAILURE命令列出问题。如果目标实例使用了恢复目录，可以在STARTED（NOMOUNT）模式使用，否则需要在MOUNTED模式使用。

LIST FAILURE命令不会去检查诊断新的问题，而是列出先前评估的结果。重复执行LIST FAILURE命令重新验证所有存在的问题。如果用户手动修复问题，或如果问题消失，则数据恢复顾问从LIST FAILURE输出中删除这些问题。语法描述如下：

failnum：要显示修复选项的问题数量
ALL：列出所有优先级的问题
CRITICAL：列出CRITICAL优先级和OPEN状态的问题。这些问题会导致整个数据库不可用（例如丢失控制文件）
HIGH：列出HIGH优先级和OPEN状态的问题，这些问题会导致数据库部分不可用或者不可恢复（例如丢失归档日志文件）
LOW：列出LOW优先级和OPEN状态的问题
CLOSED：只列出关闭的问题
EXCLUDE FAILURE：排除指定的问题
DETAIL：列出问题详细信息，例如文件中多个块损坏，会列出每一个块信息

例子：列出问题

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 HIGH OPEN 27-JAN-16 One or more non-system datafiles are missing

7、修复建议

OCP课程50：管理II之诊断数据库

ADVISE FAILURE命令为问题列出了推荐的修复选项，隐式关闭已经修复了的所有问题，显示输入问题的概要信息。

ADVISE FAILURE默认（不加选项）为记录在ADR中所有CRITICAL和HIGH优先级的问题提出建议。如果自上一次LAST FAILURE命令之后有新的问题记录到ADR，则该命令的输出会包含WARNING。

当数据恢复顾问生成一个自动修复选项，会生成一个脚本，显示RMAN将如何修复问题。

语法：

ADVISE FAILURE

[ ALL | CRITICAL | HIGH | LOW | failnum[,failnum,…] ]

[ EXCLUDE FAILURE failnum [,failnum,…] ]

例子：修复建议

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 HIGH OPEN 27-JAN-16 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/STONE/datafile/user01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Restore and recover datafile 4

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_277717931.hm

8、执行修复

OCP课程50：管理II之诊断数据库

REPAIR FAILURE命令需要在同一个RMAN会话中，ADVISE FAILURE命令之后使用。默认（不加选项）使用一个推荐的修复选项。如果没有推荐的修复选项，REPAIR FAILURE命令发出一个隐式的ADVISE FAILURE命令。

使用USING ADVISE OPTION interger，通过选项编号指定使用哪一个修复选项。

由于修复可能会需要很长时间进行大量修改，故需要对其进行确认。完成修复后，会关闭问题。

不能同时运行多个修复会话，但可以同时运行REPAIR ... PREVIEW。

PREVIEW：不执行修复而是显示修复脚本

NOPROMPT：不需要确认

例子：执行修复

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3201719897.hm

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_3201719897.hm

contents of repair script:

# restore and recover datafile

restore datafile 4;

recover datafile 4;

sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting restore at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_24/o1_mf_nnndf_TAG20160124T211323_cb9mhmmc_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_24/o1_mf_nnndf_TAG20160124T211323_cb9mhmmc_.bkp tag=TAG20160124T211323

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 27-JAN-16

Starting recover at 27-JAN-16

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_26/o1_mf_1_8_cbfqhwdr_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_27/o1_mf_1_9_cbj37c44_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_27/o1_mf_1_10_cbj9dox3_.arc

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=7

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_24/o1_mf_annnn_TAG20160124T211500_cb9mlnkc_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_24/o1_mf_annnn_TAG20160124T211500_cb9mlnkc_.bkp tag=TAG20160124T211500

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_27/o1_mf_1_7_cbjtpskc_.arc thread=1 sequence=7

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_27/o1_mf_1_7_cbjtpskc_.arc RECID=240 STAMP=902242777

archived log file name=/u01/app/oracle/fast_recovery_area/STONE/archivelog/2016_01_26/o1_mf_1_8_cbfqhwdr_.arc thread=1 sequence=8

media recovery complete, elapsed time: 00:00:02

Finished recover at 27-JAN-16

sql statement: alter database datafile 4 online

repair failure complete

Do you want to open the database (enter YES or NO)? yes

database opened

9、分类（和关闭）问题

OCP课程50：管理II之诊断数据库

CHANGE FAILURE命令用于修改问题优先级以及关闭问题。

语法：

CHANGE FAILURE

{ ALL | CRITICAL | HIGH | LOW | failnum[,failnum,…] }

[ EXCLUDE FAILURE failnum[,failnum,…] ]

{ PRIORITY {CRITICAL | HIGH | LOW} |

CLOSE } – change status of the failure(s) to closed

[ NOPROMPT ] – do not ask user for a confirmation

问题优先级只能从HIGH到LOW和从LOW到HIGH。不能修改为CRITICAL的优先级。（修改优先级从HIHG到LOW的情况之一是不想在LIST FAILURE命令的默认输出中看到该问题。例如块损坏为HIGH优先级，如果块位于一个很少使用的表空间，可以临时将其优先级修改为LOW。）

问题修复后会隐式关闭问题，也可以显示关闭问题。

该命令默认需要用户进行确认。

例子：更改问题优先级

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 HIGH OPEN 27-JAN-16 One or more non-system datafiles are missing

RMAN> change failure 3282 priority low;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 HIGH OPEN 27-JAN-16 One or more non-system datafiles are missing

Do you really want to change the above failures (enter YES or NO)? yes

changed 1 failures to LOW priority

RMAN> list failure;

no failures found that match specification

RMAN> list failure all;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 LOW OPEN 27-JAN-16 One or more non-system datafiles are missing

RMAN> change failure 3282 priority high;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 LOW OPEN 27-JAN-16 One or more non-system datafiles are missing

Do you really want to change the above failures (enter YES or NO)? yes

changed 1 failures to HIGH priority

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

3282 HIGH OPEN 27-JAN-16 One or more non-system datafiles are missing

10、数据恢复顾问视图

OCP课程50：管理II之诊断数据库

V$IR_FAILURE：列出所有的问题，包括关闭的，RMAN命令LIST FAILURE的结果

V$IR_MANUAL_CHECKLIST：列出需要手动操作的建议，RMAN命令ADVISE FAILURE的结果

V$IR_REPAIR：列出自动修复建议，RMAN命令ADVISE FAILURE的结果

V$IR_FAILURE_SET：是否只能手动修复

11、最佳实践：主动检查

OCP课程50：管理II之诊断数据库

对于非常重要的数据库，可能需要执行额外的前瞻性主动检查（在每天的业务低峰期间）。可以通过Health Monitor或者使用RMAN的VALIDATE命令进行定期的健康检查。在一般情况下，当被动检查检测到数据库组件中的问题时，可以对受影响的组件执行一个更完整的检查。

RMAN的VALIDATE DATABASE命令用于为数据库及其组件调用健康检查，扩展了现有的VALIDATE BACKUPSET命令，将显示验证过程中检测到的所有问题。如果检测到问题，则将其作为finding记录到ADR。可以使用LIST FAILURE命令来查看存储库中记录的所有问题。

VALIDATE命令支持单个备份集和数据块的验证。默认情况下，VALIDATE命令只检查物理损坏。可以指定CHECK LOGICAL检查逻辑损坏。

块损坏可分为块间（interblock）和块内（intrablock）损坏。块内损坏发生在块体本身，可以是物理的或逻辑的损坏。块间损坏发生在块之间，只能是逻辑损坏。VALIDATE命令只检查块内损坏。

12、什么是块损坏

OCP课程50：管理II之诊断数据库

一个已损坏的数据块是指块不是Oracle可以识别的格式或其内容内部不一致。通常情况下，损坏是由硬件故障或操作系统问题造成的。数据库将块损坏分为“逻辑损坏”或“介质损坏”。如果是逻辑损坏，则有一个Oracle内部错误。逻辑损坏的块在数据库检测到不一致后会被标记为损坏。如果是介质损坏，则块格式不正确；在被读取到磁盘后，该块中的信息没有任何意义。

通过恢复块或删除包含损坏块的数据库对象可以修复一个介质损坏块。如果介质损坏是由于硬件故障导致，则需要修复硬件故障。

13、块损坏：ORA-01578

OCP课程50：管理II之诊断数据库

通常ORA-01578错误是由硬件问题造成的。如果ORA-01578错误总是返回相同的参数，则最有可能是介质损坏块。

如果参数每次都发生变化，则可能会是硬件问题，应该进行内存和页空间检查以及I/O子系统。

注意：ORA-01578返回相对文件号，但随之而来的ORA-01110错误显示绝对文件号。

例子：模拟块损坏

SQL> create tablespace tstest datafile '/home/oracle/tstest.dbf' size 10m;

Tablespace created.

SQL> create table emptest tablespace tstest as select * from hr.employees where rownum<10;

Table created.

定位表对应的块。

SQL> select rowid,dbms_rowid.rowid_relative_fno(rowid) rel_fno,dbms_rowid.rowid_block_number(rowid) block from emptest;

ROWID REL_FNO BLOCK

------------------ ---------- ----------

AAAWO6AAIAAAACDAAA 8 131

AAAWO6AAIAAAACDAAB 8 131

AAAWO6AAIAAAACDAAC 8 131

AAAWO6AAIAAAACDAAD 8 131

AAAWO6AAIAAAACDAAE 8 131

AAAWO6AAIAAAACDAAF 8 131

AAAWO6AAIAAAACDAAG 8 131

AAAWO6AAIAAAACDAAH 8 131

AAAWO6AAIAAAACDAAI 8 131

验证是否有坏块，目前没有。

[oracle@oracletest ~]$ dbv userid=sys/123456 file=/home/oracle/tstest.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 27 16:30:20 2016

DBVERIFY - Verification starting : FILE = /home/oracle/tstest.dbf

DBVERIFY - Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 1

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 127

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1152

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 4154173 (0.4154173)

先进行数据库全备。

RMAN> backup database plus archivelog delete input;

RMAN标记坏块。

RMAN> recover datafile 8 block 131 clear;

Starting recover at 27-JAN-16

using channel ORA_DISK_1

Finished recover at 27-JAN-16

再次验证，发现坏块。

[oracle@oracletest ~]$ dbv userid=sys/123456 file=/home/oracle/tstest.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 27 16:34:57 2016

DBVERIFY - Verification starting : FILE = /home/oracle/tstest.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x02000083 (file 8, block 131)

Bad header found during dbv:

Data in bad block:

type: 6 format: 6 rdba: 0x02000094

last change scn: 0x0000.003f633d seq: 0x2 flg: 0x04

spare1: 0xed spare2: 0xf3 spare3: 0x0

consistency value in tail: 0x633d0602

check value in block header: 0xadcc

computed block checksum: 0xe7fa

DBVERIFY - Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 129

Total Pages Processed (Seg) : 1

Total Pages Failing (Seg) : 0

Total Pages Empty : 1149

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 4154177 (0.4154177)

查询报错。

SQL> select count(*) from emptest;

select count(*) from emptest

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 8, block # 131)

ORA-01110: data file 8: '/home/oracle/tstest.dbf'

14、如何处理块损坏

OCP课程50：管理II之诊断数据库

总是试图找出错误是否是永久性的。多次运行ANALYZE命令，如果可能的话，关机再启动，然后再试一次执行失败的操作。找出是否有更多的损坏。

硬件故障应立即解决。当遇到硬件问题，联系供应商进行检查和修复。

硬件故障的类型：

错误的硬件或固件
操作系统I/O或缓存问题
内存或分页问题

例子：运行ANALYZE命令分析表

SQL> analyze table emptest validate structure cascade;

analyze table emptest validate structure cascade

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 8, block # 131)

ORA-01110: data file 8: '/home/oracle/tstest.dbf'

15、设置参数检测损坏

OCP课程50：管理II之诊断数据库

使用db_ultra_safe参数方便管理，它会影响下列参数的默认值：

db_block_checking，是否进行数据块校验。可以防止内存和数据损坏。（默认值：FALSE，建议值：FULL）
db_block_checksum，在数据块写入到磁盘的时候，对数据块的缓存头部进行校验和。校验和协助检测磁盘，存储系统或I/O系统导致的损坏。（默认值：TYPICAL，建议值：TYPICAL）
db_lost_write_protect，失写检查。数据块失写发生在主库已经完成写入，但备库还未完成写入。（默认值：TYPICAL，建议值：TYPICAL）

可以显式设置这些参数，db_ultra_safe参数（11g新加入）仅仅修改这些参数的默认值。

16、设置参数检测损坏

OCP课程50：管理II之诊断数据库

根据要求，可以加强对块损坏的检查。启用db_ultra_safe参数（默认关闭）会增加系统开销。开销的量与每秒块改变数量相关，因此不容易量化。对于一个频繁更新的应用程序，会对CPU有10%-20%的影响。

当db_ultra_safe参数设置为data_only，db_block_checking参数被设置为MEDIUM。块内数据的检查是逻辑自包含的。内存中的块内容发生改变后执行基本的块头检查（例如，更新或插入命令后的磁盘读取或者RAC环境中的内部实例块传输）。这一级别的检查包括所有非索引组织表块的语义块检查。
当db_ultra_safe参数设置为data_and_index，db_block_checking参数设置为FULL。除了前面的检查，对索引块执行语义检查（即，该对象的子对象，可以在块损坏时进行删除和重建）。
当db_ultra_safe参数设置为data_only或data_and_index，db_block_checksum参数设置为FULL以及db_lost_write_protect参数设置为TYPICAL。

例子：查看参数

SQL> show parameter db_ultra

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_ultra_safe string OFF

SQL> show parameter db_block_check

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_checking string FALSE

db_block_checksum string TYPICAL

SQL> show parameter db_lost

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_lost_write_protect string NONE

SQL> alter system set db_ultra_safe=data_only scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

SQL> show parameter db_ultra

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_ultra_safe string DATA_ONLY

SQL> show parameter db_block_check

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_checking string MEDIUM

db_block_checksum string FULL

SQL> show parameter db_lost

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_lost_write_protect string TYPICAL

SQL> alter system set db_block_checking=full;

System altered.

SQL> show parameter db_block_check

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_checking string FULL

db_block_checksum string FULL

17、块介质恢复

OCP课程50：管理II之诊断数据库

大多数情况下，当首次遇到损坏的块，数据库标记块为介质损坏并写入到相关视图中，后续对块的读取都会失败。可以只对标记为损坏的块进行块恢复。使用RMAN命令RECOVER ... BLOCK进行块介质恢复。默认情况下，RMAN搜索闪回日志查找损坏块的可用拷贝，然后搜索全备或者0级增量备份查找可用块。找到后，还原并进行介质恢复。块介质恢复只能使用重做日志，不能使用增量备份。

V$DATABASE_BLOCK_CORRUPTION视图显示被RMAN命令、ANALYZE、dbv、SQL查询等标记为损坏的块。以下类型的损坏将会增加到这个视图：

物理/介质损坏：数据库不能识别块：校验和无效，块内都是0或者块头损坏。默认启用物理损坏检查。

逻辑损坏：块有有效的校验和，块头和块尾匹配，但内容不一致。块介质恢复不能修复逻辑块损坏。默认不启用逻辑损坏检查。可以通过BACKUP，RESTORE，RECOVER和VALIDATE命令指定CHECK LOGICAL选项启用逻辑损坏检查。

18、块介质恢复的前提条件

OCP课程50：管理II之诊断数据库

执行RECOVER ... BLOCK命令的前提条件：

目标数据库必须是归档模式，并使用当前控制文件打开或者挂载。
包含损坏块的数据文件的备份必须是全备或者0级增量备份，不能是代理拷贝。如果只有代理拷贝，需要还原到非默认位置以便让RMAN认为是数据文件的拷贝。
只能使用归档日志文件进行恢复。不能使用1级增量备份。
从闪回日志中搜索损坏块的可用拷贝需要启用闪回数据库，如果闪回日志有损坏块的老版本的完好的块，则RMAN会使用这些块加快恢复速度。

19、RECOVER...BLOCK命令

OCP课程50：管理II之诊断数据库

块恢复前，需要先定位到损坏的块，一般使用如下方法定位：

LIST FAILURE，VALIDATE或者BACKUP ... VALIDATE命令
V$DATABASE_BLOCK_CORRUPTION视图
标准输出的错误消息
告警日志和用户跟踪文件
ANALYZE TABLE和ANALYZE INDEX命令
DBVERIFY工具

例如在用户跟踪文件发现如下信息：

ORA-01578: ORACLE data block corrupted (file # 7, block # 3)

ORA-01110: data file 7: '/oracle/oradata/orcl/tools01.dbf'

ORA-01578: ORACLE data block corrupted (file # 2, block # 235)

ORA-01110: data file 2: '/oracle/oradata/orcl/undotbs01.dbf'

在RMAN提示符后运行RECOVER ... BLOCK命令，指定文件和块编号：

RECOVER

DATAFILE 7 BLOCK 3

DATAFILE 2 BLOCK 235;

例子：定位损坏的块并恢复

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

8323 HIGH OPEN 27-JAN-16 Datafile 8: '/home/oracle/tstest.dbf' contains one or more corrupt blocks

RMAN> validate database;

Starting validate at 27-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 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=/u01/app/oracle/oradata/STONE/datafile/o1_mf_system_cb1769f2_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_cb1769f6_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_example_cb33c57f_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_cb1769ff_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_cb178oqs_.dbf

input datafile file number=00006 name=/u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_cb178or2_.dbf

input datafile file number=00008 name=/home/oracle/tstest.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/STONE/datafile/user01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:01:26

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

1 OK 0 13892 116552 4168226

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_system_cb1769f2_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 66551

Index 0 13992

Other 0 22045

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

2 OK 0 19632 116550 4168231

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_sysaux_cb1769f6_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 33407

Index 0 28236

Other 0 35205

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

3 OK 0 1 14721 4168230

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_undotbs1_cb1769ff_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 0

Other 0 14719

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4 OK 0 78 989 3979319

File Name: /u01/app/oracle/oradata/STONE/datafile/user01.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 268

Index 0 51

Other 0 563

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

5 OK 0 31371 44323 3351224

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_example_cb33c57f_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 4547

Index 0 1149

Other 0 7253

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

6 OK 0 1 1281 3759632

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_test_ind_cb178or2_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 0

Index 0 0

Other 0 1279

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

7 OK 0 513 1921 3706513

File Name: /u01/app/oracle/oradata/STONE/datafile/o1_mf_rcat_ts_cb178oqs_.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 0 409

Index 0 234

Other 0 764

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

8 FAILED 0 1149 1280 4154177

File Name: /home/oracle/tstest.dbf

Block Type Blocks Failing Blocks Processed

---------- -------------- ----------------

Data 1 1

Index 0 0

Other 0 130

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/stone/stone/trace/stone_ora_11705.trc for details

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:01

List of Control File and SPFILE

===============================

File Type Status Blocks Failing Blocks Examined

------------ ------ -------------- ---------------

SPFILE OK 0 2

Control File OK 0 608

Finished validate at 27-JAN-16

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

---------- ---------- ---------- ------------------ ---------

8 131 1 4154173 CORRUPT

SQL> select count(*) from emptest;

select count(*) from emptest

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 8, block # 131)

ORA-01110: data file 8: '/home/oracle/tstest.dbf'

SQL> analyze table emptest validate structure cascade;

analyze table emptest validate structure cascade

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 8, block # 131)

ORA-01110: data file 8: '/home/oracle/tstest.dbf'

[oracle@oracletest ~]$ dbv userid=sys/123456 file=/home/oracle/tstest.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 27 19:11:02 2016

DBVERIFY - Verification starting : FILE = /home/oracle/tstest.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x02000083 (file 8, block 131)

Bad header found during dbv:

Data in bad block:

type: 6 format: 6 rdba: 0x02000094

last change scn: 0x0000.003f633d seq: 0x2 flg: 0x04

spare1: 0xed spare2: 0xf3 spare3: 0x0

consistency value in tail: 0x633d0602

check value in block header: 0xadcc

computed block checksum: 0xe7fa

DBVERIFY - Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 129

Total Pages Processed (Seg) : 1

Total Pages Failing (Seg) : 0

Total Pages Empty : 1149

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 4154177 (0.4154177)

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

---------- -------- --------- ------------- -------

8323 HIGH OPEN 27-JAN-16 Datafile 8: '/home/oracle/tstest.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

no manual actions available

Automated Repair Options

========================

Option Repair Description

------ ------------------

1 Perform block media recovery of block 131 in file 8

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2387623021.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/stone/stone/hm/reco_2387623021.hm

contents of repair script:

# block media recovery

recover datafile 8 block 131;

Do you really want to execute the above repair (enter YES or NO)? yes

executing repair script

Starting recover at 27-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00008

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_27/o1_mf_nnndf_TAG20160127T163123_cbk02w4k_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/STONE/backupset/2016_01_27/o1_mf_nnndf_TAG20160127T163123_cbk02w4k_.bkp tag=TAG20160127T163123

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 27-JAN-16

repair failure complete

20、自动诊断流程

OCP课程50：管理II之诊断数据库

使用自动诊断知识库ADR（Automatic Diagnostic Repository）存放关键错误事件的诊断信息。可以使用这些诊断信息创建事件包并发送给Oracle支持服务。

诊断工作流如下：

（1）在EM中出现事件告警。

（2）在EM告警页面查看告警。

（3）向下钻取到的事件和问题的细节。

（4）通过My Oracle Support将信息打包并发送到Oracle支持服务。

21、自动诊断知识库ADR

OCP课程50：管理II之诊断数据库

ADR是一个基于文件的知识库，包括跟踪文件，事件转储和包，告警日志，健康监测报告，核心转储等数据。多个实例以及多个产品共用一个统一的位于数据库之外的目录结构。因此可以在数据库关闭时用于问题诊断。

从Oracle数据库11gR1开始，数据库，自动存储管理（ASM），集群就绪服务（CRS），和其他Oracle产品或部件存储所有诊断数据到ADR。每个产品的每个实例都存储诊断数据到自己的ADR主目录。例如，在一个共享存储和ASM的RAC环境中，每个数据库实例和每个ASM实例都有一个在ADR中的主目录。ADR的统一目录结构，统一的诊断数据格式和一套统一的工具，可以使用户和Oracle支持跨多个实例进行关联和分析相关诊断数据。

ADR基目录通过DIAGNOSTIC_DEST初始化参数设置，如果忽略该参数或者为空，那么如果设置了ORACLE_BASE环境变量，则DIAGNOSTIC_DEST就为ORACLE_BASE，如果没有设置ORACLE_BASE环境变量，则DIAGNOSTIC_DEST为$ORACLE_HOME/log。

SQL> show parameter diag

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

diagnostic_dest string /u01/app/oracle

[oracle@oracletest ~]$ tree -L 2 /u01/app/oracle/diag/

/u01/app/oracle/diag/

├── asm

├── clients

│ └── user_oracle

├── crs

├── diagtool

├── lsnrctl

├── netcman

├── ofm

├── rdbms

│ ├── dummy

│ └── stone

└── tnslsnr

└── oracletest

22、ADR命令行工具ADRCI

OCP课程50：管理II之诊断数据库

ADRCI是一个命令行工具，是数据库的故障可诊断性基础设施的一部分。ADRCI可以：

查看自动诊断知识库内的诊断数据
打包事件和问题信息到zip文件用于发给Oracle Support。

ADRCI可以在交互模式下使用或在脚本里使用。此外，ADRCI可以执行ADRCI命令脚本，和在SQL*Plus中执行SQL脚本和PL/SQL命令类似。由于ADR的数据没有安全要求，故无需登录到ADRCI，通过操作系统权限确保ADR数据安全。

打包和管理诊断数据最简单的方式是使用EM的支持工作台（Support Workbench）。

ADRCI提供了支持工作台的大部分功能，并增加了诸如查看跟踪文件的功能。上图中例子列出所有打开的事件。

例子：通过ADRCI查看事件

[oracle@oracletest ~]$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Wed Jan 27 21:02:27 2016

ADR base = "/u01/app/oracle"

adrci> show incident

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_3709602581_80:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/oracletest/listener:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/dummy/stone:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/stone/stone:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

-------------------- ----------------------------------------------------------- ----------------------------------------

73353 ORA 1578 2016-01-27 16:35:59.925000 +08:00

73354 ORA 1578 2016-01-27 16:36:04.770000 +08:00

73355 ORA 1578 2016-01-27 16:46:00.525000 +08:00

73356 ORA 1578 2016-01-27 16:50:18.178000 +08:00

74781 ORA 1578 2016-01-27 19:09:09.792000 +08:00

74782 ORA 1578 2016-01-27 19:09:26.033000 +08:00

74783 ORA 1578 2016-01-27 19:09:30.499000 +08:00

7 rows fetched

23、V$DIAG_INFO视图

OCP课程50：管理II之诊断数据库

V$DIAG_INFO视图列出了所有重要的ADR的位置：

ADR Base：ADR基目录
ADR Home：当前数据库实例的ADR主目录
Diag Trace：文本告警日志和前后台进程的跟踪文件的位置
Diag Alert：XML版本的告警日志的位置
Diag Incident：事件日志位置
Diag Cdump：诊断核心文件位置
Health Monitor：健康监测日志位置
Default Trace File：用户会话跟踪文件路径，SQL跟踪文件位置。

SQL> select * from v$diag_info;

INST_ID NAME VALUE

---------- ------------------------- -----------------------------------------------------------------

1 Diag Enabled TRUE

1 ADR Base /u01/app/oracle

1 ADR Home /u01/app/oracle/diag/rdbms/stone/stone

1 Diag Trace /u01/app/oracle/diag/rdbms/stone/stone/trace

1 Diag Alert /u01/app/oracle/diag/rdbms/stone/stone/alert

1 Diag Incident /u01/app/oracle/diag/rdbms/stone/stone/incident

1 Diag Cdump /u01/app/oracle/diag/rdbms/stone/stone/cdump

1 Health Monitor /u01/app/oracle/diag/rdbms/stone/stone/hm

1 Default Trace File /u01/app/oracle/diag/rdbms/stone/stone/trace/stone_ora_14570.trc

1 Active Problem Count 1

1 Active Incident Count 7

11 rows selected.

24、诊断文件位置

OCP课程50：管理II之诊断数据库

上图是跟踪数据和转储文件在10g和11g的位置对比。

在11g中，前台进程和后台进程跟踪文件没有区别，都位于ADR_HOME/trace目录。也就是说，所有非事件的跟踪文件都存储在trace子目录。事件转储文件与进程跟踪文件分开存放。

跟踪文件和转储文件之间的主要区别是，跟踪文件是一个连续的输出，如SQL跟踪，转储文件是一个事件的一次性输出。此外，核心转储是一个二进制内存转储。

25、健康监控：概览

OCP课程50：管理II之诊断数据库

数据库包括一个健康监测框架，用于对数据库中的各种组件进行诊断检查。健康监测检查数据库中的各种组件，包括文件、内存、事务完整性、元数据和进程使用。这些检查产生报告以及解决问题的建议。故障诊断基础设施可以自动运行健康检查以响应关键错误，也可以通过DBMS_HM PL/SQL包或EM界面手动运行健康检查。

通过V$HM_CHECK查看健康监测可以运行的检查的描述，这些检查分为两类：

DB-online：在数据库打开的时候运行（open模式）。
DB-offline：实例可用，数据库关闭（nomount模式）。

在检查运行后，会产生一个包含检查结果的报告，包括优先级（低，高，或关键），描述以及基本统计数据。健康监测产生XML格式的报告并存储在ADR中。可以使用V$HM_RUN，DBMS_HR，ADRCI或者EM查看。

例子：查看健康监测可以运行的检查的描述

SQL> select name,description from v$hm_check;

NAME DESCRIPTION

---------------------------------------- ----------------------------------------------------------------------

HM Test Check Check for health monitor functionality

DB Structure Integrity Check Checks integrity of all database files

CF Block Integrity Check Checks integrity of a control file block

Data Block Integrity Check Checks integrity of a data file 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

No Mount CF Check Checks control file in NOMOUNT mode

Mount CF Check Checks control file in mount mode

CF Member Check Checks a multiplexed copy of the control file

NAME DESCRIPTION

---------------------------------------- ----------------------------------------------------------------------

All Datafiles Check Checks all datafiles in the database

Single Datafile Check Checks a data file

Tablespace Check Check Checks a tablespace

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 accessibility

Block IO Revalidation Check Checks file accessibility

Txn Revalidation Check Revalidate corrupted transaction

Failure Simulation Check Creates dummy failures

NAME DESCRIPTION

---------------------------------------- ----------------------------------------------------------------------

Dictionary Integrity Check Checks dictionary integrity

ASM Mount Check Diagnose mount failure

ASM Allocation Check Diagnose allocation failure

ASM Disk Visibility Check Diagnose add disk failure

ASM File Busy Check Diagnose file drop failure

ASM Toomanyoff Check Diagnose mount failed because there were too many offline disks

ASM Insufficient Disks Check Diagnose mount failed because there were insufficient disks

ASM Insufficient Mem Check Check to adjust memory on allocation failure

30 rows selected.

26、手工运行健康检查：PL/SQL示例

OCP课程50：管理II之诊断数据库

使用DBMS_HM.RUN_CHECK程序运行健康检查。调用RUN_CHECK，使用V$HM_CHECK中的名字，提供一个运行的名字（用来检索报告）以及控制执行的输入参数。可以使用V$HM_CHECK_PARAM查看这些参数。

在上图中，运行一个数据库字典检查，检查表TAB$（一个重要的核心字典对象），命名为mycheck，不设置任何超时。

执行后，使用DBMS_HM.GET_RUN_REPORT从V$HM_RUN，V$HM_FINDING和V$HM_RECOMMENDATION视图获取报告。报告清楚地显示了TAB$表的一个关键错误。

当你调用GET_RUN_REPORT函数，在ADR中的HM目录产生XML报告文件，在上面的例子中，文件名字为HMREPORT_mycheck.hm。

例子：手工运行监控检查

SQL> begin

2 dbms_hm.run_check(

3 check_name => 'Data Block Integrity Check',

4 run_name => 'stone',

5 input_params => 'BLC_DF_NUM=8;BLC_BL_NUM=131');

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> set long 100000

SQL> select dbms_hm.get_run_report('stone') from dual;

DBMS_HM.GET_RUN_REPORT('STONE')

--------------------------------------------------------------------------------

Basic Run Information

Run Name : stone

Run Id : 8585

Check Name : Data Block Integrity Check

Mode : MANUAL

Status : COMPLETED

Start Time : 2016-01-27 22:52:01.234049 +08:00

End Time : 2016-01-27 22:52:01.533168 +08:00

Error Encountered : 0

Source Incident Id : 0

Number of Incidents Created : 0

DBMS_HM.GET_RUN_REPORT('STONE')

--------------------------------------------------------------------------------

Input Paramters for the Run

BLC_DF_NUM=8

BLC_BL_NUM=131

Run Findings And Recommendations

Finding

Finding Name : Media Block Corruption

Finding ID : 8589

Type : FAILURE

Status : OPEN

DBMS_HM.GET_RUN_REPORT('STONE')

--------------------------------------------------------------------------------

Priority : HIGH

Message : Block 131 in datafile 8: '/home/oracle/tstest.dbf' is media

corrupt

Message : Object EMPTEST owned by SYS might be unavailable

27、使用ADRCI工具查看健康检查报告

OCP课程50：管理II之诊断数据库

可以使用ADRCI创建和查看健康监测检查报告。需要先确保正确设置操作系统环境变量，如ORACLE_HOME，然后在操作系统命令提示符输入命令：adrci。

命令显示如上图，可以使用show homes列出所有ADR主目录，使用set homepath改变当前ADR主目录。使用create report hm_run命令生成报告，使用show report hm_rum命令查看报告。

例子：使用ADRCI工具查看健康检查报告

adrci> show homes

ADR Homes:

diag/clients/user_oracle/host_3709602581_80

diag/tnslsnr/oracletest/listener

diag/rdbms/dummy/stone

diag/rdbms/stone/stone

adrci> show hm_run -p "run_id=8585"

ADR Home = /u01/app/oracle/diag/clients/user_oracle/host_3709602581_80:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/tnslsnr/oracletest/listener:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/dummy/stone:

*************************************************************************

0 rows fetched

ADR Home = /u01/app/oracle/diag/rdbms/stone/stone:

*************************************************************************

**********************************************************

HM RUN RECORD 1

**********************************************************

RUN_ID 8585

RUN_NAME stone

CHECK_NAME Data Block Integrity Check

NAME_ID 3

MODE 0

START_TIME 2016-01-27 22:52:01.234049 +08:00

RESUME_TIME

END_TIME 2016-01-27 22:52:01.533168 +08:00

MODIFIED_TIME 2016-01-27 22:53:09.975723 +08:00

TIMEOUT 0

FLAGS 0

STATUS 5

SRC_INCIDENT_ID 0

NUM_INCIDENTS 0

ERR_NUMBER 0

REPORT_FILE /u01/app/oracle/diag/rdbms/stone/stone/hm/HMREPORT_stone.hm

1 rows fetched

adrci> set homepath diag/rdbms/stone/stone

adrci> create report hm_run stone

adrci> show report hm_run stone

HM Report: stone

Data Block Integrity Check

8585

stone

MANUAL

COMPLETED

2016-01-27 22:52:01.234049 +08:00

2016-01-27 22:52:01.533168 +08:00

BLC_DF_NUM=8

BLC_BL_NUM=131

Media Block Corruption

8589

FAILURE

OPEN

HIGH

2016-01-27 22:52:01.526152 +08:00

Block 131 in datafile 8: '/home/oracle/tstest.dbf' is media corrupt

Object EMPTEST owned by SYS might be unavailable

相关习题：

（1）Examine the section of the Health Check report given below:

DBMS_HM.GET_RUN_REPORT ('HM_RUN_1061')

Run Name: HM_RUN_1061 Run Id: 1061 Check Name: Data Block Integrity

Check Mode: REACTIVE Status: COMPLETED

Start Time: 2007-05-12 22:11:02.032292 -07:00 End Time: 2007-05-12 22:11:20.835135-07:00

Error Encountered: 0 Source Incident Id: 7418

Number of Incidents Created: 0

Which two statements are true regarding the Health Check report? (Choose two.)

A.Health Check was performed manually.

B.Health Check was performed to check the disk image block corruptions.

C.Health Check was performed to check interblock and intersegment corruption.

D.Health Check was performed to verify the integrity of database files and report failures.

E.Health Check was performed by the Health Monitor automatically in response to a critical error.

答案：BE

（2）Which two statements are true regarding the Automatic Diagnostic Repository (ADR) in Oracle Database 11g? (Choose two.)

A.A single ADR can support multiple ADR homes for different database instances.

B.The alert files are stored in XML file format in the TRACE directory of each ADR home.

C.If the environmental variable ORACLE_BASE is set, then DIAGNOSTIC_DEST is set to $ORACLE_BASE.

D.The BACKGROUND_DUMP_DEST initialization parameter overrides the DIAGNOSTIC_DEST

initialization parameter for the location of the alert log file.

答案：AC

（3）Identify the three predefined server-generated alerts. (Choose three.)

A.Drop User

B.Tablespace Space Usage

C.Resumable Session Suspended

D.Recovery Area Low On Free Space

E.SYSTEM Tablespace Size Increment

答案：BCD

（4）102. Examine the following values of the initialization parameters in the database having the SID ORCL:

BACKGROUND_DUMP_DEST=/u01/app/oracle/product/1 1. 1. 0/db_1/bdump

USER_DUMP_DEST=/ u01/app/oracle/product/1 1.1. 0/db_1/udump

CORE_DUMP_DEST=/u01/app/oracle/product/1 1.1. 0/db_1/cdump

DIAGNOSTIC_DEST=

The environment variables have the following value:

ORACLE_BASE=/u01/ app/oracle

ORACLE_HOME=/u01/app/oracle/product/ 1 1.1.0/db_1

What is the location of the Automatic Diagnostic Repository (ADR) home?

A. /u01/app/oracle/product /11.1. 0/db_1

B. /u01/app/oracle

C. $ORACLE_HOME/bdump

D. $ORACLE_HOME/log

答案：B

（5）You plan to collect the Automatic Workload Repository (AWR) data every Monday morning for a month. You want Oracle Database to automatically create a baseline every Monday and remove the old baseline. What is the correct action to achieve this?

A.Create and populate a SQL Tuning Set from the AWR on every Monday.

B.Change the RETENTION setting for the AWR snapshots to 7 days on Monday.

C.Create a repeating baseline template.

D.Insert a finding directive for future ADDM tasks.

答案：C

（6） Which tasks can be accomplished using the Enterprise Manager Support Workbench in Oracle Database 1 1g? (Choose all that apply .)
A. Generate reports on data failure such as data file failures.
B. You can track the Service Request (SR) and implement repairs.
C. You can package and upload diagnostic data to Oracle Support.
D. You can manually run health checks to gather diagnostic data for a problem.

答案：BCD

（7） Which statements are true regarding the concept of problems and incidents in the fault diagnosability infrastructure for Oracle Database 1 1g? (Choose all that apply .)
A. Only the incident metadata and dumps are stored in the Automatic Diagnostic Repository (ADR).
B. The problem key is the same as the incident number .
C. The database sends an incident alert to the Oracle Enterprise Manager Database Home page.
D. Every problem has a problem key , which is a text string that describes the problem.
E. The database makes an entry into the alert log file when problems and incidents occur .

答案：CDE

（8）Which statement describes the significance of the CHANGE FAILURE command in RMAN?

(Choose all that apply.)

A. It is used to change failure priority only for HIGH or LOW priorities.

B. It is used to execute the advised repair script.

C. It is used to change failure priority only for the CRITICAL priority.

D. It is used to explicitly close the open failures.

E. It is used to inform the database about the repair after the repair script executes.

答案：AD

（9） Which statements are true regarding the creation of an incident package file by using the EM Workbench Support? (Choose all that apply .)
A. You can add or remove the trace files to the package.
B. You can create the incremental incident package ZIP file for new or modified diagnostic information for the incident package already created.
C. You can add SQL test cases to the incident package.
D. You cannot create an incremental incident package when the physical files are purged rom the ADR.

答案：ABC

（10）Which two statements are true regarding Health Monitor checks in Oracle Database 11g?

(Choose two.)

A. Health Monitor checks can be used to scan the contents of the redo log and archive logs for accessibility and corruption.

B. Health Monitor checks can be used to verify the integrity of database files and report failures if these files are inaccessible, corrupt or inconsistent.

C. Health Monitor checks can be used to verify the contents of dictionary entries for each dictionary object and fix it automatically.

D. Health Monitor checks are always initiated manually when there is some critical error.

答案：AB

（11）You installed Oracle Database 1 1g afresh. Which statements are true regarding the default audit settings in this database? (Choose all that apply .)
A. The audit trail is stored in an operating system file.
B. Auditing is disabled for all privileges.
C. The audit trail is stored in the database.
D. Auditing is enabled for all privileges.
E. Auditing is enabled for certain privileges related to database security .

答案：CE

（12）The DB_BLOCK_CHECKING initialization parameter is set to OFF. Which block checking would be performed?

A. The Oracle database will perform block checking for the index blocks only

B. The Oracle database will not perform block checking for any of the data blocks

C. The Oracle database will perform block checking for the default permanent tablespace only

D. The Oracle database will perform block checking for the data blocks in all user tablespaces

E. The Oracle database will perform block checking for the data blocks in the SYSTEM tablespace only

答案：E

（13）View the Exhibit to examine the details for an incident. Which statement is true regarding the status of the incident?

此主题相关图片如下164.png：
OCP课程50：管理II之诊断数据库

A. The incident has been newly created and is in the process of collecting diagnostic information.
B. The incident is now in the Done state and the ADR can select the incident to be purged.
C. The DBA is working on the incident and prefers that the incident be kept in the ADR.
D. The data collection for the incident is complete and the incident can be packaged and sent to Oracle
Support.

答案：D

（14）Exhibit:

此主题相关图片如下：
OCP课程50：管理II之诊断数据库
View the Exhibit to examine a portion of the output from the VALIDATE DATABASE command.

Which statement is true about the block corruption detected by the command?

A. No action is taken except the output in the Exhibit.

B. The ADVISE FAILURE command is automatically called to display the repair script.

C. The failure is logged into the Automatic Diagnostic Repository (ADR).

D. The corruption is repaired by the command implicitly.

答案：C

（15）View the Exhibit to examine the error during the database startup.

You open an RMAN session for the database instance. To repair the failure, you executed the following as the first command in the RMAN session:

RMAN> REPAIR FAILURE;

Which statement describes the consequence of the command?

此主题相关图片如下：
OCP课程50：管理II之诊断数据库

A. The command performs the recovery and closes the failures.

B. The command only displays the advice and the RMAN script required for repair.

C. The command produces an error because the ADVISE FAILURE command has not been executed before the REPAIR FAILURE command.

D. The command executes the RMAN script to repair the failure and removes the entry from the Automatic Diagnostic Repository (ADR).

答案：C

（16）You executed the following PL/SQL block successfully:

VARIABLE tname VARCHAR2(20)

BEGIN

dbms_addm.insert_finding_directive (NULL, DIR_NAME=>'Detail CPU Usage',

FINDING_NAME=>'CPU Usage', MIN_ACTIVE_SESSIONS=>0, MIN_PERC_IMPACT=>90);

:tname := 'database ADDM task4';

dbms_addm.analyze_db(:tname, 150, 162);

END; /

Then you executed the following command:

SQL> SELECT dbms_addm.get_report(:tname) FROM DUAL;

The above command produces Automatic Database Diagnostic Monitor (ADDM) analysis ____.

A. with the CPU Usage finding if it is less than 90

B. without the CPU Usage finding if it is less than 90

C. with the CPU Usage finding for snapshots below 90

D. with the CPU Usage finding for snapshots not between 150 and 162

答案：B

（17）You plan to set up the Automatic Workload Repository (AWR) baseline metric thresholds for a moving window baseline. Which action would you take before performing this task?

A. Compute the baseline statistics.

B. Take an immediate AWR snapshot.

C. Decrease the window size for the baseline.

D. Decrease the expiration time for the baseline.

答案：A

（18）View the Exhibit to examine the Automatic Database Diagnostic Monitor (ADDM) tasks. Y ou execut ed
the following commands:
SQL> V AR tname V ARCHAR2(60);
SQL> BEGIN

:tname := 'my_instance_analysis_mode_task';
DBMS_ADDM.INSERT_SEGMENT_DIRECTIVE(:tname,'Sg_directive','SCOTT');
END;
Which statement describes the consequence?

此主题相关图片如下：
OCP课程50：管理II之诊断数据库
A. The ADDM task is filtered to suppress the Segment Advisor suggestions for the SCOTT schema.
B. The ADDM task is filtered to produce the Segment Advisor suggestions for the SCOTT schema only .
C. The PL/SQL block produces an error because the my_instance_analysis_mode_task task has not been reset to its initial state.
D. All subsequent ADDM tasks including my_instance_analysis_mode_task are filtered to suppress the Segment Advisor suggestions for the SCOTT schema.

答案：C

（19）You executed the following commands in an RMAN session for your database instance that has failures:

RMAN> LIST FAILURE;

After some time, you executed the following command in the same session:

RMAN> ADVISE FAILURE;

But there are new failures recorded in the Automatic Diagnostic Repository (ADR) after the execution of the last LIST FAILURE command. Which statement is true for the above ADVISE FAILURE command in this scenario?

A. It produces a warning for new failures before advising for CRITICAL and HIGH failures.

B. It ignores new failures and considers the failures listed in the last LIST FAILURE command only.

C. It produces an error with recommendation to run the LIST FAILURE command before the ADVISE FAILURE command.

D. It produces advice only for new failures and the failures listed in the last LIST FAILURE command are ignored.

答案：A

（20）View the Exhibit to examine the output for the V$DIAG_INFO view.

Which statements are true regarding the location of diagnostic traces? (Choose all that apply.)

此主题相关图片如下：
OCP课程50：管理II之诊断数据库

A. The path to the location of the background as well as the foreground process trace files is /u01/oracle/diag/rdbms/orclbi/orclbi/trace.

B. The location of the text alert log file is /u01/oracle/diag/rdbms/orclbi/orclbi/alert.

C. The location of the trace file for the current session is /u01/oracle/diag/rdbms/orclbi/orclbi/trace.

D. The location of the XML-formatted version of the alert log is /u01/oracle/diag/rdbms/orclbi/orclbi/alert.

答案：ACD

（21）Which two kinds of failures make the Data Recovery Advisor (DRA) generate a manual checklist?

(Choose two.)

A. failures because a data file is renamed by error

B. failures when no standby database is configured

C. failures that require no archive logs to be applied for recovery

D. failures due to loss of connectivity-for example, an unplugged disk cable

答案：AD

（22）Which two activities are NOT supported by the Data Recovery Advisor? (Choose two.)

A. Diagnose and repair a data file corruption offline.

B. Diagnose and repair a data file corruption online.

C. Diagnose and repair failures on a standby database.

D. Recover from failures in the Real Application Cluster (RAC) environment.

答案：CD

（23）Which statement is true for enabling Enterprise Manager Support Workbench in Oracle Database 1 1g to upload the physical files generated by Incident Packaging Service (IPS) to MetaLink?
A. The database must be running in ARCHIVELOG mode.
B. No special setup is required, and the feature is enabled by default.
C. The path for the Automatic Diagnostic Repository (ADR) must be configured with the DIAGNOSTIC_DEST initialization parameter .
D. The Enterprise Manager Support Workbench can be enabled only if the background process manageability monitor (MMON) is configured.
E. Select the Enable option in the Oracle Configuration Manager Registration window during the installation of the Oracle Database 1 1g software, provide valid MetaLink credentials and select license agreement.

答案：E

（24）View the Exhibit to examine the error while executing the REPAIR FAILURE command in an RMAN session.

此主题相关图片如下：
OCP课程50：管理II之诊断数据库

What is the reason for this error?

A. Another repair session is running concurrently.

B. The failure ID has not been mentioned in the command for data file 5.

C. There are new failures recorded in the Automatic Diagnostic Repository (ADR).

D. The ADVISE FAILURE command has not been issued before the REPAIR FAILURE command.

答案：A

（25）Observe the following warning in an RMAN session of your database instance:

WARNING: new failures were found since last LIST FAILURE command

Which statement describes the scenario that must have produced this warning?

A. The CHANGE FAILURE command has detected new failures recorded in the Automatic Diagnostic Repository(ADR).

B. The VALIDATE DATABASE command has detected new failures recorded in the Automatic Diagnostic Repository (ADR).

C. The ADVISE FAILURE command has detected new failures recorded in the Automatic Diagnostic Repository (ADR) since the last LIST FAILURE.

D. The RECOVER command has detected new failures recorded in the Automatic Diagnostic Repository (ADR) since the last LIST FAILURE command was executed.

答案：C

（26）Which of the following initialization parameters have been deprecated in Oracle 11g because of

the introduction of the Automatic Diagnostic Repository? (Choose all that apply.)

A. BACKGROUND_DUMP_DEST

B. FOREGROUND_DUMP_DEST

C. CORE_DUMP_DEST

D. USER_DUMP_DEST

E. DIAGNOSTIC_DEST

F. All of the above

答案：ACD

（27）Which of the following statements is true regarding the initialization parameter DIAGNOSTIC_DEST?

A.The default value is the value of the environment variable $ORACLE_HOME; if $ORACLE_HOME isn't set,then the default is set to $ORACLE_BASE.

B.The default value is the value of the environment variable $ORACLE_BASE; if $ORACLE_BASE isn't set, then it is set to $ORACLE_HOME.

C. DIAGNOSTIC_DEST is always equal to $ORACLE_HOME.

D. DIAGNOSTIC_DEST is always equal to $ORACLE_BASE.

答案：B

（28）Which of these formats represents the correct hierarchy for the ADR?

A. /rdbms/diag//

B. /diag/rdbms//

C. /diag/rdbms//

D. None of the above

答案：C

（29）Which of the following are not fundamental tasks of the Support Workbench? (Choose all that

apply.)

A. View long-running SQL workloads

B. View problem details

C. Gather additional diagnostic information

D. Create a Service Request

E. Clean up incident data after upload to Oracle Support

答案：AE

（30）Which of the following tasks does the tool Incident Packaging Service (IPS) perform?

A. Cleans up the ADR by deleting files not associated with an incident uploaded

to Oracle Support.

B. Identifies all files associated with a critical error and adds them to a zip file to

be sent to Oracle Support.

C. Automatically opens a Service Request with Oracle Support for each critical

error and sends all relevant files.

D. Displays a high-level view of critical errors on the database home page.

答案：B

（31）Choose the correct order to package and upload data for an incident to Oracle Support.

A. Schedule, create new package, view manifest, view contents

B. Create new package, view manifest, view contents, schedule

C. Schedule, create new package, view contents, view manifest

D. Create new package, view contents, view manifest, schedule

E. None of the above.

答案：D

（32）Which statement is true regarding the VALIDATE DATABASE command?

A. It checks the database for intrablock corruptions only.

B. It checks for block corruption in the valid backups of the database.

C. It checks the database for both intrablock and interblock corruptions.

D. It checks for only those corrupted blocks that are associated with data files.

答案：A

（33）Which statement is true regarding the retention policy for the incidents accumulated in the Automatic Diagnostic Repository (ADR)?

A. The incident metadata is purged when the problem is resolved and the DBA closes the SR.

B. The incident files and dumps are not retained in the ADR for the manually created incidents.

C. The incident files are retained but the incident metadata is purged when the problem is resolved and the DBA closes the SR

D. The default setting is for one year after which the incident metadata is purged from the ADR and the files are retained for one month.

答案：D

（34）View the Exhibit to examine the error during the database startup. You open an RMAN session for the database instance. To repair the failure, you executed the following as the first command in the RMAN session:

RMAN> REPAIR FAILURE;

Which statement describes the consequence of the command? Exhibit:

此主题相关图片如下：
OCP课程50：管理II之诊断数据库
A.The command performs the recovery and closes the failures.

B.The command executes the RMAN script to repair the failure and removes the entry from the Automatic

Diagnostic Repository (ADR).

C.The command only displays the advice and the RMAN script required for repair.

D.The command produces an error because the ADVISE FAILURE command has not been executed before the REPAIR FAILURE command.

答案：D

（35）View the Exhibit and note the contents of V$DIAG_INFO. Which statement is true about the ADR?

Exhibit:

此主题相关图片如下：
OCP课程50：管理II之诊断数据库

A. The text alert log file will be available in Diag Trace

B. A copy alert log file will be kept in Diag Incident for every incident.

C. The XML version of the alert log file will be available in Diag Trace.

D. An Automatic Database Diagnostic Management (ADDM) report is generated and stored in the Health Monitor whenever an incident occurs.

答案：A

（36）Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)

A. The ADR base keeps all diagnostic information in binary format

B. SQL*Plus provides the ADRI script, which can be used to work with ADR

C. The ADR can be used for problem diagnosis only when the database is open

D. The ADR can be disabled by settting the DIAGNOSTIC_DEST parameter to null

E. The ADR can be used for problem diagnosis even when the database instance is down

F. The ADR base is shared across multiple instances

答案：EF

（37）Examine the following RMAN script:

RMAN> run {

debug on;

allocate channel c1 type disk;

backup datafile 5;

}

Which statement describes the purpose of the script?

A. The data file is checked for physical corruption and backed up if found clean.

B. The backup of data file 5 is performed and the interactive messages during the backup are suppressed.

C. The existing backup for the data file is checked and the backup is performed if there are changes in the data file after the last backup.

D. The backup of data file 5 is performed and all SQL statements that are executed during RMAN compilation and their results are displayed

答案：D

（38）The environmental variable ORACLE-BASE is set. You want to check the diagnostic files created as part of the Automatic Diagnostic Repository (ADR). View the Exhibit and note the various parameters set in your database.

What will be the location of the ADR base?

此主题相关图片如下：
OCP课程50：管理II之诊断数据库