3 Replies Latest reply: Feb 7, 2014 4:55 PM by Svetoslav Gyurov RSS

    Oracle Dataguard Switchover and DNS

    Hernan Fernandez

      Hi, few days ago we planned a switchover in a dataguard 10g configuration (pasive dataguard)  between 2 servers.

       

      primary:                APP   in europe.com

      secondary:            APP1 in asia.com

       

      Both databases was Oracle 10.2.0.3 in Redhat (single instance)

       

      Everything goes ok with the databases and the switchover but what happend with the web based applications ?

      I have a lot of PHP web applicattion that have a connection string with oci and oracle client, all of them have the connection like user/pass@dbname where dbname is defined in the local tnsnames.ora with the DNS names to the host.

      (All of them have xxx/xxx@APP)  , and APP was ( host:europe.com and SID=APP)

       

      europe.com was ip 1.1.1.1

      asia.com was ip 2.2.2.2

       

      My primary instance was APP (sid name) and the standby was APP1  (sid name) but what happend when I switchover the dataguard ?

      yes, as we have a lot of applications and we don´t change the string connection , all of them will try to connect to the new physical standby database APP who is in europe.com. (and the new primary is APP1 in asia.com)

       

      Well, first of all I must change the SID in the standby database (identical to the primary database ) for the string connection so for that I : (so development are dont want to change the string connection...)

      1) shutdown immediate the standby database

      2) remove the dataguard broker configuration,

      3) change the instance name in the spfile  from APP1 to APP.

      4) change db name in the spfile  from APP1 to APP.

      5) change the fal_client parameter from (INSTANCE_NAME=APP1) to  (INSTANCE_NAME=APP)

      6) change the orapwd file to APP from APP1

      7) change the bash_profile configuration to APP and re start the standby database.

      8) Check the listener.ora, so the new listen service is APP and no APP1

      8) re config the dataguard broker configuration

       

      Ok, everything works fine and we have the primary and the standby database with the same SID name.

       

      But what happend with the dns servers ? because the web based applications will try to connect to APP but APP was defined as europe.com and I want to redirect to asia.com as primary database.

      Well , first of all I change all of my parameters file and dataguard broker configuration to IP and no dns, then I made a procedure guide for switchover so when we made that, first we must switchover the databases and then the NET area must change the dns server so new dns europe.com point to ip 2.2.2.2

       

      After that everything works fine !!!

       

      So in conclusion, before a switchover with dataguard ( who was an excellent feature) check the string connection of the applications and take care of them) and if it is necesary, use the same SID for both (primary and standby databases )

       

       

      ( In Oracle 12c we have a lot of thing to use like application continuity for that )

       

      Regards,

      Hernan

        • 1. Re: Oracle Dataguard Switchover and DNS
          Svetoslav Gyurov

          Hi Herman,

           

          Maybe I'm getting it wrong, correct me if that's the case.

           

          Assuming you are using oracle client why not just use the built in mechanism for that purpose ? DNS has nothing to do with that but I see people still using it for that purpose - it's not just suitable for failovers, I don't like having my TTL of 60 seconds or less for my A records.

           

          I had a project for Internet banking HA where after switchover is initiated the application (Tomcat) was able within a minute to connect automatically to the standby database. First of all you should be using database service instead of SID, thus you may have the same service on the standby. What's more having a service you can guarantee that it won't be running on the standby until it becomes primary, otherwise you may find yourself in a funny situation. Second, why not using built in failover parameter of the connection string, this way you can list standby db(s) and in case your primary doesn't respond the oracle client will simply try to connect to the standby (looking for the same service). To make sure the standby database won't run the service at any time you can deploy trigger (AFTER startup) to check the database role, if it's "primary" it simply starts the database service.

           

          Regards,

          Sve

          • 2. Re: Oracle Dataguard Switchover and DNS
            Hernan Fernandez

            Of course Sve, you are right, There are many ways to do a switchover......

            My intention is to open a discussion about the different ways to make a switchover because one thing is the database alone and the other is the database and the applications in a company

            If you use failover config in the connection string you may need to check the database role with a trigger but in case you use an passive standby database (mounted) you must check the open mode (for "mounted" ).


            • 3. Re: Oracle Dataguard Switchover and DNS
              Svetoslav Gyurov

              Hi Herman,

               

              Indeed, it's all about application and how it will handle the failover. Yes the standby will always be in mount state (unless using ADG or opened read only). The trigger is created on the standby database and it will ONLY kick in if you change the database mode - the moment you initiate switchover or failover.

               

              Consider following scenario:

               

              You have a JAVA app deployed on Tomcat, the connection string in context.xml is this:

              jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1751)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.11)(PORT = 1521))(FAILOVER = on)(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MY_APP)))"

               

              The failover clause above is not mandatory as the service won't be available on the standby. The following trigger created on the standby will make sure the service is running only when the database role is primary:

              create or replace trigger my_app_service 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('my_app'); end if;

              end;

              /

               

              In 11g you have role based services so you can skip the trigger.

               

              So far your application will look for service_name MY_APP first on 192.168.1.11 and if it's not available then will failover to 192.168.2.11.

               

              In case you initiate switchover:

              1. You primary database will stop as well the service

              2. You standby database will start along with the service my_app

              3. Meanwhile the application will try to connect to either host

               

              As I said, in my case it took less than a minute for the application server to restart the connections and successfully connect to the standby database.

               

              Regards,

              Sve