Managing Oracle 12c CDB’s and PDB’s – Plugging and Unpluggging PDB’s – Part B

One of the main features of the Oracle Database 12c is the portable nature of the pluggable databases (PDBs). You can easily unplug a PDB from a CDB and then plug it into a different Container database (CDB). This ease of Plugging in and Unplugging PDBs makes the 12c database truly Cloud ready. If you have just downloaded the Oracle Database software you may want to read previous article.

Oracle 12c PDB Multitenent Database

Let’s look at how to accomplish this task of plugging and unplugging PDB’s. There are two ways you can do that and essentially they are based on whether you want to move the datafiles from one location to the other or not. We will look at both of these methods here.

Using the NoCopy Method

The first method uses the NoCopy option which implies that you want to unplug the PDB from one CDB to another without moving the actual datafiles. Obviously this can only be done when you want to unplug a PDB from one CDB to another CDB, on the same server.

In our examples here, we will use two CDBs named CDB12c and CDBNEW. The CDB12c container database has one PDB database named PDB3 and the CDBNEW container database has one PDB database named PDB10. The SQL commands below confirm the databases in the current environment.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdb12c
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3 READ WRITE
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdbnew
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB10 READ WRITE

First lets move the PDB10 from the CDBNEW to CDB12c. Lets now unplug CDB10 from CDBNEW .
SQL> alter pluggable database pdb10 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb10 unplug into '/u01/app/oracle/oradata/pdb10_unplug.xml';
Pluggable database altered.
SQL> drop pluggable database pdb10 keep datafiles;
Pluggable database dropped.

Just three commands and the database has been unplugged and ready to be moved. First we closed the database, then the second command generates an XML manifest file and the third command drops the PDB from the current CDB it is attached to. Please note the “Keep datafiles” clause which make sure that data files are not deleted.

To confirm that PDB10 is no longer part of CDBNEW issue the following command.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY

The next step is to plug the database into our second CDB i.e. CDB12c. Log into CDB12c and run the following command to plug PDB10 into PDB12c container database.
SQL> create pluggable database pdb10_nocopy using '/u01/app/oracle/oradata/pdb10_unplug.xml'
2 nocopy
3 tempfile reuse;
Pluggable database created.
Note the use of XML manifest file and also the NoCopy clause. The NoCopy clause basically makes sure that data files retain their location. The Temp File reuse clause is required to re-initiate the temporary files.

You can query the v$PDBS to confirm plugging of database.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3 READ WRITE
PDB10_NOCOPY MOUNTED

The newly plugged database is in the mount state. You can now open it for normal use.
SQL> alter pluggable database pdb10_nocopy open;
Pluggable database altered.

Using Copy Method

The second method uses the Copy clause to copy the datafiles from the old location to new. Almost all steps are the same, except for the actual command which plugs the database to new CDB container database. In this example we will move PDB3 from CDB12c to CDBNEW. Before we move onto unplugging the PDB3 database from CDB12c first check the location of datafiles. This is to make sure that Copy clause can indeed move data files.
SQL> select file_name from cdb_data_files where con_id=3;
FILE_NAME
----------------------------------------------------------------------
/u02/app/oracle/oradata/cdb12c/pdbtest/system01.dbf
/u02/app/oracle/oradata/cdb12c/pdbtest/sysaux01.dbf
(We have used CDB_DATA_Files view which is new in 12c. Any view starting with CDB will only be part of CDBs. They do not exist in PDBs. They show information from all PDBs attached to CDB and also information about CDB itself. For example CDB_DATA_FILES will have information about all datafiles. Whether they belong to CDB itself or they belong to some PDB attached to it. You can filter information using the CON_ID column as shown above. The DBA_DATA_FILES is still there in PDBs and in CDBs and will only show information pertinent to datafiles of the current database only.)

Now that we have the current location of data files, lets move onto to the unplugging phase.
SQL> alter pluggable database pdb3 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb3 unplug into '/u01/app/oracle/oradata/pdb3_unplug.xml';
Pluggable database altered.
SQL> drop pluggable database pdb3 keep datafiles;
Pluggable database dropped.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB10_NOCOPY READ WRITE


The unplugging part is almost the same. First we close the database, create the manifest file and then drop the database from CDB while keeping datafiles intact. Querying the V$PDBS confirms the process.
To plug PDB3 into our other CDB i.e. CDBNEW, log into CDBNEW and use the following command.
SQL> create pluggable database pdb3_copy using '/u01/app/oracle/oradata/pdb3_unplug.xml'
2 copy
3 tempfile reuse;
Pluggable database created.

SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB3_COPY MOUNTED

SQL> alter pluggable database pdb3_copy open;
Pluggable database altered.

The command to plug it is similar to the method above except that this time around we used the copy clause. The Copy clause will move the files to the new default location of the CDBNEW container database. The subsequent commands shows that PDB_COPY has been created in CDBNEW. To verify the new location of data files use the following command.
SQL> select file_name from cdb_data_files where con_id=3;
FILE_NAME
----------------------------------------------------------------------
/u02/app/oracle/oradata/CDBNEW/E03A6382B68D4162E0450000FF00020F/datafile/
o1_mf_system_8wzvmf7n_.dbf
/u02/app/oracle/oradata/CDBNEW/E03A6382B68D4162E0450000FF00020F/datafile/
o1_mf_sysaux_8wzvmktl_.dbf

As you can see that the datafiles have been moved into the new default location of the CDNBEW container. The names are not what you may be expecting and that is because we did not provided any convention. You can use the FILE_NAME_CONVERT clause to properly specify the custom location where data files should be copied.
File_Name_Reuse(,)

Using As Clone … Move Method

The last scenario of plugging and unplugging pdb’s is when you want to plug back a database into the same CDB from where it was removed. Suppose we have the following pdbs in our CDB.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE

Let’s unplug the PDB1 database.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/unplug_pdb1.xml';
Pluggable database altered.

The PDB1 database has been unplugged from the CDB container. Hence it cannot be used anymore. If you try to open it, you will get the error message.
SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

What if you want to get it back up and running again? Here the “As Clone … Move method” will come in handy. You will be able to plug the database back in again, with a single command after renaming the database name.
SQL> create pluggable database pdb1_plug_move
2 as clone using '/u01/app/oracle/unplug_pdb1.xml'
3 move
4 file_name_convert=('pdb1','pdb1_plug_move');
Pluggable database created.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB1_PLUG_MOVE MOUNTED

The new database PDB1_PLUG_MOVE has been created as an exact replica of the PDB1 database. You can go ahead and open that database and drop the original PDB1.
SQL> alter pluggable database pdb1_plug_move open;
Pluggable database altered.
SQL> drop pluggable database pdb1 including datafiles;
Pluggable database dropped.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1_PLUG_MOVE READ WRITE

Managing Oracle 12c CDB and PDB – Part A

While the installation of the Oracle 12c Database is more or less similar to that of 11g, the same cannot be said about the actual administration. Managing Oracle 12c PDBs and CDBs is different in many ways. That’s mainly because of architectural changes introduced 12c. If you have just downloaded the Oracle Database software you may want to read the following articles I have written previously.

Before getting into the actual administration lets briefly review the CDBs and PDBs concepts as it is very important to understand the multitenant environment which the installer creates.

About Multitenant Architecture

There are three main concepts that we need to be familiar with.

CDB Components: 

CDB is the main container database. It is much like traditional database except for the fact that it now supports the multitenant architecture. The PDB’s database plug into this container database. The data in the PDB’s is accessed from the SGA and bacground processes of the CDB database. Mutiple PDB’s can be plugged into a container database. There are three main components of CDB.

Oracle-12c-Multitenant Architecture.

Root: 

The root database, CDB$ROOT, is be the main container and holds the Oracle metadata and common users. A typical example of the metadata are the Oracle supplied PL/SQL packages. Common users are users defined at the root level which have access to all databases plugged into the CDB. They are available across the multitent architecture similar to SYS and SYSTEM, however their privileges vary across the PDB/CDB databases. A CDB can only have one root.

Seed: 

A seed is a template which is used to create new PDBs. Its named as PDB$SEED. You cannot edit or modify objects within PDB$SEED and its Read-Only. There can be only PDB Seed against each CDB.

PDBs: 

From an end user’s perspective they only know and need to connect with PDBs. From their point of view PDB is no different from non-CDB database (Non-CDB is the term that will be used for databases created in versions before 12c). Ideally a PDB will correspond to one application and thus many applications can be hosted against one CDB. Currently Oracle supports 252 PDBs against one CDB. Every PDB will be fully compatible with previous versions of Oracle database. Also you can easily plug any Non-CDB database as PDB to CDB database and vice versa.

Common and Local Users:

There will be two types of users. Common users will be the users which have same identity in Root and every other PDB database. What they are authorized to do within each database may vary from database to database but they will have identity in all of them.
Local users will be the users local to individual PDBs. They will not have identity in other databases. So a local user named Scott can exist in two databases with same name. However common user will have unique name across all databases.

CDB and PDB Administration:

There is a clear SOD (Separation of Duties) defined between the administrator accounts of CDB and PDB. A CDB administrator can manage Root database and can also perform some operations on PDB level as well like Creating and dropping PDBs.
A PDB administrator on the other hand will only be able to manage individual PDB where it is created. He can manage space, manager other users, move data files here and there but only to its specific PDB. He will not have access to other PDBs and CDB.

Connecting to CDB and PDB

During installation if you select to install and create database option then Oracle at least creates CDB$ROOT and PDB$SEED. If you choose to create a PDB then it will create a PDB as well the name of which will be specified by you. The screenshot below shows you this option when you are creating database.

