1 2 Previous Next 16 Replies Latest reply: Jan 27, 2012 3:18 AM by 911566 RSS

    TNS  Names on dataguard setup.

    911566
      Hi,

      I would like to get details which is related to tns entries. Eg. My Primary database name is “PPRIM” and standby database name is “PSTAND” . Both the databases tns entries holding each other database details.

      Here is my exact question: I have given my primary database tns entries to application team to connect the database. As usual I have tested the switch over, now standby became a primary and primary became a standby. At this situation to connect application team to my new primary database have to change the tns entries at application team side to point new primary database or is there any other option do we have. Please help on this. Thanks in advance.

      Regards,
      Mahendran
        • 1. Re: TNS  Names on dataguard setup.
          orawiss
          Hello,
          Welcome to the forum,
          You can use Transparent Application Failover (TAF)
          • 2. Re: TNS  Names on dataguard setup.
            Shivananda Rao
            Well, normally whenever a switchover is performed, we change the tns entries.
            The application team will have to use the standby database entry (new primary database entry) to connect to the primary database after the switchover.
            • 3. Re: TNS  Names on dataguard setup.
              911566
              Thanks for your udpates... Would you please brief me. How the TAF help me here. My actual requirement is the client side/server side tns entry for connection that after switchover or failover without any interuption user can connect to database.
              • 4. Re: TNS  Names on dataguard setup.
                CKPT
                user10782193 wrote:
                Hi,

                I would like to get details which is related to tns entries. Eg. My Primary database name is “PPRIM” and standby database name is “PSTAND” . Both the databases tns entries holding each other database details.

                Here is my exact question: I have given my primary database tns entries to application team to connect the database. As usual I have tested the switch over, now standby became a primary and primary became a standby. At this situation to connect application team to my new primary database have to change the tns entries at application team side to point new primary database or is there any other option do we have. Please help on this. Thanks in advance.

                Regards,
                Mahendran
                If this is planned and going to perform switchover and well known, then Better way is to update new TNS entries to Application users.

                TAF you can go Still you can perform only select statements,
                Check this

                http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr2-client-failover-173305.pdf
                http://www.oracle.com/technetwork/database/app-failover-oracle-database-11g-173323.pdf

                For better understanding read Automating Site and Client Failover of 11g Dataguard Handbook by Larry Carpenter. :)

                Edited by: CKPT on Jan 17, 2012 8:12 PM
                • 5. Re: TNS  Names on dataguard setup.
                  orawiss
                  user10782193 wrote:
                  Thanks for your udpates... Would you please brief me. How the TAF help me here. My actual requirement is the client side/server side tns entry for connection that after switchover or failover without any interuption user can connect to database.
                  Please read Uwe 's article;

                  http://uhesse.wordpress.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/

                  Hope it helps,
                  Wissem
                  • 6. Re: TNS  Names on dataguard setup.
                    mseberg
                    Its fairly easy, something like this :

                    ernie =
                     (DESCRIPTION =
                        (ADDRESS_LIST =
                           (ADDRESS = (PROTOCOL = TCP)(HOST = Primary.host)(PORT = 1521))
                           (ADDRESS = (PROTOCOL = TCP)(HOST = Standby.host)(PORT = 1521))
                           )
                           (CONNECT_DATA =
                           (SERVICE_NAME = ernie)
                        )
                     )
                    The service 'ernie' needs to be created manually on the primary database.
                    BEGIN
                     
                       DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
                    
                    END;
                    /
                    After creating the service needs to be manually started.
                     BEGIN
                     
                       DBMS_SERVICE.START_SERVICE('ernie');
                     
                     END;
                     /
                    Once this is done several of the default parameters can now be set for 'ernie'.
                    BEGIN
                       DBMS_SERVICE.MODIFY_SERVICE
                       ('ernie',
                       FAILOVER_METHOD => 'BASIC',
                       FAILOVER_TYPE => 'SELECT',
                       FAILOVER_RETRIES => 200,
                       FAILOVER_DELAY => 1);
                    END;
                    /
                    Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.
                    CREATE TRIGGER CHECK_ERNIE_START 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('ernie');
                     ELSE
                     DBMS_SERVICE.STOP_SERVICE('ernie');
                     END IF;
                    END;
                    /
                    Once this is done use your "lsnrctl status" to check.

                    Believe it or not, "Ernie" is not mandatory.


                    Best Regards

                    mseberg
                    • 7. Re: TNS  Names on dataguard setup.
                      911566
                      Thanks all for the valuable updates. Do we have any other options instead of using TAF. Thanks in advance.
                      • 8. Re: TNS  Names on dataguard setup.
                        mseberg
                        This is what I tested after reading the Uwe Hesse Document.

                        Nothing else compares in my book and this works. Information from Uwe is always top shelf.


                        Best Regards

                        mseberg
                        • 9. Re: TNS  Names on dataguard setup.
                          911566
                          Thanks lot for the updates. Some one please help me, How the below tns entries works on dataguard setups.

                          The Oracle DR configurations on both the primary and standby servers are identical. Thus the alias TEST points to the primary server and STTEST points to the standby server on both environments. Only the TNSNAMES.ORA file differs on each server defining the aliases TEST and STTEST. For example on the primary server PRSERVER, TEST points to PRSERVER as the primary server and STSERVER as the standby; where as on the standby server STSERVER the alias TEST points to STSERVER as the primary server and PRSERVER as the standby (opposite to that on PRSERVER).



                          TNSNAMES.ORA File on the primary server PRSERVER:


                          TEST.DBSUPPORTSERVICES.CO.UK =


                          (DESCRIPTION =


                          (ADDRESS_LIST =


                          (ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))


                          )


                          (CONNECT_DATA =


                          (SERVER = DEDICATED)


                          (SERVICE_NAME = TEST)


                          )


                          )









                          STTEST.DBSUPPORTSERVICES.CO.UK =


                          (DESCRIPTION =


                          (ADDRESS_LIST =


                          (ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))


                          )


                          (CONNECT_DATA =


                          (SERVER = DEDICATED)


                          (SERVICE_NAME = TEST)


                          )


                          )









                          TNSNAMES.ORA File on the standby server STSERVER:


                          TEST.DBSUPPORTSERVICES.CO.UK =


                          (DESCRIPTION =


                          (ADDRESS_LIST =


                          (ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))


                          )


                          (CONNECT_DATA =


                          (SERVER = DEDICATED)


                          (SERVICE_NAME = TEST)


                          )


                          )









                          STTEST.DBSUPPORTSERVICES.CO.UK =


                          (DESCRIPTION =


                          (ADDRESS_LIST =


                          (ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))


                          )


                          (CONNECT_DATA =


                          (SERVER = DEDICATED)


                          (SERVICE_NAME = TEST)


                          )


                          )


                          http://hungrydba.com/DataGuard_via_RMAN.aspx
                          • 10. Re: TNS  Names on dataguard setup.
                            mseberg
                            Hello;

                            Your tsnnames are wrong, but its an easy fix.

                            TEST.DBSUPPORTSERVICES.CO.UK cannot point to one server on the Primary and a different server on the Standby

                            (HOST = PRSERVER)
                            (HOST = STSERVER)

                            Same with STTEST.DBSUPPORTSERVICES.CO.UK

                            (HOST = STSERVER)
                            (HOST = PRSERVER)


                            Each of these has 1 host only. Both entries need to be there, but the host cannot change from server to server.

                            Primary (HOST = PRSERVER)

                            On both
                            TEST.DBSUPPORTSERVICES.CO.UK =
                            (DESCRIPTION =
                            (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))
                            )
                            (CONNECT_DATA =
                            (SERVER = DEDICATED)
                            (SERVICE_NAME = TEST)
                            )
                            )
                            Standby = (HOST = STSERVER)

                            On both
                            STTEST.DBSUPPORTSERVICES.CO.UK =
                            (DESCRIPTION =
                            (ADDRESS_LIST =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))
                            
                            )
                            (CONNECT_DATA =
                            (SERVER = DEDICATED)
                            (SERVICE_NAME = TEST)
                            
                            )
                            ) 
                            Best Regards

                            mseberg
                            • 11. Re: TNS  Names on dataguard setup.
                              911566
                              Mr.Mseberg, Thanks a lot for your quick and valuable updates.

                              I have the copied the above tns entries from the below url. I hope those tns entries help us to connect database without doing any changes on client machine while doing failover/switch over. Please help me if i am wrong. Thanks in advance.

                              http://hungrydba.com/DataGuard_via_RMAN.aspx

                              Regards,
                              Mahendran
                              • 12. Re: TNS  Names on dataguard setup.
                                Shivananda Rao
                                The entries mentioned in the site you have specified is wrong.

                                Use this entry in both primary and standby server. You only need to copy this in the tnsnames.ora of both primary and standby servers. Nothing else is required to change. The tns entries on primary and standby servers should be identical.

                                TEST.DBSUPPORTSERVICES.CO.UK =
                                (DESCRIPTION =
                                (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = PRSERVER)(PORT = 1521))
                                )
                                (CONNECT_DATA =
                                (SERVICE_NAME = TEST)
                                )
                                )
                                STTEST.DBSUPPORTSERVICES.CO.UK =
                                (DESCRIPTION =
                                (ADDRESS_LIST =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = STSERVER)(PORT = 1521))
                                )
                                (CONNECT_DATA =
                                (SERVICE_NAME = TEST)
                                )
                                )
                                • 13. Re: TNS  Names on dataguard setup.
                                  CKPT
                                  user10782193 wrote:
                                  Mr.Mseberg, Thanks a lot for your quick and valuable updates.

                                  I have the copied the above tns entries from the below url. I hope those tns entries help us to connect database without doing any changes on client machine while doing failover/switch over. Please help me if i am wrong. Thanks in advance.

                                  http://hungrydba.com/DataGuard_via_RMAN.aspx

                                  Regards,
                                  Mahendran
                                  What is your latest TNS entries? Paste here.
                                  • 14. Re: TNS  Names on dataguard setup.
                                    mseberg
                                    Hello;

                                    The url you listed has them wrong too. I'm pretty sure mine are complete and correct :

                                    http://www.visi.com/~mseberg/data_guard_on_oracle_11_step_by_step.html

                                    Best Regards

                                    mseberg

                                    Later

                                    I took the time to post here :

                                    http://hungrydba.com/contactus.aspx

                                    And provided them with a correct example.

                                    MS

                                    Edited by: mseberg on Jan 18, 2012 7:17 AM
                                    1 2 Previous Next