Ora - 12154 when startsap

Cannot restart SAP - sql error 12154

DB starts fine.

I can log onto to the DB with

Connect SAPR3/SAP @ QAS - logs on fine - however, when I run startsap - it fails

R3trans -d logs on fine also.

There are 2 instances on this server - the listener is stopped and started by the ORADEV user for both instances. - This appears to work fine.

tnsnames.ora / sqlnet.ora / listener.ora all look fine.

I've checked all obvious notes.

Any assistance appreciated.

Thanks

SAP Managed Tags:
Oracle Database

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (3)

Restored full file system

Issue went away

Overkill but it worked.

Thanks

Conor

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi,

Can you provide the results of env command and the contents of files tnsnames.ora, sqlnet.ora and listener.or?

Thanks

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello,

Execute tnsping <SID> for both instances and let me know the result. If it is an ORA-12154, it definetely has to do with the tnsnames.ora file / configuration

Execure oerr ora 12154 from orasid, this will give you some clues

If you could copy the logfile here when you try to start SAP it would be helpful

Did you say that none of the 2 instances you have in the server can start, while for both you can run R3trans -d successfully?

Regards

Andreas

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

tnsping QAS -> succesful using the qasadm user

tnsping DEV - also successful

The DEV instance is running fine

It is the QAS one that I'm having issues with.

will attach logs / *.ora files in a moment.

Thanks

Conor

Format may not be the best - but here goes

ENV

newsapdev:qasadm 21% env

HOME=/export/home/qasadm

PATH=/oracle/QAS/bin:/export/home/qasadm:/usr/sap/QAS/SYS/exe/run:/usr/bin:.:/usr/ccs/bin:/usr/ucb:/usr/sbin

LOGNAME=qasadm

HZ=

TZ=Eire

TERM=xterm

SHELL=/usr/bin/csh

PWD=/usr/sap/QAS/DVEBMGS01/work

USER=qasadm

SAPSYSTEMNAME=QAS

DIR_LIBRARY=/usr/sap/QAS/SYS/exe/run

LD_LIBRARY_PATH=/usr/sap/QAS/SYS/exe/run:/oracle/QAS/lib

THREAD=NOPS

dbms_type=ORA

dbs_ora_tnsname=QAS

dbs_ora_schema=SAPR3

ORACLE_PSRV=QAS

ORACLE_SID=QAS

ORACLE_HOME=/oracle/QAS

ORACLE_BASE=/oracle

ORA_NLS=/oracle/QAS/ocommon/NLS_723/admin/data

ORA_NLS32=/oracle/QAS/ocommon/NLS_733/admin/data

ORA_NLS33=/oracle/QAS/ocommon/nls/admin/data

NLS_LANG=AMERICAN_AMERICA.WE8DEC

SAPDATA_HOME=/oracle/QAS

startsap_newsapdev_01

SAP-R/3-Startup Program Rel 46D V1.8 (2003/04/24)

Starting at 2007/09/13 13:41:12

Startup Profile: "/usr/sap/QAS/SYS/profile/START_DVEBMGS01_newsapdev"

Execute Pre-Startup Commands

(4148) Local: /usr/sap/QAS/SYS/exe/run/sapmscsa -n pf=/usr/sap/QAS/SYS/profil

e/QAS_DVEBMGS01_newsapdev

/usr/sap/QAS/SYS/exe/run/sapmscsa: SCSA is attached and useable.

/usr/sap/QAS/SYS/exe/run/sapmscsa: finished.

(4148) Local: rm -f ms.sapQAS_DVEBMGS01

(4148) Local: ln -s -f /usr/sap/QAS/SYS/exe/run/msg_server ms.sapQAS_DVEBMGS0

(4148) Local: rm -f dw.sapQAS_DVEBMGS01

(4148) Local: ln -s -f /usr/sap/QAS/SYS/exe/run/disp+work dw.sapQAS_DVEBMGS01

(4148) Local: rm -f co.sapQAS_DVEBMGS01

