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:
- Open the non-CDB database into read-only mode
- Create directory for new PDB and add new TNS entry to connect non-cdb
- Create Database link to allow connection to non-CDB database
- Create a new PDB database by cloning the non-cdb using dblink
- Execute the noncdb_to_pdb.sql script
- Check the newly created PDB status
- 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…