To create, start ,close and drop a pluggable database

To create a pluggable database with an admin user PDBADM. 

When the "CREATE PDB" command is fired, this command additionally grants PDB_DBA role to the PDB administrator PDBADM.

Login as SYS user and set  container to an existing PDB say orclpdb.

By default when a new database is created in express edition the XEPDB1 PDB is already created.

To find out the list of existing PDB in a container you can run the following query as SYS or SYSTEM user.

select name, guid from v$pdbs;

NAME	GUID
PDB$SEED	2E446E4166A1407DA02E3D2EF05A301A
XEPDB1	ACBB912E832347A0BBD908E9A6B71AEF
ORCLPDB	AABB912E8534347A0SFGFD9A6B71PNQ

SQL> ALTER SESSION SET CONTAINER = orclpdb;

--to create a local user in a PDB

SQL> CREATE USER PDBADM IDENTIFIED BY PDBADM CONTAINER=CURRENT;

SQL> grant create session to PDBADM;

When this user is created, it has only the create session system privelage assigned to it.

select con_id,grantee,privilege from cdb_sys_privs where grantee='PDBADM';

 PDB2ADM                CREATE SESSION

There is no DBA ROLE assigned to it

select con_id,grantee,granted_role from cdb_role_privs where grantee='PDBADM';

no rows selected

Now to create a PDB you need to be logged in to the root container. 

If you try to create a new PDB from within an existing PDB you will get the following error

ORA-65040: operation not allowed from within a pluggable database

SQL> alter session set container=cdb$root;

Session altered.

To create the pluggable database

create pluggable database orclpdb1
admin user PDBADM
identified by PDBADM
FILE_NAME_CONVERT=('D:\app\oracle\oradata\ORCL\pdbseed','D:\app\oracle\oradata\ORCL\orclpdb1');

Pluggable database created.

Now we open the pluggable database created

SQL> alter pluggable database orclpdb1 open;

Now if we check, the PDBADM user will have the PDB_DBA role assigned to it

select con_id,grantee,granted_role from cdb_role_privs where grantee='PDBADM';

 PDBADM             PDB_DBA

To connect to the new PDB that is created we can add the following entry in the tns file New entry created TNS entry for a new PDB created

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb1)
    )
  )

Existing TNS Entry for the main container Database

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclpdb1)
    )
  )

Entry for new PDB to be done in listener.ora. The new listener.ora will be as below.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_home\bin\oraclr19.dll")
    )
	
   (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\ora_home)
      (SID_NAME = ALPHA)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\ora_home)
      (SID_NAME = ORCLPDB1)
    )	
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

start and stop the listener as follows

$lsnrctl stop
$lsnrctl start

If we will check in lsnrctl a new listener will be created for ths PDB

cmd> lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JUL-2019 01:09:40

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                10-JUL-2019 01:32:04
Uptime                    4 days 23 hr. 37 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...                                                                                                                                                                                            
Service "86b637b62fdf7a65e053f706e80a27" has 1 instance(s).                                                                                                                                    
  Instance "orcl", status READY, has 1 handler(s) for this service...                                                                                                                                          
Service "8d50ba5e384412fbe053193ec40a5c2c" has 1 instance(s).                                                                                                                                    
  Instance "orcl", status READY, has 1 handler(s) for this service... 
....
....  
Service "orcl" has 1 instance(s).                                                                                                                                                                
  Instance "orcl", status READY, has 1 handler(s) for this service...                                                                                                                                          
Service "orclpdb" has 1 instance(s).                                                                                                                                                             
  Instance "orcl", status READY, has 1 handler(s) for this service...                                                                                                                                          
Service "orclpdb1" has 1 instance(s).                                                                                                                                                             
  Instance "orcl", status READY, has 1 handler(s) for this service...                                                                                                                                          
The command completed successfully 

Now u can connect to the new PDB with the connect descriptor mentioned in the tns file

C:\Users\Administrator>sqlplus system/oracle@orclpdb1
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 16 10:51:45 2021

Version 19.3.0.0.0

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

Last Successful login time: Wed Jun 16 2021 10:50:25 +06:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

--To close a pluggable database

SQL> alter pluggable database orclpdb1 close;

--To drop a PDB In sqlplus ensure the current container is the CDB root container. 

If the PDB is plugged into the CDB root, then the current container must be the CDB root. 

If the PDB is plugged into an application root, then the current container must be that application root or the CDB root. 

Dropping PDB salespdb While Keeping Its Data Files

DROP PLUGGABLE DATABASE salespdb KEEP DATAFILES;

Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb INCLUDING DATAFILES;

Steps to start the database and pluggable databases

$ sqlplus sys@orcl as sysdba
password: 
SQL> startup
ORACLE instance started.

Total System Global Area 3355443200 bytes
Fixed Size                  8791392 bytes
Variable Size            1929382560 bytes
Database Buffers         1409286144 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.

SQL>alter pluggable database all open;

SQL> ALTER SESSION SET CONTAINER = orclpdb1;

SQL> conn system/oracle123@orclpdb
Connected.

When opening the pluggable database if you get below error 

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor 

on linux check whether local listener exists or not by using command.

SQL> show parameter local_listener

For me local_listener value is empty. set the listener dynamically by using command

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1522))' scope=both; 

Then do register by using alter command

SQL> alter system register;

Now check whether listener is added or not

SQL> show parameter local_listener. 

Now listener is added successfully with Value = (ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1522)). If HOST=LOCALHOST still gives error, you can give the actual ip address of the linux server. eg HOST=

Now if I try to connect to the PDB I am able to connect successfully.

References 

https://dba.stackexchange.com/questions/153335/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect

Thought for the day

Let love and faithfulness never leave you; 

  bind them around your neck, 

  write them on the tablet of your heart. 

Then you will win favor and a good name 

  in the sight of God and man.

Proverbs 3:3-4