CDB and PDBs 19c Commands

Finding my database is Multitenant or not?

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME Multitenant Option ? OPEN_MODE CON_ID

--------- -------------------------- -------------------- ----------

CTEST1 Multitenant Option enabled READ WRITE 0

Connecting the CDB to PDB

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

Display the list of available services for the root and the PDBs.

SQL> Show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 appspdb READ WRITE NO

Verifying the status

select pdb_name, status from cdb_pdbs;

select name, open_mode from v$pdbs;

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

Connecting to PDBS

SQL> alter session set container="appsdb";

Session altered.

SQL>

Startup and shutdown the CDB

Before startup and shutdown ensure your connected sysdba

Shutdown

This operation first close the PDBs and dismount the control files then finally shutdown the instance.

SQL>show con_name

SQL>shutdown immediate

Startup

This operation first starts the instance and mount the control files then finally open the root container

SQL>startup

Verify

SQL>select name, open_mode from v$pdbs;

Startup and shutdown the PDBs

Shutdown

When connect to current PDB:

SQL> alter pluggable database close;

When connect to root:

SQL> alter pluggable database <PDB_NAME> close;

SQL>alter pluggable database <PDB_Name> close instances =all;

SQL>alter pluggable database <PDB_NAME> close immediate;

Startup

When connect to current PDB:

SQL> alter pluggable database open;

When connect to root:

SQL> alter pluggable database <PDB_NAME> open;

SQL>alter pluggable database <PDB_Name> open read write instances =all;

Verify

SQL>select name, open_mode from v$pdbs;

Renaming PDBs

SQL>alter pluggable database pdb3 close immediate;

SQL>alter pluggable database pdb3 open restricted;

SQL>select name, restricted from v$pdbs;

SQL>alter pluggable database pdb3 rename global_name to pdb3_bis;

Managing tablespaces

Create a tablespace in a CDB

Display the root tablespace, data files, temp files

SQL>select tablespace_name, con_id from cdb_tablespaces where con_id=1;

SQL> select file_name, con_id from cdb_data_files where con_id=1;

SQL> select file_name, con_id from cdb_temp_files where con_id=1;

Creating tablespace

SQL> create tablespace test datafile '+DATAC1/TEST/DATAFILE/test01.dbf' size 10M;

Tablespace created.

SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;

Create Temp tablespace CDB

SQL>create temporary tablespace temp_root tempfile '/u01/app/oracle/oradata/cdb1/temproot01.dbf' SIZE 10M;

SQL>select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY' and con_id=1;

SQL>select file_name, con_id from cdb_temp_files where con_id=1;

Drop the tablespaces that you created in the CDB root.

SQL>drop tablespace test including contents;

SQL>drop tablespace temp_root including contents;

Create a tablespace in a PDB

SQL>create tablespace apex datafile '/u01/app/oracle/oradata/cdb1/pdb3/apex01.dbf' SIZE 10M;

SQL>select tablespace_name, con_id from cdb_tablespaces order by con_id;

SQL>select file_name, con_id from cdb_data_files order by con_id;

SQL>select file_name from dba_data_files;

Create Temp tablespace in a PDB

SQL> select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY';

SQL> select file_name from dba_temp_files;

SQL> create temporary tablespace temp_pdb3 tempfile '/u01/app/oracle/oradata/cdb1/pdb3/temppdb301.dbf' SIZE 10M;

Managing USERS

Creating common user

SQL>select username, common, con_id from cdb_users where username like 'C##%';

SQL> create user c##Friday identified by testuser container=all;

User created.

SQL> grant dba to c##Friday;

Grant succeeded.

SQL> conn c##Friday/testuser

Connected.

SQL>

Create local User

SQL> create user sanjeev identified by testuser container=current;

select username, common, con_id from cdb_users where username ='sanjeev';

Dropping PDBs

connect / as sysdba

alter pluggable database all close immediate;

select name, open_mode from v$pdbs;

Drop the PDBs, including their data files.

SQL>drop pluggable database pdb3_bis including datafiles;

SQL>select name from v$pdbs;

Monitoring the Database

Alert log file location

SQL>select * from v$diag_info;

Where to look for PDB errors if encounter a problem?

SQL>select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;