Home » RDBMS Server » Networking and Gateways » ORA-12154 Error (Oracle 9.2.0.6.0 on Windows)
ORA-12154 Error [message #436570] |
Thu, 24 December 2009 04:27 |
chetanaZ
Messages: 132 Registered: October 2009 Location: UK
|
Senior Member |
|
|
Hi,
First, this being common error, I googled it but found that all options mentioned in various articles/forums are proper in my case
I am struggling to connect to a database using tnsnames.ora
Since I will be using this database as App Server Metadata Repository, I am first testing to connect the database using tnsnames.ora from the same machine itself
Even I am unable to do tnsping to the database instance
however if I set ORACLE_SID I am able to connect properly
Moreover while configuring TNSnames.ora, everytime the test comes successful using 'Net Configuration Assistant'
One thing is this is VMware machine. Will it be causing any issue?
Following are related configuration files, logs and output of commands
Please help on this
Thanks and Regards,
Chetana
A)lsnrctl status
C:\Documents and Settings\oradba.DEV>lsnrctl status
LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 - Production on 24-DEC-2009 10:09:17
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=appserver1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.6.0 - Production
Start Date 24-DEC-2009 10:09:01
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File e:\ora9_home\network\admin\listener.ora
Listener Log File e:\ora9_home\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=appserver1abc.co.uk)(PORT=1521)))
Services Summary...
Service "DB01.WORLD" has 1 instance(s).
Instance "DB01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
B)tnsping DB01
C:\Documents and Settings\oradba.DEV>tnsping DB01
TNS Ping Utility for 32-bit Windows: Version 9.2.0.6.0 - Production on 24-DEC-2009 10:09:05
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name
C:\Documents and Settings\oradba.DEV>
C) Tnsnames.ora
# TNSNAMES.ORA Network Configuration File: e:\ora9_home\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
DB01abc.co.uk =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = appserver1abc.co.uk)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db01.world)
)
)
D) Listener.ora
# LISTENER.ORA Network Configuration File: E:\ora9_home\network\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = appserver1)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DB01.WORLD)
(ORACLE_HOME = E:\ora9_home)
(SID_NAME = DB01)
)
)
E) Sqlnet.ora
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = dev.luthor.biz
F)Extract of Listener.log
TNSLSNR for 32-bit Windows: Version 9.2.0.6.0 - Production on 24-DEC-2009 10:03:34
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
System parameter file is e:\ora9_home\network\admin\listener.ora
Log messages written to e:\ora9_home\network\log\listener.log
Trace information written to e:\ora9_home\network\trace\listener.trc
Trace level is currently 0
Started with pid=1792
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=appserver1abc.co.uk)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
24-DEC-2009 10:04:33 * service_register * DB01 * 0
24-DEC-2009 10:05:58 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=oradba))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093632)) * status * 0
24-DEC-2009 10:06:55 * service_update * DB01 * 0
24-DEC-2009 10:06:59 * service_died * DB01 * 12537
24-DEC-2009 10:07:50 * service_register * DB01 * 0
24-DEC-2009 10:07:56 * service_update * DB01 * 0
24-DEC-2009 10:08:21 * service_update * DB01 * 0
24-DEC-2009 10:08:24 * service_died * DB01 * 12537
24-DEC-2009 10:08:44 * service_register * DB01 * 0
24-DEC-2009 10:08:51 * service_update * DB01 * 0
TNSLSNR for 32-bit Windows: Version 9.2.0.6.0 - Production on 24-DEC-2009 10:09:01
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
System parameter file is e:\ora9_home\network\admin\listener.ora
Log messages written to e:\ora9_home\network\log\listener.log
Trace information written to e:\ora9_home\network\trace\listener.trc
Trace level is currently 0
Started with pid=2840
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=appserver1abc.co.uk)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
24-DEC-2009 10:09:17 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=oradba))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093632)) * status * 0
24-DEC-2009 10:09:59 * service_register * DB01 * 0
24-DEC-2009 10:12:11 * service_update * DB01 * 0
24-DEC-2009 10:12:44 * service_update * DB01 * 0
G)
Enter user-name: system/oracle@db01.world
ERROR:
ORA-12154: TNS:could not resolve service name
H) netstat -a
C:\Documents and Settings\oradba.DEV>netstat -a
Active Connections
Proto Local Address Foreign Address State
TCP appserver1:http appserver1abc.co.uk:0 LISTENING
TCP appserver1:epmap appserver1abc.co.uk:0 LISTENING
TCP appserver1:https appserver1abc.co.uk:0 LISTENING
TCP appserver1:microsoft-ds appserver1abc.co.uk:0 LISTENING
TCP appserver1:1040 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1521 appserver1abc.co.uk:0 LISTENING
TCP appserver1:2030 appserver1abc.co.uk:0 LISTENING
TCP appserver1:3339 appserver1abc.co.uk:0 LISTENING
TCP appserver1:3389 appserver1abc.co.uk:0 LISTENING
TCP appserver1:6050 appserver1abc.co.uk:0 LISTENING
TCP appserver1:8081 appserver1abc.co.uk:0 LISTENING
TCP appserver1:8228 appserver1abc.co.uk:0 LISTENING
TCP appserver1:41523 appserver1abc.co.uk:0 LISTENING
TCP appserver1:netbios-ssn appserver1abc.co.uk:0 LISTENING
TCP appserver1:1046 appserver1abc.co.uk:1748 ESTABLISHED
TCP appserver1:1521 appserver1abc.co.uk:1649 ESTABLISHED
TCP appserver1:1649 appserver1abc.co.uk:1521 ESTABLISHED
TCP appserver1:1736 dev-dc1abc.co.uk:microsoft-ds ESTABLISHED
TCP appserver1:1748 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1748 appserver1abc.co.uk:1046 ESTABLISHED
TCP appserver1:1754 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1808 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1809 appserver1abc.co.uk:0 LISTENING
TCP appserver1:3389 D02501:4436 ESTABLISHED
TCP appserver1:1047 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1047 appserver1abc.co.uk:1651 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1653 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1655 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1657 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1659 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1661 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1663 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1665 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1667 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1669 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1671 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1673 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1675 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1677 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1679 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1681 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1683 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1685 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1687 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1689 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1691 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1693 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1695 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1697 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1699 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1701 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1703 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1705 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1707 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1709 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1711 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1713 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1715 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1717 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1719 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1721 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:pptp TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1725 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1727 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1729 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1731 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1733 TIME_WAIT
TCP appserver1:1047 appserver1abc.co.uk:1735 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:0 LISTENING
TCP appserver1:1049 appserver1abc.co.uk:1650 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1652 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1654 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1656 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1658 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1660 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1662 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1664 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1666 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1668 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1670 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1672 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1674 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1676 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1678 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1680 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1682 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1684 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1686 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1688 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1690 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1692 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1694 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1696 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1698 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1700 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1702 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1704 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1706 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1708 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1710 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1712 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1714 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1716 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1718 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1720 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1722 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1724 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1726 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1728 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1730 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1732 TIME_WAIT
TCP appserver1:1049 appserver1abc.co.uk:1734 TIME_WAIT
UDP appserver1:microsoft-ds *:*
UDP appserver1:isakmp *:*
UDP appserver1:1039 *:*
UDP appserver1:4500 *:*
UDP appserver1:6050 *:*
UDP appserver1:8081 *:*
UDP appserver1:8082 *:*
UDP appserver1:41524 *:*
UDP appserver1:ntp *:*
UDP appserver1:netbios-ns *:*
UDP appserver1:netbios-dgm *:*
UDP appserver1:ntp *:*
UDP appserver1:1025 *:*
UDP appserver1:1052 *:*
UDP appserver1:1113 *:*
UDP appserver1:1164 *:*
C:\Documents and Settings\oradba.DEV>
|
|
|
|
Re: ORA-12154 Error [message #436575 is a reply to message #436570] |
Thu, 24 December 2009 04:49 |
chetanaZ
Messages: 132 Registered: October 2009 Location: UK
|
Senior Member |
|
|
Thanks Michel
I have checked each and every condition listed there
Tnsnames.ora
Listener.ora
sqlnet.ora
I am not at all using LDAP
I have listed its content in my mail
I have also checked for any setting of tns_admin in registry as well system and user environment variables, but no such variable is configured
I have traces the listener and following is the output after traced twice
I am really stuck now
Thanks and Regards,
Chetana
Started Trace and tried to connect as system/oracle@db01.world from 2 different sessions
C:\Documents and Settings\oradba.DEV>lsnrctl
LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 - Production on 24-DEC-2009 10:32:20
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> set trc_level ADMIN
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=appserver1)(PORT=1521)))
LISTENER parameter "trc_level" set to admin
The command completed successfully
C:\Documents and Settings\oradba.DEV>E:
E:\ora9_home\network\trace>trcasst listener.trc
Trace Assistant Utility: Version 9.2.0.1.0 Production on 24 December 2009 10:35:13
(c) Copyright 2002 Oracle Corporation. All rights reserved.
*************************************************************************
* Trace Assistant *
*************************************************************************
///////////////////////////////////////////////////////////////
Error found. Error Stack follows:
id:3
Operation code:68
NS Error 1:12537
NS Error 2:12560
NT Generic Error:507
Protocol Error:0
OS Error:0
NS & NT Errors Translation
12537, 00000 "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.
/
12560, 00000 "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors.For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.
/
00507, 00000 "Connection closed"
// *Cause: Normal "end of file" condition has been reached; partner has
// disconnected.
// *Action: None needed; this is an information message.
/
///////////////////////////////////////////////////////////////
----------------------
Trace File Statistics:
----------------------
Total number of Sessions: 0
DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES
ORACLE NET SERVICES:
Total Calls : 0 sent, 0 received, 0 oci
Total Bytes : 0 sent, 0 received
Average Bytes: sent per packet, received per packet
Maximum Bytes: 0 sent, 0 received
Grand Total Packets: 0 sent, 0 received
*************************************************************************
* Trace Assistant has completed *
*************************************************************************
Restarted Listener and tried to connect as system/oracle@db01.world from 2 different sessions
E:\ora9_home\network\trace>trcasst listener.trc
Trace Assistant Utility: Version 9.2.0.1.0 Production on 24 December 2009 10:37:46
(c) Copyright 2002 Oracle Corporation. All rights reserved.
*************************************************************************
* Trace Assistant *
*************************************************************************
///////////////////////////////////////////////////////////////
Error found. Error Stack follows:
id:0
Operation code:78
NS Error 1:12612
NS Error 2:0
NT Generic Error:0
Protocol Error:0
OS Error:0
NS & NT Errors Translation
00000, 00000, "Not An Error"
// *Cause: Everything is working as it should.
// *Action: Dont worry: Be happy.
/
00000, 00000 "Not An Error"
// *Cause: Everything is working as it should.
// *Action: Dont worry: Be happy.
/
00000, 00000 "Not An Error"
// *Cause: Everything is working as it should.
// *Action: Dont worry: Be happy.
/
///////////////////////////////////////////////////////////////
----------------------
Trace File Statistics:
----------------------
Total number of Sessions: 0
DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES
ORACLE NET SERVICES:
Total Calls : 0 sent, 0 received, 0 oci
Total Bytes : 0 sent, 0 received
Average Bytes: sent per packet, received per packet
Maximum Bytes: 0 sent, 0 received
Grand Total Packets: 0 sent, 0 received
*************************************************************************
* Trace Assistant has completed *
*************************************************************************
E:\ora9_home\network\trace>
|
|
|
Re: ORA-12154 Error [message #436597 is a reply to message #436570] |
Thu, 24 December 2009 06:28 |
chetanaZ
Messages: 132 Registered: October 2009 Location: UK
|
Senior Member |
|
|
Hi,
The issue is resolved
I have configured TNS_ADMIN environment variable with the <oracle_home>\network\admin\ and I am able to connect now
Still not sure why I needed to do that
Many Thanks for your help
Thanks and Regards,
Chetana
|
|
|
Goto Forum:
Current Time: Thu Dec 05 01:57:39 CST 2024
|