Of course you can create pluggable databases later on as well. This is to show you that if you have followed our installation article then you will have one CDB Root CDB$ROOT (named cdb12c), one PDB Seed PDB$SEED and one pluggable database named pdb12c.
It is worth mentioning that every PDB created will have its own Service name which users will use to connect to – Just like in previous versions. However it will not have a separate instance. It will use the same instance and share memory structures (SGA) and background processes from CDB. This feature is known as Consolidation and its new in 12c. So the ORACLE_SID environment parameter will always have value equal to CDB instance name. The following query will always return the instance name of CDB regardless if you are connected to CDB or any PDB attached to that CDB.
SQL> select instance_name from v$instance;

With that said lets try to connect to Oracle instance using the very familiar command.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 03:12:40 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2293928 bytes
Variable Size 562040664 bytes
Database Buffers 272629760 bytes
Redo Buffers 2318336 bytes
Database mounted.
Database opened.
SQL>

To check which database you are connected to you can use the new con_name and con_id SQL*PLUS functions.
SQL> show con_id
CON_ID
------------------------------
1
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>

To connect to PDB databases you need to use the proper username/password combination. OS level authentication will not work for PDBs. This also means that you cannot connect to PDBs without first configuring tnsnames.ora and starting the listener. However for test purpose you can use the following syntax to avoid tnsnames configurations for now. Your listener should be listening however for this to work.
$ sqlplus sys/oracle@VST-12c:1521/pdb12c as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 03:32:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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>
To confirm that we are connected to right database.
SQL> show con_name
CON_NAME
------------------------------
PDB12c
SQL> show con_id
CON_ID
------------------------------
3
SQL>

The following command however will show that instance is same. This is because of consolidation. All PDBs will use the CDB’s instance.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
cdb12c

If you are connected to CDB as SYS or any other Common user then you can switch between different Containers on the go – provided that you have relevant privileges. For example if you are connected to CDB as SYS and want to perform some operation in one of your PDB as SYS as well then instead of opening another session you can just switch Container as shown below.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb12c;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB12C

Creating and dropping PDBs

In order to create a new PDB or to drop an existing one you will have to connect to CDB as SYSDBA. First lets check what current PDBs are attached to CDB.
SQL> select pdb_name,status from cdb_pdbs;
PDB_NAME STATUS
------------------------------ -------------
PDB12c NORMAL
PDB$SEED NORMAL

As you can see there are two PDBs. One is seeded and the other is what we created during installation. To create a new PDB we will first create a directory to hold the data files for new database and then use the following command.
SQL> create pluggable database pdbtest
2 admin user pdbtest_admin identified by oracle
3 roles = (DBA)
4 file_name_convert=('/u01/app/oracle/oradata/cdb12c/pdbseed','/u02/app/oracle/oradata/cdb12c/pdbtest');
Pluggable database created.
Elapsed: 00:00:17.08

I specifically turned on timing for this command. As you can see it took us just 17 seconds to create a new database. Application deployment is going to be extremely fast with this new concept. This was made possible due to the fact that for every new database Oracle does not have to extract and copy files from template, create and initialize instance and configure everything. Everything has already been done during CDB creation. Oracle merely copies the required data files and creates a service which can be used to connect to CDB instance.
The above command will only create SYSTEM and SYSAUX tablespaces for the new PDB. The files like for Undo tablespace and Redo Logs will be used to that of CDB.
Run the following command to confirm creation of database.
SQL> select pdb_name,status from cdb_pdbs;
PDB_NAME STATUS
------------------------------ -------------
PDB12c NORMAL
PDB$SEED NORMAL
PDBTEST NEW
Elapsed: 00:00:00.10

The status column shows New for our newly created database. This is because database has never been opened. We will see next how to start/stop CDB and PDBs.
To drop a PDB you must close it first.
SQL> alter pluggable database pdb12c close;
Pluggable database altered.
Elapsed: 00:00:02.25
SQL> drop pluggable database pdb12c including datafiles;
Pluggable database dropped.
Elapsed: 00:00:00.66
SQL> select name from v$pdbs;
NAME
------------------------------
PDB$SEED
PDBTEST
Elapsed: 00:00:00.06

Starting/Stopping CDB and PDBs

The traditional approach of starting and stopping databases is now only valid for CDB. So in order to start and stop CDB you will use the familiar startup and shutdown commands. PDBs don’t get automatically started with CDB. Even if your CDB is up and running it is possible that PDBs are still inaccessable. To view the current status of PDBs run the following command.
SQL> select name,open_mode
2 from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12c MOUNTED
PDBTEST MOUNTED
Elapsed: 00:00:00.01

