Creating a Pluggable Database

In this topic, I will show how to Create and manage PDB:

Summary of File Location Clauses and Initialization Parameters:

Create a PDB from SEED:

Clone a PDB from non-CDB using DBMS_PDB Package

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO

SQL> select name from v$datafile where name like '%pdbseed%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

[oracle@ocm12c admin]$ mkdir -p /u01/app/oracle/oradata/salespdb

Note:::If Oracle Manage File parameter (db_file_name_convert) disable then need to provide the value for FILE_NAME_CONVERT parameter with Create Pluggable Database Command.

SQL> show parameter FILE_NAME_CONVERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
pdb_file_name_convert                string

SQL> CREATE PLUGGABLE DATABASE salespdb
  2    ADMIN USER salesadm IDENTIFIED BY password
  3    ROLES = (dba)
  4    DEFAULT TABLESPACE sales
  5      DATAFILE '/u01/app/oracle/oradata/salespdb/sales01.dbf' SIZE 25M AUTOEXTEND ON
  6    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/',
  7                         '/u01/app/oracle/oradata/salespdb/')
  8    STORAGE (MAXSIZE 2G)
  9    PATH_PREFIX = '/u01/app/oracle/oradata/orcl/pdbseed';

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO
         4 SALESPDB                       MOUNTED

SQL> alter pluggable database SALESPDB open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO
         4 SALESPDB                       READ WRITE NO

Create PDB with Oracle Manager File Option:

SQL>  select name from v$datafile where name like '%pdbseed%';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

SQL> CREATE PLUGGABLE DATABASE pdbtest ADMIN USER pdbadmin IDENTIFIED BY pdbadmin;
CREATE PLUGGABLE DATABASE pdbtest ADMIN USER pdbadmin IDENTIFIED BY pdbadmin
                                                                           *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/orcl';

System altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/orcl

SQL>  CREATE PLUGGABLE DATABASE pdbtest ADMIN USER pdbadmin IDENTIFIED BY pdbadmin;

Pluggable database created.

SQL> SELECT name, open_mode, restricted FROM v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB$SEED                       READ ONLY  NO
PDBORCL                        MOUNTED
SALESPDB                       READ WRITE NO
PDBTEST                        READ WRITE NO

Use below query to show list of datafiles for CDB and all PDBs:

SQL> col DATA_FILE_NAME for a60
SQL> col DB_NAME for A15
SQL> SELECT c.name DB_NAME, b.name DATA_FILE_NAME
-- a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
FROM v$tablespace a, v$datafile b, v$containers c
WHERE a.con_id = b.con_id AND a.con_id = c.con_id AND a.ts# = b.ts#
ORDER BY a.con_id, a.TS#;

DB_NAME         DATA_FILE_NAME
--------------- ------------------------------------------------------------
CDB$ROOT        /u01/app/oracle/oradata/orcl/system01.dbf
CDB$ROOT        /u01/app/oracle/oradata/orcl/sysaux01.dbf
CDB$ROOT        /u01/app/oracle/oradata/orcl/undotbs01.dbf
CDB$ROOT        /u01/app/oracle/oradata/orcl/users01.dbf
PDB$SEED        /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
PDB$SEED        /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
PDBORCL         /u01/app/oracle/oradata/orcl/pdborcl/system01.dbf
PDBORCL         /u01/app/oracle/oradata/orcl/pdborcl/sysaux01.dbf
PDBORCL         /u01/app/oracle/oradata/orcl/pdborcl/pdborcl_users01.dbf
SALESPDB        /u01/app/oracle/oradata/salespdb/system01.dbf
SALESPDB        /u01/app/oracle/oradata/salespdb/sysaux01.dbf
SALESPDB        /u01/app/oracle/oradata/salespdb/sales01.dbf
PDBTEST         /u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/o1_mf_system_hmvw32m1_.dbf
PDBTEST         /u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/o1_mf_sysaux_hmvw32m4_.dbf
SQL> conn / as sysdba
Connected.
SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string

SQL> Alter session set pdb_file_name_convert='/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/pdbone/';

Session altered.

SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /u01/app/oracle/oradata/orcl/pdbseed/, /u01/app/oracle/oradata/orcl/pdbone/

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/orcl

SQL> CREATE PLUGGABLE DATABASE pdbone1 admin user newadmin identified by oracle;

Pluggable database created.

SQL> alter session set container=pdbone1;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/ORCL/AD6DEF6186271432E053DC38A8C09AE0/datafile/o1_mf_system_hn0vrzqv_.dbf

