Master Oracle Backup & Recovery: Strategies, Scripts, and Best Practices

A reader complained that his boss said his knowledge of database backup and recovery was so poor that he might not survive the probation period. Data is crucial for enterprises; mastering backup and recovery can lead to promotion, while poor handling can cause data loss.

Database Failure Types

User process failure – handled automatically by PMON.

Instance failure – handled automatically by SMON.

User errors – require DBA to restore from backup.

Media failure – must be recovered using backups and logs.

Backup and recovery protect data from media, user, or application issues, preserve data, and enable data transfer.

Backup and Recovery Classification

Restore means “Restore”, recovery means “Recover”.

Logical Backup and Recovery

Traditional export/import: exp/imp.

Data Pump export/import: expdp/impdp.

Object‑oriented logical backup captures the state of database objects at a point in time.

Not applicable to media failures; logical recovery only restores the backup without a recover phase.

Physical Backup and Recovery

Targeted at media failures.

Manual backup and recovery (UMAN) using OS commands, then apply logs for recovery.

Automatic backup and recovery using Oracle RMAN.

Physical backups can be consistent (cold) or inconsistent (hot). A complete backup strategy should prioritize physical backups, with logical backups supplementing important tables.

Flashback Technology

Fast recovery technique using UNDO data or flashback logs.

Provides logical recovery at various levels.

Oracle 11g supports seven flashback methods.

The fast recovery area is only related to flashback database.

Complete vs Incomplete Recovery

Complete Recovery

Using full or partial backups to restore datafiles.

Restores to the last committed transaction before failure, with no data loss.

Incomplete Recovery

Requires full backup and archived logs to restore the database to a past point (time or SCN), which may involve data loss.

Backup Strategy

Full level‑0 backup every Sunday; incremental level‑1 backups Monday through Saturday.

Retention policy set to redundancy 2.

Enable automatic control‑file backup.

RMAN> show all;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Backup Scripts

# Create script directory
mkdir /home/oracle/scripts

# Level‑0 backup script (rman_level0.sh)
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=PROD
PATH=/usr/sbin:$PATH
export PATH
PATH=$ORACLE_HOME/bin:$PATH
export PATH
rman target / <<EOF
run{
  crosscheck backup;
  allocate channel c1 device type disk;
  allocate channel c2 device type disk;
  backup incremental level 0 database format '/u01/backup/rman/db_%U.bak' \
    plus archivelog format '/u01/backup/rman/ar_%U.bak';
  backup current controlfile format '/u01/backup/rman/ctl_%U.bak';
  report obsolete device type disk;
  delete noprompt obsolete device type disk;
  delete noprompt expired backup device type disk;
  release channel c1;
  release channel c2;
}
EOF
exit

CROSSCHECK verifies backup files on disk or tape; statuses include AVAILABLE, UNAVAILABLE, EXPIRED.

chmod a+x /home/oracle/scripts/rman_level0.sh
chmod a+x /home/oracle/scripts/rman_level1.sh

Crontab Scheduling

0 * * * * /home/oracle/scripts/rman_level0.sh >> /u01/backup/rman/PROD_rman_L0_$(date +\%Y\%m\%d_\%H\%M\%S).log 2>&1
15,30,45 * * * * /home/oracle/scripts/rman_level1.sh >> /u01/backup/rman/PROD_rman_L1_$(date +\%Y\%m\%d_\%H\%M\%S).log 2>&1

Check backup status and logs, verify obsolete backups and archive deletions.

select a.TIME_TAKEN_DISPLAY as "Duration",
       a.INPUT_BYTES_DISPLAY "Size",
       a.INPUT_BYTES_PER_SEC_DISPLAY as "Speed",
       a.INPUT_TYPE "Backup Type",
       to_char(start_time, 'day') day1,
       to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as "Start Time",
       to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') "End Time",
       output_device_type,
       status,
       input_type
  from V$RMAN_BACKUP_JOB_DETAILS a;