This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 27, 2012 1:18 AM by 911566 RSS

TNS  Names on dataguard setup.

911566 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    Hello,
    Welcome to the forum,
    You can use Transparent Application Failover (TAF)
  • 2. Re: TNS  Names on dataguard setup.
    Shivananda Rao Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points