8 Replies Latest reply: Apr 3, 2013 2:58 PM by ji li RSS

    logs not shipping to new standby

    977635
      Well, here I am again... I've recreated the entire DGTEST environment and did a switchover (using instructions from http://www.visi.com/~mseberg/data_guard/Data_Guard_switchover.html), and after the switchover to standby (now primary) my log files are not being shipped to the original primary server.

      I can't find anything wrong, and tnsping works as it should to the services as well as the database.

      Ref. thread: primary db logs going to $OH/dbs location on standby instead of dest_1

      Perhaps I have a net configuration error somewhere...
      From the NEW primary database server:  "brahe"
      ===================================
      
      $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>sqlplus / as sysdba
      
      SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 3 12:36:01 2013
      
      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      
      
      SQL> connect sys/sys@DGTEST_DG1 as sysdba
      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
      
      
      Warning: You are no longer connected to ORACLE.
      SQL> connect sys/sys@DGTEST as sysdba
      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
      But yet, I can tnsping all day long without any problems....
      From the NEW primary database server:  "brahe"
      ===================================
      $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST
      
      TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:46
      
      Copyright (c) 1997, 2010, Oracle.  All rights reserved.
      
      Used parameter files:
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kepler.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
      OK (0 msec)
      $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST_DG1
      
      TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:52
      
      Copyright (c) 1997, 2010, Oracle.  All rights reserved.
      
      Used parameter files:
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = kepler.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
      OK (0 msec)
      $ [brahe] <DGTEST> /oracle/product/db/11.2.0.2/dbs>tnsping DGTEST_DG2
      
      TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:00:59
      
      Copyright (c) 1997, 2010, Oracle.  All rights reserved.
      
      Used parameter files:
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = brahe.nnet)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DGTEST) (UR = A)))
      OK (10 msec)
      On the new Standby server:  "kepler"
      =========================
      $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/dbs>lsnrctl services
      
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:39:53
      
      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kepler.nnet)(PORT=1521)))
      Services Summary...
      Service "DGTEST_DG1" has 1 instance(s).
        Instance "DGTEST", status READY, has 1 handler(s) for this service...
          Handler(s):
            "DEDICATED" established:0 refused:0 state:ready
               LOCAL SERVER
      The command completed successfully
      
      
      $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/dbs>lsnrctl status
      
      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 03-APR-2013 12:39:57
      
      Copyright (c) 1991, 2010, Oracle.  All rights reserved.
      
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kepler.nnet)(PORT=1521)))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
      Start Date                02-APR-2013 14:20:11
      Uptime                    0 days 22 hr. 19 min. 46 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   /oracle/product/db/11.2.0.2/network/admin/listener.ora
      Listener Log File         /oracle/product/diag/tnslsnr/kepler/listener/alert/log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kepler.nnet)(PORT=1521)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      Services Summary...
      Service "DGTEST_DG1" has 1 instance(s).
        Instance "DGTEST", status READY, has 1 handler(s) for this service...
      The command completed successfully
        • 1. Re: logs not shipping to new standby
          mseberg
          OK

          I avoid tnsping because the connect test is much better. Tnsping has limits
          SQL> connect sys/sys@DGTEST_DG1 as sysdba
          ERROR:
          ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
           
           
          Warning: You are no longer connected to ORACLE.
          SQL> connect sys/sys@DGTEST as sysdba
          ERROR:
          ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
          The listener on the Old Primary has an issue. Can you post it?

          I have had great luck with this format :

          http://www.visi.com/~mseberg/data_guard/Data_Guard_Listener_Example.html

          Make sure you restart the listener after changes.

          Update

          Have done a switchover and the test is working on mine.

          SQL> connect sys/<password>@primary as sysdba
          Connected.
          SQL> 
          Another update

          If I try the same test against the old primary server with a database that is not in the standby server tnsnames.ora I get this error. ( might be something else )
          ERROR:
          ORA-12154: TNS:could not resolve the connect identifier specified
          
          
          Warning: You are no longer connected to ORACLE.
          Best Regards

          mseberg

          Edited by: mseberg on Apr 3, 2013 11:44 AM

          Edited by: mseberg on Apr 3, 2013 11:47 AM

          Edited by: mseberg on Apr 3, 2013 11:50 AM
          • 2. Re: logs not shipping to new standby
            977635
            Thanks mseberg for all your help.

            Here is my listener.ora file on my old primary server (now new Standby server):
            # listener.ora Network Configuration File: /oracle/product/db/11.2.0.2/network/admin/listener.ora
            # Generated by Oracle configuration tools.
            
            ADR_BASE_LISTENER = /oracle/product
            
            LISTENER =
              (DESCRIPTION_LIST =
                (DESCRIPTION =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = kepler.nnet)(PORT = 1521))
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                )
              )
            
            SID_LIST_DGTEST =
              (SID_LIST =
                (SID_DESC =
                  (SID_NAME = PLSExtProc)
                  (ORACLE_HOME = /oracle/product/db/11.2.0.2)
                  (PROGRAM = extproc)
                )
                (SID_DESC =
                  (SID_NAME = DGTEST)
                  (ORACLE_HOME = /oracle/product/db/11.2.0.2)
                )
                (SID_DESC =
                  (SID_NAME = DGTEST)
                  (GLOBAL_DBNAME = DGTEST_DG1)
                  (ORACLE_HOME = /oracle/product/db/11.2.0.2)
                )
              )
            Also, I tried to do a loopback test on this server and it also failed.
            $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/network/admin>sqlplus system/system@DGTEST
            
            SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 3 12:47:32 2013
            
            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
            
            ERROR:
            ORA-12560: TNS:protocol adapter error
            
            
            Enter user-name:
            
            $ [kepler] <DGTEST> /oracle/product/db/11.2.0.2/network/admin>sqlplus system/system@DGTEST_DG1
            
            SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 3 12:47:39 2013
            
            Copyright (c) 1982, 2010, Oracle.  All rights reserved.
            
            ERROR:
            ORA-12560: TNS:protocol adapter error
            
            Enter user-name:
            • 3. Re: logs not shipping to new standby
              mseberg
              OK

              SID_LIST_LISTENER

              Is you listener named "DGTEST" ?

              Mine is the default so I use SID_LIST_LISTENER. If I remember correctly its SID_LIST_<LISTENER_NAME>

              Otherwise mine looks like :
              # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/network/admin/listener.ora
              # Generated by Oracle configuration tools.
              
              LISTENER =
                (DESCRIPTION_LIST =
                  (DESCRIPTION =
                    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
                    (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))
                  )
                )
                
              
              SID_LIST_LISTENER =
                (SID_LIST =
                  (SID_DESC =
                    (SID_NAME = PLSExtProc)
                    (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                    (PROGRAM = extproc)
                  )
                 (SID_DESC =
                    (global_dbname = STANDBY_DGMGRL.myhost)
                    (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
                    (sid_name = STANDBY)
                  ) 
                )                    
              
              ADR_BASE_LISTENER = /u01/app/oracle
              
              INBOUND_CONNECT_TIMEOUT_LISTENER=120
              The ADR_BASE_LISTENER and INBOUND_CONNECT_TIMEOUT_LISTENER should not cause you error.

              Sometimes a rogue space in the file will.

              I'm thinking lsnrctl status does not show READY.


              I believe we are close. ( kind of fun except for the not working )

              Best Regards

              mseberg
              • 4. Re: logs not shipping to new standby
                977635
                BINGO! That was it, (at least for the listener problem).

                Now, to do the switchover again.... :-)

                Yes, kind of fun. I agree. Thanks for working with me on this.
                In my spare time in my other windows, I've just moved our production standby off of our Direct Attached Storage to our SAN (all new mount points, etc.).
                That was also something I spoke with you about in another thread.

                BTW: If you would like my updated version of your instructions for how to create standby except with both primary and standby using the same name, I'd be glad to email it to you. You've been a great help.

                Now I have another strange DG issue in my production system that I'll open another thread on.
                • 5. Re: logs not shipping to new standby
                  mseberg
                  Great!

                  "my updated version of your instructions for how to create standby" - Yes. Not sure how to get my email to you without the spammers collecting me.

                  I will switch my test system back.

                  Thanks!

                  mseberg
                  • 6. Re: logs not shipping to new standby
                    977635
                    Check my profile and grab my email address and let me know when you have it so I can hide it again.
                    Thanks.
                    • 7. Re: logs not shipping to new standby
                      mseberg
                      Complete.

                      Why didn't I think of that!!
                      • 8. Re: logs not shipping to new standby
                        ji li
                        Nice work Michael.

                        :-)