My problem:
I tried connecting to my XE (10g) database via MSQuery (in Excel). I got a listener error. (ORA-12541: TNS:no listener Driver's SQLSetConnectAttr failed)
A few notes, first...
1) I've definitely been able to query my XE database in the past this way... (and I haven't made any changes since then)
2) All day I have been using SQL*Plus to query my XE database, with no problems, even right now.
3) I only have one database installed on my computer (it is on my local machine), and I have never had any other database installed.
4) I only have one tnsnames.ora file and one listener.ora file.
5) My local machine is running Windows XP
So, first I went to the command line and tried tnsping XE
and I got:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =myhostname)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
TNS-12541: TNS:no listener
Next, I checked my tnsnames.ora file and my listener.ora file.
My tnsnames.ora file had the hostname, and listener.ora had an IP address. I changed tnsnames to the IP address and tried tnsping XE again. No luck, same error.
Next, I started LSNRCTL from the command line. Here is what I did there:
LSNRCTL> show current listener
Current Listener is LISTENER
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myipaddress)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
LSNRCTL> start listener
Starting tnslsnr: please wait...
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
Next I went to Control Panels --> Administrative Tools --> Services
I found OracleXETNSListener and clicked 'Start the service'.
Then, I got the message: "The OracleXETNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service."
I changed tnsnames.ora back to the hostname (instead of the IP address) and repeated the steps with LSNRCTL and Control Panels, and got the same errors.
Help please?
Edited by: user11033437 on Jun 27, 2011 4:20 PM: added computer OS