Multitenant - Performing RMAN Recovery from non-SYSTEM Root Datafile Loss on CDB

Multitenant -  Performing RMAN Recovery from non-SYSTEM Root Datafile Loss


Scenario Preview : In this case we will delete syssaux tablespace on PLM2 pluggable database and recover it using RDA recovery data advisor.


SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         8 PLM5                           READ WRITE NO
SQL> alter session set container=PLM2;

• Delete sysaux tablespace on PLM2 pluggable database.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/
o1_mf_sysaux_hlr3x0xq_.dbf


oracle@srv3:/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB> rm -f /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/
o1_mf_sysaux_hlr3x0xq_.dbf

• Using Recovery data Advisor RDA  validate datafile.

RMAN> validate datafile '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf';

Starting validate at 17-AUG-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
RMAN-06169: could not read file header for datafile 31 error reason 5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 08/17/2020 16:18:38
RMAN-06056: could not access datafile 31

• List Failure 

RMAN> LIST FAILURE;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

RMAN>  LIST FAILURE detail;

Database Role: PRIMARY

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 6202

  Failure ID Priority Status    Time Detected Summary

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

  6205       HIGH     OPEN      17-AUG-20     Datafile 31: '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf' is missing

    Impact: Some objects in tablespace SYSAUX might be unavailable

• Advise Failure 

RMAN> ADVISE FAILURE;

Database Role: PRIMARY

List of Database Failures

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

Failure ID Priority Status    Time Detected Summary

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

6202       HIGH     OPEN      17-AUG-20     One or more non-system datafiles are missing

  Impact: See impact for individual child failures

  List of child failures for parent failure ID 6202

  Failure ID Priority Status    Time Detected Summary

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

  6205       HIGH     OPEN      17-AUG-20     Datafile 31: '/u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf' is missing

    Impact: Some objects in tablespace SYSAUX might be unavailable

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

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

1. If file /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

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

Option Repair Description

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

1      Restore and recover datafile 31

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

  Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

RMAN> REPAIR FAILURE PREVIEW;

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

Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

contents of repair script:

   # restore and recover datafile

   sql 'PLM2' 'alter database datafile 31 offline';

   restore ( datafile 31 );

   recover datafile 31;

   sql 'PLM2' 'alter database datafile 31 online';

• Recover datafile syssaux below :

RMAN> REPAIR FAILURE;

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

Repair script: /u01/app/oracle/diag/rdbms/smrcdb/SMRCDB/hm/reco_4038193287.hm

contents of repair script:

   # restore and recover datafile

   sql 'PLM2' 'alter database datafile 31 offline';

   restore ( datafile 31 );

   recover datafile 31;

   sql 'PLM2' 'alter database datafile 31 online';

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

executing repair script

sql statement: alter database datafile 31 offline

Starting restore at 17-AUG-20

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 00031 to /u01/app/oracle/oradata/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/datafile/o1_mf_sysaux_hlr3x0xq_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/SMRCDB/SMRCDB/AC37117322776C58E0534938A8C02132/backupset/2020_08_17/o1_mf_nnndf_TAG20200817T141833_hmnk31kj_.bkp tag=TAG20200817T141833

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 17-AUG-20

Starting recover at 17-AUG-20

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 17-AUG-20

sql statement: alter database datafile 31 online

repair failure complete

RMAN>