4 Managing Network Connections

This section explains how to manage net work connections to the database. It includes the following topics:

About Network Connections and the Oracle Net Listener
Viewing Listener Status
Stopping and Starting the Listener
Changing Listener Port Numbers
Enabling Remote HTTP Connection to the Database

See Also:

About Network Connections and the Oracle Net Listener

Oracle Database Express Edition (Oracle Database XE) supports connections from client applications both remotely over the network and locally. Remote client applications and the database communicate through Oracle Net, which is a software layer that resides both on the remote computer and on the Oracle Database XE host computer. Oracle Net establishes the connection between the client application and the database, and exchanges messages between them using TCP/IP. Oracle Net is automatically installed when you install Oracle Database XE and Oracle Database Express Edition Client.

Included with Oracle Net in an Oracle Database XE installation is the Oracle Net listener, commonly known as the listener. It is the host process that listens on specific TCP/IP ports for connection requests. When the listener receives a valid connection request from a client application, it routes the connection request to the database. The client application and the database then communicate directly.

Table 4-1 lists the types of connection requests that the listener handles.

Table 4-1 Types of Connection Requests Handled by the Listener

Connection Request Type	Default TCP Port Number	Used For
Database	1521	Database connections using Oracle Net over TCP/IP. Examples include: Remote connection from SQL Command Line. Remote connection from a Java application that connects with JDBC.
HTTP	8080	Database connections using the HTTP protocol. Examples include: Accessing the Database Home Page, locally or remotely. See "Accessing the Database Home Page" for more information. Accessing the Oracle XML DB repository. Oracle XML DB is the Oracle Database XE feature that provides high-performance, native XML storage and retrieval. Through the Oracle XML DB repository, you can access XML data with the HTTP and WebDAV (Web folder) protocols. See Oracle XML DB Developer's Guide for more information.

Connection Request Type

Default TCP Port Number

Used For

Database

1521

Database connections using Oracle Net over TCP/IP. Examples include:

Remote connection from SQL Command Line.
Remote connection from a Java application that connects with JDBC.

HTTP

8080

Database connections using the HTTP protocol. Examples include:

Accessing the Database Home Page, locally or remotely. See "Accessing the Database Home Page" for more information.
Accessing the Oracle XML DB repository. Oracle XML DB is the Oracle Database XE feature that provides high-performance, native XML storage and retrieval. Through the Oracle XML DB repository, you can access XML data with the HTTP and WebDAV (Web folder) protocols. See Oracle XML DB Developer's Guide for more information.

Note:

The listener can also handle FTP c onnection requests for the Oracle XML DB repository. For security reasons, FTP requests are disabled when you install Oracle Database XE. See Oracle XML DB Developer's Guide for more information.

You can disable certain types of connection requests by manually stopping the listener, and reenable them by restarting the listener. (The listener is automatically started when you install Oracle Database XE and when you restart the Oracle Database XE host computer.)

Table 4-2 indicates the types of connections that require the listener to be started.

Table 4-2 Connections That Require the Listener

Connection Type	Local Connection	Remote Connection
Database	Not required	Required
HTTP	Required	Required

As the table shows, stopping the listener disables all connection requests except local database connection requests.

Configuring the Listener

You can change the ports that the listener listens on, both for database and HTTP connection requests, either during the Oracle Database XE installation process, or at a later time after installation. See "Changing Listener Port Numbers" for details on changing port numbers after installation.

Note:

The Windows installation process prompts for the port number for HTTP requests only if the default port number, 8080, is already in use. The Linux configuration script always prompts for HTTP port number.

Remote HTTP Connections Initially Disabled

As a security measure, remote HTTP connection requests are initially disabled. This means that remote users cannot use the the Oracle Database XE graphical user interface until you enable remote HTTP connections. See "Enabling Remote HTTP Connection to the Database" for instructions.

See Also:

"Stopping and Starting the Listener"
"Changing Listener Port Numbers"
"Connecting to the Database"
"Viewing Listener Status"

Viewing Listener Status

You vie w listener status to determine if the listener is started and to check listener properties (such as the TCP/IP port numbers that the listener is listening on). You do so with the Listener Control (lsnrctl) utility.

To view listener status:

Do one of the following:
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environm ent variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
Enter the following command:
```
LSNRCTL STATUS
```

