Unable to start SAP in DR after database switch over

[Former Member]

Hi All,

My enviroment is ERP 6.0/Oracle 10.2.0.5/AIX 6.1 in both primary and secondary datacenters. We have implemented Oracle dataguard. On testing swithover everything works fine at database level. No errors are experienced. However I'm unable to start SAP application. Kindy see attachments for errors experienced when attempting to start SAP.

Thanks

[Former Member]

Hi Msoro,

Please note your earlier screenshot had error:

tns listener does not currently know of SID given in connect descriptor

So as per example below ensure this correct!

A sample entry for the alias C11 and default domain WORLD could be, for example:

  C11.WORLD =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST = c11host) (PORT = 1527)

        )

      )

      (CONNECT_DATA =

        (SID = PRD)

      )

    )

              With regard to ORA-12154, only the first line is important. tnsnames.ora must contain this type of line, comprising the alias used and the default domain. If this entry is missing, ORA-12154 is issued. Even if the line <sid>.WORLD is indented (that is, starting with blank spaces or tabs), ORA-12154 occurs.

Avoid using the "@" character in passwords for database users. Otherwise, ORA-12154 errors may occur because Oracle may interpret the part behind "@"as a TNS alias. This is particularly difficult to analyze if the password defined in the SAPUSER table contains an "@" for SAPR3 or SAP<sid>

Johan

[Former Member]

I'm assuming that the validation test of the switch works fine, and all the upgrade/modify activity (SBP, parameters setting, upgrade of release, upgrade ora_client) was performed on the slave server.

So, all the modification regarding upgrade of DB instances, profile parameters or other changes, I really dont understand the reason you are facing the 12154

In your log:

(HOST = devsvr)

(HOST = 172.16.5.12)

Are you sure that the devsrv hostname is resolved with this IP?

(SID_NAME = DEVDR)

is this value correct?

Are you sure that the DR has this Oracle SID? And in case, how do you perform the copy of DB using different DBSID? Usually, the difference on the DR environment is the logical IP address, mapped with the DNS propagation in case of Disaster Recovery Validation

What about the /oracle/client directory? Try to change the permission to 777

Please take a look at this note

Note 443867 - ORA-12154 Collective SAP note

Regards,

Cristian

[Former Member]

Did you setup the tnsnames.ora properly? Remember on newer installations SAP has its own tnsnames.ora in the profile/oracle directory (/sapmnt/SID/profile/oracle).

You need to specify both locations for the service SAP is using.

Cheers Michael

[Former Member]

Hi...

While u are starting database, please also check alert log which is in sub folders of  'saptrace' directory.

or share the last lines when u start the database.

Regards

KHS

[former_member188883]

Hi Msoro,

Please check whether R3trans -d works. If it fails paste the output of trans.log

Also as mentioned by RB check whether listener is running or not.

Regards,

Deepak Kori

[Former Member]

Thanks for your response Deepak. I've confirmed the listner is up and running. See attachment for output of commnd R3trans -xd and the translog

[Reagan]

Hello

You are not doing any favor to us by attaching those screenshots which are barely visible.

Attach the trans.log and the output of this query from the SQL.

select name, open_mode from v$database;

Cheers

RB

[Former Member]

here is an excerpt of the translog

Tue May 21 21:11:01 CDT 2013

devsvr:oradev 5> tail -900f trans.log

4 ETW000 R3trans version 6.14 (release 700 - 14.02.08 - 14:55:00).

4 ETW000 unicode enabled version

4 ETW000 ===============================================

4 ETW000

4 ETW000 date&time   : 21.05.2013 - 00:00:57

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -d

4 ETW000  trace at level 2 opened for a given file pointer

4 ETW000  [dev trc     ,00000]  Tue May 21 00:00:57 2013                              91  0.000091

4 ETW000  [dev trc     ,00000]  db_con_init called                                    15  0.000106

4 ETW000  [dev trc     ,00000]  create_con (con_name=R/3)                             35  0.000141

4 ETW000  [dev trc     ,00000]  Loading DB library '/usr/sap/DEV/SYS/exe/run/dboraslib.o' ...

4 ETW000                                                                              56  0.000197

4 ETW000  [dev trc     ,00000]  load shared library (/usr/sap/DEV/SYS/exe/run/dboraslib.o), hdl 0

4 ETW000                                                                            3111  0.003308

4 ETW000  [dev trc     ,00000]  Library '/usr/sap/DEV/SYS/exe/run/dboraslib.o' loaded

