Recovering PDBs Using Preplugin Backups

Before You Begin

This 15-minute tutorial shows how to recover a pluggable database (PDB), unplugged and plugged from a container database (CDB) into another CDB, by using preplugin backups.

Preplugin backups are all backups of a PDB taken before unplugging/plugging operations, usable after plugging a PDB into a new CDB.

Background

In Oracle Database 12c, backups taken for a PDB before the unplugging and plugging operation cannot be reused after plugging the PDB into another CDB.

In Oracle Database 18c, backups taken for a PDB before the unplugging and plugging operation can be reused to recover the PDB even if no other backups have been completed in the target CDB.

What Do You Need?

Familiarity with PDB unplugging and plugging operations
Oracle Database 18c installed
Two CDBs, ORCL and cdb1 with one PDB opened in cdb1: PDB1
The HR schema that is installed in the non-CDB is an example of application tables or any other table that you created. If you want to use the HR.EMPLOYEES table, use the hr.sql script. Download the SQL script to the labs directory created on your server /home/oracle/labs. In the script, update the password of the user connected to the database

Prepare PDB Before Unplugging to Use Preplugin Backups

In this section, you'll use a new procedure to export all RMAN backup metadata that belong to the PDB before the unplug operation. The metadata is transported along with the PDB during the unplug operation.

sqlplus system@PDB1 
Enter password: password

Use the hr.sql script to create the HR user and DEPARTMENTS table.
```
@/home/oracle/labs/hr.sql
```
Query the HR.DEPARTMENTS table and ensure that the output is similar to code1.
```
SELECT * FROM hr.departments;
```
In another terminal window, launch rman. This is Session2.
```
export ORACLE_SID=cdb1
rman target /
```
Create backups for PDB1 of cdb1 before the unplug operation. It is recommended that you back up all the archivelogs before the unplug operation so that they can be restored from the preplugin backups when a recovery might be required in the destination CDB. A sample of the RMAN session is available in code1a.
```
BACKUP PLUGGABLE DATABASE pdb1 PLUS ARCHIVELOG;
```
Quit the rman session.
```
EXIT
```
In Session1, export the RMAN backup information that belongs to PDB1 to its dictionary before the unplug operation so that the preplugin backups can be used in the target CDB even if no new backup will be performed after you plug the PDB. The metadata is transported along with the PDB during the migration.
```
EXEC dbms_pdb.exportrmanbackup('pdb1')
```

Unplug and Plug the PDB

In this section, you'll unplug and plug PDB1 and verify that the preplugin backups are cataloged.

Connect to cdb1 as SYSDBA.
```
CONNECT / AS SYSDBA
```
Close the PDB before the unplug operation.
```
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
```

Unplug PDB1 from cdb1.

ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml';

Archive the current redo log.
```
ALTER SYSTEM SWITCH LOGFILE;
```
Query the V$ARCHIVED_LOG view and ensure that the output is similar to code2 to retrieve the last archive log file name will have to be cataloged later in Section2.
```
SELECT name, next_change#, sequence# FROM V$ARCHIVED_LOG ORDER BY 3;
```
Quit the session.
```
EXIT
```
Before you plug PDB1, create a directory for the new PDB datafiles.
```
mkdir /u02/app/oracle/oradata/ORCL/orcl_pdb1
```

sqlplus sys@ORCL AS SYSDBA
Enter password: password

Plug PDB1 as ORCL_PDB1 into ORCL.

CREATE PLUGGABLE DATABASE orcl_pdb1 USING '/tmp/pdb1.xml'
                  CREATE_FILE_DEST='/u02/app/oracle/oradata/ORCL/orcl_pdb1'
                  COPY;

Open ORCL_PDB1.

ALTER PLUGGABLE DATABASE orcl_pdb1 OPEN;

In the rman terminal window, set the ORACLE_SID to ORCL.
```
export ORACLE_SID=ORCL
```
Launch a new rman session.
```
rman target /
```
Check that the preplugin backups for ORCL_PDB1 are cataloged in ORCL. First set the container name that you want the list of preplugin backups.
```
SET PREPLUGIN CONTAINER=orcl_pdb1;
```
Execute the following command to retrieve the list of preplugin backups for ORCL_PDB1 and observe the output is similar to code3.
```
LIST PREPLUGIN BACKUP;
```
Execute the following command to retrieve the list of preplugin archive log files for ORCL_PDB1 and ensure that the output is similar to code4.
```
LIST PREPLUGIN ARCHIVELOG ALL;
```
Verify if the last redo log file has been archived, previously recorded, or been cataloged. If it was not cataloged it is because it was created after the PDB was unplugged. Catalog it and change the file reference to match the recorded file. Ensure that the output is similar to that shown below.

