How to Unplug an Oracle Database PDB into a .pdb File and Replug into Another Container Database

Oracle’s Multitenant architecture allows you to unplug a Pluggable Database (PDB) from one Container Database (CDB) and plug it into another, simplifying database migration and management. This quick guide walks through the process of unplugging a PDB into a .pdb archive file and replugging it into another CDB. We will be using the gvenzl/oracle-free images to demonstrate multiple Oracle Database environments.

• Prerequisites
• Step 1: Set Up the Source CDB Podman Container
• Step 2: Unplug the PDB into a .pdb File
• Step 3: Set Up the Target CDB Podman Container
• Step 4: Plug the PDB into the Target CDB
• Step 5: Test the Plugged PDB
• Cleanup (Optional)
• Tips and Considerations
• Conclusion

Prerequisites

• Podman: Installed and running on your system.
• gvenzl/oracle-free Podman Image: We’ll use the gvenzl/oracle-free:23-slim image for Oracle Database 23ai Free.
• Basic familiarity with Oracle SQL*Plus and Podman commands.
• Ensure you have sufficient disk space for the .pdb archive file.

Step 1: Set Up the Source CDB Podman Container

1. Create a location for the .pdb file:

Because this is demonstrated with two Podman containers, we will need an external volume to save and import the .pdb file

mkdir /tmp/pdb_file
chmod a+rw /tmp/pdb_file

2. Run the Source CDB Container:

Start a container for the source CDB, mapping a host directory to persist data and exposing port 1521.

podman run -d --name source-cdb \
  -p 1521:1521 \
  -v /tmp/pdb_file:/opt/oracle/pdb_file \
  -e ORACLE_PASSWORD=LetsTest1 \
  gvenzl/oracle-free:23-slim

3. Verify the Container:

Check that the container is running and the database is ready.

podman logs source-cdb

Look for a message indicating the database is open (e.g., DATABASE IS READY TO USE!).

4. Connect to the Source CDB:

Use SQL*Plus to connect as sysdba.

podman exec -it source-cdb sqlplus / as sysdba

5. Create a Sample PDB:

Create a PDB named PDB1 in the source CDB for this demo.

CREATE PLUGGABLE DATABASE pdb1
  ADMIN USER pdbadmin IDENTIFIED BY LetsTest1
  FILE_NAME_CONVERT=('pdbseed', 'pdb1');

ALTER PLUGGABLE DATABASE pdb1 OPEN;

6. Create a dummy table in the PDB:

Create a dummy table in the new pdb1 to check whether it is still there in the target database.

ALTER SESSION SET CONTAINER=pdb1;

CREATE TABLE my_dummy_table (id NUMBER);

INSERT INTO my_dummy_table VALUES (1), (2), (3);
COMMIT;

Step 2: Unplug the PDB into a .pdb File

1. Close the PDB:

The PDB must be closed before unplugging.

ALTER SESSION SET CONTAINER=CDB$ROOT;

ALTER PLUGGABLE DATABASE pdb1 CLOSE;

2. Unplug the PDB:

Unplug pdb1 into a .pdb archive file. The .pdb file includes the PDB’s metadata (XML) and datafiles, simplifying transfer.

ALTER PLUGGABLE DATABASE pdb1
  UNPLUG INTO '/opt/oracle/pdb_file/pdb1.pdb';

3. Verify the .pdb file:

Check that the .pdb file was created in the container.

podman exec source-cdb ls -lh /opt/oracle/pdb_file/pdb1.pdb

4. Drop the PDB (Optional):

If you no longer need pdb1 in the source CDB, drop it, including the datafiles (already in the .pdb archive).

DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;

Step 3: Set Up the Target CDB Podman Container

1. Run the Target CDB Container:

Start a new container for the target CDB, using a different name and port to avoid conflicts.

podman run -d --name target-cdb \
  -p 1522:1521 \
  -v /tmp/pdb_file:/opt/oracle/pdb_file \
  -e ORACLE_PASSWORD=LetsTest2 \
  gvenzl/oracle-free:23-slim

2. Verify the Container:

Check that the container is running and the database is ready.

podman logs target-cdb

Look for a message indicating the database is open (e.g., DATABASE IS READY TO USE!).

Step 4: Plug the PDB into the Target CDB

1. Connect to the Target CDB:

Use SQL*Plus to connect to the target CDB.bash

podman exec -it target-cdb sqlplus / as sysdba

2. Check Compatibility:

Verify that the unplugged PDB is compatible with the target CDB.

SET SERVEROUTPUT ON
DECLARE
  compatible BOOLEAN;
BEGIN
  compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
    pdb_descr_file => '/opt/oracle/pdb_file/pdb1.pdb'
  );
  IF compatible THEN
    DBMS_OUTPUT.PUT_LINE('PDB is compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('PDB is not compatible');
  END IF;
END;
/

If the output indicates compatibility issues, check the PDB_PLUG_IN_VIOLATIONS view for details.

3. Plug in the PDB:

Create a new PDB using the .pdb archive file. The COPY option copies the datafile it a new location (default).

CREATE PLUGGABLE DATABASE pdb1
  USING '/opt/oracle/pdb_file/pdb1.pdb'
  COPY
  FILE_NAME_CONVERT=('/opt/oracle/pdb_file', '/opt/oracle/oradata/FREE/PDB1');

4. Open the PDB:

Open the PDB to make it accessible.

ALTER PLUGGABLE DATABASE pdb1 OPEN;

5. Verify the PDB:

Check the status of the PDBs in the target CDB.

SELECT name, open_mode FROM v$pdbs;

You should see PDB1 in READ WRITE mode.

Step 5: Test the Plugged PDB

1. Connect to the PDB:

Switch to pdb1 to verify its contents.

ALTER SESSION SET CONTAINER=pdb1;
SELECT * FROM my_dummy_table;

You will see the data from the previously created dummy table.

Cleanup (Optional)

If you’re done with the demo, stop and remove the containers and delete the temporary directory:

podman stop source-cdb target-cdb
podman rm source-cdb target-cdb
rm -rf /tmp/pdb_file

Tips and Considerations

• Compatibility: Ensure the source and target CDBs have the same endianness, character sets, and installed options.
• Storage: The .pdb archive can be large for big databases. Ensure sufficient disk space for the archive location.
• Security: Use strong passwords in production and consider Transparent Data Encryption (TDE) for sensitive data, as noted in Oracle’s documentation.
• Alternative Methods: You can use an XML file (.xml) with separate datafiles instead of a .pdb archive, but the .pdb simplifies file management, especially for databases with many datafiles.

Conclusion

With Oracle Database, you can easily unplug a PDB into a .pdb archive and plug it into another CDB. This process is ideal for database migrations, testing, or maintaining “gold image” PDBs.

For more details, check Oracle’s official documentation on PDB unplug/plug operations.

Happy database plugging!