MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Provision Oracle 12c PDB

with 8 comments

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:

  1. The following SQL command lets you create a pluggable database (PDB) with a video user 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
  1. 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.
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))
    )
  )
  1. You also need to add a VIDEO TNS alias to the tnsnames.ora file for the VIDEODB pluggable 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)
    )
  )
  1. 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
  1. 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;
  1. 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;
  1. 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\mclaughlinm>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"
  1. 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. I’ve blogged about it here.

Written by maclochlainn

September 13th, 2013 at 1:51 am

Posted in Oracle,Oracle 12c,sql,SQL*Plus

Tagged with , , ,

«
»