No question at this time
DBA Top 10
1 A. Kavsek 12000
2 M. Cadot 7900
3 B. Vroman 5700
4 P. Wisse 4500
5 J. PĂ©ran 2000
6 . Lauri 1000
7 T. P 300
7 D. Johnson 300
7 R. Pattyn 300
10 J. B 200
10 J. Van Der Steen 200
10 G. Muijrers 200
10 R. Chandran 200
10 A. Ismail 200
10 G. Am77 200
10 B. Bartben 200
10 N. Abdul Rahman 200
10 J. Akhtar 200
10 G. Graham 200
10 D. Ramakrishna 200
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48484
Total active users1453
Act. users last 24h2
Act. users last hour0
Registered user hits last week97
Registered user hits last month634
Go up

RMAN backups
Next thread: auto upgrade from 11.2.0.4 to 18.3.0.0
Prev thread: RU PATCH ON RAC ENV

Message Score Author Date
Hi I have a query regarding RMAN backups, killi...... James Alcroft Jan 16, 2020, 10:20
Sorry, missed a file in my size for the smaller ba...... James Alcroft Jan 16, 2020, 10:23
Hi James, first one question, could you please ...... Jan Schnackenberg Jan 16, 2020, 12:06
HI apologies, looks like I did a bit too much c...... James Alcroft Jan 16, 2020, 12:19
That wasn't in pre-tags, but at least readable. ...... Jan Schnackenberg Jan 16, 2020, 13:01
Hi There are no database changes, only data. ...... James Alcroft Jan 16, 2020, 13:41
Hello James, this is not really a reply to your...... Bruno Vroman Jan 17, 2020, 09:53

Follow up by mail Click here


Subject: RMAN backups
Author: James Alcroft, United Kingdom
Date: Jan 16, 2020, 10:20, 38 days ago
Os info: Win 2012
Oracle info: 12.2
Message: Hi

I have a query regarding RMAN backups, killing my backup plan.

My level 0 script is as follows (there is more, taken out the crosschecking and deleting of expired backups)

run {
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
incremental level 0
filesperset 5
database include current controlfile
format 'e:\backups\db\DB_%d_%I_%T_%s.bak';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
archivelog all
format 'e:\backups\db\ARC_%d_%I_%T_%s.arc' delete input;
}
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
tag PV247USP_20200116_1
archivelog all
format 'e:\backups\PV247USP\ARC_%d_%I_%T_%s.arc' delete input;
}

My level 1 script is exactly the same, but the obvious change of 'incremental level 1'.

When RMAN completes, all backup files are zipped up and moved to a file share.

Level 0 backup runs over the weekend. Last Saturday's backup zip files was 521Gb.

We run archive only backups on weekdays between 06:00 and 22:00. The total of Monday's archive only backup is 1.4Gb. Tuesday's archive backups totals 605Mb.

Level 1 backup is supposed to run at 1am every Tuesday to Friday. Tuesday's level 1 backup zip files comes to 527Gb.

I would expect this to be in the region of the total amount of my archive only backups, give or take a gb or 2. But this looks like another full backup as it is larger than my level 0 on Saturday.

On another database, which is smaller, the level 0 is 8Gb, Monday's archive logs is 357Mb and the level 1 is 800Mb. A bit closer to what i am expecting to see.

Can anyone explain why my level 1 backup is so big when it should only be the changes since the last level 0 or 1, and clearly it isn't doing that.

Many thanks.

James
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: James Alcroft, United Kingdom
Date: Jan 16, 2020, 10:23, 38 days ago
Message: Sorry, missed a file in my size for the smaller backup. The level 0 size if 40Gb, not 8Gb.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: Jan Schnackenberg, Germany
Date: Jan 16, 2020, 12:06, 38 days ago
Message: Hi James,

first one question, could you please check the script you posted and post the correct script including it in pre-tags (see formatting help at the top of the "reply"-page).

The script you posted looks... wrong. ;)

Apart from that:
If RMAN determines (for whatever reason, that would have to be looked into) that there is no viable LEVEL 0 backup as base for the LEVEL 1 backup, it will implicitly do a LEVEL 0 backup. The RMAN-views won't show this, but it will happen (I'm not sure, but I think it will show in the RMAN logs).

So, since your LEVEL 1 backup is bigger than your LEVEL 0 backup I my guess is the same as yours, that RMAN actually did a LEVEL 0 backup.

Is it possible, that you changed something about the database in between? COMPATIBLE parameter, for example?

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: James Alcroft, United Kingdom
Date: Jan 16, 2020, 12:19, 38 days ago
Message: HI

apologies, looks like I did a bit too much copy and paste for the script. Below is the correct script

run {
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
incremental level 0
filesperset 5
database include current controlfile
format 'e:\backups\db\DB_%d_%I_%T_%s.bak';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
backup
archivelog all
format 'e:\backups\db\ARC_%d_%I_%T_%s.arc' delete input;
}

The RMAN backup is controlled via an application that we have written which creates the backup script and runs it. It is the same application on all my database servers, but this server is the only one which is giving me an issue.

James
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: Jan Schnackenberg, Germany
Date: Jan 16, 2020, 13:01, 38 days ago
Message: That wasn't in pre-tags, but at least readable.

Basically this looks fine, as you will have expected.

What about my question regarding changes to the database?

Did this issue occur once? Or more often?

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: James Alcroft, United Kingdom
Date: Jan 16, 2020, 13:41, 38 days ago
Message: Hi

There are no database changes, only data.

I have kicked off another level 1 backup today, and we are currently at 66Gb and still going.

The last level 1 backup completed yesterday afternoon. The total archive only backups for Tuesday and Wednesday is a total of 1.9Gb

So it looks like the level 1 running now is again a level 0.

James
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: RMAN backups
Author: Bruno Vroman, Belgium
Date: Jan 17, 2020, 09:53, 37 days ago
Message: Hello James,

this is not really a reply to your question but just a few thoughts.

-1- I suggest you to use "increment level 1 cumulative" instead of simply "incremental level 1" (in fact this is "more work" for the backup, but the reward is when we will have to restore the database...)

-2- (and this can counterbalance a lot the "cumulative" I mentioned in "-1-" ;-) Have a look at Block Change Tracking if you don't use this yet... Amazing.

-3- You might also add "backup spfile"

-4- Include %U in format to be sure of uniqueness.

Best regards,

Bruno Vroman.
P.S.: https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=94446
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here