Restoring a Dropped Oracle PDB Without a CDB Backup

by Simon Pane on Sep 28, 2021 12:00:00 AM

Background

I ran into a situation where we needed to restore just one dropped (and recreated) pluggable database (PDB) in an Oracle 19c Container Database (CDB), but we did not have any backups for CDB$ROOT, PDB$SEED, etc. Our only backup was from Recovery Manager (RMAN) before it was dropped (to be recreated from another PDB—a procedure that had to be reverted). But the container also had other PDBs which could not be stopped or taken offline—all the other PDBs needed to remain online and available.

MOS Doc ID 2034953.1 describes a restore process, but it’s based on having a backup of the container, which I didn’t have.

Could the dropped PDB be restored somehow? The answer was still yes if the PDB RMAN backup included a control file backup—which it would if RMAN was configured with “control file AUTOBACKUP ON,” which is the default setting.

It couldn’t be restored in the live/original database; it had to be restored in a secondary environment (CDB), as the control file had been updated with new file IDs and file names for the PDB in question.

Consequently, the challenges were:

How could we get the PDB data files out of an RMAN backup set?
How could we get the restored files into a container (CDB)?
How could we clone the resultant PDB back to the original container?

Important point: If the PDB backup was taken with it closed (in the “MOUNT” state) and hence was essentially an offline PDB backup, then the restore process would work fine. If the PDB was opened (even in the “READ WRITE” state) but there were no active transactions, then it still might work as the PDB process would automatically set the hidden initialization parameter “_no_recovery_through_resetlogs.” If you’re in this situation, you should review the implications of opening an Oracle database with “_no_recovery_through_resetlogs.” It might be okay if your application was quiesced when the backup was taken. It might not work perfectly if there were many transactions written to redo and archived redo logs, as there is no recovery step (only a restore step).

Getting into this situation

Of course, ideally you’ll have regular backups of the container database and adequate retention of those backups. Undoubtedly that’s best. But in other cases, you may have a Data Guard standby to protect from physical failures, you might have a cascading Data Guard standby with apply latency to protect from user errors, and you might make full offline PDB backups prior to significant activities such as replacing the database. The issue is when that last activity needs to be reverted.

The rest of this article gives a detailed step-by-step explanation of the restore process. It’s based on using Oracle Database 19c.

Example overview

In this sample environment, I have a CDB with two PDBs, each containing important information (in SYSTEM.DEMO_TABLE that’s in the USERS tablespace of each PDB). I’ll take a FULL RMAN backup of PDB1 (only), then drop it. The requirement is to restore the backup of PDB1—ven though there are no backups of CDB$ROOT— without disrupting or incurring downtime for PDB2.

The process is summarized as:

Copy all of the backup pieces from the primary environment (“server-A”) to a secondary environment (“server-B”) with the same version of Oracle Database software installed.
On “server-B,” create an instance with the same database name and start it in the NOMOUNT state.
Restore the control file(s) from the copied control file autobackup file.
MOUNT the database using the restored control file.
Restore the data files for PDB1 from the other RMAN backup set pieces.
Use another (up and running and fully usable) instance on the same server to generate the required XML metadata file and plug in the restored PDB1.
Clone the restored (and plugged in) PDB1 through a database link back to the original CDB.

Detailed example setup

First, I checked the original environment and I can see both PDBs are available:

SQL> SELECT con_id, name, guid FROM v$containers WHERE con_id > 2;

    CON_ID NAME       GUID
---------- ---------- --------------------------------
         3 PDB1       C471569CB28C0E4BE0536D3810AC346D
         4 PDB2       C471569CB2900E4BE0536D3810AC346D

SQL>

And just to prove that the process will work, I checked and made note of some sample data in both PDBs:

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL> SELECT * FROM system.demo_table;

MY_DATA
----------------------------------------
Important PDB1 data from before drop

SQL> ALTER SESSION SET container = PDB2;

Session altered.

SQL> SELECT * FROM system.demo_table;

MY_DATA
----------------------------------------
This PDB2 data must not be lost

SQL>

Using RMAN, I took an offline backup of PDB1 (note there are no other backups of this database):

RMAN> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;

using target database control file instead of recovery catalog
Statement processed

RMAN> LIST BACKUP;

specification does not match any backup in the repository

RMAN> BACKUP PLUGGABLE DATABASE PDB1;

Starting backup at 10-JUN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00083 name=+DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/system.257.1074872859
input datafile file number=00084 name=+DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/sysaux.258.1074872857
input datafile file number=00085 name=+DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/undotbs1.259.1074872857
input datafile file number=00089 name=+DATA_PDB2_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/users.261.1074873435
channel ORA_DISK_1: starting piece 1 at 10-JUN-21
channel ORA_DISK_1: finished piece 1 at 10-JUN-21
piece handle=+FRA_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/BACKUPSET/2021_06_10/nnndf0_tag20210610t165853_0.270.1074877135 tag=TAG20210610T165853 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-JUN-21

