cancel
Showing results for 
Search instead for 
Did you mean: 

big full backup problem

SOLVED
Go to solution

big full backup problem

Hello,
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
Thanks,
Robert Verhagen.
5 REPLIES
Yogeeraj_1
Honored Contributor

Re: big full backup problem

hi,

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!

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

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.

regards
Yogeeraj
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.

-greg
Ed Sampson
Frequent Advisor

Re: big full backup problem

Robert,
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