From Oracle Technical Journal (a.k.a. OReview Magazine)
In its most basic form, SQL*Net is a software tool that enables a network of
Oracle clients and servers to communicate transparently on top of any underlying
network topology or protocol via SQL. Although SQL*Net is a very robust and
sophisticated tool, you must appreciate the inherent complexity that accompanies
its flexibility. This article provides a no-nonsense overview of the SQL*Net
architecture. All of the examples are based on Unix.
SQL*Net is not easy to install. You need the following files on the server
side in order to operate SQL*Net 2.0 on a Unix system:
- /etc/TNSNAMES.ORA -- Used for outgoing database requests, this file
contains all of the database names (or service identifiers, SIDs) running on
the processor, as well as domain name, protocol, host, and port information.
When a new database is added to a box, you must update this file (changes to
TNSNAMES.ORA become effective instantly). Note: SQL*Net version 1.0 equivalent
is /etc/ORATAB.
- /etc/LISTENER.ORA -- This file contains a list of destinations for
outgoing database connections. When you add a new destination database to a
Unix host, you must also add it to this file.
- /etc/HOSTS -- This file lists all of your network addresses.
- /etc/SERVICES -- This file lists all of the services available on a
server, including SQL*Net services. It is no longer necessary with version
2.0.
- The only file required on the client side is /etc/TNSNAMES.ORA. (On a PC
client, this file is determined by the path in the c:\windows\oracle.ini
file.)
- (Although the Oracle Network Manager now sets up these file automatically,
sometimes you must access or set them up manually.)
In version 2.0 and later (2.2 was due at press time, and 2.3 is due in
March), Oracle added several important enhancements to SQL*Net, in addition to
the badly needed bug fixes. SQL*Net now allows multiple "community" access. A
community is a group of computers that share a common protocol (such as TCP/IP
or LU6.2). Furthermore, the Oracle7 database engine now defines a multithreaded
server (MTS) for servicing incoming data requests. In the MTS, all communication
to the database is handled through dispatchers (single or multiple). In SQL*Net
version 1.0, a separate process is "spawned" for each connection (you can view
these connections via the Unix "ps" command).
When upgrading to SQL*Net 2.0 or later, you should be aware of subtle
differences in how it handles communications as opposed to version 1.0. (See
Figure 1, page 37.) Version 1.0 uses an orasrv component on the destination
database to listen for incoming requests, while version 2.0 uses a process
called tnslsnr (TNS listener). Also, SQL*Net 1.0 cannot use an MTS.
When you make a connection to SQL*Net, it passes the request to its
underlying layer, the TNS, where the request is transmitted to the appropriate
server. At the server, SQL*Net receives the request from TNS and passes the SQL
to the database. TNS stands for "transparent network substrate" -- a fancy term
meaning a single, common interface to all protocols that lets you connect to
databases in physically separate networks. At the lowest level, TNS communicates
to other databases with message-level send/receive commands.
On the client side, the User Programmatic Interface (UPI) converts SQL to
associated parse, execute, and fetch statements. The UPI parses the SQL, opens
the SQL cursor, binds the client application, describes the contents of returned
data fields, executes the SQL, fetches the rows, and closes the cursor. Oracle
attempts to minimize messages to the server by combining UPI calls whenever
possible. On the server side, the Oracle Programmatic Interface (OPI) responds
to all possible messages from the UPI and returns requests.
For server-to-server communication, there is no UPI; instead, a Network
Programmatic Interface (NPI) resides at the initiating server. The responding
server uses its OPI.
SQL*Net supports network transparency in such a way that the network
structure may be changed without affecting the SQL*Net application. Location
transparency is achieved via views, procedures, objects, synonyms, and so
on.
Let's trace a sample data request through SQL*Net. Essentially, SQL*Net will
look for the link name in the database link table (DBA_dDB_LINKS) and extract
the service name. The service name is then located in the TNSNAMES.ORA file and
the host name is extracted. It is a three-stage process, beginning with the link
name, which references the service name, which references the host name.
In Unix environments, you can find the host name in a host file (/etc/HOSTS),
and the Internal Protocol (IP) address is gathered. In the following example,
london_unix translates into an IP address of 143.32.142.3. The following four
steps illustrate how SQL*Net takes a remote request and translates it into the
IP address of the destination database:
1. Issue a remote request -- check the database link called london:
SELECT * FROM CUSTOMER@LONDON
2. Database link -- obtain service name (london_unix_d) using the link_name
(london):
CREATE PUBLIC DATABASE LINK LONDON
CONNECT TO LONDON_UNIX_D;
3. TNSNAMES.ORA -- obtain the SID (london_sid) using service name
(london_unix_d):
london_unix_d = (description=(address=
(protocol=tcp) (host=marvin)
(port=1521) (connect_data=(sid=london_sid)
(server=dedicated)))
4. /etc/hosts -- get IP address (143.32.142.3) using the SID
(london_sid):
143.32.142.3 london_sid london_unix.corporate.com
As you can see, this translation is a multistage process. The tnsnames.ora
file specifies the name of the host containing the destination database. For
Unix environments, this host name is then looked up in the /etc/hosts file to
get the IP address of the destination box.
Listing 1 shows a sample tnsnames.ora file. The service name is looked up in
tnsnames.ora, and if the service exists, the IP address is found in the
/etc/hosts file and a communication request is sent to the destination IP
address. Note that both of the entries in this file connect to london, but
london_unix_d directs SQL*Net to spawn a dedicated process, while london_unix
uses the MTS component because a shared server is specified.
#######################################
# FILENAME: TNSNAMES.ORA
#######################################
london_unix_d =
(description =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = tps)
(PORT = 1521)
)
)
(connect_data=
(sid=london_sid)
(server=dedicated)
)
)
london_unix =
(description =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = tps)
(PORT = 1521)
)
)
(connect_data=
(sid=london_sid)
(server=shared)
)
)
After you create the tnsnames.ora and /etc/hosts files, you can include any
tables from the London sites by qualifying the remote site name in the SQL
query:
SELECT CUSTOMER.CUSTOMER_NAME,
ORDER.ORDER_DATE
FROM CUSTOMER@LONDON, ORDER
WHERE CUSTOMER.CUST_NUMBER =
ORDER.CUSTOMER_NUMBER;
This query joins two tables at different locations, and the database link
called london determines how the Oracle connection will be established on the
destination system. Regardless of what user ID the local machine is using to
make the connection to the destination, the user ID used to make the connection
must have select privileges against the remote Customer table, or this query
will fail.
SQL*Net for Client/Server
SQL*Net typically establishes database communications via two ways: a
client-to-server connection or a server-to-server request.
Client-to-Server Connection. This is the easiest way to make a
database connection. The sending database makes a request by specifying a table
name suffixed with "@". SQL*Net takes it from there, seamlessly accessing the
remote database and returning the data to the initiating system.
Communication is established simply by making a remote request to a remote
database at one site. Within Oracle, @ specifies the remote database name, but
the functionality of the @ operator depends upon where it is used. The following
is an example:
sqlplus scott/tiger@london
SELECT COUNT(*) FROM EMPLOYEE;
COUNT(*)
-
162
In this request, Scott is using SQL*Plus to connect to the London database,
and "@london" is the service name, as defined in the tnsnames.ora file. SQL*Net
recognizes this as a client-to-server remote connection, and determines the
appropriate linkage to establish communications with London. Internally, Oracle
will check the tnsnames.ora file to ensure that London is a valid
destination.
Server-to-Server Request. The following is another way of connecting
to London from the same database using a database link:
sqlplus scott/tiger
SELECT COUNT(*) FROM EMPLOYEE@LONDON;
COUNT(*)
162
Unlike a client-to-server connection, where the connection is made directly
from SQL*Plus, a server-to-server request has Scott connecting to the local copy
of SQL*Plus to specify the remote table (in this case, employee@london). In
order for a server-to-server request to work, however, a database link must
define "London." A database link is a connection pathway to a remote database
that specifies the service name of that remote database. Without this database
link, the same request will fail:
sqlplus scott/tiger
SELECT COUNT(*) FROM EMPLOYEE@LONDON;
This request will give you an "ORA-02019: connection description for remote
database not found" error message. The reason is the way that Oracle defines the
@ operator. When entering an Oracle service such as SQL*Plus, the @ operator
will go directly to the tnsnames.ora file to manage the request, while the @
operator within an Oracle program specifies a database link.
To make the code functional, you must define a database link that specifies
the service name used to establish the connection. The database link name and
the service name are the same in the following example, but the database link
and the connect descriptor are not related in any way:
CREATE DATABASE LINK LONDON USING 'london';
SELECT COUNT(*) FROM EMPLOYEE@LONDON;
COUNT(*)
-
162
Let's take a closer look at the database link. In the previous example, there
is no mention of the user ID that is used to establish the connection on the
remote database. Because Scott is connecting to SQL*Plus from a remote server,
scott will be the user ID when the direct connection is established with the
London database. Therefore, Scott must have select privileges against the
Employee table in London in order for the query to work properly. Scott's
privileges on the initiating server have no bearing on the success of the query.
When select security is not an issue, you can enhance the database link syntax
to include a remote connect description:
CREATE DATABASE LINK LONDON USING 'LONDON'
CONNECT TO SCOTT1 IDENTIFIED BY TIGER1;
This way, all users who specify the London database link will connect as
scott1 and will have the same privileges that scott1 has on the London system.
In other words, there is no accountability, because everyone goes through the
same account. This is the issue of central accounts vs. individual accounts --
individual accounts are more secure because every account must be granted
privileges explicitly, but they are also harder to maintain.
Once you have established a communications pathway to the remote database,
you should implement location transparency. In relational databases such as
Oracle, one way to achieve location transparency is by creating database links
to the remote database and then assigning a global synonym to the remote tables.
The database link specifies a link name and a SQL*Net service name. You can
create database links with a location suffix that is associated with a host name
(in this example, london_unix).
You can use database links to allow applications to "point" to other
databases without altering the application code. For data warehouse
applications, you can replicate a table on another machine and establish links
to enable the application to point transparently to the new box containing the
replicated table.
To see the links for a database, query the Oracle dictionary:
SELECT DISTINCT DB_LINK FROM DBA_DB_LINKS;
Keep in mind that SQL*Net bypasses all operating system connections when it
connects to a database. All user accounts that are identified externally (that
is, without an Oracle password) will not be allowed in SQL*Net transactions
unless the init.ora parameter is changed. The "identified externally" clause
(ops$) in Oracle version 6 allowed the operating system to manage passwords, but
because SQL*Net bypasses the operating system, impostor accounts could be
created from other platforms, thereby bypassing security. Consequently, Oracle
recommends that "identified externally" accounts be forbidden for distributed
connections, and version 2.0 takes care of the problem through services such as
DCE.
It is interesting to note that Oracle will let you create accounts with an
ops$ prefix. Therefore, the operating system can manage its passwords, and you
can also have passwords within Oracle. For example, consider the following user
definition:
CREATE USER OPS$SCOTT IDENTIFIED BY TIGER;
Assuming that Scott has logged onto the operating system, Scott could enter
SQL*Plus with or without a password:
sqlplus /
sqlplus scott/tiger
This ability to connect directly to Oracle presents a confounding issue with
password management. Because two sets of passwords exist, one in the operating
system and the other in Oracle, you may need a third-party tool to keep the
passwords synchronized. (See Figure 2.)
Domains and Location Transparency
Many IS organizations recognize the need to track the locations of remote
databases while providing location transparency to their users and programmers.
Domains are especially important in situations of horizontal partitioning, where
tables with identical names are kept at numerous locations. Domains establish a
logical hierarchy of physical locations for the enterprise. (See Figure 3.)
The host name for each node in the network is appended to all of the table
names within the distributed network. For duplicate table structures that exist
at many locations, you can create abbreviated domains. Using the hierarchy in
Figure 3 as an example, assume that both Japan and Ohio have Customer tables
that are identical in structure but contain different rows. You can assign
Oracle synonyms as follows:
CREATE SYNONYM JAPAN_CUSTOMER FOR
CUSTOMER@HQ.SALES.ASIA.JAPAN;
CREATE SYNONYM OHIO_CUSTOMER FOR
CUSTOMER@HQ.MFTG.OHIO;
But where is the location transparency? To make the Customer table
transparent (and to make the query appear to be local), you can assign synonyms
for the Customer table in London:
CREATE SYNONYM CUSTOMER FOR
CUSTOMER@LONDON;
You can now run the following query with location transparency:
SELECT CUSTOMER.CUSTOMER_NAME,
ORDER.ORDER_DATE
FROM CUSTOMER, ORDER
WHERE CUSTOMER.CUST_NUMBER =
ORDER.CUSTOMER_NUMBER;
But what happens if Japan and London both have Customer tables? With SQL*Net,
you can define and use a set of synonyms with a view to select all tables,
regardless of their physical locations:
CREATE SYNONYM CUSTOMER1 FOR
CUSTOMER@HQ.SALES.ASIA.JAPAN;
CREATE SYNONYM CUSTOMER2 FOR
CUSTOMER@LONDON;
CREATE VIEW CUSTOMERS AS
SELECT CUSTOMER_NAME
FROM CUSTOMER1
UNION ALL
SELECT CUSTOMER_NAME
FROM CUSTOMER2;
You can now issue a simple SQL statement to get all customer names:
SELECT * FROM CUSTOMERS;
Managing SQL*Net
The following examples describe some of the utilities you can use to manage
SQL*Net sessions effectively. You should be aware that some of these examples
are operating system-independent and may not apply to your environment.
Showing/Killing SQL*Net Sessions. On systems running SQL*Net 2.0, you
can use a session script to query the number of dedicated and shared servers on
the system. Although you can access this information via SQL*DBA and Server
Manager, you can run the following SQL report to show all connected users:
ttitle "dbname Database|UNIX/Oracle Sessions";
spool /tmp/session
set heading off;
select 'Sessions on database '||substr(name,1,8)
from v$database;
set heading on;
select
substr(a.spid,1,5) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL
SESSION',
''''||'SID, SER#'||''''||';' from dual;
The output from this script is shown in Listing 2.
[cad1]sh: sqlx session
cert Database
Unix/Oracle Sessions
PID SID SER# BOX USERNAME OS_USER PROGRAM
17723 15 7462 cad2 OPS$JREINH jreinhar unmenu50@t:cad:cad2
18422 14 4607 cad2 OPS$LJOYCE ljoyce runmenu50@cad2
18524 12 5390 cad2 OPS$JSTEPH jstephan runmenu50@cad2
19047 6 2308 cad2 ECHO_LNK ljoyce runmenu50@cad2
19127 10 2603 cad2 OPS$BWYSZO bwyszomi sqlplus@cad2 (TNS interface)
19155 7 4081 cad1 OPS$ORACLE oracle sqlplus@cad1 (Pipe Two-Task)
10058 7 2084 HRIS ggallipe C:\WINDOWS\system\KRNL386.EXE
10091 17 4733 HRIS mbuddend C:\WINDOWS\system\KRNL386.EXE
6 rows selected.
To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION ?SID, SER#?;
Unlike dedicated SQL*Net sessions, you cannot kill multithreaded SQL*Net
sessions directly from the operating system. For example, you can identify a
runaway session on a dedicated server by using the Unix "ps-ef|grep ora"
command, and subsequently kill it using the "kill -9 nnn" command. With the MTS,
operating system processes no longer exist for each separate task, and you must
use the Oracle SQL alter system kill session command to kill the task at the
Oracle subsystem level (using the "alter system kill session 'sid, ser#';"
command).
To kill a user in an MTS session, enter SQL*Plus and type:
SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
SID SERIAL# USERNAME
8 28 OPS$xxx
10 211 POS$yyy
13 8 dburleso
If "dburleso" is the session you want to kill, enter the alter system kill
session '13, 8' command. You can also accomplish this with SQL*DBA and Server
Manager.
This cumbersome method of clobbering runaway SQL*Net connections can be very
annoying in development environments in which dozens of programmers are testing
programs, and they must call the DBA every time they want to kill a runaway
task. The only alternative, however, is to grant the programmers alter system
authority on test platforms.
The type of SQL*Net connection you have is determined by the way you
configure SQL*Net to connect with the database. Listing 3 shows how different
two_task settings can affect Oracle sessions.
[oracle]iss: TWO_TASK=t:iss:cert /* old SQL*Net version 1.0 */
[oracle]iss: sqlx session
SERVER Oracle username O/S
User Machine Program
----------- ----------------- ---- -----
--------------------------------------
DEDICATED OPS$ORACLE oracle iss
sqlplus@t:iss:iss (TCP Two-Task)
[oracle]iss: TWO_TASK=d_cert
/* dedicated service name on version two */
[oracle]iss: sqlx session
SERVER
Oracle username O/S User Machine Program
-----------
----------------- ---- ----- --------------------------------------
DEDICATED OPS$ORACLE
oracle iss sqlplus@iss (TNS interface)
[oracle]iss: TWO_TASK=cert /* version
2.0 service name */
[oracle]iss: sqlx session
SERVER
Oracle username O/S User Machine Program
-----------
----------------- ---- ----- --------------------------------------
SHARED OPS$ORACLE oracle
iss sqlplus@iss (TNS interface)
PC task initiated from
ODBC/PowerBuilder
[oracle]iss: sqlx session
SERVER
Oracle username O/S User
Machine Program
-----------
----------------- ---- ----- --------------------------------------
DEDICATED OPS$ORACLE oracle
iss c:\windows\krnl386.exe
Managing the MTS. One of the problems with version 1.0 was that each
incoming transaction was spawned by the listener as a separate operating system
task. With version 2.0, Oracle now has a method that allows the listener
connection to dispatch numerous subprocesses. With the MTS, all communications
to a database are handled through dispatchers, rather than with separate Unix
process IDs (PIDs) on each database. This translates into faster performance for
most online tasks. Even local transactions will be directed through the MTS, and
you will no longer see a PID for your local task when you issue "ps-ef|grep
oracle". A sample init.ora for starting the MTS is shown in Listing 4.
# ???????????-
# Multi-threaded Server
# ???????????-
MTS_DISPATCHERS = tcp,3
MTS_LISTENER_ADDRESS = (ADDRESS=(PROTOCOL=tcp)
(HOST=marvin) (PORT=1521))
MTS_MAX_DISPATCHERS = 5
MTS_MAX_SERVERS = 20
# ?????????????-
# Distributed systems options
# ?????????????-
DISTRIBUTED_LOCK_TIMEOUT = 60
DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME = 200
DISTRIBUTED_TRANSACTIONS = 6
However, be aware that the MTS is not a panacea. There are times when you
want to invoke a dedicated process for your program. For batch processing
applications, you may get better performance using a dedicated process.
In general, the MTS offers benefits such as reduced memory use, fewer
processes per user, and automatic load balancing. But, it is often very
confusing to know if the MTS is turned on, much less if it is working properly.
Remember the following rules of thumb about the MTS:
- The MTS is governed by the init.ora parameters. If no MTS parameters are
present in init.ora, the MTS is disabled.
- The MTS is used when the MTS parameters are in init.ora and requests are
made by service name (such as @myplace). In other words, you must eliminate
all version 1.0 connect strings (such as t:unix1:myplace).
- Each user of the MTS requires 1K of storage. Plan to increase your
shared_pool_size. You can also implement a resource limitation via an MTS
tuning parameter.
- The v$queue and v$dispatcher system tables indicate if the number of MTS
dispatchers is too low. Even though the number of dispatchers is specified in
the init.ora file, you can change it online in SQL*DBA with the alter system
command:
SQLPLUS> ALTER SYSTEM SET MTS_DISPATCHERS = 'TCPIP,4';
- If you encounter problems with the MTS, you can quickly regress to
dedicated servers by issuing an alter system command. The following command
turns off the MTS by setting the number of MTS servers to zero:
SQLPLUS>
ALTER SYSTEM SET MTS_SERVERS=0;
- In order to use ops$, you must set two init.ora values to true (they
default to false):
remote_os_authent = true remote_os_roles = true
- When both SQL*Net 1.0 and 2.0 are installed, users can connect to the
server either via a dedicated server or via the MTS. However, you need a
dedicated server for administration options such as startup and shutdown.
- In some cases, you must shutdown and restart the server if the listener is
stopped, or it will restart in dedicated mode. The listener reads the MTS
parameters only if it is running before startup of the instance.
Managing the Listener. As a service request is intercepted by an
Oracle server, the listener may direct the request via a dedicated server, an
MTS, or an existing process (prespawned shadow). The key is whether the
connection contacts the listener via a service name or bypasses the listener
with the two_task connect string. If the listener is contacted as part of the
connection, and the MTS parameters are defined to init.ora, the client will use
the MTS.
There are five basic listener commands: reload, start, stop, status, and
services. The output of the status and services commands is shown in Listing 5.
The listener decides (based on the request) whether to dispatch a connection to
a dedicated-server process (which it spawns) or to use the MTS. The programmer
has several options when deciding how Oracle will manage the process. You can
implement dedicated requests by specifying a version 1.0 connect string, or by
using a service name that specifies "server=dedicated" in the tnsnames.ora
file.
lsnrctl status
[oracle]cad2: lsnrctl stat
LSNRCTL for HPUX: Version 2.0.15.0.0 - Production on 16-SEP-94 15:38:00
Copyright (c) Oracle Corporation 1993. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=cad2)(PORT=1521))
STATUS of the LISTENER
-------------------------------------------------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 2.0.15.0.0 - Production
Start Date 29-AUG-94 13:50:16
Uptime 18 days 1 hr. 47 min. 45 sec
Trace Level off
Security OFF
Listener Parameter File /etc/listener.ora
Listener Log File /usr/oracle/network/log/listener.log
Services Summary...
dev7db has 1 service handlers
cad2db has 1 service handlers
The command completed successfully
lsnrctl services
[oracle]marvin: lsnrctl services
LSNRCTL for HPUX: Version 2.0.15.0.0 - Production on 16-SEP-94 15:36:47
Copyright (c) Oracle Corporation 1993. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=marvin)(PORT=1521))
Services Summary...
tdb000 has 4 service handlers
DISPATCHER established:1 refused:0 current:2 max:55 state:ready
D001 (machine: marvin, pid: 4146)
(ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1323))
DISPATCHER established:1 refused:0 current:2 max:55 state:ready
D000 (machine: marvin, pid: 4145)
(ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1321))
DISPATCHER established:0 refused:0 current:1 max:55 state:ready
D002 (machine: marvin, pid: 4147)
(ADDRESS=(PROTOCOL=tcp)(DEV=5)(HOST=141.123.224.38)(PORT=1325))
DEDICATED SERVER established:0 refused:0
The command completed successfully
Note: Local connections will use the listener if the MTS is defined. Even
internal invocations to Oracle (for example, sqlplus/) will add a connection to
the MTS.
Miscellaneous Management Tips. Keep the following rules in mind:
- Just as the /etc/oratab file for SQL*Net version 1.0 is interpreted, the
tnsnames.ora file is also interpreted. This means that you can change it at
any time, so you can change or add destinations during production hours.
However, changes to listener.ora require that the listener be reloaded with
lsnrctl reload.
- Don't forget to change your oracle_home/bin/oraenv file to unset
oracle_sid and set two_task=sid.
- There are three logs in SQL*Net:
listener log = /usr/oracle/network/log/listener.log
sqlnet log = /usr/oracle/network/log/sqlnet.log trace log =destination
set with the TRACE_DIRECTORY_LISTENER parameter of the /etc/listener.ora
file
- There are three levels of tracing in SQL*Net:
lsnrctl trace admin
lsnrctl trace user lsnrctl trace off
It is possible to run two
listeners, one for version 1.0 and another for version 2.0. If a version 1.0
connect string is sent, a version 1.0 listener (tcpctl) will be used.
Conversely, if a TNS connect description is sent, the version 2.0 listener
(lsnrctl) will be used. A connect description is the name of a database (such
as @mydata), which maps to the tnsnames.ora on the sending side, and
listener.ora on the receiving side.
- You should be aware that the functions of the oracle_sid and two_task
variables has changed. To use the MTS while you are "local" to the database,
you should unset the oracle_sid variable and set the two_task to the sid name
(export two_task=mydb). If the oracle_sid is active, you will still be able to
connect, but you will not be able to take advantage of the MTS. You must
change all login scripts and oracle_home/bin/oraenv files to reflect this new
functionality.
With MTS, there are now three ways to establish distributed database
communications: by shared service-name (sqlplus /@cad2db); by dedicated service
name (sqlplus /@d_cad2db -- prefixing the sid with "d_" will direct the listener
to spawn a dedicated process for your program); and by a (two_task) server
connect string (sqlplus /@t:host:sid). This latter approach will bypass the MTS
and use a dedicated process.
Managing Two-Phase Commits (2PCs). When a distributed update (or
delete) has finished processing, SQL*Net will coordinate commit processing so
that if any portion of the transaction fails, the entire transaction will roll
back. The first phase of this process is a "prepare phase" to each node,
followed by the commit, and then terminated by a "forget phase."
If a distributed update is in the process of issuing the 2PC and a network
connection breaks, Oracle will place an entry in the dba_2pc_pending table, and
the recovery background process, reco, will roll back or commit the good node to
match the state of the disconnected node, thereby ensuring consistency. You can
activate reco via the alter system enable distributed recovery command. The reco
process is applicable only when the distributed option is installed in Oracle,
and is only used to manage 2PCs. If your system does not perform cross-database
synchronization, you may want to disable reco.
The dba_2pc_pending table contains an "advise" column that directs the
database to either commit or rollback the pending item. You can use the alter
session advise syntax to direct the 2PC mechanism. For example, to force the
completion of an insert, you can enter the following:
ALTER SESSION ADVISE COMMIT;
INSERT INTO PAYROLL@LONDON . . . ;
When a 2PC transaction fails, you can query the dba_2pc_pending table to
check the "state" column. You can enter SQL*DBA and use the "recover in-doubt
transaction" dialog box to force either a rollback or a commit of the pending
transaction. If you do this, the row will disappear from dba_2pc_pending after
the transaction has been resolved. If you force the transaction the wrong way
(for example, to roll back when other nodes have already committed), reco will
detect the problem, set the mixed column to "yes," and the row will remain in
dba_2pc_pending table.
SQL*Net on the Job
Although I can only gloss over the high points, sophisticated tools such as
SQL*Net require a great deal of knowledge and skill to use effectively. As
systems continue to evolve into complex distributed networks, interdatabase
communications will become even more complex, and require more sophisticated
tools. While object orientation promises to make interdatabase communications
simple, the DBA in the trenches will continue to struggle with implementing
everyday distributed database communications.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
