12c Database Commands

12c Database Commands

1. Create and open a new Oracle 12c Pluggable database (PDB)

Create a pdb using the seed:

Now we will create and open a new Pluggable database (PDB) called My_PDB.
Every CDB has a standard template of a PDB whose name is PDB$Seed. We actually create a new PDB by cloning this Seed. See below a SQL example :

Pre-requisite
connect to root with CREATE PLUGGABLE DATBASE privileges.
configure a storage location (and ensure disk space) for the new database’s files.
select a unique name for the new database.

create pluggable database My_PDB admin user App_Admin identified by pass file_name_convert = ('/pdbseed/', '/my_pdb/');

Ex:
create pluggable database lub admin user pdbmqm identified by oracle file_name_convert=('/u02/app/oracle/oradata/orcl/pdbseed', '/u02/app/oracle/oradata/orcl/lub');

The file_name_convert clause specifies how names for the new files are derived from those of the seed using the same scheme as you know from RMAN. During the PDB creation Oracle copies just 2 database files for System and Sysaux tablespaces to the new location of our PDB. The rest of database files for undo, redo, and so on is global for the whole CDB and belongs to the special container called CDB$Root.

The admin user clause is mandatory. In an extended form, it lets you grant privileges and roles to the new user. This user is able to start a new session only within My_PDB.

On completion of a create pluggable database command, the new PDB will be in MOUNTED mode. Before you can start a session in the new PDB, it must be open. So, let’s open our PDB using below SQL :

alter pluggable database My_PDB open;

2. Check Container Database (CDB) and Pluggable database (PDB) files

select con_id, tablespace_name, file_name from cdb_data_files where file_Name like '%/cdb1/pdbseed/%' or file_Name like '%/cdb1/my_pdb/%' order by 1, 2;

CON_ID Tablespace_Name File_Name
------ ------------ -------------------------------------------------
2 SYSAUX /opt/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
2 SYSTEM /opt/oracle/oradata/cdb1/pdbseed/system01.dbf
3 SYSAUX /opt/oracle/oradata/cdb1/My_PDB/sysaux01.dbf
3 SYSTEM /opt/oracle/oradata/cdb1/My_PDB/system01.dbf

3. Open all Oracle 12c Pluggable Databases (PDB)

Each PDB within a CDB has its own Open_Mode and Restricted status in each instance of a RAC. The possible values Open_Mode are MOUNTED, READ ONLY, and READ WRITE; and the possible values for the Restricted status are YES and NO when the PDB is open and otherwise null.

Starting an instance (which opens the entire CDB) does not cause PDBs to be opened. The alter pluggable database statement is used to set the Open_Mode. You give in SQL either the name of a particular PDB or use the keyword all, thus:

alter pluggable database all open;

4. Close all Oracle 12c Pluggable Databases (PDB) in CDB
This SQL statement closes all the PDBs in the CDB:

alter pluggable database all close;

Show the open time of PDBs

select substr(name,1,10) name, con_id, open_mode, to_char(open_time,'DD-MON-YYYY') open_time, total_size from v$pdbs;

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME   OPEN_MODE
---------- ------------------------------ ----------
 2 PDB$SEED   READ ONLY
 3 ORCL   READ WRITE
 4 PROD   READ WRITE

or

show pdbs

How to set the pdb database:

SQL> alter session set container=PROD;
SQL> show con_name

CON_NAME
------------------------------
PROD
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/cdb1/prod/system01.dbf
/home/oracle/app/oracle/oradata/cdb1/prod/sysaux01.dbf

How to close the PDB database:
alter pluggable database devpdb1 close immediate;

How to set the container database:
alter session set container = <db_name>;

How to check the container database:
show con_name

How to drop the PDB database:
drop pluggable database My_PDB keep datafiles;

How to Tell If the Database Is a CDB
select name, cdb, con_id from v$database;

Viewing Information About Containers
select name, con_id, db_id from v$containers;

Viewing Information About the PDBs
select pdb_id, pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB
select name, open_mode, restricted, open_time from v$pdbs;

Viewing the History of PDBs
select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property;

Viewing all the tablespaces in a CDB
select tablespace_name, con_id from cdb_tablespaces;</em>

Note:

1. After creating a PDB database open it atleast once in read-write mode to complete the integration with the CDB.

2. Memory and processes are required at container database level only.