Cannot connect to local database even if Listener and DB Service are UP
752002Nov 15 2011 — edited Nov 17 2011I am having a problem connecting to the Oracle database installed on my machine. I have been using SQL Developer to access it for the longest time, but now it just hangs at the "connecting" screen. Trying SQLPlus on the command prompt produces the same result, it just hangs.
The service name is CCBV22DB. Running TNSPing is successful:
C:\>tnsping CCBV22DB
TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-NOV-2
*011 16:07:55*
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
C:\oracle\product\11.1.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
*(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CCBV22DB)))*
OK (0 msec)
C:\>
But logging into SQLPlus this way ends up with a login hang:
C:\>sqlplus cisadm/cisadm@CCBV22DB
SQLPlus: Release 11.1.0.6.0 - Production on Tue Nov 15 16:11:20 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
It doesn't error out or anything, and just hangs with no prompt.
Here is the status of the listener:
C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-NOV-2011 16:12
*:14*
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date 15-NOV-2011 15:47:58
Uptime 0 days 0 hr. 24 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\11.1.0\db_1\network\admin\listener.o
ra
Listener Log File c:\oracle\diag\tnslsnr\gsee-ph\listener\alert\log.xml
Listening Endpoints Summary...
*(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gsee-ph.splwg.com)(PORT=1521)))*
Services Summary...
Service "CCBV22DB" has 2 instance(s).
Instance "CCBV22DB", status UNKNOWN, has 1 handler(s) for this service...
Instance "CCBV22DB", status READY, has 1 handler(s) for this service...
Service "CCBV22DBXDB" has 1 instance(s).
Instance "CCBV22DB", status READY, has 1 handler(s) for this service...
Service "CCBV22DB_XPT" has 1 instance(s).
Instance "CCBV22DB", status READY, has 1 handler(s) for this service...
The command completed successfully
C:\>
There is only one way that I have made it work so far, and that is to initialize the ORACLE_SID environment variable, then simply running sqlplus (no username or password). It will then prompt for the username and password, which upon providing, logs me on properly:
C:\>set ORACLE_SID=CCBV22DB
C:\>sqlplus
SQLPlus: Release 11.1.0.6.0 - Production on Tue Nov 15 16:15:51 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: cisadm
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Unfortunately I cannot work with simply this. All my applications that try to access this database, such as SQL Developer, or Oracle Utilities CC&B, are not able to work because of the issue.
Curiously, even if I initialize ORACLE_SID, if I declare the Service Name during login, I will get the same hanging issue:
C:\>set ORACLE_SID=CCBV22DB
C:\>sqlplus cisadm/cisadm@CCBV22DB
SQLPlus: Release 11.1.0.6.0 - Production on Tue Nov 15 16:23:27 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
As for my listener and tnsnames files, I have 2 Oracle Homes that have them:
*1. C:\oracle\ora11g\network\admin*
*2. C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN*
They both have the same content in the listener.ora and tnsnames.ora files, as follows:
In listener.ora:
LISTENER =
*(DESCRIPTION_LIST =*
*(DESCRIPTION =*
*(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))*
*)*
*)*
In tnsnames.ora:
CCBV22DB =
*(DESCRIPTION =*
*(ADDRESS_LIST =*
*(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))*
*)*
*(CONNECT_DATA =*
*(SERVICE_NAME = CCBV22DB)*
*)*
*)*
I hope somebody could advise. Your help is very much appreciated.