Start/Stop CDB/PDB

Sumit Kumar

How to Startup/Shutdown of container database ?

When you open a CDB, its PDBs are mounted. Use the ALTER PLUGGABLE DATABASE statement to modify the open mode of one or more PDBs.
You can create, modify, and drop tablespaces for the CDB and for individual PDBs. You can specify the default temporary tablespace for the CDB.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];

List all PDBs and their status in container CDB.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL01 READ WRITE
PDBORCL02 READ WRITE

SQL> select CON_ID,DBID,NAME,OPEN_MODE,OPEN_TIME from v$pdbs;
CON_ID DBID NAME OPEN_MODE OPEN_TIME
---------- ---------- ------------------------------ ---------- -----
2 1532999638 PDB$SEED READ ONLY 13-JAN-15 04.39.00.089 PM +05:30
3 2220732750 PDBORCL01 READ WRITE 13-JAN-15 04.35.55.778 PM +05:30
4 820603074 PDBORCL02 READ WRITE 14-JAN-15 12.27.03.465 PM +05:30

Each container has a unique ID and name in a CDB. You can use the CON_ID and CON_NAME parameters in the USERENV namespace to determine the current container ID and name with the SYS_CONTEXT function.
For example, the following query returns the current container name:

SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------
CDB$ROOT

How to change to different Container?

ALTER SESSION SET CONTAINER=PDBORCL02;
ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER SESSION SET CONTAINER = PDB$SEED;

What are the CDB administrator roles?

Grant the CDB_DBA role to CDB administrators Grant the PDB_DBA role to PDB administrators

How to move data between PDBs?

You can move data between PDBs using the same methods that you would use to move data between non-CDBs.
For example, you can transport the data or use Data Pump export/import to move the data.

How to use a standby database for CDB/PDB?

Oracle Data Guard can configure a physical standby or a logical standby of a CDB. Data Guard operates on the entire CDB, not on individual PDBs.

How to drop PDB/CDB database?

When you drop a CDB, the root, seed, and all of its PDBs (including their data) are also dropped. You can also drop individual PDBs with the DROP PLUGGABLE DATABASE statement.

How to generate AWR report for PDB/CDB ?

A common user whose current container is the root can view AWR data for the root and for PDBs. A user whose current container is a PDB can view AWR data for the PDB only.

How to set an Initialization Parameter for All Containers or PDB?

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;
Setting an Initialization Parameter for the Root ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;

How to Create Common Users for all containers?

CONN / AS SYSDBA
-- Create the common user using the CONTAINER clause.
CREATE USER c##test_user1 IDENTIFIED BY password1 CONTAINER=ALL;
GRANT CREATE SESSION TO c##test_user1 CONTAINER=ALL;

-- Create the common user using the default CONTAINER setting.
CREATE USER c##test_user2 IDENTIFIED BY password1;
GRANT CREATE SESSION TO c##test_user2;

How to Create Local Users?

CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdb1;

-- Create the local user using the CONTAINER clause.
CREATE USER test_user3 IDENTIFIED BY password1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO test_user3 CONTAINER=CURRENT;

What are important dictionary views for managing CDB and PDB in 12C database?

GV$PDBS
DBA_PDBS
CDB_PDBS
DBA_PDB_HISTORY
CDB_PDB_HISTORY
PDB_PLUG_IN_VIOLATIONS
PDB_ALERTS
DBA_PDB_SAVED_STATES
CDB_PDB_SAVED_STATES
CDB_USERS