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
very informative
Thanks Syed
Thanks for Sharing