Hello, friends in this article we are going to learn how to schedule database export backup automatically. Yes, it is possible to export backup automation with a crontab scheduler.
How to schedule Export Backup
Using shell scripting, we can schedule the export backup as per our convenient time. Using the following steps we can schedule expdp backup.
Step 1: Create Backup Location
First, we need a backup location where we create the export backup.
A. Create a directory at OS level.
OS level directory called physical directory.
B. Create directory at the Database level.
Database level directory called logical directory.
$sqlplus / as sysdba SQL> create directory EXP_DUMP as '/u01/EXPORT_BKP/ ';
Check directory information at the Database level.
SQL> col OWNER for a20 SQL> col DIRECTORY_NAME for a30 SQL> col DIRECTORY_PATH for a65 SQL> set lin 300 pagesize 300 SQL> select * from dba_directories;
List of directories:
Step 2: Create Schema
To execute the export backup we need a dedicated schema to run the database backup. We also run the backup as an SYS user but sometimes clients require a dedicated schema.
SQL> create user master identified by master default tablespace users quota unlimited on users account unlock; user created.
Step 3: Grant Permissions
In step 2 we created a schema with the name of the master, so this is the schemas that we are going to use for taking database export backup. So, we need to grant the required permissions to the master user. Use the below commands to grant permissions.
SQL> grant read, write on directory EXP_DUMP to master; Grant succeeded. SQL> grant exp_full_database, imp_full_database to master Grant succeeded.
Step 4: Create a Backup shell script
It’s time to prepare the shell script and grant 775 permission to shell script.
Add below code inside export_bkp.sh
export ORACLE_SID=digital export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1/ export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH expdp master/master full=y directory=exp_dump dumpfile=full_bkp.dmp logfile=full_bkp.log
Set 775 permission for shell script
$chmod 775 /u01/EXPORT_BKP/export_bkp.sh
Step 5: Schedule export backup in crontab.
This is the final step, here we are going to make it automatic with the help of the crontab scheduler.
Open crontab and add the below entry inside.
30 19 * * * /u01/EXPORT_BKP/export_bkp.sh
As per the above scheduler, we scheduled the export backup at 7:30 PM. So, you can change the time as per your requirement. Now Backup will run automatically. This is the way to make Export Backup Automation.
Advance Export Backup Script
There is an advance script for export backup, using this script we can take export backup with the date.
export ORACLE_SID=digital export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1/ export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH mkdir -p /u01/EXPORT_BKP/`date +%d-%m-%y` expdp master/master full=y directory=exp_dump dumpfile=full_bkp_`date +%d-%m-%y`.dmp logfile=full_bkp_`date +%d-%m-%y`.log mv /u01/EXPORT_BKP/*`date +%d-%m-%y`.dmp /u01/EXPORT_BKP/`date +%d-%m-%y`/ mv /u01/EXPORT_BKP/*`date +%d-%m-%y`.log /u01/EXPORT_BKP/`date +%d-%m-%y`/
I hope you really enjoyed this article if yes please write in the comment box.
You Must Read:
- RMAN BACKUP SCRIPT
- Queries to Monitor Expdp Datapump Jobs Status
- How to Export Tables using PARFILE STEP BY STEP
- Estimate Required Disk Space for Export Using Estimate_only
- Check Datapump dump file is corrupted or not
Learn more about DataPump