An Information Technology blog by Naga Appani: June 2013

In this post, I discuss moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBE metadata procedure is used for the same purpose. Following analogy applies to the post,

ORA12C - Non CDB Database (Instance)
CONDB - CDB Database (Instance)

Please follow steps below to move a Non CDB to CDB environment as PDB,

1. Open the Non CDB (ORA12C) in READ-ONLY Mode to keep it transactionally consistent.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 668082176 bytes
Fixed Size 2291952 bytes
Variable Size 507512592 bytes
Database Buffers 155189248 bytes
Redo Buffers 3088384 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;
Database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME OPEN_MODE
--------- --------------------
ORA12C READ ONLY

2. Connect to the Non-CDB Database(ORA12C) and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database(CONDB)

SQL> BEGIN

DBMS_PDB.DESCRIBE(

pdb_descr_file => '/backup/expdp/ora12c.xml');

END;

2 3 4 5

PL/SQL procedure successfully completed.

3. Shutdown the Non CDB (ORA12C) Database, and login to CDB Database (CONDB) as SYS user

On Non CDB (ORA12C)
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.

ORACLE instance shut down.

On CDB (CONDB)

[oracle@ora12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 30 17:07:00 2013

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

4. On CDB (CONDB), Create the PDB Database by mapping the XML file that Is generated in step 2

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME OPEN_MODE

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

CONDB READ WRITE

SQL> CREATE PLUGGABLE DATABASE ORA12C USING '/backup/expdp/ora12c.xml'

COPY

FILE_NAME_CONVERT = ('/dbs/ORA12C/', '/dbs/CONDB/ORA12C/'); 2 3

Pluggable database created.

From the parameter FILE_NAME_CONVERT, "/dbs/ORA12C/" is the source Database files location (Non CDB), and "dbs/CONDB/ORA12C/" is the target database files location (CDB/PDB). When we execute the above command, Oracle copies files from Non CDB's location to CDBs and plugs the database into Container, and creates it. Following query gives the status of the new PDB,

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB READ WRITE

SALESPDB READ WRITE

PDB1 READ WRITE

ORA12C MOUNTED

5. Connect to the new PDB that has been created on CDB Database (CONDB) as SYS, and execute the "noncdb_to_pdb.sql" script.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME OPEN_MODE

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

CONDB READ WRITE

SQL> ALTER SESSION SET CONTAINER=ORA12C;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

During the execution of script, Oracle restarts the PDB several times to complete the activity.

Sample Script output:

SQL> SET SERVEROUTPUT ON

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL>

SQL> WHENEVER SQLERROR EXIT;

SQL>

SQL> DOC

DOC>#######################################################################

DOC> The following statement will cause an "ORA-01403: no data found"

DOC> error if we're not in a PDB.

DOC> This script is intended to be run right after plugin of a PDB,

DOC> while inside the PDB.

DOC>#######################################################################

DOC>###################################################################

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

SQL> alter session set container = "&pdbname";

Session altered.

SQL>

SQL> -- leave the PDB in the same state it was when we started

SQL> BEGIN

2 execute immediate '&open_sql &restricted_state';

3 EXCEPTION

4 WHEN OTHERS THEN

5 BEGIN

6 IF (sqlcode <> -900) THEN

7 RAISE;

8 END IF;

9 END;

10 END;

11 /

PL/SQL procedure successfully completed.

SQL>

SQL> WHENEVER SQLERROR CONTINUE;

SQL> SPOOL OFF;

SQL> !pwd

/home/oracle

6. Open the PDB in READ WRITE Mode, confirm the status, and make it available for Client Operations, it is also advisable to take a backup.

SQL> ALTER PLUGGABLE DATABASE ORA12C OPEN;

Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE

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

ORA12C READ WRITE

1 row selected.

Hope it helps. Please stay tuned, add me to your G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards

For the past several years, we have witnessed significant contributions to Oracle Database. Every version has brought us some ground breaking technology and a leap forward innovation, that is a lot impressive and inspirational. As we already knew, Oracle has announced next major release candidate of Database, 12c at Oracle open world'2012, which is now available for download with all necessary product documentation. It has been completely redesigned from the ground and introduced fundamentally a major architectural change to Oracle Database which is called Multitenant Architecture. Throughout this post, I broadly elaborate it, and discuss its "what, why, and how", also includes its basic command line instructions.

What is Multitenant Architecture?

It is quite simple, Multiple tenants share same resources on a mutual benefit for different purposes at a very broad level. The same applies to Oracle Database where Multiple Databases share a single instance of resources aiming for different purposes on the same Server. This Oracle Database which is built on Multitenant foundation is called Container Database(CDB), and each container(tenant) residing inside is called Pluggable Database(PDB, Container).

Why is Multitenant Architecture?

Technology has vastly improved as customers can afford servers with hundreds of CPUs and huge chunk of Physical Memory where the Hardware resources can be distributed and managed dynamically. If applications get hold of this, that would be a huge benefit for Enterprises. That is what exactly Oracle has brought to the table, and this is only a beginning.

Benefits

One of the main reasons for Multitenant Architecture to be in place is "Database Consolidation". Until so far, we have databases deployed across multiple Small Physical machines on various platforms. It could be waste of resources having a database on each machine dedicatedly rather keeping them all on a single powerful machine. By consolidating all Databases onto a Single powerful chip and a Single Oracle Instance, we achieve following benefits

Cost reduction in terms of Product licensing and Hardware procurement as we require less number of processors and less memory compare to a Non-CDB environment

Easier Database maintenance, Pluggable Databases can easily be moved or copied locally or remotely across Physical machines in the network, Upgrading Operating System, Upgrading or Patching Database itself is a lot easier and "often" a one shot process, that greatly improves the availability
Performance tuning becomes a lot easier as all PDBs in a CDB environment share same Hardware/Platform/Network resources like CPUs/Memory/OS/Bandwidth etc.. For example, It is quite easy to tune one single piece of SGA/PGA on one single instance rather tuning each SGA on each physical machine

What more?

It is completely transparent to Application, there is no specific configuration required to connect to a Database in Multitenant Architecture over a network. It works the same way as it works with Non CDBs. Every Container or PDB has its own Service name to allow connections from Clients like JDBC/ODBC/XA/SQPLUS etc...
A Multitenant Container Database is created using either DBCA or "CREATE DATABASE" Command
A Multitenant Container Database or shortly a CDB, can be created with an empty container, no Pluggable Databases or shortly PDB(s) associated with it, [or] with one or more containers(More than one PDB)
Memory configurable is sum of memory required for all PDBs, is distributed on demand across all PDBs
A CDB can contain 253 PDBs including the SEED Container
Starting up and Shutting down a CDB is no different than a Non-CDB except we need to manually mount and dismount associated PDBs
The Architecture basically consists following containers

Root container - It is the core of the CDB, contains all Oracle supplied metadata like packages, core dictionary objects, and common users. It is denoted as CDB$ROOT
SEED Container (SEED PDB) - This is a template database distributed for new PDBs, whenever we create a new PDB, Oracle copies mandatory datafiles (common datafiles) for creation of PDB from this SEED Database. This is by default in READ-ONLY mode, can't be altered or modified. This is denoted as PDB$SEED
User defined PDB Container (PDB) - This is the Pluggable Database created for Application purpose, contains only Consumer Data or USER Data

CDB has common user environment. A User exists in the ROOT Container can login to any PDB with the privileges given. A CDB also has local users exclusive to PDBs. A Common user and role name must start with C## or c## and consists only of ASCII characters.
If a common user is required to login to a container, "c##" must be supplied as suffix. For example, If a common user "PDBADMIN" has privilege to acces a container PDB, We need to use it as "C##PDBADMIN" in order to login successfully to container PDB
"CONTAINER=ALL" clause can be used on root container while performing different tasks such as "ALTER SYSTEM", "ALTER DATABASE", "ALTER USER" etc.. This clause sets the specific action or configuration to all available PDBs, as well as future PDBs
Control files, REDO Logs, and UNDO files are owned by ROOT Container as these Database files are Instance specific. Every PDB has its own set of SYSTEM/SYSAUX/TEMP tablespaces, and also includes the Sample User data such as SCOTT, HR etc..
ENABLE_PLUGGABE_DATABASE initialization parameter specifies If a particular Database is CDB or Non-CDB
By default Oracle instance mounts the Pluggable Database, so it has to be manually opened into READ/WRITE Mode. In case of Cluster, it has to be opened in each instance

Basic Monitoring Command Line Instructions

To find If a Database is CDB or Non-CDB

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID

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

CONDB YES 0

[OR]

SQL> SHOW PARAMETER enable_pluggable_database

NAME TYPE VALUE

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

enable_pluggable_database boolean TRUE

To find the Current Database that we are logged in,

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

CDB$ROOT

To find the information about Pluggable Databases,

COL PDB_NAME FOR A30

SELECT PDB_ID,PDB_NAME,STATUS,CON_ID FROM CDB_PDBS;

PDB_ID PDB_NAME STATUS CON_ID

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

3 PDB NORMAL 1

2 PDB$SEED NORMAL 1

4 SALESPDB NORMAL 1

To login to a Container or a specific PDB

There are two ways we can login into a Container or PDB

1. SQLPLUS/CONNECT - This requires a TNS entry to login to a specific PDB locally or remotely

oracle@ora12c ~]$ sqlplus sys/123@PDB as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 12:35:02 2013

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

