Understanding SQL*Net Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index


Go to previous file in sequence Go to next file in sequence

Using SQL*Net

This chapter describes how to use SQL*Net release 2.3 after it has been configured. It includes:

Testing the Network Configuration

Note: This section assumes that you are not using Oracle Names 2.0 with the Dynamic Discovery Option enabled. If you are using the Dynamic Discovery Option, please refer to the Oracle Names Administrator's Guide for information about starting the network.

Configure the network using the Oracle Network Manager. Once the configuration files are on the destination machines, each component can be started and tested. The preferred sequence for testing the network is to:

Start a Names Server

The STARTUP command of the NAMESCTL utility loads the Names Server into memory and tells it to begin executing. At startup, the Names Server loads its configuration, loads its data, then becomes available to answer requests.

NAMESCTL> STARTUP

Test a Names Server

To test that a Names Server is operating correctly, use the PING command. Following are two ways to PING the server LABRADOR in the US.ACME domain. Alternatively, you can use TNSPING (described on page 5-5). 

NAMESCTL> PING LABRADOR.US.ACME
or
NAMESCTL> SET SERVER LABRADOR.US.ACME
NAMESCTL> PING

You can also PING multiple Names Servers by using a single command, for example:

NAMESCTL>PING HUEY.UK.ACME DUEY.UK.ACME LOUIE.UK.ACME

PING responds with the time it takes to contact the Names Server and return an acknowledgment.

If PING fails, make sure the Names Server has been started. If it has been, double-check the configuration in Network Manager, especially the defined address of the Names Server in question.

Start the Listener

From each listener's node, use the Listener Control Utility, LSNRCTL, to start each listener. In command line mode, to start a default listener (which is a listener defined in a LISTENER.ORA file with the alias LISTENER): 

LSNRCTL START

To start a non-default listener: 

LSNRCTL START listener_name

LSNRCTL will display a status message indicating that it has started successfully. After LSNRCTL has started the listener, it has no further control over the listener. Check that all expected SIDs for that listener are listed in the services summary in the status message. For more information on the Listener Control Utility see the section "Using the Listener Control Utility" later in this chapter.

Test the Listener

To test the listener, initiate a connection from a client in the same community as the listener to any active database controlled by that listener.

The simplest test uses SQL*Plus as follows: 

SQLPLUS user/password@service_name

The service_name may be found in the TNSNAMES.ORA file, an Oracle Names Server, or a native naming service such as NIS or DCE's CDS. For more information on testing SQL*Net from a client, see the section "Initiating a SQL*Net Connection" later in this chapter.

If there are no clients in the same community as the listener, you must start an Interchange before testing the listener.

Repeat these steps for each listener in the network.

Start the Interchange

Use the Interchange Control Utility, INTCTL, on the Interchange node to start an Interchange. (You cannot run INTCTL from a remote node.) For example, in command linemode the command is: 

INTCTL START INTERCHANGE

or 

INTCTL START INT

INTCTL displays a status message indicating that it has started successfully. For more information on the Interchange Control Utility see Chapter 6, "Controlling the MultiProtocol Interchange," in the Oracle MultiProtocol Interchange Administrator's Guide.

Test the Interchange

To test the Interchange, initiate a connection through the Interchange from a client in one community to a database in another. On the client machine type: 

SQLPLUS user/password@service_name

The service_name for the database in the other community may be stored in TNSNAMES.ORA, an Oracle Names Server, or a native naming service such as NIS or DCE's CDS. To ensure that the connection went through the Interchange, type:

INTCTL STATUS INTERCHANGE

The Connection Manager should indicate that there is one active connection through the Interchange. You can run the STATUS command on the Interchange machine. If the Interchange is listed in the TNSNAMES.ORA file, stored in an Oracle Names Server, or stored in a native naming service, then you can run the STATUS command remotely from any other node.

Test Network Objects Using NAMESCTL

To test that a network object exists, use the QUERY comand. The syntax for this command is as follows:

NAMESCTL> QUERY global_object_name type

Database service names have the type A.SMD, and database links have the type DL.RDBMS.OMD. The following example shows a query of the database service name BUGSY in the MACS.ACME domain. 

NAMESCTL> QUERY BUGSY.MACS.ACME A.SMD

The QUERY command returns the amount of time the transaction took and information about the network object.

Test Network Objects Using TNSPING

The network administrator can use TNSPING to determine whether or not a service on a SQL*Net network can be successfully reached. The service being contacted can be an Oracle database, an Oracle Names Server, or any other Oracle (TNS) service.

If you can connect successfully to a TNS service using TNSPING, it displays an estimate of the round trip time (in milliseconds) it takes to reach the Oracle service. If it fails, it displays a message describing the error that occurred. This allows you to see the network error that is occurring without the overhead of a database connection.

You invoke TNSPING on the command line as follows: 

	tnsping service_name count

On some platforms, such as Microsoft Windows, the interface may be different, but the program accepts the same arguments.

If the service name specified is a database name, TNSPING attempts to contact the corresponding network listener. It does not actually determine whether or not the database itself is running. (To do this, use Server Manager to attempt a connection to the database.)

Examples of the Use of TNSPING

Following are some examples of how a network administrator or user can use TNSPING. For example, to connect to a database named SPOTDB, the command:

	tnsping spotdb

produces the following message: 

TNS Ping Utility for SunOS:  Version 2.2.2.0.0 - Production on 10-Mar-95 10:09:13
Copyright (c) Oracle Corporation 1995.  All rights reserved.
Attempting to contact (ADDRESS=(COMMUNITY=build_tcp.us.oracle.com)(PROTOCOL=TCP)
(HOST=spot)(PORT=1599)) 
OK (50msec)

To check whether an Oracle Names Server can be reached, use a command similar to the following: 

	tnsping (ADDRESS=(COMMUNITY=build_tcp.us.oracle.com)     (PROTOCOL=TCP)(HOST=fido)(PORT=1600))

The address of the Oracle Names Server is located in SQLNET.ORA (in the NAMES.PREFERRED_SERVERS and NAME.PREFERRED_SERVERS parameters) on the client.

Note: Both parameters showing the client's preferred Names Server must be included for backward compatibility with an earlier version of the product.

A message similar to the following will be returned to the user: 

TNS Ping Utility for SunOS: Version 2.2.2.0.0 - Production on 10-Mar-95 10:09:59
Copyright (c) Oracle Corporation 1995. All rights reserved. 
Attempting to contact (ADDRESS=(COMMUNITY=build_tcp.us.oracle.com) (PROTOCOL=TCP)(HOST=fido)(PORT=1600))
OK (70 msec)

To determine whether the STPRD database can be connected to, and to specify that TNSPING try to connect 10 times and then give up, use the following command: 

	tnsping stprd 10

This command produces the following message: 

TNS Ping Utility for SunOS:  Version 2.2.2.0.0 - Production on 10-Mar-95 10:10:28 
Copyright (c) Oracle Corporation 1995. All rights reserved. 
Attempting to contact (ADDRESS=(COMMUNITY=build_tcp.us.oracle.com) (PROTOCOL=TCP)(HOST=spot)(PORT=1599))
OK (290 msec)
OK (100 msec)
OK (70 msec)
OK (70 msec)
OK (60 msec)
OK (70 msec) 
OK (70 msec)
OK (80 msec)
OK (180 msec) 
OK (340 msec)

Below is an example of TNSPING attempting to connect to an invalid service name: 

tnsping invalid_service_name

This attempt produces the following message: 

TNS Ping Utility for SunOS:  Version 2.2.2.0.0 - Production on 10-Mar-95 10:10:58
Copyright (c) Oracle Corporation 1995.  All rights reserved.  
TNS-03505: Failed to resolve name

Following is an example of using TNSPING to connect to a name that is valid, but that resolves to an address where no server is located (for example, the server may be down or not started): 

tnsping testing

The following message is returned: 

TNS Ping Utility for SunOS: Version 2.2.2.0.0 - Production on 10-Mar-95 10:11:42 
Copyright (c) Oracle Corporation 1995.  All rights reserved.  
Attempting to contact (ADDRESS=(PROTOCOL=tcp)(HOST=spot)(PORT=1599))
  TNS-12541: TNS:no listener