(4148) Local: ln -s -f /usr/sap/QAS/SYS/exe/run/rslgcoll co.sapQAS_DVEBMGS01

(4148) Local: rm -f se.sapQAS_DVEBMGS01

(4148) Local: ln -s -f /usr/sap/QAS/SYS/exe/run/rslgsend se.sapQAS_DVEBMGS01

Starting Programs

(4167) Starting: local ms.sapQAS_DVEBMGS01 pf=/usr/sap/QAS/SYS/profile/QAS_DV

EBMGS01_newsapdev

(4168) Starting: local dw.sapQAS_DVEBMGS01 pf=/usr/sap/QAS/SYS/profile/QAS_DV

EBMGS01_newsapdev

(4169) Starting: local co.sapQAS_DVEBMGS01 -F pf=/usr/sap/QAS/SYS/profile/QAS

DVEBMGS01newsapdev

(4170) Starting: local se.sapQAS_DVEBMGS01 -F pf=/usr/sap/QAS/SYS/profile/QAS

DVEBMGS01newsapdev

(4148) Waiting for Child Processes to terminate.

(4148) **** 2007/09/13 13:41:12 Child 4168 terminated with Status 150 . ****

(4148) **** 2007/09/13 13:41:12 Child 4169 terminated with Status 150 . ****

(4148) **** 2007/09/13 13:41:12 Child 4167 terminated with Status 150 . ****

(4148) **** 2007/09/13 13:41:12 Child 4170 terminated with Status 150 . ****

(4148) No more Child Processes to wait for.

(4148) Parent Shutdown at 2007/09/13 13:41:12

Execute Post-Shutdown Commands

(4148) Exiting with Return-Code 3. (No more child processes)

Startup of Instance failed

newsapdev:qasadm 8% more listener.ora

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

Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 4.0A
Name..........:
Date..........:

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

LISTENER =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL=IPC) (KEY= QAS.WORLD))

(ADDRESS = (PROTOCOL=IPC) (KEY= QAS))

(ADDRESS = (PROTOCOL=IPC) (KEY= DEV.WORLD))

(ADDRESS = (PROTOCOL=IPC) (KEY= DEV))

(ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP)

(HOST = newsapdev) (PORT = 1527))

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = QAS)

(ORACLE_HOME = /oracle/QAS)

)

(SID_DESC =

(SDU = 32768)

(SID_NAME = DEV)

(ORACLE_HOME = /oracle/DEV)

)

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/DEV)

(PROGRAM = extproc)

)

sqlnet.ora

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

Filename......: sqlnet.ora
Created.......: created by SAP AG, R/3 Rel. >= 4.0A
Name..........:
Date..........:

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

AUTOMATIC_IPC = ON

TRACE_LEVEL_CLIENT = OFF

SQLNET.EXPIRE_TIME = 0

NAMES.DEFAULT_DOMAIN = WORLD

NAMES.DIRECTORY_PATH = (TNSNAMES)

NAME.DEFAULT_ZONE = WORLD

newsapdev:qasadm 9%

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

#Filename.......: tnsnames.ora

Created.......: created by SAP AG, R/3 Rel. >= 4.0A
Name..........:
Date..........:

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

EXTPROC_CONNECTION_DATA.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

QAS.WORLD=

(DESCRIPTION =

(SDU = 32768)

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = newsapdev)

(PORT = 1527)

)

(CONNECT_DATA =

(SID = QAS)

(GLOBAL_NAME = QAS.WORLD)

)

DEV.WORLD=

(DESCRIPTION =

(SDU = 32768)

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = newsapdev)

(PORT = 1527)

)

(CONNECT_DATA =

(SID = DEV)

(GLOBAL_NAME = DEV.WORLD)

)

newsapdev:qasadm 541% tail -100 dev_w22

B 0: name = R/3, concnt= 000000 state = DISCONNECTED, perm = YES, reco = YES

M *** ERROR => ThHdlReconnect: db_reconnect failed (-1) [thxxhead.c 14280]

B Thu Sep 13 13:33:34 2007

