SQL*Plus connection to remote Oracle database

SQL*Plus connection to remote Oracle database

SQL*Plus is a very handy command line tool when you want to quickly access an Oracle database or execute SQL statements from scripts, e.g. BASH scripts. However, getting it to connect to a remote Oracle database server in a script proved to be quite a challenge. This blog documents how to write the connection string in various cases.


Local database

Connecting to a local database is trivial, just use:

$ sqlplus dbUser/dbPassword@dbSid


Remote database from command line

Here's the nasty syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))'

You can also connect using a SERVICE_NAME instead of SID.
Note that the single-quotes are needed to preserve the brackets.


Remote database from a BASH script

I needed to write a script that called SQL*Plus several times. In order to make it maintainable, the username, password and the part after the @ were specified as environment variables. Unfortunately, there did not seem to be any obvious way of setting the value of the environment variable so that the single-quotes were properly sent to the sqlplus command at run time. I had to explicitly escape each bracket with a backslash, like so:

DB_CONNECTION=\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=remoteServer\)\(PORT=1521\)\)\)\(CONNECT_DATA=\(SID=dbSid\)\)\)

Because this script was part of a Java project, I subsequently moved this value to a Java properties file. This means every backslash had to be escaped with another backslash to prevent Java from interpreting it. The entry in the Java properties file:

db.connection = \\(DESCRIPTION=\\(ADDRESS_LIST=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(HOST=remoteServer\\)\\(PORT=1521\\)\\)\\)\\(CONNECT_DATA=\\(SID=dbSid\\)\\)\\)

(I know I could have just used JDBC, but there was a good reason why I had to use SQL*Plus for this Java project.)



Define a local service name to a remote server

An alternative to using the big ugly connection string is to define a local service name in the local tnsnames.ora that references the remote instance. Add the following:

DB_LOCAL_NAME = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= remoteServer)(Port= 1521)) (CONNECT_DATA = (SID = dbSid)) )
to

${ORACLE_HOME}/server/network/admin/tnsnames.ora

Connecting to the remote database is simply:

$ sqlplus dbUser/dbPassword@DB_LOCAL_NAME

Of course, this solution has a few drawbacks (enough to dissuade me from using it).
- Need write permission to the tnsnames.ora.

- The application relies of custom entries in tnsnames.ora, which must be maintained together with the application code.

- Every environment (e.g. development, staging, UAT, production) in which the application is deployed to will need to have its tnsnames.ora modified and maintained.

Error While Loading Shared Libraries: libdb.so.2 on RedHat Linux 5

Error While Loading Shared Libraries: libdb.so.2 on RedHat Linux 5

After clonning (or installing) an Oracle Applications Release 12.0 or 12.1 instance on Linux 5, an error may occur while starting up the Apache service. The error would be this:

You are running adapcctl.sh version 120.6.12000000.4
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
adapcctl.sh: exiting with status 204


After review the HTTP log file. The error is very clear about the missing soft link. It would show this error:

/u01/apps/AJI_apps12/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
/u01/apps/tech_st/10.1.3/Apache/Apache/bin/httpd: error while loading shared
libraries: libdb.so.2: cannot open shared object file: No such file or directory

Soft link libdb.so.2 is missing.


To solve this issue, Shut down all Oracle Application Services

As root user, create a soft link as follows:
# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

Start up all Oracle Application Services