In Oracle Database backup operations, are you concerned that “I want to shorten recovery time, but daily full backups place too much load on storage”? Incremental Update Backups is an efficient method that utilizes Recovery Manager (RMAN) features to combine image copies with incremental backups, allowing you to maintain a constantly up-to-date full backup.
- Mechanism and Benefits of Incremental Update Backups
- Setup Procedures for Incremental Update Backups
- Executing Restore and Recovery (In Case of Failure)
- Operation Example for Incremental Update Backups
- Best Practices
- Troubleshooting: Representative ORA Errors
- Operational and Security Considerations
- FAQ: Frequently Asked Questions
- Summary
Mechanism and Benefits of Incremental Update Backups
Normally, recovery from incremental backups follows the procedure of “Restore full backup + Apply multiple incremental backups,” which takes time.
Incremental Update Backups significantly skip steps during recovery by applying increments (ROLLING FORWARD) to the image copy in advance.
Key Benefits
- Immediate Recovery (Reduced MTTR): Since the image copy is already up-to-date (or as of the previous day), file transfer time is unnecessary.
- Storage Optimization: Instead of creating a full backup every time, operations can be managed with only one generation of data plus incremental data.
- Reduced Backup Time: Read and write loads are limited only to changed blocks.
Setup Procedures for Incremental Update Backups
1. Taking Incremental Backups (Performed Regularly)
Take an Incremental Level 1 backup to create the backup for updates.
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INCR_UPDATE' DATABASE;
2. Applying Increments to Image Copy (ROLLING FORWARD)
Apply the acquired incremental backup to the image copy to keep it in a constant state of being up-to-date.
RMAN> RECOVER COPY OF DATABASE WITH TAG 'INCR_UPDATE';
By executing these procedures regularly, the latest image copy is maintained without taking a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'INCR_UPDATE' DATABASE;
Starting backup at 25-02-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/V19/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/V19/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/V19/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/V19/rctbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/V19/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-02-20
channel ORA_DISK_1: finished piece 1 at 25-02-20
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/173ia8cn_39_1_1 tag=INCR_UPDATE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 25-02-20
Starting Control File and SPFILE Autobackup at 25-02-20
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250220-09 comment=NONE
Finished Control File and SPFILE Autobackup at 25-02-20
RMAN> RECOVER COPY OF DATABASE WITH TAG 'INCR_UPDATE';
Starting recover at 25-02-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copy(ies) to restore
datafile copy file number=00001 name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-SYSTEM_FNO-1_113ia8ah
datafile copy file number=00003 name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-SYSAUX_FNO-3_123ia8ao
datafile copy file number=00004 name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-UNDOTBS1_FNO-4_133ia8ar
datafile copy file number=00005 name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-RCTBS_FNO-5_143ia8au
datafile copy file number=00007 name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-USERS_FNO-7_153ia8b1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/173ia8cn_39_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/173ia8cn_39_1_1 tag=INCR_UPDATE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished recover at 25-02-20
Starting Control File and SPFILE Autobackup at 25-02-20
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2957249400-20250220-0a comment=NONE
Finished Control File and SPFILE Autobackup at 25-02-20
RMAN>
Executing Restore and Recovery (In Case of Failure)
In the event of a failure, perform restore and recovery quickly using the image copy.
RMAN> shutdown immediate
RMAN> startup mount;
RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;
RMAN> alter database open;
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1543500120 bytes
Fixed Size 8925528 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7847936 bytes
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-SYSTEM_FNO-1_113ia8ah"
datafile 3 switched to datafile copy "/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-SYSAUX_FNO-3_123ia8ao"
datafile 4 switched to datafile copy "/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-UNDOTBS1_FNO-4_133ia8ar"
datafile 5 switched to datafile copy "/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-RCTBS_FNO-5_143ia8au"
datafile 7 switched to datafile copy "/u01/app/oracle/product/19.0.0/dbhome_1/dbs/data_D-V19_I-2957249400_TS-USERS_FNO-7_153ia8b1"
RMAN> RECOVER DATABASE;
Starting recover at 25-02-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
Starting media recovery
Media recovery complete, elapsed time: 00:00:01
Finished recover at 25-02-20
RMAN> alter database open;
Statement processed
RMAN> select status from v$instance;
STATUS
------------
OPEN
Operation Example for Incremental Update Backups
The following is an example schedule for effectively operating Incremental Update Backups.
| Day | Backup Content |
| Sunday | Full backup (Create image copy) |
| Monday | Apply Incremental Level 1 (Update image copy) |
| Tuesday | Apply Incremental Level 1 (Update image copy) |
| Wednesday | Apply Incremental Level 1 (Update image copy) |
| Thursday | Apply Incremental Level 1 (Update image copy) |
| Friday | Apply Incremental Level 1 (Update image copy) |
| Saturday | Apply Incremental Level 1 (Update image copy) |
By following this operation, the latest full backup is maintained, and recovery time can be significantly shortened.
Best Practices
- Ensure Adequate Storage
- Secure sufficient disk space at the destination for image copies.
- Perform appropriate disk management considering the retention period of incremental backups.
- Implement Automation
- Automate the application of incremental backups using CRON or DBMS_SCHEDULER.
- Utilize RMAN scripts to automatically execute periodic backups and applications.
- Regular Restore Tests
- Periodically restore backup data and perform recovery verification to prepare for failures.
- Use the
RESTORE DATABASE VALIDATEcommand to check backup integrity.
RMAN> RESTORE DATABASE VALIDATE; - ARCHIVELOG Management
- Operation in ARCHIVELOG mode is mandatory for Incremental Update Backups.
- Manage archived logs appropriately to prevent storage overload.
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
Troubleshooting: Representative ORA Errors
| Error Code | Cause | Action / Verification |
| ORA-19625 | Backup file not found | Confirm file existence with LIST COPY. If lost, take an image copy again. |
| ORA-19504 | Insufficient disk space at write destination | Check storage remaining. Delete old incremental backups or logs. |
| ORA-00257 | ARCHIVELOG area full | After applying increments, delete unnecessary archived logs with DELETE ARCHIVELOG. |
Operational and Security Considerations
- Consideration for Reverting: After switching to the backup destination with the
SWITCHcommand, the DB operates on the backup disk. Since there is a risk of performance degradation, standardize the procedure to move data back to the original high-speed storage after recovery. - Thorough Tag Management: If
WITH TAGis not specified, it may be confused with other backups, causing updates to be applied incorrectly. - Block Change Tracking: To speed up incremental backups, it is strongly recommended to configure
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;.
FAQ: Frequently Asked Questions
Q1. What is the difference between Incremental Update Backups and regular incremental backups?
A1. Regular incremental backups require applying “Full + Incr 1 + Incr 2…” in order during recovery, but Incremental Update pre-applies updates to the copy, making recovery overwhelmingly faster.
Q2. How much storage capacity is required?
A2. At a minimum, the total size of the database (for the image copy) + the daily change amount (for incremental backups) is required.
Q3. Can this be used with Standard Edition 2 (SE2)?
A3. Yes, it is a standard RMAN feature available in both SE2 and EE editions.
Summary
- Incremental Update Backups is a method combining image copies and incremental application (Recover).
- It can dramatically shorten MTTR (Mean Time To Repair) and is ideal for large-scale DB operations.
- By utilizing the SWITCH command, high-speed restore without file copying is possible.
- Use of Block Change Tracking is recommended for operations.
This article is explained for Oracle Database 19c (screens and default values may differ for other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント