LLI Wiki — My Digital Knowledge Base

Oracle Instant Client on Linux: installation and login issues

Install Instant Client and SQL*Plus on Red Hat/Oracle Linux, configure sqlnet/tnsnames, fix ORA-01017 and audit failed logins.

How to install Oracle Instant Client (Basic, SQL*Plus and Tools packages) on Red Hat 8 / Oracle Linux, configure the network files, set the environment, and handle a few common login issues. The Windows environment variables are listed at the end.

Prerequisites

SQL*Plus needs the libnsl library:

yum install libnsl.so.1
yum install libnsl.so.1*64*

Download the packages from the Instant Client Downloads for Linux x86-64 page (Basic, SQL*Plus and Tools zip files).

Install

mkdir -p /opt/oracle
unzip -d /opt/oracle instantclient-basic-linux.x64-19.14.0.0.0dbru.zip
unzip -d /opt/oracle instantclient-sqlplus-linux.x64-19.14.0.0.0dbru.zip
unzip -d /opt/oracle instantclient-tools-linux.x64-19.14.0.0.0dbru.zip

The archives extract into an instantclient_19_14 directory (adapt the name to the version downloaded). Check the content with:

cd /opt/oracle/instantclient_19_14 && find . -type f | sort

You should see, among others, sqlplus, adrci, libclntsh.so.*, libsqlplus.so, ojdbc8.jar and network/admin/README.

Network configuration files

Create the network/admin folder and the files ldap.ora (optional, for directory naming), sqlnet.ora and tnsnames.ora. One set per OS user that uses the client.

echo ${USER}
date=$(date '+%Y%m%d%H%M%S')
mkdir -p /opt/oracle/instantclient_19_14/network/admin

ldap.ora:

cat > /opt/oracle/instantclient_19_14/network/admin/ldap.ora <<EOF
# last modif : ${date}
DIRECTORY_SERVERS = (ldap1.example.com:389, ldap2.example.com:389)
DEFAULT_ADMIN_CONTEXT = "dc=example,dc=com"
DIRECTORY_SERVER_TYPE = OID
EOF

sqlnet.ora:

cat > /opt/oracle/instantclient_19_14/network/admin/sqlnet.ora <<EOF
# last modif : ${date}
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP)
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
sqlnet.expire_time=5
EOF

tnsnames.ora:

cat > /opt/oracle/instantclient_19_14/network/admin/tnsnames.ora <<EOF
# last modif : ${date}
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )
EOF

User environment

Create a small environment script in the user session (sudo su - <username>) and source it from ~/.bashrc:

mkdir -p /home/${USER}/scripts

cat > /home/${USER}/scripts/setEnv.sh <<'EOF'
export ORACLE_HOME=/opt/oracle/instantclient_19_14
export LD_LIBRARY_PATH="$ORACLE_HOME":$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
EOF

Test:

source ~/.bashrc
sqlplus -V

ORA-01017 between Oracle 12c and 19c clients (password case sensitivity)

On the server side, edit $ORACLE_HOME/network/admin/sqlnet.ora and add:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Then, as SYSDBA, reset the account and relax the default profile if needed:

alter user app_user password expire;
alter user app_user identified by '<PASSWORD>' account unlock;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

select USERNAME, ACCOUNT_STATUS from dba_users where username='APP_USER';
SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

alter user <your_username> account unlock;

References: ORA-01017 explained and the Oracle sqlnet.ora parameters.

Audit failed logins

To find the origin of failed login attempts (see this article):

show parameter audit_trail
alter system set audit_trail=db,extended scope=spfile;
audit session whenever not successful;

set linesize 400
set pagesize 10000
col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

The audit_trail change in the spfile takes effect after an instance restart. To disable the audit:

NOAUDIT SESSION;

Windows

Environment variables (example with Instant Client 21.6):

Variable Value
ORACLE_HOME C:\oracle\instantclient_21_6
PATH add C:\oracle\instantclient_21_6 (separator ;)
TNS_ADMIN C:\oracle\instantclient_21_6\network\admin

For a 64-bit client, registry key HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE\KEY_<home name>:

Value Data
NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_HOME C:\oracle\instantclient_21_6
ORACLE_HOME_NAME instantclient_21_6

Other Oracle registry locations: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE and HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services. To point a single cmd session to another TNS folder: set tns_admin=c:\temp.

Windows environment variables for Oracle Instant Client

Windows registry: ORACLE key with ORACLE_HOME and TNS_ADMIN

Notes

Screenshots

Oracle Instant Client download page: Basic, SQL*Plus and Tools (ZIP) packages

Mastodon LLIMastodon SocialFosstodon