Test All Client Types

Make sure that each client type is tested. It is not enough to test that the Interchange works. If there are several different client types in your network, initiate a connection to a server from each of them. If a connection is unsuccessful, use logging and tracing--the diagnostic tools--to find the cause of the problem. An error stack in the error log may point to the problem. If not, turn on tracing and repeat the operation. You can find information about error logging and tracing in Chapters 1 through 3 of the .

Common Errors During Testing

If you are unsuccessful in bringing up a listener, Interchange, or Names Server, or fail to make a connection to a database, check to see if the cause is one of the following common errors.

Note: The default for QUEUESIZE is operating system-specific. Similarly, it is dependent upon the operating system whether the QUEUESIZE parameter will be recognized and be put into effect.

Other common errors are listed in Appendix C of this manual. All the error messages generated by SQL*Net, the Interchange, Oracle Names, Oracle Native Naming Adapters, and Oracle Network Manager and their underlying layers can be found in the Oracle Network Products Troubleshooting Guide. The Oracle Network Products Troubleshooting Guide also contains information about how to interpret log files and how to use the trace facility for troubleshooting purposes.

Using the Listener Control Utility

The network listener establishes listen endpoints on a machine. These listen endpoints are well-known addresses that clients and servers use to connect to a database. The Listener Control Utility, LSNRCTL, is a tool that you run from the operating system prompt to start and control the listener. For SQL*Net use, the general form of the Listener Control Utility is: 

LSNRCTL command [listener_name]

In this syntax:

LSNRCTL

Specifies the name of the tool that controls the listener. In some operating systems, this fixed parameter is case sensitive. If the operating system is case sensitive, enter LSNRCTL in lowercase.

command

Specifies the action to be performed.

listener_name

Specifies the listener name defined in LISTENER.ORA. If listener_name is not included, the default value LISTENER is used.

You can also issue Listener Control commands at the program prompt. When you enter LSNRCTL on the command line, the program is opened. You can then enter the desired commands from the program prompt. For example, the following command starts the database subagent for a node managed by SNMP support.

LSNRCTL> dbsnmp_start

Commands for LSNRCTL Information

The Listener Control Utility has three commands that provide information about LSNRCTL itself. These commands are HELP, SET, and SHOW.

HELP

HELP provides a list of all the LSNRCTL commands available. An example follows:

iris[331] lsnrctl 
 
LSNRCTL for SunOS: Version 2.3.1.1.0 - Beta on 17-AUG-95 17:15:02 
 
Copyright (c) Oracle Corporation 1994.  All rights reserved. 
 
Welcome to LSNRCTL, type "help" for information. 
 
LSNRCTL> help 
The following operations are available 
An asterisk (*) denotes a modifier or extended command: 
 
start               stop                status 
services            version             reload 
trace               spawn               dbsnmp_start 
dbsnmp_stop         dbsnmp_status       change_password 
quit                exit                set* 
show*

SET

This command lists the operations that can be set using the SET command. 

LSNRCTL> set
The following operations are available after set
An asterisk (*) denotes a modifier or extended command:

password             trc_file             trc_directory
trc_level            log_file             log_directory
log_status           current_listener     connect_timeout
startup_waittime     use_plugandplay

SHOW

This command lists the operations that can be set using the SHOW command. 

LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:

trc_file             trc_directory        trc_level
log_file             log_directory        log_status
current_listener     connect_timeout      startup_waittime
snmp_visible         use_ckpfile          use_plugandplay

LSNRCTL Commands to Control the Listener

The following commands act on the listener. If there is more than one listener on the node, the command acts against the default LISTENER, unless you provide another listener name on the command line.

CHANGE_PASSWORD [listener_name]

You can dynamically change the password of a listener using LSNRCTL. From within LSNRCTL, enter the following:

change_password

The control utility prompts you for your old password, then for the new one. It asks you to re-enter the new one, then changes it.

Note: Neither the old nor the new password displays during this procedure.

The following is an example of changing a password:

iris> lsnrctl

LSNRCTL for SunOS: Version 2.3.2.0.0 - on 10-Sep-95 18:59:34
Copyright (c) Oracle Corporation 1994. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
Password changed for LISTENER
The command completed successfully
LSNRCTL>

SPAWN

Use the SPAWN command to start a program stored on the machine on which the listener runs, and which is listed with an alias in the LISTENER.ORA file.

For example, you might create a program called NSTEST, and manually add the following to the LISTENER.ORA file:

nstest = (PROGRAM=(NAME=nstest)(ARGS=test1)(ENVS='ORACLE_HOME=/usr/oracle'))

To run the NSTEST program, you would enter the following:

lsnrctl spawn nstest

START [listener_name]

Starts the named listener. If no listener name is entered, LISTENER is started by default.

STOP [listener_name]

Stops the named listener. If no listener name is entered, LISTENER is stopped by default.

Note: You must have set a valid password if one is listed in the LISTENER.ORA parameter PASSWORDS_listener_name to be able to use this command.

STATUS [listener_name]

Displays basic information: version, start time, uptime, what LISTENER.ORA file is used, and whether tracing is turned on.

SERVICES [listener_name]

Provides detailed information about the services the listener listens for: how many connections have been established, how many refused. It displays three different types of services: dedicated servers from LISTENER.ORA, dispatcher information, and prespawned shadows.

Note: You must have set a valid password if one is listed in the LISTENER.ORA paramete,r PASSWORDS_listener_name, to be able to use this command.

RELOAD [listener_name]

Shuts down everything except listener addresses, and re-reads the LISTENER.ORA file. This command enables you to add or change services without rebooting the system.

Note: You must have set a valid password, if one is listed in the LISTENER.ORA file parameter PASSWORDS_listener_name, to be able to use this command.

TRACE [listener_name] level

Turns on tracing for the listener. Choices of level are OFF, USER, or ADMIN. USER provides a limited level of tracing; ADMIN provides a more detailed trace. This command overrides the setting in the LISTENER.ORA file. (This command has the same functionality as SET TRC_LEVEL.)

Note: You must have set a valid password, if one is listed in the LISTENER.ORA file parameter PASSWORDS_listener_name, to be able to use this command.

For detailed information on how to use tracing, see the Oracle Network Products Troubleshooting Guide.

VERSION [listener_name]

Use this command if you are asked to provide version information to Oracle Worldwide Customer Support. It provides version numbers for the following:

DBSNMP_START

Use this command to start the SNMP subagent for an Oracle database running on the same node.

DBSNMP START must be run locally--you cannot run it remotely.

DBSNMP_STOP

Use this command to stop the SNMP subagent for an Oracle database running on the same node.

DBSNMP STOP must be run locally--you cannot run it remotely.

DBSNMP_STATUS

Use this command to verify whether the SNMP subagent for an Oracle database is running.

DBSNMP STATUS must be run on the same node the Oracle database is on.

SET PASSWORD [listener_name]

Enter this command if you want to perform administrator-only tasks on the listener. For example, you must enter the SET PASSWORD command before you can stop the listener. The password should match one listed in the LISTENER.ORA file. You may enter this command when you start up the shell or any time during your session.

The preferred, secure way to enter your password is in interactive mode. Enter the command from within LSNRCTL, for example, 

LSNRCTL> SET PASSWORD

The Listener Control Utility responds:

enter listener password:

When you enter your password and press [Return], the password is not echoed on the terminal. You receive the message:

Command successful

Note: You must enter the SET PASSWORD command before you can stop the listener (with the STOP [listener_name)

Note: The listener supports encrypted and unencrypted passwords.

SET TRC_LEVEL [listener_name] level

Turns on tracing for the listener. Choices of level are OFF, USER, or ADMIN. Selecting USER provides a limited level of tracing; ADMIN provides a more detailed trace. This command overrides the setting in the LISTENER.ORA file. (This command has the same functionality as TRACE.)

Note: You must have set a valid password, if one is listed in the LISTENER.ORA file parameter PASSWORDS_listener_name to be able to use this command.

For detailed information on how to use tracing, see the Oracle Network Products Troubleshooting Guide.

SET TRC_FILE [listener_name]

Use this command to set a non-default name for the trace file.

For example, the following command sets the name of the file that contains listener trace information:

LSNRCTL> set trc_file list.trc

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "trc_file" set to list.trc
The command completed successfully

SET TRC_DIRECTORY [listener_name]

Use this command to set a non-default location for the trace file or to return the location to the default.

For example, the following command sets the directory in which the trace file is placed:

LSNRCTL> set trc_directory /usr/oracle/admin

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "trc_directory" set to /usr/oracle/admin
The command completed successfully

SET LOG_STATUS

Logging for a listener is always ON. This command has no effect.

SET LOG_FILE [listener_name]

Use this command to set a non-default name for the log file.

For example, the following command sets the name of the file that contains listener log information:

LSNRCTL> set log_file list.trc

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "log_file" set to list.log
The command completed successfully

SET LOG_DIRECTORY [listener_name]

Use this command to set a non-default location for the log file or to return the location to the default.

For example, the following command sets the directory in which the log file is placed:

LSNRCTL> set log_directory /usr/oracle/admin

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "log_directory" set to /usr/oracle/admin
The command completed successfully

SET CURRENT_LISTENER [listener_name]

If there is more than one listener on a node, any LSNRCTL command acts on the default listener (LISTENER) unless another listener has been set.

For example, suppose there were two listeners on a node, LISTENER and LSNR1. If you wanted to set or show parameters for LSNR1, you would first need to send the following command from within LSNRCTL:

LSNRCTL> set current_listener lsnr1

Any subsequent LSNRCTL commands within the same LSNRCTL session would then apply to LSNR1, unless CURRENT_LISTENER were reset. For example, if the current listener had been set to LSNR1, then the STAT command would produce something like the following output:

LSNRCTL> stat
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=IRIS))
STATUS of the LISTENER
-----------------------
Alias 		lsnr1
Version		TNSLSNR for SunOS: Version 2.3.1.1.0- Beta
Start Date		18-Aug-95 11:25:45
Uptime		0 days 0 hr. 0 min. 3 sec
Trace Level		admin
Security		OFF
SNMP			ON
Listener Parameter File	/etc/oracle/network/admin/listener.ora
Listenr Log File	/etc/oracle/network/log/lsnr1.log
Listener Trace File	/etc/oracle/network/trace/lsnr1.trc
Services Summary...
  db1		has 1 service handler(s)
The command completed successfully

You can also display the current listener by using the LSNRCTL SHOW command.

Note: You must enter SET CURRENT_LISTENER from within the LSNRCTL utility. When you exit the utility, the setting will be lost.

SET CONNECT_TIMEOUT TIME [listener_name]

This command determines the amount of time the listener will wait for a valid connection request after a connection has been started

LSNRCTL> set connect_timeout 20

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "connect_timeout" set to 20
The command completed successfully

SET STARTUP_WAITTIME TIME [listener_name]

This command sets the amount of time the listener sleeps before responding to a STATUS command:

lSNRCTL> set startup_waittime 10

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "startup_waittime" set to 10
The command completed successfully

SET USE_PLUGANDPLAY [listener_name] ON|OFF

Use this command to determine whether the Dynamic Discovery Option of Oracle Names is enabled on a listener:

LSNRCTL> set use_plugandplay ON|OFF

By default, the value of this parameter is OFF. If Oracle Names version 2 has been installed, and you want the listener to use the Dynamic Discovery Option (that is, to register itself with a well-known Names Server, set it to ON. You can also set the value in LISTENER.ORA through Oracle Network Manager, which places a slightly different parameter into LISTENER.ORA, as follows:

use_plugandplay_listener_name=[ON|OFF]

If the Dynamic Discovery Option is enabled, you can use the LSNRCTL STATUS command to see whether a service has registered itself. For example:

LSNRCTL> stat lsnr1
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=IRIS))
STATUS of the LISTENER
-----------------------
Alias 		lsnr1
Version		TNSLSNR for SunOS: Version 2.3.1.1.0- Beta
Start Date		18-Aug-95 11:25:45
Uptime		0 days 0 hr. 1 min. 49 sec
Trace Level		admin
Security		OFF
SNMP			ON
Listener Parameter File	/etc/oracle/network/admin/listener.ora
Listener Log File	/etc/oracle/network/log/lsnr1.log
Listener Trace File	/etc/oracle/network/trace/lsnr1.trc
Services Summary...
  db1 (Registered)	has 1 service handler(s)
The command completed successfully

SHOW [listener_name] subcommand

All of the SET commands listed except SET PASSWORD have equivalent SHOW commands. In response to one of the SHOW comands, LSNRCTL displays the current setting of the listener for that parameter.

In addition, there are two other parameters that can be shown, but not set, through LSNRCTL.

SHOW [listener_name] SNMP_VISIBLE

This command displays whether the listener is accessible to SNMP clients:

LSNRCTL> show snmp_visible

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "snmp_visible" set to ON
The command completed successfully

Note: The SNMP_VISIBLE parameter can be displayed, but not set, through LSNRCTL:

SHOW [listener_name] USE_CKPFILE

Use LSNRCTL to see whether the the use of a checkpoint file has been enabled in LISTENER.ORA:

LSNRCTL> show use_ckpfile

The computer output would be something like the following:

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))
LISTENER parameter "use_ckpfile" set to OFF
The command completed successfully

If USE_CKPFILE_listener_name is not set in LISTENER.ORA, and you use LSNRCTL SET commands to change listener parameters, when you stop the listener, LSNRCTL sends a message reminding you that the changes you have made are not persistent.

Note: You cannot set this parameter using LSNRCTL; it must be set in LISTENER.ORA. For more information about the use of checkpoint files, see "Persistent Changes" later in this chapter.

QUIT

Use this command to quit LSNRCTL and return to the operating system prompt. (Same as EXIT.)

EXIT

Use this command tp quit LSNRCTL and return to the operating system prompt. (Same as QUIT.)

Making Changes Persistent

If set to ON, the following parameter in LISTENER.ORA will make the changes you make by using the SET command in LSNRCTL persistent; that is, if you shut down the listener and then start it up again, the parameters will retain the values you set through LSNRCTL. 

use_ckpfile_listener_name=ON

If this parameter is in the LISTENER.ORA file, the parameters you set using LSNRCTL are saved to a checkpoint file, listener_name.CKP, either in the TNS_ADMIN directory if one exists, or in the ORACLE_HOME/NETWORK/ADMIN directory. The values in this checkpoint file will override the values set in LISTENER.ORA.

If the USE_CKPFILE parameter is set to OFF in LISTENER.ORA, the values set by LSNRCTL are lost when the listener is restarted or the LISTENER.ORA parameters are reloaded. The default is for USE_CKPFILE to be OFF.

Note: If checkpointing is enabled and parameters in LISTENER.ORA are changed, the changes will not be visible because they are overridden by the parameters in the checkpoint file. If you want the changes in LISTENER.ORA to be visible, set the value of USE_CKPFILE to OFF and use the LSNRCTL RELOAD command to reread the LISTENER.ORA file.

Examples of the Use of LSNRCTL

This section provides some examples of the most common uses of the LSNRCTL utility.

Starting the Listener

To start a default listener, that is, a listener defined in the LISTENER.ORA file using the name LISTENER, use the command: 

LSNRCTL START

Alternatively, you can enter LSNRCTL on the command line and then enter START from the program prompt.

To start a listener configured in the LISTENER.ORA file with a name other than LISTENER, include that name. For example, if the listener name is TCP_LSNR, enter: 

LSNRCTL START TCP_LSNR

Or, from the LSNRCTL program prompt, just enter:

LSNRCTL> START TCP_LSNR

Stopping the Listener

To stop the default listener, use the command: 

LSNRCTL STOP

To stop a running listener defined in LISTENER .ORA as TCP_LSNR, use the command: 

LSNRCTL STOP TCP_LSNR

Remember, if there are any passwords in the LISTENER.ORA file, you must use the SET PASSWORD command before you can use the STOP command. You must set the password from within the LSNRCTL program; you cannot set it from the operating system command line. The method for setting the password depends on whether you are using the encrypted password feature. If you are not using an encrypted password, enter the password on the LSNRCTL command line. For example, the following commands stop the TCP_LSNR using an unencrypted password:

