Basic Multitenant CDB / PDB Operations

Here is a FAQ on Multitenant basic CDB / PDB Operations.

 

How do I know if my database is Multitenant or not?

select NAME, DECODE(CDB, ‘YES’, ‘Multitenant Option enabled’, ‘Regular 12c Database: ‘) “Multitenant Option ?” , OPEN_MODE, CON_ID from V$DATABASE;

 

What Pluggable databases do I have in this container database?

select CON_ID, NAME, OPEN_MODE from V$PDBS;

 

How do I connect to my Pluggable Database PDB1?

Connected as sysdba in the CDB:

alter session set container = pdb1;

With a tnsnames alias:

pdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = my-host)
(Port = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PDB1)
)
)

Using easy connect

Format: CONNECT username/password@host[:port][/service_name][:server][/instance_name]
$ sqlplus scott/tiger@//localhost:1521/pdb1
$ sqlplus scott/tiger@//my-server-name:1525/pdb1

To switch back to the main container:
ALTER SESSION SET CONTAINER = CDB$ROOT;

Where am I connected?

SQL> show con_name
CON_NAME
———
PDB1

 

How to stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE [Instances=all];
(connected on the PDB) ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE [Instances=all];

This will stop the associated service too.
Manually stopping the associated service will not close the PDB. You have to use the SQL command.

 

How to start and stop a PDB in Oracle RAC?

On the current node [or on all the nodes]:
(connected on the CDB) ALTER PLUGGABLE DATABASE PDB1 OPEN [Instances=all;]
(connected on the PDB) ALTER PLUGGABLE DATABASE OPEN [Instances=all;]

You can also start the PDB with the associated service (srvctl start service -d my-db_unique_name -s pdb1)
This will NOT start the service(s) associated with this PDB.

 

Author: Vincent Fenoll – Oracle DBA Montreal