How to delete large oracle datafiles faster?

[muniraju_h]

Small tips matter a lot !

We had a situation where 3 TB of database (datafiles) needed to be deleted from filesystems (sapdata1..2..3.......12).

There are 300 files of 10 GB and 2 files of 2 GB.

Traditional linux rm command used to take 1 min per 10 GB taking in total 5 hours.

Even firing multiple rm processes in background did not help much.

Necessity forced me to google and I hit a great link.

http://www.depesz.com/2010/04/04/how-to-remove-backups/

I learnt that truncate function in perl can be used efficiently for this scenario.

What truncate does? It truncates a given file by a said amount (in bytes).

For example: truncate ( <file> 10 )   will truncate <file> by 10 bytes.

If <file> was of 50 bytes, it will then become 40 bytes.

Did some tests, truncating with 10MB, 100MB, 1MB & 5B and found that truncating chunks of 100 MB took the least time.

First, I built list of file to be deleted. For example dbfiles.lst

Sample contents of dbfiles.lst is as below

/oracle/ERQ/sapdata1/sr3_198/sr3.data198

/oracle/ERQ/sapdata2/sr3_204/sr3.data204

/oracle/ERQ/sapdata3/sr3_203/sr3.data203

/oracle/ERQ/sapdata4/sr3_200/sr3.data200

/oracle/ERQ/sapdata5/sr3_206/sr3.data206

/oracle/ERQ/sapdata6/sr3_209/sr3.data209

/oracle/ERQ/sapdata7/sr3_208/sr3.data208

Then I wrote two scripts.

delete.sh

#!/bin/sh

for (( c=1; c <= 2048; i++ ))

do

for i1 in `cat dbfiles.lst`

do

i2=`du -b $i1`

./truncate.pl $i2 &

done

done

truncate.pl

#!/usr/bin/perl -w

$s1=$ARGV[0];

$n1=$ARGV[1];

$s1-=104857600;

open (my $FILE, "+<", $n1);

truncate( $FILE, $s1 );

close($FILE);

Executed the script delete.sh in background.

Monitored the deletion speed every 60 seconds.

Hurray! The average deletion speed was around 1 TB per minute.

Deletion was completed in 3 minutes vs. 5 hours with traditional rm command. Unimaginable Gain !!

Hope this helps someone with similar requirement.

Good Luck !