Detect and repair data failures with Data Recovery Advisor

Summary

There are three (3) RMAN commands:

list failure
advise failure
repair failure

References

Oracle Manuals

Oracle^® 12.1 Database Backup and Recovery Reference
- 2 RMAN Commands: @ (at sign) to QUIT
  - ADVISE FAILURE
  - LIST
- 3 RMAN Commands: RECOVER to VALIDATE
  - REPAIR FAILURE
  - REPORT
Oracle^® 12.1 Database Backup and Recovery User's Guide
- 1 Introduction to Backup and Recovery
  - About Data Recovery Advisor
- 15 Diagnosing and Repairing Failures with Data Recovery Advisor
Oracle^® 12.1 Database Administrator's Guide
- 9 Managing Diagnostic Data
  - 9.9 Repairing Data Corruptions with the Data Recovery Advisor

Sample Session

List Failure

In order to find what recovery errors there are, I ran the following command under RMAN on the JAR database instance

list failure;

The known failures were:

Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 642 CRITICAL OPEN 2018/04/29 22:20:33 System datafile 11: '/opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_system_c7kqc77v_.dbf' needs media recovery 645 HIGH OPEN 2018/04/29 22:20:34 One or more non-system datafiles need media recovery 622 HIGH OPEN 2018/04/29 21:25:32 One or more non-system datafiles are missing

Advise Failure

In order to find what recovery options there are, I ran the following command under RMAN on the JAR database instance

advise failure;

The known failures were:

Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 642 CRITICAL OPEN 2018/04/29 22:20:33 System datafile 11: '/opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_system_c7kqc77v_.dbf' needs media recovery 645 HIGH OPEN 2018/04/29 22:20:34 One or more non-system datafiles need media recovery 622 HIGH OPEN 2018/04/29 21:25:32 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=61 device type=DISK analyzing automatic repair options complete Mandatory Manual Actions ======================== 1. If file /opt/app/oracle/oradata/transport/o1_mf_demo_fg8ssf96_.dbf was unintentionally renamed or moved, restore it 2. If you have an export of tablespace DEMO, then drop and re-create the tablespace and import the data. 3. 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 ======================= 1. If you restored the wrong version of data file /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_system_c7kqc77v_.dbf, then replace it with the correct one 2. Automatic repairs may be available if you shutdown the database and restart it in mount mode 3. If you restored the wrong version of data file /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_sysaux_c7kqc77w_.dbf, then replace it with the correct one 4. If you restored the wrong version of data file /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_users_c7kqc77w_.dbf, then replace it with the correct one Automated Repair Options ======================== Option Repair Description ------ ------------------ 1 Recover datafile 11; Recover datafile 12; Recover datafile 13 Strategy: The repair includes complete media recovery with no data loss Repair script: /opt/app/oracle/diag/rdbms/jar/jar/hm/reco_3230405272.hm

Repair Failure Automatically

In order to find what recovery options there are, I ran the following command under RMAN on the JAR database instance

repair failure;

The failures were repaired as follows by RMAN:

Strategy: The repair includes complete media recovery with no data loss Repair script: /opt/app/oracle/diag/rdbms/jar/jar/hm/reco_3230405272.hm contents of repair script: # recover datafile recover datafile 11, 12, 13; Do you really want to execute the above repair (enter YES or NO)? yes executing repair script Starting recover at 2018/04/29 22:22:50 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:02 Finished recover at 2018/04/29 22:22:55 repair failure complete

Check for Other Failures

In order to there are any subsequent recovery errors, I ran the following command under RMAN on the JAR database instance:

list failure;

The failures were:

Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 622 HIGH OPEN 2018/04/29 21:25:32 One or more non-system datafiles are missing

Get Advice for Other Failures

In order to get advice on these new recovery errors, I ran the following command under RMAN on the JAR database instance:

advise failure;

The failures were:

Database Role: PRIMARY List of Database Failures ========================= Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 622 HIGH OPEN 2018/04/29 21:25:32 One or more non-system datafiles are missing analyzing automatic repair options; this may take some time using channel ORA_DISK_1 analyzing automatic repair options complete Mandatory Manual Actions ======================== 1. If file /opt/app/oracle/oradata/transport/o1_mf_demo_fg8ssf96_.dbf was unintentionally renamed or moved, restore it 2. If you have an export of tablespace DEMO, then drop and re-create the tablespace and import the data. 3. 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 ======================== no automatic repair options available

