CDB & PDB

Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB)
Oracle 12c Release 1 (12.1) introduced the Multitenant option.

Overview

The multitenant option represents one of the biggest architectural changes in the history of the Oracle database. The option introduced the concepts of the Container Database (CDB) and Pluggable Database (PDB).

Container Database (CDB) : 
On the surface this seems very similar to a conventional Oracle database, as it contains most of the working parts you will be already familiar with (controlfiles, datafiles, undo, tempfiles, redo logs etc.). It also houses the data dictionary for those objects that are owned by the root container and those that are visible to all PDBs.

Pluggable Database (PDB) : 
Since the CDB contains most of the working parts for the database, the PDB only needs to contain information specific to itself. It does not need to worry about controlfiles, redo logs and undo etc. Instead it is just made up of datafiles and tempfiles to handle it's own objects. This includes it's own data dictionary, containing information about only those objects that are specific to the PDB. From Oracle 12.2 onward a PDB can, and should, have a local undo tablespace.




This split of the data dictionary between common objects, in the root container, and PDB-specific objects, in the PDB's data dictionary, is very important, because this separation is what gives the multitenant option its flexibility. From the perspective of the PDB, the data dictionary is the union of the root and PDB data dictionaries, so internally the PDB feels very much like a normal Oracle database. For example, the DBA_% and ALL_% views within the PDB appears the same as any non-CDB database.

Oracle Managed Files (OMF) and Multitentant

Oracle recommend the use of Oracle Managed Files (OMF) when using the multitenant architecture, as it simplifies a number of pieces of functionality. It seems the use of OMF is mandatory for some functionality, like the Application Containers functionality in Oracle 12.2.

Creating Pluggable Databases (PDBs)

Since the bulk of the working parts are already present in the root container, creating a new PDB is a comparatively quick and simple task. When creating a completely new PDP, the PDB is created as a copy of a seed PDB, so it only takes as long as the files take to copy.

Create a Pluggable Database (PDB) Manually on Oracle cloud-DB instance
To create a new pluggable database from the seed database, all we have to do is tell Oracle where the file should be placed.
Create new Pluggable database and name it TEST in the oracle cloud-DB in the JCDB.

IP:- 100.100.100.100  CDB.compute.oraclecloud.internal CDB

connect through putty.
IP: - opc@100.100.100.100
Using username "opc".
Authenticating with public key "imported-openssh-key"

Switch To DB-User: -
[opc@CDB ~]$ sudo su - oracle

[oracle@CDB ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 5 08:44:35 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

Check Env. status:-
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
CDB.ORACLECLOUD.INTERNAL

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> select NAME, CDB, CON_ID, OPEN_MODE from V$DATABASE;
NAME      CDB     CON_ID OPEN_MODE
--------- --- ---------- --------------------
CDB      YES          0 READ WRITE

Create a subdirectory for the new PDB under the CDB file location from the OS oracle software owner by typing

[oracle@CDB JDB]$ pwd
/u02/app/oracle/oradata/CDB

[oracle@CDB CDB]$ mkdir TEST

[oracle@CDB CDB]$ chmod 777 TEST/

Back in SQL*Plus as SYSDBA, create pluggable database command by typing

SQL> ALTER SYSTEM SET db_create_file_dest = '/u02/app/oracle/oradata/CDB/TEST/';
System altered.

we can specify the PDB_FILE_NAME_CONVERT initialization parameter before calling the command without using the FILE_NAME_CONVERT clause.

SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u02/app/oracle/oradata/CDB/pdbseed/','/u02/app/oracle/oradata/CDB/TEST/';

Session altered.

SQL> CREATE PLUGGABLE DATABASE TEST ADMIN USER TEST identified by TEST;
  Pluggable database created.


We can see the PDBs are present by querying the DBA_PDBS and V$PDBS views.

SQL> COLUMN pdb_name FORMAT A20
SQL> SELECT pdb_name, status FROM   dba_pdbs ORDER BY pdb_name;
PDB_NAME             STATUS
-------------------- ---------
OEM13C            NORMAL
TEST              NEW
PDB$SEED             NORMAL

SQL> SELECT name, open_mode FROM   v$pdbs ORDER BY name;
NAME                           OPEN_MODE
------------------------------ ----------
OEM13C                      READ WRITE
TEST                        MOUNTED
PDB$SEED                       READ ONLY

SQL> SHOW PDBS
    CON_ID CON_NAME
---------- ------------------------------
OPEN MODE  RESTRICTED
---------- ----------
         2 PDB$SEED
READ ONLY  NO
         3 OEM13C
READ WRITE NO
         4 TEST

MOUNTED


The PDBs are created with the status of 'NEW'.
They must be opened in READ WRITE mode at least once for the integration of the PDB into the CDB to be complete.

SQL> ALTER PLUGGABLE DATABASE TEST OPEN READ WRITE;
Pluggable database altered.

SQL> SELECT name, open_mode FROM   v$pdbs ORDER BY name;
NAME                           OPEN_MODE
------------------------------ ----------
OEM13C                      READ WRITE
TEST                        READ WRITE
PDB$SEED                       READ ONLY

SQL> SELECT pdb_name, status FROM   dba_pdbs ORDER BY pdb_name;
PDB_NAME             STATUS
-------------------- ---------
OEM13C            NORMAL
TEST              NORMAL
PDB$SEED             NORMAL


Connect your pluggable database with following:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> alter session set container=TEST;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
TEST

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
TEST                        READ WRITE


SQL> select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------
TEST

SQL> set line 500
SQL> select CON_ID,OPEN_MODE,RESTRICTED,OPEN_TIME,TOTAL_SIZE from v$pdbs;

    CON_ID     OPEN_MODE     RES                          OPEN_TIME                 TOTAL_SIZE
---------- ---------- --- --------------------------------------------------------------------------- ----------
         4             READ WRITE       NO      05-APR-17 07.18.03.560 PM +00:00    891289600

[oracle@CDB OEM13C]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-APR-2017 10:01:33
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CDB.computer.oraclecloud.internal)(PORT=1521)))
Services Summary...
Service "CDB.oraclecloud.internal" has 1 instance(s).
  Instance "CDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "CDB.oraclecloud.internalXDB" has 1 instance(s).
  Instance "CDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: JCDB.compute.oraclecloud.internal, pid: 10509>
         (ADDRESS=(PROTOCOL=tcp)(HOST=CDB.compute.oraclecloud.internal)(PORT=53087))
