Home » RDBMS Server » Networking and Gateways » Unable to connect to database through client (11g, 11.2.0.1, Window server 2008)
| Unable to connect to database through client [message #630488] |
Mon, 29 December 2014 04:37  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
DeaR All,
In an installed client on windows server, one database is connecting through client but other is not connecting. It gives error
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 29 15:55:48 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: sys@tagicthm as sysdba
Enter password:
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
Enter user-name: system@tagicthm
Enter password:
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
Enter user-name: SYSTEM@uicuintg
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
C:\Users\Administrator>lsnrctl
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 29-DEC-2014 15:57
:48
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=UAT-PV-MERG.cmcindia.com
)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 29-DEC-2014 15:19:42
Uptime 0 days 0 hr. 38 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\Administrator\product\11.2.0\dbhome_1\network\a
dmin\listener.ora
Listener Log File f:\app\administrator\diag\tnslsnr\UAT-PV-MERG\listener
\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=UAT-PV-MERG.cmcindia.com)(PORT=1521)
))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.140.128)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "TAGICTHMXDB" has 1 instance(s).
Instance "tagicthm", status READY, has 1 handler(s) for this service...
Service "UICUINTGXDB" has 1 instance(s).
Instance "uicuintg", status READY, has 1 handler(s) for this service...
Service "tagicthm" has 1 instance(s).
Instance "tagicthm", status READY, has 1 handler(s) for this service...
Service "uicuintg" has 1 instance(s).
Instance "uicuintg", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
C:\Users\Administrator>tnsping tagicthm
TNS Ping Utility for 64-bit Windows: Version 11.1.0.6.0 - Production on 29-DEC-2
014 15:59:44
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Administrator\product\11.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.140.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = tagicthm
)))
OK (0 msec)
C:\Users\Administrator>tnsping uicuintg
TNS Ping Utility for 64-bit Windows: Version 11.1.0.6.0 - Production on 29-DEC-2
014 15:59:52
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
D:\app\Administrator\product\11.1.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.140.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = UICUINTG
)))
OK (10 msec)
C:\Users\Administrator>
but same database was connecting through command prompt -
C:\Users\Administrator>path
PATH=D:\app\Administrator\product\11.1.0\client_1\bin;F:\app\Administrator\produ
ct\11.2.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C
:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\Windows System Re
source Manager\bin;;C:\Windows\idmu\common
C:\Users\Administrator>path=F:\app\Administrator\product\11.2.0\dbhome_1\bin
C:\Users\Administrator>set oracle_sid=tagicthm
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 29 16:04:42 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system@tagicthm
Enter password:
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
Enter user-name: system@conf
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select name from v$database;
NAME
---------
TAGICTHM
Please assist me why it is not connecting through client. Same thing was check with 10g client but got same error.
Regards,
Ashish
[Updated on: Mon, 29 December 2014 04:40] Report message to a moderator |
|
|
|
|
|
| Re: Unable to connect to database through client [message #630503 is a reply to message #630502] |
Mon, 29 December 2014 07:19  |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
C:\Users\Administrator>lsnrctl services
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 29-DEC-2014 18:50:17
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=UAT-PV-MERG.cmcindia.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 5 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:0 refused:0
LOCAL SERVER
"DEDICATED" established:0 refused:0
LOCAL SERVER
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_123C_EEFB7AA.ORA))
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_123C_EEFB7AA.ORA))
Service "TAGICTHMXDB" has 1 instance(s).
Instance "tagicthm", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: UAT-PV-MERG, pid: 1752>
(ADDRESS=(PROTOCOL=tcp)(HOST=UAT-PV-MERG.cmcindia.com)(PORT=51354))
Service "UICUINTGXDB" has 1 instance(s).
Instance "uicuintg", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: UAT-PV-MERG, pid: 2328>
(ADDRESS=(PROTOCOL=tcp)(HOST=UAT-PV-MERG.cmcindia.com)(PORT=49222))
Service "tagicthm" has 1 instance(s).
Instance "tagicthm", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "uicuintg" has 1 instance(s).
Instance "uicuintg", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Services & database is up and running...
Even though, unable to connect to database through client...
[Updated on: Mon, 29 December 2014 07:21] Report message to a moderator |
|
|
|
| Re: Unable to connect to database through client [message #630505 is a reply to message #630503] |
Mon, 29 December 2014 07:57 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Al,
I found one issue at tns entry of database/ When I use host name as ip address, it gives error. When I use host name as server name, it proceeded without error. Below is the error and tns entry at database. But what ever use in host name at client side, it gives same error.
May be it would help you to assist me to rectify the problem..
Enter user-name: system@tagicthm
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
C:\Users\Administrator>SQLPLUS
SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 29 19:12:16 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system@conf
Enter password:
ERROR:
ORA-12523: TNS:listener could not find instance appropriate for the client
connection
Below is the tns entry at database ---
TAGICTHM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = UAT-PV-MERG)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TAGICTHM)
)
)
conf =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.128)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TAGICTHM)
)
)
|
|
|
|
|
|
| Re: Unable to connect to database through client [message #630531 is a reply to message #630506] |
Mon, 29 December 2014 22:17 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
Below are the output -
C:\Users\Administrator>ping UAT-PV-MERG
Pinging UAT-PV-MERG.cmcindia.com [::1] with 32 bytes of data:
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Reply from ::1: time<1ms
Ping statistics for ::1:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
C:\Users\Administrator>tnsping TAGICTHM
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 30-DEC-2014 09:46:21
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = UAT-PV-MERG)(PORT = 1521))) (CONNECT_DATA = (SERVICE
_NAME = tagicthm)))
OK (50 msec)
C:\Users\Administrator>tnsping conf
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 30-DEC-2014 09:46:32
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.128)(PORT = 1521))) (CONNECT_DATA = (SER
VICE_NAME = tagicthm)))
OK (0 msec)
[Updated on: Mon, 29 December 2014 22:19] Report message to a moderator |
|
|
|
|
|
| Re: Unable to connect to database through client [message #630533 is a reply to message #630532] |
Mon, 29 December 2014 23:18 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear BlackSwan,
There is only one oracle home 2 databases at server.
I have installed 10g client again to check, whether same problem is persisting or not? Still, the same error persist.
Below is the path-
C:\Users\Administrator>path
PATH=D:\oracle\product\10.2.0\client_1\bin;F:\app\Administrator\product\11.2.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System
32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\Windows System Resource Manager\bin;;C:\Windows\idmu\common
Request you to elaborate your view. May be your valuable inputs may come out with great results.
Regards,
Ashish Kumar Mahanta
|
|
|
|
|
|
|
|
| Re: Unable to connect to database through client [message #630556 is a reply to message #630550] |
Tue, 30 December 2014 03:06 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
It is happening for only one database. Because, client is able to connect to other database with ip address.
C:\Users\Administrator>SQLPLUS
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 30 14:31:49 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: sys@uicuintg as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Tns-entry at both database & client is same -
UICUINTG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.128)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = UICUINTG)
)
)
Parallely, I am taking help of network team to find out the conflict of ip address and host name.
Regards,
Ashish
|
|
|
|
|
|
|
|
| Re: Unable to connect to database through client [message #630571 is a reply to message #630561] |
Tue, 30 December 2014 05:15 |
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
|
Senior Member |
|
|
Dear Michel,
You are correct !!!
After requesting network team to check the issue. Problem is resolved. They check and rectified.
Below are the response of host name
C:\Users\Administrator>ping UAT-PV-MERG
Pinging UAT-PV-Merg [192.168.140.128] with 32 bytes of data:
Reply from 192.168.140.128: bytes=32 time<1ms TTL=128
Reply from 192.168.140.128: bytes=32 time<1ms TTL=128
Reply from 192.168.140.128: bytes=32 time<1ms TTL=128
Reply from 192.168.140.128: bytes=32 time=1ms TTL=128
Ping statistics for 192.168.140.128:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 1ms, Average = 0ms
C:\Users\Administrator>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 30 16:43:29 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: system@conf
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\Administrator>tnsping conf
TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 30-DEC-2014 16:43:49
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.140.128)(PORT = 1521))) (CONNECT_DATA = (SER
VICE_NAME = tagicthm)))
OK (0 msec)
C:\Users\Administrator>
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 18:57:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
