Data Guard & Oracle Restart in 11gR2

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 :-)

I continued to create a Primary Database & a Physical Standby Database pretty much in the same way (on the command line) as described on the Whitepaper 11g Data Guard on the command line on my Downloads page – of course, the directories must be remapped to the 11gR2 ORACLE_HOME etc., but almost identically else. I also created the Data Guard Broker Configuration in the same way as in the Whitepaper described. Now Oracle Restart kicks in:

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

Thes service is now up and running:

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-SEP-2010 08:15:36
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=uhesse)(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 0 hr. 35 min. 8 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 "prima", status READY, has 1 handler(s) for this service...
The command completed successfully

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 (Do not 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? That is even reflected in v$session:

SYSTEM@prod > select username,FAILED_OVER,
              FAILOVER_METHOD,FAILOVER_TYPE from v$session;
USERNAME                       FAI FAILOVER_M FAILOVER_TYPE
------------------------------ --- ---------- -------------
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
PUBLIC                         NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
 NO  NONE       NONE
SYSTEM                         YES BASIC      SELECT
 NO  NONE       NONE
31 rows selected.

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 “indestructable” 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.

I must admit that I am really enthusiastic about these new features in 11gR2 and how smooth it all fits together especially on the Data Guard part. Kudos to our Developement Teams!

About these ads

, , ,

  1. #1 by emre baransel on September 7, 2010 - 08:56

    Restart is a useful feature !! Glad to be compatible with dataguard :)
    Thanks Uwe!!

  2. #2 by Uwe Hesse on September 7, 2010 - 13:39

    You’re welcome :-) Thank you for your nice feedback!

  3. #3 by Stéphane Fromholtz on 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. #4 by Uwe Hesse on September 20, 2010 - 09:49

    Stéphane,
    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. #5 by Stéphane Fromholtz on 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. #6 by TGASCARD on October 13, 2010 - 16:48

    Hi Uwe,

    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.

    Thierry

  7. #7 by Kotesh on October 15, 2010 - 15:37

    Uwe,
    what is the command to add logical standby to olr. Is it srvctl add database -db_name -r logical_standby -s mount

    Regards,
    Kotesh

  8. #8 by monto on October 19, 2010 - 18:52

    Uwe,

    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.

    Thanks

  9. #9 by Uwe Hesse on October 25, 2010 - 14:08

    Thierry,
    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. #10 by Uwe Hesse on October 25, 2010 - 14:24

    Kotesh,
    yes
    (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. #11 by Uwe Hesse on October 25, 2010 - 14:37

    Monto,
    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. #12 by tanos on May 13, 2011 - 15:36

    Hello
    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?
    NAME=ora.mg.db
    TYPE=ora.database.type
    ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r–
    ACTION_FAILURE_TEMPLATE=
    ACTION_SCRIPT=
    ACTIVE_PLACEMENT=1
    AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
    AUTO_START=restore
    CARDINALITY=%CRS_SERVER_POOL_SIZE%
    CHECK_INTERVAL=1
    CHECK_TIMEOUT=600
    CLUSTER_DATABASE=true
    DB_UNIQUE_NAME=MG
    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%)
    DEGREE=1
    DESCRIPTION=Oracle Database resource
    ENABLED=1
    FAILOVER_DELAY=0
    FAILURE_INTERVAL=60
    FAILURE_THRESHOLD=1
    GEN_AUDIT_FILE_DEST=/u01/app/oracle/admin/MG/adump
    GEN_USR_ORA_INST_NAME=
    GEN_USR_ORA_INST_NAME@SERVERNAME(aix_rac1)=MG
    HOSTING_MEMBERS=
    INSTANCE_FAILOVER=0
    LOAD=1
    LOGGING_LEVEL=1
    MANAGEMENT_POLICY=AUTOMATIC
    NLS_LANG=
    NOT_RESTARTING_TEMPLATE=
    OFFLINE_CHECK_INTERVAL=0
    ORACLE_HOME=/u01/app/oracle/11.2.0
    PLACEMENT=restricted
    PROFILE_CHANGE_TEMPLATE=
    RESTART_ATTEMPTS=2
    ROLE=PRIMARY
    SCRIPT_TIMEOUT=60
    SERVER_POOLS=ora.MG
    SPFILE=
    START_DEPENDENCIES=weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) hard(ora.DATA.dg,ora.FRA.dg)
    START_TIMEOUT=600
    STATE_CHANGE_TEMPLATE=
    STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)
    STOP_TIMEOUT=600
    UPTIME_THRESHOLD=1h
    USR_ORA_DB_NAME=
    USR_ORA_DOMAIN=
    USR_ORA_ENV=
    USR_ORA_FLAGS=
    USR_ORA_INST_NAME=
    USR_ORA_OPEN_MODE=open
    USR_ORA_OPI=false
    USR_ORA_STOP_MODE=immediate
    VERSION=11.2.0.1.0

  13. #13 by Uwe Hesse on May 14, 2011 - 20:58

    Tanos,
    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. #14 by Sigrid Keydana on October 20, 2011 - 15:15

    Hi Uwe,

    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…

    Many thanks,
    Sigrid

  15. #15 by Uwe Hesse on October 20, 2011 - 16:52

    Sigrid,
    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. #16 by Sigrid Keydana on October 27, 2011 - 10:06

    Hi Uwe,
    many thanks for the answer! This is strange however, because on our system (which is 11.2.0.2.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 :-;
    Ciao
    Sigrid

  17. #17 by wendy on January 1, 2012 - 17:05

    Hi, Uwe:

    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. #18 by Uwe Hesse on January 1, 2012 - 17:11

    Hi Wendy,
    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. #19 by wendy on January 1, 2012 - 17:40

    So is it possible I can configure ORacle Restart while keeping the dbms_services and triggers?

  20. #20 by Uwe Hesse on January 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.
    So:
    srvctl add database
    but not
    srvctl add service
    when you want to keep the trigger & dbms_service configuration.

  21. #21 by Wendy on January 2, 2012 - 04:58

    ok, Thanks. Have a happy new year.

  22. #22 by Sung on March 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. #23 by Uwe Hesse on March 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:
    http://docs.oracle.com/cd/E11882_01/rac.112/e16795/srvctladmin.htm#i1008562

  24. #24 by Sung on March 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.

    Thanks.

  25. #25 by Uwe Hesse on March 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. #26 by mitchell.loren@viewpointe.com on June 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:
    LISTENER_XPT =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = colorado.localdomain)(PORT = 1523))
    )
    )

    SID_LIST_LISTENER_XPT =
    (SID_LIST =
    (SID_DESC =
    (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.

    Thanks.

  27. #27 by Uwe Hesse on June 14, 2012 - 12:04

    Mitchell, you need to point to the non-standard port number with the initialization parameter LOCAL_LISTENER then:
    http://docs.oracle.com/cd/E11882_01/network.112/e10836/listenercfg.htm#NETAG1154

  28. #28 by mitchell.loren@viewpointe.com on June 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. #29 by Uwe Hesse on June 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. #30 by mitchell.loren@viewpointe.com on June 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. #31 by mark on June 27, 2012 - 15:08

    Hello Uwe

    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
    –snip–
    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.

    Thanks
    Mark

  32. #32 by Buhari on August 8, 2012 - 20:13

    Hi Uwe

    Great blog!

    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?

    Thanks
    Buhari

  33. #33 by Uwe Hesse on 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. #34 by Pavol Babel on July 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. #35 by mark on July 11, 2013 - 10:30

    Pavol

    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.

    thanks
    Mark

  36. #36 by Uwe Hesse on July 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 11.2.0.3? Must check that once again…

  37. #37 by Mark on July 30, 2013 - 10:54

    Hello Uwe

    I’m sorry to say it isn’t fixed in 11.2.0.3 – we are using that and it definitely still happens.

    thanks
    Mark

  38. #38 by Uwe Hesse on August 13, 2013 - 09:44

    Mark, thank you for keeping us informed about that nasty issue! Will keep an eye on it.

  39. #39 by Marc Vandermeiren (Mata) on September 18, 2013 - 18:08

    Hi Uwe , Mark , Pavol

    this is indeed a great post ,

    we have the same problem in 11.2.0.3 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.

    example :

    CREATE OR REPLACE TRIGGER avoid_active_dataguard
    AFTER STARTUP ON DATABASE
    BEGIN
    /* 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’
    THEN
    EXECUTE IMMEDIATE ‘ALTER DATABASE CLOSE';
    END IF;
    END;
    /

    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. #40 by Uwe Hesse on 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. #41 by Marc Vandermeiren (Mata) on 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. #42 by Pavol BabelPavol on 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. #43 by Uwe Hesse on October 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 :-)

  1. Merry Christmas & A Happy New Year 2012! « The Oracle Instructor
  2. Merry Christmas & A Happy New Year 2012! | Oracle Administrators Blog - by Aman Sood
  3. Top 10 postings in 2012 « The Oracle Instructor

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,604 other followers

%d bloggers like this: