Starting ABAP instance: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

[mario_bisonti2]

Hallo.

I upgraded my delepment portal to Oracle 12.2.0.1 from 11.2.0.3

My system has two instance:

1 ABAP instance

1 Java instance

on the same host.

When I start ABAP Instance, the instance is not able to start the db

I obtain:

.

.

OCIServerAttach(OCI_DEFAULT) failed with SQL error 12505:

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor                                           

.

.

If have to start manually the db else the ABAP instance doesn't work.

What could I check?

I already checked tnsnames.ora and listener.ora

Thanks a loto

Mario

[mario_bisonti2 · Accepted solution]

Yes, it use new sqlplus 12.2.1.0 when I execute it from command line.

I don't understand...

When I use strdb.cmd I don't know if it use <sid>adm or SAPSefvice<sid>?

[JamesZ · Accepted solution]

If so, then startsap may change change the environment variables during starting SAP.

are you able to find the os process called sapstartsrv? If you can find it, please restart it:

sapcontrol -nr <instance #> -function RestartService

All you can check the registry for user sidadm, to check if the is anything point to old oracle_home

Best regards,

James

[mario_bisonti2 · Accepted solution]

The problem was in the instance profile.

There was:SETENV_04 = PATH=$(DIR_EXECUTABLE):%(PATH)

Perhaps it forces the environment of SAPService<sid> and this setting doesn't permit that sapstart copies the environment of <sid>adm into SAPService<sid> I suppose.

I deleted the entry:

SETENV_04 = PATH=$(DIR_EXECUTABLE):%(PATH)

from the instance profile, and SAPstart is able to start the db.

Thanks

[JamesZ · Accepted solution]

Great!

[mario_bisonti2]

I note, in the stderr3 log file this entry:

Z:\usr\sap\ABC\DVEBMGS00\work>echo WHENEVER SQLERROR exit SQL.SQLCODE  1>v9start.sql

Z:\usr\sap\ABC\DVEBMGS00\work>echo connect / as sysdba  1>>v9start.sql

Z:\usr\sap\ABC\DVEBMGS00\work>echo startup  1>>v9start.sql

Z:\usr\sap\ABC\DVEBMGS00\work>echo exit  1>>v9start.sql

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 08:58:10 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-12557: TNS:protocol adapter not loadable

It seems that it is using sqlplus 11.2.0.3 instead of 12.1.0.2

But the environment of <sid>adm is:

ALLUSERSPROFILE=C:\ProgramData

APPDATA=C:\Users\ABCadm\AppData\Roaming

CommonProgramFiles=C:\Program Files\Common Files

CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files

CommonProgramW6432=C:\Program Files\Common Files

COMPUTERNAME=SABC01

ComSpec=C:\windows\system32\cmd.exe

DBMS_TYPE=ora

dbs_ora_schema=SAPSR3

dbs_ora_tnsname=ABC

FP_NO_HOST_CHECK=NO

HOMEDRIVE=C:

HOMEPATH=\Users\ABCadm

JAVA_HOME=Z:\usr\sap\ABC\DVEBMGS00\exe\sapjvm_4

LOCALAPPDATA=C:\Users\ABCadm\AppData\Local

LOGONSERVER=\\SWDCVIVV04

NLS_LANG=AMERICAN_AMERICA.UTF8

NUMBER_OF_PROCESSORS=4

ORACLE_HOME=Z:\oracle\oraclehome01\ABC\12102

ORACLE_SID=ABC

OS=Windows_NT

Path=C:\Program Files\EMC NetWorker\nsr\bin;C:\windows\system32;C:\windows;C:\windows\System32\Wbem;C:\windows\System32\WindowsPowerShell\v1.0\;C:\j2sdk1.4.2_16-x64\bin;Z:\oracle\oraclehome01\ABC\12102\BIN;Z:\usr\sap\ABC\DVEBMGS00\exe;Z:\usr\sap\ABC\SYS\exe\uc\NTAMD64

PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC

PROCESSOR_ARCHITECTURE=AMD64

PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 15 Stepping 1, GenuineIntel

PROCESSOR_LEVEL=6

PROCESSOR_REVISION=0f01

ProgramData=C:\ProgramData

ProgramFiles=C:\Program Files

ProgramFiles(x86)=C:\Program Files (x86)

ProgramW6432=C:\Program Files

PROMPT=$P$G

PSModulePath=C:\windows\system32\WindowsPowerShell\v1.0\Modules\

PUBLIC=C:\Users\Public

rsdb_ssfs_connect=1

RSEC_SSFS_DATAPATH=Z:\usr\sap\ABC\SYS\global\security\rsecssfs\data

RSEC_SSFS_KEYPATH=Z:\usr\sap\ABC\SYS\global\security\rsecssfs\key

SAPDATA_HOME=Z:\oracle\ABC

SAPEXE=Z:\usr\sap\ABC\DVEBMGS00\exe

SAPLOCALHOST=SABC01

SAPSYSTEMNAME=ABC

SAP_DIR_PERF=Z:\usr\sap\PRFCLOG

SESSIONNAME=RDP-Tcp#0

SystemDrive=C:

SystemRoot=C:\windows

TEMP=c:\tmp\3

TMP=c:\tmp\3

TNS_ADMIN=\\SABC01\sapmnt\ABC\SYS\profile\oracle

