Pluggable Database Remote Cloning Using Database Link

Cloning a pluggable database locally or remotely has become a routine task for DBAs. Different DBAs use different methods to clone a pluggable database. The cloning may be needed on the local host, where source pluggable database exists, or requirement could be to clone to a different host. In this article, I will explain how to clone a pluggable database remotely using a database link. Click here to learn about how to clone a pluggable database on the local host (local container database).

First step is to create a user in the source PDB. This user would be used in the database link that we would create later in the target root container where this PDB would be cloned.

ALTER SESSION SET CONTAINER=sourcedb; CREATE USER clone_user IDENTIFIED BY mypassword; GRANT DBA TO clone_user;

On target host, create a TNS service in the tnsnames.ora file that resolves to the source pluggable database. Alternatively, you may use the full TNS string in the CREATE DATABASE LINK command coming later. For this article, I am creating a TNS service “to_source”.

TO_SOURCE =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = source_host)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = sourcedb)     )   )

Connect to the target root container and create a database link for connecting to the source pluggable database using database user and TNS service I just created above. A point to note is that if your init parameter GLOBAL_NAMES is set to TRUE, your database link name must exactly match with the source database (PDB).

CREATE DATABASE LINK to_source_dblink CONNECT TO clone_user IDENTIFIED BY mypassword USING ‘to_source’; -- Test the database link by executing any SQL, as follows.   SELECT count(*) FROM dba_objects@to_source_dblink

Close the source PDB, and open it as read only. Downtime is needed from this step forward.

ALTER PLUGGABLE DATABASE sourcedb CLOSE IMMEDIATE;   ALTER PLUGGABLE DATABASE sourcedb OPEN READ ONLY;

Connect to target root container and create/clone target pluggable database. For this exercise, I am creating/cloning “targetdb” using following command.

CREATE PLUGGABLE DATABASE targetdb FROM sourcedb@so_source_dblink;

 Wait until PDB cloning completes. Once it completes successfully, try opening the cloned PDB.

ALTER PLUGGABLE DATABASE targetdb OPEN;

Even if you are able to open the new PDB successfully, still it is good to check PDB_PLUG_IN_VIOLATIONS for any errors or warnings, and resolve them before handing over to end users. This will also show if datapatch needs to be executed for the newly created/cloned PDB if source and target are at a different patch level. Follow instructions in this article to learn how to execute datapatch for a newly cloned PDB

For cleaning up, you may now drop the database link you created in the target root container and database link user created in the source pluggable database.