Sunday, January 18, 2015

Autostart of Pluggable Databases

In Oracle, you would need to start all pluggable database manually after the startup of the Container Database. To automate the startup of pluggable databases, you have 2 options. First option is to create a system trigger (having pluggable database “open” commands for the pluggable databases) which fires at container database  startup and opens the pluggable database(s). Trigger code would be something like this.
CREATE OR REPLACE TRIGGER  open_pluggable_db
   EXECUTE IMMEDIATE 'alter pluggable database all open';

Second option which I seems batter to me is to register the database with Grid infrastructure autostart option (This is only possible if you already have GI installed). Support we have a database with name pdb1, we will use following method to register the pluggable database in Grid Infrastructure as a service so that I starts automatically
As RDBMS software owner user (oracle), execute following where container database name is cdb and pluggable database is pdb1

[oracle@salman1 ~]$ srvctl add service -db cdb -pdb pdb1 -service pluggable_pdb1

As GI software owner (grid), execute following to see the status of this pluggable database
[grid@salman1 ~]$ srvctl start service -service pluggable_pdb1 -db cdb
[grid@salman1 ~]$ srvctl config service -service pluggable_pdb1 -db cdb
Service name: pluggable_pdb1
Cardinality: SINGLETON
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
Pluggable database name: pdb1
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled

Starting, instead of adding pluggable database as a service to the GI, we have an option to preserve the state of the pluggable database. After this setting, every time when container database would start, pluggable database would be in the state which was preserved. It means that if we preserve the “open” state for any of our pluggable database, we will find it open every time container database is started. Following is the example
SQL> select name,open_mode from v$pdbs;

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

SQL>  select con_name,state from dba_pdb_saved_states;

no rows selected

SQL> alter pluggable database pdb1 open;

Pluggable database altered.


Pluggable database altered.

SQL> select con_name,state from dba_pdb_saved_states;

---------- --------------
PDB1       OPEN

Now preserved state for pdb1 is “open” and hence it would always become open with the start of container database

No comments: