Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)

In the initial release of Oracle Database 12c Release 1 (12.1.0.1) remote cloning of PDBs was listed as a feature, but it didn't work. The 12.1.0.2 patch has fixed that, but also added the ability to create a PDB as a clone of a remote non-CDB database. This provides an alternative to the initial migration path from non-CDB to PDB described here.

Related articles.

Prerequisites

The prerequisites for cloning a remote PDB or non-CDB are very similar, so I will deal with them together.

Note. In this context, the word "local" refers to the destination or target CDB that will house the cloned PDB. The word "remote" refers to the PDB or non-CDB that is the source of the clone.

In the examples below I have three databases running on the same virtual machine, but they could be running on separate physical or virtual servers.

Cloning a Remote PDB

Connect to the remote CDB and prepare the remote PDB for cloning.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a user in the remote database for use with the database link. In this case, we will use a local user in the remote PDB.

ALTER SESSION SET CONTAINER=pdb5;

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

Open the remote PDB in read-only mode.

ALTER PLUGGABLE DATABASE pdb5 CLOSE;
ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;
EXIT;

Connect to the local database to initiate the clone.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

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

DROP DATABASE LINK clone_link;

CREATE DATABASE LINK clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'pdb5';

-- Test link.
DESC user_tables@clone_link

Create a new PDB in the local database by cloning the remote PDB. In this case we are using Oracle Managed Files (OMF), so we don't need to bother with FILE_NAME_CONVERT parameter for file name conversions.

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

Pluggable database created.

SQL>

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

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        MOUNTED

SQL>

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

ALTER PLUGGABLE DATABASE pdb5new OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        READ WRITE

SQL>

As with any PDB clone, check common users and the temporary tablespace is configured as expected.

Cloning a Remote Non-CDB

Connect to the remote database to prepare it for cloning.

export ORAENV_ASK=NO
export ORACLE_SID=db12c
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a user in the remote database for use with the database link.

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

Open the remote database in read-only mode.

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

Connect to the local database to initiate the clone.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

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

DROP DATABASE LINK clone_link;

CREATE DATABASE LINK clone_link
  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'db12c';

-- Test link.
DESC user_tables@clone_link

Create a new PDB in the local database by cloning the remote non-CDB. In this case we are using Oracle Managed Files (OMF), so we don't need to bother with FILE_NAME_CONVERT parameter for file name conversions. Since there is no PDB to name, we use "NON$CDB" as the PDB name.

CREATE PLUGGABLE DATABASE db12cpdb FROM NON$CDB@clone_link;

Pluggable database created.

SQL>

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

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       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=db12cpdb;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The PDB can now be opened in read-write mode.

ALTER PLUGGABLE DATABASE db12cpdb OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

NAME                           OPEN_MODE
------------------------------ ----------
DB12CPDB                       READ WRITE

SQL>

As with any PDB clone, check common users and the temporary tablespace is configured as expected.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.