Oracle 12c - PDB Administration

 

 

Here are some of the examples showing various PDB Administration activities...

 
 
 
In the following example, new PDB database PDBTEST is created from the seed PDB, PDB$SEED.
SQL>  select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB12C                         READ WRITE
         4 PDB_PLUG_NOCOPY                MOUNTED
         5 EXNONCDB                       MOUNTED
         6 PDBTEST                        READ WRITE


FILE_NAME_CONVERT clause is used to specify the directory structure for the new PDB

SQL> CREATE PLUGGABLE DATABASE pdbtest1
ADMIN USER pdba IDENTIFIED BY pdba ROLES = (dba)
FILE_NAME_CONVERT = ('/oradata/db12c/pdbseed/','/oradata/db12c/pdbtest1/');
  2    3

Pluggable database created.

Open the newly created PDB

SQL>  alter pluggable database  pdbtest1 open;

Pluggable database altered.


Query v$pdbs to check the status of the newly created PDB

SQL>  select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB12C                         READ WRITE
         4 PDB_PLUG_NOCOPY                MOUNTED
         5 EXNONCDB                       MOUNTED
         6 PDBTEST                        READ WRITE
         7 PDBTEST1                       READ WRITE

6 rows selected.

Verify that the datafiles for the newly created PDB are created in the right location

SQL>   select con_id,file_name from cdb_data_files order by con_id;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /oradata/db12c/users01.dbf
         1 /oradata/db12c/undotbs01.dbf
         1 /oradata/db12c/sysaux01.dbf
         1 /oradata/db12c/system01.dbf
         2 /oradata/db12c/pdbseed/system01.dbf
         2 /oradata/db12c/pdbseed/sysaux01.dbf
         3 /oradata/db12c/pdb12c/SAMPLE_SCHEMA_users01.dbf
         3 /oradata/db12c/pdb12c/example01.dbf
         3 /oradata/db12c/pdb12c/sysaux01.dbf
         3 /oradata/db12c/pdb12c/system01.dbf
         6 /oradata/db12c/pdbtest/system01.dbf
         6 /oradata/db12c/pdbtest/sysaux01.dbf
         6 /oradata/db12c/pdbtest/SAMPLE_SCHEMA_users01.dbf
         6 /oradata/db12c/pdbtest/example01.dbf
         7 /oradata/db12c/pdbtest1/sysaux01.dbf
         7 /oradata/db12c/pdbtest1/system01.dbf

16 rows selected.

 

This example uses PDB_FILE_NAME_CONVERT parameter value to specify the directory structure for the new PDB


Check and set the PDB_FILE_NAME_CONVERT parameter value to the desired filename convert value.

SQL> show parameter pdb_file_name_convert

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

SQL> alter system set pdb_file_name_convert='/oradata/db12c/pdbtest/','/oradata/db12c/pdbtest1/' scope=both;

System altered.

SQL> show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /oradata/db12c/pdbtest/,
                                                 /oradata/db12c/pdbtest1/

Close and open the source PDB as read only

SQL> alter  pluggable database pdbtest close immediate;

Pluggable database altered.

SQL>  alter  pluggable database pdbtest open read only;

Pluggable database altered.


Create the new pdb, PDBTEST1. 
Optional Clause -> PATH_PREFIX : Directory path to be used when the relative paths are used for the pdb directory objects


CREATE PLUGGABLE DATABASE pdbtest1 FROM pdbtest PATH_PREFIX = '/oradata/db12c/pdbtest1/';

Pluggable database created.

Close and Open the PDBs

SQL>  alter pluggable database pdbtest,pdbtest1  close immediate;

Pluggable database altered.

SQL>  alter pluggable database  pdbtest,pdbtest1  open;

Pluggable database altered.


Check the status of the newly created PDB

SQL>  select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB12C                         READ WRITE
         4 PDB_PLUG_NOCOPY                MOUNTED
         5 EXNONCDB                       MOUNTED
         6 PDBTEST                        READ WRITE
         7 PDBTEST1                       READ WRITE

6 rows selected.

Verify the datafiles location for the newly created PDB

SQL> select con_id,file_name from cdb_data_files order by con_id;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------
         1 /oradata/db12c/users01.dbf
         1 /oradata/db12c/undotbs01.dbf
         1 /oradata/db12c/sysaux01.dbf
         1 /oradata/db12c/system01.dbf
         2 /oradata/db12c/pdbseed/system01.dbf
         2 /oradata/db12c/pdbseed/sysaux01.dbf
         3 /oradata/db12c/pdb12c/SAMPLE_SCHEMA_users01.dbf
         3 /oradata/db12c/pdb12c/sysaux01.dbf
         3 /oradata/db12c/pdb12c/example01.dbf
         3 /oradata/db12c/pdb12c/system01.dbf
         6 /oradata/db12c/pdbtest/system01.dbf
         6 /oradata/db12c/pdbtest/example01.dbf
         6 /oradata/db12c/pdbtest/SAMPLE_SCHEMA_users01.dbf
         6 /oradata/db12c/pdbtest/sysaux01.dbf
         7 /oradata/db12c/pdbtest1/system01.dbf
         7 /oradata/db12c/pdbtest1/example01.dbf
         7 /oradata/db12c/pdbtest1/SAMPLE_SCHEMA_users01.dbf
         7 /oradata/db12c/pdbtest1/sysaux01.dbf

18 rows selected.

SQL>

 

 

 

 

 
 
This example uses DB_CREATE_FILE_DEST (default location for the Oracle Managed Files, OMF) parameter value to specify the directory structure for the new PDB.
You may notice that when the DB_CREATE_FILE_DEST parameter is defined, the parameter value specified under PDB_FILE_NAME_CONVERT is ignored


Check and set the DB_CREATE_FILE_DEST parameter value to the desired filename convert value.


SQL>  show parameter   pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /oradata/db12c/pdbtest/,
                                                 /oradata/db12c/pdbtest1                                                 /

SQL> show parameter db_create_file_dest

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

SQL> alter system set db_create_file_dest='/oradata/db12c/' scope=both;

System altered.

SQL> show parameter db_create_file_dest

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



Close and open the source PDB as read only

SQL> alter  pluggable database pdbtest close immediate;

Pluggable database altered.

SQL>  alter  pluggable database pdbtest open read only;

Pluggable database altered.


Create the new pdb, PDBTEST1. 
Optional Clause -> PATH_PREFIX : Directory path to be used when the relative paths are used for the pdb directory objects

SQL> CREATE PLUGGABLE DATABASE pdbtest1 FROM pdbtest PATH_PREFIX = '/oradata/db12c/pdbtest1/';

Pluggable database created.


Close and Open the PDBs

SQL>  alter pluggable database pdbtest,pdbtest1  close immediate;

Pluggable database altered.

SQL>  alter pluggable database  pdbtest,pdbtest1  open;

Pluggable database altered.


Check the status of the newly created PDB

SQL>   select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB12C                         READ WRITE
         4 PDB_PLUG_NOCOPY                MOUNTED
         5 EXNONCDB                       MOUNTED
         6 PDBTEST                        READ WRITE
         7 PDBTEST1                       READ WRITE

6 rows selected.


Verify the datafiles location for the newly created PDB

SQL>   select con_id,file_name from cdb_data_files order by con_id;


    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /oradata/db12c/users01.dbf
         1 /oradata/db12c/system01.dbf
         1 /oradata/db12c/sysaux01.dbf
         1 /oradata/db12c/undotbs01.dbf
         2 /oradata/db12c/pdbseed/system01.dbf
         2 /oradata/db12c/pdbseed/sysaux01.dbf
         3 /oradata/db12c/pdb12c/SAMPLE_SCHEMA_users01.dbf
         3 /oradata/db12c/pdb12c/sysaux01.dbf
         3 /oradata/db12c/pdb12c/example01.dbf
         3 /oradata/db12c/pdb12c/system01.dbf
         6 /oradata/db12c/pdbtest/system01.dbf
         6 /oradata/db12c/pdbtest/sysaux01.dbf
         6 /oradata/db12c/pdbtest/SAMPLE_SCHEMA_users01.dbf
         6 /oradata/db12c/pdbtest/example01.dbf
         7 /oradata/db12c/DB12C/F5ECFD2AE2672862E0440003BA901D6D/datafile/o1_mf_system_9mmkrgl1_.dbf
         7 /oradata/db12c/DB12C/F5ECFD2AE2672862E0440003BA901D6D/datafile/o1_mf_sysaux_9mmkrgl0_.dbf
         7 /oradata/db12c/DB12C/F5ECFD2AE2672862E0440003BA901D6D/datafile/o1_mf_users_9mmkrgp7_.dbf
         7 /oradata/db12c/DB12C/F5ECFD2AE2672862E0440003BA901D6D/datafile/o1_mf_example_9mmkrgp2_.dbf

18 rows selected.

 

 
This example uses FILE_NAME_CONVERT clause to specify the directory structure for the new PDB.
FILE_NAME_CONVERT Clause will override the parameter values defined with DB_CREATE_FILE_DEST and PDB_FILE_NAME_CONVERT values

As you see below, both DB_CREATE_FILE_DEST and PDB_FILE_NAME_CONVERT parameters are specified for this database.

SQL>  show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oradata/db12c/
SQL>  show parameter pdb_file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert                string      /oradata/db12c/pdbtest/,
                                                 /oradata/db12c/pdbtest1/


Close and Open the PDBTEST in readonly mode

SQL>  alter  pluggable database pdbtest close immediate;