Starting Control File and SPFILE Autobackup at 10-JUN-21
piece handle=+FRA_DG/ORA1/AUTOBACKUP/2021_06_10/s_1074877159.257.1074877165 comment=NONE
Finished Control File and SPFILE Autobackup at 10-JUN-21

RMAN>

Now I dropped the PDB1 database:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED    NO
         4 PDB2                           READ WRITE NO

SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING data files;

Pluggable database dropped.

SQL>

Trying to simply restore it gives an error, as the PDB1 no longer exists:

RMAN> restore pluggable database PDB1;

Starting restore at 10-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/10/2021 17:02:38
RMAN-06813: could not translate pluggable database PDB1

RMAN>

When I tried creating a new PDB1 (using exactly the same name), it still doesn’t help and I got the error:

RMAN> restore pluggable database PDB1;

Starting restore at 10-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/10/2021 17:17:13
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 93 found to restore
RMAN-06023: no backup or copy of datafile 92 found to restore
RMAN-06023: no backup or copy of datafile 91 found to restore

RMAN>

Those are data files from a different PDB that just happens to have the same name of “PDB1”; the PDB guide, the file numbers and file names are different. A restore isn’t going to work and we can drop this “new” PDB1.

Restore process

Since we can’t restore into the original CDB on the original server, we’ll restore it in a secondary environment. The first step is to move the RMAN backup set pieces to that secondary server.

There are lots of ways to move the RMAN backup set pieces (including possibly hardware disk clones). In this demo environment, I first copied them out of ASM:

$ asmcmd cp +FRA_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/BACKUPSET/2021_06_10/nnndf0_tag20210610t165853_0.270.1074877135 /tmp
copying +FRA_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/BACKUPSET/2021_06_10/nnndf0_tag20210610t165853_0.270.1074877135 -> /tmp/nnndf0_tag20210610t165853_0.270.1074877135

$ asmcmd cp +FRA_DG/ORA1/AUTOBACKUP/2021_06_10/s_1074877159.257.1074877165 /tmp
copying +FRA_DG/ORA1/AUTOBACKUP/2021_06_10/s_1074877159.257.1074877165 -> /tmp/s_1074877159.257.1074877165

Then I scp (not shown) them to the /tmp directory of the remote server.

On the secondary server, I need an initialization file, which can be copied from the primary server. I just need to start an instance with the same name so I can use RMAN to get the data files out of the backup set pieces.

The process is pretty simple: restore the control file from the copied control file autobackup, catalog the copied backup pieces and restore the data files. One additional point: To have RMAN restore the data files into the desired ASM disk group, I also need to set the db_create_file_dest parameter:

RMAN> STARTUP NOMOUNT;

Oracle instance started

Total System Global Area    2634018936 bytes

Fixed Size                     9138296 bytes
Variable Size               1174405120 bytes
Database Buffers            1442840576 bytes
Redo Buffers                   7634944 bytes

RMAN> ALTER SYSTEM SET db_create_file_dest='+DATA_PDB1_DG' SCOPE=memory;

using target database control file instead of recovery catalog
Statement processed

RMAN> RESTORE control file FROM '/tmp/s_1074877159.257.1074877165';

Starting restore at 10-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output file name=+DATA_DG/ORA1/control file/current.269.1071227145
output file name=+FRA_DG/ORA1/control file/current.256.1071227145
Finished restore at 10-JUN-21

RMAN> ALTER DATABASE MOUNT;

released channel: ORA_DISK_1
Statement processed

RMAN> CATALOG BACKUPPIECE '/tmp/nnndf0_tag20210610t165853_0.270.1074877135';

Starting implicit crosscheck backup at 10-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 10-JUN-21

Starting implicit crosscheck copy at 10-JUN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-JUN-21

searching for all files in the recovery area
cataloging files...
no files cataloged

channel ORA_DISK_1: cataloged backup piece
backup piece handle=/tmp/nnndf0_tag20210610t165853_0.270.1074877135 RECID=41 STAMP=1074879938

RMAN> RESTORE PLUGGABLE DATABASE PDB1;

Starting restore at 10-JUN-21
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 00083 to +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/system.257.1074872859
channel ORA_DISK_1: restoring datafile 00084 to +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/sysaux.258.1074872857
channel ORA_DISK_1: restoring datafile 00085 to +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/undotbs1.259.1074872857
channel ORA_DISK_1: restoring datafile 00089 to +DATA_PDB2_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/users.261.1074873435
channel ORA_DISK_1: reading from backup piece /tmp/nnndf0_tag20210610t165853_0.270.1074877135
channel ORA_DISK_1: piece handle=/tmp/nnndf0_tag20210610t165853_0.270.1074877135 tag=TAG20210610T165853
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 10-JUN-21

