Simon Krenger

I was eager to try out the new Pluggable Database feature of the newly released Oracle 12c Database. I installed the software, created the database (see my post about the “ENABLE PLUGGABLE DATABASE” clause which I forgot the first time around) and then wanted to create a new Pluggable Database (PDB) like so:

SQL> CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger;
CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Oh, really strange, what kind of error message is that?
First off, I made sure my main database is a Container Database:

SQL> select cdb from v$database;

CDB
---
YES

Alright, so let’s query some of the views that contain information about our databases (container and pluggable):

SQL> desc cdb_pdbs;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PDB_ID                                    NOT NULL NUMBER
 PDB_NAME                                  NOT NULL VARCHAR2(128)
 DBID                                      NOT NULL NUMBER
 CON_UID                                   NOT NULL NUMBER
 GUID                                               RAW(16)
 STATUS                                             VARCHAR2(13)
 CREATION_SCN                                       NUMBER
 CON_ID                                             NUMBER

SQL> select * from cdb_pdbs;
select * from cdb_pdbs
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-00942: table or view does not exist

Huh? How can it be that I can execute the DESCRIBE statement but not select anything from the view?

Cause and Resolution

These errors appear when the CATALOG.SQL, CATPROC.SQL and PUPBLD.SQL scripts were not executed using the catcon.pl script provided by Oracle. This means views for the data dictonary were only created in your root database (the CDB) but not in the seed database. This leads to the errors above.

Run the scripts to create the catalog as follows:

$ PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
$ perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
$ perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
$ perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /home/oracle -b pupbld -u SYSTEM/oracle $ORACLE_HOME/sqlplus/admin/pupbld.sql;

After running these scripts with the catcon.pl script, the database now behaves as expected:

SQL> connect / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE kdb121p1 ADMIN USER simon IDENTIFIED BY tiger;

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE kdb121p1 OPEN;

Pluggable database altered.

SQL> select * from cdb_pdbs;

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
      DBID    CON_UID GUID			       STATUS
---------- ---------- -------------------------------- -------------
CREATION_SCN	 CON_ID
------------ ----------
	 2
PDB$SEED
4061965581 4061965581 E04C7957F2C30742E045000000000001 NORMAL
	 229	      1

[..]