Installing SQL*plus instant client on Linux

We back to the wonderful world of Oracle for a few hours. I have written a few articles on how to install APEX and Oracle XE on the same server, well now its time to run the APEX web server and Oracle server separate. I am again going to leverage the  power of Amazon AWS infrastructure as per usual, more about setting up the Oracle server over at my AWS tips n tricks blog.

I need to create a web server to access the APEX oracle back end server for this you need to get SQL*PLUS working to help you get things moving.

Well you can read the helpful documents from Oracle if you want, to be fare they get you most of the way there and in fact would get you working if the install did not do something a little strange, more about this at the end.

SQPLUS first this can be down loaded from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

You will need :

oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

 oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

These were the latest at the time of writing, basically you need the basic and sqlplus rpm files for your environment.

Download them and transfer them  to the server, I have yet to workout how to download these with wget.

It is assumed that you have the files in the /opt/software directory

cd /opt/software

rpm -i oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

rpm -i oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

You then need to create a script or add the following lines to the .bashrc file.

I created a small script file to set all of the path statements.

mkdir /opt/oracle

cd /opt/oracle

vi set_env

Add these lines

LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:${LD_LIBRARY_PATH}

export LD_LIBRARY_PATH

SQLPATH=/usr/lib/oracle/11.2/client64/lib:${SQLPATH}

export SQLPATH

ORACLE_HOME=/usr/lib/oracle/11.2/client64

export ORACLE_HOME

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

LIB_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

 

To explain what these are variables are

LD_LIBRARY_PATH :

this is where the .so files are, that allows the connection to the database, to find your path

cd /

find . -name glogin.sql

SQLPATH :

this is the same location as the LD_LIBRARY_PATH set, not sure what it is used for, I just know it is needed

ORACLE_HOME :

This is where the directory prior to the oracle binaries and the libraries needed, this is standard practice in the Oracle world ???? beats me some of this does.

LIB_PATH:

again another Oracle thing, not going to argue as long as it works
PATH:

this is the important one, this indicates where the sqlplus binary can be found note the /bin after ORACLE_HOME

Every time that you want to use sqlplus you will need to run this script

TNS_ADMIN:

This will be explained below 🙂

To make it easier to use the sqlplus command line it is best to create a tnsnames.ora file, this can be stored anywhere, but based on the standards when installing Oracle locally it is best to store it in /u01/app/oracle/product/{VERSION}/{DATABASENAME}/network/admin/

e.g.

/u01/app/oracle/product/11.2.0/general/network/admin/

So

mkdir -p /u01/app/oracle/product/11.2.0/general/network/admin/

vi tnsnames.ora

Insert the following

DATABASE NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ENDPOINT FROM THE RDS CONSOLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DATABASE NAME WHEN CREATED)
)
)

e.g.

This is based on the database name of general and the Oracle instance being called companyname-general-orc

GENERAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GENERAL)
)
)

SQLPLUS now need to know where the tnsnames file is

vi /opt/oracle/set_env

Add these lines

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

Now to try to connect to the database, make sure that you run the set_env script

cd /opt/oracle

. set_env

Then run the sqplus command, in this example the database is called general

sqlplus username/password@general

Easy I here you say, well if you get this dreaded error

sqlplus username@general
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 17 15:25:04 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
 
 
Enter user-name: 
Its easy to fix he says, well the issue that I had was the symbolic link in /usr/bin, I deleted this and set the path to sqlplus in the set_env file to take care of it, so make sure you delete the offending entry
rm /usr/bin/sqlplus
rm /usr/bin/sqlplus64
Another error encountered when I was also trying to get this to work on another Linux version was: (It takes several attempts to get stuff working, this is why these documents are written 🙂 )

/usr/bin/sqlplus: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To fix this

yum install glibc.i686

One trick to make sure you can connect to the database is this,

/usr/lib/oracle/11.2/client64/bin/sqlplus username@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = database server name or ip address)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=database name)))’

So in our example

/usr/lib/oracle/11.2/client64/bin/sqlplus username@’GENERAL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = GENERAL)))’

I can not take credit for this it was a helpful DBA at Inoapps

On more just test connectivity, especially if have routers, firewalls, vlans or any other anti connection equipment it a simple telnet to the database

telnet Oracle server IP address 1521

If you are successful in the connection then you will get the following prompt

Escape character is ‘^]’.

If you don’t have telnet install on the server

yum install telnet


Oracle Bits and Pieces

There a loads of little tips for adminastring Oracle, I am just starting out with a full system 10gR2 on Red Hat ES 4.

A very steep learning curve, got the SQL and PLSQL sorted its just the Oracle bit now.  We have 3rd party DBA’s so it helps, some

of these tips are from me and some are from the professionals.

I will highlight any in RED if they have the potential to harm your system, please be careful though with any commands unless you are in your own test environment.

To see what databases are running

ps -ef | grep mon

To see what listners are active

ps -ef | grep lsnr


Installing Oracle on Linux CentOS

Link to really good guide

Kamranagayev


Oracle system tables

Below are some of the system table in Oracle (10gR2)

As I use the tables I will link useful and relevant SQL (Original)

ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences

accessible to the user

ALL_COL_COMMENTS Comments on columns of accessible tables

and views

ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in

constraint definitions

ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user

is allowed to create

ALL_INDEXES Descriptions of indexes on tables accessible

to the user

ALL_IND_COLUMNS COLUMNs comprising INDEXes on

accessible TABLES

ALL_LOBS Description of LOBs contained in tables

accessible to the user

ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible

to the user

ALL_SEQUENCES Description of SEQUENCEs accessible to

the user

ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that

user is allowed to create

ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible

to the user

ALL_TAB_COLUMNS Columns of user’s tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user’s tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible

to the user

ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user’s triggers or in triggers

on user’s tables

ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary

tables and views

GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for

component products

ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes.

Maps privilege type numbers to type names

TABLE_PRIVILEGES Grants on objects for which the user is the

grantor, grantee, owner, or an enabled role

or PUBLIC is the grantee

TABLE_PRIVILEGE_MAP Description table for privilege (auditing option)

type codes. Maps privilege (auditing option)

type numbers to type names