Restore pluggable database from RMAN CDB backup in Oracle

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:\ORACLE\18.0.0\ORADATA\XE\PDB3\UNDOTBS01.DBF'

Solution
We are using RMAN Advisory for recovery the pluggable database from RMAN Backup.

1. Connect with RMAN

C:\Users\oracle>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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SY
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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SYSTEM01.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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SYSTEM01.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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SYSAUX01.DBF was unintentionally renamed or moved, restore it
4. If file D:\ORACLE\18.0.0\ORADATA\XE\PDB3\UNDOTBS01.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:\ORACLE\18.0.0\diag\rdbms\xe\xe\hm\reco_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:\ORACLE\18.0.0\diag\rdbms\xe\xe\hm\reco_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:\ORACLE\18.0.0\diag\rdbms\xe\xe\hm\reco_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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN\FULL_DB_1002713354_27P1
channel ORA_DISK_1: piece handle=D:\RMAN\FULL_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:\ORACLE\18.0.0\ORADATA\XE\PDB3\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN\FULL_DB_1002713422_30P1
channel ORA_DISK_1: piece handle=D:\RMAN\FULL_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:\ORACLE\18.0.0\ORADATA\XE\PDB3\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN\FULL_DB_1002713482_35P1
channel ORA_DISK_1: piece handle=D:\RMAN\FULL_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.

Advertisements

1 thought on “Restore pluggable database from RMAN CDB backup in Oracle

  1. Pingback: ORA-01157: cannot identify/lock data file | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.