Problems with Listener service in Oracle 10.2.0 XE

Posted on 2009-07-07
Last Modified: 2013-12-19
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

Question by:Darktan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
LVL 74

Expert Comment

ID: 24803215
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
LVL 48

Expert Comment

ID: 24803271
You have to turn off all firewalls and antiviruses.
Possibly they block some ports.

Author Comment

ID: 24803285
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.

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 24803304
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.
LVL 74

Expert Comment

ID: 24803398
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?

Author Comment

ID: 24803504
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.
LVL 74

Expert Comment

ID: 24803633
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

Author Comment

ID: 24803892
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. :)

Author Comment

ID: 24803919
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)
LVL 74

Accepted Solution

sdstuber earned 250 total points
ID: 24803976
sqlplus is running so your oracle home must be in your path

oracle_sid should be XE

Author Comment

ID: 24804035
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.


Author Closing Comment

ID: 31600813
Perfect answer.
LVL 74

Expert Comment

ID: 24804068
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question