Connection problem using tnsnames after Oracle upgrade

[Former Member]

Hi,

I upgraded Oracle version from 9.2.0.4 to 10.2.0.4 and now I cannot connect using a service name. The wp trace:

M Fri Jun 25 13:33:52 2010

M calling db_connect ...

C Prepending G:\oracle\product\10.2.0\db_1 to Path.

C Client NLS settings: AMERICAN_AMERICA.US7ASCII

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

C Connecting as /@CRD on connection 0 (nls_hdl 0) ... (dbsl 640 070308)

C Nls CharacterSet NationalCharSet C EnvHp ErrHp ErrHpBatch

C 0 US7ASCII 1 000000000F9950B0 0000000009FEB300 0000000009FEE658

C Attaching to DB Server CRD (con_hdl=0,svchp=0000000009FEE518,srvhp=000000000F9A5108)

C

C Fri Jun 25 13:34:01 2010

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

[dboci.c 4289]

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

[dbsloci.c 11395]

If I use tnsping with service name CRD it works,but when I try to connect through sqlplus it doesnu00B4t respond.

C:\Documents and Settings\crdadm>SQLPLUS system/************@CRD

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 28 12:27:13 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Sqlplus shows no messages,no errors.

And these are my environment variables. Iu00B4m on win 2003 x64.

ALLUSERSPROFILE=C:\Documents and Settings\All Users

APPDATA=C:\Documents and Settings\crdadm\Application Data

CLIENTNAME=CYPTI14

ClusterLog=C:\WINDOWS\Cluster\cluster.log

CommonProgramFiles=C:\Program Files\Common Files

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

COMPUTERNAME=ACDCPDQCRM101

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

dbms_type=ORA

DBS_ORA_SCHEMA=SAPCRD

DIR_LIBRARY=G:\usr\sap\CRD\SYS\exe\run

DIR_PROFILE=G:\usr\sap\CRD\SYS\profile

FP_NO_HOST_CHECK=NO

HOMEDRIVE=C:

HOMEPATH=\Documents and Settings\crdadm

JAVA_HOME=C:\j2sdk1.4.2_19

LOGONSERVER=
ACDCPDPDDC002

NLS_LANG=AMERICAN_AMERICA.WE8DEC

NUMBER_OF_PROCESSORS=4

ORACLE_HOME=G:\oracle\product\10.2.0\db_1

ORACLE_SID=CRD

OS=Windows_NT

Path=G:\oracle\product\10.2.0\db_1\bin;C:\Program Files (x86)\Oracle\jre\1.3.1\b

in;C:\Program Files (x86)\Oracle\jre\1.1.8\bin;C:\Program Files\HP\NCU;C:\WINDOW

S\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;D:\NetApp\MPIO\;D:\NetApp\SnapDri

ve\;G:\usr\sap\CRD\SYS\exe\run

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

PERL5LIB=G:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-X64-multi-thread;G

:\oracle\product\10.2.0\db_1\perl\5.8.3\lib;G:\oracle\product\10.2.0\db_1\perl\5

.8.3\lib\MSWin32-X64-multi-thread;G:\oracle\product\10.2.0\db_1\perl\site\5.8.3;

G:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib;G:\oracle\product\10.2.0\db_1\

sysman\admin\scripts

PROCESSOR_ARCHITECTURE=AMD64

PROCESSOR_IDENTIFIER=EM64T Family 6 Model 23 Stepping 6, GenuineIntel

PROCESSOR_LEVEL=6

PROCESSOR_REVISION=1706

ProgramFiles=C:\Program Files

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

PROMPT=$P$G

SAPDATA_HOME=J:\oracle\CRD

SAPLOCALHOST=ACDCPDQCRM101

SESSIONNAME=RDP-Tcp#3

SystemDrive=C:

SystemRoot=C:\WINDOWS

TEMP=C:\WINDOWS\TEMP\1

TMP=C:\WINDOWS\TEMP\1

TNS_ADMIN=G:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

USERDNSDOMAIN=HOTELS.CORP

USERDOMAIN=CENTRAL

USERNAME=crdadm

USERPROFILE=C:\Documents and Settings\crdadm

windir=C:\WINDOWS

Any help? What is wrong?

Regards,Pablo.

[markus_doehr2]

> C Prepending G:\oracle\product\10.2.0\db_1 to Path.

> C Client NLS settings: AMERICAN_AMERICA.US7ASCII

It seems you did not install the system as per SAP installation guide. Usually the PATHs are like

g:\oracle\<SID>\102\...

And the database codepage is also not correct (should be AMERICAN_AMERICA.WE8DEC).

Markus

[former_member227600]

PLease check the listener is running or not. Please paste the content of linstner.ora file here.

[Former Member]

Hi,

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

Things you need to check are :

1. Check if correct host entry is configured in c:\windows\system32\drivers\etc\hosts file.

2. Check if tnsnames.ora and listener.ora has correct hostname.

3. Check if "lsnrctl.exe" file exists in %ORACLE_HOME%\bin directory.

4. Check if LISTENER service is up and running.

If it doesn't solve your problem, then add the below line in SQLNET.ora.

NAMES.DIRECTRY_PATH=(TNSNAMES)

Also refer the below note

[Note 443867 - ORA-12154 Collective SAP note|https://service.sap.com/sap/support/notes/443867]

Regards.

Rajesh Narkhede