by George Pujol - gpujol@interserv.com

Contents

• Multiple Server Connections
• Versioning
• Troubleshooting your BDE-to-ORACLE connection
  • Server Problems
  • Client Problems
  • General Tips

If you want to connect to more than one remote server you just have to follow these simple rules:

• Each server must be running its own listener.
• Each listener must be listening on a different port and running a different SID.

We will assume we have three servers, one running OWS 7.3 for Netware and the other two running OWS 7.3 for Windows NT. We will name them ORACLE_NW, ORACLE_NT_ONE, and ORACLE_NT_TWO respectively. In order to simplify this example we will only use TCP/IP.

The following LISTENER.ORA will reside on each server

ORACLE_NW:

LISTENER=(ADDRESS_LIST=
            (ADDRESS=
               (PROTOCOL=TCP)
               (HOST=ORACNW)
               (PORT=1521)
            )
         )
         CONNECT_TIMEOUT_LISTENER=10
         TRACE_LEVEL_LISTENER=OFF
         SID_LIST_LISTENER=(SID_DESC=(SID_NAME=SID1)(ORACLE_HOME=APPVOL1:ORANW72))

LISTENER=(ADDRESS_LIST=
            (ADDRESS=
               (PROTOCOL=TCP)
               (HOST=ORACNT1)
               (PORT=1527)
            )
         )
         CONNECT_TIMEOUT_LISTENER=10
         TRACE_LEVEL_LISTENER=OFF
         SID_LIST_LISTENER=(SID_DESC=(SID_NAME=SID2)(ORACLE_HOME=C:\SERVER1\ORANT73))

LISTENER=(ADDRESS_LIST=
            (ADDRESS=
               (PROTOCOL=TCP)
               (HOST=ORACNT2)
               (PORT=1528)
            )
         )
         CONNECT_TIMEOUT_LISTENER=10
         TRACE_LEVEL_LISTENER=OFF
         SID_LIST_LISTENER=(SID_DESC=(SID_NAME=SID3)(ORACLE_HOME=C:\SERVER2\ORANT73))

ORACLE_NW=(DESCRIPTION=
             (ADDRESS_LIST=
                (ADDRESS=
                   (PROTOCOL=TCP)
                   (PORT=1521)
                )
             )
             (CONNECT_DATA=SID1)
          )

ORACLE_NT_TWO=(DESCRIPTION= (ADDRESS_LIST= (ADDRESS = (PROTOCOL=TCP) (PORT=1528) ) ) (CONNECT_DATA=SID3) )

We have been referring to SQL*Net as version 2.x and Oracle Workgroup Server as version 7.x. It is time to get more specific and list the minimum version you need for using a particular product or protocol.

SQL*Net 2.2
You need at least version 2.2.2.2.0 if SPX is your protocol of choice.

SQL*Net 2.3
You need at least version 2.3.2.2.0 if using Windows NT 4.0 Server. Version 2.3.2.1.x will also work, but Oracle does not provide support for it.

OWS 7.3
Please note that the Oracle Workgroup Server matches the SQL*Net version with which it is bundled, starting at the second digit from the left (i.e., version 7.3.2.2.0 comes with SQL*Net version 2.3.2.2.0). The most current versions as of this writing are 7.3.3.0.0 for Windows NT and 7.3.2.2.0 for Netware.
 

Note on Oracle 8:

Oracle dropped the "Workgroup Server" in version 8 from the product name. The corresponding minimum SQL*NET version is 8.0.4

A list of the most common Oracle error messages you can receive while setting your SQL*Net connection and the possible causes follow.

Server Problems

ORA-01034: Oracle not available
The database instance has not been started. With versions 7.1 and 7.2 you have to manually start it using SQL*DBA. Version 7.3 automatically starts the database instance.

ORA-12203: TNS: Unable to connect to destination
The listener has not been started or other server modules (NLM files for Netware, services for NT) have not been loaded or started.

Client Problems

Vendor initialization failed - File : ORA71WIN.DLL (16-bit drivers)
or
Vendor initialization failed - File : ORANT71.DLL(32-bit drivers)

This message can be misleading sometimes, because you may have defined a different "Vendor Init" value through you BDE configuration utility (i.e. ORA72WIN.DLL, ORA73WIN.DLL,ORA72.DLL or ORA73.DLL) and still get this erroneous reference. The fix for this problem is easy: make sure your path includes the ORACLE_HOME\BIN directory.

ORA-03121: No interface driver connected - Function not performed
Your main dynamic link library has been loaded (ORA7xWIN.DLL, ORANT7x.DLL, or ORA7x.DLL) but some other necessary module has not been found. At some point in the chain of loaded modules (it varies according to the Oracle version you are using) the error will turn into ORA-12203, meaning that a TNS-specific module has not been found.

ORA-12154: TNS: Could not resolve service name
The "SERVER NAME" value in your BDE Oracle alias (see section 4.3.2) is not identical to the alias name defined in TNSNAMES.ORA.

Whenever possible let SQL*Net Easy Configuration manage your TNSNAMES.ORA, SQLNET.ORA, and LISTENER.ORA files. If you plan to have a more involved configuration use the Oracle Network Manager instead. Be aware that any manual modification will not be recognized by any of these utilities, and your files will be returned to their default values.

If you still want to make manual changes verify that all parenthesis match and you have no comment signs (#) followed by either spaces or tabs.

ORA-12203: TNS: Unable to connect to destination
Possible causes:
1) Missing or wrong ORACLE_HOME value in your ORACLE.INI file.
2) Wrong TCP/IP port specified either in your TNSNAMES.ORA file or your ORACLE.INI file (if connecting through TCP/IP).

ORA-12224: TNS: No listener
Wrong TCP/IP port specified either in your TNSNAMES.ORA file or your ORACLE.INI file (if connecting through TCP/IP).

ORA-12504: TNS: Listener was not given the SID in CONNECT_DATA
Check your TNSNAMES.ORA file.

ORA-12505: TNS: Listener could not resolve SID given in connect descriptor.
Check your TNSNAMES.ORA file.

ORA-12545: TNS: Name lookup failure
This is a TCP/IP error. Check for wrong settings either in your TNSNAMES.ORA file or your ORACLE.INI file. If you are using 16-bit drivers, it could also mean that your VSL.INI file is missing or has the wrong settings.

General Tips

• Make sure you have a local ORACLE_HOME directory defined on each workstation.
• When using Personal Oracle the "SERVER NAME" value in your BDE Oracle alias should be set to "2:" for Personal Oracle 7.1 and "2" (without the colon) for Personal Oracle for Windows 95.
• Check your SQL*Net Client and Server versions. They should either match (i.e., SQL*Net 2.3 on both) or the client should be running a higher version.
• It is very helpful to generate trace information while tuning your TNS connection. Set the TRACE_LEVEL_LISTENER entry of your LISTENER.ORA equal to "ADMIN", and the TRACE_LEVEL_CLIENT entry of your SQLNET.ORA equal to "16". The trace files will show you the connection process step by step, including port, protocol, and packet information. They will even give you the complete error stack if your connection was unsuccessful, with all corresponding error numbers and full message text!
• Once your connection is up and running, set the previous values back to "OFF".