[Oracle 12c] Exploring the Multitenant Architecture: CDBs and PDBs

Hello guys,

I have been studying Oracle 12c as a preparation for my upgrade certification. And this will be my second article on Oracle 12c. I have a bunch of scratch notes on 12c but I have a lot of deployments: upgrades and migration to 12c so, I haven't finalize most of them.

This simple article presents how to create Pluggable Databases (PDBs) from the seed PDB. Remember that we can also create (clone) PDBs using an existing regular PDB.

Let begin by familiarizing ourselves with the root CDB or the CDB$ROOT

How to connect to a CDB?

[oracle@orcl12c-serv01 ~]$ echo $ORACLE_SID
ORCLDB
[oracle@orcl12c-serv01 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@orcl12c-serv01 ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
-------------
CDB$ROOT --> This is our current container, which is the ROOT CDB.

Connecting to a CDB is the same as you connect with the regular, non-container database (11g and below).

What are the components of the root container (ROOT CDB) by default?

SQL> select name from v$controlfile;

NAME --> Set of control files
-----------------------------------------------------------
+DATA/ORCLDB/control01.ctl
+DATA/ORCLDB/control02.ctl


SQL> select member from v$logfile;

MEMBER --> Set of online redolog files
-----------------------------------------------------------------
+DATA/ORCLDB/redo03.log
+DATA/ORCLDB/redo02.log
+DATA/ORCLDB/redo01.log

SQL> select name from v$tempfile;

NAME --> Set of tempfiles for the entire CDB (CDB and the PDBs)
----------------------------------------------------------------+DATA/ORCLDB/temp01.dbf
+DATA/pdbseed_temp01.dbf


SQL> select file_name from dba_data_files;

FILE_NAME --> One UNDO, SYSTEM, SYSAUX and the default USERs tablespace
-------------------------------------------
+DATA/ORCLDB/users01.dbf
+DATA/ORCLDB/undotbs01.dbf
+DATA/ORCLDB/sysaux01.dbf
+DATA/ORCLDB/system01.dbf


What are the components of the SEED PDB (PDB$SEED) by default?

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME                                 STATUS
---------------------------------------- -------------
PDB$SEED                                 NORMAL

SQL> alter session set container = PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED


Let us run the same sets of commands to see the components of the default SEED PDB.

SQL> alter session set container = PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED


SQL> select name from v$controlfile;

NAME --> PDB$SEED uses THE SAME controlfile as the CDB. It does NOT have its own set of controlfiles.
------------------------------------
+DATA/ORCLDB/control01.ctl
+DATA/ORCLDB/control02.ctl



SQL> select member from v$logfile;

MEMBER --> It also uses THE SAME redologs as the CDB. It does NOT have its own set of online redolog files.
-----------------------------------
+DATA/ORCLDB/redo03.log
+DATA/ORCLDB/redo02.log
+DATA/ORCLDB/redo01.log



SQL> select name from v$tempfile;

NAME --> It uses its OWN temporary tablespace
----------------------------------
+DATA/pdbseed_temp01.dbf


SQL> select file_name from dba_data_files;

FILE_NAME --> It has its own SYSTEM and SYSAUX tablespace. ONLY these two tablespaces are there, and should be there by default.
---------------------------------------------
+DATA/pdb$seed_system01.dbf
+DATA/pdb$seed_sysaux01.dbf


Now, let us create our own PDB named GABPDB1 using the seed PDB as our template. First, I created the directory where I will store the datafiles of my new PDB.

ASMCMD> mkdir GABPDB1
ASMCMD> cd GABPDB1/
ASMCMD> pwd
+DATA/GABPDB1 --> This will be the location for the datafiles

Then connect to the root CDB,


SQL> show con_name

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


SQL> CREATE PLUGGABLE DATABASE gabpdb1 ADMIN USER gabpdb1admin IDENTIFIED BY "oracle_4U"
  2  DEFAULT TABLESPACE GABP1TBS
  3  DATAFILE '+DATA/GABPDB1/GABP1TBS01.dbf' SIZE 500M AUTOEXTEND OFF
  4  PATH_PREFIX = '+DATA/GABPDB1/'
  5  FILE_NAME_CONVERT = ('+DATA/', '+DATA/GABPDB1/');

Pluggable database created.


Let's view the PDBs in our current container.


SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME             STATUS
-------------------- -------------
PDB$SEED             NORMAL
GABPDB1              NEW --> All new created PDBs will have a status of NEW until you open them in READ WRITE mode.

SQL> ALTER PLUGGABLE DATABASE GABPDB1 OPEN READ WRITE;

Pluggable database altered.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME             STATUS
-------------------- -------------
PDB$SEED             NORMAL
GABPDB1              NORMAL

Now let's check what do we have in our CDB and in our newly created PDB.


SQL> show con_name

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


SQL> select file_name from dba_data_files;

FILE_NAME --> NOTHING was added. Root CDB still has the same datafiles.
-------------------------------------------
+DATA/ORCLDB/users01.dbf
+DATA/ORCLDB/undotbs01.dbf
+DATA/ORCLDB/sysaux01.dbf
+DATA/ORCLDB/system01.dbf

Also, the redolog files, control files and still the same.
Let us now explore the newly created GABPDB1 from cloning the seed PDB.

SQL> alter session set container = GABPDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
GABPDB1


SQL> select name from v$controlfile;

NAME --> It does NOT have its own controlfile. It uses the SAME controlfiles as the root CDB.
------------------------------
+DATA/ORCLDB/control01.ctl
+DATA/ORCLDB/control02.ctl


SQL> select member from v$logfile;

MEMBER --> It does NOT have its own redolog files. It uses the SAME redolog files as the root CDB.
------------------------------
+DATA/ORCLDB/redo03.log
+DATA/ORCLDB/redo02.log
+DATA/ORCLDB/redo01.log

SQL> select name from v$tempfile;

NAME --> It has its OWN temporary tablespace based on the seed PDB template.
---------------------------------------
+DATA/GABPDB1/pdbseed_temp01.dbf


SQL> select file_name from dba_data_files;

FILE_NAME --> It has its OWN SYSTEM and SYSAUX based on the seed PDB template. And it has its own set of datafiles, which we created earlier.
-------------------------------------------
+DATA/GABPDB1/pdb$seed_system01.dbf
+DATA/GABPDB1/pdb$seed_sysaux01.dbf
+DATA/GABPDB1/gabp1tbs01.dbf

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

--> It shares the same UNDO tablespace as the root CDB and the other PDBs.

We also noticed that the naming convention of the files for our newly created PDB is quite off, so let's create a new PDB with a more specific naming convention.

ASMCMD> mkdir GABPDB2
ASMCMD> cd GABPDB2
ASMCMD> pwd
+DATA/GABPDB2

SQL> show con_name

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

--> In creating PDB, you SHOULD always be connected ot the root CDB or you will get the error: ORA-65040: operation not allowed from within a pluggable database


CREATE PLUGGABLE DATABASE gabpdb2 ADMIN USER gabpdb2admin IDENTIFIED BY "oracle_4U"
DEFAULT TABLESPACE GABP2TBS 
DATAFILE '+DATA/GABPDB2/GABP2TBS01.dbf' SIZE 500M AUTOEXTEND OFF
PATH_PREFIX = '+DATA/GABPDB2/'
FILE_NAME_CONVERT = ('+DATA/pdb$seed_sysaux01.dbf','+DATA/GABPDB2/gabpdb2_sysaux01.dbf',
'+DATA/pdb$seed_system01.dbf','+DATA/GABPDB2/gabpdb2_system01.dbf',
'+DATA/pdbseed_temp01.dbf','+DATA/GABPDB2/gabpdb2_temp01.dbf');

Pluggable database created.

Now the newly created PDB has a better naming convention!

SQL> alter session set container = gabpdb2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
GABPDB2

SQL> alter pluggable database gabpdb2 open read write;

Pluggable database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------
+DATA/GABPDB2/gabpdb2_temp01.dbf

SQL>  select file_name from dba_data_files;

FILE_NAME
--------------------------------------------
+DATA/GABPDB2/gabpdb2_system01.dbf
+DATA/GABPDB2/gabpdb2_sysaux01.dbf
+DATA/GABPDB2/gabp2tbs01.dbf

In the future articles, I will be discussing how to plug and uplug PDBs from a local CDB and to a remote CDB. How to connect clients to your PDBs.

Let's share and make others love Oracle, too.

Comments

  1. AnonymousFebruary 17, 2016 at 5:40 PM

    The more I read the article. The more i learn about PDBs! Thanks for sharing! And I would love to read more.

    Dan

    ReplyDelete
  2. AnonymousNovember 23, 2019 at 3:07 AM

    Good post!

    ReplyDelete

Post a Comment

Popular posts from this blog

RMAN Restoration to New Server with Different Directory and New Database Name

In this case, I have an existing production database with RMAN Level 0 backup: CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\flash_recovery_area\galaxy\backupsets\autobackup\SAMSUNG-ctl_%F'; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'C:\flash_recovery_area\galaxy\backupsets\level0\level0-%d-%T-%U' MAXPIECESIZE 4G; DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 7'; SHOW ALL; REPORT SCHEMA; LIST BACKUP OF DATABASE; REPORT NEED BACKUP; REPORT UNRECOVERABLE; LIST EXPIRED BACKUP BY FILE; LIST ARCHIVELOG ALL; REPORT OBSOLETE; CROSSCHECK BACKUP DEVICE TYPE DISK; CROSSCHECK COPY OF ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK; DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK; RUN { sql 'alter system archive log current'; BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG DELETE INPUT; } LIST BACKUP ...
Read more

[Script] Tablespace Usage Alert

Hi, This is another variation of the previous scripts presented in this blog site. The building blocks of this script is the same as the Disk Space and ASM Diskgroup Space Monitoring scripts. This will alert you via email if a certain tablespace  reaches the set threshold. Let's share and make others love Oracle, too. For tablespace monitoring alert scripts. We have the following: Tablespace Monitoring Script You will notice that there is a pattern in the scripts creation presented in this blog.  Part 1: Report Generation : Here it is the tablespace report Part 2: Report output string process : Scripts are created to process further report into columns. Conditional statement was created to test the values in the report. Part 3: Check if the file generates alert : Email is sent if entities met the threshold. # # # # # #----- Start of Script -----###### #!/bin/bash  # Report tablespace usage  sqlplus "/ as sysdba" << EOF SET LINES 700...
Read more

[Script] ASM Diskgroup Space Usage Alert

Hi Again, Another important task that a DBA should do is to monitor the ASM diskgroup usage. We do not just add space to tablespaces, create more tablespaces or resize datafiles to increase storage without checking if there is space to our ASM diskgroup. Also, when we perform RMAN backups stored in ASM or Datapump Exports stored in ASM, we have to monitor and get alerts if we are running out of space. This is a simple monitoring script. I hope this could help you. Let's share and make others love Oracle, too. ASM Diskgroup  Space Monitoring ######--- Start of Script -----###### #!/bin/bash  # Report ASM Diskgroup space usage  #NOTES:  # 1. There is really a slash on the v$asm_diskgroup so Unix interpreter will treat dollar sign as a literal $ # 2. Set your tablespace threshold by changing the value of the AND clause  " >= 80" sqlplus "/ as sysdba" << EOF SET LINES 700 SET FEEDBACK OFF SET TRIMSPOOL ON SET ECHO OFF SPOOL /tmp/ASMs...
Read more