Oracle: Migrate PDB to Another Database

If you want to migrate or relocate the PDB from one database to another, there are multiple options available in Oracle. Here, we will discuss a few of them. The source and target database can be in a standalone database, RAC, cloud, or autonomous database. After verifying the PDB is on the target, open it for customer access and remove it from the source database based on company policy. 

Prerequisites

• The target database version should same or higher than the source database.
• The source database should be accessible from the target.
• The degree of parallelism should be calculated properly.
• Aware of DBA privileged username/password on the source to create DB link
• The encryption key is different from the user password. Must have access to the encryption key - it may be either database or tablespace or table level
• The user in the remote database that the database link connects to, must have the CREATE PLUGGABLE DATABASE privilege.
• The character set on source and target should be compatible.

Known Issues

• Tablespace may be in a big file.
• Tablespace may be encrypted.
• Using a database link, the target database should be able to access the source database. Create an Access Control List (ACL) or whitelist the IP address and port if required.
• To access the DB link, either enter the source database information in TNSnames.ora or give a full connection string.
• Stable network connectivity between source and target
• RMAN jobs may interfere with refreshable cloning.
• Port from source to target should be opened to copy the files or to access the DB link
• Remote CDB uses local undo mode. Otherwise, remote PDB may be opened in read-only mode. 
• Copy/cloning/synchronization between source and target may vary by network traffic and speed. 

A few of the approaches are as follows:

Approach 1: Unplug and Plug the PDB

In this approach, unplug the PDB into an XML file, copy data files and XML files to the target host, and create a pluggable database using the XML file. Find abstract steps in the image shown below:

The steps are:

Step 1

Unplug the PDB on the source. As we are unplugging the PDB, there is no need to close it.

ALTER PLUGGABLE DATABASE <source_pdb> 
     UNPLUG INTO '<path>/source_pdb.xml';

Step 2

Copy the XML file and data files on the target host manually. For Windows, copy, ftp, or similar commands can be used. For Linux, scp , rsync, ftp, or similar commands can be used.

Step 3

Plug in the PDB to target. It will retain the parameters from the source PDB.

CREATE PLUGGABLE DATABASE <target_pdb> 
    USING '<target-xml-path>/source_pdb.xml'
    NOCOPY TEMPFILE REUSE;

Step 4

Open the PDB in read-write mode on the target.

ALTER PLUGGABLE DATABASE <target_pdb> 
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <target_pdb>
    SAVE STATE
    INSTANCES=ALL;

Approach 2: Data Pump Export (expdp) and Data Pump Import (impdp)

Using data pump export and import, you can copy the PDB from one database to another. This is best for an offline copy of the PDB. During online copying, there may be overhead to continuously generate the data pump file based on Oracle System Change Number (SCN). The target PDB should exist on the target database. An overview is shown in the image below:

Step 1

Create a database directory on the source.

CREATE OR REPLACE DIRECTORY export_dir AS '<PATH>';

Step 2

Grant the directory on the source.

GRANT READ, WRITE ON DIRECTORY export_dir TO system;

Step 3

Perform data pump export on the source.

expdp system/password@<source_pdb_connect_string> \
    full=Y \
    directory=export_dir \
    dumpfile=<source_pdb>.dmp \
    logfile=expdp_<source_pdb>.log

Step 4

Move the files from the source to the target manually. Please use a project project-approved method to transfer the files. For Linux, this can be achieved through rsync, scp, or ftp commands.

Step 5

Create a directory on the target database.

CREATE OR REPLACE DIRECTORY import_dir AS '<PATH>';

Step 6

Grant the directory on the target database.

GRANT READ, WRITE ON DIRECTORY import_dir TO system;

Step 7

Import data on the target database using data pump import.

impdp system/password@<target_pdb> \
    full=Y \
    directory=import_dir \
    dumpfile=<source_pdb>.dmp \
    logfile=impdp_<source_pdb>.log

Step 8

Clean up the directories on the source and target databases.

-- Source
drop directory export_dir;
-- Target
drop directory import_dir;

Step 9

Open the PDB in read-write mode on the target database.

ALTER PLUGGABLE DATABASE <target_pdb> 
    OPEN 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <target_pdb>
    SAVE STATE 
    INSTANCES=ALL;

Approach 3: Refreshable Clone

In this approach, create the PDB on the target database and refresh it after every certain interval using the database link. This will be an online operation. The last phase will be the offline operation, where you need to bring the source PDB in read-only, refresh manually, and open the target PDB.

Step 1

Confirm whether PDB is opened on the source database.

SELECT count(*) cnt FROM v$pdbs 
  WHERE name='<source_pdb>' 
    AND open_mode='READ WRITE' 
    AND restricted='NO'

