8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Clone a Remote PDB or Non-CDB in Oracle Database 12c (12.1.0.2)
There is an update of this article for Oracle Database 12c Release 2 (12.2) here.
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.
- Multitenant : All Articles
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)
Prerequisites
The prerequisites for cloning a remote PDB or non-CDB are very similar, so I will deal with them together.
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.
- 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.
- If the remote database uses Transparent Data Encryption (TDE) the local CDB must be configured appropriately before attempting the clone. If not you will be left with a new PDB that will only open in restricted mode.
- 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).
- When cloning from a non-CDB, both the the local and remote databases must using version 12.1.0.2 or higher.
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.
- 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).
It is possible to initiate and complete a clone without putting the source database into read-only mode, but this sometimes results in an ORA-00600. Since it is not documented, you should not do this in a real system.
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.
CONN / AS SYSDBA ALTER PLUGGABLE DATABASE pdb5 CLOSE; ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY; EXIT;
Switch to the local server and create a "tnsnames.ora" entry pointing to the remote database for use in the USING
clause of the database link.
PDB5 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb5) ) )
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;
Switch to the local server and create a "tnsnames.ora" entry pointing to the remote database for use in the USING
clause of the database link.
DB12C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = db12c) ) )
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:
- Multitenant : All Articles
- Cloning a Remote PDB or Non-CDB
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Metadata Only PDB Clones in Oracle Database 12c Release 1 (12.1.0.2)
- Multitenant : PDB Subset Cloning in Oracle Database 12c Release 1 (12.1.0.2)
Hope this helps. Regards Tim...