Solved

Problems with Listener service in Oracle 10.2.0 XE

Posted on 2009-07-07
14
1,328 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
  • 7
  • 5
14 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 47

Expert Comment

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

Author Comment

by:Darktan
Comment Utility
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
 

Author Comment

by:Darktan
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
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 73

Accepted Solution

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

oracle_sid should be XE
0
 

Author Comment

by:Darktan
Comment Utility
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
Comment Utility
Perfect answer.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now