ITPub博客

首页 > 数据库 > Oracle > 诊断和修复错误的步骤

诊断和修复错误的步骤

原创 Oracle 作者:zhangsharp20 时间:2016-04-05 23:51:05 0 删除 编辑
The Data Recovery Advisor workflow begins when you either suspect or discover a failure. You can discover failures in many ways, including error messages, alerts, trace files, and failed data integrity checks.

 the database can automatically diagnose failures when errors occur.

The basic process for responding to failures is to start an RMAN session and perform all of the following steps in the same session:

1. List failures by running the LIST FAILURE command

RMAN> LIST FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles
are missing
101 HIGH OPEN 23-APR-07 Datafile 1:
'/disk1/oradata/prod/system01.dbf' contains one or more corrupt blocks

RMAN> LIST FAILURE 101 DETAIL;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
101 HIGH OPEN 23-APR-07 Datafile 1:
'/disk1/oradata/prod/system01.dbf' contains one or more corrupt blocks
 List of child failures for parent failure ID 101
 Failure ID Priority Status Time Detected Summary
 ---------- -------- --------- ------------- -------
 104 HIGH OPEN 23-APR-07 Block 56416 in datafile 1:
'/disk1/oradata/prod/system01.dbf' is media corrupt
 Impact: Object BLKTEST owned by SYS might be unavailable

2. If you suspect that failures exist that have not been automatically diagnosed by the database, then run VALIDATE DATABASE to check for corrupt blocks and missing files.

If VALIDATE detects a problem, then RMAN triggers execution of a failure assessment. If a failure is detected, then RMAN logs it into the Automated Diagnostic Repository, where is can be accessed by Data Recovery Advisor.

RMAN> VALIDATE CHECK LOGICAL SKIP INACCESSIBLE DATABASE;
Starting validate at 23-APR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=103 device type=DISK
could not access datafile 28
skipping inaccessible file 28
RMAN-06060: WARNING: skipping datafile compromises tablespace USERS
recoverability
RMAN-06060: WARNING: skipping datafile compromises tablespace USERS
recoverability
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/disk1/oradata/prod/system01.dbf
input datafile file number=00002 name=/disk1/oradata/prod/sysaux01.dbf
input datafile file number=00022 name=/disk1/oradata/prod/undotbs01.dbf
input datafile file number=00023 name=/disk1/oradata/prod/cwmlite01.dbf
input datafile file number=00024 name=/disk1/oradata/prod/drsys01.dbf
input datafile file number=00025 name=/disk1/oradata/prod/example01.dbf
input datafile file number=00026 name=/disk1/oradata/prod/indx01.dbf
input datafile file number=00027 name=/disk1/oradata/prod/tools01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 FAILED 0 3536 57600 637711
 File Name: /disk1/oradata/prod/system01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data 1 41876
 Index 0 7721
 Other 0 4467
.
.
.
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
27 OK 0 1272 1280 400914
 File Name: /disk1/oradata/prod/tools01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data 0 0
 Index 0 0
 Other 0 8
validate found one or more corrupt blocks
See trace file /disk1/oracle/log/diag/rdbms/prod/prod/trace/prod_ora_2596.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 512
Finished validate at 23-APR-07

3. Determine repair options by running the ADVISE FAILURE command

RMAN> ADVISE FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles
 are missing
101 HIGH OPEN 23-APR-07 Datafile 1:
'/disk1/oradata/prod/system01.dbf' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=103 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
1. If file /disk1/oradata/prod/users01.dbf was unintentionally renamed or
moved, restore it
2. Contact Oracle Support Services if the preceding recommendations cannot be
used, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform block media recovery of block 56416 in file 1
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_1863891774.hm


RMAN> ADVISE FAILURE 101;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
101 HIGH OPEN 23-APR-07 Datafile 1:
'/disk1/oradata/prod/system01.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 56416 in file 1
 Strategy: The repair includes complete media recovery with no data loss
 Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_708819503.hm

4. Choose a repair option. You can repair the failures manually or run the REPAIR FAILURE command to fix them automatically.

预演
RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_475549922.hm
contents of repair script:
 # restore and recover data file
 sql 'alter database datafile 28 offline';
 restore datafile 28;
 recover datafile 28;
 sql 'alter database datafile 28 online';
 # block media recovery
 recover datafile 1 block 56416;
执行
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_475549922.hm
contents of repair script:
 # restore and recover data file
 sql 'alter database datafile 28 offline';
 restore datafile 28;
 recover datafile 28;
 sql 'alter database datafile 28 online';
 # block media recovery
 recover datafile 1 block 56416;
 
 Strategy: The repair includes complete media recovery with no data loss
Repair script: /disk1/oracle/log/diag/rdbms/prod/prod/hm/reco_475549922.hm
contents of repair script:
 # restore and recover data file
 sql 'alter database datafile 28 offline';
 restore datafile 28;
 recover datafile 28;
 sql 'alter database datafile 28 online';
 # block media recovery
 recover datafile 1 block 56416;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
sql statement: alter database datafile 28 offline
Starting restore at 23-APR-07
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 00028 to /disk1/oradata/prod/users01.dbf
channel ORA_DISK_1: reading from backup piece /disk2/PROD/backupset/2007_04_
18/o1_mf_nnndf_TAG20070418T182042_32fjzd3z_.bkp
channel ORA_DISK_1: piece handle=/disk2/PROD/backupset/2007_04_18/o1_mf_nnndf_
TAG20070418T182042_32fjzd3z_.bkp tag=TAG20070418T182042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 23-APR-07
Starting recover at 23-APR-07
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-APR-07
sql statement: alter database datafile 28 online
Starting recover at 23-APR-07
using channel ORA_DISK_1
searching flashback logs for block images until SCN 429690
finished flashback log search, restored 1 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 23-APR-07
repair failure complete

5. Return to the first step to confirm that all failures were repaired or determine which failures remain.

If appropriate, you can use CHANGE FAILURE command at any time in the Data Recovery Advisor workflow to change the priority of a failure from LOW to HIGH or HIGH to LOW, or close a failure that has been fixed manually

RMAN> LIST FAILURE;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles
 are missing
101 HIGH OPEN 23-APR-07 Datafile 25:
'/disk1/oradata/prod/example01.dbf' contains one or more corrupt blocks


RMAN> CHANGE FAILURE 101 PRIORITY LOW;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
101 HIGH OPEN 23-APR-07 Datafile 25:
'/disk1/oradata/prod/example01.dbf' contains one or more corrupt blocks
Do you really want to change the above failures (enter YES or NO)? YES
changed 1 failures to LOW priority


RMAN> LIST FAILURE ALL;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 23-APR-07 One or more non-system datafiles
                                    are missing
101 LOW OPEN 23-APR-07 Datafile 25:
'/disk1/oradata/prod/example01.dbf' contains one or more corrupt blocks

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29802484/viewspace-2075850/,如需转载,请注明出处,否则将追究法律责任。

下一篇: MAA配置文档
请登录后发表评论 登录
全部评论

注册时间:2014-08-12

  • 博文量
    382
  • 访问量
    634476