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.]
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:
- 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.
- 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.
- 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.
No comments:
Post a Comment