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 126.96.36.199.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> show configuration
Configuration - myconf
Protection Mode: MaxAvailability
prima - Primary database
physt - Physical standby database
Fast-Start Failover: DISABLED
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
ORACLE instance shut down.
Operation requires startup of instance "prima" on database "prima"
Starting instance "prima"...
ORACLE instance started.
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 188.8.131.52.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 184.108.40.206.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;
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.