Best practices Oracle big data backup and recovery

Backup and recovery for Oracle systems with large data volumes (big data) requires a different approach than small systems. Here are best practices for Oracle backup and recovery at big data scale:

1. Use RMAN (Recovery Manager)

Why RMAN?

* Native tool from Oracle * Efficient in incremental backup and compression * Can do parallelism * Support granular and fast recovery

Best Practices:

* Use Incremental Level 1 backups daily * Create Level 0 full weekly backup * Enable Block Change Tracking to speed up incremental backups. * Combine with compression (RMAN COMPRESSED BACKUPSET) * Use RMAN Catalog for long-term backup metadata management*

2. Partitioning & Subsetting

For large tables:

* Use Partition Table per time (month/year) → easy to backup and recover partially * Backups can be done per partition using DATA PUMP or RMAN per tablespace.

3. Implement Data Guard / Standby

Oracle Data Guard:

* Create a standby database (physical/logical) * Can be used as read-only for reporting * Can directly promote as primary if primary is down

For big data, this provides fast failover and minimal downtime.


4. Backup ke Cloud atau Disk Terpisah

* Use Oracle Secure Backup (OSB) for backup to tape/cloud * Or use direct backup to cloud storage (Oracle Cloud, S3-compatible) * Make sure the backup location is different from primary storage.

5. Data Pump (expdp/impdp) untuk Logical Backup

Gunakan untuk:

* Schema backup * Backup data subsets (partitions, tables) * Migration between systems

Tips:

* Use the PARALLEL parameter, DIRECT_PATH=Y * Split output files across multiple disks (multiplexing)

6. Documented & Tested Recovery Plan

Must:

Documentation for recovery from:

* RMAN backup * Data Guard failover * Flashback Database (if active)

Simulate recovery periodically:

* Create scenarios: lost tablespace, data corruption, human error, crash

7. Flashback Technology (optional but powerful)

If using Enterprise Edition:

* Flashback Table * Flashback Database * Flashback Drop (Recyclebin)

Very useful for recovery from human error without restoring the full database.


8. Monitoring & Automation

* Use Oracle Enterprise Manager (OEM) for backup & restore monitoring * Schedule backup with cron/Scheduler * Ensure alerts if backup fails (email, webhook, etc.)

Daily Big Data Backup Strategy Example:

Day	Backup Type	Tool
Monday - Saturday	Incremental (Level 1)	RMAN
Sunday	Full Backup (Level 0)	RMAN
Every day	Logical subset (optional)	Data Pump
Weekly	Export Metadata Only	Data Pump
Monthly	Archiving ke Cloud/Tape	RMAN

Here is an example backup scenario for Oracle big data using RMAN and Data Pump with best practices.

1. RMAN Backup: Daily and Weekly Strategy

Initial Setup

Enable Block Change Tracking to make incremental backups faster:

sql ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/bct.f';

Weekly: Full Level 0 Backup (Sunday)

bash rman target / RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 0 DATABASE     TAG 'FULL_WEEKLY'     FORMAT '/backup/rman/full_%d_%T_%U.bkp';   BACKUP CURRENT CONTROLFILE     FORMAT '/backup/rman/ctl_%d_%T_%U.bkp';   BACKUP SPFILE     FORMAT '/backup/rman/spf_%d_%T_%U.bkp';   RELEASE CHANNEL ch1; }

Daily: Incremental Level 1 Backup (Monday–Saturday)

bash rman target / RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 1 DATABASE     TAG 'INCR_LEVEL_1'     FORMAT '/backup/rman/incr_%d_%T_%U.bkp';   BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES     FORMAT '/backup/rman/arch_%d_%T_%U.bkp'     DELETE INPUT;   RELEASE CHANNEL ch1; }

Restore Simulation (Summary)

bash rman target / STARTUP MOUNT; RESTORE DATABASE; RECOVER DATABASE; ALTER DATABASE OPEN;

2. Data Pump Backup: Logical Export

Use this for logical backups (schema, tables, etc.)


Backup 1 Scheme (eg SCOTT)

bash expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_%DATE:~0,10%.dmp logfile=SCOTT_exp.log \   schemas=SCOTT parallel=4 compression=all

Backup 1 Large Table (partitioned)

bash expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SALES_%DATE:~0,10%.dmp logfile=SALES_exp.log \   tables=SALES query="WHERE sales_date >= TO_DATE('01-APR-2025','DD-MON-YYYY')" \   parallel=4 compression=all

Restore (Import) Example

bash impdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_2025-04-23.dmp \   remap_schema=scott:scott_test \   logfile=SCOTT_imp.log

Combination Tips:

* Use RMAN for fast disaster recovery and restore * Use Data Pump for logical backups and migrations (between servers/sandboxes) * Logical backups can run in parallel and be compressed * RMAN backups are stored on disk or cloud (via SBT library or Oracle Cloud Backup)

Here is a complete example of a shell script + cron job to perform an Oracle backup using RMAN and Data Pump, with daily and weekly scenarios respectively.

Backup Directory Structure

Example backup directory:

bash /backup/oracle/rman/ /backup/oracle/datapump/ /log/oracle/backup/

# for RMAN backups # for export Data Pump # for backup logs

1. RMAN Backup Scripts: backup_rman.sh

bash #!/bin/bash DATE=$(date +%F) BKUP_DIR="/backup/oracle/rman" LOG_DIR="/log/oracle/backup" ORACLE_SID=ORCL export ORACLE_SID mkdir -p $BKUP_DIR $LOG_DIR rman target / log="$LOG_DIR/rman_backup_$DATE.log" <<EOF RUN {   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;   BACKUP INCREMENTAL LEVEL 1 DATABASE     TAG 'INCR_LEVEL_1'     FORMAT '$BKUP_DIR/incr_%d_$DATE_%U.bkp';   BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES     FORMAT '$BKUP_DIR/arch_%d_$DATE_%U.bkp'     DELETE INPUT;   BACKUP CURRENT CONTROLFILE     FORMAT '$BKUP_DIR/ctl_%d_$DATE_%U.bkp';   RELEASE CHANNEL ch1; } EOF

2. Data Pump Scripts: backup_datapump.sh

bash #!/bin/bash DATE=$(date +%F) BKUP_DIR="/backup/oracle/datapump" LOG_DIR="/log/oracle/backup" ORACLE_SID=ORCL export ORACLE_SID mkdir -p $BKUP_DIR $LOG_DIR expdp system/password directory=DATA_PUMP_DIR \   dumpfile=SCOTT_${DATE}.dmp \   logfile=$LOG_DIR/datapump_SCOTT_${DATE}.log \   schemas=SCOTT parallel=4 compression=all

3. Add to Crontab

Edit crontab:

bash crontab -e

RMAN Daily Schedule (01:00 AM)

cron 0 1 * * 1-6 /path/to/backup_rman.sh

Data Pump Weekly Schedule (every Sunday at 02:00)

cron 0 2 * * 0 /path/to/backup_datapump.sh

Important Tips:

* Test the script first with bash backup_rman.sh before cronizing it. * Make sure the user running the cron job (oracle, backup, etc.) has access to ORACLE_HOME, ORACLE_SID, and the rman, expdp path. * Jangan lupa bersihkan backup lama secara berkala (pakai find ... -mtime +X -delete)

Delete RMAN backups

1. Backup is no longer needed

You may delete RMAN backups if:

* Already have a more recent backup (e.g. weekly level 0 backup). * Not being used by recovery window. * Not required for point-in-time recovery.

2. Do Not Manually Delete Files from Disk

Do not directly delete .bkp files from a folder like using rm. That can cause the RMAN catalog to become unsynchronized.

Use the official RMAN command:

rman DELETE BACKUP OF DATABASE; DELETE BACKUP OF ARCHIVELOG ALL; DELETE EXPIRED BACKUP;

Example: Delete Backups Older Than 14 Days

bash rman target / DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-14'; DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7';

Check the Saved Backup First

sql LIST BACKUP SUMMARY; LIST BACKUP OF DATABASE;

Add in Cron Job (Automatic Cleanup)

bash rman target / <<EOF DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-14'; EOF

Additional Tips:

Use RECOVERY WINDOW for automatic setup:

rman CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

RMAN will automatically know which backups are "expired" if there is a retention policy.


Shell script to automatically delete old RMAN backups, suitable to run via weekly cron job for example.

Script: cleanup_rman_backup.sh

bash #!/bin/bash # Setup environment ORACLE_SID=ORCL export ORACLE_SID LOG_DIR="/log/oracle/backup" mkdir -p $LOG_DIR DATE=$(date +%F) rman target / log="$LOG_DIR/rman_cleanup_$DATE.log" <<EOF -- Konfigurasi retensi (kalau belum diatur) CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; -- Hapus backup yang tidak dibutuhkan lagi (berdasarkan retensi) DELETE NOPROMPT OBSOLETE; -- Optional: hapus archive log lama (jika tidak pakai FRA) DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DISK; -- Bersihkan backup expired dari katalog CROSSCHECK BACKUP; DELETE NOPROMPT EXPIRED BACKUP; EOF

Notes:

* This script does not delete files directly. It only deletes officially obsolete or expired backups from the RMAN catalog. * Safe to use and supports automatic retention.

Weekly Cron Job Example (Every Sunday Morning at 3am)

bash 0 3 * * 0 /path/to/cleanup_rman_backup.sh

If you use Fast Recovery Area (FRA), RMAN can also automatically clean up old files, but this script is still important for cleaning up irrelevant catalogs and backups.