12c Non-CDB into PDB Migration (Version 12.1) - Non-CDB (12c) -----> CDB (12c)
---> Method 1: Using DBMS_PDB package
---> Method 2: Cloning a Remote Non-CDB
Method 1: Using DBMS_PDB package
The DBMS_PDB package is used to generate an XML metadata file from a non-CDB 12c database.This can be used when the non-CDB to be plugged in as a PDB into an existing CDB.
Cleanly shutdown the non-CDB and start it in read-only mode.
export ORACLE_SID=my12cdb
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP OPEN READ ONLY;
Describe the non-DBC using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/tmp/my12cdb.xml');
END;
/
Shutdown the non-CDB database.
export ORACLE_SID=my12cdb
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.
export ORACLE_SID=cdb1
sqlplus / as sysdba
CREATE PLUGGABLE DATABASE pdb6 USING '/tmp/my12cdb.xml'
COPY
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/my12cdb/', '/u01/app/oracle/oradata/cdb1/pdb1/');
Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB. You can see an example of the output produced by this script here.
ALTER SESSION SET CONTAINER=pdb1;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Startup the PDB and check the open mode.
ALTER SESSION SET CONTAINER=pdb1;
ALTER PLUGGABLE DATABASE OPEN;
SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB6 READ WRITE
1 row selected.
Method 2: 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>
---> Method 1: Using DBMS_PDB package
---> Method 2: Cloning a Remote Non-CDB
Method 1: Using DBMS_PDB package
The DBMS_PDB package is used to generate an XML metadata file from a non-CDB 12c database.This can be used when the non-CDB to be plugged in as a PDB into an existing CDB.
Cleanly shutdown the non-CDB and start it in read-only mode.
export ORACLE_SID=my12cdb
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP OPEN READ ONLY;
Describe the non-DBC using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.
BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/tmp/my12cdb.xml');
END;
/
Shutdown the non-CDB database.
export ORACLE_SID=my12cdb
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
Connect to an existing CDB and create a new PDB using the file describing the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.
export ORACLE_SID=cdb1
sqlplus / as sysdba
CREATE PLUGGABLE DATABASE pdb6 USING '/tmp/my12cdb.xml'
COPY
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/my12cdb/', '/u01/app/oracle/oradata/cdb1/pdb1/');
Switch to the PDB container and run the "$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql" script to clean up the new PDB, removing any items that should not be present in a PDB. You can see an example of the output produced by this script here.
ALTER SESSION SET CONTAINER=pdb1;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Startup the PDB and check the open mode.
ALTER SESSION SET CONTAINER=pdb1;
ALTER PLUGGABLE DATABASE OPEN;
SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB6 READ WRITE
1 row selected.
Method 2: 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>