4 ETW000                                                                              23  0.003331

4 ETW000  [dev trc     ,00000]  function DbSlExpFuns loaded from library /usr/sap/DEV/SYS/exe/run/dboraslib.o

4 ETW000                                                                              24  0.003355

4 ETW000  [dev trc     ,00000]  Version of '/usr/sap/DEV/SYS/exe/run/dboraslib.o' is "700.08", patchlevel (0.144)

4 ETW000                                                                             277  0.003632

4 ETW000  [dev trc     ,00000]  function dsql_db_init loaded from library /usr/sap/DEV/SYS/exe/run/dboraslib.o

4 ETW000                                                                              23  0.003655

4 ETW000  [dev trc     ,00000]  function dbdd_exp_funs loaded from library /usr/sap/DEV/SYS/exe/run/dboraslib.o

4 ETW000                                                                              49  0.003704

4 ETW000  [dev trc     ,00000]  New connection 0 created                              32  0.003736

4 ETW000  [dev trc     ,00000]  0: name = R/3, con_id = -000000001 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO

4 ETW000                                                                              27  0.003763

4 ETW000  [dev trc     ,00000]  db_con_connect (con_name=R/3)                         51  0.003814

4 ETW000  [dev trc     ,00000]  find_con_by_name found the following connection for reuse:

4 ETW000                                                                              22  0.003836

4 ETW000  [dev trc     ,00000]  0: name = R/3, con_id = 000000000 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO

4 ETW000                                                                              23  0.003859

4 ETW000  [dev trc     ,00000]  Got LIBPATH=/usr/lib:/lib:/usr/sap/DEV/SYS/exe/run:/oracle/DEV/102_64/lib from environment

4 ETW000                                                                             434  0.004293

4 ETW000  [dev trc     ,00000]  Oracle Client Version: '10.2.0.5.0'                   60  0.004353

4 ETW000  [dev trc     ,00000]  -->oci_initialize (con_hdl=0)                         25  0.004378

4 ETW000  [dev trc     ,00000]  Client NLS settings: AMERICAN_AMERICA.UTF8          1853  0.006231

4 ETW000  [dev trc     ,00000]  Logon as OPS$-user to get SAPSR3's password           19  0.006250

4 ETW000  [dev trc     ,00000]  Connecting as /@DEVDR on connection 0 (nls_hdl 0) ... (dbsl 700 250407)

4 ETW000                                                                              23  0.006273

4 ETW000  [dev trc     ,00000]  Nls CharacterSet                 NationalCharSet              C      EnvHp      ErrHp ErrHpBatch

4 ETW000                                                                              25  0.006298

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      1 0x110f78cc0 0x110fbb3f8 0x110fbaca8

4 ETW000                                                                              36  0.006334

4 ETW000  [dev trc     ,00000]  Allocating service context handle for con_hdl=0       30  0.006364

4 ETW000  [dev trc     ,00000]  Allocating server context handle                      16  0.006380

4 ETW000  [dev trc     ,00000]  Attaching to DB Server DEVDR (con_hdl=0,svchp=0x110fbe598,srvhp=0x110fbe7b8)

4 ETW000                                                                              79  0.006459

4 ETW000  [dboci.c     ,00000]  *** ERROR => OCI-call 'OCIServerAttach' failed with rc=12154

4 ETW000                                                                           10778  0.017237

4 ETW000  [dbsloci.    ,00000]  *** ERROR => CONNECT failed with sql error '12154'

4 ETW000                                                                              26  0.017263

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 12154        14  0.017277

4 ETW000  [dev trc     ,00000]  Try to connect with default password                  50  0.017327

4 ETW000  [dev trc     ,00000]  Connecting as SAPSR3/<pwd>@DEVDR on connection 0 (nls_hdl 0) ... (dbsl 700 250407)

4 ETW000                                                                              23  0.017350

4 ETW000  [dev trc     ,00000]  Nls CharacterSet                 NationalCharSet              C      EnvHp      ErrHp ErrHpBatch

4 ETW000                                                                              24  0.017374

4 ETW000  [dev trc     ,00000]    0 UTF8                                                      1 0x110f78cc0 0x110fbb3f8 0x110fbaca8

4 ETW000                                                                              24  0.017398

4 ETW000  [dev trc     ,00000]  server_detach(con_hdl=0,stale=0,svrhp=0x110fbe7b8)

4 ETW000                                                                              21  0.017419

4 ETW000  [dev trc     ,00000]  Detaching from DB Server (con_hdl=0,svchp=0x110fbe598,srvhp=0x110fbe7b8)

