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

Home » Articles » 12c » Here

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.

Related articles.

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

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 XML metadata 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>

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 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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.