MacLochlainns Weblog

Oracle OpenWorld 2013 and JavaOne 2013 are over and the America’s Cup a done deal. It was a full week. I didn’t have a moment to blog because I stay out in Livermore, which adds an hour plus to the beginning and ending of my day. It’s funny but I didn’t get a sense from some that they were excited about Oracle 12c. That’s probably because they don’t appear to know too much about it yet.

The Container Database (CDB) versus Pluggable Database (PDB) has enormous impact on how we can leverage Oracle in the private or public cloud. Admittedly, the ideas and concepts take some time to understand, implement, and appreciate.

Talking to folks, I got some great ideas for future blog posts. If you have any that I should add, let me know with a comment. I think the first one will show everyone how to migrate traditional surrogate key primary key columns to Oracle 12c‘s new Identity Columns.

Having seen some presentations referencing zetta-bytes, I wondered to myself (and now blog readers): “When will the Exadata server be replaced by a Zettadata server?”

Up there on my list for new blog posts are Oracle 12c‘s new Invisible Columns and new DBMS_REDACT package. For those interested, I’ll also present at UTOUG’s Fall Symposium on October 29th, 2013 on SQL and PL/SQL New Features in the Oracle Database 12c.

It was great to see familiar faces and sad to note those missing. Somehow the energy in Dell’s demo ground didn’t quite replace the old Quest Software demo ground’s pizazz.

Once home, I had to pivot back to task. I’m copy editing the Oracle Database 12c Programming PL/SQL Programming book, which will release in February 2014.

If you’re wondering about the photo of the Golden Gate Bridge, my son Joseph took it with his new iPhone 5s. I’m looking forward to my own iPhone upgrade in November. 😉

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\');

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.

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))
    )
  )

3. 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)
    )
  )

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

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

SQL> startup
Pluggable DATABASE opened.

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;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL

SQL> ALTER SESSION SET container=pdborcl;
SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN;
SQL> CREATE USER johnny IDENTIFIED BY johnny;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        READ WRITE NO         NORMAL

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

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"

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;

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.