Auto restart of PDB in multi tenant ( CDB) database

Most of the time , when we start/restart  the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE )  to open the pdbs .  In 12c release 2 , the other option was to save the current state  , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE). 


Here is an example. 

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted. 


SQL> col name format a20

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              MOUNTED


Now , let us open the PDB , confirm its status and save the state. 
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------

PDB_SIS               OPEN

Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs  re-initalized to the saved state. 

SQL>  shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1.0335E+10 bytes

Fixed Size                 12468584 bytes

Variable Size            2751467160 bytes

Database Buffers         7549747200 bytes

Redo Buffers               21082112 bytes

Database mounted.

Database opened.

SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE

-------------------- --------------

PDB_SIS               OPEN

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE

-------------------- ----------

PDB$SEED             READ ONLY

PDB_SIS               READ WRITE

Thanks !

Creating Common User in Oracle 12c

Oracle has introduced multi-tenant architecture in 12c. With this , we will have an instance ( Single Instance) , multiple instances ( RAC) will be hosting multiple pluggable databases ( PDBs) with in a  container database  ( CDB). 

Please note that the  CDB/PDB is extra licensing option . With the regular license , we can have a CDB with only one PDB. 

Accordingly , there are changes to how users are maintained in 12c.  There are two type of users. 

a) Common User  

b) Local User 

In short , common user is for all PDBs and Local user is local to a PDB. 


There are few rules / restrictions in creating a common user. 
One of them being , the username has to be prefixed with ( c##) . 
However  , this can be circumvented . But I would recommend against against this practice, as this is not ( likely ) supported by oracle . 

Here is an example . 


SQL> show con_name

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

SQL> create user c##zahir identified by ZahirPassword container=all;

User created.


As you can see, common user ( c##zahir) was created sucessfully . 

Let us try , by removing c##. 

SQL> create user zahirm identified by ZahirPassword container=all;
create user zahirm identified by ZahirPassword container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


Let us set the UNDOCUMENTED parameter to true . 
Being this is undocumented parameter ; as like many Oracle professionals , 
I would not recommend with meddling  with these  parameters unless otherwise directed by Oracle support. 

Note : This parameter also needed , when you need to create a CDB from sqlplus. 

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> create user zahirm identified by ZahirPassword container=all;

User created.

Voila . The user was created . 


Comments Welcome. 

Renaming PDB

Oracle 12c has introduced CDB/ PDB architecture . Prior to 12c , Only one database can  be serviced by an instance ( in a Single Instance ) / by instances ( in RAC) . In other words , no more than one databases can be serviced by an instance(s). 

In Oracle 12c, we can have ( pluggable) databases that are  contained with in a  (container) database. Container database will not have user data , where as the pluggable database will have the user data. This is almost synonymous with Sybase / MS SQL Server or DB2.  Loosely speaking , we can equate Container database as to Combination of System (master , msdb , model and resource ) databases in Sybase / MS-SQL Server. 

Note : With the standard license , we can one CDB and one PDB . We would need additional license , if we are planning to have more than one PDB. 

Let us say , we created a database with DBCA and asked for 2 PDBs with the pdb_docs, then we will have a CDB ( whatever we have given ) , PDB seed  and two pdbs with pdb_docs1 and pdb_docs2. 

As you can see , OXYZ12DB is a the containter database .

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:37:02 2015

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, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name , CDB  from v$database;

NAME      CDB
--------- ---
OXYZ12DB  YES

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB$SEED                       READ ONLY  NO
PDB_DOCS1                      READ WRITE YES

PDB_DOCS2                      READ WRITE NO

Now , let us see how we can change the name of pdb_docs2 to pdb_noncredit .
We need to be connected the correct PDB and it needs to be in restricted mode. 

C:\>sqlplus sys@pdb_docs2 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:50:16 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name , open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------

PDB_DOCS2                      READ WRITE


We need to open the PDB in restricted mode , so we can its change its name. 

SQL>  alter pluggable database pdb_docs2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb_docs2 open restricted;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_DOCS2                      READ WRITE YES



SQL> alter pluggable database pdb_docs2  rename global_name to pdb_noncredit;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_NONCREDIT                  READ WRITE YES

Here we go. Now the PDB's name is changed to PDB_NONCREDIT.

Make sure that you change your tnsnames.ora to reflect the new name . 

Comments welcome.