Oracle 19c Backup Basics for Small Businesses: RMAN, Data Pump and Restore Testing

Many small and mid-sized businesses run their ERP, POS or accounting system on a single Oracle Database server in the office or a data centre. Often there is no full-time DBA, and the “backup” is a copied folder or an export that nobody has tried to restore. When the disk fails or ransomware strikes, that is when the gap is discovered.

This guide gives a simple, realistic backup plan for Oracle Database 19c that a small IT team can run and verify.

1. Decide what you can afford to lose

Two questions define your backup design:

• Recovery Point Objective (RPO) – how much data can you lose? A day of sales? An hour? Nothing?
• Recovery Time Objective (RTO) – how long can the system be down while you restore?

A retail chain posting hundreds of invoices per hour usually cannot accept losing a full day. That requirement leads directly to ARCHIVELOG mode and RMAN.

2. Use two kinds of backup

• Physical backups with RMAN copy the database files and archived redo logs. They allow recovery to a point in time and are the primary protection.
• Logical backups with Data Pump (expdp) export schemas and tables. They are useful for restoring a single table, moving data to a test server or keeping a portable copy.

A Data Pump export alone is not a complete backup strategy: you can only restore to the moment the export was taken.

3. Enable ARCHIVELOG mode

In NOARCHIVELOG mode, Oracle overwrites its redo logs, so you can only restore to your last full backup. ARCHIVELOG mode keeps copies of the logs so changes after the backup can be replayed. Check and enable it during a planned maintenance window:

SELECT log_mode FROM v$database;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Make sure the archive destination or Fast Recovery Area has enough disk space and is monitored. A full archive destination will stop the database.

4. Configure RMAN once

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

The retention policy keeps enough backups to recover to any point in the last seven days. Control file autobackup ensures the control file and server parameter file are backed up with every backup — without them, a restore on a new server is much harder. Basic compression is included in Oracle Database editions and reduces backup size significantly.

5. A daily RMAN backup script

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE NOPROMPT OBSOLETE;
  CROSSCHECK ARCHIVELOG ALL;
}

Save this as a script file and run it from Windows Task Scheduler or cron, for example:

rman target / cmdfile=D:\backup\daily_backup.rman log=D:\backup\logs\daily.log

For busier systems, add an hourly BACKUP ARCHIVELOG ALL NOT BACKED UP; job so archived logs are copied off the main disk frequently. That is what keeps your possible data loss small.

6. A weekly Data Pump export

expdp system schemas=ERP_APP directory=DATA_PUMP_DIR
      dumpfile=erp_app_weekly.dmp logfile=erp_app_weekly.log
      flashback_time=systimestamp

flashback_time makes the export consistent to one moment, so related tables such as invoices and invoice lines match each other. Include the date in the file name in your script so older exports are not overwritten.

7. Follow the 3-2-1 rule

• 3 copies of your data (production plus two backups)
• 2 different storage types (for example, a backup disk and cloud or NAS storage)
• 1 copy offsite and not permanently connected to the server

Ransomware can encrypt backup folders on the same server or on mapped drives. Copy backups to storage that the database server cannot overwrite, such as object storage with versioning or a rotated external disk.

8. Test restores — on purpose

A backup that has never been restored is an assumption. RMAN can check backups without overwriting anything:

RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE DATABASE PREVIEW;

At least every quarter, restore the latest backup onto a separate test server and confirm the application opens and recent transactions are present. Time the restore — that measured time is your real RTO.

9. Monitor every day

• Check the backup log for errors, or query V$RMAN_BACKUP_JOB_DETAILS for job status.
• Monitor free space on the archive destination and backup disks.
• Send an email alert when a backup job fails (APEX_MAIL or a monitoring tool can do this).
• Keep a short written restore procedure where more than one person can find it.

Backup checklist

• RPO and RTO agreed with management
• ARCHIVELOG mode enabled and space monitored
• RMAN retention, control file autobackup and compression configured
• Daily full and frequent archive log backups scheduled
• Weekly consistent Data Pump export
• Offsite, ransomware-resistant copy
• Quarterly restore test with documented results
• Failure alerts in place

Every environment is different. Review this plan against your Oracle edition, licensing, storage and business requirements before relying on it.

Need help protecting your Oracle database?

AbairTech supports Oracle Database, PL/SQL and APEX systems for ERP and POS users. See Oracle Database & PL/SQL Development Services or contact us.