Already discussed above PDB$SEED cannot be opened for Read Write. Our two example PDBs are in Mount state. You can open a PDB using the following command.
SQL> alter pluggable database pdbtest open;
Pluggable database altered.
Elapsed: 00:00:11.69
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12c MOUNTED
PDBTEST READ WRITE
Elapsed: 00:00:00.04

Likewise you can close a PDB using the following command.
SQL> alter pluggable database pdbtest close;
Pluggable database altered.
Elapsed: 00:00:00.92

To open/close all PDBs at once you can use the following commands.
SQL> alter pluggable database all open;
Pluggable database altered.
Elapsed: 00:00:05.96

SQL> alter pluggable database all close;
Pluggable database altered.
Elapsed: 00:00:01.33

If you are connected to PDB and issue the Startup and Shutdown commands then those commands will Open and Close that PDB only . For example
SQL> show con_name
CON_NAME
------------------------------
PDB12C
SQL> shutdown immediate;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.

Automating Startup of PDBs

You can automate the startup of PDBs so that they are opened as soon as CDB is up and running. The following code will create a trigger on Startup event to open all PDBs.
SQL> create or replace trigger sys.after_startup after startup on database
2 begin
3 execute immediate 'alter pluggable database all open';
4 end after_startup;
5 /
Trigger created.
Elapsed: 00:00:00.19

Lets test this by shutting down and then starting up the CDB.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2293928 bytes
Variable Size 562040664 bytes
Database Buffers 272629760 bytes
Redo Buffers 2318336 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12c READ WRITE
PDBTEST READ WRITE
Elapsed: 00:00:00.18

Renaming a PDB

You can easily change the global name of a PDB with few very simple commands. This is simple because there is no specific instance with same name attached to PDBs. They use instance attached to CDB. However you will have to log into the specific PDB to change its name. You cannot do this from CDB.
First close the database and open it in restricted mode.
SQL> alter pluggable database pdb_test close;
Pluggable database altered.
SQL> alter pluggable database pdb_test open restricted;
Pluggable database altered.

We are currently connected to CDB. If we try to change the global of PDB_TEST, we will get the error message as shown below.
SQL> alter pluggable database pdb_test rename global_name to pdb3;
alter pluggable database pdb_test rename global_name to pdb3
*
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database

To do this we will first connect to PDB_TEST and then change the name from there.
$ sqlplus sys/oracle@localhost:1521/pdb_test as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 01:39:34 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
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> show con_name
CON_NAME
------------------------------
PDB_TEST
SQL> alter pluggable database pdb_test rename global_name to pdb3;
Pluggable database altered.

Global name has been changed. Close the database and reopen it in Normal Read Write mode.
SQL> alter pluggable database pdb_test rename global_name to pdb3;
Pluggable database altered.
SQL> alter pluggable database close immediate;
Pluggable database altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB3 READ WRITE

Data Dictionary Views

There are new data dictionary views which start with CDB_* and are only visible to SYSDBA when connected to CDB. They show information about all objects no matter where they are. In CDB or in some PDB. For example the following command will show names of all data files whether they belong to CDB, PDBSEED or to our PDB i.e. PDB12C.
SQL> select file_name from cdb_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb12c/pdb12c/system01.dbf
/u02/app/oracle/oradata/cdb12c/pdb12c/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/system01.dbf
/u01/app/oracle/oradata/cdb12c/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/undotbs01.dbf
/u01/app/oracle/oradata/cdb12c/users01.dbf
/u01/app/oracle/oradata/cdb12c/cdata.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf
9 rows selected.

These data dictionary views have an extra column of CON_ID which can be used to select objects attached to specific database.
SQL> select file_name from cdb_data_files
2 where con_id=2;
FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf

Almost every data dictionary view have a corresponding CDB_* view. The normal DBA_* views will only show information about objects of current container only.
SQL> select file_name from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb12c/system01.dbf
/u01/app/oracle/oradata/cdb12c/sysaux01.dbf
/u01/app/oracle/oradata/cdb12c/undotbs01.dbf
/u01/app/oracle/oradata/cdb12c/users01.dbf
/u01/app/oracle/oradata/cdb12c/cdata.dbf

Oracle 12c Container and Pluggable Databases (Multi tenant Architecture)

The Oracle 12c Database has the most new features ever released with any version of any database. As you might have guessed from it’s name, the new version is more focused on Cloud Computing. Larry Ellison had pointed out in his Keynote session, at Openworld 2012, that in-fact it is the biggest release of database that they have done in quite some time. This time there have been some drastic architectural changes in this version, with about 500 new features!

Oracle 12c Container and Pluggable Databases [It is a major design change on how Oracle deals with databases and the underlying architecture.]

This is the most talked about feature in Oracle 12c. Oracle introduces the concept of Container databases and Pluggable databases. The container database has all the metadata which will be inherited by the core Oracle components when it is installed. Pluggable databases will store user’s data and other pertinent metadata.

