Oracle Database Backup & Recovery: RMAN Scripts, Incremental Strategies & Flashback Essentials

Database Failure Types

User process failure – handled automatically by PMON.

Instance failure – handled automatically by SMON.

User errors – require DBA intervention via backup and recovery.

Media failure – must be recovered using backups and redo logs.

Summary: Backup and recovery primarily protect data against media, user, and application failures, retain data, and transport data.

Backup and Recovery Classification

Restore (重储) vs Recover (恢复). Two main categories:

Logical Backup and Recovery

Traditional export/import: exp/imp.

Data Pump export/import: expdp/impdp.

Object‑oriented; captures a logical snapshot of database objects at a point in time.

Cannot be used for media failure; logical restore has no recover phase.

Physical Backup and Recovery

Targets media failure.

User‑managed backup and recovery (UMAN): uses OS commands to copy files, then applies redo logs for recovery.

Automated backup and recovery with RMAN: automates restore and recover.

Physical backups can be consistent (cold backup) or inconsistent (hot backup). A complete strategy uses physical backup as primary, logical backup as supplementary for critical tables.

Flashback Technology

Fast recovery using undo data or flashback logs.

Supports logical recovery at different levels.

Oracle 11g provides seven flashback variants; the fast recovery area is only related to flashback database.

Complete vs Incomplete Recovery

Complete Recovery

Uses full or partial backups to restore datafiles to the last commit before failure – no data loss.

Incomplete Recovery

Uses full backup and logs to restore the database to a past point in time (or SCN) – some data loss occurs.

Backup Strategy

Weekly level‑0 full backup on Sunday; daily level‑1 incremental backups Monday through Saturday.

Retention policy: REDUNDANCY 2.

Control file autobackup enabled.

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

Backup Scripts

Level‑0 Full 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 pieces on disk/tape; statuses: AVAILABLE, UNAVAILABLE, EXPIRED.

Make scripts executable:

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

Cron Schedule (Test Environment)

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 logs for success; verify obsolete backups and archivelogs are deleted correctly.

Backup Job Monitoring Query

select a.TIME_TAKEN_DISPLAY as "用时",
       a.INPUT_BYTES_DISPLAY "大小",
       a.INPUT_BYTES_PER_SEC_DISPLAY as "速度",
       a.INPUT_TYPE "备份类型",
       to_char(start_time, 'day') day1,
       to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') as "开始时间",
       to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') "结束时间",
       output_device_type,
       status,
       input_type
from V$RMAN_BACKUP_JOB_DETAILS a