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!
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.
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 184.108.40.206.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 220.127.116.11.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>