Hi,
We can schedule expdp and impdp using cronjob to have logical backup of our Databases on daily basis or depending on the requirement..In the below article
I'm taking two Databases TESTDB_1 and TESTDB_2.In the below shell scripts,I'm doing tasks for scheduling expdp job using
cronjob. In the below article we are doing below tasks for 'expdp',However similary we can do for 'impdp' by changing impdp scripts:
1)I'm setting the Oracle Environment,
2)Creating expdp script for taking export Datapump backup(Logical Database backup).
3)Moving log files to a separate Directory on the server.
4)Giving all the required permission for the dumpfile backup.
5)Zipping the dumpfiles using gunzip utility.
6)Deleting the dumpfiles which are more than 6 days(7days) old. &
7)Using Cronjob to execute and schedule this task Daily at 9pm & 11pm.
Pre-requistics steps:
-----------------------
1)TESTDB_1: Before executing and scheduling this script perform the below task in the Database side.Create a directory
for taking the datapump export backup and grant the required permissions to the user which you are going to use for
doing this export backup.In my case I took 'system' user.
Creating Directory:
--------------------
CREATE DIRECTORY expdp_TESTDB_2_dir as '/u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily';
GRANT READ,WRITE ON DIRECTORY expdp_TESTDB_2_dir TO SYSTEM;
Note: If you a creating a separate user or plannning to use separate user for taking the datapump export backup than,
Grant the required privileges.
Eg: Grant connect,exp_full_database,imp_full_database to Test_user; or Grant DBA to Test_user;
Shell script for TESTDB_1 Database is below:
--------------------------------------------
backup TESTDB_1.sh:
--------------------------
#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History
#================
#DATE AUTHOR http://rafioracledba.blogspot.com LAST-CHANGE
#--------- ----------------------- --------------------------------- -----------
#26-MAY-2011 RAFI ALVI SCRIPT FOR FULL EXPORT TESTDB_1 01-JUN-2011
#
#
#
################################################################
export ORACLE_BASE=/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB_1
export PATH=$PATH:$ORACLE_HOME/bin
expdp system/TESTDB_1dba@TESTDB_1 dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=expdp_TESTDB_1_dir logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_TESTDB_1
############################################################################
#Moving log files
mv /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/expdp*.log /u05/DB_BACKUP_TESTDB_1/logs/expdp_fulldb_backup_logs
###############################################################################3
#Granting permissions for Dump files
chmod -R 775 /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/*
#################################################################################
#Compressing files
gzip /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup/*.*
#################################################################################33
#Removing 7 days old dump files
find /u05/DB_BACKUP_TESTDB_1/expdp_fulldb_backup -mtime +6 -exec rm {} \;
######################################################################################
cronjob script:
----------------------
##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log
Similary you can do for any Database.In the below case I'm doing it for the 2nd Database 'TESTDB_2'.
2)TESTDB_2:
------------
SQL>CREATE DIRECTORY expdp_TESTDB_2_dir as '/u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily';
SQL>GRANT READ,WRITE ON DIRECTORY expdp_TESTDB_2_dir TO SYSTEM;
backup TESTDB_2.sh:
--------------------------------
#!/bin/bash
#Script to Perform Datapump Export Every Day
################################################################
#Change History
#================
#DATE AUTHOR http://rafioracledba.blogspot.com LAST-CHANGE
#--------- ----------------------- --------------------------------- -------------
#26-MAY-2011 RAFI ALVI SCRIPT FOR FULL EXPORT TESTDB_2 01-JUN-2011
#
#
#
################################################################
export ORACLE_BASE=/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=TESTDB_2
export PATH=$PATH:$ORACLE_HOME/bin
expdp system/TESTDB_2dba@TESTDB_2 dumpfile=expdp-`date '+%d%m%Y_%H%M%S'`.dmp directory=expdp_TESTDB_2_dir logfile=expdp-`date '+%d%m%Y_%H%M%S'`.log FULL=Y EXCLUDE=STATISTICS CONTENT=ALL JOB_NAME=FULL_EXPDP_TESTDB_2
###################################################################
#Moving log files
mv /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/expdp*.log /u05/DB_BACKUP_TESTDB_2/logs/expdp_fulldb_backup_logs
#####################################################################
#Granting Permission to dump files
chmod -R 775 /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/*
########################################################################
#Compressing the files
gzip /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily/*.*
###########################################################################
#Removing 7 days old dump files
find /u05/DB_BACKUP_TESTDB_2/expdp_fulldb_backup_daily -mtime +6 -exec rm {} \;
cronjob script:
--------------------------
##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log
Scheduling task using Cronjob:
----------------------------------------
We have to schedule the 2 export Datapump job using cronjob for doing this task perform the steps described below:
1)Create one separate directory for all your DBA/Cronjob tasks and grant all the permissions so that scripts can execute.
$mkdir DBA_TASKS
$chmod -R 775 /u04/DBA_TASKS
2)All the shell scripts and Cronjob script you can keep in /u04/DBA_TASKS.
3)We can create a separate file or edit the existing crontab file.
In my example I'm creating a separate file 'crontab.oracle' where I will put the below cronjob scripts:
vi crontab.oracle
##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log
##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log
:wq
Save and quit
(or) crontab -e
##############################################################
#Script for full export backup TESTDB_1 DATABASE
###############################################################
00 21 * * * /u05/DB_BACKUP_TESTDB_1/Backup_Scipts/expdp_FULL_BACKUP_TESTDB_1.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/UGal_cron.log
##############################################################
#Script for full export backup TESTDB_2 DATABASE
###############################################################
00 23 * * * /u05/DB_BACKUP_TESTDB_2/SCRIPTS/expdp_FULL_DB_TESTDB_2.sh 2>&1 >/u05/DB_BACKUP_TESTDB_1/logs/CRONJOBS_LOGS/wgal_cron.log
4)Schedule the job for execution using cronjob:
--------------------------------------------------
$crontab crontab.oracle
=>This command will schedule the job for execution,In the above case at 9pm and 11pm daily.
For more details regarding Cronjob Check my link Label 'UNIX FOR DBA'.
5)For verifying the cronjobs scheduled for execution:
$crontab -l
This command will list all jobs schedule for execution.
Hope it helps.
Best regards,
Rafi.
aslam alequm...brother
ReplyDeletenice explanation.
Its very good Script. Its saves my time a lot.
ReplyDelete