Oracle database files being backed up via RMAN have thousands of extra bloks being allocated

[Gerald Deinzer]

Why does the allocation of blocks for files being created by RMAN cousume so much space.
I have set the cluster size to 1096.

[Hein van den Heuvel]

It would help if you would indicate exactly which file you are complaining about.

At first I looked at some ALB files, but there is no discrepancy there. The difference of ALQ - EOF being less than the cluster size.

So now I guess you are talking about the DBF files. Those appear to be allocated at 4GB and 3.7GB mostly. The actual usage it smaller, but surely that is an Oracle choice.

There is a VMS mechnisme which will grow files with 'autoextent' and when the file is closed, then it is truncated at actual usage (RMS: FOP=TEF). Oracle does appears not to have used that, nor did it use an RMS SYS$TRUNCATE call.
For a 'normal' DBF file, I unnderstand that.
The application designer selects a pre-allocate size and Oracle honors that.
For backup files that SEEMS pointless.

I would recommend a question/suggestion to Oracle to TRUNCATE backup files either explicitly or implicitly with FOP=TEF on CLOSE. There is no VMS setting that woudl influence this. This is an application (Oracle) choice.

hth,
Hein.

[Hein van den Heuvel]

Oh, I forgot to ask/mention....

Perhaps RMAN has an option where you can set an INITIAL size as well as an EXTENT size for RMAN output? Perhaps it defaults to the DBF file size? If so maybe you can can set the initial size to 1/2 of the dbf size and the extent to something much smaller. With the large cluster size you have, and the usage you have, fragmentationa and extent costs will be minimal anyway.

Hein.

[Gerald Deinzer]

Thank you and I will certainly pose those same questions to our DBA.. By the way, when the RMAN job was running it appears the system had blocked all access to that drive, it took well over a minute to return a directory listing. It also seems to affect the rest of the users as the system appears to hang at times and a monitor cluster shows less than 1/2 of memory being used or perhaps I should query the Oracle folks...
Thanks again.

[Wim Van den Wyngaert · Accepted solution]

Had the same problem with Sybase (and Sybase crashed). Is your high water marking turned on ? Put it off and the file is created directly.

You can also recuperate the diskspace with "set file/trunc".

Wim

[Gerald Deinzer]

I turned off FHW on the volum and the response improves dramatically. Thank you!
Why would file highwater_marking on the volume affect the response and should I do it for the remainder of the Oracle database disks?

I do not know if issuing the set set file/truncate will render these backup files unusable to RMAN or Oracle in the event a restore is needed. I doubt they would but I need to be sure.

[Ian Miller.]

file highwater_marking is a security feature. Security has a cost and is a trade-off. When file highwater_marking is on then extending a file will take longer as the newly allocated blocks are erased. This causes additonal load on the disk.

[Hein van den Heuvel]

Hmm, this warrant investigation and/or a support call to Oracle. I'll ask around if/wehn I run into the right people, and point some folks to this topic.

At the very least Oracle must document the HWM impact and allow a system manager to make a good decision. Maybe it is documented in a release note / VMS specific install guide / metalink ariticle already?

IMHO there is no good reason for HWM to have an effect on backup style file creation, but clearly it does.

Oracle must be doing some sort of random write to the output file to trigger HWM to take action.

Maybe they use a 'Unix compatible' 'SEEK + write" to extent the file instead of a VMS native approach?

For a simple sequential write, HWM has no effect, being only pointer. After a 'random' (Not actually random of course, just not sequential) write the HWM setting instructs teh file system to zero blocks beyond the current EOF in order to prevent data scavenging type security attacks.

Also, yes I also suspect that SET FILE/TRUNC will simply return the unused space, but would not want to recommend doing so without Oracle consent and/or extensive backup + restore testing on a non-production database. Again, with would behoove Oracle to document whether truncating is acceptable or not. Maybe it is documented already?

Actually, set file /truncate is pretty safe considering that the very HWM we are talking about will make sure that any block read beyond the EOF will just return zeroes. Therefor one can always undo this truncate.
So I would do it for _my_ file in this circumstance, but could not recommend it for others. It would have to be your own choice and responsibility.

Regards,
Hein.

[DICTU OpenVMS]

Don't mix up the HWM of Oralce and the HWM of OpenVMS... ;-) They are completly different things. The VMS one means 'erase blocks on allocate', the Oracle one is then 'end of records' (or how do you say that in dutch, eeh english).

[Gerald Deinzer]

This is now a moot point as I found out (finally) that RMAN does not work properly in Oracle 8.1.7.3. I guass I will have to wait until we upgrade Oracle to 9i or 10g.
Thank you everyone for all the help!