CATALOG PREPLUGIN ARCHIVELOG '/u03/app/oracle/fast_recovery_area/CDB1/archivelog/2018_01_16/o1_mf_1_97_f5vj3vmx_.arc';

cataloged archived log
archived log file name=/u03/app/oracle/fast_recovery_area/CDB1/archivelog/2018_01_16/o1_mf_1_97_f5vj3vmx_.arc RECID=96 STAMP=0

Verify that the cataloged preplugin archive log files are available on disk and ensure that the output is similar to code5.
```
CROSSCHECK PREPLUGIN ARCHIVELOG ALL;
```

Verify that the cataloged preplugin backups are available on disk.

CROSSCHECK PREPLUGIN BACKUP;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/app/oracle/fast_recovery_area/CDB1/backupset/2018_01_16/o1_mf_annnn_TAG20180116T090329_f5vhz21w_.bkp RECID=6 STAMP=965552609
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/app/oracle/fast_recovery_area/CDB1/62CEFFD0369E09BBE0532133960A4225/backupset/2018_01_16/o1_mf_nnndf_TAG20180116T090415_f5vj0j1d_.bkp RECID=7 STAMP=965552655
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/app/oracle/fast_recovery_area/CDB1/backupset/2018_01_16/o1_mf_annnn_TAG20180116T090451_f5vj1n2f_.bkp RECID=8 STAMP=965552692
Crosschecked 3 objects

Recover From Preplugin Backups

In this section, you'll have to recover ORCL_PDB1 after a datafile loss. There has been no new backup completed since the plug operation. You will use the preplugin backups.

In Session1, consider adding the new service name into the tnsnames.ora file before you connect to the plugged PDB to find a datafile to remove.
```
CONNECT sys@ORCL_PDB1 AS SYSDBA
Enter password: password
```
Find a datafile to remove from V$DATAFILE. Ensure that the output is similar to code6.

SELECT name FROM v$datafile;

Intentionally remove one of these files. In this example, the UNDO tablespace of the PDB is removed.

HOST rm /u02/app/oracle/oradata/ORCL/orcl_pdb1/ORCL/62CEFFD0369E09BBE0532133
960A4225/datafile/o1_mf_undotbs1_f5vjm6mg_.dbf

Reconnect to ORCL_PDB1 as HR and view the error messages from code7.
Connect to ORCL to close ORCL_PDB1, if it is not already closed.
```
CONNECT sys@ORCL AS SYSDBA
Enter password: password
```

Close ORCL_PDB1.

ALTER PLUGGABLE DATABASE orcl_pdb1 CLOSE;

To recover the situation, because there is no new backup completed after the plugin operation, you will use the preplugin backups. The datafiles that are restored from backups were taken before the PDB was plugged in. In the Session2, first set the session to the container that you want to restore and recover.
```
SET PREPLUGIN CONTAINER=orcl_pdb1;
```

Restore and recover ORCL_PDB1 from preplugin backups. Ensure that the output is similar to code8.

RUN {
     RESTORE PLUGGABLE DATABASE orcl_pdb1 FROM PREPLUGIN;
     RECOVER PLUGGABLE DATABASE orcl_pdb1 FROM PREPLUGIN;
}

Run a normal recovery after preplugin recovery in order to apply the redo log files of the CDB on the PDB.

RECOVER PLUGGABLE DATABASE orcl_pdb1;

Starting recover at 16-JAN-18
using channel ORA_DISK_1

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

Finished recover at 16-JAN-18

Open ORCL_PDB1.

ALTER PLUGGABLE DATABASE orcl_pdb1 OPEN;

Quit the session.
```
EXIT
```
In the Session1, verify the content of HR.DEPARTMENTS table in ORCL_PDB1.
```
CONNECT hr@ORCL_PDB1
Enter password: password
```
Query HR.DEPARTMENTS table and ensure that the output is similar to code1.
```
SELECT * FROM hr.departments;
```

Clean up the Tutorial Environment

Still in Session1, drop PDB_ORCL1 that was created in a previous section. Reconnect to the CDB root as SYSDBA.
```
CONNECT sys@ORCL AS SYSDBA
Enter password: password
```

Close ORCL_PDB1.

ALTER PLUGGABLE DATABASE orcl_pdb1 CLOSE;

Drop the PDB.

DROP PLUGGABLE DATABASE orcl_pdb1 INCLUDING DATAFILES;

Quit the session.
```
EXIT
```