Home » RDBMS Server » Networking and Gateways » ORA-12154 and ORA-12504: (Oracle11.2.0.3.0 , Solaris 10)
ORA-12154 and ORA-12504: [message #548553] Thu, 22 March 2012 18:51 Go to next message
duncbarc
Messages: 2
Registered: March 2012
Location: Birmingham
Junior Member
Hi experts
I have installed an Oracle 11 database called TST on Solaris 10 container.
also installed the client software. I can tnsping the database and sqlplus.
I have a a MDM console software that connects to the database via my pc
Can you tell me what is wrong with my .ora files and how I correct them please.I have probably messed arround with them so much , I don't know what Im doing anymore.
Here are my files and outputs.


Please help

Duncan


------------------------------------------------------------------

tstadm> tnsping TST

TNS Ping Utility for Solaris: Version 11.2.0.3.0 - Production on 22-MAR-2012 23:01:06

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

Used parameter files:
/oracle/TST/112_64/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sapmdmdb)(PORT = 1521)) (LOAD_BALANCE = yes)) (CONNECT_DATA = (SID = TST) (SERVER = DEDICATED)

(SERVICE_NAME = TST)))
OK (40 msec)
----------------------------------------------------------------------

tstadm> sqlplus system/mypasswd@TST

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 22 23:01:56 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


---------------------------------------------------------------------

This is the logfile generated on the server when i try to connect the Console on my laptop to the server. The pop up say it cannot connect to the server.

,MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,201,,,,server_user,server_global,,,
2012-03-22T22:20:52.757,5 ,24,"OCI Attach returned: -1",MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,543,,,,server_user,server_global,,,
2012-03-22T22:20:52.758,5 ,24,"Service 'sapmdmdb', Schema 'system', ERROR CODE=12154 ||| ORA-12154: TNS:could not resolve the connect identifier specified

Last CMD:
",MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,201,,,,server_user,server_global,,,
2012-03-22T22:36:53.894,5 ,24,"Service 'sapmdmdb', Schema 'system', ERROR CODE=12504 ||| ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
OCI Attach, try again ...
Last CMD:
",MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,201,,,,server_user,server_global,,,
2012-03-22T22:36:53.898,5 ,24,"OCI Attach returned: -1",MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,543,,,,server_user,server_global,,,
2012-03-22T22:36:53.898,5 ,24,"Service 'sapmdmdb', Schema 'system', ERROR CODE=12154 ||| ORA-12154: TNS:could not resolve the connect identifier specified

Last CMD:
",MDSAdminServer@GetDbmsSettings,Oracle_DBConnection.cpp,201,,,,server_user,server_global,,, Sad
---------------------------------------------------------------------------

# sqlnet.ora Network Configuration File: /oracle/TST/112_64/network/admin/sqlnet
.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
NAME.DEFAULT_ZONE = WORLD
SQLNET.EXPIRE_TIME = 0
TCP.NODELAY=YES

ADR_BASE = /oracle

-------------------------------------------

# tnsnames.ora Network Configuration File: /oracle/TST/112_64/network/admin/tnsn
ames.ora
# Generated by Oracle configuration tools.

TST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sapmdmdb)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SID = TST)
(SERVER = DEDICATED)
(SERVICE_NAME = TST)
)
)
-------------------------------------------------------------------

listener.ora Network Configuration File: /oracle/TST/112_64/network/admin/list
ener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = sapmdmdb)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 32768)
(SID_NAME = TST)
(ORACLE_HOME = /oracle/TST/112_64)
)
)
ADR_BASE_LISTENER = /oracle
--------------------------------------------------------------------------------

tstadm> env
DIR_LIBRARY=/usr/sap/TST/SYS/exe/run
DISPLAY=localhost:10.0
HOME=/export/home/tstadm
LC_COLLATE=en_GB.ISO8859-15
LC_CTYPE=en_GB.ISO8859-15
LC_MESSAGES=C
LC_MONETARY=en_GB.ISO8859-15
LC_NUMERIC=en_GB.ISO8859-15
LC_TIME=en_GB.ISO8859-15
LD_LIBRARY_PATH=/oracle/client/11x_64/instantclient:/usr/sap/TST/SYS/exe/run:/usr/sap/TST/SYS/exe/uc/sun_64
LIBPATH=/oracle/client/11x_64/instantclient:
LOGNAME=tstadm
MAIL=/var/mail//tstadm
MDM_HOME=/usr/sap/TST/MDS00/exe
ORACLE_HOME=/oracle/TST/112_64
ORACLE_SID=TST
PATH=/oracle/TST/112_64/bin:.:/export/home/tstadm:/usr/sap/TST/SYS/exe/run:
/usr/sap/TST/SYS/exe/uc/sun_64:/usr/bin:/bin:/usr/ccs/bin: /usr/ucb
PS1=
tstadm>
SAPSYSTEMNAME=TST
SHELL=/bin/sh
SSH_TTY=/dev/pts/1
TERM=xterm
TZ=GB
USER=tstadm
------------------------------------------------------
sapmdmdb% env
USER=oratst
LOGNAME=oratst
HOME=/oracle/TST
PATH=/oracle/TST/112_64/bin:/usr/bin:/bin
MAIL=/var/mail//oratst
SHELL=/bin/csh
TZ=GB
LC_CTYPE=en_GB.ISO8859-15
LC_COLLATE=en_GB.ISO8859-15
LC_TIME=en_GB.ISO8859-15
LC_NUMERIC=en_GB.ISO8859-15
LC_MONETARY=en_GB.ISO8859-15
LC_MESSAGES=C
SSH_TTY=/dev/pts/4
TERM=xterm
DISPLAY=localhost:11.0
PWD=/oracle/TST/112_64/network/admin
ORACLE_HOME=/oracle/TST/112_64
ORACLE_SID=TST
LD_LIBRARY_PATH=/oracle/client/11x_64/instantclient
LIBPATH=/oracle/client/11x_64/instantclient


------------------------------
lsnrctl start
Starting /oracle/TST/112_64/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
System parameter file is /oracle/TST/112_64/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/sapmdmdb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sapmdmdb)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 22-MAR-2012 23:11:49
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/TST/112_64/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/sapmdmdb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sapmdmdb)(PORT=1521)))
Services Summary...
Service "TST" has 1 instance(s).
Instance "TST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
sapmdmdb%
-------------------------------------------------------
LSNRCTL for Solaris: Version 11.2.0.3.0 - Production on 22-MAR-2012 23:20:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 - Production
Start Date 22-MAR-2012 23:11:49
Uptime 0 days 0 hr. 8 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/TST/112_64/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/sapmdmdb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sapmdmdb)(PORT=1521)))
Services Summary...
Service "TST" has 2 instance(s).
Instance "TST", status UNKNOWN, has 1 handler(s) for this service...
Instance "TST", status READY, has 1 handler(s) for this service...
Service "TSTXDB" has 1 instance(s).
Instance "TST", status READY, has 1 handler(s) for this service...
The command completed successfully
---------------------------------------------------------

[Updated on: Fri, 23 March 2012 02:08] by Moderator

Report message to a moderator

Re: ORA-12154 and ORA-12504: [message #548555 is a reply to message #548553] Thu, 22 March 2012 19:09 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
ORA-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ORA-12154 occurs when client requests a connection to some DB server system using some connection string.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ORA-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
http://edstevensdba.wordpress.com/2011/02/26/ora-12154tns-03505/
Re: ORA-12154 and ORA-12504: [message #548561 is a reply to message #548555] Thu, 22 March 2012 22:32 Go to previous messageGo to next message
duncbarc
Messages: 2
Registered: March 2012
Location: Birmingham
Junior Member
Thankyou BlackSwan , I almost understand it. It is nearly 0330hrs here.
In my MDM console gui I was trying to connect to the host name....no no , I should be connecting to the database name. EG TST or as per the link mytestdb.
I got rid of the NAME.DEFAULT_ZONE = WORLD from my sqlnet.ora and restarted the listerer and application, connected to TST and hey presto, the application is talking to me!
Re: ORA-12154 and ORA-12504: [message #548562 is a reply to message #548561] Thu, 22 March 2012 23:11 Go to previous message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
> the application is talking to me!
YEA!
Previous Topic: Listener could not listen Ora 11 XE server
Next Topic: Oracle Transparent Gateway fails on AS400
Goto Forum:
  


Current Time: Sat Oct 25 14:16:51 CDT 2014

Total time taken to generate the page: 0.08581 seconds