/u01/app/oracle/oradata/orcl/ORCL/AD6DEF6186271432E053DC38A8C09AE0/datafile/o1_mf_sysaux_hn0vrzqx_.dbf
SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /u01/app/oracle/oradata/orcl/pdbseed/, /u01/app/oracle/oradata/orcl/pdbtwo/

SQL> !
[oracle@ocm12c oradata]$ mkdir -p /u01/app/oracle/oradata/orcl/pdbtwo/

[oracle@ocm12c oradata]$ exit

SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /u01/app/oracle/oradata/orcl/pdbseed/, /u01/app/oracle/oradata/orcl/pdbtwo/

SQL> CREATE PLUGGABLE DATABASE pdbtwo admin user newadmin identified by oracle;

Pluggable database created.

SQL> Alter session set container=pdbtwo;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdbtwo/system01.dbf
/u01/app/oracle/oradata/orcl/pdbtwo/sysaux01.dbf
SQL> Alter session set container=PDBTEST;

Create table tbltest (id number);

Table created.

SQL> Insert into tbltest values(1);

1 row created.

SQL> Commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdbtest close;

Pluggable database altered.

SQL> alter pluggable database pdbtest open read only;

Pluggable database altered.

SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> CREATE PLUGGABLE DATABASE PDBCLONETEST FROM PDBTEST;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 SALESPDB                       READ WRITE NO
         5 PDBTEST                        READ ONLY  NO
         6 PDBONE                         MOUNTED
         7 PDBCLONETEST                   MOUNTED

SQL> alter pluggable database PDBCLONETEST open;

Pluggable database altered.

SQL> alter session set container=PDBCLONETEST;

Session altered.

SQL> select * from tbltest;

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

Clone a PDB from existing local PDB:

SQL> alter session set container=PDBTEST;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBTEST                        READ ONLY  NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/o1_mf_system_hmvw32m1_.dbf
/u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/o1_mf_sysaux_hmvw32m4_.dbf


SQL> conn / as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/app/oracle/oradata/orcl

SQL> alter session set db_create_file_dest='';

Session altered.

SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string

SQL> CREATE PLUGGABLE DATABASE PDBCLONETEST1 FROM PDBTEST FILE_NAME_CONVERT=('PDBTEST','PDBCLONETEST');
CREATE PLUGGABLE DATABASE PDBCLONETEST1 FROM PDBTEST FILE_NAME_CONVERT=('PDBTEST','PDBCLONETEST')
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file -
/u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/o1_mf_system_hmvw32m1_.dbf

SQL> ! mkdir -p /u01/app/oracle/oradata/orcl/pdbclonetest1

SQL> CREATE PLUGGABLE DATABASE PDBCLONETEST1 FROM PDBTEST FILE_NAME_CONVERT=('/u01/app/oracle/oradata/orcl/ORCL/AD47DCFB4D7A165BE053DC38A8C0373F/datafile/','/u01/app/oracle/oradata/orcl/pdbclonetest1');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 SALESPDB                       READ WRITE NO
         5 PDBTEST                        READ ONLY  NO
         6 PDBONE                         MOUNTED
         7 PDBCLONETEST                   READ WRITE NO
         8 PDBCLONETEST1                  MOUNTED

SQL> Alter pluggable database PDBCLONETEST1 open;

Pluggable database altered.

SQL> alter session set container=PDBCLONETEST1;

Session altered.

SQL> select * from tbltest;

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

Clone a PDB with No Data:

If Create any table on SYSTEM & SYSAUX tablespace by user, these data will consider to move to new database. But all others tablespace data will not consider for movement.

SQL> alter pluggable database pdbone open;

Pluggable database altered.

SQL> alter session set container=pdbone;

Session altered.


SQL> CREATE TABLESPACE TBSTEST DATAFILE '/u01/app/oracle/oradata/orcl/tbstest01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

Tablespace created.

SQL> CREATE USER JOHN IDENTIFIED BY test
DEFAULT TABLESPACE tbstest
QUOTA UNLIMITED ON tbstest;

User created.


SQL> create table JOHN.t1(id number) tablespace tbstest;

Table created.

SQL> create table JOHN.t2(id number) tablespace system;

Table created.

SQL> create table sys.t1 (id number) tablespace system;

Table created.

SQL> create table sys.t2 (id number) tablespace tbstest;

Table created.

SQL> Select OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME IN ('T1','T2');

OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- --------------------
SYS        T2         TBSTEST
SYS        T1         SYSTEM
JOHN       T2         SYSTEM
JOHN       T1         TBSTEST

SQL> INSERT INTO SYS.T1 VALUES(10);

1 row created.

SQL> INSERT INTO SYS.T1 VALUES(20);

1 row created.

SQL> INSERT INTO SYS.T2 VALUES(30);

1 row created.

SQL> INSERT INTO SYS.T2 VALUES(40);

1 row created.

SQL> INSERT INTO JOHN.T1 VALUES(50);

1 row created.

SQL> INSERT INTO JOHN.T1 VALUES(60);

1 row created.

SQL> INSERT INTO JOHN.T2 VALUES(70);

1 row created.

SQL> INSERT INTO JOHN.T2 VALUES(80);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         6 PDBONE                         READ WRITE NO

SQL> conn / as sysdba
Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        MOUNTED
         6 PDBONE                         READ WRITE NO

SQL> alter pluggable database pdbone close;

Pluggable database altered.

SQL> alter pluggable database pdbone open read only;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE PDBNODATA FROM PDBONE NO DATA;

Pluggable database created.

SQL> alter pluggable database pdbnodata open;

Pluggable database altered.

SQL> alter session set container = pdbnodata;

Session altered.

SQL> select * from sys.t1;

        ID
----------
        10
        20

SQL> select * from sys.t2;

no rows selected

SQL> select * from john.t1;

no rows selected

SQL> select * from john.t2;

        ID
----------
        70
        80

SQL> col OWNER for A10
SQL> col TABLE_NAME for A10
SQL> col TABLESPACE_NAME for A20
SQL> Select OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME IN ('T1','T2');

OWNER      TABLE_NAME TABLESPACE_NAME
---------- ---------- --------------------
SYS        T2         TBSTEST
SYS        T1         SYSTEM
JOHN       T1         TBSTEST
JOHN       T2         SYSTEM

Clone a PDB from existing remote PDB:

We have two CDBs called ORCL and MYCDB. There is a PDB called PDBNODATA under ORCL CDB and we will close this PDB from ORCL to MYCDB.

Execute below command on ORCL:

[oracle@ocm12c datafile]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ocm12c datafile]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 16:25:22 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user dblink_user identified by oracle;^C

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        MOUNTED
         6 PDBONE                         MOUNTED
         7 PDBNODATA                      READ WRITE NO
SQL> alter session set container=PDBNODATA;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         7 PDBNODATA                      READ WRITE NO
		 
SQL> create table john.move (id number);

Table created.

SQL> insert into john.move values (10);

1 row created.

SQL> commit;

Commit complete.

		 
SQL> create user dblink_user identified by oracle;

User created.

SQL> grant connect, resource, create pluggable database to dblink_user;

Grant succeeded.

Execute below command on MYCDB:

[oracle@ocm12c ~]$ . oraenv
ORACLE_SID = [oracle] ? mycdb
The Oracle base has been set to /u01/app/oracle
[oracle@ocm12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 15:59:36 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         READ WRITE NO

SQL> alter system register;

System altered.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm12c.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-AUG-2020 16:04:03
Uptime                    0 days 0 hr. 34 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ocm12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm12c.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ocm12c.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "mycdb" has 1 instance(s).
  Instance "mycdb", status READY, has 1 handler(s) for this service...
Service "mycdbXDB" has 1 instance(s).
  Instance "mycdb", status READY, has 1 handler(s) for this service...
Service "mypdbt" has 1 instance(s).
  Instance "mycdb", status READY, has 1 handler(s) for this service...
Service "orcl.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbnodata.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbone.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdbtest.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "salespdb.localdomain" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


PDBNODATA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12c.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbnodata.localdomain)
    )
  )

[oracle@ocm12c admin]$ tnsping PDBNODATA

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-AUG-2020 16:44:44

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12c.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdbnodata.localdomain)))
OK (0 msec)


SQL> select name from v$database;

NAME
---------
MYCDB


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create database link dblink_pdbnodata connect to dblink_user identified by oracle using 'pdbnodata';

Database link created.

SQL> select sysdate from dual@dblink_pdbnodata;

SYSDATE
---------
22-AUG-20

SQL> create pluggable database pdbclone from pdbnodata@dblink_pdbnodata;

Pluggable database created.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/oradata


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         READ WRITE NO
         4 PDBCLONE                       MOUNTED

SQL> alter pluggable database pdbclone open;

Pluggable database altered.