B db_con_reconnect performing the reconnect for con:

B 0: name = R/3, concnt= 000000 state = DISCONNECTED, perm = YES, reco = YES

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Thu Sep 13 13:33:39 2007

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Thu Sep 13 13:33:44 2007

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

B Reconnect failed for connection:

B 0: name = R/3, concnt= 000000 state = DISCONNECTED, perm = YES, reco = YES

M *** ERROR => ThHdlReconnect: db_reconnect failed (-1) [thxxhead.c 14280]

B db_con_reconnect performing the reconnect for con:

B 0: name = R/3, concnt= 000000 state = DISCONNECTED, perm = YES, reco = YES

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Thu Sep 13 13:33:49 2007

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Thu Sep 13 13:33:54 2007

C Got NLS_LANG=AMERICAN_AMERICA.WE8DEC from environment

C Logon as OPS$-user to get SAPR3's password

C Connecting as /@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

C Try to connect with default password

C Connecting as SAPR3/<pwd>@QAS on connection 0 ...

C *** ERROR => OCI-call 'olog' failed: rc = 12154

[dboci.c 2457]

C *** ERROR => CONNECT failed with sql error '12154'

[dbsloci.c 7018]

B Reconnect failed for connection:

B 0: name = R/3, concnt= 000000 state = DISCONNECTED, perm = YES, reco = YES

M *** ERROR => ThHdlReconnect: db_reconnect failed (-1) [thxxhead.c 14280]

newsapdev:qasadm 542%

also provide contents of file dev_w0

It's last entry in this file is from friday sept 7th @ 6:05am

will upload in a moment.

Thanks

COnor

both DEV and QAS are listening to the same TCP port.

leave one at 1527 and change the other one to another value, such as 1521 or 1523. adapt listener.ora and tnsnames.ora accordingly, then restart listener.

Hi - Where should this change be made in listener.ora file - ?

I can see where it should be made in tnsnames.ora

I will post you my listener.ora and tnsnames.ora from 2 systems that work ok here and they are located in the same host (QAS and TRN). Just make sure that you change the hostname in there and the SIDs (and the Oracle Home path, since you are not running Oracle 9

tnsnames.ora

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

Filename......: tnsnames.ora
Created.......: created by SAP AG, R/3 Rel. >= 4.0A
Name..........:
Date..........:

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

QAS.WORLD=

(DESCRIPTION =

(SDU = 32768)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC) (KEY = QAS))

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = qatda001)

(PORT = 1527)

)

(CONNECT_DATA =

(SID = QAS)

(GLOBAL_NAME = QAS.WORLD)

)

TRN.WORLD=

(DESCRIPTION =

(SDU = 32768)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC) (KEY = TRN))

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = qatda001)

(PORT = 1527)

)

(CONNECT_DATA =

(SID = TRN)

(GLOBAL_NAME = TRN.WORLD)

)

listener.ora

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

Filename......: listener.ora
Created.......: created by SAP AG, R/3 Rel. >= 4.0A
Name..........:
Date..........:

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

LISTENER =

(ADDRESS_LIST =

(ADDRESS=

(PROTOCOL=IPC)

(KEY= QAS.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= QAS)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= TRN.WORLD)

)

(ADDRESS=

(PROTOCOL=IPC)

(KEY= TRN)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = qatda001)

(PORT = 1527)

)

STARTUP_WAIT_TIME_LISTENER = 0

CONNECT_TIMEOUT_LISTENER = 10

TRACE_LEVEL_LISTENER = OFF

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32768)

(SID_NAME = QAS)

(ORACLE_HOME = /oracle/QAS/920_64)

)

(SID_DESC =

(SDU = 32768)

(SID_NAME = TRN)

(ORACLE_HOME = /oracle/TRN/920_64)

)

I am running both systems on the same ports and have no issue. But I am on a different Oracle version than you

Message was edited by:

Andreas Malandrinos

Message was edited by:

Andreas Malandrinos

Ask a Question

Top Q&A Solution Author

User

Count