LSNRCTL 
LSNRCTL> SET PASSWORD password
LSNRCTL> STOP TCP_LSNR

If you are using an encrypted password, enter the password in interactive mode. For example, the following commands stop the listener named TCP_LSNR:

LSNRCTL 
LSNRCTL> SET PASSWORD 
	Enter listener password (password is not displayed)
	Command successful
LSNRCTL> STOP TCP_LSNR

Stopping a listener when in batch mode is not recommended, because to do so you must include your password in a cleartext batch file, which would threaten your security. However, if you are not using an encrypted password, stopping a listener can be done by redirecting input into the command interpreter.

Note: You should not stop the listener in batch mode if it requires an encrypted password.

Different operating systems use different syntax. An example for VMS and two alternative methods for UNIX follow.

To stop a listener in batch mode on VMS, create a DCL script, with a name like LSNRSTOP.COM, as follows:

$ lsnrctl 
set password password
stop listener_name
exit

When you want to stop the listener, run the script as follows:

@LSNRSTOP

On a UNIX system or on OS/2, the following procedure would be effective:

Create a file with a name like LSNRSTOP that contains the following lines:

set password password
stop listener_name

You can then stop the listener from the command line by entering:

lsnrctl < LSNRSTOP

Alternatively, in UNIX you can stop the listener by creating a shell script named something like "lsnrstop". The shell script would look something like this::

lsnrctl <<!
set password password
stop listener_name
exit
!

You can then stop the listener from the command line by entering:

LSNRSTOP

Again, Oracle Corporation recommends against stopping a listener in batch mode because of the need to expose your password.

Note: Be careful when stopping a listener. On some platforms and with some protocols, when a listener is stopped any SQL*Net connections currently running are shut down. In some situations the connections continue, but it is then not possible to start the listener again until the running processes have been closed. It is good practice to send a warning message to all network users before stopping a listener.

Checking Listener Status

One of the most useful commands available with the Listener Control Utility is the STATUS command. With the status command, an administrator can get a view of the "state" of a listener by checking:

Suppose that a user wanted to know the status of a listener on a UNIX system defined in LISTENER.ORA with the default name LISTENER. The user would enter the following command at the operating system command line:

LSNRCTL STATUS

Following is sample output from a status report of a UNIX listener : 

LSNRCTL for SunOS: Version 2.2.2.0 - Production on 15-FEB-95 07:07:10
Copyright (c) Oracle Corporation 1995. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(HOST=orchid)(port=1334))
STATUS of the LISTENER
------------------------
Alias                  LISTENER
Version                TNSLSNR for SunOS: Version 2.2.2.0 - 
  Production
Start Date             10-FEB-94 07:06:34
Uptime                 0 days 0 hr. 0 min. 44 sec
Trace Level            ADMIN
Security               ON
SNMP                   ON
Listener Parameter File   /private1/dvl/7012/network/admin/listener.ora
Listener Log File         /private1/dvl/7012/network/log/listener.log
Listener Trace File       /private1/dvl/7012/network/trace/listener.trc
Listening on            (ADDRESS=(PROTOCOL=tcp)(HOST=orchid)
  (port=1334)))
                        (ADDRESS=(PROTOCOL=decnet)(node=23.106)
  (object=orchid)))
Services Summary...
  orchid		has 1 service handlers
The command completed successfully

Retrieving Listener Services

A database administrator who wanted to get information about the services of the listener would enter the following commands from within LSNRCTL:

LSNRCTL>[SET PASSWORD password]
LSNRCTL> SERVICES

The output of a LSNRCTL SERVICES command follows:

LSNRCTL for SunOS: Version 2.1.3.0.0 - Production on 10-FEB-94 07:14:55

Copyright (c) Oracle Corporation 1993.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=ruth))
Services Summary...
  ruth		has 1 service handlers
    DEDICATED SERVER established:99 refused:0
The LSNRCTL command completed successfully

In this example, the LSNRCTL SERVICES command returned the information that the listener had established 99 connections using a dedicated server process and refused none.

In the following example the LSNRCTL SERVICES command returns information about four types of service handlers.

LSNRCTL for SunOS: Version 2.2.2.0.0 - Production on 15-Mar-1995 17:41:12



Copyright (c) Oracle Corporation 1994.  All rights reserved.



Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=orchid))

