Sujeet APPS DBA: 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

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

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)

)

Sujeet APPS DBA

CDB & PDB

Delete a Pluggable Database (PDB) Manually

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database