PDB

2. ALTER SESSION

SQL> ALTER SESSION SET CONTAINER=SALESPDB;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

SALESPDB

To switch between Containers using ALTER SESSION,

1. To switch to SEED Container,

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

PDB$SEED

2. To switch to ROOT Container,

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

CDB$ROOT

To mount or dismount and find the status of a PDB,

SQL> ALTER PLUGGABLE DATABASE PDB OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB CLOSE IMMEDIATE;

Pluggable database altered.

[OR]

SQL> SHUT IMMEDIATE

Pluggable Database closed.

For multiple PDBs,
SQL> ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB OPEN;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE PDB,PDB1,SALESPDB CLOSE;
Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE,TOTAL_SIZE/1024/1024/1024 FROM V$PDBS;

NAME OPEN_MODE TOTAL_SIZE/1024/1024/1024
------------------------------ ---------- -------------------------
PDB$SEED READ ONLY .263671875
PDB READ WRITE .424804688
SALESPDB MOUNTED 0

To modify any PDB Characteristics,

"ALTER PLUGGABLE DATABASE" is the command replaces "ALTE DATABASE" command in a CDB Environment. For example If the goal is to change the default tablespace of a PDB,

1. Tablespace must be created within the PDB

2. Use the below command to change it,

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

PDB

SQL> CREATE TABLESPACE TS_PDB DATAFILE '/dbs/CONDB/PDB/ts_pdb_01.dbf' SIZE 10M;

