Automatic Backup in Oracle Database
Slovak University of Technology
Faculty of Informatics and Information Technologies
Ilkovičova 3, 842 16 Bratislava, Slovakia
Abstract. One of the Database Administrator’s (DBA) most important
responsibilities is to keep the database available for use. To protect the
data from all types of failures that can occur, the DBA must make
regular backup of the database. In this paper I concentrate on the
“logical” backup and cold (or offline) backup (database in
NOARCHIVE LOG mode). Final product (set of scripts) will do the
“logical” and cold backup of the Oracle database with selected
frequency and send a log-file with results by e-mail to Database
Without a backup it is impossible for the DBA to get a database up and running, if
there is a file loss, without loosing data. Backups are critical for recovering from
different types of failures.
Backup and recovery strategy depend on the type of company. For example I
choose software Development Company, which needs the logical backup and cold
(offline) backup of the testing database.
2 Backup scripts
2.1 Backup and Recovery strategy
The nature of backups and recovery strategy is always affected by the type of business
operations. In this case, I want to select proper backup and recovery strategy for the
small Development Company.
Supervisor: prof. Ing. Vladimír Vojtek, PhD., Institute of Applied Informatics, Faculty of
Informatics and Information Technologies STU in Bratislava
M. Bieliková (Ed.), IIT.SRC 2005, April 27, 2005, pp. 145-149.
146 Miloš Sýkora
2.2 Cold (offline) backup
Database can be configured in Archivelog Mode, so that a history of the redo
information is maintained in archived files, allowing for a complete recovery up to the
point of failure. This option is suitable for situation where a database must be available
24 hours a day, 7 days a week for continuous option.
Fig. 1. Backup Methods
Development Company does not need this solution. Therefore, the database is
configured in noarchivelog mode. The main feature of running database in
noarchivelog mode is that the redo log files are used in a circular fashion. DBA may
perform only operating system backups of the database when the database is shut
down. During each backup must DBA backup the entire set of data files, redo log files
and control files. In the case of failure DBA must recover these files from an earlier
copy of a full database (offline backup).
A closed database (cold) backup is an operating system backup of all data files,
control files, redo log files, parameter files, and the password file that constitute an
I define an operating system backup procedure that will always back up all
necessary files as part of a strategy to safeguard against potential media failures that
can damage files. It is a simple script that shut down the database, copies the data files,
and opens the database. Then another script sends log file with results by e-mail to
DBA. For a Development Company I select to perform cold backup twice a month.
Automatic Backup in Oracle Database 147
Fig. 2. Closed Database Backup
Performing a closed database backup:
1. define local variables (ORACLE_HOME, ORACLE_SID¸
2. connect to database
3. start spooling in log file
4. select start date
5. shutdown database
6. copy and pack all necessary files
7. startup database
8. select end date
9. stop spooling
10. send log file via e-mail to DBA
2.3 Logical backup
Logical backup provides Oracle Export and Import Utility. Export utility provides a
logical backup of database objects or an entire database. The import utility is used to
read a valid Export file for moving data into database.
For a Development Company I choose everyday logical backup in Full database
mode. This mode exports all objects, except those in SYS schema.
148 Miloš Sýkora
Fig. 3. Logical backup in Full database mode
Performing a logical backup:
1. define local variables (ORACLE_HOME, ORACLE_SID¸
2. create directory for actual month (if necessary)
3. backup in Full database mode
4. pack Export file
5. send log file via e-mail to DBA
2.4 E-mail notification
In previous sections I describe how to make backups. It is important to know for each
DBA what the result of these scripts was. The easiest way is send an e-mail with the
log file of screen output.
Sending mails from operating system depends on platform. Because of this, DBA
needs to implement the functionality of sending email directly from the Oracle
database. Oracle has an e-mail server option that allows programmer to send mail from
within his code. In the case, users can send e-mails to competent persons when a
critical parameter is altered, when a database has some issues (for DBAs), or when a
backup is over and so on.
For e-mails without attachment UTL_SMTP and UTL_TCP packages are
required. UTL_SMTP is designed for sending e-mail over Simple Mail Transfer
Protocol (SMTP). The UTL_TCP package provides TCP/IP client-side access
functionality in PL/SQL.
Automatic Backup in Oracle Database 149
In this case DBA need to attach log files. When DBA sends email using Java
Stored Procedures (JSP) and the Sun Microsystems JavaMail package, attachments can
also be sent.
Performing mail notification:
1. Create the mail user
2. Grant appropriate role privileges to the mail user
3. Grant java permissions
4. Create java source
The DBA have to ensure the complete path names of the files are noted and used
appropriately in backup. In a multiple database environment, care must be taken to
associate these files with the corresponding database through some naming convention,
since the names of the parameter files and password files are not recorded in the
Location of backup scripts depends on operating system platform. UNIX cron
jobs can be scheduled with the crontab command. Windows can schedule any
application using the Windows Scheduler.
The primary goal of these backup scripts is to relieve the DBA from backup routine.
Only what he has to do is check his e-mails, and makes appropriate action (copy
backup files to tape, etc).
1. Schwinn, U., Venkatachalam, V.: Oracle8: Database Administration, Vol 1,
2. DiIanmi, J., Raman, S., Robson, P.: Oracle8: Backup and Recovery Workshop, Vol
1 (October 1998)