52. Oracle Full and Incremental Backups

Flowchart of backup strategy:

┌──────────────────────────────────────────────────────────────┐
│             Define RPO/RTO (Recover point and time           │     
│                   Backup window and capacity)                │     
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│             Choose baseline (Full or level zero              │   
│                  Backup set or image copy)                   │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐   
│        b    Daily incremental (Differential or               │ 
│              cumulative changed backup only)                 │ 
└──────────────────────────────────────────────────────────────┘     
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                   Archive Logs (Back up redo,                │
│                    protect recovery change)                  │
└──────────────────────────────────────────────────────────────┘  
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                Write Backup (Disk / FRA / SBT                │
│                   Tag and record metadata)                   │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                    Monitor & List (V$RMAN views              │
│                       LIST BACKUP SUMMARY)                   │  
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│                 Validate Restore (RESTORE VALIDATE           │
│                    Preview required backup/logs)             │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│    Retain/Crosscheck(Retention policy and metadata health    │ 
└──────────────────────────────────────────────────────────────┘ 
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│         Recovery Ready (Backups and logs available)          │ 
└──────────────────────────────────────────────────────────────┘ 
                               
Strategy flow: define objective -> baseline -> incrementals -> archive logs -> monitor -> validate -> retain -> remain recovery ready

Flowchart of restore and recovery flow:

┌──────────────────────────────────────────────────────────────┐  
│     Level 0 Full(Baseline datafile backup restored first)    │ 
└──────────────────────────────────────────────────────────────┘ 
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│              Level 1 Backups (Apply changed blocks           │
│                    Differential or cumulative)               │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│              Archived Logs (Roll forward to target           │
│               SCN/time complete recovery chain)              │
└──────────────────────────────────────────────────────────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐  
│              Recovered Database (Open normally or            │
│              RESETLOGS Depends on recovery type)             │
└──────────────────────────────────────────────────────────────┘


┌────────────────────────────────────────────────────────────────────────────────────┐  
│          RMAN Chooses the required Backup chain Automatically                      │
│   (RESTORE selects backup pieces RECOVER applies incremental and archived redo)    │
│           Use RESTORE...PREVIEW and RESTORE...VALIDATE before an incident          │                                                                     
└────────────────────────────────────────────────────────────────────────────────────┘

Flow Explanation:

• RMAN restores the baseline full or level 0 backup.

• RMAN applies the necessary level 1 backups to move datafiles towards efficiently.

• Archived redo logs and online redo are applied to reach the required recovery point.

• The database is opened normally after complete recovery or with RESETLOGS after incomplete recovery.

Core Backup Concepts:

Full Backup:

Backup of used blocks of selected files. Cannot serve as parent of level 1 unless it is level 0.

Level 0:

Incremental baseline containing all used blocks. Parent for level 1 backups.

Level 1 differential:

Changed blocks since most recent level 0 or level 1. Smallest daily backup, longer recovery chain.

Level 1 cumulative:

Changed blocks since most recent level 0. Larger daily backup, shorter recover chain.

Backup set:

RMAN proprietory logical backup. Can be compressed and skips unused blocks.

Image copy:

Physical Copy of a datafile, control file or archived log. Usedful for incrementally updated copy strategy.

Tag:

A level assigned to the backup file. Used for listing and restore selection.

Checkpoint SCN:

System change number, a number generated every nano second the database is active, used in many things, like to put the SCN of the commited DML sentence in control file and the header of data file to know which statements are commited. Used with redo to recover consistently.

Full vs Level 0 vs Level 1:

Full:

Backup Type: Full
Blocks Included: All used blocks
Can be Level 1 parent: No unless explicitly level 0.
Typical Use: Standalone full backup.

Level 0:

Backup Type: Incremental Level 0
Blocks Included: All used blocks
Can be Level 1 parent: Yes
Typical Use: Weekly baseline.

Level 1 Differential:

Backup Type: Level 1 differential
Blocks Included: Blocks changed since latest level 0 or level 1.
Can be Level 1 parent: N/A
Typical Use: Small daily backup.

Level 1 Cumulative:

Backup Type: Full
Blocks Included: Blocks changed since latest level 0.
Can be Level 1 parent: N/A
Typical Use: Faster restore/recovery.

Note: A full backup and level 0 backup may contain similar data, but only level 0 is recognised as an incremental baseline.

Backup Set vs Image copy:

Features of Backup set

Storage Format: RMAN propritory backup pieces.

Compression: Supports RMAN compression.

Unused Block Skipping: Supported. Unused blocks over High water mark can be skipped.

Restore: Must be restored.

Typical Use: Standard Backup Strategy.

Command: Backup database;

Feature of Image copy

Storage Format: Exact copy of source file.

Compression: Not normally compressed by RMAN. Same size as source.

Unused Block Skipping: Not applicable in the same way. Whole file is copied.

Restore required: Can switch directly to copies.

Typical Use: Fast recovery/incremental updated copies.

Command: Backup as copy database.

Associated Views and Commands:

v$rman_backup_job_details:

Backup status, input type, duration, compression, throughput

v$rman_status:

Cuurrent and historical RMAN Operations.

v$backup_set:

Backup set metadata during incremental level.

v$backup_piece:

Backup piece file details.

v$backup_datafile:

Datafile backup metadata and checkpoint SCNs.

v$backup_redolog:

Archive log backup records.

v$database_block_corruption:

Known corrupt blocks.

v$session_longops:

Backup and restore progress.

list backup:

Display RMAN repository backup inventory.

RESTORE..VALIDATE:

Checks whether backups can be read and selected.

RESTORE...PREVIEW:

Shows required backup and redo chain. (?)

Pre Backup checks:

Database status:

select name, open_mode, log_mode, database_role, force_logging from v$database;

select instance_name, host_name, status, startup_time from v$instance;

Datafile status:

select file#, name, status, enabled, checkpoint_change# from v$datafile order by file#;

Archive destination and FRA:

select dest_id, status, destination, error from v$archive_dest_status where status <> 'INACTIVE' order by dest_id;

select name, space_limit/1024/1024/1024 as limit_gb, space_used/1024/1024/1024 as used_gb, space_reclaimable/1024/1024/1024 as reclaimable_gb from v$recovery_file_dest;

RMAN Configuration:

show all;

Destination capacity:

host df -h /u05/rman host df -i /u05/rman host ls -id /u05/rman

Full Backup Scenario:

Scenario: Take a full backup.

Step 1 - connect:

rman target /

---

Step 2: Review Configuration

show all

---

Step 3: Run full Backup

run
{
allocate channel c1 device type disk format '/u05/rman/full_%d_%T_%U.bkp';
backup as compressed backupset database tag='FULL_DB_LAB';
backup current controlfile tag='full_db_lab_controlfile';
backup spfile tag='full_db_lab_spfile';
release channel c1;
}

---

---

Step 4: List result.

list backup tag='full_db_lab'; list backup of controlfile; list backup of spfile;

---

---

---

SQL Verification:

select session_key, input_type, status, start_time, end_time, input_bytes_display, output_bytes_display, compression_ratio from v$rman_backup_job_details order by start_time desc fetch first 10 rows only;

---

Incremental Level 0 scenario:

backup incremental level 0 as compressed backupset database tag='weekly_L0';

List level 0 Result:

list backup tag='weekly_L0';

SQL verification:

select bs.recid, bs.set_stamp, bs.set_count, bs.incremental_level, bs.backup_type, bs.start_time, bs.completion_time, bp.handle, bp.bytes/1024/1024 as piece_mb from v$backup_set bs inner join v$backup_piece bp on bp.set_stamp = bs.set_stamp and bp.set_count = bs.set_count where bs.incremental_level = 0 order by bs.completion_time desc;

Level 1 differential scenario:

Backup incremental level 1 as compressed backupset database tag 'daily_l1_diff';

How it works:

RMAN backs up the blocks changed since the most recent level 0 or level 1 backup. Every day's backup is usually smaller but recovery may require multiple level 1 backups.

List results:

list backup tag 'daily_l1_diff';

Level 1 cumulative scenario:

backup incremental level 1 cumulative as compressed backupset database tag 'weekly_l1_cumu';

How it works:

RMAN backs up the blocks changed since the most recent level 0 backup. This backup is larger than differential level 1 but recovery generally larger but recovery needs only the last level 0 backup and the most latest level 1 backup.

List results:

list backup tag 'daily_l1_cumu';

Differential vs cumulative:

+-----------------+------------------------------------+-----------------------------------+
| Feature         | Differential Backup                | Cumulative Backup                 |
+-----------------+----------------------------------- +-----------------------------------+
| Backup size     | Usually smaller                    | Usually larger                    |
| Backup time     | Usually shorter                    | Usually longer                    |
| Recovery chain  | May require several level 1 backups| Requires latest cumulative level 1|
| Recovery time   | Potentially longer                 | Potentially shorter               |
+-----------------+------------------------------------+-----------------------------------+

Image copy scenario:

Create image copy:

backup as copy database tag 'db_image_copy';

List image copies:

list copy of database;

Incrementally updated copy pattern:

List and verify backup results:

Backup summary:

list backup summary;

Database backups;

list backup of database;

Incremental backups:

list backup of database completed after 'sysdate -7';

Archived log backup:

list backup of archivelog all;

Backup by tag:

List backup tag 'weekly_l0'; List backup tag 'weekly_l1_diff'; List backup tag 'weekly_l1_cumu';

Backup piece details:

Select handle, status, device_type, bytes/1024/1024 as size_mb, start_time, completion_time, compressed, tag from v$backup_piece order by completion_time desc fetch first 50 rows only;

Datafile backup SCNs:

Select file#, incremental_level, checkpoint_change#, checkpoint_time, completion_time, blocks, datafile_blocks from v$backup_datafile order by completion_time desc, file#;

Monitor backup progress:

RMAN long operations:

Select sid, serial#, opname, target, sofar, totalwork, units, round(sofar*100/nullif(totalwork,0),2 as pct_done, elapsed_seconds, time_remaining from v$session_longops where opname like 'RMAN%' and totalwork > 0 and sofar <> totalwork order by start_time;

Backup Job throughput:

Select session_key, input_type, status, start_time, end_time, elapsed_seconds, input_bytes_display, output_bytes_display, input_bytes_per_sec_display, output_bytes_per_sec_display, compression_ratio from v$rman_backup_job_details order by start_time desc fetch first 20 rows only;

Current RMAN status;

Select session_recid, operation, status, start_time, end_time, object_type, output_bytes from v$rman_status where status like 'RUNNING%' order by start_time;

Validate Restore readiness:

Restore preview:

Restore database preview summary;

restore validate:

restore database validate;

---

Validate logical blocks:

Backup validate check logical database;

---

---

---

Validate control file and spfile:

Restore controlfile validate: Restore spfile validate

Check corruption:

Select file#, block#, blocks, corruption_change#, corruption_type from v$database_block_corruption order by file#, block#;

Check need for backup:

Report need backup recovery window of 7 days;

Example weekly backup strategy:

+-----------------+--------------------------+--------------------------------------------------------+
| DAY             | BACKUP                   | PURPOSE                                                |
+-----------------+--------------------------+--------------------------------------------------------+
| Sunday          | Incremental level 0      | Weekly baseline                                        |
+-----------------+--------------------------+--------------------------------------------------------+
| Monday          | Level 1 differential     | Changes since sunday level 0                           |
+-----------------+--------------------------+--------------------------------------------------------+
| Tuesday         | Level 1 differential     | Changes since monday level 1                           |
+-----------------+--------------------------+--------------------------------------------------------+
| Wednesday       | Level 1 cumulative       | Changes since sunday level 0; shorter recovery chain   |
+-----------------+--------------------------+--------------------------------------------------------+ 
| Thursday        | Level 1 differential     | Changes since wednesday                                |
+-----------------+--------------------------+--------------------------------------------------------+
| Friday          | Level 1 differential     | Changes since thursday                                 |
+-----------------+--------------------------+--------------------------------------------------------+
| Saturday        | Level 1 cumulative       | Latest cumulative before next level 0                  |
+-----------------+--------------------------+--------------------------------------------------------+
| Every 4-6 hours | Archive logs             | Reduce data loss exposure.                             |
+-----------------+--------------------------+--------------------------------------------------------+

The reason for this weekly arrangment:

Sunday is level 0 (level 0 is the same for both differential and cumulative) which is full backup datawise.

Monday we can take level 1 differential backup,

Tuesday we can take another level 1 differtial backup (as if Mondays backup got deleted there will no no data loss as that level 1 backup will record all changes happened after Sunday's level 0.

Wednesday we will take a level 1 cumulative, as in Wednesday's case if Mondays data got deleted, but Tuesday's remain intact then, Monday's changes will not be backed up again as differential level 1 backup only record the changes takes place after most recent level 0 and level 1 backup. This will break the chain. So we take cumulative level 1 backup here which always backups all the changes happened after last level 0, i,e after sunday.

Thursday we take another level 1 differential as we already have the cumulative level 1 backup in Wednesday.

Friday we also take a differential level 1 backup again as there will no risk of breaking the chain is Thursday's backup got deleted.

Saturday we will take another level 1 cumulative backup for the same reason as Wednesday.

Example commands:

----Sunday
Backup incremental level 0 database tag 'weekly_l0';

----Monday, Tuesday, Thursday, Friday
Backup incremental level 1 database tag 'weekly_l1_diff';

----Wednesday, Saturday
Backup incremental level 1 cumulative database tag 'weekly_l1_cumu';

-----Every 4-6 hours 
Backup archivelog all not backed up 1 times;

Troubleshooting scenarios:

Level 1 runs without level 0:

RMAN may create a level 0 equivalant baseline bahavior depending on circumstances, but the strategy should explicitly schedule a level 0.

list backup of database;

Backup destination full:

host df -h /u05/rman/ host df -i /u05/rman/

report obsolete; crosscheck backup; list expired backup;

Backup piece missing:

crosscheck backup; list expired backup;

Restore mount or file access before deleting metadata if the backup is still required.

Incremental backup too large: (?)

Possible causes:

• High changed block workload

• Block changed tracking dissabled or unavailable

• Large batch operations

• Level 0 too old

• Nologging opeartions requiring additional protection.

Change block change tracking:

select status, filename, bytes from v$block_change_tracking;

Enable block change tracking:

alter database enable block change tracking using file '/u01/app/oracle/oradata/PRODUCTION/control01.ctl';

---

Disable block change tracking:

alter database disable block change tracking;

Restore validate fails:

list backup summary;
crosscheck backup;
restore database preview;
restore database validate;

Production safe backup checklist:

1. Confirm database, host, role, log_mode, FRA and destination.

2. Review show all and retention policy

3. Confirm control file autobackup and SPFILE protection.

4. Validate free space and mount availability.

5. Check recent backup failures and expired peices.

6. Confirm data gaurd archived log deletion requirements.

7. Use tags and timestamped logs.

Pre backup commands:

show all;
crosscheck backup;
crosscheck archivelog all;
list backup summary;
report obsolete;

Post backup commands:

list backup summary;
list backup of database;
list backup of archivelog all;
restore database preview summary;
restore database validate;

SQL post backup check:

Select input_type, status, start_time, end_time, elapsed_seconds, input_bytes_display, output_bytes_display, compression_ratio from v$rman_backup_job_details order by start_time desc fetch first 10 rows only;

---