Understanding SQL*Net
Upgrading from Version 1 to Version 2.x
The act of upgrading from SQL*Net version 1 to version 2.x requires few actual changes. You mustdo the following:
- Update embedded database links on the servers.
- On a multi-user system, relink any 3GL programs that you wish to use with SQL*Net version 2.
Locating Connect Strings
Before you can update SQL*Net version 1 connect strings into SQL*Net version 2.x connect descriptors you must first understand the scope of the potential change. Depending on your SQL*Net version 1 installation, there may be connect strings in both the client application and server, and particularly on the client, they can be in any number of places.
Application Connect Strings
For example, at the application, connect strings can be found in:
- the SQL*Net version 1 alias file for that platform (for an Oracle RDBMS version 6 or earlier.)
Most platforms have a means of assigning a single name to represent a connect string to simplify its use. See your Oracle operating system-specific documentation for the SQL*Net version 1 drivers on your platform.
Commonly an operating system startup program can start another program to simplify a complex command string. For example, an operating system startup program called THINK might start an application that connects to a database with SQL*Forms using a command such as:
runform think scott/tiger@connect_string
These programs allow an operating system command like the one in the THINK example above to be presented as a menu option.
Some non-Oracle products allow access to Oracle data by way of connect strings embedded in the application. For example, DDE Manager allows Microsoft Excel to access Oracle data from a spreadsheet; to do this, the spreadsheet may contain a connect string.
Server Connect Strings
On the server, connect strings are usually found only in the data dictionary tables USER_DBLINKS or DBA_DBLINKS, where they are inserted by a CREATE DATABASE LINK statement.
Changing Connect Strings
This section lists the procedure for migrating your SQL*Net version 1 connect strings to SQL*Net version 2.x connect descriptors:
1. Consider the requirements for your network, identifying which nodes will be running SQL*Net version 1, version 2.x, or both.
2. Determine where and how SQL*Net version 1 connect strings are used in your installation. Find how they are used at the system level and at the user level. (For example, connect strings may be stored in a global TNSNAMES.ORA accessible by everyone in the network, as well as in individual users' private TNSNAMES.ORA files.)
3. Install SQL*Net version 2.x following the instructions in the Oracle documentation for your platform.
4. Create the necessary configuration files using Oracle Network Manager. If you are adding SQL*Net version 2.x support to an existing SQL*Net version 1 installation, SQL*Net version 1 operation will be unaffected; that is, the version 1 listener and configuration files will function as before. The TNSNAMES.ORA configuration file (or Oracle Names or a Native Naming service such as NIS) contains service names that act as aliases for the SQL*Net version 2.x connect descriptors for each database.
Note: Service names and global database names are often the same; for example, HR.US.ACME.COM.
5. Distribute the TNSNAMES.ORA file so that all clients and servers running SQL*Net version 2.x have access to it.
Note: If the network includes Oracle Names, the service names and connect descriptors are stored in a database and resolved by Oracle Names, so TNSNAMES.ORA is not necessary. Similarly, if Oracle service names are stored in a native naming service such as NIS or DCE's CDS, then TNSNAMES.ORA files are not necessary.
6. On all version 2.x clients, change all version 1 connect strings to the equivalent service names mapped to version 2.x connect descriptors if the node the clients reference can run version 2.x. Not all nodes may be upgraded, but the ones that are should use SQL*Net version 2.x.
Note: If the network includes Oracle Names, global database links are available, and can be edited using the Network Manager. See Chapter 5 in the Oracle Network Manager Administrator`s Guide for details.
8. Test the new configuration. Make sure the new reference works as it did with SQL*Net version 1.
Sample Upgrade for Two Nodes
This section provides a simple example of the eight-step procedure described in the previous section, "Changing Connect Strings". If you think in terms of pairings, upgrading any number of nodes from SQL*Net version 1 to version 2.x should not be difficult.
This example uses three nodes, one client and two servers, as shown in Figure 4 - 5. It shows how the client changes to access either server over version 2.x, and how the DETROIT server changes to access the CLEVELAND server.
Figure 4 - 5. Sample Nodes for V2 Upgrade
1. First, determine that all three nodes will be upgraded to SQL*Net version 2.x.
The client node uses SQL*Net version 1 connect strings in operating system-specific startup files and within SQL*Menu. They currently appear as follows:
For the CLEVELAND server:
T:CLEVELAND:JOINTS
T:DETROIT:PARTS
In addition, the CLEVELAND server has a database link connecting it to a table in the DETROIT server as follows:
CREATE DATABASE LINK CAR
CONNECT TO MOTOR IDENTIFIED BY CITY
USING 'T:DETROIT:PARTS'
2. Perform the software installation as described in the Oracle operating system-specific manual for the platforms involved.
3. Use Oracle Network Manager (described in the Oracle Network Manager Administrator's Guide) to create the configuration files, including LISTENER.ORA, TNSNAMES.ORA, and SQLNET.ORA. In this example, the resulting TNSNAMES.ORA file would have the following entries, where CLEVELAND and DETROIT are service names mapped to their respective connect descriptors:
CLEVELAND.SALES.ACME =
(DESCRIPTION=
(ADDRESS=
( COMMUNITY=TCP.SALES.ACME)
(PROTOCOL=TCP)
(HOST=CLEVELAND.SALES.ACME)
( PORT=1521))
(CONNECT_DATA=
(SID=JOINTS)))
DETROIT.SALES.ACME =
(DESCRIPTION=
(ADDRESS=
(COMMUNITY=TCP.SALES.ACME)
(PROTOCOL=TCP)
(HOST=DETROIT.SALES.ACME)
(PORT=1521))
(CONNECT_DATA=
(SID=PARTS)))
4. Distribute the configuration files so that they are accessible to the appropriate clients and servers, as described in the Oracle Network Manager Administrator's Guide. Note that all nodes refer to a given server the same way. See the Oracle operating system-specific documentation for your platforms for the correct locations of the files.
Note: If you are using Oracle Names, the TNSNAMES.ORA file is unnecessary. The service names and connect descriptors are stored in a database and accessed by Oracle Names. Similarly, if you have configured an Oracle Native Naming Adapter for a client type, service names are stored in a native naming service such as NIS. Refer to the Oracle Names Administrator's Guide and the Oracle Network Manager Administrator's Guide for further information.
runform max/python@DETROIT.SALES.ACME
runform max/python@CLEVELAND.SALES.ACME
6. Change the database link definition in the CLEVELAND server :
CREATE DATABASE LINK DETROIT
CONNECT TO MOTOR IDENTIFIED BY CITY
USING 'DETROIT'
Because in SQL*Net version 2 the database link name must match the global database name, the database link name must be changed from CAR to DETROIT. In this example, the CLEVELAND server and the DETROIT server are in the same domain; therefore you do not need to include the domain in the CREATE DATABASE LINK statement.
With Oracle Names, you could create a database link named CAR. It would require two steps:
- First, use the Network Manager to create the alias CAR for the service name DETROIT.SALES.ACME.
- Then create a database link from CLEVELAND to DETROIT using CAR as the link name. You do not have to include a USING clause because Oracle Names finds the service name that CAR is mapped to in the network definition database.
CREATE PUBLIC DATABASE LINK CAR
CONNECT TO MOTOR IDENTIFIED BY CITY
7. At the client, run an application that uses the database link DETROIT. If the client connects to the CLEVELAND server and the database link returns the data, then those components are functioning correctly. Run a similar test of the client/server link between the client and the DETROIT server.
Coexistence of Version 1 and Version 2
If you have a large number of SQL*Net version 1 connect strings to update, you can install SQL*Net version 2.x beside SQL*Net version 1 in both the clients and servers as a temporary method of allowing version 2.x functionality to coexist with version 1 connect strings. All existing connect strings will continue to function within their version 1 limitations. This may make the migration more manageable.
Note: It is not possible to have both SQL*Net version 1 and SQL*Net version 2 running simultaneously on an MS-DOS machine.