Oracle: Migrate PDB to Another Database
Explore different approaches to move the pluggable database from source to target database such as plug/unplug, data pump, refreshable clone and DBCA relocate.
Join the DZone community and get the full member experience.
Join For FreeIf 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.
Opinions expressed by DZone contributors are their own.
Comments