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.
#1 von emre baransel am September 7, 2010 - 08:56
Restart is a useful feature !! Glad to be compatible with dataguard 🙂
#2 von Uwe Hesse am September 7, 2010 - 13:39
You’re welcome 🙂 Thank you for your nice feedback!
#3 von Stéphane Fromholtz am September 16, 2010 - 15:32
Having configured a Data Guard config using Physical standby and your previous article with dbms_service and the famous trigger, I’m happy to see that there is now a clean way to do this in 11g R2.
It really seems to be the best option for a Data Guard configuration with 11g. Thanks for this article, that will probably be helpful for the next Data Guard setup.
#4 von Uwe Hesse am September 20, 2010 - 09:49
glad that you found the article helpful 🙂
I am not sure whether the new method with Oracle Restart is „more clean“ than the old method with the Database Event Trigger before, though. It is just feasible to use Oracle Restart (if you have it setup) for that purpose also.
In other words: I would not take the effort to implement Oracle Restart only to get my services in a Data Guard Environment properly managed. That is more an additional benefit, in my view.
The big advantage of Oracle Restart is that it will start each component in the appropriate order and especially, it also will RESTART it (if possible), should it fail. That was the reason to give it that name, I guess 🙂
#5 von Stéphane Fromholtz am September 24, 2010 - 08:32
I did read another article about the memory usage of Grid Infrastructure (http://www.pythian.com/news/9179/oracle-11gr2-grid-infrastructure-memory-footprint/). It talks about 500Mb (see comments below for a fix) to run Grid Infra on a node. This is quite large for what it does to my opinion.
As you said, I’ll perhaps keep the trigger and the service for an 11g Data Guard setup.
#6 von TGASCARD am Oktober 13, 2010 - 16:48
You need to be careful with Data Guard and Oracle Restart. I found Bug 9645789: SWITCHOVER BY DATAGUARD BROKER FAILS BY ORA-16535.
I think it is better to use a trigger than srvctl add service.
#7 von Kotesh am Oktober 15, 2010 - 15:37
what is the command to add logical standby to olr. Is it srvctl add database -db_name -r logical_standby -s mount
#8 von monto am Oktober 19, 2010 - 18:52
I’m a regular visitor of your blog and i have question can i use oracle 11gr2 RAC(primary) and 11gr2(oracle restart) as standby which i assume i should be able to ,and configure an application service how should be by tns entry for this ,is it possible to put multiple address list when using scan name?What is that i need to do to make this service failover to standby database when using jdbc thin driver.
#9 von Uwe Hesse am Oktober 25, 2010 - 14:08
thank you for mentioning this bug that I did not notice yet. The Metalink Note only talks about a scenario with a RAC-Primary. Not sure whether this bug is also hitting in Single Instance setups. Will keep an eye on it 🙂
#10 von Uwe Hesse am Oktober 25, 2010 - 14:24
(look at http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/restart004.htm#i1008403 ) , but you wouldn’t give -s mount for a logical standby because it is always open
#11 von Uwe Hesse am Oktober 25, 2010 - 14:37
thanks for visiting my Blog regularly 🙂 Yes, you can use the SCAN entries in a tnsnames (or in a thin JDBC coding) from RAC Primary (first SCAN) and RAC Standby (second SCAN), very similar as you would do for two Single Instance DBs. We don’t call that Oracle Restart then but Clusterware resp. Grid Infrastructure. Oracle Restart is the Marketing Name for „Clusterware on a Single Instance Architecture“ 🙂
#12 von tanos am Mai 13, 2011 - 15:36
This arcticle is very helpfull.
I got a problem with the service creation
srvctl add service -d MG -s test-appli -l PRIMARY -e SELECT -m BASIC
I receive this error :
PRKO-2001 : Invalid command line syntax
So i check my resource
crsctl status resource ora.mg.db -p
and i find a server_pool associate is that normal.
The detail of my resource could you compare with yours?
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%)
DESCRIPTION=Oracle Database resource
#13 von Uwe Hesse am Mai 14, 2011 - 20:58
please take into account that the example in the posting is with Oracle Restart on a Single Instance system. That might have a different behavior as your environment which seems to be Grid Infrastructure on a RAC cluster. I can right now not reproduce your environment, because I have no MAA (RAC Primary + RAC Standby) setup available.You may consider to contact Oracle Support, though. They are so good with those troubleshooting issues that they can even make a living from it 🙂
#14 von Sigrid Keydana am Oktober 20, 2011 - 15:15
may I still add a comment / ask a question here? When I do a switchover in dgmrl in a DataGuard+Restart environment, the new primary (which, when still a standby, I added with -s mount -r physical_standby) is not opened, but mounted only…
Is this normal behavior, meaning you have to modify the Restart config before doing a switchover, – or shouldn’t Restart have done this modification automatically, exchanging the mount vs. open configs of the databases automatically?
In that case, I wonder what might be wrong in my setup…
#15 von Uwe Hesse am Oktober 20, 2011 - 16:52
indeed, after the sitchover the new Primary is supposed to reach the status OPEN without an additonal command from your side. If you look at the posting closer, you see that it is done that way: The last select of the user system would be impossible (because the connection was not as sysdba) in status MOUNT.
#16 von Sigrid Keydana am Oktober 27, 2011 - 10:06
many thanks for the answer! This is strange however, because on our system (which is 220.127.116.11.3 for the restart as well as the database home) it does not work like that – we have to reconfigure before the switchover in order to get the new primary opened… I guess we have to inspect the restart logs and try to find out why :-;
#17 von wendy am Januar 1, 2012 - 17:05
This is Wendy. I find this post of you regarding Oracle Restart and Dataguard. I already configured my Dataguard and Primary DB with dbms_service and database triggers per your other post. Is it worth to reconfigure it with oracle restart?
#18 von Uwe Hesse am Januar 1, 2012 - 17:11
in my opinion, it is more a matter of taste whether you use Oracle Restart or the Trigger approach, in order to achieve Client Connectivity. With the Grid Infrastructure already installed, I would use it when creating a Data Guard Configuration.
I would not remove my working trigger configuration to replace it with the Oracle Restart solution just to get the same result, though.
#19 von wendy am Januar 1, 2012 - 17:40
So is it possible I can configure ORacle Restart while keeping the dbms_services and triggers?
#20 von Uwe Hesse am Januar 1, 2012 - 23:04
I would not recommend to configure the service with Oracle Restart (as described in the posting you saw) AND also the trigger. It should be okay to configure the Databases with Oracle Restart additionally, though.
srvctl add database
srvctl add service
when you want to keep the trigger & dbms_service configuration.
#21 von Wendy am Januar 2, 2012 - 04:58
ok, Thanks. Have a happy new year.
#22 von Sung am März 8, 2012 - 22:15
I really enjoyed your blog and find your notes are very helpful. I have a question here. I have a two RAC nodes primary and two RAC nodes as standby, both in 1120301 version on linux redhat 5. How do we use oracle restart? I found in oracle docs, it said oracle restart is for standalone server. and For Oracle Real Application Clusters (Oracle RAC) environments, the functionality to automatically restart components is provided by Oracle Clusterware. But I could not find any oracle official documents for that. Can you help me? Thanks in advance.
#23 von Uwe Hesse am März 9, 2012 - 10:29
Sung, thank you for the nice feedback! To your question: With RAC, we don’t call it Oracle Restart indeed. It is the Clusterware that can be used to do the service configuration there. See:
#24 von Sung am März 12, 2012 - 16:25
Sorry, I just saw your comments today. Thanks for the link, it is very helpful.
I still need to clear on those srvctl add steps for RAC. So do we need to add asm, listener, primary database, standby database and all other related services? or it should already added when database, asm , listener created? I cannot find in oracle documents that the recommended steps/procedures for adding those components into oracle clusterware. Please help.
#25 von Uwe Hesse am März 17, 2012 - 11:23
Sung, the Oracle Universal Installer (OUI) adds these resources automatically into the Cluster Registry. You can monitor that with
crsctl stat res
#26 von firstname.lastname@example.org am Juni 13, 2012 - 22:14
Uwe-great blog, very helpful. However, I need some additional insight. Its seems that the Restart configured service will automatically register with the listener on port 1521. In my case I need to create services for the Data Guard broker to use ON A DIFFERENT port since 1521 is not open between the primary and standby. Here is the entry from listener.ora:
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = colorado.localdomain)(PORT = 1523))
(GLOBAL_DBNAME = PRI_DGMGRL.localdomain)
(ORACLE_HOME = /app/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = pri01)
(SERVICE_NAME = PRDRAC01_BLD_DGMGRL)
If I create a service using srvctl with for PRDRAC01_BLD_DGMGRL how do I get the service running on LISTENER_XPT using the non-default port.
#27 von Uwe Hesse am Juni 14, 2012 - 12:04
Mitchell, you need to point to the non-standard port number with the initialization parameter LOCAL_LISTENER then:
#28 von email@example.com am Juni 14, 2012 - 19:03
Thanks for the quick reply, however, still more questions. This is how I would add a local listener and services through srvctl. My end game is to have the service and listener startup with Oracle Restart.
1. srvctl add listener -l LISTENER_XPT -p „TCP:1523“ (note there is no option to associate a service.
2. srvctl add service -d prdrac01 -s PRI_PRDRAC01_BLD_DGMGRL (note there is no option to associate this service with the listener just created.
3. ALTER SYSTEM SET LOCAL LISTENER=’LISTENER_XPT‘ scope=both;
So, how does the service know which listener to activate on ?
Thanks again for your assistance.
#29 von Uwe Hesse am Juni 15, 2012 - 08:48
The PMON background process of the instance where you set LOCAL_LISTENER will register ALL services of this instance at that listener then. You do not need (and should not, in my opinion) create an additional listener for Data Guard, by the way.
#30 von firstname.lastname@example.org am Juni 15, 2012 - 14:59
I tried this last night and did indeed find out that the new listener had ALL services registered with it, The reason for the DG listener is that I have a separate port opened for inter-site communication. That port is not open for user requests and therefore needs a separate listener.
Thanks for your information. Cheers.
#31 von mark am Juni 27, 2012 - 15:08
Thanks for your posts they are very informative.
I have a question regarding the -s flag for the database resource. I have create the standby resource using:
$> srvctl add database -d physt -o /home/path -r PHYSICAL_STANDBY -s MOUNT
However, this value persists even after the database becomes the primary (following switchover). I am therefore left with a database ‚physt‘ which has now become the primary but still has a startup status of mount:
$> srvctl config database -d physt
Start options: mount
On killing this instance I expect Oracle Restart to make a couple of attempts at restarting it before a failover occurs. Unfortunately the database only comes up as far as mounted.
(for information, the prima database service was created with
$> srvctl add database -d prima -r PRIMARY
and retains the status of ‚open‘ when it becomes the standby)
This is probably a simple oversight on my part but I’m currently stumped by it.
I appreciate I’m replying to an older post but would appreciate it if you have time to answer.
#32 von Buhari am August 8, 2012 - 20:13
Ive been trying to implement a seamless application solution. So far Ive Installed the Grid Infrastructure software and Oracle software as you did. Then I configured a Primary and Standby database using the broker for fast start fail over. So I can automatically fail over. My problem is now getting the Oracle restart to work properly. Ive registered the listener, added the database, and added the application service, along with ons.
When I start all those added components using srvctl or by just rebooting the sever I notice that everything automatically starts as planned but when I log into the database using „sqlplus / as sysdba“ it says that I am connected to an idle instance. I then proceeded to start the database using startup from the sqlplus command line and the instance started.
When I checked the pmon process I now saw that 2 instances where started. One from Oracle restart and the other from the command line.
So I guess my question to you is how is that happening? Maybe I missed someting? And when I add the database using „srvctl add“ whey didnt every synchronize as far as the instances?
#33 von Uwe Hesse am August 8, 2012 - 23:09
Buhari, I can only guess that you may not have set the right ORACLE_SID environment variable before you do sqlplus / as sysdba. Or maybe it took Oracle Restart a little longer to start the instance and your command came before it could actually start it.
#34 von Pavol Babel am Juli 11, 2013 - 03:02
When switchover is performed on database, everything is OK, however the definition of services in cluster is not changed after role transitions. Still some steps should be performed manually after switchover.
srvctl modify database -d PRIM -r PHYSICAL_STANDBY -s MOUNT
srvctl modify database -d SEC -r PRIMARY -s OPEN
#35 von mark am Juli 11, 2013 - 10:30
We have found exactly the same thing (for switchover and for failover). More importantly if the manual action is not carried out then if the host for database PRIM restarts (quite feasible expecially if it was a host error that triggered the failover) then database PRIM will open READ ONLY. This is Active Data Guard.
Exactly the same happens if using OEM Grid Control (11g or 12c).
This is hardly a complete solution in my opinion. Certainly the sales pitch that DBAs can devolve powers to other areas of their IT departments (operations bridge etc.) does not tie in with having to log onto the host and start using command line utilities like srvctl.
So using Oracle Restart with Data Guard, following all documentation/best practice, can inadvertently turn on Active Data Guard.
#36 von Uwe Hesse am Juli 19, 2013 - 19:15
Pavol Babel & Mark,
I have seen that behavior – nasty, you’re right. But isn’t that gone with recent versions like 18.104.22.168? Must check that once again…
#37 von Mark am Juli 30, 2013 - 10:54
I’m sorry to say it isn’t fixed in 22.214.171.124 – we are using that and it definitely still happens.
#38 von Uwe Hesse am August 13, 2013 - 09:44
Mark, thank you for keeping us informed about that nasty issue! Will keep an eye on it.
#39 von Marc Vandermeiren (Mata) am September 18, 2013 - 18:08
Hi Uwe , Mark , Pavol
this is indeed a great post ,
we have the same problem in 126.96.36.199 and I implemented following workaround :
– create a startup trigger that alters the standby database back to mount state in case it opens
– configure oracle restart with the „-s open“ option for both standby and primary database so that the primary will always open.
CREATE OR REPLACE TRIGGER avoid_active_dataguard
AFTER STARTUP ON DATABASE
/* This trigger is created because the startup option in oracle restart is not changed automatically
after a switchover or failover .
The result can be that
1.a standby database is opened read only which should be avoided when no license for active dataguard is purchased
2.a primary database is mounted and not opened
IF sys_context(‚userenv‘,’database_role‘) = ‚PHYSICAL STANDBY‘
EXECUTE IMMEDIATE ‚ALTER DATABASE CLOSE‘;
in the alertfile you will see :
Physical standby database opened for read only access.
ALTER DATABASE CLOSE
Warning: ALTER DATABASE CLOSE is not a publicly supported command.
Wed Sep 18 17:56:26 2013
#40 von Uwe Hesse am September 19, 2013 - 08:57
Marc, thanks for sharing this workaround! Did you confirm with Oracle Support that you can do that safely? Just curious 🙂
#41 von Marc Vandermeiren (Mata) am September 28, 2013 - 13:46
Hi Uwe ,
Oracle Support told me the command is not in the Oracle documentation and should not be used interactively.It may work now but you cannot rely on it to work in new releases.
They have given me the docid that contains a workaround using srvctl modify commands to be executed after each switchover/failover.
ORACLE RESTART – Physical Standby Database Is Started In Mode „Open, Readonly“ After Server Reboot (Doc ID 1436313.1)
#42 von Pavol BabelPavol am September 28, 2013 - 18:38
Using ALTER DATABASE CLOSE is realy nasty hack 😉 I remember so clearly how we used ALTER DATABASE DISMOUNT on Standby Database (since background processes didn’t release file handlers on datafiles) 9i, unfortunately it stopped working on 11g 😉
Probably the best solutions is two write actionscript and register it to cluster. Actionscript checks database role every 30 seconds and modify options in srvctl accordingly
#43 von Uwe Hesse am Oktober 1, 2013 - 10:20
Marc, thank you for mentioning the MOS Note 1436313.1
Pavol, what you say is an option for RAC environments with a clusterware running – which is not the case with Oracle Restart aka Grid Infrastructure for a Standalone Server 🙂
#44 von Isam am Dezember 12, 2014 - 19:44
we have standby database as Grid infrastructure environment , when servers rebooted the database started in open mode how can we configure it to start in mount state we use 188.8.131.52
#45 von Erin am August 15, 2016 - 14:37
Good article! I have a question here I hope someone can help me out —
I have the above setting. The problem I found out was when the standby was down, the service I had on the primary was taken down by oracle, how could I do so Oracle would not take down the application service on primary when there is problem with standby?
#46 von Ahmed Abdel Fattah am Januar 14, 2017 - 18:27
Thanks Uwe, nice post as usual.
Just a small question, If we added FSFO (Fast Start FailOver) to this post’s configuration (DG + Oracle Restart) or (DG + GI), any recommendation for the timing configuration of the different components?
For example, the observer may be quickly failover to the standby, while the database may be required to be just restarted by the Oracle Restart?
So, any precautions to avoid conflict between FSFO configuration and Oracle Restart configuration with DG?
#47 von Uwe Hesse am Januar 18, 2017 - 10:53
Ahmed, sure, you may change the configuration property faststartfailoverthreshold to something larger than the default 30 seconds if Oracle Restart needs more time to just restart your crashed primary. No recommendation here that I’m aware of, though.