Tablespace created.

SQL> ALTER PLUGGABLE DATABASE PDB DEFAULT TABLESPACE TS_PDB;

Pluggable database altered.

To set an initialization Parameter,
A Parameter can be set exclusive to a PDB, or for all current and future PDBs using "ALTER SYSTEM" command.
On the root container:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE CONTAINER=ALL;
System altered.

On a specific container or PDB:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
System altered.

To create a user in the ROOT Container and accessing other PDBs,

A CDB has common user environment. A User exists in the ROOT Container can login to any PDB with the privileges given and by default it is mapped to all available PDBs and future PDBs. A CDB also has local users exclusive to PDBs.

A Common user and role name must start with C## or c## and consists only of ASCII characters.
To login to a specific PDB, A Common user must have CREATE SESSION Privilege on the PDB.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

CDB$ROOT

SQL> CREATE USER C##PDB IDENTIFIED BY PDB;

User created.

SQL> GRANT CREATE SESSION TO C##PDB;

Grant succeeded.

SQL> GRANT CREATE SESSION TO C##PDB CONTAINER=ALL; (ALL Specifies all Containers or PDBs)

Grant succeeded.

SQL> CONNECT C##PDB@PDB

Enter password:

Connected.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','CON_NAME')

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

PDB

SQL> SHOW USER

USER is "C##PDB"

Hope it helps. I will write more posts on 12c Database in coming weeks and publish, Please stay tuned, add me to you G+ Circles!! Your feedback always helps me and motivates me to blog more, please leave your valuable comment that is always a pleasure to read.

Many Thanks,

Regards

An Information Technology blog by Naga Appani

Sunday, June 30, 2013

Moving a Non Container Database (Non CDB) to Container Database (CDB, as PDB)

Saturday, June 29, 2013

Oracle Database 12c - All about Multitenant Architecture

replica sets, Replication with mongoDB

Translate