Create Pluggable Database using dblink

We can create a pluggable database using dblink from a remote location.

Step 1: Check container database

SQL> select name,open_mode,cdb from v$database;

NAME	  OPEN_MODE	       CDB
--------- -------------------- ---
UPDB	  READ WRITE	       YES

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  READ WRITE NO
SQL>

Step 2: Connect to a pluggable database you want to clone :

SQL> ALTER SESSION SET CONTAINER=pdb2;

Session altered.

Step 3: Create a user with which you want to clone pdb, you can use existing user too. The user should have created pluggable database privilege.

SQL> CREATE USER ADM IDENTIFIED BY "oracle";

User created.

Step 4: Grant permission if not already done

SQL> GRANT CONNECT,CREATE PLUGGABLE DATABASE TO ADM;

Grant succeeded.

Step 5: Close pluggable database

SQL> ALTER PLUGGABLE DATABASE CLOSE;

Pluggable database altered.

Step 6: Open pluggable database in the read-only state

SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Pluggable database altered.

Step 7: Connect to destination container database where you want to clone pdb, check details :

SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
DCDB	  READ WRITE

SQL> 
SQL> 
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB				  READ WRITE NO
	 4 APP_SAL			  MOUNTED
	 5 APP_PDB			  READ WRITE NO
	 7 DB1				  MOUNTED
SQL>

Step 8: Create tns entry for source pdb

pdb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-d1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb2)
    )
  )

Step 9: Create a database link using tns entry of pdb :

SQL> CREATE DATABASE LINK create_pdb CONNECT TO adm IDENTIFIED BY oracle USING 'pdb2';

Database link created.

Step 10: Check your database link is working properly :

SQL> select 1 from dual@create_pdb;

	 1
----------
	 1

Step 11: Finally, use CREATE PLUGGABLE DATABASE FROM dblink statement to create the pluggable database

SQL> CREATE PLUGGABLE DATABASE dup_pdb2 FROM pdb2@create_pdb;

Pluggable database created.

Step 12: Check pluggable database is created

SQL> show pdbs 

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB				  READ WRITE NO
	 4 APP_SAL			  MOUNTED
	 5 APP_PDB			  READ WRITE NO
	 6 DUP_PDB2			  MOUNTED
	 7 DB1				  MOUNTED

Step 13: Open pluggable database

SQL> alter pluggable database dup_pdb2 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 DB				  READ WRITE NO
	 4 APP_SAL			  MOUNTED
	 5 APP_PDB			  READ WRITE NO
	 6 DUP_PDB2			  READ WRITE NO
	 7 DB1				  MOUNTED
SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us an Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp