4 Managing Network Connections

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

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 the SQL Command Line.

  • Remote connection from a Java application that connects with JDBC.

HTTP

8080

Database connections using the HTTP protocol. Examples include:

  • Accessing Oracle Application Express applications that you create on the local system.

  • 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 connection 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 Oracle Database XE graphical user interface until you enable remote HTTP connections. See "Enabling Remote HTTP Connection to the Database" for instructions.

Viewing Listener Status

You view 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:

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

  2. On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".

  3. 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=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
Start Date                08-MAR-2011 16:12:26
Uptime                    0 days 1 hr. 57 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File
C:\oraclexe\app\oracle\diag\tnslsnr\user1-pc\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=user1-pc.example.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=user1-pc.example.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, 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" 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 stop the listener:

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

  2. On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".

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

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

  2. On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".

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

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

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 the SQL Command Line" must explicitly include the port number.

For example, if you change the port number for database connection requests to 1522, subsequent the 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.example.com:1522

Example: Changing Listener Port Number for Database Connection Requests

Assume that your Oracle Database XE host computer is named myhost.example.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:

  1. Stop the listener.

    See "Stopping and Starting the Listener" for instructions.

  2. 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/11.2.0/server/network/admin/

    Windows

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


  3. Locate the following 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.

  4. Change the text (PORT = 1521) to (PORT = 1522).

  5. Save the modified listener.ora file.

  6. Start the listener.

    See "Stopping and Starting the Listener" for instructions.

  7. Start the SQL Command Line and connect to the database as user SYSTEM.

    See "Connecting Locally with the 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.

  8. Enter the following two commands:

    ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.example.com)(PORT=1522))";
    
    ALTER SYSTEM REGISTER;
    
  9. Exit the 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 Oracle Application Express login page locally:

http://127.0.0.1:8087/apex

Note:

When you change the listener port number for HTTP, the Get Started 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:

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

  2. On Linux, ensure that environment variables are set according to the instructions in "Setting Environment Variables on the Linux Platform".

  3. Ensure that the listener is started.

    See "Viewing Listener Status" and "Stopping and Starting the Listener" for instructions.

  4. Enter the following command at the operating system prompt to start the SQL Command Line:

    sqlplus /nolog
    
  5. 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.

  6. 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.
    
  7. Exit the 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.

  8. (Optional) To enable the Get Started command on the desktop to work with the new port number, change the port number in the script or shortcut that this command uses. The following table shows the script or shortcut that you must change on each platform.

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

Enabling 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 applications that you create using Oracle Application Express on the local system. 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.

Enabling Remote HTTP Connection with the SQL Command Line

To enable remote HTTP connection requests using the SQL Command Line:

  1. Start the 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 the SQL Command Line" or "Connecting Remotely with the SQL Command Line" for instructions.

  2. At the SQL Command Line prompt, enter the following command:

    EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);