This concept is similar to the virtual machines in a virtualized environment, hosted on a physical server machine. The virtualized software runs different virtual machines on the host operating system, with each of the virtual machines dependent on the host but having their own identity and their own specific purpose. Additionally the individual machines are also independent of the other. Here, in 12c database, it is defined as a multitenant database with the Container database as a host operating system and each individual virtual machine as a Pluggable database. The main advantage is obvious. Just like in a virtualized server, here too we will be able to better make use of the hardware resources. How?

When you install Oracle Database 12C, a Container database will be created. This database will have all the metadata information related to the Oracle software and will also host the SYSTEM and SYSAUX tablespaces. Then with every new Pluggable database, the SYSTEM and SYSAUX tablespaces will not be created, rather only tablespaces to store user’s data and metadata inside the Pluggable database. The SGA memory area and background is shared between all the new pluggable databases. Each of this Container database can support roughly up to 250 pluggable databases. Each pluggable database in turn will be used as a service by any other application or database.

Apart from this obvious advantage of better resource utilization there are also numerous other advantages. Pluggable databases will be easy to back-up and move to another system. Patching or upgrading the Container database will automatically update all the pluggable databases. Another major advantage is having a single point of administration. From the development perspective developers will not have to worry about implementing Multitenancy into their application code. Rather the simple process of creating a separate database for every different potential tenant will do the trick more easily and more efficiently. Separation of these Plugin databases will also help satisfy Security concerns for some applications.

Container and Pluggable databases is one of the main new features introduced in 12c nd is referred to as the Multitenant Option.

A Container database (CDB) is made up of the following containers:

1. There is one root container which stores the Oracle supplied metadata like the PL/SQL data dictionary packages and the common users. This root container is referred to as CDB$ROOT.
2. One seed Pluggable Database (PDB) which is a system supplied template which can be used to create new PDB’s. This seed PDB is called PDB$SEED.
3. None or more user created PDBs.

A container database can also be created via the SQL statement.

$ SQL> CREATE DATABASE ... ENABLE PLUGGABLE DATABASE

Lets take a look at some of the characteristics of a CDB

• There is a separate SYSTEM and SYSAUX tablespace for the root container of the CDB and each PDB
• There is only one UNDO tablespace for the entire CDB.
• There is only one set of control files and online redo logs files for the entire CDB. Individual PDB’s have their own data files (which contain the user data), but do not have distinct redo log or control files.
• We can create on default temporary tablespace for the entire CDB or each PDB can have its own additional temporary tablespaces.
• There is single network administration files like listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use the same files.
• There is only one set of background processes shared by the root and all PDBs.
• There is a single SGA shared by all  PDB’s.
• When the CDB is shut down then all PDB’s are also automatically shut down

When the CDB is open then we can change the open mode of individual PDBs via the ALTER PLUGGABLE DATABASE SQL statement.

Let us look at some examples of using CDBs and PDBs. In this case we have a CDB called cdb12c and the PDB name is testdb1.

Connect to the CDB – by default we are connected to the root container – the container id is 1

C:\Users\adixit3034c>sqlplus sys/password@localhost:1525/cdb12c as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 11:07:52 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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> show con_name

CON_NAME

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

CDB$ROOT

SQL> show con_id

CON_ID

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

1

Now connect to the PDB testdb1 – note the container id is 3 ( 2 is the container id for PDB$SEED)

C:\Users\adixit3034c> sqlplus sys/password@localhost:1525/testdb1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 11:09:47 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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> show con_name

CON_NAME

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

TESTDB1

SQL> show con_id

CON_ID

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

3

When the container database is started up, by default the pluggable databases are not automatically opened but remain in a mount state

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE

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

TESTDB1                        MOUNTED

SQL> alter pluggable database testdb1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE

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

TESTDB1                        READ WRITE

Note the datafiles shown by V$DATAFILE view when we are connected to the root container.

We see the datafiles for the root container CDB$ROOT, the PDB$SEED PDB and the testdb1 PDB

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/cdb12c/system01.dbf

/u01/app/oracle/oradata/cdb12c/sysaux01.dbf

/u01/app/oracle/oradata/cdb12c/undotbs01.dbf

/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf

/u01/app/oracle/oradata/cdb12c/users01.dbf

/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/system01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/sysaux01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/example01.dbf

Connected to the root container, we can see the datafiles of the seed PDB

SQL> select name from v$datafile where con_id=2;

NAME

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

/u01/app/oracle/oradata/cdb12c/pdbseed/system01.dbf

/u01/app/oracle/oradata/cdb12c/pdbseed/sysaux01.dbf

Now when connected to the PDB testdb1, the V$DATAFILE only displays the datafiles belonging to that individual PDB

SQL> select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/cdb12c/undotbs01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/system01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/sysaux01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/SAMPLE_SCHEMA_users01.dbf

/u01/app/oracle/oradata/cdb12c/testdb1/example01.dbf

