Data Recovery Advisor:
- list failure
- advise failure
- repair failure
After dropping one datafile:
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If you restored the wrong version of data file +DATA/oratest/datafile/drop_tbs.269.778508815, then replace it with the correct one
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 21-MAR-12
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 00007 to +DATA/oratest/datafile/drop_tbs.269.778508815
channel ORA_DISK_1: reading from backup piece +FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083
channel ORA_DISK_1: piece handle=+FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083 tag=TAG20120321T123122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-MAR-12
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
repair failure complete
RMAN> exit
Recovery Manager complete.
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 12:36:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn drop_user
Enter password:
Connected.
SQL> select count(*) from drop_table;
select count(*) from drop_table
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/oratest/datafile/drop_tbs.269.778509333'
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 12:37:18 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORATEST (DBID=3309052188)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
862 HIGH OPEN 21-MAR-12 One or more non-system datafiles are offline
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
862 HIGH OPEN 21-MAR-12 One or more non-system datafiles are offline
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
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 Online tablespace DROP_TBS
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm
contents of repair script:
# online a offline tablespace
sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;";
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;
repair failure complete
RMAN> exit
Recovery Manager complete.
Views related to DRA:
ReplyDelete- v$ir_failure - list of all failures including closed ones (result of list failure command)
- v$ir_manual_checklist - view of all manual advice (list of advice failure command)
- v$ir_repair - list of repairs (list of advice failure command)
- v$ir_failure_set - cross reference of failures and advice ids