Service "oem13c.a430011.oraclecloud.internal" has 1 instance(s).
  Instance "CDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "test.oraclecloud.internal" has 1 instance(s).
  Instance "CDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully.
*****************************END*******************************************

Delete a Pluggable Database (PDB) Manually

When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.
[oracle@CDB ~]$ sqlplus / as sysdba

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;
SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SQL> SELECT name, open_mode FROM   v$pdbs ORDER BY name;
NAME                           OPEN_MODE
------------------------------ ----------
OEM13C                      READ WRITE
TEST                        READ WRITE
PDB$SEED                       READ ONLY
PTEST1                      READ WRITE

SQL> ALTER PLUGGABLE DATABASE TEST CLOSE;
Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE TEST INCLUDING DATAFILES;
Pluggable database dropped.

SQL> ALTER PLUGGABLE DATABASE PTEST1 CLOSE;
Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE PTEST1 INCLUDING DATAFILES;
Pluggable database dropped.

SQL> SELECT name, open_mode FROM   v$pdbs ORDER BY name;
NAME                           OPEN_MODE
------------------------------ ----------
OEM13C                      READ WRITE
PDB$SEED                       READ ONLY

*****************************END*******************************************

Clone a Pluggable Database (PDB) Manually

Cloning an existing local PDB is like creating a new PDB from the seed PDB,
except now we are using non-seed PDB as the source, which we must identify using the FROM clause.
 Make sure the source PDB is open in READ ONLY mode.

Setting the source to read-only is not necessary for Oracle 12cR2.

SQL> ALTER PLUGGABLE DATABASE TEST CLOSE;

SQL> ALTER PLUGGABLE DATABASE TEST OPEN READ ONLY;

SQL> CREATE PLUGGABLE DATABASE TEST-new FROM TEST
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/ TEST-new /');

SQL> ALTER PLUGGABLE DATABASE TEST-new OPEN READ WRITE;

 Switch the source PDB back to read/write if you made it read-only.

SQL> ALTER PLUGGABLE DATABASE pdb3 CLOSE;

SQL> ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

The cloning syntax also allows for cloning from remote databases using a database link in the local CBD.

There are a few restrictions associated with this functionality.


[root@CDB etc]# cat oratab
# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
# Multiple entries with the same $ORACLE_SID are not allowed.
CDB:/u01/app/oracle/product/12.1.0/dbhome_1:Y
OEM13C:/u01/app/oracle/product/12.1.0/dbhome_1:N
TEST:/u01/app/oracle/product/12.1.0/dbhome_1:N


*****************************END*******************************************

 [oracle@CDB admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
#VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
#SSL_VERSION = 1.0
#USE_SID_AS_SERVICE_listener=on

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = CDB.compute.oraclecloud.internal)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = CDB)
      (SID_NAME = CDB)
    )

   )
[oracle@CDB admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDB.compute.oraclecloud.internal)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB)
    )
  )
OEM13C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDB.compute.oraclecloud.internal)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OEM13C..oraclecloud.internal)
    )
  )
TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDB.compute.oraclecloud.internal)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST..oraclecloud.internal)
    )
  )
PRD01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CDB.compute.oraclecloud.internal)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRD01.oraclecloud.internal)
    )
  )


No comments:

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...