8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : PDB Archive Files for Unplug and Plugin in Oracle Database 12c Release 2 (12.2)

In Oracle 12.1 a pluggable database could be unplugged to a ".xml" file, which describes the contents of the pluggable database. To move the PDB, you needed to manually move the ".xml" file and all the relevant database files. In addition to this functionality, Oracle 12.2 allows a PDB to be unplugged to a ".pdb" archive file. The resulting archive file contains the ".xml" file describing the PDB as well as all the datafiles associated with the PDB. This can simplify the transfer of the files between servers and reduce the chances of human error.

Multitenant : Unplug/Plugin

This article includes the unplug/plugin functionality available from Oracle 12.1. This was first described in the 12.1 article here.

This article assumes you are using Oracle Managed Files (OMF). If you aren't, you will need add the file name conversion information, as explained here.

Unplug PDB to ".pdb" Archive File
Plugin PDB from ".pdb" Archive File
Unplug PDB to ".xml" File
Plugin PDB from ".xml" File
Considerations

Unplug PDB to ".pdb" Archive File

Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the ".pdb" archive file.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba


ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 UNPLUG INTO '/u01/pdb5.pdb';

You see the archive file not now present.

HOST ls -al /u01/pdb5.pdb
-rw-r--r--. 1 oracle oinstall 161702502 Jan  7 21:01 /u01/pdb5.pdb

SQL>

You can delete the PDB and drop the datafile, as they are all present in the archive file.

DROP PLUGGABLE DATABASE pdb5 INCLUDING DATAFILES;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY

SQL>

Plugin PDB from ".pdb" Archive File

Plugging in a PDB into the CDB is similar to creating a new PDB. First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the archive file and the name of the PDB you want to create using it.

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/pdb5.pdb',
                pdb_name       => 'pdb5');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/
compatible

PL/SQL procedure successfully completed.

SQL>

If the PDB is not compatible, violations are listed in the PDB_PLUG_IN_VIOLATIONS view. If the PDB is compatible, create a new PDB using it as the source. If we were creating it with a new name we might do something like this.

CREATE PLUGGABLE DATABASE pdb5 USING '/u01/pdb5.pdb';

ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB5			       READ WRITE

SQL>

Unplug PDB to ".xml" File

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba


ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 UNPLUG INTO '/u01/pdb5.xml';

The pluggable database is still present, but you shouldn't open it until the metadata file and all the datafiles are copied somewhere safe.

COLUMN name FORMAT A30

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB5			       MOUNTED

SQL>

You can delete the PDB, choosing to keep the files on the file system.

DROP PLUGGABLE DATABASE pdb5 KEEP DATAFILES;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY

SQL>

Plugin PDB from ".xml" File

First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.

SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/pdb5.xml',
                pdb_name       => 'pdb5');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/
compatible

PL/SQL procedure successfully completed.

SQL>

CREATE PLUGGABLE DATABASE pdb2 USING '/u01/pdb5.xml'
  FILE_NAME_CONVERT=('/u02/app/oracle/oradata/cdb3/pdb5/','/u02/app/oracle/oradata/cdb3/pdb2/');

Instead, we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following.

CREATE PLUGGABLE DATABASE pdb5 USING '/u01/pdb5.xml'
  NOCOPY
  TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb5 OPEN READ WRITE;

SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB5			       READ WRITE

SQL>

Considerations

Having a single file to transport between servers is a lot simpler as you can't accidentally forget to transfer one or more files.
Using a zipped version of the database can reduce the total amount of network traffic involved in moving the database.
Zipping the datafiles of a large database can take a lot of time and CPU. If this is a problem, you may want to avoid this PDB archive method.
Even in Oracle 12.1 it was possible to unplug a PDB to an archive using the DBCA. Having this functionality available directly from the database just simplifies things.
If you are using Transparent Data Encryption (TDE) you will need to manage the encryption keys, which is described here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.