Thanks for the question, John.
Asked: March 31, 2016 - 9:58 pm UTC
Last updated: April 05, 2016 - 2:05 am UTC
Version: 11g
Viewed 10K+ times! This question is 
You Asked
After weekend reboot of Windows 7 DB server I'm unable to connect to DB using sqlplsu or SQL Developer
Database server is 11g with two installs of Oracle:
ORACLE_HOME=C:\DATABASE\app\oracle\product\11.2.0\dbhome_1
GMRADHOST database
ORACLE_HOME=C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2
Q3CET database
Per earlier response from Ask Tom about connection issue to GMRADHOST (11.2.0) version.
You recomended:
...
Pick your highest version number, and use its listener. You only need 1 listener.
...
explcitly configure the databases in listener.ora (in C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\network\admin).
You can use netca (ora => config mgt => net config assist) to delete existing listeners and start from scratch,
so that the appropriate windows services are cleaned up/re-initialised.
I tried this advice but still can connect.
For Q3CET, I can connect in sqlplus using:
sqlplus sys/***** as sysdba
sqlplus system/*****
sqlplus scott/*****
But when I try to test the connections to Q3CET in SQL Developer I get the following:
UserName: sys Password: ****** Role: SYSDBA Connect String: sys@//uswrntdmsr16040.nam.corp.gm.com:1522/Q3CET
UserName: system Password: ****** Connect String: system@//uswrntdmsr16040.nam.corp.gm.com:1522/Q3CET
UserName: scott Password: ****** Connect String: SCOTT@//uswrntdmsr16040.nam.corp.gm.com:1522/Q3CET
They all return: ORA-12505
Status : Failure -Test failed: Listener refused the connection.... ORA-12505, TNS:Listener does not currently know of SID in connect desc
For GMRADHOST, when I try to connect in sqlplus using:
sqlplus sys/##### as sysdba
ORA-01017: invalid username/password; logon denied
sqlplus sys/#####@GMRADHOST as sysdba
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
sqlplus system/#####@USWRNTDMSR16040.nam.corp.gm.com:1521/GMRADHOST.nam.corp.gm.com
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
When I try to test the connections to GMRADHOST in SQL Developer I get the following:
UserName: sys Password: ****** Role: SYSDBA Connect String: sys@//uswrntdmsr16040.nam.corp.gm.com:1521/gmradhost.nam.corp.gm.com
UserName: system Password: ****** Connect String: system@//uswrntdmsr16040.nam.corp.gm.com:1521/gmradhost.nam.corp.gm.com
UserName: john Password: ****** Connect String: john@//uswrntdmsr16040.nam.corp.gm.com:1521/gmradhost.nam.corp.gm.com
They all return: ORA-12514
Status : Failure -Test failed: Listener refused the connection.... ORA-12514, TNS:Listener does not currently know of service requested
in connect desc
echo %ORACLE_HOME% C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2
echo %ORACLE_SID% Q3CET
echo %TNS_ADMIN% C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN
I'm using the following listener.ora in: C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN
# listener.ora Network Configuration File: C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_Q3CET =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\bin\oraclr11.dll")
)
)
LISTENER_GMRADHOST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER_GMRADHOST = C:\DATABASE11204\app\oracle
LISTENER_Q3CET =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
ADR_BASE_LISTENER_Q3CET = C:\DATABASE11204\app\oracle
I'm using the following tnsnames.ora in: C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN
# tnsnames.ora Network Configuration File: C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Q3CET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = q3cet.nam.corp.gm.com)
)
)
GMRADHOST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gmradhost.nam.corp.gm.com)
)
)
NETCA didn't generate a sqlnet.ora file in: C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN
For the ORACLE_HOME=C:\DATABASE\app\oracle\product\11.2.0\dbhome_1
In Dir: C:\DATABASE\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN
I have an old listener.ora file that I don't think is referenced.
And in tnsnames.ora I have:
# tnsnames.ora Network Configuration File: C:\DATABASE\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
Q3CET =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = q3cet.nam.corp.gm.com)
)
)
GMRADHOST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = USwrntdmsr16040.nam.corp.gm.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gmradhost.nam.corp.gm.com)
)
)
#LISTENER_GMRADHOST =
# (ADDRESS = (PROTOCOL = TCP) (HOST = USWRNTDMSR16040.nam.corp.gm.com)(PORT = 1521))
And in sqlnet.ora I have:
# sqlnet.ora Network Configuration File: C:\DATABASE\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.
# 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)
ADR_BASE = C:\DATABASE\app\oracle\product\11.2.0\dbhome_1\log
Here is the LSNRCTL info, I concerns me that the default listener and LISTENER_GMRADHOST have q3cet refrerences:
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_GMRADHOST
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2016 15:38:03
Uptime 0 days 2 hr. 11 min. 59 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN\listener.ora
Listener Log File C:\DATABASE11204\app\oracle\diag\tnslsnr\USWRNTDMSR16040\listener_gmradhost\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uswrntdmsr16040.nam.corp.gm.com)(PORT=1521)))
Services Summary...
Service "q3cet.nam.corp.gm.com" has 1 instance(s).
Instance "q3cet", status READY, has 1 handler(s) for this service...
Service "q3cetXDB.nam.corp.gm.com" has 1 instance(s).
Instance "q3cet", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status LISTENER_Q3CET
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=USwrntdmsr16040.nam.corp.gm.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_Q3CET
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2016 15:56:29
Uptime 0 days 1 hr. 53 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN\listener.ora
Listener Log File C:\DATABASE11204\app\oracle\diag\tnslsnr\USWRNTDMSR16040\listener_q3cet\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uswrntdmsr16040.nam.corp.gm.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> status LISTENER_GMRADHOST
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=USwrntdmsr16040.nam.corp.gm.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER_GMRADHOST
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 31-MAR-2016 15:38:03
Uptime 0 days 2 hr. 12 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2\NETWORK\ADMIN\listener.ora
Listener Log File C:\DATABASE11204\app\oracle\diag\tnslsnr\USWRNTDMSR16040\listener_gmradhost\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=uswrntdmsr16040.nam.corp.gm.com)(PORT=1521)))
Services Summary...
Service "q3cet.nam.corp.gm.com" has 1 instance(s).
Instance "q3cet", status READY, has 1 handler(s) for this service...
Service "q3cetXDB.nam.corp.gm.com" has 1 instance(s).
Instance "q3cet", status READY, has 1 handler(s) for this service...
The command completed successfully
Sorry for such a long post but I'm stumped and new to ORACLE DBA work.
Thanks for any advise.
and Connor said...
OK, hopefully this helps with understanding
ORA-12505, TNS:Listener does not currently know of SID in connect desc
means you successfully *contacted* the listener, but you asked for "abc" and the listner said "I dont know what abc is".
So your listener is up and running, it just doesn't have enough information.
When a database starts, it will try to register with a listener to let it know of its existence. It does this by default on port 1521 on the local host address. This is normally sufficient. Alternatively, you can configure your listener to explicitly tell it what database facilities it needs to know about via listener.ora
So in your case,
ORACLE_HOME=C:\DATABASE\app\oracle\product\11.2.0\dbhome_1
GMRADHOST database
ORACLE_HOME=C:\DATABASE11204\app\oracle\product\11.2.0.4\dbname_2
Q3CET database
You only need 1 listener, let's call it "LISTX". It will run out of the 11.2.0.4 installation area.
So in the 11.2.0.4 network/admin, your listener.ora could be (for example):
SID_LIST_LISTX =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oracle\product\11.2.0.4)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\11.2.0.4\bin\oraclr12.dll")
)
)
LISTX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
That's pretty much the default - notice there's nothing really in there about database. Hence I'm relying on any database to register itself with this listener, which it starts.
Alternatively, I can tell the listener *more* info, for example:
SID_LIST_LISTX =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oracle\product\11.2.0.4)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\11.2.0.4\bin\oraclr12.dll")
)
(SID_DESC =
(SID_NAME = mydb11)
(ORACLE_HOME = C:\oracle\product\11.2.0.1)
(ORACLE_SID = mydb11)
)
)
which says "there will be an instance called mydb11 running out of my 11.2.0.1 installation that you need to take care of".
So even if mydb11 is down (or even non-existent), the listener has been told "if someone askes for mydb11, they are asking for an instance called mydb11 running out of 11.2.0.1 installation"
(There's a bit more to it that what I've covered here...but hopefully that will get you started).
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment