Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Showing results for 
Search instead for 
Did you mean: 

big full backup problem

Go to solution

big full backup problem

I have a backup problem with Oracle: to make a full database backup I put the database in backup mode and backup de tablespaces with cpio and gzip. In time this backup was getting bigger and bigger so that a time ago I cleared some very big tables. Now is the data 1/10th from the data before but the backup size keeps the same (I also make an exp. witch is 1/10 of the original size). I think I understand why: I cleared the table but the tablespaces are as patitions on a OS, clearing a file only clears the pointer to this file, the file contains remains on the harddisk.
Does anybody knows how to really clear a tablespace so that my full backup is smaller again or is the only way to delete the tablesspaces, make them again and do an import?
I use Oracle 7.3.3
Robert Verhagen.
Honored Contributor

Re: big full backup problem


There is a chance you can shrink some of the datafiles. We can only shrink files back to their high water mark -- if there is an extent way out at the end of a file -- we'll not be able to shrink it.

If not, the only thing that will undo this is to recreate the database (exp, imp).

hope this helps!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Honored Contributor

Re: big full backup problem

hi again,

Script to guide you on which datafiles can be shrinked is attached!

CAUTION: Adjust the values accordingly otherwise you may run into space problems.

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Greg OBarr
Regular Advisor

Re: big full backup problem

I don't know how many tables and tablespaces you're talking about here, but if it's not too many, you could try this:

1) Create a new tablespace large enough to hold what's in the one you want to shrink
2) Find all tables in the tablespace you want to shrink and move them to the new tablespace using
SQL> alter table
move tablespace ;
If you're going to leave them there, recreate your indexes in the new tablespace too.
3) When they're all moved, drop the original tablespace
4) Now, if you want, you can re-create the original tablespace with a smaller size
5) Reverse the procedure in step 2 to move the tables back to the original tablespace (if desired) and re-build the indexes.

This should work in the higher 8i versions and above.

Ed Sampson
Frequent Advisor

Re: big full backup problem

You may want to do a dbexport of the data. This will only backup the actual table entries, not the entire tablespace. You can dump it to a flat file in another filesystem, compress it and use regular backups on it.
You will need to do the dbexport for each table in the database. This gives you a 2 tiered backup scheme, with the most current on disk, and +1 on tape.
Hope this helps..
Michael Steele_2
Honored Contributor

Re: big full backup problem

This is a fragmentation issue and has always been a problem with fixed table sizes. All oracle file systems are 99% full. So defragmentation is only allowed through exporting and importing.

If the oracle file system was 85% full then you could use online JFS's defrag. utility.

You're other alternative is to dbstop and copy the data in and out from the O/S instead of using Oracle.

cp -p -r /dir/* /temp/*
rm /dir/*
cp -p -r /temp/* /dir/*

Data is not fragmented when loaded for the first time or copied back in after deletion.
Support Fatherhood - Stop Family Law