Connect Time Failover & Transparent Application Failover for Data Guard

I was giving a 10g Data Guard course this week in Düsseldorf, demonstrating amongst others the possibility to configure Transparent Application Failover (TAF) for Data Guard. I always try to keep things as simple as seriously possible, in order to achieve an easy and good understanding of what I like to explain. Later on, things are getting complex by themselves soon enough :-)

In my simple scenario, I have one Primary Database (prima) and one Physical Standby Database (physt). On my downloads page, you may find an installation guide for that scenario for 10g and for 11g. After a switchover or after a failover, the primary is going to be physt. The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = myapp)
 )
 )

HostA is the host on which prima runs, HostB has physt running.

Second, we take care that the service myapp is offered only at the right database – only on the primary.Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin
 dbms_service.create_service('myapp','myapp');
end;
/
begin
 DBMS_SERVICE.START_SERVICE('myapp');
end;
/

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database
declare
 v_role varchar(30);
begin
 select database_role into v_role from v$database;
 if v_role = 'PRIMARY' then
 DBMS_SERVICE.START_SERVICE('myapp');
 else
 DBMS_SERVICE.STOP_SERVICE('myapp');
 end if;
end;
/

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at physt without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to prima are disconnected if a switchover or failover to physt takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin
 dbms_service.modify_service
 ('myapp',
 FAILOVER_METHOD => 'BASIC',
 FAILOVER_TYPE => 'SELECT',
 FAILOVER_RETRIES => 200,
 FAILOVER_DELAY => 1);
end;
/

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption (about 20 seconds, in a typical case). Only if sessions have open transactions during the failover/switchover, they will receive error messages (“transaction must roll back”) after they try commit then.

I use to demonstrate that with a select on a table with 100000 rows that starts on the primary. Then I kill the SMON of that primary and the select stops at row 30000 something, waits a couple of seconds (maximal 200, with the above settings) and then continues on the new primary after the failover, fetching exactly the 100000 rows! That is always quite impressive and shows how robust Oracle Databases – especially combined with Data Guard – are :-)