Pluggable database altered.

SQL> alter  pluggable database pdbtest open read only;

Pluggable database altered.


Create new pluggable database, PDBTEST1 from PDBTEST using FILE_NAME_CONVERT clause
SQL> CREATE PLUGGABLE DATABASE pdbtest1 FROM pdbtest
  FILE_NAME_CONVERT = ('/oradata/db12c/pdbtest/', '/oradata/db12c/pdbtest_1/')
  PATH_PREFIX = '/oradata/db12c/pdbtest_1/';  2    3

Pluggable database created.


Close and Open both pluggable databases

SQL> alter pluggable database pdbtest,pdbtest1  close immediate;

Pluggable database altered.

SQL> alter pluggable database  pdbtest,pdbtest1  open;

Pluggable database altered.


Check the status of the newly created PDB

SQL>  select con_id,name,open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB12C                         READ WRITE
         4 PDB_PLUG_NOCOPY                MOUNTED
         5 EXNONCDB                       MOUNTED
         6 PDBTEST                        READ WRITE
         7 PDBTEST1                       READ WRITE

6 rows selected.

Verify the location of the datafiles for the newly created PDB

SQL>  select con_id,file_name from cdb_data_files order by con_id;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
         1 /oradata/db12c/users01.dbf
         1 /oradata/db12c/system01.dbf
         1 /oradata/db12c/sysaux01.dbf
         1 /oradata/db12c/undotbs01.dbf
         2 /oradata/db12c/pdbseed/system01.dbf
         2 /oradata/db12c/pdbseed/sysaux01.dbf
         3 /oradata/db12c/pdb12c/SAMPLE_SCHEMA_users01.dbf
         3 /oradata/db12c/pdb12c/example01.dbf
         3 /oradata/db12c/pdb12c/sysaux01.dbf
         3 /oradata/db12c/pdb12c/system01.dbf
         6 /oradata/db12c/pdbtest/system01.dbf
         6 /oradata/db12c/pdbtest/sysaux01.dbf
         6 /oradata/db12c/pdbtest/SAMPLE_SCHEMA_users01.dbf
         6 /oradata/db12c/pdbtest/example01.dbf
         7 /oradata/db12c/pdbtest_1/system01.dbf
         7 /oradata/db12c/pdbtest_1/sysaux01.dbf
         7 /oradata/db12c/pdbtest_1/SAMPLE_SCHEMA_users01.dbf
         7 /oradata/db12c/pdbtest_1/example01.dbf

18 rows selected.

SQL>

 

In this example, a non-CDB database is converted into a pluggable database.

Startup the non-CDB database in readonly mode

SQL> startup mount
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size		    2288968 bytes
Variable Size		  989856440 bytes
Database Buffers	  637534208 bytes
Redo Buffers		    7135232 bytes
Database mounted.

SQL> alter database open read only;

Database altered.


SQL> select cdb from v$database;

CDB
---
NO

Generate xml metadata for the non-cdb using dbms_pdb package

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/data/oracle/app/oracle/oradata/noncdb.xml');

PL/SQL procedure successfully completed.



Log on to the CDB database and create the pluggable database with the non-CDB xml file

SQL> create pluggable database noncdb using 
  2   '/data/oracle/app/oracle/oradata/noncdb.xml' copy
  3  file_name_convert=('/data/oracle/app/oracle/oradata/noncdb/','/data/oracle/app/oracle/oradata/ora12/noncdb/');

Pluggable database created.

SQL> 


Set the container to noncdb and run the pdb conversion script

SQL>  alter session set container=noncdb;

Session altered.

SQL> @?/rdbms/admin/noncdb_to_pdb.sql


Open the pluggable database

SQL> alter pluggable database all open;

Pluggable database altered.


Check the status of the newly converted PDB

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 2 PDB$SEED			  READ ONLY
	 3 PDBORA12			  READ WRITE
	 4 NONCDB			  READ WRITE


Verify the datafiles location for the newly created PDB
SQL> select con_id,file_name from cdb_data_files;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
	 4 /data/oracle/app/oracle/oradata/ora12/noncdb/users01.dbf
	 4 /data/oracle/app/oracle/oradata/ora12/noncdb/sysaux01.dbf
	 4 /data/oracle/app/oracle/oradata/ora12/noncdb/system01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/system01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/sysaux01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/system01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/sysaux01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/pdbora12_users01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/users01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/undotbs01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/sysaux01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/system01.dbf

12 rows selected.

 

In this example, the pluggable database, "noncdb" is renamed to "pdbnoncdb".


Connect as a privileged user
SQL> conn sys as sysdba
Enter password: 
Connected.

Connect to the pdb 
SQL> alter session set container=noncdb;
Session altered.

Open the PDB in restrict mode
SQL> startup restrict
Pluggable Database opened.