Services Summary...

   listener          has 4 service handlers

    DEDICATED SERVER established:0 refused:0

    PRESPAWNED SERVER established:0 refused:0 current:0 max:1 state:ready

      PID:15439

      (ADDRESS=(PROTOCOL=ipc)(DEV=4)(KEY=#15439.1))

    PRESPAWNED SERVER established:5 refused:0 current:0 max:1 state:ready

      PID:15441

      (ADDRESS=(PROTOCOL=tcp)(DEV=4)(HOST=139.185.22.25)(PORT=3334))

    DISPATCHER established:30 refused:0 current:7 max:21 state:ready

      D000 (machine: orchid, pid: 15406)

      (ADDRESS=(PROTOCOL=tcp)(DEV=7)(HOST=139.185.22.25)(PORT=3330))

The command completed successfully

This message shows that since this listener has been running, it has made a total of five connections using a prespawned server for TCP/IP, and that it has one running at this time. It also shows that there are seven connections currently established using a multi-threaded server dispatcher, with a total of 30 established since the process started.

Modifying Client Parameters

Client configuration parameters are contained in the SQLNET.ORA file. Generally, you generate SQLNET.ORA files for clients with similar navigation needs (called client profiles or client types) using Oracle Network Manager. The SQLNET.ORA file determines the following client characteristics:

The SQLNET.ORA file created by Network Manager generally is used by a number of clients. If you want to change any of these parameters for a whole group of clients, you may use Network Manager to change the network definition and create new SQLNET.ORA files to be distributed as needed.

Alternatively, if you want to change parameters for individual clients, you may use the SQLNET.ORA Editor. This utility is part of the Client Status Monitor. It enables you to open the SQLNET.ORA file on an individual client and change its parameters either through a graphical user interface or through the command line.

If you are using Oracle Names version 2.0 and the Dynamic Discovery Option, you do not necessarily need to use Network Manager. If you do not use it, a SQLNET.ORA file is not created. If you find you want to provide some non-default parameters for the client, you can create an empty SQLNET.ORA using any text editor, such as vi, and then add the parameters you need using the SQLNET.ORA Editor. Using this method to create the file makes it less likely that typographical or formatting mistakes will cause errors.

Controlling Network Services from Network Manager

Oracle Network Manager version 3.0 lets you run some Listener Control Utility, Names Control Utility, and Interchange Control Utility commands without having to go to a command line prompt to execute them.

After your network has been defined, you can obtain information on any network service by selecting it on the Map View or Tree View Object List and then selecting Control Network Service from the Special menu.

Note: You must define the network configuration and distribute the configuration files throughout the network before you can use this option.

A control window appears. Select a command from the drop-down list. (The commands available depend on the type of network object you have selected.) For example, if you select Trace, select the level of tracing you want from the Level list. The result of your command will be displayed in the large read-only box on this window.

Following are the commands you can use from Network Manager to control network services remotely.

LSNRCTL

VERSION STATUS RELOAD SET_TRACE_LEVEL SERVICES

NAMESCTL

VERSION STATUS PING SET TRACE_LEVEL SHOW TRACE_LEVEL

INTCTL

VERSION STATUS

The Listener Control Utility (LSNRCTL) commands are described in "Using the Listener Control Utility" in this chapter. See the Oracle Names Administrator's Guide for information on NAMESCTL commands. See the Oracle MultiProtocol Interchange Administrator's Guide for information on INTCTL commands.

Initiating a SQL*Net Connection

There are a number of ways to initiate a connection with an Oracle server. Commonly used methods are:

The specifics of use are slightly different in each case. Each of the general methods listed is briefly covered here. To identify the method used in a specific tool, refer to the tool's user's guide.

Connecting from the Operating System Command Line

The general form of connecting an application to a database server from the command line is: 

tool username/password@service_name

In this syntax:

tool

Specifies the command used to invoke a tool such as SQL*Plus, SQL*Forms, etc.

username

Specifies an Oracle username on the server.

password

Specifies the corresponding password on the server.

service_name

Specifies a service name entered in the TNSNAMES.ORA file that identifies the connect descriptor for the desired server. If the server is in the client's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the client's SQLNET.ORA file. See the section on the SQLNET.ORA file in Appendix A of this manual.)

For example, in a network with only one domain, the default .WORLD domain, it is not necessary to include .WORLD in the service name. For example:

			% sqlplus scott/tiger@SERVERX

However, if the client's default domain were .EAST and the server's domain were .WEST, then the service name would have to include the domain. For example,

			% sqlplus scott/tiger@SERVERX.WEST

Note: To prevent the password from displaying during a logon, you can leave out the password parameter on the command line; you will then be prompted to enter your password without it showing on screen.

Most Oracle tools can use the operating system command line to connect; some provide alternatives.

Connecting from the Tool Logon Screen

Some tools provide a logon screen as an alternative form of logon. A user can log on to a database server just as easily by identifying both the username and service name in the username field of the tool logon screen, and typing the password as usual in the password field. Figure 5 - 1 shows a SQL*Forms logon screen where the user SCOTT is connecting to the server SERVERX with a password of TIGER. Notice the password cannot be seen, a standard feature of Oracle tool logon screens.

Figure 5 - 1. Connection from Logon Screen

Connecting from a 3GL Application

In applications written using a 3GL, the program must establish a connection to a server using the following syntax: 

EXEC SQL CONNECT :username IDENTIFIED BY :password

In this connection request, the :username and :password are 3GL variables that can be set within the program either statically or by prompting the user. When connecting to a database server, the value of the :username variable is in the form: 

username@service_name

which is the same as in the tool logon screen above. The :password variable contains the password for the database account being connected to.

Connecting Using Special Commands within Tools

Some Oracle tools have commands for database connection, once the tool has been started, to allow an alternative username to be specified without leaving the tool. Both SQL*Plus and SQL*DBA allow the CONNECT command using the following syntax: 

SQL> CONNECT username/password@service_name

For example: 

SQL> CONNECT SCOTT/TIGER@SERVERX

This is very similar to the operating system command line method, except that it is entered in response to the tool prompt instead of the operating system prompt.

Other Oracle tools use slightly different methods specific to their function or interface. For example, Oracle CDE tools use logon buttons and a pop-up window with the username, password, and remote database ID field. For more information on connecting to Oracle with a specific tool, refer to the tool's user guide.

Improving Network Performance

This section describes three ways you can influence network performance. You can do the following:

Why Use Listener Load Balancing?

Listener load balancing is useful when there are many connection requests made to a server. By having more than one listener to receive those requests, the burden on a single listener is reduced and connection time is faster.

Listener load balancing is also useful in a multiple server environment, in which there can be multiple listeners that listen for replicated servers.

For more detailed information about listener load balancing, see "Listener Load Balancing" in Chapter 2. For information about configuring multiple listeners, see "Configuring Listener Load Balancing," in Appendix A.

Why Use Prestarted Processes?

If you are using a dedicated server, prestarting (also known as prespawning) processes can improve connect time. Normally, when the listener gets a connection request for a dedicated server, it starts a new process and hands off the request to that process. If prestarted processes are enabled, however, the listener will have a pool of prestarted processes available so that when a connection request comes in, the listener can hand off the connection to a process with no wait time.

This feature is appropriate in a heavily loaded system when connect times are slow and Multi-threaded Servers are not used.

Note: Async and APPC/LU6.2 protocols do not support this feature.

For more information about prestarted processes, see "How SQL*Net Establishes Connection to a Prestarted Dedicated Server," in Chapter 2.

Why Adjust SDU Size?

The Session Data Unit (SDU) is what SQL*Net buffers data into before sending it across the network. It sends the data stored in this buffer when the buffer is full or when an application tries to read the data. When large amounts of data are being retrieved, and when packet size is consistently the same, it may speed retrieval to adjust the default SDU size.

How to Determine Optimal SDU Size

Optimal SDU size depends on the normal packet size. Use a sniffer to find out the frame size, or set tracing on to its highest level to check the number of packets sent and received, and to see if they are fragmented. Tune your system to limit the amount of fragmentation. Doing so requires experimenting with various sized buffers, as there is no formula.

The default SDU size is 2048. It is adjustable from 512 to 32K.

SDU size should be set on both the client and server sides, and should generally be the same; if different SDU sizes are requested on the client and the server, the SDU size is negotiated down to the lower of the two.

How to Configure SDU Size

Use Oracle Network Manager to configure a change to the default SDU size on both the client and the server. Enter the following parameter in the User Defined field on the Database - Location Details Page:

(SDU=number)

Be sure to include the parentheses. The parameter and value appear in both the LISTENER.ORA file and the TNSNAMES.ORA file.

An example of how it might appear in the LISTENER.ORA file follows:

...
(SID_DESC=
	(SDU=4096)
	(SID_NAME=dbprod))

An example of how it might appear in the TNSNAMES.ORA file follows:

DESCRIPTION=
		(SDU=4096)
		(ADDRESS_LIST=
		...

Note: A user can change the client side request for SDU size by manually editing TNSNAMES.ORA, but the SDU size used will be the lower of the SDU size listed in LISTENER.ORA and TNSNAMES.ORA. Therefore, if SDU size is not listed in the LISTENER.ORA file, the SDU size used will not be greater than the default of 2048.

Distributed Database Management

SQL*Net plays a major role in distributed database management. In a distributed transaction, SQL*Net provides the means for clients and servers to communicate by way of their SQL-based dialog language. SQL*Net performs transparently to enable distributed database functions. This section highlights the database functionality used in conjunction with SQL*Net.

Database Links

A database link is a database object that links an Oracle account in one database to an Oracle account in another database. The data in the remote schema is then accessible to the database in which the database link is defined.

Note: If your network uses Oracle Names, a global database link is created on every database on the network to every other database. Therefore, you do not need to create additional database links, as described in this section. See "Database Links wth Oracle Names" later in this chapter.

The generic syntax for creating a database link in SQL is: 

CREATE [PUBLIC] DATABASE LINK linkname 
[CONNECT TO username IDENTIFIED BY password] 
USING 'service_name'

In this syntax:

[PUBLIC]

Specifies a database link available to all users with the CREATE SESSION privilege. If the PUBLIC option is omitted, a private link available only to the creator is created. Note that creating a public database link requires CREATE PUBLIC DATABASE LINK privilege.

linkname

Specifies the name of the database link. If the remote server is in the local server's domain, the link name does not need to include the domain name. However, if the server is in another domain, the link name must include the domain. (The domain is determined by DB_DOMAIN in the initialization parameter file).

CONNECT TO

Optionally specifies a single username and password for all users of the database link to share. If the clause is omitted, the Oracle username and password of the user account using the database link will be used to connect to the remote database server.

username

Specifies a valid Oracle username on the remote database server.

password

Specifies the corresponding password of the username on the remote database server.

service_name

Specifies the service name defined in the TNSNAMES.ORA file or stored in Oracle Names associated with the connect descriptor for the desired database. If the remote server is in the local server's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the server's SQLNET.ORA file. See the section on the SQLNET.ORA file in Appendix A of this manual.)

Prior to Oracle7, a database administrator could specify any linkname for a database link. However, with Oracle7 and later, a database link must have the same name as the global database name of the database. Remember that the service name is also the same as the global database name; therefore, the linkname and service name are now the same.

For example, the command for creating a public database link to a database which has the global database name ORCHID.HQ.ACME is as follows:

CREATE PUBLIC DATABASE LINK ORCHID.HQ.ACME
CONNECT TO scott IDENTIFIED BY tiger
USING 'ORCHID.HQ.ACME'

Note: The CONNECT TO username IDENTIFIED BY password clause and the USING 'global_database_name' clause are both optional.

Public Database Links with a Default Connection

Figure 5 - 2 shows a public database link created by the DBA user SYSTEM using the service name NY_FIN.HQ.ACME. The link is created by entering:

CREATE PUBLIC DATABASE LINK NY_FIN.HQ.ACME
USING 'NY_FIN.HQ.ACME'

Figure 5 - 2. Public Database Link with Default Connection

Users connected to OHIO.SALES.ACME can use the NY_FIN.HQ.ACME database link to connect to NY_FIN.HQ.ACME with the same username and password they have on OHIO.SALES.ACME. To access the table on NY_FIN.HQ.ACME called EMP, any user could issue the SQL query: 

SQL> SELECT * FROM EMP@NY_FIN.HQ.ACME;

Note: If the target database were in the default domain of the source database, the user would not need to include the domain in the link name or service name, or in the SELECT command.

This query would initiate a connection from OHIO to NY_FIN using the current username and password to log onto NY_FIN. The query would then be processed on NY_FIN, and the data available to the current user from the table EMP would be returned to OHIO. Each user creates a separate connection to the server. Subsequent queries to that database link by that user would not require an additional logon.

Public Database Links with a Specific Connection

Figure 5 - 3 shows the database link created by the user SYSTEM with the service name NY_FIN:

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO FINPUBLIC IDENTIFIED BY NOPASS
USING 'NY_FIN'

Note: The CONNECT TO username IDENTIFIED BY password clause and the USING 'global_database_name' clause are both optional.

Figure 5 - 3. Public Database Link with Specific Connection

Any user connected to OHIO can use the NY_FIN database link to connect to NY_FIN with the common username/password of FINPUBLIC/NOPASS. To access the table in the FINPUBLIC account of NY_FIN called ALL_SALES, any user could issue the SQL query:

SQL> SELECT * FROM ALL_SALES@NY_FIN;

This query would initiate a connection from OHIO to NY_FIN to the common account FINPUBLIC. The query would be processed on NY_FIN and data from the table ALL_SALES would be returned to OHIO.

Each user creates a separate connection to the common account on the server. Subsequent queries to that database link by that user would not require an additional logon.

Connection Qualifiers

You can also define connection qualifiers to database links. Connection qualifiers provide a way to create more than one link to a given database. Alternate links are a useful way to access different accounts on the same database with different sets of access privileges. The alternate link created by a connection qualifier must include a reference to a database by its global database name (or service name).

A connection qualifier contains a qualifier name and, optionally, a username and password. To create a connection qualifier, use a statement similar to the following:

CREATE PUBLIC DATABASE LINK NY_FIN@PROFITS
CONNECT TO ACCOUNTS IDENTIFIED BY TAXES
USING 'NY_FIN'

To use the connection qualifier, you append the qualifier name to the service name of the database you want to access.

For example, the following SQL queries use three separate database links to the same database, using different connection qualifiers:

SELECT * FROM EMP@NY_FIN;
SELECT * FROM SCHEDULE@NY_FIN@PROFITS;
SELECT * FROM EMPSALARIES@NY_FIN@FIN;

In this example @PROFITS and @FIN are connection qualifiers.

Dropping a Database Link

You can drop a database link just as you can drop a table or view. The command syntax is: 

DROP DATABASE LINK linkname;

For example, to drop the database link NY_FIN, the command would be: 

DROP DATABASE LINK NY_FIN;

Finding Available Database Links

Any user can query the data dictionary to determine what database links are available to that user. For information on viewing the data dictionary, refer to the Oracle7 Server Concepts or the Oracle7 Administrator's Guide.

Database Links with Oracle Names

When you define a network that includes Oracle Names, Network Manager automatically creates a global database link to every database server you define from every other database server in the network. These database links do not reside in the data dictionary, but in the network definition to which the Names Servers refer. The database links thus created do not initially include a CONNECT TO clause, so that users reach the linked database using the same usernames and passwords as they use to reach the first database. 

SQL> SELECT * FROM EMP@OHIO, DEPT@NY_FIN;

Explicitly Defined Database Links

You can edit global database links to include CONNECT TO data using Network Manager. When you edit a database, you can specify a single default username and password for the database link. See Chapter 5 in the Oracle Network Manager Administrator's Guide for details on how to edit database links.

Connection Qualifiers

You can also define connection qualifiers to global database links through Network Manager. Connection qualifiers provide a way to create multiple links to the same database. Multiple database links to the same database provide different access routes with different accounts and privileges. See Chapter 5 in the Oracle Network Manager Administrator's Guide for details on how to create connection qualifiers using the Network Manager.

For a more detailed discussion of database links, see Oracle7 Server Distributed Systems, Volume I, and the Oracle Names Administrator's Guide.

Synonyms

Database synonyms are a standard SQL feature used to provide alternate names for database objects and, optionally, their locations. A synonym can be created for any table, view, snapshot, sequence, procedure, function, or package. All synonyms are stored in the data dictionary of the database in which they are created. To simplify remote table access through database links, a synonym can be defined to allow single-word access to remote data, isolating the specific object name and the location from users of the synonym. The syntax to create a synonym is: 

CREATE [PUBLIC] SYNONYM_name 
FOR [schema.]object_name[@database_link_name]

In this syntax:

[PUBLIC]

Specifies that this synonym is available to all users. Omitting this parameter makes a synonym private, and usable only by the creator. Public synonyms can be created only by a user with CREATE PUBLIC SYNONYM system privilege.

synonym_name

Specifies the alternate object name to be referenced by users and applications.

schema

Specifies the schema of the object specified in object_name. Omitting this parameter uses the creator's schema as the schema of the object.

object_name

Specifies either a table, view, sequence, or other name as appropriate.

database_link_name

Specifies the database link which identifies the remote username in which the object specified in object_name is located.

A synonym must be a uniquely named object for its schema. If a schema contains a database object and a public synonym exists with the same name, Oracle always finds the database object when the user that owns the schema references that name.

Because a synonym is merely a reference to the actual object, the security domain of the object is used when the synonym is accessed. For example, a user that has access to a synonym for a specific table must also have privileges on that table to access the data in it. If the user attempts to access the synonym, but does not have privileges on the table it identifies, an error occurs indicating that the table or view does not exist.

Figure 5 - 4 shows two servers, OHIO and NY_FIN, in which a database link from OHIO to NY_FIN and the synonym FOR_SALE provide an alternate object name for use in OHIO to reference the OPEN table in NY_FIN. The database link and the synonym are created as follows:

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO REAL_ESTATE IDENTIFIED BY NOPASS;
USING 'NY_FIN'
CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_FIN;

Figure 5 - 4. Using Synonyms for Alternate Object Names

The table OPEN on NY_FIN could be accessed from OHIO using the SQL statement: 

SELECT * FROM FOR_SALE;

Using this database link, the user is logging on to NY_FIN as user REAL_ESTATE. Notice that this public synonym was created by the DBA on behalf of the REAL_ESTATE username. If the table OPEN were owned by another user, such as SALES_MGR, the CREATE SYNONYM statement would have referred to the object as SALES_MGR.OPEN@NY_FIN. Without such a prefix, a table that does not exist in the database link user's schema would return an error, since it would be looking for the OPEN table owned by the REAL_ESTATE user.

Maintaining Location Transparency

When using a synonym to access a database object over a database link, the user of the synonym is said to have location transparency. For example, an application developer using the FOR_SALE synonym from the previous example has the illusion that FOR_SALE is a database object available for use as any other object would be. The reference to the database link is invisible to the developer; therefore, any application built using the synonym would have no reference to the location of specific data.

This ability to isolate applications from the location of data in a distributed transaction ensures maximum flexibility for future enhancements or changes to the application. For example, if the OPEN table were to move from one database server to another, only the synonym or the database link would need to be changed to identify the new location. The applications would continue to reference the same object name, although they would be connecting to a new location to access the data in that table. Figure 5 - 5 shows the most common method of redefining the location of a table to retain location transparency.

Figure 5 - 5. Redefining Table Location to Retain Location Transparency

CREATE PUBLIC DATABASE LINK NY_TAX
CONNECT TO REALTOR IDENTIFIED BY NOPASS
USING 'NY_TAX'
DROP SYNONYM FOR_SALE;
CREATE PUBLIC SYNONYM FOR_SALE
FOR OPEN@NY_TAX;

To relocate the table, a second database link was created called NY_TAX that connected to a new database with the service name NY_TAX, and the synonym was recreated to reference the NY_TAX database link instead of the NY_FIN database link. Any other tables that were accessed through the NY_FIN database link to NY_FIN would continue to function properly.

Alternatively, if the only table being accessed on NY_FIN were the OPEN table, the synonym could have remained unchanged and the database link redefined to use the service name NY_TAX instead of NY_FIN. Either option is equally effective.

Snapshots

An Oracle system with both the distributed option and the procedural option can replicate tables that are frequently queried by users on many nodes of a distributed database. By having read-only copies of heavily accessed data on several nodes, the distributed database does not need to send information across a network repeatedly, thus helping to improve the performance of the database application. Oracle provides an automatic method for table replication called snapshots. Snapshots are read-only copies of a master table located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table.

Maintaining snapshots of a master table among the nodes of a distributed database is often a useful feature for the following reasons:

Figure 5 - 6. Table Replication Using Snapshots

A snapshot is a full copy or a subset of a table that reflects a recent state of the master table. A snapshot is defined by a distributed query that references one or more master tables, view, or other snapshots. A database that contains a master table is referred to as the master database.

Simple vs. Complex Snapshots Each row in a simple snapshot is based on a single row in a single remote table. Therefore, a simple snapshot's defining query has no GROUP BY or CONNECT BY clauses, or subqueries, joins, or set operations. If a snapshot's defining query contains any of these clauses or operations, it is referred to as a complex snapshot.

Creating Snapshots

Create a local snapshot using the SQL command CREATE SNAPSHOT. As when creating tables, you can specify storage characteristics for the snapshot's data blocks, extent sizes and allocation, and the tablespace to hold the snapshot; or you can specify a cluster to hold the snapshot. Unique to snapshots, you can specify how the snapshot is to be refreshed and the distributed query that defines the snapshot. You must fully qualify any remote table names used in the defining query. For example, the following CREATE SNAPSHOT statement defines a local snapshot to replicate the remote EMP table located in the SCOTT schema in NY:

CREATE SNAPSHOT emp_sf
   PCTFREE 5 PCTUSED 60
   TABLESPACE users
   STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 50)
   REFRESH FAST
           START WITH sysdate
           NEXT sysdate + 7
   AS SELECT * FROM scott.emp@ny;

