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
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