Simon Krenger

As many of my colleagues, I have been eager to try out the new Pluggable Database (PDB) feature of the newly released Oracle 12c Database. I installed the software, created the database using my default CREATE DATABASE statement I have lying around and then tried to create a pluggable database by issuing the following command:

SQL> create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/');
create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/')
                          *
ERROR at line 1:
ORA-65090: operation only allowed in a container database

It turns out a database explicitly needs to be created as a Container Database (CBD) in order to be able to create new PDBs!

Cause

Now before you can create a pluggable database, there are some prerequisites. The most important one is that you have to create the database with the “ENABLE PLUGGABLE DATABASE” clause:

To create a CDB, the CREATE DATABASE statement must include the ENABLE PLUGGABLE DATABASE clause. When this clause is included, the statement creates a CDB with the root and the seed.

When the ENABLE PLUGGABLE DATABASE clause is not included in the CREATE DATABASE statement, the newly created database is a non-CDB. The statement does not create the root and the seed, and the non-CDB can never contain PDBs.

If you did not specify the above clause, you will not be able to create a PDB. Also, make sure the ENABLE_PLUGGABLE_DATABASE initialization parameter is set to “TRUE”. To find out wherever a database was created as a Container Database, check the v$database dynamic performance view:

SQL> select cdb from v$database;

CDB
---
NO

1 row selected.

SQL>

Since I used a CREATE DATABASE statement from my default Oracle Database 11g installation, the database shown here is NOT a Container Database.

Resolution

Follow the instructions in the Oracle documentation for Creating and Configuring a CDB. That documentation is extremely valuable when coming in contact with CDBs.

To create a CDB, ensure that you have set the ENABLE_PLUGGABLE_DATABASE initialization parameter to TRUE in your SPFILE. Then, explicitly specify the clause in your CREATE DATABASE statement:

CREATE DATABASE kdb121
	LOGFILE GROUP 1 ('/u01/app/oracle/oradata/kdb121/redo01a.rdo',
			'/u02/app/oracle/oradata/kdb121/redo01b.rdo',
			'/u03/app/oracle/oradata/kdb121/redo01c.rdo') SIZE 100M,
	GROUP 2 ('/u01/app/oracle/oradata/kdb121/redo02a.rdo',
		'/u02/app/oracle/oradata/kdb121/redo02b.rdo',
		'/u03/app/oracle/oradata/kdb121/redo02c.rdo') SIZE 100M,
	GROUP 3 ('/u01/app/oracle/oradata/kdb121/redo03a.rdo',
		'/u02/app/oracle/oradata/kdb121/redo03b.rdo',
		'/u03/app/oracle/oradata/kdb121/redo03c.rdo') SIZE 100M
        CHARACTER SET AL32UTF8
        NATIONAL CHARACTER SET AL16UTF16
        EXTENT MANAGEMENT LOCAL
        DATAFILE '/u02/app/oracle/oradata/kdb121/system01.dbf'
	SIZE 700M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
        SYSAUX DATAFILE '/u02/app/oracle/oradata/kdb121/sysaux01.dbf'
	SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
        DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u02/app/oracle/oradata/kdb121/temp01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
        UNDO TABLESPACE undo DATAFILE '/u02/app/oracle/oradata/kdb121/undo01.dbf'
	SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
        ENABLE PLUGGABLE DATABASE
        SEED
        FILE_NAME_CONVERT = ('/u02/app/oracle/oradata/kdb121/', '/u02/app/oracle/oradata/pdbseed/')
        SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
        SYSAUX DATAFILES SIZE 100M
        USER_DATA TABLESPACE users DATAFILE '/u02/app/oracle/oradata/pdbseed/users01.dbf'
	SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

After the creation of the database and creation of the catalog (Important: Run the catalog.sql, catproc.sql and pupbld.sql script using the catcon.pl tool provided by Oracle), we can check if the database was indeed created as a CDB:

SQL> select cdb from v$database;

CDB
---
YES

Very nice. We are now ready to create a new pluggable database using the command above:

SQL> CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger FILE_NAME_CONVERT = ('/pdbseed/','/kdb121p1/');

Pluggable database created.

SQL>

After creating the Pluggable Database, we need to open it since the PDB is created in the MOUNT state:

SQL> ALTER PLUGGABLE DATABASE kdb121p1 OPEN;

Pluggable database altered.

And now I am able to connect to the newly created pluggable database:

$ sqlplus simon/tiger@localhost:1521/kdb121p1.krenger.local

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jun 29 14:55:23 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>