Step 2

Create a database link on the target database.

CREATE DATABASE LINK Target_to_Source_DBLink 
    CONNECT TO SYSTEM 
    IDENTIFIED BY <source_db_password> 
    USING 'Source_IP_Address:Source_Port/Source_CDB';

Step 3

Confirm whether the database link is accessible on the target.

SELECT sysdate FROM dual@Target_to_Source_DBLink

Step 4

Create a refreshable clone PDB on target.

CREATE PLUGGABLE DATABASE <target_pdb>
    FROM <source_pdb>@Target_to_Source_DBLink 
    REFRESH MODE EVERY <refresh_frequency> MINUTES 
    KEYSTORE IDENTIFIED BY <TDE_Wallet_Password> 
    create_file_dest=<Target_Dir_Path>;

Step 5 

Check the PDB refresh lag status on target.

SELECT 
    last_refresh_scn,
    cast(scn_to_timestamp(last_refresh_scn) as date) refresh_Time 
FROM dba_pdbs 
  WHERE pdb_name = '<TARGET_PDB>'

Step 6

Make PDB read-only mode (all instances) on source.

ALTER PLUGGABLE DATABASE <source_pdb> 
    CLOSE IMMEDIATE
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <source_pdb>
    OPEN READ ONLY 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <source_pdb>
    SAVE STATE 
    INSTANCES=ALL;

Step 7

Set refresh mode to manual on target.

ALTER PLUGGABLE DATABASE <TARGET_PDB> REFRESH MODE MANUAL;

Step 8

Complete the refresh on target (the final refresh).

ALTER SESSION SET CONTAINER = <TARGET_PDB>;
ALTER PLUGGABLE DATABASE REFRESH;

Step 9

Wait for some time to finish the manual refresh. Lag can be verified using the query mentioned above in step 5.

Step 10

Set refresh mode to none on target.

ALTER PLUGGABLE DATABASE <TARGET_PDB> 
    REFRESH MODE NONE;

Step 11

Open the PDB in read-write mode on target.

ALTER PLUGGABLE DATABASE <target_pdb> 
    CLOSE IMMEDIATE 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <target_pdb>
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <target_pdb>
    SAVE STATE 
    INSTANCES=ALL;

Step 12

Drop the database link on target.

DROP DATABASE LINK Target_to_Source_DBLink;

Approach 4: Relocate PDB Using DBCA

The Database Configuration Assistant (DBCA) can be used to relocate a pluggable database (PDB) in Oracle 19c and above. It will be an online operation. For this, the PDB should be in archivelog mode and local undo should be enabled.

Step 1

Create a common user in the target database.

CREATE USER c##remote_clone_user 
    IDENTIFIED BY <password> 
    CONTAINER=ALL;
GRANT 
    CREATE SESSION, 
    SYSOPER, 
    CREATE PLUGGABLE DATABASE 
  TO c##remote_clone_user 
  CONTAINER=ALL;

Step 2

Verify the remote CDB is in local undo mode and archivelog mode on the target database.

SELECT property_name, property_value 
FROM database_properties 
WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

Step 3

Add a TNS entry in the source database.

TARGET_CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <target-host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TARGET_CDB)
    )
  )

Step 4

Create a public database link in the local CDB, pointing to the remote CDB.

CREATE PUBLIC DATABASE LINK target_to_source_db_link 
    CONNECT TO c##remote_clone_user 
    IDENTIFIED BY <password> 
    USING 'TARGET_CDB';

Step 5

Login to the source host and switch to Oracle user.

Step 6

Set the environment variable or set the oracle_sid environment variable to the source database.

Step 7

Run dbca (database configuration assistant) command in silent mode.

dbca -silent \
    -relocatePDB \
    -pdbName <source pdb name> \
    -sourceDB <source database name> \
    -remotePDBName <target pdb name> \
    -remoteDBConnString <target host>:<port>/target_cdb \
    -remoteDBSYSDBAUserName sys \
    -remoteDBSYSDBAUserPassword <password> \
    -dbLinkUsername c##remote_clone_user \
    -dbLinkUserPassword <password>

Step 8

Open the target PDB.

ALTER PLUGGABLE DATABASE <target_pdb> 
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE <target_pdb>
    SAVE STATE
    INSTANCES=ALL;

Conclusion: Post-Steps

• If the source database version is lower than the target database, upgrade the PDB (apply the data patch).
• Make sure the invalid object count is the same in both source and target databases.
• If the source and target PDB are on the same host, confirm the service is pointing to the correct PDB.
• Clean up the PDB from the source database based on the company retention policy.