Showing posts with label Shell Script. Show all posts
Showing posts with label Shell Script. Show all posts

Shell Script to export database tables from oracle

Hi,

After a long time gap I am back again. This time I was assigned a very interesting task to automate backup and restore functionality on a oracle installed on a linux machine(though I am not a geek using linux as well as oracle :P).

I started to dig around the google for this task and found many webpages which have helped(I really appriciate all the people who gave me the knowledge on this..) to acheive the task successfully.

So here I go:

1> Create a folder where you want your backup folder to be placed. I have created a folder called DBIMPEXP at /home/oracle/

$ cd /home/oracle/DBIMPEXP

2> vi DBExport.sh

3> Type all the below into this file.

#!/bin/bash
ORACLE_HOME=/opt/oracle/products/11.1.0/database;export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH;export PATH
ORACLE_SID=CI;export ORACLE_SID
DIRECTORY='dmpdir';export DIRECTORY

echo $ORACLE_HOME
echo $PATH
SUBJECT="Database export operation performed"
EMAIL="email@domain.com"
MSG="/home/oracle/DBIMPEXP/Hi"

echo "Database or tables have been exported successfully!" >$MSG
echo "This is a system generated message so do not reply to this email!" >>$MSG

NOW=$(date +"%d-%m-%Y-%T")
echo $NOW

exp DBusername/DBPassword@yourSID file=/home/oracle/DBIMPEXP/"Backup_$NOW.dmp" log=/home/oracle/DBIMPEXP/"explog_$NOW.log" tables={SC_LEARNING,SC_NEWS}

/bin/mail -s "$SUBJECT" "$EMAIL" < $MSG

4> To save and close hit Esc and ctrl+zz

5> execute the shell script by sh DBExport.sh

6> Now inside the /home/oracle/DBIMPEXP folder you should get the dmp file and the log file.

7> Thats it!!! Be happy always :)