SQL> alter session set container=pdbclone;

Session altered.

SQL> select * from john.move;

        ID
----------
        10

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/MYCDB/datafile/o1_mf_undotbs1_hn2xz0ck_.dbf
/u02/oradata/MYCDB/AD7E855251A11CBDE053DC38A8C00CE6/datafile/o1_mf_system_hn31fsgq_.dbf
/u02/oradata/MYCDB/AD7E855251A11CBDE053DC38A8C00CE6/datafile/o1_mf_sysaux_hn31fsgq_.dbf
/u02/oradata/MYCDB/AD7E855251A11CBDE053DC38A8C00CE6/datafile/o1_mf_tbstest_hn31fsgq_.dbf

Clone a PDB from non-CDB:

[oracle@ocm12c datafile]$ . oraenv
ORACLE_SID = [orcl] ? noncdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ocm12c datafile]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 17:36:50 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select cdb from v$database;

CDB
---
NO

SQL> create user dblink_user identified by oracle;

User created.

SQL> create table tbl_noncdb (id number);

Table created.

SQL> insert into tbl_noncdb values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> grant dba to dblink_user;

Grant succeeded.

SQL> alter system register;

System altered.
NONCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12c.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = noncdb)
    )
  )

[oracle@ocm12c admin]$ tnsping NONCDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-AUG-2020 17:44:10

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12c.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noncdb)))
OK (10 msec)


  
[oracle@ocm12c ~]$ . oraenv
ORACLE_SID = [noncdb] ? mycdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ocm12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 22 17:36:18 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select cdb from v$database;

CDB
---
YES

SQL> create database link dblink_noncdb connect to dblink_user identified by oracle using 'noncdb';

Database link created.


SQL> select name from v$database@dblink_noncdb;

NAME
---------
NONCDB

SQL> select sysdate from dual@dblink_noncdb;

SYSDATE
---------
22-AUG-20

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         READ WRITE NO
         4 PDBCLONE                       READ WRITE NO
		 
SQL> create pluggable database pdbclone1 from NON$CDB@dblink_noncdb;

Pluggable database created.


SQL> alter pluggable database PDBCLONE1 open;

Warning: PDB altered with errors.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         READ WRITE NO
         4 PDBCLONE                       READ WRITE NO
         5 PDBCLONE1                      READ WRITE YES
SQL> startup force
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  2927528 bytes
Variable Size             671089752 bytes
Database Buffers         1828716544 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         MOUNTED
         4 PDBCLONE                       MOUNTED
         5 PDBCLONE1                      MOUNTED

SQL> alter session set container=PDBCLONE1;

Session altered.

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
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> WHENEVER SQLERROR EXIT;

...
...

SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDBCLONE1                      MOUNTED
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database PDBCLONE1 open;

Pluggable database altered.

SQL> alter session set container=PDBCLONE1;

Session altered.

SQL> select * from tbl_noncdb;

        ID
----------
        10

1 row selected.

Clone a non-CDB using DBMS_PDB Package to CDB:

NEWDB is a Non-CDB database. We will clone this database (NEWDB) to CDB (MYCDB) using DBMS_PDB. Run DBMS_PDB.DESCRIBE package on NEWDB to generate the xml file of this non-cdb database.

SQL> select name, cdb from v$database;
NAME	  CDB
--------- ---
NEWDB	  NO

SQL> BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/u02/oradata/NONCDB/noncdb.xml');
END;
/ 
 
PL/SQL procedure successfully completed.
SQL>

SQL> create table t12(id number);

Table created.

Run Below command on MYCDB to check the compatibility.

SQL> conn / as sysdba
Connected.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file => '/u02/oradata/NONCDB/noncdb.xml',
           pdb_name       => 'newdb')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

YES

PL/SQL procedure successfully completed.

Create Pluggable database using the created xml from non-cdb.

SQL> alter session set db_create_file_dest='/u02/oradata/MYCDB/datafile' ;

Session altered.

SQL> CREATE PLUGGABLE DATABASE newdb USING '/u02/oradata/NONCDB/noncdb.xml' COPY USER_TABLESPACES=('USERS');  

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         MOUNTED
         4 PDBCLONE                       MOUNTED
         5 PDBCLONE1                      READ WRITE NO
         6 NEWDB                          MOUNTED
  
  SQL> alter session set container=newdb;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> alter pluggable database newdb open;

Pluggable database altered.

SQL> alter session set container=newdb;

Session altered.

SQL> select * from tbl_noncdb;

        ID
