Solved

Problems with Listener service in Oracle 10.2.0 XE

Posted on 2009-07-07
14
1,404 Views
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.

Peace

C:\>lsnrctl status
 
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 07-JUL-2009 16:07
:08
 
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
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...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE_LAW)(PORT=1521
)))
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

0
Comment
Question by:Darktan
[X]
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
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
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
0
 
LVL 48

Expert Comment

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

Author Comment

by:Darktan
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.

0
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!

 

Author Comment

by:Darktan
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.
0
 
LVL 74

Expert Comment

by:sdstuber
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?
0
 

Author Comment

by:Darktan
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.
0
 
LVL 74

Expert Comment

by:sdstuber
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
0
 

Author Comment

by:Darktan
ID: 24803892
Using the scott/tiger or any of the users for the Oracle database I get this.
C:\>sqlplus /
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul
Copyright (c) 1982, 2005, Oracle.  All rights reserve
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Enter user-name: scott
Enter password:
ERROR:
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 10.2.0.1.0 - 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. :)
0
 

Author Comment

by:Darktan
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)
0
 
LVL 74

Accepted Solution

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

oracle_sid should be XE
0
 

Author Comment

by:Darktan
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.

Peace
0
 

Author Closing Comment

by:Darktan
ID: 31600813
Perfect answer.
0
 
LVL 74

Expert Comment

by:sdstuber
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

729 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