This discussion is archived
8 Replies Latest reply: Apr 3, 2013 12:58 PM by ji li RSS

logs not shipping to new standby

977635 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Complete.

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

    :-)

Legend

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