Databse backup automated through Crontab

[Jeena_1]

Hi i'm new to Unix.I need to do a database backup on a daily basis.This databse backup requires user entry for data (example: database name,date of backup....).

I want to make this automated using the crontab, but i'm not sure how to create a script to run this databse backup daily through crontab.

Can someone assist me on how to create this script???

pleaseeee.

[VEL_1]

Put all your database related commands in
# crontab -e

# Will execute the
15 1 * * *

[Mel Burslan]

easy part of the answer is the automating the process via cron.

logged in as root, run

crontab -e

and add this line

00 02 * * * /oracle/backup_database.sh

and save and exit.

this line will let your system run the /oracle/backup_database.sh script every day at 2:00 AM in the morning.

the hard part of the question answer is what will be inside the /oracle/backup_database.sh script ?

The answer is : it all depends. Depends on what ? Well, first it depends on how your database container(s) are structured ? Is it a filesystem structure or raw disk ? Do you have any backup software to use (like veritas netbackup or HP Data Protector) ? Or you would like to use fbackup ?

If you can tell more details, I am sure you will get better tailored answers.

[renarios]

Hi Jaskirat,

Can you tell us what your environment looks like (Unix version, Database software and version)?

Cheers,

Renarios

[Muthukumar_5]

You can do it easily with cron tab scheduling.

Make a script to use configuration for database name, date of backup. Based on that backup, you can start executing backup script with cron tab.

First create your user configuration file that will get user entry for data. Read that configuration file from your script as,

.

in the backup script. Execute cron based on your need.

hth.

[Jeena_1]

Hi....First, thanks to all for the reply..

According to Mel's solution,yes I know that the command for crontab is crontab -e.
What I don't know is what to put in the /oracle/backup_database.sh script .

Information about my system:-
-It is a Unix server
-Oracle database
-I log in into the server
-I come to a page where I have a few options
-From the options I choose database backup
-A user prompt comes out asking me what is the database name for backup....so I key in the database name
-Another user prompt comes out asking me for table name for backup...so I key in table name
-Backup will be in process...and is stored in a directory cd /u01/backup/exp

I don't know how to create the script...

Please help...

[Yogeeraj_1]

hi

Since you are running Oracle Database, you should be using RMAN for backup. This is the easiest way.

see the url below on how you would go about configuring rman (if you have not already done so, of course):
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm

the rest is to create the script to do the backup then schedule the script as described above.

if you need any further help, do let us know.

regards
yogeeraj

[Yogeeraj_1]

hi again,

a typical backup script for your full database backup would be:

#!/bin/sh
##############################################################################
#Script name: rman_full.sh
#Description: RMAN full backup script
#last Modified: Yogeeraj-10/01/2003
##############################################################################
messagelog="/backup/rman/logfiles/rman_full-`date +%d%m%y-%H%M`"
rman target xxx/xxx@mydb rcvcat rman/rman@catalog msglog $message
log </dev/null
run {
allocate channel fs1 type disk format '/backup/rman/full/fs1/df_%u_%s_$t.%p';
allocate channel fs2 type disk format '/backup/rman/full/fs2/df_%u_%s_$t.%p';

set limit channel fs1 kbytes=750000;
set limit channel fs2 kbytes=750000;

#Backup the whole database
backup
  tag Whole_database_hot
  database;

#Switch out of the current logfile
sql 'alter system archive log current';

#Backup the archived logs 
backup
  archivelog all
  format '/backup/rman/full/fs1/al_%u.%p';

#Backup a copy of the controlfile that contains records for the
#other backups just made
backup
  current controlfile
  tag = cf1
  format '/backup/rman/full/fs1/cf_%u.%p';
}
exit;
EOF
#end of script




hope this helps too!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,
sorry for the corrupted output!

script attached.

let us know it it helps!

kind regards
yogeeraj

[Jeena_1]

Hi Yogeeraj....thanks 4 your solution.I also forgot to mention that I know very basic stuff in Oracle as well...and i must admit that I never knew that RMAN can be used for backup...So, i'll have to read the link you sent me on RMAN ;) ...then I shall see how it can help in my database backup.

I am actually only doing a backup on a table in a database.So, i'm not sure if using the option "full databse" in RMAN will be a good choice....Can you let me know of that...

The script you sent me..I don't entirely understand it...is there any simpler code or something...

Thanks

[Raj D.]

Hi Jeena ,

If you have Oracle backup scripts already , then you need to schedule it with crontab , to make it automated backup , and also to decide what kind of backup you want , hotbackup or a cold backup.

For cold backup you need to shutdown oracle and those scripts also need to put in crontab .

[ To start a oracle backup at 8 pm everyday , need to add in cron as follows:]

# crontab -e
00 20 * * * su - oracle -c /oracle_home/yourbkup.script

To check and verify crontab entry :
# crontab -l


Cheers,
Raj.

[Jeena_1]

