Problems with Listener service in Oracle 10.2.0 XE

I've recently discovered a problem that might have been around since Jun.

We have a small reference database that had to be converted from an NT 4 machine to a W2003 system. It was an Oracle database (7 I believe)  so we just installed XE on a system and did an import. Everything worked fine and no one was more impressed than I considering I've never used Oracle and my SQL skills consist of knowing how to spell it. But it worked fine.

I just got a message from the end user that she can't connect. When I log into the server and attempt to pull up the Database home page I get a Page cannot be displayed error. I checked the services and everything is running. The OracleServiceXE and OracleXETNSListener services are both started. The TNSNAMES.ORA has not changed and is set correctly. No changes have been done, but the host is correct and so is the service name. A lsnrctl status shows the below code (host name changed to protect the innocent). I am most worried about the unknown status's for the services summary but really I'm just trying to figure this out.

I'm experienced in Windows and networking, but sorely lacking in database skills so if you have suggestions assume I don't know what I'm doing because I don't.


C:\>lsnrctl status
LSNRCTL for 32-bit Windows: Version - Production on 07-JUL-2009 16:07
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version - Produ
Start Date                07-JUL-2009 15:31:23
Uptime                    0 days 0 hr. 35 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
  Instance "xe", status BLOCKED, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

Open in new window

Who is Participating?
sqlplus is running so your oracle home must be in your path

oracle_sid should be XE
The problem is the "BLOCKED" status which means the database itself is not accepting connections.  

shutdown and restart your database. you can do that from the windows menus but you may need to shutdown the service through the control panel.

If that doesn't do it, we'll probably need more sophisticated diagnostics but I'd try the simple "reboot" first
You have to turn off all firewalls and antiviruses.
Possibly they block some ports.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DarktanAuthor Commented:
I'll go through and recycle everything again, though that was the first thing I tried (sorry. Should've said).  I even rebooted the machine. But I'll go through the steps again just to make sure I didn't miss something.

DarktanAuthor Commented:
Also all firewalls should be turned off. I have a suspicion that this started back in June when we might have patched the server. I don't know with what off the top of my head though. I'd have to go back and look at the change controls. But it is possible it popped the Windows Firewall back on. I'll check that as well.
I seriously doubt firewalls have anything to do with the problem.

The listener doesn't know about your firewalls.   It has BLOCKED status because the database isn't accepting connections from the listener.

A firewall would prevent you from reaching the listener itself but that would be a separate problem.  

What is the exact error you are getting?
DarktanAuthor Commented:
The user can not use her client to access the database. I don't have anything on my machine to test the connection so I'm just on the server itself which as you say, should rule out firewalls. Trying to pull up the database on the server gets me a 'Page cannot be displayed' error. As someone who knows nothing about Oracle, I assumed the database was down or a service not started. My fumbling troubleshooting seems to point to something else but I'm not sure what. I can pull and post any of the logs or ORA (?) files if it will help. Just let me know what you need or how to get it. As I said in the first message, I really have no idea when it comes to databases.
on the server can you log in with "sqlplus /"   from the command line?

or "sqlplus / as sysdba" if you must  (and you might)

if you don't get logged in post the error.

if you do get logged in post any messages that appear
DarktanAuthor Commented:
Using the scott/tiger or any of the users for the Oracle database I get this.
C:\>sqlplus /
SQL*Plus: Release - Production on Wed Jul
Copyright (c) 1982, 2005, Oracle.  All rights reserve
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Enter user-name: scott
Enter password:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

This is what I get using "sqlplus / as sysdba"
C:\>sqlplus / as sysdba
SQL*Plus: Release - Production on Wed Jul 8 10:04:34 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

Sorry for the hand holding. :)
DarktanAuthor Commented:
I seem to not have any Oracle_SID or Oracle_Home variable defined. Could that be the cause? And if so, what should they be?

(I really need to pick up a database book if I'm going to have to support this stuff)
DarktanAuthor Commented:
That was it. I can now get into the database and see the data. Thank you sdstuber. I know it's tough walking people through when they don't know anything (or just enough to really screw things up). I'm still not sure how the variable get deleted or what happened to break everything, but it seems to be working now.  Thanks again. You were a great help.

DarktanAuthor Commented:
Perfect answer.
glad I could help.  

I do encourage reading through the oracle docs.
They are, I think, some of the better written technical documentation.  The SQL syntax docs could stand a some improvement but the administration docs are generally pretty good.

and of course, you can always ask more questions on EE.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.