Introduction:
Oracle does not recommend nor support the use of 'sparse' files for an Oracle database, except in the very special case of TEMPFILE database files which can be created in Oracle8i onwards. With an increasing number of third party data management tools becoming available it is important to ascertain if any utilities to be used will affect your database files. This is most important with respect to backup and recovery of files. Oracle have seen situations in the past where recovery of backed up data produced 'sparse' database files which then caused serious problems.
What is a Sparse file ?
An Oracle7 data file is filled with blocks of '0's when it is created.
Ie: Space is pre-allocated and filled with zeroes.
An Oracle8 data file is filled with formatted blocks when it is
created, but the middle of these blocks contains all '0's which may
span one or more O/S blocks.
Note: An Oracle8i TEMPFILE is NOT explicitly filled with '0's and
can be created as a sparse file on some platforms. This is
correct behaviour for this form of file.
A sparse file is a file where any 'empty' O/S blocks do not actually
take up a full block but have a marker to show the block is empty.
The operating system then finds a free block for use when the block
is populated with data.
On most systems creating a sparse file is simple. The code below is
an example of how a sparse file could be created:
#include
#include
#define FILESIZE 1048576L /* 1Mb file (Plus EOF tag) */
main()
{
int fd;
if ((fd=open( "sparse_file", O_CREAT|O_RDWR, 0666 ))>=0) {
lseek( fd, FILESIZE, 0 );
write( fd, "EOF", 3 );
close( fd );
}
}
Ie: You just seek past the End-Of-File to a new location and blocks
in between will be 'sparse' blocks in that they take no real space.
So how can an Oracle file become sparse ?
Backup and restore utilities need to be as fast as possible. When data is backed up it is quite common for some form of compression to occur, especially where there are completely empty blocks as in an Oracle datafile. If on restoring files the 'restore' program does not actually write out empty blocks but just seeks past them it will produce a sparse file (where the Operating System supports sparse files). You then get a sparse file as part of the database.
Why is this a problem ? Oracle has preallocated space for its datafiles. If there are any disk space problems these will become apparent when the datafile is CREATED. They should NEVER occur once the file has been created as Oracle has already been allocated all the disk space it intends to use. Hence errors when filling in new blocks in the datafile are classed as internal errors and can force a datafile offline, or even stop the database. Sparse files can also lead to low level fragmentation. If a datafile is created on a new Unix partition it should obtain contiguous space on that disk. If the space is allocated during usage (as in a sparse file) there is likely to be much greater fragmentation of the datafile on the underlying disk. Although sparse files should behave the same as normal files it is possible for there to be small obscure differences in the behaviour of system calls made by Oracle which can cause unexpected behaviour.
How do I detect if a file is sparse ? The only way to detect a sparse file is to compare its size (in disk blocks), with the free disk space on a disk. This is easiest to check by removing the file and noting the increase in free disk space. **Note: Always make a safe copy of a file before removing it. For database files the database should be in a shutdown state.
Eg: On Sequent PTX:
Check disk space:
>>> df -St .
. (/dev/dsk/zd2s2 ): 517860 blocks 134338 i-nodes
total: 552420 blocks 134400 i-nodes
Check file size:
>>> ls -l sparse_file
-rw-r--r-- 1 usupport sys 1048579 Sep 28 09:01 sparse_file
File size in blocks:
>>> du -s sparse_file
2080 sparse_file
Copy file safely away then remove it:
>>> rm sparse_file
>>> df -St .
. (/dev/dsk/zd2s2 ): 517892 blocks 134339 i-nodes
total: 552420 blocks 134400 i-nodes
We have only freed up 517892 - 517860 = 32 blocks for a 2080 block
file !! Hence this file was a sparse file.
For a non sparse file we have:
>>> df -St .
. (/dev/dsk/zd2s2 ): 513722 blocks 134336 i-nodes
total: 552420 blocks 134400 i-nodes
>>> ls -l non_sparse
-rw-r--r-- 1 usupport sys 1048579 Sep 28 09:14 non_sparse
>>> du -s non_sparse
2080 non_sparse
>>> rm non_sparse
>>> df -St .
. (/dev/dsk/zd2s2 ): 515802 blocks 134337 i-nodes
total: 552420 blocks 134400 i-nodes
We have freed up 515802 - 513722 = 2080 blocks, as expected.
It is a good idea just to use 'du -s' on a disk to get its disk usage
and see if this compares to the disk free (df) results. If it does
not (Ie: du reports much more space used than df shows) then analyse
the disk further as above.
It is also possible on most versions of Unix to use the command
"ls -ls" to show the actual number of blocks allocated to a file.
Eg: ls -ls sparse.dbf
144 -rw-r----- 1 oracle dba 104859648 Feb 3 17:54 sparse.dbf
^^^-- 144 OS blocks occupied (=77K)
How do I 'de-sparse' a sparse file ?
Oracle datafiles MUST be 'de-sparsed' to be used. The only effective way to do this is file by file using a known 'safe' command, Eg: tar. For example to 'de-sparse' a file, 'tar' it onto tape (or a tar disk file) and then fetch it back. You should check the disk free space before and after to ensure the file has expanded. 'tar' is safe for this on most machines but you should check the 'df' results to confirm this.
Eg: To desparse our sparse file earlier:
df -St . -> 517850 blocks free
tar cvf /dev/xxx sparse_file
rm sparse_file
tar xvf /dev/xxx sparse_file
df -St . -> 515802 blocks free
Ensure there is no other disk activity on the current disk that
would confuse the 'df' readings.
Safe Backups
The following Unix commands are usually safe for backup and
recovery:
tar
cpio
dd
Any other commands or third party utilities should be checked
individually for correct behaviour.
Please note that the tar and cpio commands for OSF/1 3.0 can
produce sparce files. See note:22151.1 for further information.
References:
Document 251336.1 What Are Sparse Files
No comments:
Post a Comment