Oracle 12c pluggable database commands

Here are very useful Oracle 12c pluggable database commands which will be very helpful in day-to-day DBA administration for Container and Pluggable database

Query to check if the database is CDB or non-CDB

Suppose you want to know if the target database is CDB (container database ) or Non-CDB ( Normal Non-container database). You can log in to the DB Box and source the Oracle DB and then find using the below query

sqlplus / as sysdba
SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID
--------------------------- --------- ----------
CDB01 YES 0

how to check container database in oracle 12c

Query to check containers in the CDB database. Each PDB and root is a container

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
1 CDB$ROOT READ WRITE 0
2 PDB$SEED READ ONLY 891289600
3 SEED READ WRITE 1248961280
4 TEST READ WRITE  1248961280
5 TEST2 READ WRITE 1248961280

If you are connected to a PDB, it will show that container only

SQL> alter session set container=PDB$SEED;
SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE
---------- -------------------- ------------------------------ ----------
2 PDB$SEED READ ONLY 891289600

Query to check PDB in the CDB database.

Connected to root container

SQL> alter session set container=CDB$ROOT;

Session altered.

select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
2 5604597646 PDB$SEED READ ONLY
3 823307170 SEED READ WRITE
4 458722011 TEST READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SEED READ WRITE NO
4 TEST READ WRITE NO

Connected to a particular PDB

SQL> alter session set container=SEED;

Session altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs
2 ;

CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ------------------------------
3 823307170 SEED READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SEED READ WRITE NO
SQL>

Startup and shutdown of PDB

Connected to root container

alter pluggable database test1_tech close;

alter pluggable database test1_tech close immediate;

alter pluggable database test1_tech open;

alter pluggable database test1_tech open read only ;

alter pluggable database test1_tech open force;

If the container is set, we can use the below command

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;
ALTER PLUGGABLE DATABASE OPEN FORCE;

ALTER PLUGGABLE DATABASE NOLOGGING;

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

STARTUP OPEN
STARTUP OPEN READ ONLY
STARTUP RESTRICT OPEN READ ONLY

How to add service in CDB

srvctl add service -db CDB01 -service TEST_NEW -pdb TEST
srvctl modify service -db CDB01 -service TEST_NEW -pdb TEST
srvctl remove service -db CDB01 -service TEST_NEW

BEGIN
DBMS_SERVICE.CREATE_SERVICE(
service_name => 'TEST_NEW',
network_name => 'TEST_NEW.com');
END;
/

BEGIN
DBMS_SERVICE.DELETE_SERVICE(
service_name => 'TEST_NEW');
END;
/

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
-------------------- ----------
CDB01 1
CDB01XDB 1
SYS$BACKGROUND 1
SYS$USERS 1
TEST 4
seed 3

6 rows selected.

Show command with 12c database

show con_name

CON_NAME
------------------------------
CDB$ROOT

show con_id

CON_ID
------------------------------
1

How to close and open all the PDB in a single command

alter pluggable database all close immediate;

alter pluggable database all open;

How to drop the pluggable database

drop pluggable database pdb3_test including datafiles;

Container storage

With the root container, we have cdb_tablespaces,cdb_data_files shows the tablespace and data files of all the containers in the CDB, and dba_tablespaces,dba_data_files will show the root tablespace and datafiles only

alter session set container=CDB$ROOT;

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME CON_ID
------------------------------------------------------------------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1

SYSTEM 2
SYSAUX 2

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------------------------------------ ----------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

When connected to a PDB, cdb_tablespaces,cdb_data_files shows the same information as  dba_tablespaces,dba_data_files for the PDB

How to rename the pluggable database

Here is the sql command to rename the pluggable database

alter pluggable database TEST rename global_name to TEST1;

How to check for the Local Undo feature in 12.2

Here is the query to check for the Local Undo feature in Oracle database 12.2. This feature enables local undo tablespace for each of the pdb’s

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED TRUE

Viewing the History of PDBs

COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;

I hope you like the compilation of these useful Oracle 12c pluggable database commands. Please provide feedback on it and tell me what else can be added to this list

Related Articles

srvctl commands: check out srvctl commands like srvctl status database, stop database, start database, config database,add database
Flashback PDB in Oracle Database 12c Release 2
PDB Relocate in Oracle database 12c Release 2
How to remotely clone a PDB or non-CDB in Oracle Database 12.1
Hot PDB cloning in Oracle database 12.2
Refreshable PDB in Oracle Database 12.2
https://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13720