Clone a non-CDB into CDB in Oracle 12c

Oracle has introduced new Multitenant Option from Oracle 12cR1 and this option allows a single container database (CDB) to host multiple pluggable databases.

Refer here for more details on Oracle Multitenant option.

Related articles:

Create Container Database (CDB) in Oracle 12c using DBCA
Create Container database (CDB) in silent mode
Drop a container database in silent mode
Create a pluggable database manually in Oracle 12c
Clone a pluggable database (PDB) within a CDB in Oracle 12c

Description:

In this post I will describe how to clone a non-CDB database into a CDB in Oracle 12c.

In the below example I have two databases running on the same VM, and will create a new PDB to clone the non-cdb database
db2 – Non-CDB database
cdb2 – CDB database used to clone the non-cdb.
pdb_db2 – new pdb database name which has to clone from non-cdb.

Below are the steps:

1. Open the non-CDB database into read-only mode
2. Create directory for new PDB and add new TNS entry to connect non-cdb
3. Create Database link to allow connection to non-CDB database
4. Create a new PDB database by cloning the non-cdb using dblink
5. Execute the noncdb_to_pdb.sql script
6. Check the newly created PDB status
7. Open newly created PDB database

1. Open the non-CDB database into read-only mode

export ORACLE_SID=db2
sqlplus "/as sysdba"
shutdown immediate;
startup mount;
alter database open read only;

O/P:
----
[oracle@cdb12c tmp]$ . oraenv
ORACLE_SID = [cdbdev] ? db2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c tmp]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 06:46:34 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 2432696320 bytes
Fixed Size                  8623592 bytes
Variable Size             654314008 bytes
Database Buffers         1761607680 bytes
Redo Buffers                8151040 bytes
Database mounted.
SQL>
SQL> alter database open read only;

Database altered.

SQL>
SQL>

2. Create a directory to store new PDB Datafiles and add new TNS entry to connect the non-CDB database

mkdir /u01/app/oracle/oradata/cdb2/pdb_db2

--Add the TNS entry in $ORACLE_HOME/network/admin
db2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cdb12c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )

3. Create Database link to allow connection to non-CDB database

export ORACLE_SID=cdb2
sqlplus "/as sysdba"
create database link to_db2_link connect to system identified by idgroup using 'db2';

select name from v$database@to_db2_link;

O/P:
----
[oracle@cdb12c pdb_db2]$ . oraenv
ORACLE_SID = [db2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cdb12c pdb_db2]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:45:17 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  create database link to_db2_link connect to system identified by idgroup using 'db2';

Database link created.

SQL> select name from v$database@to_db2_link;
NAME
---------
DB2
SQL>

4. Create a new PDB database by cloning the non-CDB using dblink

export ORACLE_SID=cdb2
sqlplus "/as sysdba"
create pluggable database PDB_DB2 from NON$CDB@to_db2_link create_file_dest='/u01/app/oracle/oradata/cdb2/pdb_db2';

Output:
[oracle@cdb12c ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:50:26 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database PDB_DB2 from NON$CDB@to_db2_link
  2  create_file_dest='/u01/app/oracle/oradata/cdb2/pdb_db2';

Pluggable database created.

SQL>

5. Execute the noncdb_to_pdb.sql script

export ORACLE_SID=cdb2
sqlplus "/as sysdba"
alter session set container=PDB_DB2;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

OP:
[oracle@cdb12c ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 15:54:02 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=PDB_DB2;

Session altered.

SQL>
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
.
.
<output truncated>
.
.
SQL> set verify OFF
SQL> set wrap ON
SQL> set xmloptimizationcheck OFF
SQL>

4. Check the newly created pdb status

Status of the PDBs can be checked using v$pdbs view,

select name, open_mode from v$pdbs;
show pdbs;

SQL> select name, open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB1            READ WRITE
PDB2            READ WRITE
PDB3            READ WRITE
PDB_DB2         MOUNTED ---> Newly created pdb

6 rows selected.


SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB_DB2                        MOUNTED   -->
SQL>

5. Open the PDB database in read-write mode

--Open newly created pdb:
alter pluggable database pdb_db2 open;
--connect to new pdb
alter session set container=pdb_db2;
select * from faheem.test;

Output:
SQL> alter pluggable database pdb_db2 open;

Pluggable database altered.

SQL> alter session set container=pdb_db2;

Session altered.

SQL> select * from faheem.test;

        ID
----------
         1

1 row selected.

SQL>

We have successfully clone a non-CDB database into a CDB.

Hope this helps…