In this blog I want to illustrate the benefits of deploying PDB with RAC Services.  Although the key ingredient is the Service, RAC provides the final mile for scalability and availability.  In my mind I would not implement PDB w/o RAC

Anyways here we go…

The goal is to illustrate that Database [RAC] Services integration with PDBs provides seamless management and availability.

Initially, we have only the PDB$SEED. 

SQL> select * from v$pdbs;


CON_ID       DBID     CON_UID GUID                             NAME                           OPEN_MODE  RES OPEN_TIME                                                              CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ---------- --- --------------------------------------------------------------------------- ---------- ----------
2 4080865680 4080865680 F13EFFD958E24857E0430B2910ACF6FD PDB$SEED                       READ ONLY  NO  17-FEB-14 01.01.13.909 PM                                                 1720768  283115520

Let's create a PDB from the SEED (I have shown this from an earlier Blog post)

SQL> CREATE PLUGGABLE DATABASE pdbhansolo admin user hansolo identified by hansolo roles=(dba);

Pluggable database created.

Now we have the new PDB listed.

SQL> select * from v$pdbs;

CON_ID     DBID       CON_UID     GUID                            NAME                           OPEN_MODE  RES OPEN_TIME                                                              CREATE_SCN TOTAL_SIZE
---------- ---------- ---------- -------------------------------- ------------------------------ ----------- ----------------------------------------------------------------------- ---------- ----------
2          4080865680 4080865680 F13EFFD958E24857E0430B2910ACF6FD PDB$SEED                       READ ONLY  NO  17-FEB-14 01.01.13.909 PM                                                 1720768  283115520
         3 3403102439 3403102439 F2A023F791663F8DE0430B2910AC37F7 PDBHANSOLO                     MOUNTED        17-FEB-14 01.27.08.942 PM                                                 1846849          0

But notice that its in "MOUNTED" status. Even if I restart the whole CDB, the new PDB will not come up in OPEN READ WRITE mode. If we want to have the
PDB available on startup. Here's how we go about resolving it.

When we create or plug in a new PDB, a default Service gets created, as with previous versions, it is highly recommended not to connect to Service. Oracle took this one step forward and forced users to create a user generated Service. So let's associate a user Service with that PDB. Notice that there's a "-pdb" flag in the add service command.

$ srvctl add service -d dagobah -s hoth -pdb pdbhansolo


[oracle@rac02 ~]$ srvctl config service -d dagobah -verbose
Service name: Hoth
Service is enabled
Server pool: Dagobah
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
----> Pluggable database name: pdbhansolo
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
Preferred instances: Dagobah_1
Available instances: 

And the Service is registered with the listener

     
[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 17-FEB-2014 13:34:41

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                17-FEB-2014 12:59:46
Uptime                    0 days 0 hr. 34 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac02.viscosityna.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/product/12.1.0/db/admin/Dagobah/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "Dagobah" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "DagobahXDB" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "Hoth" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
----> Service "pdbhansolo" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "r2d2" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
The command completed successfully

Now let's test this. I close the PDB and I also stopped the CDB (probably not necessary, but what the heck :-))

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdbhansolo close;

[oracle@rac02 ~]$ srvctl stop database -d dagobah

[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 18-FEB-2014 15:36:49

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                18-FEB-2014 12:57:30
Uptime                    0 days 2 hr. 39 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX3", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
The command completed successfully


[oracle@rac02 ~]$ srvctl start database -d dagobah

[oracle@rac02 ~]$ lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 18-FEB-2014 15:37:39

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                18-FEB-2014 12:57:30
Uptime                    0 days 2 hr. 40 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.41.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=rac02.viscosityna.com)(PORT=5500))(Security=(my_wallet_directory=/u02/app/oracle/product/12.1.0/db/admin/Dagobah/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX3", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM3", status READY, has 2 handler(s) for this service...
Service "Dagobah" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "DagobahXDB" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
Service "Hoth" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
----> Service "pdbhansolo" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...</strong>
Service "r2d2" has 1 instance(s).
  Instance "Dagobah_1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac02 ~]$

SQL> select NAME,OPEN_MODE from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
-----> PDBHANSOLO                     READ WRITE

Now I can connect to this PDB using my lovely EZConnect string

sqlplus hansolo/hansolo@rac02/hoth

The key here is that RAC Service of PDBHANSOLO (non-default) becomes an important aspect of PDB auto-startup. Without the use the non-default service the PDB does not open 'read write' automatically. So where does RAC fit in here. Well if I have say a 6 node RAC cluster, I can have some PDSB-Services not started on certain nodes, this effectively prevents access to those PDB from certain nodes, thus I can have a certain pre-defined workload distribution. That's a topic for my next Blog and Oracle Users Group presentation 🙂