Whenever a snapshot is created, it is immediately populated with the rows returned by the query that defines the snapshot. Thereafter, the snapshot is refreshed as specified by the REFRESH clause; see Oracle7 Server Distributed Systems, Volume 2: Replicated Data for more information about refreshing snapshots.

When a snapshot is created, Oracle creates several internal objects in the schema of the snapshot. These objects should not be altered. At the snapshot node, Oracle creates a base table to store the rows retrieved by the snapshot's defining query. Oracle then creates a read-only view of this table that is used whenever queries are issued against the snapshot.

Specifying the Defining Query of a Snapshot The defining query of a snapshot can be any valid query of tables, views, or other snapshots that are not owned by user SYS. The query cannot contain either an ORDER BY or FOR UPDATE clause. Furthermore, simple snapshots are defined using a query that does not contain GROUP BY or CONNECT BY clauses, or join, subquery, or set operations.

The query that defines a snapshot can define a snapshot with a different structure from that of the master table. For example, the following CREATE SNAPSHOT statement creates a local snapshot named EMP_DALLAS, with only the EMPNO, ENAME, and MGR columns of the master table (in New York), and only the rows of the employees in department 10:

CREATE SNAPSHOT emp_dallas
   .
   .
   .
   AS SELECT empno, ename, mgr
      FROM scott.emp@ny
      WHERE deptno = 10;

