STEP BY STEP - Using DBMS_Scheduler to Run Hot Backups

STEP BY STEP – USING DBMS_SCHEDULER TO RUN HOT BACKUPS


With Oracle 10g onwards, you can create a dbms_scheduler task to run your nightly exports and hot backups, without the need for using cron jobs, in Linux and Solaris environments. Please note that this also applies to Windows environments as well.

To start off create the Scheduler Program:






You don’t need to add any arguments to the Scheduler Program, as the shell script hot_backup.sh will contain all the required arguments.

Next, create the Job:




Note, in the Command Type you specifiy Executable, and indicate the type of shell you would be using. In my case it was the /bin/bash shell.
For the Arguments, you indicate the location of the shell script to be run in the bash shell.

Then create the Job Schedule:



Create the Job options, if any:




This completes the setup of the scheduler task in Oracle. Now you need to create the shell script for the hot_backup.sh, which is called as an external program.

I used the following script:



You may customise it based on your requirements. I added the .bash_profile since I had problems executing the sqlplus, executable. I also added the LD_LIBRARY_PATH as it refused to execute with errors of missing ld libraries.

Next create a hot_backup.sql script to backup all the tablespaces, similar to this:

ALTER TABLESPACE TEST BEGIN BACKUP;
HOST cp /app/oracle/oradata/test01.dbf /app/oracle/backup/.
ALTER TABLESPACE TEST END BACKUP;

Add these entries to all your tablespaces. At the end of the file backup the archive logs:

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG STOP;
HOST cp /app/oracle/archive/*.dbf /app/oracle/backup/. {set this based on the archive log location}

Let me know if you have any issues.