RMAN>

Now I can see the restored data files are in ASM:

$ asmcmd ls '+DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/'
SYSAUX.258.1074880029
SYSTEM.259.1074880009
UNDOTBS1.257.1074880039
USERS.256.1074880047
$

The first objective is accomplished—I managed to get the files out of the RMAN backup set pieces. Now the data files exist, but I have no CDB to attach them to.

The next step is to add those data files as a new PDB in another instance. For that I use a second instance, this one called ORA2.

I connected to the ORA2 instance and used DBMS_PDB.RECOVER to build the XML manifest file that was needed to plug in the restore database.

SQL> BEGIN
  2     DBMS_PDB.RECOVER (
  3        pdb_descr_file    =>   '/home/oracle/pdb1_recover.xml',
  4        pdb_name          =>   'PDB1',
  5        filenames         =>   '
  6  +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/SYSTEM.259.1074880009,
  7  +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/SYSAUX.258.1074880029,
  8  +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/UNDOTBS1.257.1074880039,
  9  +DATA_PDB1_DG/ORA1/C471569CB28C0E4BE0536D3810AC346D/DATAFILE/USERS.256.1074880047'
 10     );
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>

Then I used that to plug in the restored PDB1:

SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/pdb1_recover.xml' NOCOPY;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ WRITE YES
         3 PDB1                           MOUNTED
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ WRITE YES
         3 PDB1                           MIGRATE    YES
SQL>

In the above steps, it’s standard to have it open in MIGRATE mode initially, as the new CDB may have a slightly different patch level, there may be mismatches in CDB/PDB components installed, or there might be initialization parameter issues. Check PDB_PLUG_IN_VIOLATIONS if you need to inspect the specific details.

Also, if the restored files aren’t consistent, the operation will automatically set the “_no_recovery_through_resetlogs” parameter. Whether this is going to be problematic depends on your exact situation and whether your data is essentially consistent even though the data files are not.

To resolve the MIGRATE mode status, simply close the PDB and open it again:

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ WRITE YES
         3 PDB1                           READ WRITE NO
SQL>

Now we have the PDB1 database restored and in a usable state.

But this is on the secondary ORA2 instance. The final step is to copy it back to the original ORA1 CDB.

PDB copy back to the original CDB

The last part is to follow a standard PDB copy process to copy the restored PDB1 back to the original CDB. This can be done in any number of ways, including another unplug, copy, plug in. However, in this example, I simply copied it back through a database link.

I created a database link as the SYSTEM user with password synchronized between CDBs ORA1 and ORA2, but any common user should do for your purposes.

Note that as per MOS Doc ID 2485839.1, the user requires that the CREATE PLUGGABLE DATABASE privilege be explicitly granted; otherwise, an ORA-01031 error might be encountered.

As SYSTEM in my case, I created a database link on the original ORA1 CDB going to the secondary environment ORA2 CDB:

SQL> CREATE DATABASE LINK pdb_copy_link USING '172.16.56.110:1521/ORA2';

Database link created.

SQL> SELECT * FROM dual@pdb_copy_link;

D
-
X

SQL>

And still using SYSTEM, I copied (clone) the PDB:

SQL> connect system
Enter password:
Connected.
SQL> ALTER SYSTEM SET db_create_file_dest = '+DATA_PDB1_DG' SCOPE=memory;

System altered.

SQL> CREATE PLUGGABLE DATABASE PDB1 FROM PDB1@pdb_copy_link;

Pluggable database created.

SQL>
connect / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDB2                           READ WRITE NO
         5 PDB1                           READ WRITE NO
SQL>

Lastly, I validated that the original PDB1 data is there (hence the restore must have worked) and that the data in PDB2 remains unaffected (as hoped):

SQL> ALTER SESSION SET container = PDB1;

Session altered.

SQL> SELECT * FROM system.demo_table;

MY_DATA
----------------------------------------
Important PDB1 data from before drop

SQL> ALTER SESSION SET container = PDB2;

Session altered.

SQL> SELECT * FROM system.demo_table;

MY_DATA
----------------------------------------
This PDB2 data must not be lost

SQL>

Reminder: You likely need to manually add new temp files to all temporary tablespaces.

Conclusion

Restoring a PDB RMAN backup is possible even when there are no CDB$ROOT backups to work with as long as a control file backup is available—which there usually is thanks to the control file autobackup RMAN default. It also requires that your data be consistent—which it hopefully will be because the PDB backup you’re restoring was from an offline backup taken with the PDB in the mounted state.

Given that, the restoration certainly is possible. But it does take some extra steps and the leveraging of a secondary environment.

Topics: Oracle Multitenant Technical Track Cdb Rman Pdb

No Comments Yet

Let us know what you think

Share this