Defragmentation of Oracle 91 files on W2000 server

[RAK]

A client is testing a system on Oracle 9i and Win 2000 (why not 10g and
2003 etc? dont ask, long story).

Performance was poor. Among other issues, we discovered that the Oracle
datafiles are badly fragmented. This is fragmentation in the Windows sense
of the physical file being all over the disk, not the Oracles internal file
fragmentation.
Each Oracle DB file is typically in several thousand fragments (I have no
idea how it got that bad and finding out would take too long). The defrag
program reports 112 files, 260GB total disk space, and an impressive 234,000
file fragments.

The problem: so far they cannot defragment the Oracle data & index files.

They have just tried to defragment the disk which works for other files not
for the Oracle files (*.ora). They tried using Diskeeper and PerfectDisk
with the same result.
They tried the defragment-at-boot-time options with these programs, i.e. to
do the defrag before oracle starts up and perhaps prevents the ora files
being moved. Still no good.

There is plenty of spare space (36%) on the disks which are NTFS RAID5.

Seems a silly problem, there must be an easy answer....?
I am not an Oracle DBA by the way, but a general consultant looking at this
along with some other issues.

[Jim Kennedy]

"RAK" <ra****@NOSPAMattglobal.net> wrote in message
news:1b******************************@news.teranew s.com...

A client is testing a system on Oracle 9i and Win 2000 (why not 10g and
2003 etc? dont ask, long story).

Performance was poor. Among other issues, we discovered that the Oracle
datafiles are badly fragmented. This is fragmentation in the Windows sense
of the physical file being all over the disk, not the Oracles internal file fragmentation.
Each Oracle DB file is typically in several thousand fragments (I have no
idea how it got that bad and finding out would take too long). The defrag
program reports 112 files, 260GB total disk space, and an impressive 234,000 file fragments.

The problem: so far they cannot defragment the Oracle data & index files.

They have just tried to defragment the disk which works for other files not for the Oracle files (*.ora). They tried using Diskeeper and PerfectDisk
with the same result.
They tried the defragment-at-boot-time options with these programs, i.e. to do the defrag before oracle starts up and perhaps prevents the ora files
being moved. Still no good.

There is plenty of spare space (36%) on the disks which are NTFS RAID5.

Seems a silly problem, there must be an easy answer....?
I am not an Oracle DBA by the way, but a general consultant looking at this along with some other issues.

1. Shutdown the database normally.
2. Backup all the files on the disk.
3. format the drive(s).
4. restore the files to the disk.
5. Startup the database.

I had a similar experience and no amount of defrag stuff would help. I
finally had to format the drive and that wiped out hte fragmented master
boot record and I could have a defragged disk.
Jim