Wednesday, June 26, 2013

Oracle 12c - need to start container AND pluggable databases.

In Oracle 12c we start the Container Database (CDB) the same as we started previous database versions.

[oracle@rac1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2289016 bytes
Variable Size             989856392 bytes
Database Buffers          654311424 bytes
Redo Buffers                7061504 bytes
Database mounted.
Database opened.

But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
PDB3                           MOUNTED

From CDB$ROOT we can manage any PDB.  

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

To open one PDB :-

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

To open ALL PDBS :-

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

Or we can move down to a PDB container to stop and start them individually.

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> shutdown;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.


Startup of PDBs can be automated using a startup trigger.

SQL> create or replace trigger Sys.After_Startup
                          after startup on database
begin
   execute immediate 'alter pluggable database all open';
end;
/  

Trigger created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size             973079224 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE