backup policy

[Arsalan]

Sir,

I made rman script for production database kindly check it, does it  right or have some fault ,if need

kindly bring some change on it.

Note:
1)   backup incremental level 0  Run on Wednesday and sunday at 11 pm and

2)   backup incremental level 1 Run on monday tuesday thursday friday saturday every day 11 pm



RMAN> show all;
RMAN configuration parameters for database with db_unique_name orcla are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\APP\ARSLA\FLASH_RECOVERY_AREA\ORCLA\BAKCONTROLFILE\%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\ARSLA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCLA.ORA'; # default
---------------------------------------------------------------------------------------------------------------------

RUN {
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
BACKUP INCREMENTAL LEVEL 0 FILESPERSET 1 FORMAT 'DB_FULL_BACKUP_%D_%M_%Y_%U' DATABASE INCLUDE CURRENT CONTROLFILE TAG 'DB_FULL_BACKUP';
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}
-------------------------------Incremental level 1 backup
RUN
 {
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
BACKUP INCREMENTAL LEVEL 1 FILESPERSET 1 FORMAT 'DB_INCR_BACKUP_%D_%M_%Y_%U' DATABASE INCLUDE CURRENT CONTROLFILE TAG 'DB_INCR_BACKUP';
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}

Note:
1)   backup incremental level 0  Run on Wednesday and sunday at 11 pm and

2)   backup incremental level 1 Run on monday tuesday thursday friday saturday every day 11 pm

[ErmanArslansOracleBlog]

Hi Arsala,

Seems okay.

5 things, I can recommend;

1)You should also backup the archivelogs. You can use PLUS ARCHIVELOG argument for that.

2) Your script and rman configurations are ready for deleting the obsolete backup, but as for archivelogs (not back up archivelogs, I mean), you have the delete archivelog commands in your script. This is acceptable
However, you can always consider to use delete input command as well.. (you are backing up the database everyday, so you can delete the archivelogs while you are generating archivelog backups)
Ex: backup database plus archivelog delete all input;

3) connect to a catalog (if you have one)

4)consider more parallelism, if your resources are available. (ofcourse test it, it can increase the cpu usage)

5)consider backup compression, if you have space problems. (ofcourse test it, it can increase the backup duration and increase the cpu usage)

[Arsalan]

Sir,

I changed the script so kindly recheck if their any mistake


Thanks in advance

---Incremental level 0 backup

RUN {
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUPSET DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
BACKUP INCREMENTAL LEVEL 0 FILESPERSET 1 DATABASE PLUS ARCHIVELOG FORMAT 'C:\app\arsla\flash_recovery_area\orcla\ARCHIVEBK\arch_%d_%T_%s_%t'
DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
DELETE BACKUP COMPLETED BEFORE 'SYSDATE -7';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}

-------------------------------Incremental level 1 backup
RUN
 {
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUPSET DEVICE TYPE DISK;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
BACKUP INCREMENTAL LEVEL 1 FILESPERSET 1 DATABASE PLUS ARCHIVELOG FORMAT 'C:\app\arsla\flash_recovery_area\orcla\ARCHIVEBK\arch_%d_%T_%s_%t'
DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
DELETE BACKUP COMPLETED BEFORE 'SYSDATE -7';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}

[ErmanArslansOracleBlog]

Why do you have default retention policy of 7 days and DELETE BACKUP COMPLETED BEFORE 'SYSDATE -7';
 at the same time? What will happen if your level 0 backups will encounter errors 2 times.
Consider a scenario.

Sunday Level -> success
Wednesday Level 0 -> error
Next Sunday Level 0 -> error
----after 7-8 days --
Next Monday ->  Level1 will delete your Successful Level0. You will be without any Level 0 backups.
You should consider using the default retention policy.

You want to have a recovery windows of 7 days. Then let rman decide what to delete.
You already have "DELETE NOPROMPT OBSOLETE; " there in your script.

Check the command line by line and see if it suits your purpose.
update me if you any specific questions.

[Arsalan]

Hi Arsalan,

the rman script does not delete obsolete backup which are out of retention policy,

if i write the below script then it will delete obsolete backup

why they are not delete obsolete backup kindly guide me


 DELETE BACKUP COMPLETED BEFORE 'SYSDATE -7';

[ErmanArslansOracleBlog]

You already have -> RECOVERY WINDOW OF 7 DAYS in your RMAN config.
So, when you issue rman delete obsolete command, it will delete the backups that are not needed for satisfying the recovery window of 7 days.

When we use recovery window, the point of recovery value is calculated according to the current date and the the days that we configure in the retention policy.

So, you already have delete obsolete command in your script and you have a retention policy configured.

Next time, when asking these kinds of questions, tell me your complete purpose. If your purpose is to delete backups completed 7 days ago no matter what (without thinking about the point of recovery), then your script is ok.