4 ETW000                                                                              21  0.017440

4 ETW000  [dev trc     ,00000]  Deallocating server context handle 0x110fbe7b8        19  0.017459

4 ETW000  [dev trc     ,00000]  Allocating server context handle                      15  0.017474

4 ETW000  [dev trc     ,00000]  Attaching to DB Server DEVDR (con_hdl=0,svchp=0x110fbe598,srvhp=0x110fbe7b8)

4 ETW000                                                                              28  0.017502

4 ETW000  [dboci.c     ,00000]  *** ERROR => OCI-call 'OCIServerAttach' failed with rc=12154

4 ETW000                                                                             849  0.018351

4 ETW000  [dbsloci.    ,00000]  *** ERROR => CONNECT failed with sql error '12154'

4 ETW000                                                                              23  0.018374

4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 12154        14  0.018388

4 ETW000  [dblink      ,00431]  ***LOG BY2=>sql error 12154  performing CON [dblink#5 @ 431]

4 ETW000                                                                              94  0.018482

4 ETW000  [dblink      ,00431]  ***LOG BY0=>ORA-12154: TNS:could not resolve the connect identifier specified [dblink#5 @ 431]

4 ETW000                                                                              22  0.018504

2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_ora_tnsname = 'DEVDR'"

[Former Member]

[former_member206552]

Hi Msoro

all the below steps needs to be done as SIDADM user

1) please check the value for

sidadm > env | grep TNS

TNS_ADMIN=/usr/sap/SID/SYS/profile/oracle

sidadm >

and verify the the correct tnsnames.ora file is being used.

2) try and tnsping SID from user sidadm and see if it gets resolved.

3) verify that the client is specified in LD_LIBRARY_PATH and also exists

sidadm > env | grep client

LD_LIBRARY_PATH=/oracle/client/10x_64/instantclient

sidadm >

sidadm > ls -l /oracle/client/10x_64/instantclient

hope this helps

best regards

marius

[Former Member]

As ora<sid> user run

> lsnrctl status

and attach output here. Also attach listener.ora file from $ORACLE_HOME/network/admin.

[Reagan]

Hello

Check whether the listener is up and running.

Also check whether the Oracle instant client is installed as well.

Cheers

RB

[Former Member]

Hi Msoro,

Could you verify that the listener is up? Additionally, execute the R3trans -xd at the OS level and paste the result.

Best regards,

Orkun Gedik

[Former Member]

Thanks for your response Orkun. I've confirmed the listner is up and running. See attachment for output of commnd R3trans -xd

[Former Member]

It seems that tns configuration is incorrect. Could you provide tnsnames.ora and listener.ora, here?

Best regards,

Orkun Gedik

[Former Member]

Orkun,

Below are the tnsnames.ora & listener.ora files.

Thanks

devsvr:oradev 27> more listener.ora

################

# 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 = DEV.WORLD)

        )

        (ADDRESS=

          (PROTOCOL = IPC)

          (KEY = DEV)

        )

        (ADDRESS =

          (COMMUNITY = SAP.WORLD)

          (PROTOCOL = TCP)

          (HOST = devsvr)

          (PORT = 1527)

        )

  )

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = DEVDR)

      (ORACLE_HOME = /oracle/DEV/102_64)

    )

  )

devsvr:oradev 28>

devsvr:oradev 29>

devsvr:oradev 29> more tnsnames.ora

################

# 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 $

################

DEVDR=

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = SAP.WORLD)

          (PROTOCOL = TCP)

          (HOST = devsvr)

          (PORT = 1527)

        )

    )

    (CONNECT_DATA =

       (SID = DEV)

       (GLOBAL_NAME = DEVDR.WORLD)

    )

  )

DEV.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = SAP.WORLD)

          (PROTOCOL = TCP)

          (HOST = 172.16.5.12)

          (PORT = 1527)

        )

    )

    (CONNECT_DATA =

       (SID = DEV)

       (GLOBAL_NAME = DEV.WORLD)

    )

  )

devsvr:oradev 30>

[Former Member]

Could you add the lines into listener.ora;

(ADDRESS =

(PROTOCOL = IPC)

(KEY = DEVDR.WORLD)

)

(ADDRESS=

(PROTOCOL = IPC)

(KEY = DEVDR)

)

Then restart the listener.

Best regards,

Orkun Gedik

[former_member182034]

hi Msoro,

Would you confirm the SID is same on both server?

and send me the log of tnsping SID.

Regards,