Hi Raj, yes do know the command to insert a script in crontab...But what I do not know is how to create a script...I left programming 3 years ago...and i'm not quite sure how to create a script for the backup.I would appreciate if someone could give me a hint or an example of the script itself; so I could run it through crontab.

The specification of my system, I have already mentioned in my previous post.

It is the script that i'm not sure of...

Thanks&Regards

[Yogeeraj_1 · Accepted solution]

hi,

You may wish to backup your table using a simple Oracle tool called EXP.

a simple script would look like: (backuptable.sh)


#!/bin/sh
LOG_PATH=/backup/oracle/logfiles
ORACLE_HOME=/u01/app/oracle/product/10g
DMP_PATH=/backup/oracle/export
ACC_PASS=schemaowner/*****

$ORACLE_HOME/bin/exp $ACC_PASS tables=(yourtable1, yourtable2) file=\($DMP_PATH1/tableexp.dmp\) buffer=409600 log=$LOG_PATH/tableexp.log rows=Y grants=y compress=N direct=n
#end of script



then an easy way to schedule is (using root account), adding the following line into your crontab


00 18 * * * echo "/backup/scripts/oracle/backuptable.sh" | su - oracle 1>/backup/oracle/logfiles/output-export.crn 2>/backup/oracle/logfiles/error-export.crn

hope this helps!

if you have any other questions, do let us know.

NB. You should consider doing the backup of your whole database unless your organisation accepts to take the risk of your system crashing and not being able to recover any data.

kind regards
yogeeraj

[Raj D.]

Hi Jeena ,

The above example by Yogeeraj is a good one.

Well that will kepp the oracle backup in the system , i.e disk , if you want to create a tape backup for this , you can add another line in the crontab and tape backup also can be taken of the DMP export file.


crontab -e
00 20 * * * tapebkup.sh


#script tapebkup.sh
##########################################
# Assuming Tape is in the Drive.
# starting Tape backup of Oracle Export file.

TP_STATUS="`mt -f /dev/rmt/0mn status | grep BOT | awk '{print $3}' `"
if [ $TP_STATUS = "BOT" ]
then
echo " TAPE Present in the drive .."
else
echo " No Tape in the Drive !!!!exiting...Tape backup .."
exit 1
fi
cd /backup/oracle/export
tar -cvf /dev/rmt/0m /backup/oracle/export/* >>tapebkup.log
mt -f /dev/rmt/0m rew >> tapebkup.log
mt -f /dev/rmt/0m offl >> tapebkup.log

echo "Tape backup of oracle completed at: `date ` successfully ..." >> /dev/tapebkup.log
#########################################

Hope this will help ....plannig for a script..

Cheers,
Raj.

[Jeena_1]

Yesss...this is exactly what I needed...Thanks...

Just another question..

If lets say, I decide to backup the entire database called ABC; from user abcadmin;

So, how can I modify the ACC_PASS??

Thanks

[Yogeeraj_1]

hi,

two options are possible:

1. set ACC_PASS=abcuser/*****@ABC

2. set ACC_PASS=system/***@ABC
then add the clause owner=abcuser


hope this helps too!
kind regards
yogeeraj

[Jeena_1]

hmmmm....how about the below..how can I modify it, since i'm doing database backup instead of table..


$ACC_PASS tables=(yourtable1, yourtable2) file=\($DMP_PATH1/tableexp.dmp\) buffer=409600 log=$LOG_PATH/tableexp.log rows=Y grants=y compress=N direct=n

I would also like to know what is the purpose of the command "rows=Y grants=y compress=N direct=n" as written above.



Thanks in advance...

[Yogeeraj_1]

hi again,

you should change as follows:
$ACC_PASS full=y file=\($DMP_PATH1/tableexp.dmp\) buffer=409600 log=$LOG_PATH/fulldbexp.log rows=Y grants=y compress=N direct=n

How biig is your database?

(NB. the ACC_PASS should define a user with dba privilege on the database.)

ROWS=Y: export data rows
GRANTS=Y: export grants
DIRECT=N: Don't use direct path export. Export in direct path mode simply bypasses the SQL evaluation buffer (where clause processing, column formatting and such). The speedup of the direct path export can be large however. The 10% of the processing that it cuts out, accounts for a much larger percentage of the run-time. I don't recommend using DIRECT=Y because have had faced some bugs.


more detailed explanation is available at: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm

hope this helps too!
kind regards
yogeeraj

[Jeena_1]

hi
Regarding the below:-
$ACC_PASS full=y file=\($DMP_PATH1/tableexp.dmp\) buffer=409600 log=$LOG_PATH/fulldbexp.log rows=Y grants=y compress=N direct=n

If I want the output to look like this;
ARAdmin_tm02_03102005-08:40:23.dmp
(where ARAdmin is the tablename and tm02 is the database name)

I should change the script(for line 2) to this;
file=\($DMP_PATH1/ARAdmin_tm02_"$dt".dmp\)

where dt=date (this is declared at the beginning of the scrpit)

Is it correct...Please correct me if i'm wrong...

Thanks