Each of the CDB and PDBs have their own temp files

SQL> select con_id,name from v$tempfile;

CON_ID NAME

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

         1 /u01/app/oracle/oradata/cdb12c/temp01.dbf

         2 /u01/app/oracle/oradata/cdb12c/pdbseed/pdbseed_temp01.dbf

         3 /u01/app/oracle/oradata/cdb12c/testdb1/testdb1_temp01.dbf

Note – there is only one SGA!

C:\Users\adixit3034c> sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 10:25:23 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

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> show sga

Total System Global Area 4275781632 bytes

Fixed Size                  2296576 bytes

Variable Size            2936014080 bytes

Database Buffers         1325400064 bytes

Redo Buffers               12070912 bytes

Connect to the PDB – SGA is the same

SQL> ALTER SESSION SET CONTAINER =testdb1;

Session altered.

SQL> show sga

Total System Global Area 4275781632 bytes

Fixed Size                  2296576 bytes

Variable Size            2936014080 bytes

Database Buffers         1325400064 bytes

Redo Buffers               12070912 bytes

Controlfiles and Online Redo Log files are only one for CDB as well as PDB

C:\Users\adixit3034c> sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 9 11:04:10 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

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 name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/cdb12c/control01.ctl

/u01/app/oracle/fast_recovery_area/cdb12c/control02.ctl

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/cdb12c/redo03.log

/u01/app/oracle/oradata/cdb12c/redo02.log

/u01/app/oracle/oradata/cdb12c/redo01.log

SQL>  ALTER SESSION SET CONTAINER =testdb1;

Session altered.

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/cdb12c/control01.ctl

/u01/app/oracle/fast_recovery_area/cdb12c/control02.ctl

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/cdb12c/redo03.log

/u01/app/oracle/oradata/cdb12c/redo02.log

/u01/app/oracle/oradata/cdb12c/redo01.log

Provision Oracle 12c PDB

Somebody wants help provisioning a pluggable database (PDB). It’s important to understand that PDB is a private data context. Most of it’s data catalog is private and separate from the overall database. Only a small portion of the database catalog is stored in the container database catalog, and new CDB_ administrative views are added to the database. A PDB is a great solution when you’re leveraging the Editioning feature of Oracle 11g database.

You should note the guarantee from page #9 of Oracle’s Oracle Multitenant:

From the point of view of the client connecting via Oracle Net, the PDB is the database. A PDB is fully compatible with a non-CDB. We shall refer to this from now on as the PDB/non-CDB compatibility guarantee. In other words, the installation scheme for an application backend that ran without error against a non-CDB will run, with no change, and without error, in a PDB and will produce the same result.

Here are the steps that work on both Windows, Linux, or Windows:

1. The following SQL command lets you create a pluggable database (PDB) with a video user assigned to it:

CREATE PLUGGABLE DATABASE videodb

  ADMIN USER videoadm IDENTIFIED BY Video1

  ROLES = (dba)

  DEFAULT TABLESPACE videots

    DATAFILE '/u01/app/oracle/ORADATA/ORCL/VIDEO01.DBF' SIZE 500M ONLINE

  FILE_NAME_CONVERT = ('/u01/app/oracle/ORADATA/ORCL/PDBSEED\',

                       '/u01/app/oracle/ORADATA/ORCL/VIDEOPDB');

Don’t try to create the DEFAULT TABLESPACE before you provision the database. If you do, you’ll get the following exception:

CREATE PLUGGABLE DATABASE videodb

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01537: cannot add file 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' - file already part of database

2. You need to stop the Oracle listener and modify the listener.ora file. Lines 9 through 12 configure a VIDEODB Oracle SID. After you make the changes, start the Oracle listener.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")

    )

    (SID_DESC =

      (SID_NAME = VIDEODB)

      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

3. You also need to add a VIDEO TNS alias to the tnsnames.ora file for the VIDEODB pluggable database (PDB).

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

VIDEO =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = videodb)

    )

  )

4. You connect as the SYSDBA for the VIDEO pluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the SYSDBA for the VIDEODB, you’ll be prompted for a password. The required password is the Video1 password that you used when you set up the VIDEODB database.

$ sqlplus sys@VIDEO AS sysdba 

or

adixit3034c> sqlplus sys@VIDEO AS sysdba

5. After authenticating as the SYSDBA, you need to start the VIDEODB pluggable database, like:

SQL> startup

Pluggable DATABASE opened.

You can also start the pluggable database from the generic SYSDBA account. You only need to change the CONTAINER context to pluggable database. You can find the status of pluggable databases with the following query:

SQL> COLUMN RESTRICTED FORMAT A10

SQL> SELECT   v.name

  2  ,        v.open_mode

  3  ,        NVL(v.restricted, 'n/a') AS restricted

  4  ,        d.STATUS

  5  FROM     v$PDBs v INNER JOIN dba_pdbs d USING(guid)

  6  ORDER BY v.create_scn;

