Restore Pluggable database with RMAN Advisory
Recover the Pluggable database with RMAN advisory from CDB completed backup in Oracle.
Error:
SQL> alter pluggable database pdb3 open;
alter pluggable database pdb3 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 34 - see DBWR trace file
ORA-01110: data file 34: 'D:ORACLE18.0.0ORADATAXEPDB3UNDOTBS01.DBF'
Solution
We are using RMAN Advisory for recovery the pluggable database from RMAN Backup.
1. Connect with RMAN
C:Usersoracle>rman target sys
Recovery Manager: Release 18.0.0.0.0 - Production on Tue Mar 12 11:36:55 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: XE (DBID=2882898177)
2. Check the failure with RMAN advisory
RMAN> LIST FAILURE;
using target database control file instead of recovery catalog
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 CRITICAL OPEN 12-MAR-19 System datafile 32: ‘D:ORACLE18.0.0ORADATAXEPDB3SY
STEM01.DBF’ is missing
528 HIGH OPEN 12-MAR-19 One or more non-system datafiles are missing
3. Run the Advise failure command to get the script
Advice command will help us that we have rman backup to recover this failure.
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1242 CRITICAL OPEN 12-MAR-19 System datafile 32: ‘D:ORACLE18.0.0ORADATAXEPDB3SYSTEM01.DBF’ is missing
528 HIGH OPEN 12-MAR-19 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=150 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file D:ORACLE18.0.0ORADATAXEPDB3SYSTEM01.DBF was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. If file D:ORACLE18.0.0ORADATAXEPDB3SYSAUX01.DBF was unintentionally renamed or moved, restore it
4. If file D:ORACLE18.0.0ORADATAXEPDB3UNDOTBS01.DBF was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
—— ——————
1 Restore and recover datafile 32; Restore and recover datafile 33; Restore and recover datafile 34
Strategy: The repair includes complete media recovery with no data loss
Repair script: D:ORACLE18.0.0diagrdbmsxexehmreco_3136680222.hm
4. Check the script provided in advice preview command
RMAN> REPAIR FAILURE PREVIEW;
Strategy: The repair includes complete media recovery with no data loss
Repair script: D:ORACLE18.0.0diagrdbmsxexehmreco_3136680222.hm
contents of repair script:
# restore and recover datafile
sql ‘PDB3’ ‘alter database datafile 32, 33, 34 offline’;
restore ( datafile 32, 33, 34 );
recover datafile 32, 33, 34;
sql ‘PDB3’ ‘alter database datafile 32, 33, 34 online’;
5. Finally start the Repairing Script provided by RMAN Advisory
RMAN> REPAIR FAILURE;
Strategy: The repair includes complete media recovery with no data loss
Repair script: D:ORACLE18.0.0diagrdbmsxexehmreco_3136680222.hm
contents of repair script:
# restore and recover datafile
sql ‘PDB3’ ‘alter database datafile 32, 33, 34 offline’;
restore ( datafile 32, 33, 34 );
recover datafile 32, 33, 34;
sql ‘PDB3’ ‘alter database datafile 32, 33, 34 online’;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
sql statement: alter database datafile 32, 33, 34 offline
Starting restore at 12-MAR-19
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 00033 to D:ORACLE18.0.0ORADATAXEPDB3SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:RMANFULL_DB_1002713354_27P1
channel ORA_DISK_1: piece handle=D:RMANFULL_DB_1002713354_27P1 tag=TAG20190312T112718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:37:56
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 00032 to D:ORACLE18.0.0ORADATAXEPDB3SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece D:RMANFULL_DB_1002713422_30P1
channel ORA_DISK_1: piece handle=D:RMANFULL_DB_1002713422_30P1 tag=TAG20190312T112718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00034 to D:ORACLE18.0.0ORADATAXEPDB3UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:RMANFULL_DB_1002713482_35P1
channel ORA_DISK_1: piece handle=D:RMANFULL_DB_1002713482_35P1 tag=TAG20190312T112718
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 12-MAR-19
Starting recover at 12-MAR-19
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-19
sql statement: alter database datafile 32, 33, 34 online
repair failure complete
RMAN>exit
6. Open the Pluggable Database
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
Note: It will check recreate the temp file in backgroud. you can check in alert log.