Privileges Required to Create Snapshots To create a snapshot, the following sets of privileges must be granted as follows:

In both of the above cases, the owner of the snapshot must also have sufficient quota on the tablespace intended to hold the snapshot.

The large set of privileges required to create a snapshot is due to the underlying objects that must also be created on behalf of the snapshot.

Refreshes

Periodically, a snapshot is refreshed to reflect the current state of its master table. To refresh a snapshot, the snapshot's defining query is issued and its results are stored in the snapshot, replacing the previous snapshot data. Each snapshot is refreshed in a separate transaction, either automatically by Oracle (according to the interval set when the snapshot was defined or altered) or manually. Instructions for refreshing table snapshots are included in the Oracle7 Server Administrator's Guide.

Snapshot Logs

A simple snapshot can be refreshed from a snapshot log to expedite the refresh process. A snapshot log is a table in the master database that is associated with the master table. Oracle uses a snapshot log to track the rows that have been updated in the master table when a certain simple snapshot based on the master table is refreshed. One snapshot log can be used by multiple simple snapshots). Only the appropriate rows in the snapshot log need to be applied to the snapshot to refresh it (called a fast refresh). If no other simple snapshot requires an applied row in the log, it is purged from the log to keep the log size small; however, if another simple snapshot requires the row for its next refresh, the row remains in the log.

A complex snapshot or simple snapshot without a snapshot log must be completely regenerated using the master tables every time the snapshot is refreshed (called a complete refresh).

Creating Snapshot Logs

Snapshot logs are created in the master database using the SQL command CREATE SNAPSHOT LOG. You can set storage options for the snapshot log data blocks, extent sizes and allocation, and tablespace to hold the snapshot log. The following statement creates a snapshot log associated with the EMP table:

CREATE SNAPSHOT LOG ON emp
   TABLESPACE users
   STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50);

The snapshot log is always created in the same schema that contains the master table. Since you cannot specify a name for the snapshot log (one is implicitly given by Oracle), uniqueness is not a concern.

If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE and CREATE TRIGGER system privileges. If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and CREATE ANY TRIGGER system privileges. In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

Using Snapshots

Snapshots are queried just like a table or view. For example, the following statement queries a snapshot named EMP:

SELECT * FROM emp;

To query a snapshot, you must have the SELECT object privilege for the snapshot, either explicitly or via a role.

In release 7.0 of the Oracle Server, snapshots are read-only. You cannot issue any INSERT, UPDATE, or DELETE statements when using a snapshot; if you do, an error is returned. Although INSERT, UPDATE, and DELETE statements can be issued against the base table for a snapshot, they can corrupt the snapshot. Never manipulate data in the base table of a snapshot. Updates are allowed on the master table only, which must then be refreshed to update the snapshot.

Creating Views and Synonyms Based on Snapshots Views or synonyms can be defined based on snapshots. The following statement creates a view based on the EMP snapshot:

CREATE VIEW sales_dept AS
   SELECT ename, empno
   FROM emp
   WHERE deptno = 10;

Dropping Snapshots

You can drop a snapshot independently of the master tables or a snapshot log if you decide that you no longer want to replicate a table in a database. To drop a local snapshot, use the SQL command DROP SNAPSHOT. For example:

DROP SNAPSHOT emp;

If you drop the only snapshot of a master table, you should also drop the snapshot log of the master table, if appropriate.

Only the owner of a snapshot, or a user with the DROP ANY SNAPSHOT, DROP ANY TABLE, and DROP ANY VIEW system privileges can drop a snapshot.

For more information about table replication, snapshots, and distributed database design, refer to the following manuals: Oracle7 Server Distributed Systems, Volumes I and II, and the Oracle7 Server Administrator's Guide.

Copying Data between Databases

When working with a distributed database, you may want to move data from one database to another. For example, you may want to download sales data from a particular region to your workstation for further analysis. The SQL*Plus COPY command lets you copy data from:

You do not need to be directly connected to any of the databases in order to copy the information from one to another. These operations, except the first, are impossible to perform with database links and queries. Using the COPY command is similar to using the CREATE DATABASE LINK command, in that you must know:

The COPY command syntax is: 

COPY FROM username/password@service_name 
[TO username/password@service_name] 
(APPEND | CREATE | INSERT | REPLACE) 
tablename [columnlist] 
USING subquery

In this syntax:

service_name

Specifies a valid service name in the TNSNAMES.ORA file. Each service name maps to a connect descriptor that connects to a database using the keyword SID.

APPEND

Specifies that if the destination table already exists, COPY will insert the copied data into it. If the table does not already exist, COPY will create it and then insert the copied data into it.

CREATE

Specifies that COPY will create a new table and insert the copied data into it. If the table already exists, COPY will report an error and stop processing.

INSERT

Specifies that if the destination table already exists, COPY will insert the data into it. If the table does not already exist, COPY will report an error and stop processing.

REPLACE

