How to create and configure a Pluggable Database(PDB) in Oracle 12c Release 12.2

1.Oracle Universal Installer(OUI)

2.Database Configuration Assistant(DBCA)

The Database Configuration Assistant (DBCA) gives similar options to the Oracle Universal Installer (OUI). The "Creation Mode" page allows you to enter the default installation configuration details directly.

Step0: run the DBCA

[oracle@ol7 bin]$ dbca
[oracle@ol7 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@ol7 bin]$ 

Step1: Select the operation that you want to perform , Create a databse.

Step2: Select Database Creation Mode.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[oracle@ol7 ~]$ 
[oracle@ol7 ~]$ 
[oracle@ol7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 7 14:13:24 2023

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


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

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> 

 The following sections describe some of these options.

Create a Pluggable Database (PDB) using the DBCA

[oracle@ol7 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@ol7 bin]$ 
[oracle@ol7 bin]$ dbca

On the "Manage Pluggable Databases" screen shown previously, select the "Create a Pluggable Database" option and click the "Next" button. On the resulting screen, select the container database to house the new pluggable database and click the "Next" button.

 

 Select the "Create a new Pluggable Database" option and click the "Next" button. If you were plugging in a previously unplugged database, you would select the PDB Archive or PDB File Set options to match the format of the files containing the unplugged PDB.

 

 Enter the pluggable database name, database location and admin credentials, then click the "Next" button.

 If you are happy with the summary information, click the "Finish" button.

Wait while the pluggable database is created. Once complete, click the "OK" button on the message dialog and the "Close" button on the main screen.  

 Unplug a Pluggable Database (PDB) using the DBCA

[oracle@ol7 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@ol7 bin]$ 
[oracle@ol7 bin]$ dbca

On the "Manage Pluggable Databases" screen shown previously, select the "Unplug a Pluggable Database" option and click the "Next" button. On the resulting screen, select the container database that houses the pluggable database to be unplugged and click the "Next" button.

 

 

 

 Plugin a Pluggable Database (PDB) using the DBCA

[oracle@ol7 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@ol7 bin]$ 
[oracle@ol7 bin]$ dbca

 

 

 

 

 Delete a Pluggable Database (PDB) using the DBCA

[oracle@ol7 bin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin
[oracle@ol7 bin]$ 
[oracle@ol7 bin]$ dbca

 On the "Manage Pluggable Databases" screen shown previously, select the "Delete a Pluggable Database" option and click the "Next" button. On the resulting screen, select the container database that houses the pluggable database to be deleted and click the "Next" button.

 

 

 

 Configure a Pluggable Database (PDB) using the DBCA

On the "Manage Pluggable Databases" screen shown previously, select the "Configure a Pluggable Database" option and click the "Next" button. On the resulting screen, select the container database that houses the pluggable database to be configured and click the "Next" button.

 

 

 https://ol7.localdomain:5500/em

[oracle@ol7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 7 16:00:53 2023

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


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

SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>     
SQL> alter session set container=PDB1;

Session altered.

SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> select DBMS_XDB_CONFIG.GETHTTPPORT() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
			    0

SQL> select DBMS_XDB_CONFIG.GETHTTPSPORT() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
			  5500

SQL> prompt execute the DBMS_XDB.setHTTPSPort procedure to set the HTTPS port 5501
execute the DBMS_XDB.setHTTPSPort procedure to set the HTTPS port 5501
SQL> 
SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5501);

PL/SQL procedure successfully completed.

SQL> select DBMS_XDB_CONFIG.GETHTTPSPORT() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
			  5501

SQL> prompt execute DBMS_XDB.setHTTPPort procedure to set the HTTP port 5511 for EM Express
execute DBMS_XDB.setHTTPPort procedure to set the HTTP port 5511 for EM Express
SQL> 
SQL> exec DBMS_XDB_CONFIG.SETHTTPPORT(5511);

PL/SQL procedure successfully completed.

SQL> select DBMS_XDB_CONFIG.GETHTTPPORT() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
			 5511

SQL> 

  https://ol7.localdomain:5501/em

Create a Pluggable Database (PDB) Manually

To create a new pluggable database from the seed database, all we have to do is tell Oracle where the file should be placed.

 We can do this using one of three methods. If we are using Oracle Managed Files (OMF) we don't need to worry about the file placement. Oracle will handle it for us.

ALTER SYSTEM SET db_create_file_dest = '/u02/oradata';

From 12.1.0.2 onward there is an inline variation of this using the CREATE_FILE_DEST clause. The path set in this clause will be used as the OMF location for the new PDB.

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 7 16:16:48 2023

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


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

SQL> 
SQL> 
SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> 
SQL> 
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm CREATE_FILE_DEST='/u01/app/oracle/oradata';

Pluggable database created.

SQL> 

The second method uses the FILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

oracle@ol7 datafile]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 7 16:28:03 2023

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


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

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/');

Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter before calling the command without using the FILE_NAME_CONVERT clause.

CONN / AS SYSDBA

ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb4/';

CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY pdb_adm;

Every time there is a need to convert file locations, either of these two methods will work. For the remainder of the article I will stick to using the FILE_NAME_CONVERT method to cut down on the variations I have to display.

We can see the PDBs are present by querying the DBA_PDBS and V$PDBS views.

SQL> show user;
USER is "SYS"
SQL> 
SQL> 
SQL> show parameter pdb_file_name_convert;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert		     string
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> COLUMN pdb_name FORMAT A20
SQL> 
SQL> 
SQL> select pdb_name,status from dba_pdbs order by pdb_name;

PDB_NAME	     STATUS
-------------------- ----------
PDB$SEED	     NORMAL
PDB1		     NORMAL
PDB2		     NEW

SQL> column name format a20
SQL> select name,open_mode from v$pdbs order by name;

NAME		     OPEN_MODE
-------------------- ----------
PDB$SEED	     READ ONLY
PDB1		     READ WRITE
PDB2		     MOUNTED

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
	 4 PDB2 			  MOUNTED
SQL> 
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

Pluggable database altered.

SQL> select pdb_name,status from dba_pdbs order by pdb_name;

PDB_NAME	     STATUS
-------------------- ----------
PDB$SEED	     NORMAL
PDB1		     NORMAL
PDB2		     NORMAL

SQL> select name,open_mode from v$pdbs order by name;

NAME		     OPEN_MODE
-------------------- ----------
PDB$SEED	     READ ONLY
PDB1		     READ WRITE
PDB2		     READ WRITE

SQL>