Install Oracle Instant Client in Ubuntu / Linux Mint

1. Download and install RPMs
To download related RPM select your linux version here then download following RPMs

• oracle-instantclient*-basic-*.rpm
• oracle-instantclient*-sqlplus-*.rpm
• oracle-instantclient*-devel-*.rpm

2. Install alien RPM converter if you don't have

sudo apt-get install alien

3. Install RPM by converting via alen converter 

sudo alien -i oracle-instantclient*-basic-*.rpm
sudo alien -i oracle-instantclient*-devel-*.rpm  
sudo alien -i oracle-instantclient*-sqlplus-*.rpm

below is the terminal output 

praitheeesh@Prai-Linux ~/Downloads $ sudo alien -i oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm 
dpkg --no-force-overwrite -i oracle-instantclient12.1-basic_12.1.0.1.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-basic.
(Reading database ... 157491 files and directories currently installed.)
Unpacking oracle-instantclient12.1-basic (from oracle-instantclient12.1-basic_12.1.0.1.0-2_amd64.deb) ...
Setting up oracle-instantclient12.1-basic (12.1.0.1.0-2) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place


praitheeesh@Prai-Linux ~/Downloads $ sudo alien -i oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64.rpm 
dpkg --no-force-overwrite -i oracle-instantclient12.1-devel_12.1.0.1.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-devel
(Reading database ... 157512 files and directories currently installed.)
Unpacking oracle-instantclient12.1-devel (from oracle-instantclient12.1-devel_12.1.0.1.0-2_amd64.deb) ...
Setting up oracle-instantclient12.1-devel (12.1.0.1.0-2) ...


praitheeesh@Prai-Linux ~/Downloads $ sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm 
dpkg --no-force-overwrite -i oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb
Selecting previously unselected package oracle-instantclient12.1-sqlplus.
(Reading database ... 157563 files and directories currently installed.)
Unpacking oracle-instantclient12.1-sqlplus (from oracle-instantclient12.1-sqlplus_12.1.0.1.0-2_amd64.deb) ...
Setting up oracle-instantclient12.1-sqlplus (12.1.0.1.0-2) ...

4. Setup environment veritable

To find the installation path of sqlplus type

sudo find / -name sqlplus

praitheeesh@Prai-Linux ~ $ sudo find / -name sqlplus
/usr/lib/oracle/12.1/client64/bin/sqlplus
   

Integrate Oracle Libraries

If oracle applications, such as sqlplus, are complaining about missing libraries, you can add the Oracle libraries to the LD_LIBRARY_PATH each time it is used, or to add it to the system library list create a new file as follows:

sudo vi /etc/ld.so.conf.d/oracle.conf

Then run ldconfig:

sudo ldconfig
   
   

why idconfig ?

ldconfig: Ldconfig is a basic system program which determines run-time linkbindings between ld.so and shared libraries. Ldconfig scans a running system and sets up the symbolic links that are used to load shared libraries properly. It also creates a cache (/etc/ld.so.cache) which speeds the loading of programs which use shared libraries.

ORACLE_HOME

set oracle path and HOME inside /etc/environment (system level , you may set in Session-wide environment level also. You need to logout and login if you set in system level more details here)

praitheeesh@Prai-Linux ~/Downloads $ cat /etc/environment 
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/usr/lib/oracle/12.1/client64/bin/
ORACLE_HOME="/usr/lib/oracle/12.1/client64"

Type below command and make sure all the environment variables are set correctly

praitheeesh@Prai-Linux ~/Downloads $ cat /etc/ld.so.conf.d/*.conf
/usr/lib/i386-linux-gnu/mesa
# Multiarch support
/lib/i386-linux-gnu
/usr/lib/i386-linux-gnu
/lib/i686-linux-gnu
/usr/lib/i686-linux-gnu
# libc default configuration
/usr/local/lib

/usr/lib/oracle/12.1/client64/lib
# Multiarch support
/lib/x86_64-linux-gnu
/usr/lib/x86_64-linux-gnu
/usr/lib/x86_64-linux-gnu/mesa-egl
/usr/lib/x86_64-linux-gnu/mesa

praitheeesh@Prai-Linux ~/Downloads $ echo $ORACLE_HOME
/usr/lib/oracle/12.1/client64
praitheeesh@Prai-Linux ~/Downloads $

SDK fix

Some packages may look for 'oci.h' in $ORACLE_HOME/include, or in $ORACLE_HOME/rdbms/public

The instant client sometimes places the include files, such as oci.h, in /usr/include/oracle /client.

Inspect your system by running the following commands

praitheeesh@Prai-Linux /etc/profile.d $ ls $ORACLE_HOME
bin  lib

If there is no 'include' directory under ORACLE_HOME, and it is located over in /usr/include/oracle/ , create a symbolic link to assist packages looking for these header files.

sudo ln -s /usr/include/oracle/12.1/client64 $ORACLE_HOME/include

praitheeesh@Prai-Linux /usr/include/oracle/12.1/client64 $ ls $ORACLE_HOME
bin  include  lib

libaio1 missing

If sqlplus complains of a missing libaio.so.1 file , you will get following error

praitheeesh@Prai-Linux ~ $ sqlplus
sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

run following command to install libaio1

sudo apt-get install libaio1

5. Done, type sqlplus and connect to database

Oracle developer day VM : How to use Pre-Built Oracle database for learning and testing

Oracle developer day pre-installed virtual machine is very useful to finish your homework, teach yourself about Oracle database, or get ready for that job interview in SQL / PL SQL.
If you want the Oracle database for testing purpose or if you need mobility database just you can export your application database and import into this pre-installed oracle VM, It is very quick and easy to setup. Here i'm explain how i have import our application database into this VM from scratch.

Oracle provide the pre-Build oracle installed VM for testing and learning purpose. This virtual machine contains:

• Oracle Linux 5
• Oracle Database 11g Release 2 Enterprise Edition
• Oracle TimesTen In-Memory Database Cache
• Oracle XML DB
• Oracle SQL Developer
• Oracle SQL Developer Data Modeler
• Oracle Application Express
• Oracle JDeveloper
• Hands-On-Labs (accessed via the Toolbar Menu in Firefox)

1. Download and install Oracle Virtual Box here
2. Download Oracle Developer Day pre-installed VM from here
3. Import downloaded ova file into Virtual Box

Click the "Import Appliance.." to import the downloaded ova file

Select the downloaded ova file

Click the check box as Highlighted

Importing Appliance..

Imported.

I have selected the network setting as "Host only Adapter" because i wanted to use the database only within the hosted PC. So i can connect the database from PC which i installed the virtualbox. If you need to access the database from other network you might need to select "Bridge Connection"

Once you finished the Vertualbox installation you can notice new network adapter to connect virtual machines. This will act as gatway between your PC and all virtual mechines.

Check the virtual adapter's setting  for more understanding about network connections

Start the virtual machine and login as oracle , password also "oracle"

configuration parameters.

4. Oracle TNS tnsnames.ora file has configurations for pre-installed database , below is the terminal output

[oracle@localhost ~]$ cat /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
TTORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
[oracle@localhost ~]$

5. Login into oracle database as sysdba then create new user with DBA privileges and normal user to import the database dump.The default password for all accounts is 'oracle'.So you can connect as SYS, HR with 'oracle' as the password. The Linux password are all ‘oracle’ too, so you can login as ‘root’ or as ‘oracle’ in the Linux desktop. Below are the terminal outputs to create the schema / user

[oracle@localhost ~]$ sqlplus 'sys as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 24 22:44:58 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE USER oracle_dba IDENTIFIED BY password$1;

User created.

SQL> GRANT CONNECT TO oracle_dba;

Grant succeeded.

SQL> GRANT DBA TO oracle_dba;

Grant succeeded.

SQL>; CREATE USER SNSDIA IDENTIFIED BY password$1;

User created.

SQL> GRANT CONNECT TO SNSDIA;

Grant succeeded.

SQL>ALTER USER SNSDIA QUOTA 100M ON USERS;

User altered.

6. Import the data dump into newly created schema / user

[oracle@localhost ~]$ imp oracle_dba@TTORCL file=/home/oracle/Desktop/exp_xxxxx.dmp LOG=xxxxx_import.log FROMUSER=abc TOUSER=acb ignore=Y;

7. Connect to newly created schema from host PC using sql developer

Autosys/Cron job to download ftp files with expect

Expect is a program that "talks" to other interactive programs according to a script. Expect reads cmdfile for a list of commands to execute.
following commands are available under expect package.

• spawn - starts a process
• send  - sends to a process
• expect - waits for output from a process
• interact - lets you interact with a process

Example auto download scrit using expect which connects with other ftp server and download the define files.

#!/usr/local/bin/expect

spawn /usr/local/bin/sftp user_name@ftp.server.com

expect "Password:"

send "password\r"

expect "sftp>"

send "cd /documents/downloadfiles\r"

expect "sftp>"

send "mget O_UEN*.XML \r "

expect "sftp>"

send "mget O_UL*.XML \r "

expect "sftp>"

send "mget O_UF*.XML \r "

expect "sftp>"

send "mget O_AUDIT.XML \r "

expect "sftp>"

send "bye\r"

The first line is the command interpreter.Then its Wait for the input and send the output. If we don't know the output of the next line , means if we don't know
the expect line then we can use "autoexpect" package instead of "expect" package.