Summary
There are three (3) RMAN commands:
- list failure
- advise failure
- repair failure
References
Oracle Manuals
- Oracle ® 12.1 Database Backup and Recovery Reference
- Oracle ® 12.1 Database Backup and Recovery User's Guide
- 1 Introduction to Backup and Recovery
- 15 Diagnosing and Repairing Failures with Data Recovery Advisor
- Oracle ® 12.1 Database Administrator's Guide
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:
