In this topic, I will show how to Create and manage PDB:
- Create a PDB from SEED.
- Clone a PDB with No Data.
- Clone a PDB from existing local PDB.
- Clone a PDB from existing remote PDB.
- Clone a PDB from existing remote Non-CDB.
- Clone a PDB from non-CDB using DBMS_PDB Package.
- Plugging In an Unplugged PDB from one CDB to another CDB.
- Drop a PDB (Pluggable Database)
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
