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

Create a pluggable database

dba oracle montreal create pdb oracle12c
 

How to create a pluggable database?

One of my clients in Montreal wants to create an empty pluggable database in a multitenant Oracle database. How can I do that?

In this example, I will not use the plug/unplug/clone features.

tnsnames.ora:

# PDB:
MY-PDB-NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my-hostname.ca)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = my-pdb-name.ca)
(SERVER = DEDICATED)
)
)

create pluggable database MY-PDB-NAME admin user PDBA identified by xxxxx
file_name_convert = ('/u02/oradata/CDB-NAME/seed','/u02/oradata/CDB-NAME/MY-PDB-NAME');

alter pluggable database MY-PDB-NAME open;

alter session set container=MY-PDB-NAME;

alter system set listener_networks = '((NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my-hostname.ca)(PORT=1525)))))';

-- Restart the listener on 1525 port 


-- Create the user and the index tablespace

CREATE BIGFILE TABLESPACE MY-PDB-NAME_DATA DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_data.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE BIGFILE TABLESPACE MY-PDB-NAME_INDEX DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_index.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


-- Create the application user
CREATE USER MY-PDB-NAME
 IDENTIFIED BY xxxxx
 DEFAULT TABLESPACE MY-PDB-NAME_DATA
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 
 -- 2 Roles for MY-PDB-NAME
 GRANT CONNECT TO MY-PDB-NAME;
 GRANT RESOURCE TO MY-PDB-NAME;
 GRANT SELECT_CATALOG_ROLE TO MY-PDB-NAME;
 ALTER USER MY-PDB-NAME DEFAULT ROLE ALL;
 
 -- 1 System Privilege for MY-PDB-NAME
 GRANT UNLIMITED TABLESPACE TO MY-PDB-NAME;
 
 -- 2 Tablespace Quotas for MY-PDB-NAME
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_DATA;
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_INDEX;

GRANT CREATE ANY VIEW TO MY-PDB-NAME;


-- Test the connection to the pluggable database:
sqlplus MY-PDB-NAME/xxxxx@MY-PDB-NAME
Connected.
SQL>REM THE END

Author: Vincent Fenoll
Compatible: Oracle 12c with the multitenant option.