USERDNSDOMAIN=domain.NET

USERDOMAIN=domain

USERNAME=ABCadm

USERPROFILE=C:\Users\ABCadm

windir=C:\windows

windows_tracing_flags=3

windows_tracing_logfile=C:\BVTBin\Tests\installpackage\csilogfile.log

So, where <sid>adm taks the path for sqlplus 11.2.0.3 instead of 12.1.0.2  ?

Thanks a lot

Mario

[JamesZ]

Hi Mario,

Probably the sidadm env is set in windows registry, please check it.

You can simply test if sidadm can use sqlplus to connect oracle 12c.

Best regards,

James

[former_member182657]

Hi Mario,

When I start ABAP Instance, the instance is not able to start the db

Could you share latest trans.log file from the result R3trans -d ?

Regards,

[mario_bisonti2]

The log from R3trans -d

Z:\oracle\oraclehome01\ABC\12102\sap\ora_upgrade\post_upgrade>R3trans -d

This is R3trans version 6.25 (release 742 - 06.05.15 - 20:15:05).

unicode enabled version

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

R3trans finished (0012).

[former_member207186]

Hi,

If possible could you please also provide latest trans.log file (created by R3trans -d) for further check/advice.

Regards,

Bíborka

[mario_bisonti2]

R3trans -d   dowsn't write on trans.log

The only output on the screen is:

C:\Users\ABCadm>R3trans -d

This is R3trans version 6.25 (release 742 - 06.05.15 - 20:15:05).

unicode enabled version

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

R3trans finished (0012).

[JamesZ]

Hi Mario,

Seems the database is using dynamic registry to oracle listener. Please show output of below:

lsnrctl status

Please manually start db and try below:

sqlplus /nolog

conn /@ABC

tnsping ABC

Let me know the result above.

Best regards,

James

[mario_bisonti2]

Hallo James.

tnsping ABC is ok

But if the database is shutdown when I make

sqlplus /nolog

connect / @ABC

returns

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

This question is Not Answered.(Mark as assumed answered)

[JamesZ]

did you means that, the sap can be up if you manually bring up database?

[thiago_cavalheiro]

Hi Mario,

Ok, just to be clear: you state that when you bring up the database manually the system starts fine, but if you try to start SAP directly it fails with ORA-12505, correct?

What command are you using to start SAP? If you want to start the database as well you can only use startsap, as the sapcontrol commands will not bring up the database.

In case you are using startsap, does it work when you use startdb?

Regards,

Thiago

[mario_bisonti2]

Yes.

If I manually bring up the db, SAP works

[JamesZ]

If so, then the problem could be <sid>adm has wrong environment variable, or just has no permission. Can you share the startdb log generated during starting up sap?

In addition, did you adapt the env for user <sid>adm as mentioned in the upgrade guide?

[mario_bisonti2]

I use startsap or mmc (Microsoft Management Console)

But I have the same error

ORA-12505

[mario_bisonti2]

Hallo James.

Yes, I adapted the PATH and ORACLE_HOME for the user <sid>adm as in the upgrade guide.

[mario_bisonti2]

When db is shutdown

connect / @ABC

ERROR:

ORA-12505: TNS:listener does not currently know of SID given in connect

descriptor

When I start the db manually :

connect / @ABC

Connected.

[JamesZ]

Hi Mario,

As mentioned, please attach the startdb log. Also please provide the env output of user <sid>adm.

Best regards,

James

[former_member207186]

Hi,

Regarding "ORA-12505" issue, you can check SAP Note 576919 - "Composite SAP note ORA-12505" for troubleshooting.

You can also refer to SAP Note 1914631 - "Central Technical Note for Oracle Database 12c Release 1 (12.1)"

Regards,

Bíborka

[mario_bisonti2]

Hallo .

I checked it but no helped me.

Have I to change something on the instance profile?

My listener.ora

# listener.ora Network Configuration File: Z:\oracle\oraclehome01\ABC\12102\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = Z:\oracle\oraclehome01\ABC\12102)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:Z:\oracle\oraclehome01\ABC\12102\bin\oraclr12.dll")

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = SABC01)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

My tnsnames.ora under TNS_ADMIN path:

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

ABC.WORLD=

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS =

          (COMMUNITY = SAP.WORLD)

          (PROTOCOL = TCP)

          (HOST = SABC01)

          (PORT = 1521)

        )

    )

    (CONNECT_DATA =

       (SID = ABC)

       (GLOBAL_NAME = ABC.WORLD)

    )

  )

LISTENER_ABC = (

  ADDRESS =

  (COMMUNITY = SAP.WORLD)

  (PROTOCOL = TCP)

  (HOST = SABC01)

  (PORT = 1521)

)

[thiago_cavalheiro]

Hello Mario,

Does the issue reproduce when you start the database manually using <sid>adm?
Also, did you migrate to 'oracle' user id? If so, was the ora<sid> deleted or not?

What should you check:

- TNS_ADMIN for <sid>adm and the files lay within the path, check for any incorrect reference

- instance and default profile - check whether there are references for the old ORACLE_HOME

- ORACLE_HOME for <sid>adm and ora<sid> (in case the last exist) in the environment when you log in with them. Make sure they are both correct.

Best regards,

Thiago