Simon Krenger

As of PSU 12.1.0.2, Oracle introduced “PDB State Management Across CDB Restart”, which will automatically put a pluggable database in a previously defined state on database startup. To show the current saved state of the PDBs, you can query the documented view cdb_pdb_saved_states:

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME		       INSTANCE_NAME		      STATE
------------------------------ ------------------------------ --------------
P1			       cdb2			      OPEN
P2			       cdb2			      OPEN

But beware: When you unplug and plug in the database, this saved state will be lost.

I think this is one of the more awesome improvements in 12.1.0.2, since the original startup trigger was more of a workaround than a real solution.

To connect to a PDB, you have to use the service name of the pluggable database. But what when there is an older legacy application that can only use an SID to connect to a database? And what if we just migrated an older legacy database to a PDB? When starting the application, you might see something like:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

There is a parameter called USE_SID_AS_SERVICE_listener that can be used to tell the listener to handle SIDs like service names. So the listener will interpret the SID given in the connection as a service name and will then connect you to the database.

Update: Starting with Oracle 12.1.0.2, it is possible to save a PDB state so this workaround is no longer necessary.

When starting up an Oracle 12c database with pluggable databases, the pluggable databases in a container database are not automatically opened, just placed in MOUNT mode. This can be a problem in some cases.

To automatically open the pluggable databases on startup, you’ll have to create an AFTER STARTUP trigger in the CDB. For my own reference, I will post such a trigger here:

CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
     EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_all_pdbs;
/

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:

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!