10 Replies Latest reply: Jan 14, 2013 8:54 AM by mseberg RSS

    Which tns entry provide to the customer?

    864988
      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
          Hello,

          Refer this TNS  Names on dataguard setup.


          Regards,
          Shivananda
          • 2. Re: Which tns entry provide to the customer?
            864988
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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