Connecting to Oracle
Many DX users have trouble connecting to their Oracle databases.
This is because Oracle gives the user a wide variety of connection techniques,
and DX cannot determine on its own which method works at a particular site.
Here are some tips for making the DX to Oracle connection work.
-
The environment variable ORACLE_HOME must be set before starting
DX or any DBPak application. If you do not know the value for
this variable, ask your Oracle DBA.
-
You may have to set the environment variable ORACLE_SID if you are
connecting to your default database rather than a named database.
-
You will have to know the Oracle server name to tell DX
-
You will have to know a valid user name to tell DX.
-
You may need a valid password.
Determining the Server Name
Oracle provides a wide variety of ways to connect applications to databases.
The DX server name should be set to the connection string you would use
to connect to your database. The quickest way to determine what to
tell DX is to examine how you run the Oracle SQLPLUS utility. If
you begin SQLPLUS by typing
% SQLPLUS username@DBNAME
then you should use the server name DBNAME. The DBNAME
can be any valid Oracle connection string. For most users, this is
simply an Oracle Names name for the database. For some it is a SQLNET
V.1 connection string (T:hostname:sid).
If you don't know the Oracle Names name for the database there are several
places you can look for a definition file which will list all the valid
names. The location varies depending on the OS and how the site is
set up, but it is most likely to be in
-
$HOME/.tnsnames.ora
-
/etc/tnsnames.ora
-
$ORACLE_HOME/tnsnames.ora
If you start sqlplus without the @DBNAME construct, like this
% SQLPLUS username
then you should use the server name 'default' and you must set the ORACLE_SID
environment variable to the SID of your database. If you don't
know the SID you can guess it by looking at the names of the Oracle processes.
% ps -eal | grep ora (for most System V descendants)
% ps -axw | grep ora (for SunOS 4.x, and most BSD descendants)
You should usually find at least 4 processes, which look like
oracle 400 1 0 Nov 03 ? 0:56 ora_dbwr_ONY2
oracle 398 1 0 Nov 03 ? 0:32 ora_pmon_ONY2
oracle 402 1 0 Nov 03 ? 0:37 ora_lgwr_ONY2
oracle 404 1 0 Nov 03 ? 0:08 ora_smon_ONY2
The last part of the name (ONY2 in this case) is the SID. Note: The
default connection method only works if the application and the database
are running on the same machine.
Determining your User Name
Many Oracle sites use Oracle login names when connecting to the database.
DX users are encouraged to install the sample database, which creates a
user dbpak with the password dbpakdemo. If you have done this, then
your DBA can tell you a valid login name and password. Often,
the default system password is left unchanged (SYSTEM/MANAGER).
Some sites use the operating system login information as your Oracle
login name. This is refered to as the OPS$ method. If
you want to connect this way, set your DX/DBPak user name to 'OPS$'.
In this case you do not have to give a password because the assumption
is that your operating system login provided all the credential checks
for you.
SQL-NET V1
Older Oracle installations may still only be using SQL-NET V.1. In
this case, XmNserverName must be set to the full connection string,
either P:sid or T:host:sid. P:sid indicates
a local machine connection directly to the Oracle
server identified by sid over a pipe. T:host:sid
indicates a TCP connection to a (possibly) remote server. If you
are having problems making a connection in this way, there are two things
to check.
-
Do you have this line in /etc/services?
orasrv 1525/tcp # oracle network
This indicates that Oracle should communicate over port 1525 to the remote
machine.
Is the TCP listener running on the remote host?
Use ps to check that orasrv is running on the remote host. If
not, you can start it with the Oracle utility tcpctl. Consult your Oracle
documentation for more information about this program.
SQL-NET V2
Most sites are now using SQL-NET V2. This allows you to connect to
a database by just providing a name. The host and port number to
use to connect are found in the tnsnames.ora file mentioned above.
On the remote host, the listener.ora file sets up the service to receive
connection requests. Here are some examples:
Sample listener.ora file for Oracle host.
Sample tnsnames.ora
How to spot which kind of SQL-NET you use
Before running DX, you should test your connections with sqlplus. In these
examples, the Oracle database SID is CUSTOMER, it is running on the host
dbhost, the login is worker, and the password is joesentme.
sqlplus worker/joesentme@ARGO (sqlnet v2)
sqlplus worker/joesentme@T:dbhost:CUSTOMER (sqlnet v1)
sqlplus worker/joesentme@P:CUSTOMER (two-task linking)
sqlplus worker/joesentme (works only if done on dbhost and ORACLE_SID
is set to CUSTOMER)
Other Points
-
XmNserverName handling in DBPak applications
If you leave XmNserverName unset, DBPak will try find its value
by examining environment variables. First it looks at TWO_TASK,
and then at ORACLE_SID.
