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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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)
sqlplus is running so your oracle home must be in your path

oracle_sid should be XE

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.