Rename the PDB to the new name
SQL> alter pluggable database noncdb rename global_name to pdbnoncdb;

Pluggable database altered.

Restart the PDB
SQL> startup force
Pluggable Database opened.

Verify the new name of the PDB
SQL> show con_name

CON_NAME
------------------------------
PDBNONCDB

 

In this example, Pluggable database, "pdbnoncdb" is unplugged from the CDB and plugged back into the CDB with different name and directory structure.

Unplug the PDB

Connect as a privileged user
[oracle@kirlinc admin]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 2 21:12:25 2014

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

Enter password: 

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

SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
	 2 PDB$SEED
	 3 PDBORA12
	 4 PDBNONCDB


Close the PDB to be unplugged
SQL> alter pluggable database pdbnoncdb close immediate;

Pluggable database altered.


Unplug the PDB
SQL> alter pluggable database pdbnoncdb unplug into '/data/oracle/app/oracle/oradata/ora12/pdbnoncdb.xml';

Pluggable database altered.


Drop the unplugged PDB and keep the datafiles

SQL> drop pluggable database pdbnoncdb keep datafiles;

Pluggable database dropped.


PLUG the PDB

Compatibility check
The fucntion DBMS_PDB.CHECK_PLUG_COMPATIBILITY is used to see if the PDB to be plugged is compatible with the Host CDB.
Typical checks would be related to the database options, charcterset and OS endianness which should be same for the both source and host CDBs.


SQL> @chk_compatibility.sql
SQL> set serveroutput on
SQL> declare
  2  	compatible boolean;
  3  begin
  4  	compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '&xml');
  5  	if compatible then
  6  	   DBMS_OUTPUT.PUT_LINE('PDB is compatible');
  7  	else
  8  	   DBMS_OUTPUT.PUT_LINE('PDB is not compatible');
  9  	end if;
 10  end;
 11  /
Enter value for xml: /data/oracle/app/oracle/oradata/ora12/pdbnoncdb.xml
old   4:    compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '&xml');
new   4:    compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/data/oracle/app/oracle/oradata/ora12/pdbnoncdb.xml');
PDB is compatible

PL/SQL procedure successfully completed.


Plug the PDB using the XML file
Plug the database with a new naame using MOVE method..Other options for this can be "move as clone", "copy" or "nocopy" 

SQL> create pluggable database PDB2 using '/data/oracle/app/oracle/oradata/ora12/pdbnoncdb.xml' 
move FILE_NAME_CONVERT=('/data/oracle/app/oracle/oradata/ora12/noncdb/','/data/oracle/app/oracle/oradata/ora12/pdb2/');  

Pluggable database created.


Open the PDB

SQL> alter pluggable database pdb2 open;

Pluggable database altered.


Check the status of the newly plugged in PDB

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 2 PDB$SEED			  READ ONLY
	 3 PDBORA12			  READ WRITE
	 4 PDB2 			  READ WRITE



Verify the datafiles location for the newly plugged in PDB

SQL> select con_id,file_name from cdb_data_files;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
	 4 /data/oracle/app/oracle/oradata/ora12/pdb2/users01.dbf
	 4 /data/oracle/app/oracle/oradata/ora12/pdb2/sysaux01.dbf
	 4 /data/oracle/app/oracle/oradata/ora12/pdb2/system01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/users01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/undotbs01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/sysaux01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/system01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/system01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/sysaux01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/pdbora12_users01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/system01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/sysaux01.dbf

12 rows selected.

 

This example is to show how you can drop a PDB. You need to be aware that "INCLUDING DATAFILES" clause will remove all the associated datafiles for the database.

Close the PDB that needs to be dropped

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.


Drop the PDB including datafiles

SQL> drop pluggable database pdb2 including datafiles;

Pluggable database dropped.


Check to see if the PDB exists

SQL>  select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 2 PDB$SEED			  READ ONLY
	 3 PDBORA12			  READ WRITE


Check to see if any of the dropped PDB datafiles exist

SQL>  select con_id,file_name from cdb_data_files;

    CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/system01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/sysaux01.dbf
	 3 /data/oracle/app/oracle/oradata/ora12/pdbora12/pdbora12_users01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/system01.dbf
	 2 /data/oracle/app/oracle/oradata/ora12/pdbseed/sysaux01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/users01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/undotbs01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/sysaux01.dbf
	 1 /data/oracle/app/oracle/oradata/ora12/system01.dbf

9 rows selected.


[oracle@kirlinc admin]$ ls -lart /data/oracle/app/oracle/oradata/ora12/pdb2/
total 8
drwxr-x---. 6 oracle oinstall 4096 Apr  3 08:10 ..
drwxr-x---. 2 oracle oinstall 4096 Apr  3 08:22 .
 

 

 

Cheers!

rajkiran

Copyright ©2018 Rajkiran Ghanta (deegeplanet.com)