Oracle RMAN Backup Optimization ON and backup archivelog command.

TL;DR: BACKUP OPTIMIZATION ON acts as a NOT BACKED UP 1 TIMES for the BACKUP ARCHIVELOG ALL command.

Franck Pachot
4 min readJul 30, 2019

I always mention “not backed up n times” in my “backup archive log” commands because I think it makes sense to precise exactly how many copies of archived log we want to have available to restore from each backup device

I often configure “backup optimization on” when thinking about the tablespaces I put read-only so that they are not backed up each time(given that there is no suppression of old backups that are unknown to RMAN).

But I’ve never realized that BACKUP OPTIMIZATION and NOT BACKED UP actually both work on datafiles, archivelogs, and backupsets. And then the following is redundant:

configure backup optimization on;
backup archivelog all not backed up 1 times;

It is not a problem at all and I prefer to keep this redundancy, especially because of the name: BACKUP OPTIMIZATION looks like something which concerns only the performance, but NOT BACKED UP 1 TIMES is explicit about the availability.

As I did a quick test to verify the behavior, I share it here. Of course, all this is documented in backup optimization and notBackedUpSpec.

I start to delete all archived logs, and backups of them, in a lab database, and set the default for BACKUP OPTIMIZATION:

set nocfau;
delete noprompt backup of archivelog all;
delete noprompt archivelog all;
configure backup optimization clear;
show backup optimization;

Note that I’ve disabled controlfile autobackup (with NOCFAU) to keep the screenshots small.

I run two backups of archivelog:

backup archivelog all;
backup archivelog all;

I have two copies of backups for sequence 280:

list backup of archivelog all;

This is not usually what we want. We try to backup archivelogs frequently, for availability reasons, but also want to keep backup storage low, for retention reasons.

Not Backed Up n Times

If I don’t want a 3rd copy:

backup archivelog all not backed up 2 times;

The sequence 280 has been skipped because “already backed up” 2 times. Sequence 281 has only one backup and 282 had no backup yet.

Usually, one backup is sufficient, and this is what I schedule:

backup archivelog all not backed up 1 times;

Backup Optimization

The point of this post is to show what happens when BACKUP OPTIMIZATION is ON and we do not specify “not backed up n times”

configure backup optimization on;
backup archivelog all;

Here, the sequences that already have been backed up one time have been skipped. Only the last one (generated by “backup archivelog all” which always archives the current log) is backed up.

Archivelog range

When I don’t want to archive the current log before, I run with an UNTIL TIME ’SYSDATE’:

backup archivelog until time 'sysdate';
backup archivelog until time 'sysdate';

In this case, even with BACKUP OPTIMIZATION to ON all archived logs are backed-up again. If I don’t want to, I need to explicitly mention it:

backup archivelog until time 'sysdate' not backed up 1 times;

This is because BACKUP OPTIMIZATION ON is applied to BACKUP ARCHIVELOG only when no range is provided (FROM/UNTIL/BETWEEN SCN/TIME/SEQUENCE). This only with ALL (or LIKE).

--

--

Franck Pachot

Developer Advocate for YugabyteDB (Open-Source, PostgreSQL-compatible Distributed SQL Database. Oracle Certified Master and AWS Data Hero.