Specifies that if the table already exists, it will be dropped and replaced with the new data. If the table does not already exist, it will be created and the data inserted into it.

tablename

Specifies the destination table to which you are copying data.

columnlist

Specifies that only data from the specified columns should be copied.

subquery

Specifies the query to be used to select the data to be copied.

Note that you can use any kind of query to select the data to be copied. For example, you can COPY select rows from the source table by using a WHERE clause. You can also COPY specific columns from the source table by listing those columns after the SELECT statement, instead of using the asterisk that means all columns. The copied columns will have the same names in the destination table that they had in the source table.

Following is an example of a COPY command that copies only two columns from the source table specified by the alias BOSTON, and copies only those rows in which the value of DEPTNO is 30. Since the TO clause is omitted, the table is copied to the database to which SQL*Plus is currently connected:

COPY FROM SCOTT/TIGER@BOSTON 
		REPLACE EMPCOPY2 
		USING SELECT ENAME, SAL 
			FROM EMP 
			WHERE DEPTNO = 30;

In the following example, the command copies the entire table EMP from the remote database specified by the SALES service name in the TNSNAMES.ORA file. Because the TO clause is omitted, the table is copied to the database to which SQL*Plus is currently connected. The new table is named EMP2: 

COPY FROM SCOTT/TIGER@SALES 
		CREATE EMP2 
		USING SELECT * FROM EMP;

You can also copy data to a local table from a remote table using the syntax: 

COPY FROM SCOTT/TIGER@servicename 
		INSERT INTO local_table 
		SELECT * FROM remote_table@dblink

For further information about the COPY command, see the SQL*Plus User's Guide and Reference Manual.

Support for Operating System Authorized Logins

In SQL*Net version 1, Oracle provided support for operating system authorized logins across a network using the special database login accounts. This support continues in SQL*Net version 2 on the same platforms and protocols for compatibility of function, but is not expanded to additional platforms and protocols.

Operating System Authorized Logins

Operating system authorized logins to a database allow a user who has operating system privileges on a machine to get automatic access to an Oracle database on the machine without supplying a database password. The assumption is that having supplied a password to the operating system, that user can be trusted to access his data in his own operating-system-authorized user account on that machine. The default prefix for this account is OPS$; however, the database administrator may assign some other prefix.

Extending this notion over a network, operating system authorized logins allow users to log into a database on a remote machine using their local userid, without supplying a database password. The operating system user account must match the username of the operating system authorized account.

For further information about user authentication, see the Oracle7 Server Distributed Systems, Volume I.

Warning about Operating System Authorized Logins

It is not uncommon for many employees to have DOS or other types of workstations on their desks over which they have complete control. It is a trivial task for such a person to create a username of any valid string of characters. If John Doe is the president of a company and uses operating system-authorized logon accounts across the network, any user can create a JDOE account on a personal workstation and access John's data without a password. On some server platforms this is more difficult than described, but never impossible. This is a particularly serious problem where an OPS$ account has DBA privileges. In such cases, it is very easy to masquerade as an Oracle DBA.

Oracle's Recommendation Regarding Operating System Authorized Logins

Because of these security implications, Oracle Corporation recommends against using operating system authorized logins to remote databases. This does not mean you cannot use operating system authorized login accounts in your database; it simply prevents them from being used across the network. The same OPS$ account can still be accessed if a password is supplied in connections over the network.

If you have applications that rely on operating system authorized logins across a network, you should consider altering them to require the user to supply a password when connecting over the network.

In SQL*Net version 1 the ability to perform operating system authorized logins exists in some SQL*Net TCP/IP and SQL*Net DECnet drivers on UNIX and VMS systems. The default setting is for operating system authorized logins to be enabled unless explicitly disabled. We recommend that you explicitly disable operating system authorized logons where they exist in any version 1 drivers you have installed. For information regarding your platform and SQL*Net Version 1 drivers, and how operating system authorized logins can be disabled, see your Oracle operating system specific manuals regarding SQL*Net version 1.

In SQL*Net version 2, operating system authorized login support is enhanced to make it more difficult to act as an imposter, but it is still not impossible. By default, operating system authorized logins are disabled in SQL*Net version 2.

Note: If externally authenticated logins are enabled (by setting the REMOTE_OS_AUTHENT parameter to TRUE), it will be possible to connect without a password even though the underlying SQL*Net protocol cannot verify the identity of the user. For this reason, REMOTE_OS_AUTHENT should always be set to FALSE (the default value). If it is necessary to make externally authenticated logins, then a secure protocol such as the Bequeath (Pipe) driver should be used.

In all cases with SQL*Net version 1 or version 2, Oracle recommends leaving remote DBA privileges disabled.

Support for Network Authentication Adapters in Release 2.2

With SQL*Net release 2.2 and Oracle7 Server release 7.2, the ability to use network authentication services for single sign-on and smart card authentication is provided. Use Oracle Network Manager to configure these authentication adapters. A site will have a choice of which adapters to link into their SQL*Net configuration. The Oracle Advanced Networking Option is required to enable these authentication adapters. For information on configuring network authentication adapters, see the Oracle Advanced Networking Option Administrator's Guide.

A discussion of security issues in networked systems is in Chapter 6 of Oracle7 Server Distributed Systems, Volume I.

Authentication Services

Authentication services for single sign-on servers or smart cards are usually part of a Network Operating System (NOS) which overlays several machines. Administration of a network with many machines can be centralized by creating "network users" who have the same identity and privileges no matter which machine they are actually using.

Network Identity

All authentication services provide the concept of a network identity so that no matter which machine a user is logged into, he or she can log onto the network and be identified as the same person. This method of authentication ensures that a user is who he or she claims to be.

Network Roles

Because most operating systems support some concept of roles, groups, or identifiers, many authentication services such as single sign-on servers based on Kerberos can support the concept of network roles. For example, an administrator creates the role and then assigns it to certain network users. Role definitions are consistent across all machines linked by the authentication service.

Secure External Authentication Logins

Using the network identity provided by the authentication service, the database can provide secure external authentication over a non-secure protocol such as TCP/IP.

Note: External authentication refers to authentication by either a network service or the operating system. OS authentication refers to operating system authorization only.

Users request external authentication using the same method they used previous to release 7.2. Users requesting external authentication still use a '/' to indicate the lack of a username and password. 

SVRMGR> CONNECT /@ny
Connected.

If an authentication adapter is available, the server will use it to find the user's network identity. If an authentication adapter is not available, operating system authorization will be performed. If the connection is not secure, the value of REMOTE_OS_AUTHENT will be used to determine if the login may proceed.

Note: It is highly recommended that REMOTE_OS_AUTHENT be set to FALSE, because most protocols are not secure.

Secure Database Links

Proxy authentication (secure database links, that is, secure server-to-server connections) are used when a user who has been externally authenticated by a network service attempts to use an anonymous database link (one without a username or password specified). However, the network service must support some type of credential that can make use of a proxy login. When a connection is requested, the credentials are passed from the local database server to the remote server. The remote server uses the credentials to obtain the network identity of the originating client process.

Network Roles and Privileges

Similar to the secure authentication behavior, if an authentication adapter is linked in, any external roles are retrieved from the network and not the operating system. If an authentication adapter is available (installed and linked into SQL*Net configuration), then it is used to authenticate the user; if not, then the operating system is used. If network roles are supported by the authentication service, they are used.

Configuring external roles varies depending on the authentication service. Instead of using the SID to name network roles (as OS roles does), network roles use the global database name. For example, for DCE authentication, something similar to the following naming convention to configure roles would be used: 

ORA_<global_database_name>_<role_name>[_[A][D]]

Note: Exact syntax to define network roles and privileges may vary depending on the authentication adapter in use.

Remote authentication provides a network version of the OSDBA and OSOPER privileges: SYSDBA and SYSOPER refer to the privileges necessary to perform an internal connection, whether verified by the operating system, password file, or network. A format similar to the following would be used to define network privileges: 

ORA_<global_database_name>_[DBA|OPER]_SYS

See the Oracle Advanced Networking Option Administrator's Guide for information on configuring network roles and privileges for specific authentication adapters.


Go to previous file in sequence Go to next file in sequence
Prev Next		 Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.		 Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index