Oracle Backup and Recovery

After installing the Oracle database it should be configured for archivelog mode and backed up daily. The backups should be taken offsite.

For Oracle XE there is already a backup script that uses Oracle’s RMAN.

Configure

Setup Archive Log Mode

After installing the database change it to archivelog mode:

sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

Restart the database

Verify

To make sure the database is really in archive log mode run the following sql statement:

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
Run the Initial Backup

Windows run $ORACLE_HOME/bin/Backup.bat

Linux run $ORACLE_HOME/config/scripts/backup.sh

Daily Backups

Windows

Add the $ORACLE_HOME/bin/Backup.bat to a job scheduler.

  • Make sure the user running the job is the same user who installed Oracle.
Linux

Remove the user notification line from the end of the $ORACLE_HOME/config/scripts/backup.sh script

#Wait for user to press any key
#echo -n "Press ENTER key to exit"
#read userinp

Create the following script, call it oraclebackup.sh and put it into /etc/cron.daily

Add the $ORACLE_HOME/config/scripts/backup.sh to cron.daily

#!/bin/sh
#
# oraclebackup.sh
#

export $ORACLE_BASE=/usr/lib/oracle/xe
export $ORACLE_HOME=$ORACLE_BASE/app/oracle/product/10.2.0/server
su - oracle "$ORACLE_HOME/config/scripts/backup.sh"
mail -s 'Oracle Backup' youremail < $ORACLE_BASE/oxe_backup_current.log

Make sure you set the execute bit on the file

chmod 755 /etc/cron.daily/oraclebackup.sh

Backing up files

Make sure that your operating system backup script backups up the $HOME/app directory.  This contains all the Oracle installation files, the backups and the current archive logs.  On windows make sure the oradata directory is excluded since this might cause file locking issues.

Restore

Run the restore.sh script

[oracle@localhost ~]$ $ORACLE_HOME/config/scripts/restore.sh
This operation will shut down and restore the database. Are you sure [Y/N]?Y
Restore in progress...
Restore of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_restore.log.
Press ENTER key to exit
[oracle@localhost ~]$