Find PDB for DEMO Tablespace

The defect in the above Data Recovery Advisor advice is that it does not tell me which PDB is affected.

I ran the following command under RMAN on the JAR database instance:

report schema;

The result is:

Report of database schema for database with db_unique_name JAR List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 800 SYSTEM YES /opt/app/oracle/oradata/JAR/datafile/o1_mf_system_c7k509cg_.dbf 2 257 PDB$SEED:SYSTEM NO /opt/app/oracle/oradata/JAR/277260DAB302A56EE0539801A8C03709/datafile/o1_mf_system_c7k50cqy_.dbf 3 1100 SYSAUX NO /opt/app/oracle/oradata/JAR/datafile/o1_mf_sysaux_c7k50j9r_.dbf 4 646 PDB$SEED:SYSAUX NO /opt/app/oracle/oradata/JAR/277260DAB302A56EE0539801A8C03709/datafile/o1_mf_sysaux_c7k50l5p_.dbf 5 690 UNDOTBS1 YES /opt/app/oracle/oradata/JAR/datafile/o1_mf_undotbs1_c7k50n4t_.dbf 6 100 USERS NO /opt/app/oracle/oradata/JAR/datafile/o1_mf_users_c7k50ojv_.dbf 7 100 PDB$SEED:USERS NO /opt/app/oracle/oradata/JAR/277260DAB302A56EE0539801A8C03709/datafile/o1_mf_users_c7k50qgq_.dbf 11 281 PLUM:SYSTEM NO /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_system_c7kqc77v_.dbf 12 672 PLUM:SYSAUX NO /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_sysaux_c7kqc77w_.dbf 13 100 PLUM:USERS NO /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_users_c7kqc77w_.dbf 14 257 JAM:SYSTEM NO /opt/app/oracle/oradata/JAR/56F0DC7FE8D5154FE0539801A8C063F1/datafile/o1_mf_system_dsbsd9l5_.dbf 15 663 JAM:SYSAUX NO /opt/app/oracle/oradata/JAR/56F0DC7FE8D5154FE0539801A8C063F1/datafile/o1_mf_sysaux_dsbsd9lj_.dbf 16 100 JAM:USERS NO /opt/app/oracle/oradata/JAR/56F0DC7FE8D5154FE0539801A8C063F1/datafile/o1_mf_users_dsbsd9lk_.dbf 18 0 PLUM:DEMO NO /opt/app/oracle/oradata/transport/o1_mf_demo_fg8ssf96_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 100 TEMP 33554431 /opt/app/oracle/oradata/JAR/datafile/o1_mf_temp_c7k50nrv_.tmp 2 100 PDB$SEED:TEMP 33554431 /opt/app/oracle/oradata/JAR/277260DAB302A56EE0539801A8C03709/datafile/o1_mf_temp_c7k50o4m_.tmp 3 100 PLUM:TEMP 33554431 /opt/app/oracle/oradata/JAR/2776C0571F0BCEF9E0539801A8C04CE1/datafile/o1_mf_temp_c7kqc77w_.dbf 4 100 JAM:TEMP 33554431 /opt/app/oracle/oradata/JAR/56F0DC7FE8D5154FE0539801A8C063F1/datafile/o1_mf_temp_dsbsd9lk_.dbf

Scanning through the output, the relevant line is:

18 0 PLUM:DEMO NO /opt/app/oracle/oradata/transport/o1_mf_demo_fg8ssf96_.dbf

Thus, the DEMO tablespace in the PLUM pluggable database needs to be dropped.

Drop DEMO Tablespace in PLUM PDB

For the RMAN issue reported above, the Data Recovery Advisor advised that we should fix the issue manually. I ran the following command under SQLPLUS on the PLUM pluggable database:

SQL> alter pluggable database plum open; Pluggable database altered. SQL> drop tablespace demo including contents; Tablespace dropped.

Verify No Further RMAN Issues Exist

Used the following RMAN command to check for any further RMAN issues:

list failure;

The result is:

Database Role: PRIMARY no failures found that match specification

As expected, there are no further issues.

Using OEM 12C

As I am still studying for my OCM 12C, I have not upgraded my OMS to 13C yet.

Access Advisor Central

From the database home page in OEM, access the Advisor Central in the Performance menu as shown below:

Access Data Recovery Advisor

The Data Recovery Advisor (DRA) is accessed as follows from the Advisor Central page:

View and Manage Failures

There are currently no failures as can be seen below: