Unable to ship Redo logs to standby DB (Oracle Data Gaurd)

[Former Member]

Hi all,

We have configured Oracle Data Gaurd between our Production (NPP) & Standby (NPP_DR).

The configuration is complete however, the production is unable to ship redo logs to standby DB.

We keep getting the error "PING[ARC0]: Heartbeat failed to connect to standby 'NPP_DR'. Error is 12154." in Primary DB

Primary & DR are on different boxes.

Please see the logs below in the production alert log file & npp_arc0_18944.trc trace files:

npp_arc0_18944.trc 😘
2011-01-19 09:17:38.007 62692 kcrr.c
Error 12154 received logging on to the standby
Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'NPP_DR'
Error 12154 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'NPP_DR'
2011-01-19 09:17:38.007 62692 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'NPP_DR'. Error is 12154.
2011-01-19 09:17:38.007 60970 kcrr.c
kcrrfail: dest:2 err:12154 force:0 blast:1
2011-01-19 09:22:38.863
Redo shipping client performing standby login
OCIServerAttach failed -1
.. Detailed OCI error val is 12154 and errmsg is 'ORA-12154: TNS:could not resolve the connect identifier specified

alert log file on Primary*
Error 12154 received logging on to the standby
Wed Jan 19 09:02:35 2011
Error 12154 received logging on to the standby
Wed Jan 19 09:07:36 2011
Error 12154 received logging on to the standby
Wed Jan 19 09:12:37 2011
Error 12154 received logging on to the standby
Wed Jan 19 09:13:10 2011
Incremental checkpoint up to RBA [0x2cc.2fe0.0], current log tail at RBA [0x2cc.2fe9.0]
Wed Jan 19 09:17:38 2011
Error 12154 received logging on to the standby
Wed Jan 19 09:22:38 2011
Error 12154 received logging on to the standby
Wed Jan 19 09:27:39 2011
Error 12154 received logging on to the standby

However, we are able to tnsping from primary to DR

Tnsping Results
From Primary:
juemdbp1:oranpp 19> tnsping NPP_DR
TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 19-JAN-2011 09:32:50
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
Used parameter files:
/oracle/NPP/102_64/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = 10.80.51.101) (PORT = 49160))) (CONNECT_DATA = (SID = NPP) (SERVER = DEDICATED)))
OK (60 msec)

Tnsnames.ora in Primary:
################
Filename......: tnsnames.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/TNSNAMES.ORA#4 $
################
NPP.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = nppjorp)
          (PORT = 49160)
        )
    )
    (CONNECT_DATA =
       (SID = NPP)
       (GLOBAL_NAME = NPP.WORLD)
    )
  )
NPP_HQ.WORLD=
 (DESCRIPTION =
   (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = nppjorp)
          (PORT = 49160)
        )
   )
   (CONNECT_DATA =
        (SID = NPP)
        (SERVER = DEDICATED)
   )
 )
NPP_DR.WORLD=
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS =
        (COMMUNITY = SAP.WORLD)
        (PROTOCOL = TCP)
        (HOST = 10.80.51.101)
        (PORT = 49160)
   )
 )
 (CONNECT_DATA =
        (SID = NPP)
        (SERVER = DEDICATED)
 )
 )
NPPLISTENER.WORLD=
        (DESCRIPTION =
          (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = nppjorp)
                (PORT = 49160)
          )
        )
Listener.ora in Primary
################
Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#4 $
################
ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = NPP.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = NPP)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = nppjorp)
          (PORT = 49160)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = NPP)
      (ORACLE_HOME = /oracle/NPP/102_64)
    )
  )

Thank You,

Salman Qayyum

Edited by: Salman M.A. Qayyum on Jan 19, 2011 8:12 AM

[Former Member]

In my case i got same error.

to avoid error we can start database and sap like this in primary side.

su - oraSID

sqlplus / as sysdba

startup

exit

exit

su - SIDadm

startsap

but its not a permanent solution, we need to add entries at /sapmnt/SID/profile/oracle tnsnames.ora in both server.

[Former Member]

Hello ,

I have faced the same issue after configuring oracle 10g Data guard

• 2011-01-19 09:17:38.007 62692 kcrr.c

Error 12154 received logging on to the standby

Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'NPP_DR'

Error 12154 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'NPP_DR'

• 2011-01-19 09:17:38.007 62692 kcrr.c

PINGARC0: Heartbeat failed to connect to standby 'NPP_DR'. Error is 12154.
*** 2011-01-19 09:17:38.007 60970 kcrr.c
kcrrfail: dest:2 err:12154 force:0 blast:1

*** 2011-01-19 09:22:38.863

Redo shipping client performing standby login

I copied pwdSID.ora file from primary to standby database & the issue got resolved..

pwdSID.ora located in /oracle/SID/ver/dbs

before overwriting take a backup of that file in standby location

All the best

Thanks,

rahul

[Former Member]

Hi Rahul,

Thanks for the quick reply,

I do not have any folder by the name "/oracle/SID/ver" , are u talking about the password file orapwSID located in the /oracle/SID/102_64/dbs directory??

Thank You,

Salman Qayyum

[Former Member]

yes thats correct....

[Former Member]

Hi Rahul,

That did not work

[audunlea_hansen]

Hi!

Did you restarte the standby database? You need to restart it to read the new orapw-file.

Please upload the parameters for archiver from both primary and standby: show parameter arch;

Regards

Audun

DBA

[Former Member]

Hi Salman,

Sorry my earlier answer might not be very clear.

pl look in to E:\oracle\SID\102\database --- PWDSID.ORA

Copy this file from source to target .. as i understood if all parameters are synchronzed properly

this above should connect the heart beat.. Hope you are working with Oracle 10.2

Please check this command in source system...& it should returm valid

PLEASE check with the below commands

On Primary Database:

 SQL> select max(sequence#) from v$archived_log;

On Standby Database:

 SQL> select max(sequence#) from v$log_history;

The output of above both 2 select commands should be the same.

If the output of both the command is not same then check the status of Standby archiving destination on Primary site.

On Primary Database:

 SQL> select status, Error from v$archvie_dest where dest_id=2;

The STATUS should return u2013 VALID. If it returns Error, then check the connectivity between the primary and standby machines.

At this stage where you can say you have successfully configured standby database.

thanks,

rahul

[Former Member]

Hi all,

Thanks for all replies.....the issue is now resolved.

For those who might face similar issue ...... we had configured the tnsnames.ora in the $ORACLE_HOME/network/admin directory only. The tnsping was working fine when did tnsping NPP_DR from primary to DR with the orasid user. However, the database was unable to ship the redo logs to the DR database.

After little research, I noticed that all the Oralce processes were running under <sid>adm user.

Ran tnsping with <sid>adm user & it failed....So we configured the tnsnames.ora in the /sapmnt/SID/profile/oracle directory as well with the service name of DR server & that resolved the issue......Had spent one day behind this

Thank You,

Salman Qayyum

[former_member527726]

I had a similar problem, the only difference was that I did not have any Oracle processes running under the sidadm account. But the solution was the same, i.e., add entries for the DG services in the /sapmnt/SID/profile/oracle tnsnames.ora file. You have to add them on both the primary and standby servers.

[Former Member]

Thank Salman Qayyum,

I had the same issue. Oracle processes on the primairy were running under <SID>adm

Primary database was restarted with ora<SID> user and it worked, no more issue

Thanks again

---

Lan DUONG

[Former Member]

Hi,

Please find the remaining post ...

Tnsnames.ora in DR:
################
Filename......: tnsnames.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/TNSNAMES.ORA#4 $
################
NPP.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = nppjor)
          (PORT = 49160)
        )
    )
    (CONNECT_DATA =
       (SID = NPP)
       (GLOBAL_NAME = NPP.WORLD)
    )
  )
NPP_HQ.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = SAP.WORLD)
        (PROTOCOL = TCP)
        (HOST = hq_nppjor)
        (PORT = 49160)
      )
    )
    (CONNECT_DATA =
        (SID = NPP)
        (SERVER = DEDICATED)
    )
  )
NPP_DR.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = SAP.WORLD)
        (PROTOCOL = TCP)
        (HOST = nppjor)
        (PORT = 49160)
      )
    )
    (CONNECT_DATA =
        (SID = NPP)
        (SERVER = DEDICATED)
        (SERVICE_NAME = NPP_DR)
    )
  )
NPPLISTENER.WORLD=
        (DESCRIPTION =
          (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = nppjor)
                (PORT = 49160)
          )
        )
Listener.ora in DR
################
Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 6.10
Name..........:
Date..........:
@(#) $Id: //bc/700-1_REL/src/ins/SAPINST/impl/tpls/ora/ind/LISTENER.ORA#4 $
################
ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = NPP.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = NPP)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = nppjor)
          (PORT = 49160)
        )
       (ADDRESS =
         (COMMUNITY = SAP.WORLD)
         (PROTOCOL = TCP)
         (HOST = 10.80.50.101)
         (PORT = 49160)
       )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = NPP)
      (ORACLE_HOME = /oracle/NPP/102_64)
    )
  )

/etc/hosts settings in Primary
host:oranpp 25> grep nppjor /etc/hosts
10.32.243.54    nppjor.sabic.com        nppjor
10.32.50.115    nppjorp.sabic.com        nppjorp
/etc/hosts settings in DR
host:oranpp 11> grep nppjor /etc/hosts
10.32.243.54    hq_nppjor.sabic.com     hq_nppjor
10.80.243.54    nppjor.sabic.com        nppjor
10.80.50.115    nppjorp.sabic.com        nppjorp

Thank You,

Salman Qayyum