Creating database backups using exp & and crontab

The following is a neat little script that will allow you to create logical backups (and remove old ones) of your schema in the same file system of the database.


ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH
export PATH

BKUP_DEST=/home/rode/cbackups
find $BKUP_DEST -name 'backup*.dmp' -mtime +10 -exec rm {} \;



cd /home/rode/cbackups && /u01/app/oracle/product/11.2.0/xe/bin/exp schema/password FILE=backup_`date +'%Y%m%d-%H%M'`.dmp

You will need to change the bits highlighted in orange with paths of your system. The script starts by exporting necessary paths, then removes any old backups greater than 10 days before using the exp utility to create the backup.

To set it up, follow these instructions: 

1. Create a directory where the backups will be stored. In my case it is: /home/ai/cbackups
2. Open vi and save the scripts (after replacing bits in orange with your own setup) in your home directory as say: /home/ai/backup_script.sh
3. Next, run crontab -e to set up a new cron job.
4. Add an entry like:
   10 0 * * * /home/ai/backup_script.sh

Above the the script will run every 10 minutes past midnight. Check out this link on how to schedule crontab: http://www.adminschoice.com/crontab-quick-reference


You may opt (and better idea) to use data pump instead as it supercedes the export/import utilities and if exporting involves lots of data. Check this document on how to use data pump:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Resolving ORA-01940 error

This post shows how to get around ORA-01940 error. You typically get this error when the schema you are trying to drop is connected to something (presumably another application).

The following script will allow you to kill the sessions and drop the schema.

set serveroutput on
DECLARE
  cursor c1 is select SID, SERIAL# from v$session where username = '<schema_name';
BEGIN
FOR rec IN c1 loop
EXECUTE IMMEDIATE 'alter system kill session '''||rec.SID||','||rec.SERIAL#||'''';
end loop;
DROP USER <SCHEMA_NAME> CASCADE;
END;