Home » RDBMS Server » Backup & Recovery » RMAN backups increase in size and don't delete (Oracle11g, RMAN, RedHat Linux)
RMAN backups increase in size and don't delete [message #618189] Wed, 09 July 2014 03:15 Go to next message
joeb3725
Messages: 2
Registered: July 2014
Junior Member

Hi, Sorry if this is a basic question, I have little experience of RMAN backups I'm afraid.
I'm having some problems with my RMAN backups.
The RMAN script and backup process was setup by the person who originally installed the DB.
It is supposed to do an incremental backup daily at 12:05 am and 12:05 pm and a full backup every three days

The backups get consistently get larger over time, often being much larger than my datafiles. Even with very little done ~100Mb is added each day (see table below).

If a full backup is being run every 3 days then why are the incremental backups being kept and things keep getting larger and larger overtime? Surely every 3 days the incremental backups should be reset and the backup folders reach a steady state size?

Some details below

Backup folder size and date
Date Size
22-Jun 250Mb
23-Jun 161Mb
24-Jun 368Mb
25-Jun 450Mb
26-Jun 500Mb
27-Jun 1Gb
28-Jun 1.4Gb
29-Jun 1.3Gb
30-Jun 1.5Gb
01-Jul 1.62Gb
02-Jul 1.7Gb
03-Jul 1.8Gb
04-Jul 1.9Gb
05-Jul 2.3Gb
06-Jul 2.55Gb
07-Jul 2.64Gb
08-Jul 3.2Gb

There can be lots of row deletes and inserts on the database but overall the number of rows stays about then same.

If I do
SELECT * FROM V$RECOVERY_FILE_DEST
It shows
Name - /u02/fast_recovery_area
Space limit 42949672960
Space used 32024304640
Space reclaimable 1786222080
Number_of_files 177

If I do

export ORACLE_SID=CS2DB
rman target sys/sys
delete backup completed before 'sysdate -7';

It deletes all backups older than 7 days except the first one on 2014_06_22 (which was then deleted the next time RMAN was run).

SELECT * FROM V$RECOVERY_FILE_DEST
After a delete sysdate-7 shows..

Space limit 42949672960
Space used 23694853120
Space reclaimable 1786222080

My rman backup script is

#!/bin/sh
. /home/oracle11/.bash_profile
CURR_DATE=`date +%u`
$ORACLE_HOME/bin/rman target / nocatalog log=/home/oracle11/scripts/log/rman_alo_$CURR_DATE.log << EOF
RUN {
allocate channel ch1 type disk;
RECOVER COPY OF DATABASE WITH TAG "fra_incr_backup_3d" UNTIL TIME 'SYSDATE - 3';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG "fra_incr_backup_3d" DATABASE plus archivelog;
delete noprompt obsolete;
release channel ch1;
}
exit;
EOF

The RMAN retention policy is 7 days.

If a full backup is being run every 3 days then why are the incremental backups being kept and things keep getting larger and larger overtime? Surely every 3 days the incremental should reset and the backup folder stay in asteady state size?
Also why aren't backups being deleted every 7 days?

Thanks

Joe
Re: RMAN backups increase in size and don't delete [message #618444 is a reply to message #618189] Fri, 11 July 2014 15:07 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
joeb3725 wrote on Wed, 09 July 2014 04:15

Hi,
. . . E t c . . .
My rman backup script is

#!/bin/sh
. /home/oracle11/.bash_profile
CURR_DATE=`date +%u`
$ORACLE_HOME/bin/rman target / nocatalog log=/home/oracle11/scripts/log/rman_alo_$CURR_DATE.log << EOF
RUN {
allocate channel ch1 type disk;
RECOVER COPY OF DATABASE WITH TAG "fra_incr_backup_3d" UNTIL TIME 'SYSDATE - 3';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG "fra_incr_backup_3d" DATABASE plus archivelog;
delete noprompt obsolete;
release channel ch1;
}
exit;
EOF
. . . E t c . . .

It seems you have a backup "copy" of your database and the "RECOVER COPY" is applying the last incremental backup to that copy.

Then you take a "current" incremental backup.

The incremental backups applied to the copy can be deleted.

[Updated on: Sat, 12 July 2014 01:30] by Moderator

Report message to a moderator