----------
        10

1 row selected.

SQL> select * from t12;
select * from t12
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Plugging In an Unplugged PDB from one CDB to another CDB in Same Server:

Prerequisites before plugging in an unplugged PDB:

We have two CDB (ORCL and MYCDB) databases in a server. We will unplugged PDB (PDBONE) from ORCL and will plugging to MYCDB.

[oracle@ocm12c ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle
[oracle@ocm12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 23 16:39:27 2020

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  2925072 bytes
Variable Size             989859312 bytes
Database Buffers          654311424 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        MOUNTED
         6 PDBONE                         MOUNTED
         7 PDBNODATA                      MOUNTED

SQL> alter pluggable database pdbone open;

Pluggable database altered.

SQL> alter session set container=pdbone;

Session altered.

SQL> create table tbl_plggin(id number);

Table created.

SQL> insert into tbl_plggin values (10);

1 row created.

SQL> commit;

Commit complete.

SQL> alter pluggable database pdbone close;

Pluggable database altered.

SQL> conn / as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        MOUNTED
         6 PDBONE                         MOUNTED
         7 PDBNODATA                      MOUNTED

SQL> alter pluggable database pdbone unplug into '/u02/oradata/backup/pdbone.xml';

Pluggable database altered.

SQL> drop pluggable database pdbone keep datafiles;

Pluggable database dropped.

Run DBMS_PDB.check_plug_compatibility function and provide the name of PDB and the XML file to see if the PDB can be plugged to this CDB:

[oracle@ocm12c ~]$ . oraenv
ORACLE_SID = [mycdb] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ocm12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 23 18:17:51 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 PDBCLONE                       MOUNTED
         5 PDBCLONE1                      MOUNTED
         6 NEWDB                          MOUNTED
         7 PDBPLAG                        MOUNTED

SQL> set serveroutput on
DECLARE
   compatible BOOLEAN := FALSE;
BEGIN
   compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/u02/oradata/backup/pdbone.xml',pdb_name=>'pdbpluggin');
   if compatible then
        DBMS_OUTPUT.PUT_LINE('compatible');
   else DBMS_OUTPUT.PUT_LINE('incompatible');
   end if;
END;
/
compatible

PL/SQL procedure successfully completed.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/oradata

SQL> create pluggable database pdbpluggin using '/u02/oradata/backup/pdbone.xml';

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBPLUGGIN                     MOUNTED
         4 PDBCLONE                       MOUNTED
         5 PDBCLONE1                      MOUNTED
         6 NEWDB                          MOUNTED
         7 PDBPLAG                        MOUNTED
SQL> alter session set container=PDBPLUGGIN;

Session altered.

SQL> alter pluggable database PDBPLUGGIN open;

Pluggable database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/MYCDB/datafile/o1_mf_undotbs1_hn2xz0ck_.dbf
/u02/oradata/MYCDB/AD6DDF0DEC2C01EBE053DC38A8C05C8A/datafile/o1_mf_system_hn668c
yv_.dbf
/u02/oradata/MYCDB/AD6DDF0DEC2C01EBE053DC38A8C05C8A/datafile/o1_mf_sysaux_hn668c
yy_.dbf
/u02/oradata/MYCDB/AD6DDF0DEC2C01EBE053DC38A8C05C8A/datafile/o1_mf_tbstest_hn668
cyy_.dbf

SQL> select * from tbl_plggin;

        ID
----------
        10

Note: You are good to delete files from source pdb database’s location.

Plugging In an Unplugged PDB from one CDB to another CDB in Different Server:

Drop a PDB (Pluggable Database):

When dropping a PDB, you can either keep or delete the PDB’s data files by using one of the following clauses:

KEEP DATAFILES, the default, retains the data files. The PDB’s temp file is removed even when KEEP DATAFILES is specified because the temp file is no longer needed. When KEEP DATAFILES is specified, the PDB must be unplugged.

INCLUDING DATAFILES removes the data files from disk. If a PDB was created with the SNAPSHOT COPY clause, then you must specify INCLUDING DATAFILES when you drop the PDB.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDBT                         READ WRITE NO
         4 PDBCLONE                       READ WRITE NO
         5 PDBCLONE1                      MOUNTED
SQL> drop pluggable database pdbclone1 including datafiles;

Pluggable database dropped.

For details, check oracle documents -> 38 Creating and Removing PDBs with SQL*Plus -> 38.5.2 Plugging In an Unplugged PDB

Leave a Reply