Sunday, September 16, 2007

Oracle XE 10.2.0.1.0 - Connectivity Problems

After to have experienced some connectivity problems with Oracle XE in the following lines some suggestion found looking around on the web:

Oracle XE has it's own forum - to register and discuss XE problems with XE experts use URL http://www.oracle.com/technology/xe/registration

A few things to think about when troubleshooting XE connectivity problems:

1) Listener must be up (first)
2) Database must be up
3) Listener must recognize database
4) Listener must be monitoring for APEX (Home page)
5) Port for Apex must be available (8080, the default, is also default for tomcat and others)
6) All Oracle admin stuff must be handled by a user in the right group (ORA_DBA group in Windows, DBA in Linux). The group must have been set up using a local administrator (administrator, root) that can update the registry, create groups, and write to the disk.

I'll walk thru the steps of getting it all running in Linux - assuming successful install. Listener and database are services so use control panel appropriately. I start with database and listener down and show the various outputs

oracle@fuzzy:~> lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAR-2006 07:58:27
Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 2: No such file or directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fuzzy)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

Start the listener. Use control panel in Windows

oracle@fuzzy:~> lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAR-2006 08:00:45

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr: please wait...


TNSLSNR for Linux: Version 10.2.0.1.0 - Production

System parameter file is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Log messages written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))

STATUS of the LISTENER

------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 16-MAR-2006 08:00:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE

Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Make sure the database is running. Idle instance is not good

oracle@fuzzy:~> sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 16 08:02:59 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> rem start the database. Use control panel in WIndows

SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1258488 bytes
Variable Size 92277768 bytes
Database Buffers 192937984 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Check if listener knows about DB and APEX

oracle@fuzzy:~> lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAR-2006 08:04:56
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 Linux: Version 10.2.0.1.0 - Production

Start Date 16-MAR-2006 08:00:45
Uptime 0 days 0 hr. 4 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

Great; it has a handle for database (XE) and Apex (XEXDB) but Apex is on 8080, which conflicts with Tomcat.Move Apex

oracle@fuzzy:~> sqlplus system/oracle

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 16 08:06:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> exec dbms_xdb.sethttpport(8090);


PL/SQL procedure successfully completed.


SQL> select dbms_xdb.gethttpport() from dual;

DBMS_XDB.GETHTTPPORT()
----------------------
8090
SQL> exit

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Check that Apex is on 8090

oracle@fuzzy:~> lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 16-MAR-2006 08:08:52

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 Linux: Version 10.2.0.1.0 - Production
Start Date 16-MAR-2006 08:00:45
Uptime 0 days 0 hr. 8 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fuzzy.forbrichcomputing.ca)(PORT=8090))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

now check http://localhost:8090/apex/ with a browser

If you cannot reach the remote server, there is a useful trick using a ssh tunnel:

ssh -L 8090:localhost:8090 user@IP_of_your_server

This can be done recursively and permit to reach the remote server as you are there :-)

No comments: