Thursday, April 15, 2010

Learn to install SQL*Plus(Instant Client) or Learn More...

Installation PHP + OCI8, we have to use Instant Client Package(Basic) and Instant Client Package(SDK). but If we need to use SQL*Plus to test something by connection to Oracle Database. We have to use Instant Client Package(Basic) and Instant Client Package(SQL*Plus).
$ unzip oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.zip
Archive: oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.zip
inflating: instantclient_11_2/BASIC_README
inflating: instantclient_11_2/adrci
inflating: instantclient_11_2/genezi
inflating: instantclient_11_2/libclntsh.so.11.1
inflating: instantclient_11_2/libnnz11.so
inflating: instantclient_11_2/libocci.so.11.1
inflating: instantclient_11_2/libociei.so
inflating: instantclient_11_2/libocijdbc11.so
inflating: instantclient_11_2/ojdbc5.jar
inflating: instantclient_11_2/ojdbc6.jar
inflating: instantclient_11_2/xstreams.jar

$ unzip oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.zip
Archive: oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.zip
inflating: instantclient_11_2/SQLPLUS_README
inflating: instantclient_11_2/glogin.sql
inflating: instantclient_11_2/libsqlplus.so
inflating: instantclient_11_2/libsqlplusic.so
inflating: instantclient_11_2/sqlplus

$ cd instantclient_11_2
$ ./sqlplus
./sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
We learned to install SQL*Plus(Instant Client) and we were learning to make it work, then checked "libsqlplus.so" shared library file.
$ ls -l libsqlplus.so
-r-xr-xr-x 1 oracle oinstall 1470768 Aug 15 2009 libsqlplus.so
then used "ldd" to help.
ldd prints the shared libraries required by each program or shared library specified on the command line.
$ ldd sqlplus
libsqlplus.so => not found
libclntsh.so.11.1 => not found
libnnz11.so => not found
libdl.so.2 => /lib64/libdl.so.2 (0x0000003fc1f00000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000003fc1d00000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x0000003fc2100000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003fc9400000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000003fc1a00000)
/lib64/ld-linux-x86-64.so.2 (0x0000003fc1600000)
"sqlplus" command could not load shared libraries. checked all shared libraries = "not found"
$ ls -l libsqlplus.so libclntsh.so.11.1 libnnz11.so
-rwxrwxr-x 1 oracle oinstall 48797739 Aug 15 2009 libclntsh.so.11.1
-r-xr-xr-x 1 oracle oinstall 7899997 Aug 15 2009 libnnz11.so
-r-xr-xr-x 1 oracle oinstall 1470768 Aug 15 2009 libsqlplus.so

$ pwd
/opt/instantclient_11_2
then used LD_LIBRARY_PATH environment variable.
The environment variable LD_LIBRARY_PATH is a colon-separated set of directories where libraries should be searched for first, before the standard set of directories.
$ export LD_LIBRARY_PATH=/opt/instantclient_11_2:$LD_LIBRARY_PATH

$ ldd sqlplus
libsqlplus.so => /opt/instantclient_11_2/libsqlplus.so (0x0000002a95557000)
libclntsh.so.11.1 => /opt/instantclient_11_2/libclntsh.so.11.1 (0x0000002a9573f000)
libnnz11.so => /opt/instantclient_11_2/libnnz11.so (0x0000002a97c6f000)
libdl.so.2 => /lib64/libdl.so.2 (0x0000003fc1f00000)
libm.so.6 => /lib64/tls/libm.so.6 (0x0000003fc1d00000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0x0000003fc2100000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x0000003fc9400000)
libc.so.6 => /lib64/tls/libc.so.6 (0x0000003fc1a00000)
libaio.so.1 => /usr/lib64/libaio.so.1 (0x0000003fc1800000)
/lib64/ld-linux-x86-64.so.2 (0x0000003fc1600000)
"sqlplus" command could load all shares libraries.
$ ./sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 15 20:58:18 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name:
We can use SQL*Plus(Instant Client), we learned to make it work (learned to fix and use shell command). so We learned more...

However, If we need to make it work faster... and don't necessary to learn more... (just need it work). After unzip file:
$ pwd
/opt/instantclient_11_2
$ export TNS_ADMIN=/opt/instantclient_11_2
$ export LD_LIBRARY_PATH=/opt/instantclient_11_2:$LD_LIBRARY_PATH
$ export PATH=$PATH:/opt/instantclient_11_2
$ sqlplus user/pwd@DB

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 15 21:50:53 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
TNS_ADMIN environment is the directory containing the tnsnames.ora file.

Reference(To Learn More):
Oracle Database Instant Client
SQL*Plus Instant Client
Linux Shared Libraries
Filesystem Hierarchy Standard

/lib : Essential shared libraries and kernel modules
Purpose:
The /lib directory contains those shared library images needed to boot the system and run the commands in the root filesystem, ie. by binaries in /bin and /sbin.

/usr/lib : Libraries for programming and packages
Purpose:
/usr/lib includes object files, libraries, and internal binaries that are not intended to be executed directly by users or shell scripts.
Applications may use a single subdirectory under /usr/lib. If an application uses a subdirectory, all architecture-dependent data exclusively used by the application must be placed within that subdirectory.

5 comments:

Laurent Schneider said...

I tried on Windows but did get
The application failed to initialize properly (0xc0000022). Click on OK to terminate the problem

export PATH=$ORACLE_HOME:/opt/instantclient_11_2

Are you sure?

Surachart said...

Sorry -)
My wrong, It should be:
export PATH=$PATH:/opt/instantclient_11_2

Surachart said...

To Laurent, Thank You for your comment.

I tested Instant Client Package(Basic) and Instant Client Package(SQL*Plus) on Windows7 (64bit)... Nice

C:\> set PATH=%PATH%;C:\instantclient_11_2_x64

C:\>set TNS_ADMIN=C:\instantclient_11_2_x64

C:\>sqlplus user@DB

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 16 18:20:34 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

About The application failed to initialize properly (0xc0000022). Click on OK to terminate the problem
Perhaps It's the issue about Insufficient user OS privileges to access the following files in %SYSTEMROOT%\system32 or ...

-)

Sänjay said...

I am using this in windows env for the instant client.

REM Start of script sql.bat
set installdir=C:\Oracle\local

rem by sanjay sharma
set ORACLE_HOME=%installdir%

set TNS_ADMIN=%installdir%
set LD_LIBRARY_PATH=%installdir%
set SQLPATH=%installdir%
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

sqlplus
REM end of script

This works for me .

Surachart said...

@Sänjay
Thank You for sharing -)