Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in the new Oracle 12c PL/SQL Programming Book, that won’t be out until January 2014.
If you’re new to Oracle 12c, which is most of us, then 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:
- The following SQL command lets you create a pluggable database (PDB) with a
videouser assigned to it:
1 2 3 4 5 6 7
CREATE PLUGGABLE DATABASE videodb ADMIN USER videoadm IDENTIFIED BY Video1 ROLES = (dba) DEFAULT TABLESPACE videots DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' SIZE 500M ONLINE FILE_NAME_CONVERT = ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\', 'C:\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
- You need to stop the Oracle listener and modify the
listener.orafile. Lines 9 through 12 configure a
VIDEODBOracle SID. After you make the changes, start the Oracle listener.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
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)) ) )
- You also need to add a
VIDEOTNS alias to the
tnsnames.orafile for the
VIDEODBpluggable database (PDB).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
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) ) )
- You connect as the
VIDEOpluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the
VIDEODB, you’ll be prompted for a password. The required password is the
Video1password that you used when you set up the
sqlplus sys@VIDEO AS sysdba
- After authenticating as the
SYSDBA, you need to start the
VIDEODBpluggable 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
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
- 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
SYSDBAfor the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm;
- After you’ve done all the prior steps, you can connect with the following as the Administrative
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\mclaughlinm>sqlplus /nolog SQL*Plus: Release 22.214.171.124.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"
- Once you’re connected as the
SYSDBArole, 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
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
DBMS_COMPARE isn’t provisioned automatically, and you need to manually apply it in your
ADMIN user for the PDB. I’ve blogged about it here.