The generic sample database returns this after restarting the database:

NAME                               OPEN_MODE  RESTRICTED STATUS

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

PDB$SEED                       READ ONLY     NO         NORMAL

PDBORCL                        MOUNTED       n/a        NORMAL

The following changes the CONTAINER context, opens the pdborcl PDB, and creates a traditional Oracle 11g and backward standard user/schema account in the pluggable database.

SQL> ALTER SESSION SET container=pdborcl;

SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN;

SQL> CREATE USER johnny IDENTIFIED BY johnny;

Re-query the status of the pluggable databases, and you’ll see:

NAME                           OPEN_MODE  RESTRICTED STATUS

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

PDB$SEED                       READ ONLY  NO         NORMAL

PDBORCL                        READ WRITE NO         NORMAL

If you have more than one pluggable database, you can use the following to open them all as the CDB’s SYSDBA:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

6. As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants UNLIMITED TABLESPACE. That means you need to grant it as the SYSDBA for the PDB, like:

GRANT UNLIMITED TABLESPACE TO videoadm;

7. After you’ve done all the prior steps, you can connect with the following as the Administrative VIDEO user:

sqlplus videoadm@VIDEO/Video1

Although making such a connection is a poor choice, it would be better to make a secure connection like the one below. The problem with the former is that it exposes information at the OS command line that would be visible to other users. Connecting to the Oracle SQL*Plus with the /NOLOG option prevents disclosure of that information.

C:\Users\adixit3034c>sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production ON Tue Aug 13 01:28:30 2013

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.

SQL> CONNECT videoadm@VIDEO/Video1

Connected.

SQL> SHOW USER

USER IS "VIDEOADM"

8. Once you’re connected as the SYSDBA role, you can create standard (pre-Oracle 12c) user/schemas with the old syntax. Below, we create a student account:

SQL> CREATE USER student IDENTIFIED BY student;

SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator

  2  ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION

  3  ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE

  4  ,     CREATE VIEW, UNLIMITED TABLESPACE TO student;

Naturally, you can also add a USER from the CDB’s SYSDBA role when in the proper CONTAINER context.

This has shown you how to provision a pluggable database (PDB). As a side note, you should know that you can only create user-defined common users (at the CDB-level) with a c## prefix.

An addendum: DBMS_COMPARE isn’t provisioned automatically, and you need to manually apply it in your ADMIN user for the PDB.

DBMS_COMPARISON Missing?

The dbms_comparison package isn’t deployed when you provision a pluggable databases (PDBs) in Oracle 12c. It appears to be a simple omission. At least, it let me manually compiled the dbms_comparison package with this syntax:

@?/rdbms/admin/dbmscmp.SQL

@?/rdbms/admin/prvtcmp.plb

However, when I ran the code against the PDB it failed. The same code worked against a container database (CDB). It struck me as odd. The error stack wasn’t too useful, as you can see below:

BEGIN

*

ERROR at line 1:

ORA-06564: object "SYS"."COMPARE_NAME" does NOT exist

ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 569

ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 602

ORA-06512: at "SYS.DBMS_CMP_INT", line 394

ORA-01403: no DATA found

ORA-06512: at "SYS.DBMS_COMPARISON", line 764

ORA-06512: at line 2

My test was using two copies of a table with differences between column values. Both were deployed in the same CDB or PDB. That meant it was either a missing table or a problem with my database link. Here’s the statement that caused the failure:

dbms_comparison.create_comparison(comparison_name      => 'COMPARE_NAME'

                                   , schema_name         => 'video'

                                   , object_name         => 'MEMBER#1'

                                   , dblink_name         => 'loopbackpdb'

                                   , remote_schema_name  => 'video'

                                   , remote_object_name  => 'MEMBER#2');

Unfortunately, there wasn’t any notable difference between the two database links. Playing around with it, I discovered the problem. While you don’t have to enclose your case sensitive password in double quotes for a CDB database link, you do need to enclose the password with double quotes in a PDB database link.

This database link fixed the problem:

CREATE DATABASE LINK loopbackpdb

  CONNECT TO video IDENTIFIED BY "Video1"

  USING 'video';

The delimiting double quotes on line 2 fixed the problem. Hopefully, this helps somebody who runs into it too. Any way, according to this June 2013 Oracle White Paper it would appear as a bug because it’s an inconsistent behavior between a CDB and PDB.

Oracle 12c Database Installation Guide

Oracle has finally made the much hyped and anticipated version of database i.e. 12cR1 publically available. The software is now available on OTN download or on Oracle edelivery Portal. Here we will look at Oracle Database 12c installation on Oracle Linux 6. The article assumes that you have Linux box, up and ready. Here we will using Oracle VirtualBox to host the machine.

Here are some other install guides on Oracle 12c Database Installation on Solaris 11 and Oracle 12c RAC Install on Linux 6.

Pre-Installation Tasks
Once you have your system ready and installation files downloaded from links above, you can proceed with pre-installation steps. It is worth mentioning that currently the database is only available for 64-bit OSs and that too only Linux and Solaris.

Hosts Configuration: After unpacking the zip files the first step is to properly name your host by editing the /etc/hosts file as below.
127.0.0.1         localhost.localdomain
10.0.2.15         VST-12c

Oracle Prerequisites: To perform the Oracle specific prerequisite tasks you can use the yum package which is available via ULN and also public yum. Please note that if you don’t have ULN subscription then you will have to use the 11g package to perform preinstallation steps. The Package for 12c is not available on public yum yet.

If you have ULN account then you can run the following command.
# yum install Oracle-rdbms-server-12cR1-preinstall -y
If you don’t have ULN subscription then you can use the following.
# yum install Oracle-rdbms-server-11gR2-preinstall -y

The package will install all the required packages, setup the kernel parameters and create the two mandatory groups i.e. dba and oinstall. However it will not make “Oracle” user member of those groups. You will have to make that change yourself.
Also paste the following profile options in .bash_profile file of the “Oracle” user. This will properly set the installation environment.
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export Oracle_HOSTNAME=VST-12c
export Oracle_UNQNAME=cdb12c
export Oracle_BASE=/u01/app/Oracle
export Oracle_HOME=$Oracle_BASE/product/12.1.0/db_1
export Oracle_SID=cdb12c
export PATH=/usr/sbin:$PATH
export PATH=$Oracle_HOME/bin:$PATH
export LD_LIBRARY_PATH=$Oracle_HOME/lib:/lib:/usr/lib
export CLASSPATH=$Oracle_HOME/jlib:$Oracle_HOME/rdbms/jlib

The Oracle_HOST should be the same as you set above.

A Few More Steps:
There are a few more steps before you can start the actual install process. The first step is to create the directories for installation and make the “Oracle” user owner of it.
mkdir -p /u01/app/Oracle/product/12.1.0/db_1
chown -R Oracle:oinstall /u01

chmod -R 775 /u01

Make sure you have sufficient space available. The space should be at least 10GB.
As a last step you need to disable Firewall and SELinux security. To disable Firewall go to System–>Administration–>Firewall and press the disable button. Click Apply to save.

To disable the SELinux security you need to edit the config file located in /etc/SELinux directory. Change the SELINUX parameter from “Enforcing” to “Disabled” as below.
#SELINUX=disabled

Installation Steps

Over the years the installation process has been made simpler by Oracle. Although 12c is a huge architectural shift in comparison to 11g but the installation is more or less the same. And that is actually a very good thing.

To start the install process navigate to database folder where you unzipped the install files and execute the runInstaller script.

$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 2166 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4031 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-06-26_11-35-01AM. Please wait ...

1. The installer window will pop up and will be something like below:
   
2. You can skip the Oracle support registration part although you will receive a warning message. Click Next.
   
3. On next screen choose skip updates and click Next.
   
4. Choose the first option as installation type. Click Next.
   
5. Choose Server class as the database option and click Next
   
6. Select Single Instance database and click Next.
   
7. Select the first radio button for install method. Click Next.
   
8. Select the locations of Oracle_BASE and Oracle_HOME. Provide dba group as the owner of database. Also name the container database and also the one pluggable database. This is the new architectural change and you read more on Container and Pluggable databases. Click Next.
   
9. Choose the Inventory location and also the Inventory owner group which should be oinstall. Click Next.
   
10. This is the installation summary screen and you can use the Edit link to change any option. Click Install when ready.
    
11. The installation may take a bit time. So wait and grab some snacks or Coffee.
    
12. When Setup gets completed the installer will prompt you to run these two scripts. Log in as root and run them.
    
13. After successful execution of scripts go back to Installer and click OK button.
    
14. The installation will resume to perform configurations.
    
15. The database creation is in progress.
    
16. Once the database has been created you can use this screen to change or set the passwords of seeded database accounts. Click OK when done. Also note the URL of EM Express for your database.
    

The installation is now complete!

Post Installation Tasks

To verify that everything is up and running you can always use the good old SQL*PLUS.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 12:46:12 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

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 banner from v$version;

BANNER

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

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

PL/SQL Release 12.1.0.1.0 - Production

CORE 12.1.0.1.0 Production

TNS for Linux: Version 12.1.0.1.0 - Production

NLSRTL Version 12.1.0.1.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

cdb12c

You can also use the EM express to see the current status. The DB console has been deprecated starting from 12c and an express version of Enterprise Manager will replace the DB Console. This is to give a more unified look and feel.

You can also edit the entry of database in oratab to make it auto start at OS startup. The file is located at /etc and entry should look like this.

cdb12c:/u01/app/Oracle/product/12.1.0/db_1:Y