Just playing around with Data Guard & Oracle Restart in the current release 11gR2 – it’s really cool!  Look at Joel Goodmans introduction into Oracle Restart, if you never heared of it before. At first, I installed Oracle Database 11gR2 Grid Infrastructure for Standalone Server to get Oracle Restart, then Oracle Database 11gR2 Software only. I did not use ASM in this setup – everything is running inside a VM on my notebook with only one single hard drive really, so ASM is of not much help here 🙂

Listener and Database must be registered manually if NETCA and DBCA have not been used to create them. I did that on the command line, therefore the following steps.

First registering the listener:

$ srvctl add listener
-o /u01/app/oracle/product/11.2.0/dbhome_1

$ srvctl add database -d prima
-o /u01/app/oracle/product/11.2.0/dbhome_1
-r PRIMARY

Above added the Primary to the Oracle Local Registry (OLR)

$ srvctl add database -d physt
-o /u01/app/oracle/product/11.2.0/dbhome_1
-r PHYSICAL_STANDBY -s MOUNT

Above added the Physical Standby to the OLR. -s is the startmode which defaults to OPEN. My Standby shall get mounted automatically. I would choose OPEN if I am inclined to use the 11g New Feature Real-Time Query – supposed I had purchased Active Data Guard.

I will now add an Application Service to my Configuration – with the same purpose demonstrated already in this posting, but without having to use DBMS_SERVICE. Instead, Oracle Restart will take care for the start of the services in my Data Guard environment according to the assigned Database Role. The creation of a Database Trigger for that purpose is no longer necessary. Instead, the usage of Oracle Restart is recommended:

$ srvctl add service -d prima
-s prod -l PRIMARY
-e SELECT
-m BASIC

-l is the Database Role in which this service prod is supposed to get started, -e is the Failover Type and -m the Failover Method. Next I configure Failover Retries (-z) and Failover Delays (-w):

$ srvctl modify service -d prima
-s prod
-z 180
-w 1

The same has to be done for the Standby Database also – the -l PRIMARY parameter takes care that the service will not be offered unless this database becomes Primary:

$ srvctl add service -d physt
-s prod
-l PRIMARY
-e SELECT
-m BASIC

$ srvctl modify service -d physt
-s prod
-z 180
-w 1

The nect command is going to start the service actually. We need to do this only for the first time manually – again without needing DBMS_SERVICE for it:

$ srvctl start service -d prima -s prod

Now let’s investigate the properties of our new service:

$ srvctl config service -d prima -s prod

Service name: prod
Service is enabled
Cardinality: SINGLETON
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 180
TAF failover delay: 1
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE

I have prepared a connect descriptor prod in the same way as in this posting shown already. Using it to connect as system user. Now I kill the SMON process of my Primary Instance (Don’t do that at home). Then a select on v$instance in my user system session waits for the reestablishing of the connection (maximum 180 seconds with my settings above) – after less than 5 seconds, the ohasd (Oracle High Availabilty Services Daemon) recognizes this and restarts the Primary Instance. After less than 20 seconds, I see my select on v$instance come through. This was a Transparent Application Failover without another Instance or Database in the game 🙂 Really cool, isn’t it?

Now let’s do a switchover and see how Oracle Restart will take care to start the prod service on the new Primary and how the connection again is reestablished there:

$ dgmgrl sys/oracle@prima

DGMGRL for Linux: Version 11.2.0.1.0 - Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration

Configuration - myconf

 Protection Mode: MaxAvailability

 Databases:

 prima - Primary database

 physt - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> switchover to physt

Performing switchover NOW, please wait...

New primary database "physt" is opening...

Operation requires shutdown of instance "prima" on database "prima"

Shutting down instance "prima"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "prima" on database "prima"

Starting instance "prima"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "physt"

The switchover was done as usual. Now what about the service and the session?

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-SEP-2010 08:45:25

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                06-SEP-2010 07:40:29
Uptime                    0 days 1 hr. 4 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/
                           network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/
                          tnslsnr/uhesse/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uhesse)(PORT=1521)))
Services Summary...
Service "logst_DGMGRL" has 1 instance(s).
 Instance "logst", status UNKNOWN, has 1 handler(s) for this service...
Service "physt" has 1 instance(s).
 Instance "physt", status READY, has 1 handler(s) for this service...
Service "physt_DGB" has 1 instance(s).
 Instance "physt", status READY, has 1 handler(s) for this service...
Service "physt_DGMGRL" has 1 instance(s).
 Instance "physt", status UNKNOWN, has 1 handler(s) for this service...
Service "prima" has 1 instance(s).
 Instance "prima", status READY, has 1 handler(s) for this service...
Service "prima_DGB" has 1 instance(s).
 Instance "prima", status READY, has 1 handler(s) for this service...
Service "prima_DGMGRL" has 1 instance(s).
 Instance "prima", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
 Instance "physt", status READY, has 1 handler(s) for this service...
The command completed successfully

The service was started on the new Primary by Oracle Restart automatically. Now the session:

SYSTEM@prod > select instance_name from v$instance;

INSTANCE_NAME

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

physt

SYSTEM@prod > select username,FAILED_OVER,FAILOVER_METHOD,FAILOVER_TYPE 
              from v$session where username='SYSTEM';

USERNAME                       FAI FAILOVER_M FAILOVER_TYPE

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

SYSTEM                         YES BASIC      SELECT

The session was again „unsinkable“ and survived the switchover by failing over to the new Primary. When I reboot my server, ohasd together with DMON now take care that Listener, Primary, Standby and Service all get started in the correct order.