aix disk utilization of oracle databases
Bill Wohler
we just backed up an entire file system of oracle database files on
ibm's aix, recreated the filesystem (to make it smaller), and restored the
files. after this operation, the disk usage decreased by one
half!!!
having worked with dbm files, i would have expected the disk usage
to go *up* upon the restore when the holes in the database files got
filled in.
this raises questions on the disk organization of oracle database
files, or perhaps possible poor disk space management on the part of aix.
can anyone comment on why the halving of disk usage might have happened?
Bill Wohler <woh...@sap-ag.de> <sapwdf!wohler>
Heidelberg Red Barons Ultimate Frisbee Team
Herbert van den Bergh
The database files of the Oracle RDBMS are pre-allocated. This means
that on creation of the database file, every block is written to to
make sure the disk doesn't fill up before the database file does.
The backup/restore commands will try to "sparse" a file when there are
blocks of zeroes in them, even if the file wasn't sparse on disk.
Unused blocks in the Oracle database files contain zeroes, so on restore
of such a file, the blocks are no longer allocated. This is not a good
situation, since the rdbms will run into troubles when the disk fills up
and there aren't enough free blocks to write to the datafile. A simple
program that reads every block and writeds it back in the same place
will assure the file isn't sparse anymore.
I wonder how dbm handles full disks.
Jan Diesel
>
> I wonder how dbm handles full disks.
>
-------------------------------------------------------------------
Jan Diesel j...@rivm.nl
National Institute for Public Health and Environmental Protection
Informatics Service Centre
P.O.Box 1, 3720 BA BILTHOVEN, The Netherlands.
-------------------------------------------------------------------
Bjorn Engsig
|folks,
|
| we just backed up an entire file system of oracle database files on
| ibm's aix, recreated the filesystem (to make it smaller), and restored the
| files. after this operation, the disk usage decreased by one
| half!!!
It will not write all-zero blocks (which is what never-used oracle blocks
are) back upon restore - you end up with sparse files.
Oracle isn't aware of this, it still believes it has all it's blocks
allocated, and you will get a nice surprise when you get a 'disk is full'
error from the database writer or redo log writer.
The most bizarre thing about this backup/restore feature is that it isn't
done to save space on the backup medium, where the all-zero blocks _are_ stored,
whereas the restore program realizes this and doesn't write them.
The cure is to use cpio or tar.
--
Bjorn Engsig - ORACLE Corporation. | Editor of FAQ-list
Internet: ben...@oracle.com, uunet!oracle!bengsig | for comp.unix.aix
Private : bj...@login.dkuug.dk.
Bill Wohler
> we just backed up an entire file system of oracle database files on
> ibm's aix, recreated the filesystem (to make it smaller), and restored the
> files. after this operation, the disk usage decreased by one half!!!
the short answer is that the restore operation cleaned up the
database (like reformatting in dos) which i got by word of mouth.
here are three other answers i received:
From: <kla...@sol.uvic.ca>
In another DBMS package, on a different platform, I notice the same
thing. However, if I repeat the UNLOAD/RELOAD process a **second**
time, I notice a 0% improvement. So, I guess that the UNLOAD/RELOAD
process must have "cleaned up" the heavily-used (many updates/inserts)
DB. For example, if a data-block on disk contains many records,
including two with keys 'B1' and 'B3', and you ask for a record with a
key of 'B2' to be inserted, then either the new record will be
"squeezed" into the same data-block on disk, or else the data-block
will be split; one data-block will contain "all keys
up-to-and-including B1", and the other data-block will contain "key
'B2', key 'B3', and all higher keys which were in the original
data-block". So, each of these two data-blocks will be approximately
50% "full". Thus, a RELOAD may be able to merge the records from one
of these blocks into an adjacent data-block, i.e., blocks containing
keys 'A1' to 'A9', or 'C1' to 'C9'.
Also, I remember some people from FOCUS saying that their DBs should
occasionally be UNLOAD/RELOADed. They gave one example of response
time "before" being on the order of 30 seconds, while "after", it was
sub-second responses!
So, I'm interested in hearing if you notice any improvement from a
**second** UNLOAD/RELOAD.
From: Bjorn Engsig <ben...@dk.oracle.com>
Yes, this is a know feature (I'd call it a bug) with AIX's restore
program. It will not write all-zero blocks (which is what never-used
oracle blocks are) back upon restore - you end up with sparse files.
Oracle isn't aware of this, it still believes it has all it's blocks
allocated, and you will get a nice surprise when you get a 'disk is
full' error from the database writer or redo log writer.
The most bizarre thing about this backup/restore feature is that it
isn't done to save space on the backup medium, where the all-zero
blocks _are_ stored, whereas the restore program realizes this and
doesn't write them.
From: David Natelson <dnat...@us.oracle.com>
A file backup from the OS/S level of AIX will not change the size of
files being used by Tablespaces of Oracle. You are seeing OS/Level
disk fragmentation here. The ability (via SMIT) to easily add more
space to filesystem partitions is nice (on AIX), but it will obviously
fragment your disk. Tablespaces created in Oracle are of a fixed size
from the start.
Bjorn Engsig
|> we just backed up an entire file system of oracle database files on
|> ibm's aix, recreated the filesystem (to make it smaller), and restored the
|> files. after this operation, the disk usage decreased by one half!!!
|
|
|In another DBMS package, on a different platform, I notice the same
|thing. However, if I repeat the UNLOAD/RELOAD process a **second**
|time, I notice a 0% improvement.
backup/restore. The former might do anything to the internal structure
if the files that make up the database, including things like making
the files smaller. The latter doesn't know anything about the DBMS's
use of the files.
|
|From: David Natelson <dnat...@us.oracle.com>
|
|A file backup from the OS/S level of AIX will not change the size of
|files being used by Tablespaces of Oracle. You are seeing OS/Level
|disk fragmentation here.
As I've said a few times, the reason is simply that AIX's restore program
will not allocate all zero blocks.
The following note is for people who don't know what a 'sparse' file is,
so you may want to skip the rest.
Asume you do the following operations in Unix (or AIX)
- open a new file for writing
- write a few (non-zero) bytes.
- seek to position 1 Mb
- write a few (non-zero) bytes.
- close the file.
What you end up with is a file that looks like having a size of just
above 1 Mb, but it really only has two data blocks allocated. Thus,
a file of 1Mb only takes up very little space! As soon as you write
further to the file, it will have more and more blocks allocated.
This is what happens to oracle database files that have not been used
completely (as seen from an Oracle point of view) and then are subject
to AIX backup and restore.
Stephen G. Roseman
:
: As I've said a few times, the reason is simply that AIX's restore program
: will not allocate all zero blocks.
: [portion deleted]
:
: Asume you do the following operations in Unix (or AIX)
:
: - open a new file for writing
: - write a few (non-zero) bytes.
: - seek to position 1 Mb
: - write a few (non-zero) bytes.
: - close the file.
:
: What you end up with is a file that looks like having a size of just
: above 1 Mb, but it really only has two data blocks allocated. Thus,
: a file of 1Mb only takes up very little space! As soon as you write
: further to the file, it will have more and more blocks allocated.
:
: This is what happens to oracle database files that have not been used
: completely (as seen from an Oracle point of view) and then are subject
: to AIX backup and restore.
: --
: Bjorn Engsig - ORACLE Corporation. | Editor of FAQ-list
: Internet: ben...@oracle.com, uunet!oracle!bengsig | for comp.unix.aix
: Private : bj...@login.dkuug.dk.
Questions:
What are the possible effects here? Is it simply that you may run
out of space when Oracle uses "more" space? On reading these "missing" blocks,
does the system return normally with zero-ed data as if the file wasn't sparse,
or can the database become corrupt? (assuming enough space in the filesystem.)
We are using Oracle for several systems here, and the problem
possibilities are obvious. Is there a way to reallocate the missing data if
a reload becomes necessary? Options may be 1) cp'ing and renaming the file
(will it help?), 2) reading all blocks of the file (will read allocate?), or
3) rewriting all blocks of the file (that should work.) before Oracle is
brought up.
Thanks for your advice (before problems appear...)
Steve
----------------------------------------------------------------------------
Steve Roseman
Lehigh University Computing Center
lu...@chili.CC.Lehigh.Edu
--
----------------------------------------------------------------------------
Steve Roseman
Lehigh University Computing Center
lu...@chili.CC.Lehigh.Edu
Herbert van den Bergh
|> Questions:
|>
|> What are the possible effects here? Is it simply that you may run
|> out of space when Oracle uses "more" space? On reading these "missing" blocks,
|> does the system return normally with zero-ed data as if the file wasn't sparse,
|> or can the database become corrupt? (assuming enough space in the filesystem.)
When there's enough space in the file system, no problems will occur.
The read will return normally, with a buffer filled with zeroes.
When writing to a sparse block while the file system is full, the write
will fail. I'm not sure how the RDBMS handles this error.
|> We are using Oracle for several systems here, and the problem
|> possibilities are obvious. Is there a way to reallocate the missing data if
|> a reload becomes necessary? Options may be 1) cp'ing and renaming the file
|> (will it help?), 2) reading all blocks of the file (will read allocate?), or
|> 3) rewriting all blocks of the file (that should work.) before Oracle is
|> brought up.
1) will work. cp isn't as brain damaged as restore. May be a problem for
big data files when there's not enough disk space.
2) doesn't work. read() doesn't change block allocations
3) will work. "dd if=<file> of=<file> skip=1 seek=1" too. The skip and
seek are necessary to avoid truncating the file...
|> Thanks for your advice (before problems appear...)
|> Steve
No thanks (even after problems appear ;-)
--
Herbert van den Bergh
Oracle Europe
hbe...@nl.oracle.com
vijay Raghavan
Thank You.