If the listener is not started, the command displays the following error messages:

TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener

If the listener is started, the command displays a report that looks something like this:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                10-DEC-2005 19:06:12
Uptime                    2 days 3 hr. 39 min. 32 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=tcp)(HOST=myhost.mydomain.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(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

In the preceding report, the bold text indicates particulars to look for. Their meanings are as follows:

(PORT=1521)

The listener is listening for database connections through Oracle Net on port 1521.
(PORT=8080))(Presentation=HTTP)

The listener is listening for database connections through HTTP on port 8080.
Service "XE" has 1 instance(s) and Instance "XE", status READY

Oracle Database XE is properly registered with the listener and is ready to accept connections.

Stopping and Starting the Listener

The listener is configured to start automatically when you install Oracle Database Express Edition (Oracle Database XE), and whenever the computer running Oracle Database XE is restarted. The following are reasons why you may want to stop and restart the listener:

To recover from system errors
To temporarily block remote connection requests

You stop the listener to disable remote connection requests, and restart the listener to enable them.
To change the TCP port number that the listener listens on

See "Changing Listener Port Numbers" for more information.

Stopping the Listener

To st op the listener:

Do one of the following:
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".

Enter the following command:

LSNRCTL STOP

The command displays the following output if successful.

On Linux:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521)))
The command completed successfully

On Windows:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

If the listener was already stopped, the command displays one or more TNS: no listener messages.

Starting the Listener

To start the listener:

Do one of the following:
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
Enter the following command:
```
LSNRCTL START
```
If successful, the command displays the report shown in "Viewing Listener Status".

Note:

If you stop and then start the listener while the database is running, it may take a minute or so for the database to reregister with the listener and to begin accepting connections. To determine if the database is ready to accept connections, run the lsnrctl status command repeatedly until you see the following lines in the report:

Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...

See Also:

"Viewing Listener Status"

Changing Listener Port Numbers

You would need to change a default listener port number only if there were a port number conflict with another TCP/IP service. You are given the opportunity to change listener port numbers during installation (Windows) or configuration (Linux). This section explains how to change port numbers after installation or configuration. It contains the following topics:

"Changing the Listener Port Number for Database Connection Requests"
"Changing the Listener Port Number for HTTP Connection Requests"

Changing the Listener Port Number for Database Connection Requests

If you change the listener port number for database connection requests, you must ensure that all future database connection requests use the new port number. This means that connection requests such as those discussed in "Connecting Remotely with SQL Command Line" must explicitly include the port number.

For example, if you change the port number for database connection requests to 1522, subsequent SQL Command Line (SQL*Plus) connect statements must be similar to the following (assuming a connection from Oracle Database Express Edition Client):

connect system/mypassword@myhost.mydomain.com:1522

Example: Changing Listener Port Number for Database Connection Requests

Assume that your Oracle Database XE host computer is named myhost.mydomain.com and that you want to install a new software package on this computer that requires TCP port number 1521. Assume also that the port number for that software package cannot be configured, and that you must therefore resolve the port number conflict by reconfiguring Oracle Database XE. You decide to change the listener port number for database connection requests to 1522.

To change the listener port number for database connection requests to 1522:

Stop the listener.

See "Stopping and Starting the Listener" for instructions.
Open the file listener.ora with a text editor.

Table 4-3 shows the location of this file on each platform.

Table 4-3 Location of the listener.ora File

Platform Location

Linux

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/

Windows

c:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\

Platform	Location
Linux	/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/
Windows	c:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\

Locate the followin g section of the file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
  )

Note that the line indicated in bold may or may not be present in the file.

Change the text (PORT = 1521) to (PORT = 1522).
Save the modified listener.ora file.
Start the listener.

See "Stopping and Starting the Listener" for instructions.
Start SQL Command Line and connect to the database as user SYSTEM.

See "Connecting Locally with SQL Command Line" for instructions. You must supply the SYSTEM password. You set this password upon installation (Windows) or configuration (Linux) of Oracle Database XE.

Enter the following two commands:

ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.com)(PORT=1522))";

ALTER SYSTEM REGISTER;

Exit SQL Command Line and run the lsnrctl status command to verify the port number change.

The new port number should be displayed in the Listening Endpoints Summary section of the status report, and the report should include the following lines:
```
Service "XE" has 1 instance(s).  Instance "XE", status READY, has 1 handler(s) for this service...
```

Changing the Listener Port Number for HTTP Connection Requests

If you change the listener port number for HTTP connection requests, you must ensure that all future HTTP connection requests use the new port number.

For example, if you change the listener port number for HTTP requests to 8087, you must use the following URL to access the Database Home Page locally:

http://127.0.0.1:8087/apex

Note:

When you change the listener port number for HTTP, the Go To Database Home Page menu item on the desktop can no longer open the Database Home Page. The following procedure contains an optional step that explains how to modify this menu item to function with the new port number.

To change the listener port number for HTTP connection requests:

Do one of the following:
- On Linux: Start a terminal session and log in to the Oracle Database XE host computer with the oracle user account.
- On Windows: Log in to the Oracle Database XE host computer as the user who installed Oracle Database XE, and then open a command window.
On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".
Ensure that the listener is started.

See "Viewing Listener Status" and "Stopping and Starting the Listener" for instructions.
Enter the following command at the operating system prompt to start SQL Command Line:
```
SQLPLUS /NOLOG
```
At the SQL Command Line prompt, enter the following command:
```
CONNECT SYSTEM/password
```
where password is the SYSTEM password that you set upon installation (Windows) or configuration (Linux) of Oracle Database XE.
At the SQL Command Line prompt, enter the following command:
```
EXEC DBMS_XDB.SETHTTPPORT(nnnn);
```
where nnnn represents the new port number to use for HTTP connection requests. Be certain that you select a port number that is not already in use.

For example, to use port number 8087 for HTTP connection requests, enter this command:
```
EXEC DBMS_XDB.SETHTTPPORT(8087);
```
If the command is successful, the following message is displayed:
```
PL/SQL procedure successfully completed.
```
Exit SQL Command Line (by entering the exit command) and view listener status to verify the port number change.

See "Viewing Listener Status" for instructions. The new port number is displayed in the Listening Endpoints Summary section of the status report.
(Optional) To enable the Go To Database Home Page command on the desktop to work with the new port number, change the port number in the script that this command runs. The following table shows the scripts that you must change on each platform.

Platform	Script to Change When Changing the Listener Port for HTTP Connection Requests
Linux	/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/DatabaseHomePage.sh
Windows	C:\oraclexe\app\oracle\product\10.2.0\server\Database_homepage.url

See Also:

"Accessing the Database Home Page"

Enab ling Remote HTTP Connection to the Database

After installation, database connection requests with the HTTP protocol are enabled only on the computer on which you installed Oracle Database XE. This means that remote users cannot access the Oracle Database XE graphical user interface (including the Database Home Page). As an administrator, you can enable HTTP access for remote users, thereby enabling them to access the Oracle Database XE graphical user interface.

Security Note:

With remote HTTP access to Oracle Database XE, all information exchanged between the browser and the database is in clear text—that is, unencrypted—including database user names and passwords. If this is cause for concern, do not enable remote HTTP connection to the database.

There are two ways to enable remote HTTP database connection requests:

From the Manage HTTP Access page in the Oracle Database XE graphical user interface
From SQL Command Line, using a PL/SQL package procedure

Enabling Remote HTTP Connection with the Manage HTTP Access Page

To enable remote HTTP connection requests using the Manage HTTP Access page:

Access the Database Home Page.

See "Accessing the Database Home Page" for instructions.
Click the Administration icon.

The Administration page appears.
In the Tasks list at the right of the page, click Manage HTTP Access.

If prompted for administrator credentials, enter the SYSTEM user name and password or another administrator user name and password, and then click Login. See "About Administrative Accounts and Privileges" for more information.

The Manage HTTP Access page appears.

Description of the illustration manage_access.gif
Select Available from local server and remote clients, and then click Apply Changes.

Enabling Remote HTTP Connection with SQL Command Line

To enable remote HTTP connection requests using SQL Command Line:

Start SQL Command Line and connect to the database as user SYSTEM. Provide the SYSTEM password that you assigned upon installation (Windows) or configuration (Linux) of Oracle Database XE.

See "Connecting Locally with SQL Command Line" or "Connecting Remotely with SQL Command Line" for instructions.
At the SQL Command Line prompt, enter the following command:
```
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
```