This discussion is archived
10 Replies Latest reply: Jan 14, 2013 6:54 AM by mseberg RSS

Which tns entry provide to the customer?

864988 Newbie
Currently Being Moderated
Hello,

I have just created a new physical dataguard; I would like to provide the customer the TNS entry so in case of failover all active sessions / select queries will failover to the new primary site without manual intervention of the DBA or of the customer.
My database version is 11.2.0.3 on redhat Linux 6.


following are the TNS entries of ;

Primary;
DBPRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db01.cat.ps.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbprim.cat.ps.com)
    )
  )
Standby:
DBSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db02.cat.ps.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbstby.cat.ps.com)
    )
  )
Thank you
  • 1. Re: Which tns entry provide to the customer?
    Shivananda Rao Guru
    Currently Being Moderated
    Hello,

    Refer this TNS  Names on dataguard setup.


    Regards,
    Shivananda
  • 2. Re: Which tns entry provide to the customer?
    864988 Newbie
    Currently Being Moderated
    Thank you. I have following issues on Dataguard setup;

    Alert log Errors:
    2013-01-14 08:07:30.178000 -05:00
    ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    ARC3: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    FAL[server, ARC3]: Error 270 creating remote archivelog file '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    FAL[server, ARC3]: FAL archive failed, see trace file.
    ARCH: FAL archive failed. Archiver continuing
    ORACLE Instance ctidb - Archival Error. Archiver continuing.
    The trace file is showing ;

    *** 2013-01-14 08:05:01.876
    Redo shipping client performing standby login
    *** 2013-01-14 08:05:02.145 4645 krsu.c
    Logged on to standby successfully
    Client logon and security negotiation successful!
    Error 270 creating standby archive log file at host '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    *** 2013-01-14 08:05:02.360 4320 krsh.c
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    *** 2013-01-14 08:05:02.360 4320 krsh.c
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    *** 2013-01-14 08:05:02.360 4320 krsh.c
    Error 270 for archive log file 3 to '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    *** 2013-01-14 08:05:02.419 2932 krsi.c
    krsi_dst_fail: dest:2 err:270 force:0 blast:1
    ORA-00270: error creating archive log
  • 3. Re: Which tns entry provide to the customer?
    Shivananda Rao Guru
    Currently Being Moderated
    From your primary database, post the outcome of:
    select severity,error_code,message from v$dataguard_status where dest_id=2;
    Are you able to connect to the standby database from primary and viceversa ?


    Regards,
    Shivananda
  • 4. Re: Which tns entry provide to the customer?
    mseberg Guru
    Currently Being Moderated
    Hello

    You can give them something like this
     ernie =
       (DESCRIPTION =
          (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = db01.cat.ps.com)(PORT = 1521))
             (ADDRESS = (PROTOCOL = TCP)(HOST = db02.cat.ps.com)(PORT = 1521))
             )
             (CONNECT_DATA =
             (SERVICE_NAME = ernie)
          )
     )
     
    Create
     
     
     BEGIN
      
        DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
      
    END;
    Start
    BEGIN
     
       DBMS_SERVICE.START_SERVICE('ernie');
     
     END;
     / 
     
    Modify
     
     BEGIN
        DBMS_SERVICE.MODIFY_SERVICE
        ('ernie',
        FAILOVER_METHOD => 'BASIC',
        FAILOVER_TYPE => 'SELECT',
        FAILOVER_RETRIES => 200,
        FAILOVER_DELAY => 1);
     END;
    / 
    Create Trigger
    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;
    / 
    Best Regards

    mseberg
  • 5. Re: Which tns entry provide to the customer?
    864988 Newbie
    Currently Being Moderated
    Hello,

    Yes, I am able to connect from primary to the standby as sys user using TNS alias and viceversa.

    Thank you
  • 6. Re: Which tns entry provide to the customer?
    864988 Newbie
    Currently Being Moderated
    thanks
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    14-01-2013 09:25:24
    
    SQL>
    SQL> select * from v$dataguard_status where dest_id=2
    AND  TIMESTAMP > SYSDATE - 1 / 12
    ORDER BY TIMESTAMP DESC ;
      2    3
    FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP
    ------------------------ ------------- ---------- ----------- ---------- --- -------------------
    MESSAGE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Log Transport Services   Error                  2        1509       3135 YES 14-01-2013 08:22:58
    Error 3135 for archive log file 3 to '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Log Transport Services   Warning                2        1508       3135 NO  14-01-2013 08:22:58
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Log Transport Services   Warning                2        1507       3135 NO  14-01-2013 08:22:58
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3135)
    
    Log Transport Services   Warning                2        1502        270 NO  14-01-2013 08:07:30
    ARC3: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Fetch Archive Log        Error                  2        1503        270 YES 14-01-2013 08:07:30
    FAL[server, ARC3]: Error 270 creating remote archivelog file '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Log Transport Services   Warning                2        1501        270 NO  14-01-2013 08:07:30
    ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    
    Log Transport Services   Warning                2        1494        270 NO  14-01-2013 08:05:32
    ARC2: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    
    Log Transport Services   Warning                2        1495        270 NO  14-01-2013 08:05:32
    ARC2: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Fetch Archive Log        Error                  2        1496        270 YES 14-01-2013 08:05:32
    FAL[server, ARC2]: Error 270 creating remote archivelog file '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Log Transport Services   Warning                2        1492        270 NO  14-01-2013 08:05:02
    LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Log Transport Services   Warning                2        1491        270 NO  14-01-2013 08:05:02
    LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    
    Log Transport Services   Error                  2        1493        270 YES 14-01-2013 08:05:02
    Error 270 for archive log file 3 to '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Log Transport Services   Warning                2        1487        270 NO  14-01-2013 08:01:52
    ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Log Transport Services   Warning                2        1486        270 NO  14-01-2013 08:01:52
    ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    
    Fetch Archive Log        Error                  2        1488        270 YES 14-01-2013 08:01:52
    FAL[server, ARC0]: Error 270 creating remote archivelog file '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Fetch Archive Log        Error                  2        1481        270 YES 14-01-2013 07:58:16
    FAL[server, ARC0]: Error 270 creating remote archivelog file '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02.cat.ps.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbstby.cat.ps.com)(SERVER=DEDICATED)))'
    
    Log Transport Services   Warning                2        1480        270 NO  14-01-2013 07:58:16
    ARC0: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
    
    Log Transport Services   Warning                2        1479        270 NO  14-01-2013 07:58:16
    ARC0: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
    
    
    18 rows selected.
    
    SQL>
  • 7. Re: Which tns entry provide to the customer?
    mseberg Guru
    Currently Being Moderated
    Hello;

    Having a hard time checking documents but I have these listed for your error :

    Troubleshooting ORA-3135 Connection Lost Contact [ID 787354.1]

    ORA - 03135 : connection lost contact while shipping from Primary Server to Standby server [ID 739522.1]

    Database Hanging during Archival [ID 1142856.1]

    Best Regards

    mseberg
  • 8. Re: Which tns entry provide to the customer?
    864988 Newbie
    Currently Being Moderated
    hello,

    It doesn't work for me;
    C:\Documents and Settings\mydesk>sqlplus sys@test as sysdba
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 14 15:31:47 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Enter password:
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    
    
    Enter user-name:
    The Tnsnames.ora file
    test =
       (DESCRIPTION =
          (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = db01.cat.ps.com)(PORT = 1521))
             (ADDRESS = (PROTOCOL = TCP)(HOST = db02.cat.ps.com)(PORT = 1521))
             )
             (CONNECT_DATA =
             (SERVICE_NAME = test)
          )
     )
    And the service creation
    BEGIN
      
        DBMS_SERVICE.CREATE_SERVICE('test','test');
      
    END;
    
    
    
    Start
    
    BEGIN
     
       DBMS_SERVICE.START_SERVICE('test');
     
     END;
     / 
    
    
    
    Modify
    
    
    BEGIN
        DBMS_SERVICE.MODIFY_SERVICE
        ('test',
        FAILOVER_METHOD => 'BASIC',
        FAILOVER_TYPE => 'SELECT',
        FAILOVER_RETRIES => 200,
        FAILOVER_DELAY => 1);
     END;
    / 
    
    
    
    Create Trigger
    
    
    CREATE TRIGGER CHECK_TEST_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('test');
     ELSE
     DBMS_SERVICE.STOP_SERVICE('test');
     END IF;
    END;
    /
  • 9. Re: Which tns entry provide to the customer?
    864988 Newbie
    Currently Being Moderated
    I have fixed it by adding domain name to the service; test.cat.ps.com instead of simply "test"
  • 10. Re: Which tns entry provide to the customer?
    mseberg Guru
    Currently Being Moderated
    Yes. That would make sense.

    I ran a test using this about 10 days ago.

    I used this query to test the service
    SET LINESIZE 200
    SELECT SERVICE_NAME, USERNAME, STATUS, MACHINE FROM V$SESSION WHERE USERNAME IS NOT NULL;
    That is, to prove to audit I was using the new service.

    Best Regards

    mseberg

Legend

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