VIMAL :Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c

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

Prerequisites

The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the root container.
The remote database (PDB or non-CDB) must be open in read-only mode.
The local database must have a database link to the remote database. If the remote database is a PDB, the database
link can point to the remote CDB using a common user, or the PDB using a local or common user.
The user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE privilege.
The local and remote databases must have the same endianness, options installed and character sets.
When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.

Cloning a Remote PDB

cdb1 : The local database that will eventually house the clones.
db12c : The remote non-CDB.
cdb3 : The remote CDB, used for cloning a remote PDB (pdb5).
pdb5 : Remote pluggable database


1) 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;

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

3)  Open the remote PDB in read-only mode.

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

4) Connect to the local database to initiate the clone.

5)  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

6) Create a new PDB in the local database by cloning the remote PDB

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

7) 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';

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

ALTER PLUGGABLE DATABASE pdb5new OPEN;



Cloning a Remote Non-CDB

1)  Connect to the remote database to prepare it for cloning.

2)  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;

3) Open the remote database in read-only mode.

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

4)  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

5) Create a new PDB in the local database by cloning the remote non-CDB.

CREATE PLUGGABLE DATABASE db12cpdb FROM NON$CDB@clone_link;
SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12CPDB';

6) 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';