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.
Notes
- The Instant Client directory name must match the downloaded version; adjust the paths accordingly.
- Do not leave passwords in scripts; the
<PASSWORD>value above is a placeholder. - Setting
PASSWORD_LIFE_TIMEandFAILED_LOGIN_ATTEMPTSto UNLIMITED weakens security; use it only on test systems.