Oracle backup strategy and automation (RMAN + Data Pump)

Sets up a permanent, automated backup scheme for an installed database (recovery after a failure is covered in a separate guide: "RMAN restore and recovery"): weekly level 0 + daily level 1 (or a full backup every day), frequent archived log backups, FRA or a separate directory, retention, compression, encryption, block change tracking and parallelism. In addition, a full expdp using a wallet. Complete scripts (lock, log, error checking, e-mail) are scheduled with cron, a systemd timer or DBMS_SCHEDULER; a weekly RESTORE VALIDATE and maintenance are included. Separate paths for single instance, Oracle Restart and RAC (FRA and snapshot controlfile in ASM, the job runs only on the first open instance); OL/RHEL 7.9–9. Optional: RMAN recovery catalog (CREATE CATALOG, REGISTER, connection through a wallet, backup of the catalog itself) and a monthly restore drill (RESTORE … VALIDATE + PREVIEW job, real restore on a test server).

Topology: 1 server (single instance or Oracle Restart) or RAC (2–8 nodes)

Before you start

• Database … is open and registered in /etc/oratab; ORACLE_HOME ….… The database is registered with Clusterware (srvctl config database -db …).…
• The log mode and FRA settings of the database are known. If ARCHIVELOG is off, a short outage (restart) has been planned to enable it.
• Enough disk space: about … GB for the FRA (…)…, about … GB for the backup directory (…)……, about … GB for the dump directory (…)…. The calculation is a rough estimate (Notes section); backup space must be separate from the database disks.
• cron (cronie) is installed and running. The jobs run as the oracle user through a root-owned /etc/cron.d file; PAM (/etc/security/access.conf) does not block oracle's cron jobs.
• The password of the oracle OS user is known (for the DBMS_SCHEDULER credential) and $ORACLE_HOME/bin/jssu is setuid root (root.sh has been run).
• The server can send e-mail: the local MTA (postfix) relays to a relay server, or SMTP is configured for mailx.
• The TDE keystore is configured and open (v$encryption_wallet → OPEN). The keystore password and a keystore backup are kept in a safe place: if the keystore is lost, encrypted backups cannot be read.
• Licensing is in order: …parallelism and block change tracking are available in Enterprise Edition…; … compression requires the Advanced Compression Option……; encryption of disk backups requires the Advanced Security Option……; Data Pump COMPRESSION=ALL requires the Advanced Compression Option….…On Standard Edition 2 RMAN runs with a single channel; BASIC compression can be used.…
• There is a place for a second copy of the backups: tape/MML, object storage or another server. The only copy of a backup must not sit on the same server as the database.
• OL/RHEL 7.9: supported for 19c; software is installed with yum. OL7 Premier Support ended in June 2024 (Extended Support continues); RHEL 7 needs an Extended Life Cycle Support subscription. Prefer OL/RHEL 8–9 for new installations.
• The FRA is in an ASM disk group (…)… and all instances see the same FRA; the archive destination for every instance is USE_DB_RECOVERY_FILE_DEST….

Phases of the guide

• Preparation — Measure the current state and sizes · Create the backup directories · Configure the FRA · Enable ARCHIVELOG mode · Enable ARCHIVELOG mode (srvctl) · Prepare cron and the oracle user's cron permission …
• Environment file and RMAN configuration — Write the environment file… (…)… · Enable block change tracking · Write the RMAN configuration file · Apply the RMAN settings · Set control_file_record_keep_time to match retention
• Recovery catalog (optional) — Create the catalog owner in the catalog database · Write the TNS files for the catalog connection · Add the catalog credential to the wallet · Create the catalog schema (CREATE CATALOG) · Register the database in the catalog (REGISTER DATABASE) · Back up the catalog database
• RMAN command files and the backup script — Write the level 0 command file · Write the level 1 command file · Write the archived log command file · Write the weekly validation and maintenance file · Write the monthly restore drill file · Write the backup script …
• Data Pump user and wallet — Create the export user and the directory object · Create the wallet and add the credential · Copy the wallet to the other nodes · Write sqlnet.ora (for the backup script only) · Write tnsnames.ora (for the backup script only)… (…)… · Test the passwordless connection
• First backups (manual run) — Take the first level 0 backup · Test the archived log backup · Test the level 1 backup · Take the first Data Pump export · Verify the backups from the RMAN records
• Scheduling: cron — Write the cron file · Set the cron file permissions, remove the old crontab block · Test manually with cron's environment · Verify that cron runs the jobs
• Scheduling: systemd timer — Write the service and timer files · Enable the timers
• Scheduling: DBMS_SCHEDULER — Create the OS credential · Create the scheduled jobs · Test by running a job now
• Maintenance and monitoring — Run the weekly validation and maintenance once · Run the restore drill once · Monitoring queries · Copy the backups off the server and test recovery · Perform a real restore on a separate test server · Monitor the backup history in the catalog