About these ads

  1. #1 by Aman.... on August 19, 2009 - 14:52

    Very nice Uwe(as always) :-) .

    regards
    Aman….

  2. #2 by Anand on August 19, 2009 - 21:13

    Seriously simple :)

    Regards,
    Anand

  3. #3 by Surachart Opun on August 20, 2009 - 05:39

    Excellence!

    Thank You.

  4. #4 by Uwe Hesse on August 20, 2009 - 15:19

    Thank you, Aman, Anand & Surachart! Your feedback is very much appreciated :-)

  5. #5 by Chris Adkin on December 8, 2009 - 12:50

    A very good article. One query though, with 11g a physical standby can be open, I’m not sure that this is the case for 10g. Therefore does your comment about the database trigger and the db_role_change event also apply to physical standbys with this caveat ?

  6. #6 by Uwe Hesse on December 14, 2009 - 14:53

    Chris,

    I have just tested on 11g: The failover to a read-only opened physical standby database triggers the after startup on database trigger and therefore starts the service. It is an 11g new feature that the read-only opened physical standby can be queried (Real-Time Query).

  7. #7 by Bhavik Desai on January 22, 2010 - 09:21

    Hi Uwe Hesse,

    This is very cool…I successfully implemented TAF for my 11g FSFO and its working perfectel alright.

    However, can i request you to give internal insight on how SELECT statement fail over to standby (new primary) when fail-over occurs. I am curious to know how would SESSION STATE information is propogated to new primary after fail-over?
    I have seen that SELECT is paused for a while and once DG broker opens new primary, trigger gets populated and SELECT continued from the point where it was hanged. It was not a RE-EXECUTION of select.

    I also tried to do fail-over of DML and noticed that i got ‘CONNECT LOST’ msg.
    I thought in DG FSFO configuration (MAXAVAILIBILITY), DML fail-over should also occur and transaction shold get rolled back / commited on new primary as REDO might have propogated from old primary to new primary.

    Can i ask for your kind help here ?

    Regards,
    Bhavik Desai

  8. #8 by Uwe Hesse on January 22, 2010 - 09:48

    Incidentally, I just answered your question on OTN :-)
    Look at
    http://forums.oracle.com/forums/thread.jspa?messageID=4043490#4043490

  9. #9 by Stéphane Fromholtz on March 17, 2010 - 09:28

    Hi,

    I did configure a Physical Standby on a 10.2.0.3 Db, switchover works fine between the two servers but at the client side I keep getting the ORA-01033 meaning that on the client side I connect to the Physical Standby.

    I changed the trigger to log to the alert log and register the service ABC, it still does not work.

    create or replace trigger ABC_SERVICE_TRIGGER after startup on database
    declare
    v_role varchar2(16);
    begin
    select database_role into v_role from v$database;
    if v_role = ‘PRIMARY’ then
    DBMS_SYSTEM.ksdwrt(2,to_char(sysdate)|| ‘ Setting Active Service ABC after startup of database …’);
    execute immediate ‘alter system set service_names=”ABC” scope=both’;
    execute immediate ‘alter system register’;
    DBMS_SERVICE.START_SERVICE(‘ABC’);
    else
    DBMS_SYSTEM.ksdwrt(2,to_char(sysdate)|| ‘ Setting Active Service ABCSTDBY after startup of database …’);
    execute immediate ‘alter system set service_names=”ABCSTDBY” scope=both’;
    execute immediate ‘alter system register’;
    DBMS_SERVICE.STOP_SERVICE(‘ABC’);
    end if;
    end;
    /

    The service ABC (name changed) should be active only on the Primary site, but when I stop and restart the listener on the Standby server, I can see that the service ABC is present at the standby site.

    I also searched a command (in DBMS_SERVICE package) to unregister a service from the listener, also in lsnrctl itsefl but I don’t think this is possible.

    Is it normal to have the service I want on Primarw already present on the Standby Db after the listener startup ? Without this service on the standby side I think the client would not try to connect on the Db and get the ORA-01033 msg because the service is not present.

    Also is there a minimal client version mandatory for Connect Time Failover to work ?

    Note that I did keep the default listener entry and added a listener_prim with the db_unique_name_DGMGRL for the Broker.

    If you could help me understand what the correct setup is to offer the service only on the Primary Db, and have the listener only registering the service on Primary site, you would help me a lot!

    By the way I found the article very interesting but only missing a few configuration details about the listener and services setup.

    Regards,
    Stéphane

  10. #10 by Uwe Hesse on March 17, 2010 - 10:09

    Stéphane,
    you may understand that I cannot troubleshoot your Data Guard setup. Please contact Oracle Support for that – they are very good in those tings and also get paid for it :-)
    A few general comments:
    You should not need to use DBMS_SYSTEM or
    ALTER SYSTEM SET SERVICE_NAMES in your trigger. It may very well cause the problems you describe. Also, there is no need to configure multiple listeners on a singe server – it just makes the setup unneccessarily complex and could also be responsible for the described problems.
    The trigger should work exactly as in my demonstration without any additional configuration. I did not disclose my listener.ora because it is nothing special – at least not for the connect time failover to work:
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = edd2r10p7)(PORT = 1521))
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = prima_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    (SID_NAME = prima)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = physt_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    (SID_NAME = physt)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = logst_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    (SID_NAME = logst)
    )
    )

    The SID_LIST part is just for the DG Broker, not for the connect time failover.

  11. #11 by Stéphane Fromholtz on March 17, 2010 - 10:37

    Hi,

    thanks for your comments, I perfectly understand your point but you helped me understand a few things. I’ll remove the listeners I created (LISTENER_PRIM and LISTENER_STDBY) and will keep a simple one.

    I saw the hack of changing the service_names in a trigger in Note 316740.1, but I will avoid this now. I’ll go on with listener.ora as simple as the one you showed, and setup a simple TNS alias with service_name. It should go better with that setup.

    Thanks again for your feedback
    Stéphane

  12. #12 by Uwe Hesse on March 17, 2010 - 11:11

    Stéphane,
    you’re welcome :-)
    Good luck with your implementation!

  13. #13 by Stéphane Fromholtz on March 19, 2010 - 08:17

    Hi,

    a small update to let you know that everything works as expected now, CTF and TAF have been tested and they worked fine with the service trigger.

    Regards
    Stéphane

  14. #14 by Uwe Hesse on March 19, 2010 - 09:26

    Stéphane,
    thank you for the update! It’s nice to hear that the configuration is actually working as described. Whishing you all the best for your further work :-)

  15. #15 by Satish on March 7, 2011 - 04:58

    Hi Uwe,

    Your tips to problems involving Oracle Databases are extremely pragmatic. Your blog is very educative and informative. I came to know most of the nuances of the Dataguard from your site. I Salute the Teacher in you.

    Thanks
    Satish

  16. #16 by Satish on March 11, 2011 - 10:36

    Hi,

    I have a following scenario:
    3 Servers running RHEL5
    Server1– Hosting a Primary database (Oracle 11g R1) — (10.1.40.19)
    Server2 –Hosting a Physical Standby database (Oracle 11gR1) — (10.1.140.19)
    Server3– Having a Oracle Client installation with Admin option (Oracle 11gR1) and runs the Observer on it. (10.1.140.23)
    The network N1 is used between the Primary and the Standby to ship logs.
    The network N1 is also used by the Observer to communicate with the Primary
    The network N2 is used by the Observer to communicate with the Standby.
    The Fast Start Automatic Failover is working properly and Dataguard configuration is also fine.
    With this configuration, I have a question.

    If the Physical Standy (10.1.140.19) crashes,and simultaneously the network N1 is broken down.
    Then the Primary database will lose contact with the Observer and the Standby simultaneously.
    So, after the duration of the Failover Threshold say (30 Secs) , the Primary would think that the Standby would be promoted. In that event will the Primary Shutdown automatically?

  17. #17 by Uwe Hesse on March 14, 2011 - 16:45

    Satish,
    yes I think so. The Primary will shutdown by default in this case because it assumes that the Standby got failed over to. You could disable the automatic shutdown of the primary to prevent this, though.

    And thank you for your very nice feedback in the previous comment!

  18. #18 by OracleRaj on May 7, 2011 - 10:23

    I am having issue, when I added “failover=yes” at client tnsnames.ora file….The client cannnot access the database

    primary—— machine: test9 sid: primary
    standby——machine: standby sid: standby
    —————————-
    primary machine tnsnames.ora
    primary =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = test9)
    (Port = 1521)
    )
    )
    (CONNECT_DATA = (SID = primary)
    )
    )
    standby =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = standby)
    (Port = 1521)
    )
    )
    (CONNECT_DATA = (SID = STANDBY)
    )
    )

    ———————————
    standby tnsnames.ora

    standby =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = standby)
    (Port = 1521)
    )
    )
    (CONNECT_DATA = (SID = STANDBY)
    )
    )

    primary =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = test9 )
    (Port = 1521)
    )
    )
    (CONNECT_DATA = (SID = primary)
    )
    )

    —————————–
    CLIENT TNSNAMES.ora

    ——————————————————————————–
    prim.world=
    (DESCRIPTION_LIST=
    (FAILOVER=true)
    (LOAD_BALANCE=no)
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=TCP)
    (HOST= test9)
    (PORT=1521)
    )
    (CONNECT_DATA=
    (SERVER=dedicated)
    (SID=primary)
    )
    )
    (DESCRIPTION=
    (ADDRESS=
    (PROTOCOL=TCP)
    (HOST=standby)
    (PORT=1521)
    )
    (CONNECT_DATA=
    (SERVER=dedicated)
    (SID=standby)
    )
    )
    )

    ————
    I have just performed switchover operation, standby is now up as Primary, and logs are being
    applied at standby (old primary).

    When I am trying to connect I am getting below error.

    Enter user-name: erp/erp@prim.world
    ERROR:
    ORA-01033: ORACLE initialization or shutdown in progress
    ————————————–

    I have created new entry in client’s TNSNAMES.ora for standby database to check whether it’s
    database issues or TNS issue.

    —————————-
    standby.world =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS =
    (PROTOCOL = TCP)
    (Host = standby)
    (Port = 1521)
    )
    )
    (CONNECT_DATA = (SID = standby)
    )
    )
    ————————-
    Enter user-name: erp/erp@standby.world

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    With the Partitioning, OLAP and Data Mining options

    SQL>

    I think it’s the prim.world TNS entry issue.

    Note: Did you create same service name (myapp) on both primary & standby database ?

    Regards

  19. #19 by Uwe Hesse on May 10, 2011 - 12:26

    You may not use SID for client connections that are supposed to do connect time failover but SERVICE_NAME instead. Look at the myapp connect descriptor in the posting above. The service myapp is only created on the Primary and then reaches the Standby through Redo Apply.

  20. #20 by OracleRaj on May 10, 2011 - 15:00

    Sir..I have tried with SERVICE_NAME too but again same problem….after switchover standby to primary database, the clients get below error.

    ORA-01033: ORACLE initialization or shutdown in progress

    ————————————-
    One more thing I have not created triggers and packages as you have mentioned above… Do I need to create as you have created..??

  21. #21 by OracleRaj on May 11, 2011 - 13:01

    Sir Uwe Hesse ..

    I have created the same, service, package & trigger it worked for me at client side.. I tested for both switchover & failover. Its perfectly working fine at client connection.

    I didnt create below procedure on live..

    begin
    dbms_service.modify_service
    (‘myapp’,
    FAILOVER_METHOD => ‘BASIC’,
    FAILOVER_TYPE => ‘SELECT’,
    FAILOVER_RETRIES => 200,
    FAILOVER_DELAY => 1);
    end;
    /

    Thanks alot sir.

  22. #22 by Uwe Hesse on May 11, 2011 - 16:15

    You’re welcome :-) Good to hear that you succeeded with it.

  23. #23 by Linda on September 22, 2011 - 21:56

    Hi Uwe,

    I have problem get the TAF on a physical standby working.

    I created the client tnsname to use service name instead of SID; create a service called ‘myapp’ on the primary, and also created a trigger on primary to fail over existing primary connections. Before I test the TAF, I tried to use the myapp alias to connect to the primary database, but I get ORA-12514 error.

    I checked that ‘my_app’ service is running on primary, the listener file didn’t mention the service name ‘myapp’ in any way, how does the listener know my service_name?

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = …
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = mysid)
    (ORACLE_HOME = …)
    (SID_NAME = mysid)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pirmhost)(PORT = 1521))
    )
    )
    )

    Could you give me some hint on this?

    Thanks a lot!

    Linda

  24. #24 by Uwe Hesse on September 23, 2011 - 08:33

    Linda,
    your Database is supposed to register the myapp service automatically at the listener, if the listener port is 1521 – else you must set LOCAL_LISTENER to point to the port. You may try “alter system register;” to triger the dynamic registration manually. Then say “lsnrctl status” to review whether the listener shows the service myapp as ready.
    That is all standard Oracle Net configuration, described in much more detail here:
    http://www.oracle.com/pls/db112/to_toc?pathname=network.112%2Fe10836%2Ftoc.htm&remark=portal+%28Books%29

  25. #25 by Linda on September 23, 2011 - 16:11

    and the following query from the primary database returns ‘myapp’ for the service name.

    select name, value from v$parameter
    where name like ‘service%’

    Thanks a lot for any hint.

    Linda

  26. #26 by Linda on September 23, 2011 - 16:14

    ‘lsnrctl status’ does show the following:

    Service “PLSExtProc” has 1 instance(s).
    Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Service “myapp.db005″ has 1 instance(s).
    ….
    I really appreciate your time.

    Linda

  27. #27 by Linda on September 23, 2011 - 16:22

    Hi Uwe,
    I think I know the problem, I have ‘service_name=myapp’ in my tnsnames.ora, once I changed it to include the host name ‘myapp.db005′.’ it connects fine. v$parameter from the primary database shows the service_name as ‘myapp , when the primary fail over to another host, let’s say db001, will the TAF work? how can I make the service name not host dependent?

    Thanks.

    Linda

  28. #28 by Uwe Hesse on September 23, 2011 - 17:26

    Linda, I am sorry but I cannot troubleshoot your Oracle Net configuration, sitting in the airport without a Database at hand. Looks like you use a Domain in a somewhat weird way.

    You may contact Oracle Support at http://metalink.oracle.com or try the OTN Database forum at http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0

    They don’t care for weekends even :-)

    Good luck!

  29. #29 by Linda on September 23, 2011 - 18:16

    I really appreciate your time, I will dig into the the net configuration and see how things work out.

    Thanks again!

    Linda

  30. #30 by Linda on September 23, 2011 - 18:21

    Hi Uwe,

    Just to let you know that for all our database servers, I can see there are two services associate with each oracle instance, for example, for oracle SID1, I see service SID1, and SID1.hostname as the services.

    But for the service ‘myapp’ I created, I can see only one service entry (myapp.hostname) from ‘lsnrctl status’ output, I was expecting I might see an item ‘myapp’ from the ‘lsnrctl status’ output.

    Thanks.

    Linda

  31. #31 by Linda on September 23, 2011 - 21:46

    Hi Uwe,

    The TAF now works with my tnsname change to:

    myapp =
    (DESCRIPTION_LIST =
    (FAILOVER = TRUE)
    (LOAD_BALANCE = FALSE)
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = primdb)(PORT = 1521))
    (CONNECT_DATA =
    (SERVICE_NAME = myapp.primdb)
    (SERVER = DEDICATED)
    )
    )
    (DESCRIPTION =
    (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = stddb)(PORT = 1521))
    (CONNECT_DATA =
    (SERVICE_NAME = myapp.stddb)
    (SERVER = DEDICATED)
    )
    )
    )

    Thanks and have a good weekend.

    Linda

  32. #32 by Uwe Hesse on September 26, 2011 - 09:52

    I’m glad to see that you managed to get it to work, Linda :-)

  33. #33 by Nafey on October 5, 2011 - 00:31

    Uwe,
    Thanks for a lovely post. I am trying to look for the reference in the Oracle Documentation, but could not find the reference to the client connect-time failover. I would appreciate if you could guide me to the documentation where to find the refernce?

    Cheers
    Nafey

  34. #34 by Uwe Hesse on October 5, 2011 - 08:32

    Nafey,
    you’re welcome :-)
    The documentation is not so verbose in this area. Look here for TAF:
    http://download.oracle.com/docs/cd/E11882_01/network.112/e10836/advcfg.htm#i473297

    An excellent Whitepaper about Client Failover Best Practices for Data Guard 11g Release 2:
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf

  35. #35 by Wendy Yu on December 8, 2011 - 05:08

    hi, Mr. Hesse:

    I have a question. I followed your blog and created trigger and service and it worked perfectly for my 11gr2 primary/standby database.

    Now my question is: my physical standby database is a read -only database, and some of our users need to connect to it to do read and report. How do I configure for their connection on their client side to this read-only standby db?
    Thank you very much for your time in advance.

    Wendy

  36. #36 by Uwe Hesse on December 8, 2011 - 11:33

    Hi Wendy,

    you need to create a new service & modify the trigger accordingly. I have shown that in the posting
    http://uhesse.wordpress.com/2010/11/05/real-time-query-for-read-mostly-applications/
    in the lower part, starting with the sentence: “Also, we need a connect descriptor that always connects Scott to the Physical Standby regardless of Role Changes.”

  37. #37 by Wendy Yu on December 8, 2011 - 16:09

    Thank you Mr. Hesse on your quick response. This sure solved my concern. You are guru on those MAA. I might ask you more questions when I encounter some.

    Thanks for help.

    Wendy

  38. #38 by Uwe Hesse on December 8, 2011 - 17:20

    Thank YOU, Wendy, for stopping by and the nice feedback! Mr. Hesse is my dad – you may call me by my first name (Uwe) if you like. You may also ask as much as you like; I may not answer, though :-)
    But I recommend the OTN Forum for Data Guard, because the chances that someone WILL answer your questions (faster than I could) are high:
    http://forums.oracle.com/forums/forum.jspa?forumID=849&start=0

  39. #39 by Wendy on December 11, 2011 - 05:47

    hi, Uwe, I have a question puzzled me and I am not sure how to get answers. and I googled everywhere and could not find an answer. So I would like to ask you here, maybe you can give me answer. I have build a qa environment as 11.2.0.2.4 RAC on 2 nodes on REDHAT 5 linux. The storage is EMC power path. The storage we used Oracle asm. The sa provided /dev/emcpower* pathes, and I configured oracleasm, createdisks and everything was fine. I installed Grid Infrustracture, installed database and created a database. This all happened 2-3 weeks ago, then I worked on a 2-node RAC standby for the above QA environment. I did not even look back to the server. I think at that timeframe, SA rebooted the sever, and then some of the /dev/emcpower* path got changed, and all my oracleasm disks are gone. I checked /dev/oracleasm/disks, there is nothing there.

    How to resolve this issue? How to resolve this storage disk name changes after oracleasm disks created?

    Thanks,

    Wendy

  40. #40 by Wendy on December 14, 2011 - 17:58

    Our SA have this fixed. The storage disks are dynamic across reboots.

    They had to do something to map the dynamic disks to static alias, I basically have to rebuild the whole thing from scratch.

  41. #41 by Uwe Hesse on December 15, 2011 - 09:33

    Wendy,
    sorry about that! But thank you for sharing this unpleasent issue here!

  42. #42 by Neeraj on January 31, 2012 - 12:52

    Excellent Post … This is what i am searching for long time and finally got it. I am going to post the same contain and reference your blogs ….

    All the very best Mr. Uwe Hesse ….

  43. #43 by Uwe Hesse on January 31, 2012 - 17:02

    Neeraj, thank you for the nice feedback and also for referring to my site! Very much appreciated :-)

  44. #44 by Ramya on June 6, 2012 - 10:40

    Nice post. I followed the same steps and works great..

  45. #45 by Uwe Hesse on June 6, 2012 - 11:13

    Thank you for leaving the comment about it – glad you found it useful :)

  46. #46 by Rama on July 18, 2012 - 20:51

    Uwe,

    this is my client tns entry, for some reason i keep getting ORA-01033: ORACLE initialization or shutdown in progress at client side after i did the switchover

    myApp =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primhost)(PORT = 1944))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dghost)(PORT = 1946))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = dbname)
    )
    )

    any help is appreciated

  47. #47 by Rama on July 18, 2012 - 20:53

    the same TNS entries work from client side if i don’t do switchover,

  48. #48 by Uwe Hesse on July 19, 2012 - 21:22

    Rama, you describe a typical problem that happens when the generic DB service instead of an application service like in above post is used. You cannot avoid the generıc service being offered also on the non Primary. Use an application service.

  49. #49 by Rama on July 19, 2012 - 21:51

    Uwe,
    Not sure i got what you mean to say here..i already have the service name defined in the spfile
    service_names(which is same as db name), so u want me to have some other service name(not db name) and try it?

  50. #50 by Uwe Hesse on July 21, 2012 - 16:49

    It ıs really sımple: Do ıt lıke ın thıs post (when you have Oracle Restart ınstalled) or lıke ın thıs post http://uhesse.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/ descrıbed and ıt wıll work. Do somethıng else and ıt wıll not work – somethıng else refers especıally to the usage of the generıc DB servıce :)

  51. #51 by Rama on July 21, 2012 - 17:50

    Thanks Uwe, it did work..thanks for your help

  52. #52 by Uwe Hesse on July 22, 2012 - 09:27

    You’re welcome, Rama :)

  53. #53 by Wahyu on July 26, 2012 - 10:54

    Dear Uwe,
    I’am try it success with desktop application, for web app still need relogin again. But with with app that work in full memory state still fail using this setting.
    Thanks,
    Wahyu

  54. #54 by Uwe Hesse on July 30, 2012 - 17:19

    Consider to contact Oracle Support then – or try the Data Guard forum that I pointed to in the Links List up right.

  55. #55 by john on March 15, 2013 - 20:44

    did you ever have to set remote_listener to get this to work?

  56. #56 by Uwe Hesse on March 16, 2013 - 11:07

    John, no REMOTE_LISTENER is not relevant for Data Guard (as long as you are not combining it with RAC, where it points to the other listeners inside the cluster). LOCAL_LISTENER will be needed, though, if your listener port is not 1521. It enables PMON to tell the listener about the services.

  57. #57 by Mujib on May 6, 2013 - 12:29

    Hi Uwe,

    In my parameter files for both primary and standby I have service_names both different for primary and standby consider as prod for primary and stnby for standby database. So which service_name I should give in your myapp entry mentioned below

    MYAPP =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = myapp)
    )
    )

    only db_name is common for me in both parameter files i.e. prod.

    from primary

    *.db_name=’prod’
    *.db_unique_name=’prod’
    *.service_names=’prod’
    *.instance_name=’prod’

    from physical standby

    *.db_name=’prod’
    *.db_unique_name=’stnby’
    *.service_names=’stnby’
    *.instance_name=’stnby’

    Also listeners on both Primary and Standby hosts are having different SIDs i.e prod and stnby

    from primary

    (SID_DESC =
    (GLOBAL_DBNAME = prod)
    (ORACLE_HOME = /u01/oradha/product/11.2.0)
    (SID_NAME = prod)
    )
    ————————————————————————–
    from physical standby

    (SID_DESC =
    (GLOBAL_DBNAME = stnby)
    (ORACLE_HOME = /u01/oradha/product/11.2.0)
    (SID_NAME = stnby)
    )

    Please clarify what tnsentry we need to use for seamless connection to database which may be Primary or Physical Standby.

    Regards,
    Mujib Dave

  58. #58 by Hernan on February 10, 2014 - 16:13

    ohhh great Its an excellent post !

  59. #59 by Hernan on February 11, 2014 - 20:51

    I cant stop Services with DBMS_SERVICE.STOP_SERVICE. nothing happens…
    I use the default listener in port 1521.
    The pl/sql runs fine but when I goes to lsnrctl I see the service…..
    I dont know what is wrong….
    Thanks in advanced…. excellent post !

  60. #60 by Riya on February 27, 2014 - 16:28

    Hi Uwe…

    The exact same problem above they mentioned after switchover, ORA-01033: ORACLE initialization or shutdown in progress error while trying to connect from application. but primary and standby are in sync.

    Even am suspecting the issue because of the service name. how to check the existing service options.

  61. #61 by Uwe Hesse on March 5, 2014 - 10:44

    Riya, I’m not going to troubleshoot your configuration – please contact our support for that. They are so good with it that they can even make a living from it :-)

  62. #62 by Susanne on March 5, 2014 - 15:46

    Hi Uwe,

    my clients always connect to the first machine in the client tnsnames.ora, also when this machine is standby.

    I configured the service and the trigger. But for local_listener we used antoher port. Is that a Problem? We use Port 1600 for Local_Listener?

    My SID’s are the same on both machine DBPORT and my service also calles DBPORT. Is that a problem?

    Thanks for help!

  1. Data Guard & Oracle Restart in 11gR2 « The Oracle Instructor
  2. Why you should use Application Services with your Oracle Database « 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,429 other followers

%d bloggers like this: