Search This Blog

Tuesday, August 17, 2010

This Tutorial is divided into 2 part:

1)Install Database on Linux Machine.
2)Write a Logic (Script to Insert data into Database).


1)Install Database on Linux Machine.

The below tutorial assumes you have oracle database(oracle xe,oracle 9i,10g) installed on your system.
Operating system(Linux):
This should work on Most of linux distributions but for testing purpose i have used OEL(Oracle Enterprise Linux).
# lsb_release -a
LSB Version: :core-3.1-ia32:core-3.1-noarch:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: EnterpriseEnterpriseServer
Description: Enterprise Linux Enterprise Linux Server release 5.2 (Carthage)
Release: 5.2
Codename: Carthage
# uname -a
Linux 2.6.18-92.el5 #1 SMP Fri May 23 22:17:30 EDT 2008 i686 i686 i386 GNU/Linux.

This is tested using ORACLE XE as a database but for other oracle databases it should work.
Installation steps:

Installation of Oracle XE and configuration.
Make sure your distribution supports rpm installation.
Choose suitable oracle-xe installable for your distribution.
I have selected following rpm.
oracle-xe-univ-10.2.0.1-1.0.i386.rpm

If you downloaded the oracle-xe-univ-10.2.0.1-1.0.i386.rpm executable, you would enter this command:
$ rpm -ivh downloads/oracle-xe-univ-10.2.0.1-1.0.i386.rpm

The installation displays a status of its progress.
When prompted, run the following command:
$ /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8888

Specify a port that will be used for the database listener [1521]:1521

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration:
Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to http://127.0.0.1:8888/apex

For Installation guide please refer.

http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm#CIHHJEHF

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=XE(Default instance after installation you can change it as per your installation )


2)Write a Logic (Script to Insert data into Database).

I have used following references to accomplish the task.

http://www.thegeekstuff.com/2010/07/unix-shell-script-to-execute-oracle-sql-query/
http://www.orafaq.com/wiki/SQL*Loader_FAQ


Make sure you set a proper path for ORACLE_HOME and ORACLE_SID

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=XE

use following script to execute command from bash script.
To get the basic understanding of how to connect to database and execute the command is given in below script.

#!/bin/bash
# Validate the value of ORACLE_HOME #
# If ORACLE_HOME is empty #

if [ -z $ORACLE_HOME ]
then
echo "Set the ORACLE_HOME variable"
exit 1
fi

# If ORACLE_HOME doesn't exist #
if [ ! -d $ORACLE_HOME ]
then
echo "The ORACLE_HOME $ORACLE_HOME does not exist"
exit 1
fi

# Validate the value of ORACLE_SID #
if [ -z $ORACLE_SID ]
then
echo "Set the ORACLE_SID variable"
exit 1
fi

SID=`ps -ef|grep smon|grep -v grep|awk '{print $8}'`

if [ 'xe_smon_'$ORACLE_SID != $SID ] ; then
echo "ORACLE_SID IS NOT SET"
exit 1
fi


# Enter the username and password to login to oracle #
echo "Enter the username"
read username

echo "Enter password"
stty -echo
read password
stty echo

# Get the query , no validation applied for query #
echo "Enter the query"
read query
# Login and execute the query.
echo "set feedback off verify off heading off pagesize 0
$query
exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
do
echo $output
done

output:
# databaseconnect.sh
Enter the username
lightweight
Enter password
Enter the query
select table_name from tabs;
LIGHT_INVENTORY
LIGHT_CRED
HTMLDB_PLAN_TABLE
LIGHT_ROLE

Additional Information:(Source :http://rcsg-gsir.imsb-dsgi.nrc-cnrc.gc.ca/documents/bourne/node46.html)
Little Drawback of below script:
You will need to have a different for different oracle products.
This is because they maintain different naming convention for the process.
in case of xe they reffer it as xe_smon_
where as other distribution reffer it as ora_smon_

For Oracle XE installation.(Tested and working fine)

SID=`ps -ef|grep smon|grep -v grep|awk '{print $8}'`

if [ 'xe_smon_'$ORACLE_SID != $SID ] ; then
echo "ORACLE_SID IS NOT SET"
exit 1
fi

For Other Installation(Not Tested but should work.)

SID=`ps -ef|grep smon|grep -v grep|awk '{print $8}'`

if [ 'ora_smon_'$ORACLE_SID != $SID ] ; then
echo "ORACLE_SID IS NOT SET"
exit 1
fi

stty -echo: Passwords
If the script requires the input of a ``secret" string, the string typed from the keyboard should not be displayed on the screen. To stop the display of the characters typed,

# stty -echo

may be used.

To resume echoing of the characters,

#stty echo

is used.

#!/bin/sh
echo -e "Please enter your passcode: \c"
stty -echo
read PASSCODE
stty echo
echo "The passcode was not seen when typed."


However to supply output of bash command into database i have written seperate script.
Description:
This command take a output of vmstat command and insert cpu related information into the database.

No comments:

Post a Comment