Re: RMAN backups increase in size and don't delete [message #618445 is a reply to message #618444] Fri, 11 July 2014 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: RMAN backups increase in size and don't delete [message #618682 is a reply to message #618445] Mon, 14 July 2014 16:51 Go to previous messageGo to next message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
I can see why you are questioning what is occuring. This is very difficult to figure out and would suggest you get a calendar out and try to track it. Maybe visualizing it will help you understand.

A couple of comments:

1) A "FULL BACKUP" is not part of an incremental strategy. It has to be an incremental level 0 (which is automatically done if one does not exist).

2) RMAN retention policy specifies which backups must be retained to meet your data recovery requirements (which is very different than backup dates).

So take for example if you truly do "FULL BACKUP"s every 3 days. You must maintain 4 copies to be able to recover 7 days ago.

July 1 - FULL backup
July 4 - FULL backup
July 7 - FULL backup (What if you need to recover to July 2 (5 days ago) which backup can you use? <b>July 1</b>
July 10 - FULL backup (What if you need to recover to July 3 (7 days ago) which backup can you use? <b>July 1</b>
July 13 - FULL BACKUP (What if you need to recover to July 6 (7 days ago) which backup can you use? <b>July 4</b> <i>only now can you delete July 1st backup</i> So you have to keep 4 full backups.

Now that is separate from your incremental backups. I am very interested in how this is working. Generally speaking when using incremental strategies in the manner shown in your backup script you should be using a RETENTION POLICY of 1. So my question is how do you know your retention policy is 7? I don't have any way to test this...and of course it would take me more than a week but here is a guess.

Never the less at a guess

July 1 12:05 am - INCR level 0 (first incremental so defaults to level 0)
July 1 12:05 pm - Nothing to recover INCR level 1
July 2 12:05 am - Nothing to recover INCR level 1
July 2 12:05 pm - Nothing to recover INCR level 1
July 3 12:05 am - Nothing to recover INCR level 1
July 3 12:05 pm - Nothing to recover INCR level 1
July 4 12:05 am - Nothing to recover INCR level 1
July 4 12:05 pm - Nothing to recover INCR level 1
July 5 12:05 am - (recover through to July 1 12:05 pm)INCR level 1 (delete July 1 12:05 pm backup)
July 5 12:05 pm - (recover through to July 2 12:05 am)INCR level 1 (delete July 2 12:05 am backup)

Now this may be wrong....because I do not know if you truly have RETENTION POLICY OF 7 days if this is what is occuring. Because if the RETENTION POLICY OF 7 Days "overrides" the RECOVER COPY ... UNTIL command then you will have 3 more days at least of incremental backups before deletions start occuring. You will need to look at your log files to figure that one out.

Regards
Tim



Re: RMAN backups increase in size and don't delete [message #618907 is a reply to message #618682] Wed, 16 July 2014 07:43 Go to previous message
joeb3725
Messages: 2
Registered: July 2014
Junior Member
Thanks Tim,

This makes thing clearer for me (I think!). I really appreciate you taking the time to respond with that level of detail.
I believe the retention policy is 7 as a show all in RMAN returns:-
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

If I follow what you are saying then I would expect backups to be kept for at least 11 days before they start to auto delete as obsolete (7 + 4).
If I change the retention policy to 1 then this would drop to 4 days (possibly 5 days as 4+1).
Interestingly my backup sizes since the 13th July (11 days since the oldest available backup!) have dropped to ~350Mb per day - from a peak on nearly 4Gb. This is the size i would expect.

What I still don't understand is why backups beyond 11 days are still being kept. Currently as of today 16th July, the oldest available backup is the 2nd July (I've check with report obsolete and its not obsolete).

That's 3 days longer than I would expect if I understand correctly. Any idea why?

LKBrwn_DBA - thank you for you suggestion, although from what I can see this syntax / method is what Oracle suggests for RMAN. How would I delete the incremental backups to the copy?

Blackswan - my apologies. Details below
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Red Hat Enterprise Linux Server release 5.10 (Tikanga)

Thanks all and sorry if these are basic questions..

Joe
Previous Topic: Restore table data till specified date
Next Topic: Restore ControlFile to new host
Goto Forum:
  


Current Time: Thu Mar 28 16:16:39 CDT 2024