Oracle® Database Express Edition 2 Day DBA 10g Release 2 (10.2) Part Number B25107-01 |
|
|
View PDF |
This section explains how to manage network connections to the database. It includes the following topics:
See Also:
"Connecting to the Database"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:
|
HTTP |
8080 |
Database connections using the HTTP protocol. Examples include:
|
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 the Oracle Database XE graphical user interface until you enable remote HTTP connections. See "Enabling Remote HTTP Connection to the Database" for instructions.
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:
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 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.
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
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
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 thelsnrctl
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"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"
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
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.
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.
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...
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"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.
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);