原帖地址：
http://orafaq.com/faqnet.htm
Back to Oracle FAQ Index
Oracle Networking Services FAQ (SQL*Net, Net8 and Net9)
$Date: 03-May-2004 $
$Revision: 2.11 $
$Author: Frank Naudé $
- What is SQL*Net/ Net8?
- What is the difference between Sql*Net V1, V2 and NET8?
- Where is the SQL*Net configuration files located?
- How does one configure SQL*Net?
- I have some trouble with SQL*Net. How can I produce a trace file?
- How can I set up a dedicated server connection?
- Can I upgrade to SQL*Net V2 if I still have V1 clients?
- How can I enable dead connection detection?
- What are inband and out of band breaks?
- What is a bequeath session?
- What can be done to increase SQL*Net performance?
- Can one get connected to a system regardless of machine failure?
- Can one grant or restrict access to a system via SQL*Net?
- Why do I get a 12154 error?
- Where can one get more info about SQL*Net/ Net8?
- How does one start and stop the Oracle Listener?
- What happens to connected users when the listener goes down?
- How does one start a server side SQL*Net trace?
- How does one setup an Oracle Names Server?
- How to get your listener to register itself with the Names Server?
- How does one register an Oracle Names Server Entry?
- How can I check if a listener registered itself with the Names Server?
Connection Manager Topics:
- What is the Connection Manager and what is it used for?
- How does one configure the Connection Manager?
- How does one route data through the Connection Manager?
Back to Oracle FAQ Index
What is SQL*Net/ Net8?
NET8 (called SQL*NET prior to Oracle8) is Oracle's client/server middleware product that offers transparent connection from client tools to the database, or from one database to another. SQL*Net/ Net8 works across multiple network protocols and operating systems.TNS or Transparent Network Substrate is Oracle's networking architecture. TNS provides a uniform application interface to enable network applications to access the underlying network protocols transparently.
The TNS architecture consists of three software components: TNS-based applications, Oracle Protocol Adapters (OPA), and networking software like TCP/IP.
- Back to top of file
What is the difference between SQL*Net V1, V2, NET8 and NET9?
SQL*Net V1 SQL*Net V2 Net8/ Net9 Default port 1525/tcp 1521/tcp 1521/tcp Start command tcpctl start lsnrctl start lsnrctl start Stop command tcpctl stop lsnrctl stop lsnrctl stop Connect string protocol:host:sid eg. T:SRV1:DB1 Specified in TNSNAMES.ORA Specified in TNSNAMES.ORA Config files /etc/oratab tnsnames.ora, sqlnet.ora & listener.ora tnsnames.ora, sqlnet.ora & listener.ora Env variable LOCAL= TWO_TASK= TWO_TASK= - Back to top of file
Where is the SQL*Net configuration files located?
The SQL*Net configuration files (like SQLNET.ORA and TNSNAMES.ORA) can be found in one of the following locations (SQL*Net searches for it in this order):- Directory pointed to by the TNS_ADMIN parameter ($TNS_ADMIN on Unix)
- /etc (Unix only)
- /var/opt/oracle (Unix only)
- $ORACLE_HOME/network/admin (or sometimes net8/admin directory)
- Back to top of file
How does one configure SQL*Net?
Most people (myself included) prefer to edit the SQL*Net configuration files by hand. The only "officially supported" configuration method, however, is via the Oracle Net8 Assistant or Oracle Net8 Easy Config utility (previously called Oracle Network Manager).This configuration utility is PC based. You need to generate the necessary files on your PC and FTP or copy them to the relevant operating systems you use Oracle on.
Look at the following sample configuration files:
$ORACLE_HOME/network/admin/sqlnet.ora ------------------------------------- automatic_ipc = ON # Set to OFF for PC's trace_level_client = OFF # Set to 16 if tracing is required sqlnet.expire_time = 0 # Idle time in minutes sqlnet.authentication_services = (ALL) names.directory_lookup = (TNSNAMES,ONAMES) names.default_domain = world name.default_zone = world $ORACLE_HOME/network/admin/tnsnames.ora --------------------------------------- dbname1, aliasname1, aliasname2 = (description = (address_list = (address = (protocol = tcp) (host = yourHost.domain) (port = 1521) ) ) (connect_data = (sid = yourSID) ) ) $ORACLE_HOME/network/admin/listener.ora --------------------------------------- LISTENER = # Listener name is LISTENER (address_list = (address= (protocol=ipc) (key=yourSID) ) (address= (protocol = tcp) (host = yourHost.domain) (port = 1521) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = ON TRACE_FILE_LISTENER = $ORACLE_HOME/network/trace/listener.trc SID_LIST_LISTENER = (SID_LIST= (SID_DESC= (SID_NAME=yourSID) (ORACLE_HOME=YOUR_ORACLE_HOME) ) )NOTE: A wrong TNSNAMES.ORA entry on a line will block all valid entries below. Copy names to the top until you find the incorrect entry. - Back to top of file
I have some trouble with SQL*Net. How does one produce a trace file?
Create/edit your SQLNET.ORA file. Your SQLNET.ORA file should contain the following lines to produce a trace file:trace_level_client=16 trace_unique_client=yes
Sometimes it is useful to only trace TNSPING packets. Add the following parameters to your SQLNET.ORA file:TNSPING.TRACE_LEVEL = 16 TNSPING.TRACE_DIRECTORY = /tmp/tnsping/
The following parameters are also worth setting:trace_file_client = cli.trc trace_directory_client = <path_to_trace_dir> log_file_client = sqlnet.log log_directory_client = <path_to_log_dir>
- Back to top of file
How does one set up a dedicated server connection?
When you configure your database to use MTS (Multi-threaded server), all client requests are handed off to one of the shared server processes by the listener, via a dispatcher. If you want certain clients to use a dedicated Server process, you need to set the dedicated server option in your database connect string: ie.SQLPLUS SCOTT/TIGER@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (PORT=1521) (NODE=yourServerName))) (CONNECT_DATA=(SID=yourSid) (SERVER=DEDICATED)))
You can also edit your TNSNAMES.ORA file and add the (SERVER=DEDICATED) part in the CONNECT_DATA list or simply set USE_DEDICATED_SERVER=ON in your SQLNET.ORA file. - Back to top of file
Can I upgrade to SQL*Net V2 if I still have V1 clients?
SQL*Net V1 cannot talk with SQL*Net V2, and vice versa. The only way to overcome this problem is to run SQL*Net V1 and V2 simultaneously on the same database server. You can then install SQL*Net V2 on your clients as time permits. SQL*Net V1 and V2 can coexist on the same server, or on the same client.You can also list V1 connect strings in your TNSNAMES.ORA file. Eg:
ORA1_NET1 = T:machine_name/port:database_nameNote that SQL*Net V1 is not available from version 7.3 of the database.
- Back to top of file
How does one enable dead connection detection?
Dead database connections can be detected and killed by SQL*Net if you specify theSQLNET.EXPIRE_TIME=nparameter in your SQLNET.ORA file (usually in $ORACLE_HOME/network/admin). This parameter will instruct SQL*Net to send a probe through the network to the client every n minutes, if the client doesn't respond, it will be killed.NOTE: This parameter is only useful on the database server side, specifying it on a client workstation will have no effect.
- Back to top of file
What are inband and out of band breaks?
Data exceptions like Control-C can be transmitted as part of the regular data stream (inband) or as a separate asynchronous message (outband). Obviously outband breaks are much faster as they can interrupt the flow of data.Out Of Bound Breaks (OOB) are enabled by default. One can disable OOB from the SQLNET.ORA file:
DISABLE_OOB=on
- Back to top of file
What is a bequeath session?
A bequeath session is a session for which the listener spawned a Dedicated Server Process and then passed (bequeaths) the connection to that server process. - Back to top of file
What can be done to increase SQL*Net performance?
- While a SQL statement is running SQL*Net polls the client continuously to catch CONTROL-C situations. This results into a lot of poll and fstat system calls.
The following SQLNET.ORA parameter can be specified to reduce polling overhead on your system:
BREAK_POLL_SKIP=n # Number of packets to skip between checking for breaks (default=4) - Prespawned server sessions. You can tell the listener to start up a pool of idle server processes. When a connection request is made, it doesn't have to start a server process; it just hands one of the idle processes to the client (and then starts a new connection in its own time). This is configured in LISTENER.ORA, in the SID_LIST_LISTENER section, as follows:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = yourSID) (PRESPAWN_MAX = 50) (PRESPAWN_LIST = (PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 5) (TIMEOUT = 2)))) )PRESPAWN_MAX: if there are over 50 sessions connected to the database, the listener won't prespawn any more.
POOL_SIZE: the listener will maintain an idle pool of 5 server processes. TIMEOUT: after a client disconnects, the listener will keep the freed-up server process around for two minutes, waiting for a new connection request, before killing that process. - Multiple listeners with load balancing. You can start multiple listeners on a server, and reference all of the listeners in the TNSNAMES.ORA file. When a client makes a connection request, the SQL*Net client will randomly pick one of the listeners to contact.
In LISTENER.ORA, specify multiple listeners as in:
# Define listener A... STARTUP_WAIT_TIME_LISTENER_A = 0 CONNECT_TIMEOUT_LISTENER_A = 10 LISTENER_A= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = yourHost.domain) (PORT = 1521))) SID_LIST_LISTENER_A = (SID_LIST = (SID_DESC = (SID_NAME = yourSID) (PRESPAWN_MAX = 50))) # Define the second listener... STARTUP_WAIT_TIME_LISTENER_B = 0 CONNECT_TIMEOUT_LISTENER_B = 10 LISTENER_B= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = yourHost.domain) (PORT = 1522))) SID_LIST_LISTENER_B = (SID_LIST = (SID_DESC = (SID_NAME = yourSID) (PRESPAWN_MAX = 50)))The TNSNAMES.ORA service for this database would be something like:oradb1.world = (description_list= (description= (address_list= (address= (protocol=tcp) (host=yourHost.domain) (port=1521))) (connect_data = (sid = yourSID))) (description = (address_list = (address= (protocol=tcp) (host=yourHost.domain) (port=1522))) (connect_data = (sid = yourSID))))
- While a SQL statement is running SQL*Net polls the client continuously to catch CONTROL-C situations. This results into a lot of poll and fstat system calls.
- Back to top of file
Can one get connected to a system regardless of machine failure?
You can place multiple address entries for a single connection alias in the TNSNAMES.ORA file. This means that you can connect to a database, even if some kind of physical failover occurred. Look at the following example:oradb1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = TCP_COMM) (PROTOCOL = TCP) (HOST = Machine01)) (ADDRESS = (COMMUNITY = TCP_COMM) (PROTOCOL = TCP) (HOST = Machine02))) (CONNECT_DATA=( (SID=oradb1))))Suppose Machine01 is down, then every new SQL*NET connection using service oradb1 will automatically login to Machine02. However, there is one restriction, the SID must be the same on both machines. This feature can provide guaranteed login for application servers and for the Oracle Parallel Server. - Back to top of file
Can one grant or restrict access to a system via SQL*Net?
Yes, add the following parameters to your sqlnet.ora file:tcp.validnode_checking = yes tcp.invited_nodes = (www.orafaq.org,139.185.5.111) tcp.excluded_nodes = (133.17.15.21)
The first line enables this features. The invited nodes is a list of the hosts, either DNS name or IP address, that are allowed to connect. The excluded nodes is a list of hosts that are not allowed to connect to the database.Note: The tcp.invited_nodes parameter takes precedence over the tcp.excluded_nodes parameter if both lists are present.
Note 2: For Oracle 8i and below these parameters needs to be set in a protocol.ora file on your server (located in $ORACLE_HOME/network/admin or $TNS_ADMIN).
- Back to top of file
Why do I get a 12154 error?
This error message occurs when a user attempts to logon to an Oracle database. The tnsnames.ora file was not found or has a syntax error. The Oracle communications software (SQL*Net) did not recognize the database connect descriptor as being valid. For GUI utilities, this is the name entered in the third field of the ORACLE logon box.You can find more information about this error in the Oracle Network Products Messages Manual:
ORA-12154 TNS:Could not resolve service name Cause: The service name specified is not defined in the TNSNAMES.ORA file. Action: Make the following checks and correct the error: - Verify that a TNSNAMES.ORA file exists and is in the proper place and accessible. See the operating system specific manual for details on the required name and location. - Check to see that the service name exists in one of the TNSNAMES.ORA files and add it if necessary. - Make sure there are no syntax errors anywhere in the file. Particularly look for unmatched parentheses or stray characters. Any error in a TNSNAMES.ORA file makes it unusable. See Chapter 4 in the SQL*Net Administrator's Guide. If possible regenerate the configuration files using the Oracle Network Manager. - Back to top of file
Where can one get more info about SQL*Net/ Net8?
- Back to top of file
How does one start and stop the Oracle Listener?
The Oracle SQL*Net listener can be started and stopped with the following commands:lsnrctl start LISTENER lsnrctl stop LISTENER
NOTE: 'LISTENER' is actually the name of the listener in the above example. It may be different on your system. Edit your listener.ora file to get the names of the defined listeners. - Back to top of file
What happens to connected users when the listener goes down?
Connected users will still be able to work. Users trying to establish new connections to the database (after stopping the listener) will not be able to connect until the listener is restarted. - Back to top of file
How does one start a server side SQL*Net trace?
Use:lsnrctl trace 16 (to turn the trace on) lsnrctl trace off (to turn the trace off)
Or set the following listener.ora parameters and reload:TRACE_LEVEL_LISTENER=16 TRACE_FILE_LISTENER=listener.trc (listener.trc is the default) TRACE_DIRECTORY_LISTENER=$ORACLE_HOME/network/trace
NOTE: 'LISTENER' is actually the name of the listener in the above example. It may be different on your system. Edit your listener.ora file to get the names of the defined listeners. - Back to top of file
How does one setup an Oracle Names Server?
Follow these steps to configure an Oracle Names Server:- Create a domain database: Run script $ORACLE_HOME/network/admin/namesins.sql ($ORACLE_HOME/network/names/namesini.sql on WinNT)
- From a Windows9x/ NT workstation, start the Oracle Net8 Assistant and configure
- Copy the generated configuration files to your server
- Start Oracle Names on the server: namesctl start
- Back to top of file
How to get your listener to register itself with the Names Server?
Edit your LISTENER.ORA file and add a line USE_PLUGANDPLAY_listener_name=ON for each listener defined on your machine. Secondly, assign a GLOBAL_DBNAME parameter for each listener.
Sample LISTENER.ORA file:
USE_PLUG_AND_PLAY_LISTENER = ON LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=IPC) (KEY= wblp-nt-011b_orcl.companyX.com) ) (ADDRESS= (PROTOCOL=IPC) (KEY= orcl) ) (ADDRESS = (COMMUNITY = TCPIP.companyX.com) (PROTOCOL = TCP) (Host = wblp-nt-011b.companyX.com) (Port = 1526) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = OFF SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = wblp-nt-011b_orcl.companyX.com) (SID_NAME = orcl) (ORACLE_HOME = /) (PRESPAWN_MAX = 10) ) ) - Back to top of file
How does one register an Oracle Names Server Entry?
Oracle Names Server entries are normally entered from the Oracle Network Manager (V7) or the Oracle Net8 Assistant. Both these tools run from your desktop.You can also manually register an entry on your server using the NAMESCTL command. Eg:
NAMESCTL> register mydb.world -t oracle_database -d (description=(address=(protocol=tcp)(host=123.45.67.8)(port=1526))(connect_data=(sid=MYDB)))
NOTE: the whole command must be entered on one line. Also, make sure you register this database with all Names Servers running in your domain.To check if your entry is correctly recorded in the Names Server, issue this command:
NAMESCTL> query mydb.world *
- Back to top of file
How does one check if a listener registered itself with the Names Server?
Issue the LSNRCTL command and type either SERVICES or STATUS. If the listener successfully registered itself with the Oracle Names server you will notice the keyword "Registered" next to the service name. Example:Services Summary... oraweb(Registered) has 1 service handler(s)
- Back to top of file
What is the Connection Manager and what is it used for?
The Oracle Connection Manager (CMan) is a Net8 process that relays network traffic to a different address, and optionally changes its characteristics. The Connection manager is commonly used for the following:- Connection Concentration
- Access Control
- Multiprotocol Support
- Provide Java applets to connect to a database that is not on the machine the Java applet was downloaded from.
- Back to top of file
How does one configure the Connection Manager?
The CMAN.ORA file specify Connection Manager configuration details. Look at this sample CMAN.ORA file:CMAN = (ADDRESS=(PROTOCOL=tcp)(HOST=141.145.83.4)(PORT=1610)) CMAN_ADMIN = (ADDRESS=(PROTOCOL=tcp)(HOST=141.145.83.4)(PORT=1650)) CMAN_RULES = (RULE_LIST = (RULE = (SRC=141.145.*.*) # Wildcard is "x" (DST=141.145.*.*) (SRV=ed23) (ACT=ACC) )) CMAN_PROFILE = (PARAMETER_LIST= (MAXIMUM_RELAYS=8) # Default is normally too small (LOG_LEVEL=1) (TRACING=NO) (SHOW_TNS_INFO=YES) (RELAY_STATISTICS=NO) ) - Back to top of file
How does one route data through the Connection Manager?
Code a TNSNAMES.ORA entry with two addresses. The first address specifies the address CM is listening on (coded in CMAN.ORA). The second is the address the traffic must be routed to. You also need to specify SOURCE_ROUTE=YES.ED23_cman = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=141.145.83.4)(PORT=1610)) (ADDRESS=(PROTOCOL=TCP)(HOST=zaedu2.za.oracle.com)(PORT=1923)) ) (CONNECT_DATA = (SERVICE_NAME = ed23) ) (SOURCE_ROUTE = YES) ) - Back to top of file
HOME | ASK QUESTION | ADD FAQ | SEARCH | E-MAIL US 
Topics
4582
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
