I'm working on an application which uses Oracle XE 11g as its RDBMS. It was working for several weeks but now I started to get an error message about the connection being refused.
I restarted my system but it did not help. I uninstalled XE, deleted all remaining files (even from registry) and reinstalled it. Now I get another error message:
Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
I've read about this error and others suggested adding a new SID to the listener.ora file:
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)
If I do this I get another error message:
ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist
My configuration files are: listener.ora
SID_LIST_LISTENER = (SID_LIST =
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
) )
LISTENER = (DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = AAROLD.in.mycompany.com)(PORT = 1521))
) )
DEFAULT_SERVICE_LISTENER = (XE)
tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = AAROLD.in.mycompany.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
lsnrctl status returns with:
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 19-M┴RC. -2013 15:12:24
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date 19-M┴RC. -2013 15:01:12
Uptime 0 days 0 hr. 11 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\AAROLD\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AAROLD.in.mycompany.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Do you have any idea what can be the problem? I'm not an Oracle expert so I'm completely in the dark here.
I had the exact same problem and after spending almost 4 hours trying to find and answer, finally adding
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
)
to my listener.ora and restarting both XE services solved the issue. I am using 11.2.0 (XE) on windows 7 x64 with out any hitch till yesterday. A reboot of my PC this morning caused the listener to not find the SID.
Now I am backing up both listener.ora and tnsnames.ora.
btw, forgot to mention I changed the hostname in both the files from bob.mycompany.com to localhost.
On Windows 7 64 bit, Orcale XE 11 my OracleXXETNSListener service was not running and did terminate immediately when I tried to start it.
(SID_DESC = (SID_NAME = XE) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) )
to my
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listener.ora
changing the hostname to localhost
restarting the OracleServiceXE service under System - Services solved the issue.
So the complete listener.ora section looks like:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\servers\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\servers\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = C:\servers\oraclexe\app\oracle\product\11.2.0\server)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
DEFAULT_SERVICE_LISTENER = (XE)
Update: Now it is 5 years later and I had the same issue with OracleXE 18c on Win10. The TNS listener would not start right after successful installation because oracleXE\dbhomeXE\NETWORK\ADMIN\listener.ora was pointing to localhost.docker.internal instead of localhost.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With