• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to remotely clone a PDB or non-CDB in Oracle Database 12.1

How to remotely clone a PDB or non-CDB in Oracle Database 12.1

November 23, 2018 by techgoeasy Leave a Comment

Many times, we may need to copy the remote PDB to Local PDB for testing purpose. The case would be copying Production PDB to Development PDB box.
Some times , we may want to migrate the non-CDB to PDB to make sure Multitenant features in the Oracle database

Restriction for doing this

1)The default tablespaces for each common user in the remote PDB must exist in local CDB. If this is not true, create the missing tablespaces in the root container of the local PDB. If you don’t do this your new PDB will only be able to open in restricted mode (Bug 19174942).
2) When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher

Summary of the Clone process

1) Open the source database in Read only mode
2) create database link in Target database
3) create the clone . The clone process involves moving data across the database link.

Now lets see the methods

Remote clone a PDB

Suppose we want to create PDB(test2_tech) in CDB1 from existing db test1_tech in CDB2,

a) Close the test1_tech database and open read only

alter pluggable database test1_tech close;

Pluggable database altered.

SQL> alter pluggable database test1_tech open read only;

Pluggable database altered.

b) Create tnsnames entry in Destination Server

Switch to the Destination server and create a “tnsnames.ora” entry pointing to the remote database for use in the USING clause of the database link.

TEST1_TECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tech.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1_TECH)
)
)

c) Create database link to the Source database

CREATE DATABASE LINK tech_clone_link
CONNECT TO <remote user> IDENTIFIED BY <password> USING ‘TEST1_TECH’;

remote user must be having create pluggable database privelege

— Test link.
DESC [email protected]_clone_link

b) If OMF is set i.e DB_CREATE_FILE_DEST or CREATE_FILE_DEST,

 

CREATE PLUGGABLE DATABASE
test2_tech FROM [email protected]_clone_link;

if OMF not set

CREATE PLUGGABLE DATABASE
test2_tech FROM [email protected]_clone_link
FILE_NAME_CONVERT= (‘/u01/oracle/CDB1/datafile/techdata’,
‘/u01/oracle/CDB1/datafile/tech2data’);
;

c) ReOpen the source PDB
alter pluggable database test1_tech open;

d) We can see the new PDB has been created, but it is in the MOUNTED state.

COLUMN name FORMAT A30

SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;

NAME OPEN_MODE


TEST2_TECH MOUNTED

SQL>
The PDB is opened in read-write mode to complete the process.

ALTER PLUGGABLE DATABASE TEST2_TECH OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;

NAME OPEN_MODE


TEST2_TECH READ WRITE

SQL>

Remote clone a non-CDB

Suppose we want to create PDB(test2_tech) in CDB1 from existing non-db test1_tech,

a) Close the test1_tech database and open read only

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
EXIT;

b) Create tnsnames entry in Destination Server

Switch to the Destination server and create a “tnsnames.ora” entry pointing to the remote database for use in the USING clause of the database link.

TEST1_TECH =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tech.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST1_TECH)
)
)

 

c) Create database link to the Source database

CREATE DATABASE LINK tech_clone_link
CONNECT TO <remote user> IDENTIFIED BY <password> USING ‘TEST1_TECH’;

remote user must be having create pluggable database privelege

— Test link.
DESC [email protected]_clone_link

b) If OMF is set i.e DB_CREATE_FILE_DEST or CREATE_FILE_DEST,

 

CREATE PLUGGABLE DATABASE
test2_tech FROM [email protected]_clone_link;

if OMF not set

CREATE PLUGGABLE DATABASE
test2_tech FROM [email protected]_clone_link
FILE_NAME_CONVERT= (‘/u01/oracle/CDB1/datafile/techdata’,
‘/u01/oracle/CDB1/datafile/tech2data’);
;

c) Re-Open the source PDB
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN ;
EXIT;

d) We can see the new PDB has been created, but it is in the MOUNTED state.

COLUMN name FORMAT A30

SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;

NAME OPEN_MODE


TEST2_TECH MOUNTED

SQL>

Since this PDB was created as a clone of a non-CDB, before it can be opened we need to run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean it up.

ALTER SESSION SET CONTAINER=’TEST2_TECH’;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The PDB is opened in read-write mode to complete the process.

ALTER PLUGGABLE DATABASE TEST2_TECH OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = ‘TEST2_TECH’;

NAME OPEN_MODE


TEST2_TECH READ WRITE

SQL>

With Oracle database Release 12.2,  we no longer need to keep the source PDB is read-only mode. This can be done while in read -write mode also.

Hope you like this article on Remote PDB cloning

Related Articles

Oracle database 12c:How to create Pluggable database in 12c database

Oracle database 12c: Container Database (CDB) and Pluggable Database (PDB)

Local Undo in Oracle Database 12c R2(12.2)

Oracle Database Interactive Quick Reference Guide | 11g & 12c

Online move of active datafile in 12c

Top 10 oracle 12c new features

Bestselling Udemy Courses

Oracle DBA 11g/12c – Database Administration for Junior DBA
Oracle Database 12c SQL Certified Associate 1Z0-071
Learning Oracle 12c – A Beginners Oracle Training Video

Filed Under: Oracle, Oracle Database Tagged With: remotely clone a PDB

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to use sed to remove comments and blank lines
  • How to recover database using RMAN
  • How to check Stale statistics
  • Java web start(JWS) in R12
  • How to delete the archive logs in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us