Okay - before this one dies ... this incorporates my usual NT bias.
So you have a new box - with newly created NTFS volume - lets assume JBOD
and no hardware RAID.
They're empty. Completely.
You create new tablespaces with multple datafiles, each equal to 1 GB so
that your backup job can compress them without puking. I believe that its a
safe assumption that these datafiles are on continguous tracks and blocks on
the physical hard drives - with the actual layout varying depending upon the
RAID configuration. You can even drop the datafiles from the database - just
keep the file system files around for use later. (That REUSE switch in the
datafile creation is quite handy).
Oh - you mean that you only created the datafiles as 256 MB each, and set
them to autoextend?
How large was the autoextend size? 640 KB?
Now you're complaining that you datafile is fragmented?
Of course its fragmented - you added 640 KB chunks to it.
If the logical volume was using RAW partitions, it would have been sliced up
BY SECTOR, with a starting sector and the number of sectors - guaranteed to
be contiguous (sounds like a vector). This is one of the things that I like
most about Oracle on Linux - CONTROL of the filesystem creation.
So how would you best defragment a drive? I would say by making it brand
new.
Forget the defragmentation tools. Use fdisk.
I'll assume that the datafiles that you are attempting to defragment are on
separate logical drives from other files. If not - move them, or plan on
moving the datafiles.
BEGIN
alter database backup controlfile to trace;
Perform a cold database backup to local disk and tape.
With the database still shutdown, format the logical volumes - bringing them
back to their pristine state, before you selected a sub-optimal datafile
configuration/layout.
Copy the datafile from the backup staging area to the newly formatted
volume.
As there is no garbage in the File Allocation Table of the newly formatted
logical drive - it should be simply grabbing contiguous tracks/sectors.
After opening the database - resize the datafile to its mature size, and
turn off autoextend.
alter database backup controlfile to trace;
END
I'll agree that autoextend is convenient if you don't know the storage
requirements.
You can't really blame the OS for grabbing sectors that aren't contiguous,
it its trying to (re)use vacated sections of the logical drive.
just my opinion.
Paul
-------- Original Message --------
utilities
Date: Mon, 12 Mar 2001 13:05:46 -0800
Reply-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
That is a pretty pathetic answer. "Datafiles, if they are sized
correctly,
will never need to be defraged". Yeah right, and if I install software
the
way it says to on the box, it will always work and I'll never have any
problems.
-----Original Message-----
Pierce
Sent: Monday, March 12, 2001 2:27 PM
To: Multiple recipients of list ORACLE-L
Does anyone else get the impression that Oracle tech support
isn't really answering the question about OS fragmentation
below??????
I thought it was obvious that moving db files when the db is
open is likely to be a bad idea, but can't imagine why defragging
at the OS level when the db is closed would be a problem.
eg, I read an Oracle tech support note that describes how to
move db files from one NT machine to another. If one can move
the files from one machine to another, why can't the db files
be reorganized (at the OS level) on the *same* machine?
This seems like an obvious question (and probably a straightforward
issue), I don't understand why Oracle tech support is so ambiguous
and lacking in explanation/justification for their statements.
If they are so enthusiastic about exp/imp, why wouldn't they be
as interested in gaining performance by additionally optimizing
disk access at the OS level?
Or maybe I'm wrong and NT/Win2k actually does a really efficient job
of laying out large files (Oracle's pre-allocated db file storage)
just like Oracle tech support is hinting?
regards,
ep
bcc: campus SysAdmn gurus
------- Forwarded message follows -------
Date sent: Mon, 12 Mar 2001 10:55:11 -0500 (EST)
To: [EMAIL PROTECTED]
Oracle Worldwide Support
Incident Tracking System
----------------------------------------------------------------------------
--
TAR#: 1280667.996 Reported: 10-MAR-2001
(CUS-3027991)
Assigned: DATASRVW (CHFREEMA.US) Updated: 12-MAR-2001
(CHFREEMA.US)
Severity: Severe Loss of Service (2)
Status: Soft Close (SCL)
Platform: MS Windows 2000
Product: Oracle Server - Enterprise Edition (8.1.7)
RDBMS: 8.1.7
Customer: TRUSTEES CALIFORNIA STATE UNIV
Contact: Eric Pierce Phone: 916 278-7586
----------------------------------------------------------------------------
--
Can NT/Win2k server OS defrag utilities be *safely* used on the db
files?
----------------------------------------------------------------------------
--
*** METALINK.US 10-MAR-2001 00:00:18 GMT ***
...
### Platform and O/S version, including patchset orservice pack level?
###
Oracle 8.1.x Micsosoft NT4 server or Windows 2000server.
### What version and patchset level of the database are you running?###
8.1.7.?.?
### Please describe your problem: ###
This is a generic question about behavior of Oracle8i on NT4 server
(or Windows2000 server): It is ok to use disk defragmentation
utilities (eg, Norton speedisk for NT) on the Oracle 8i db files? An
individual on the Oracle-L listserv says that NT defrag utilities
will corrupt the db files (he is claiming that the physical placement
of the db files at the OS block level is "fragile" from Oracle's
perspective). This seems to contradict my experience working with
Oracle7.3 on Netware, where it is quite possible to move db files
around, and then have Oracle see them in a new location and go on
operating normally. We are trying to set up a plan for dbserver
tuning/maintenance, and need to know if disk defragging is required
and/or advisable for performance and recovery reasons on
NT/Windows2000 servers.
Thanks,
Eric D. Pierce
Student Services
CSU, Sacramento
reply by email:[EMAIL PROTECTED]
or phone (916) 278-7586
...
### What is the impact to your business because of thisproblem? ###
could potentially be high, but it is not a current operationalissue
Contact me via : E-mail -> [EMAIL PROTECTED]
*** METALINK.US 10-MAR-2001 00:00:18 GMT ***
Automatically assigned via METALINK.
*** CHFREEMA.US 10-MAR-2001 00:12:49 GMT, 09-MAR-01 Local ***
You should never really defrag your database files. Your datafiles
should
never need to be defragged assuming that you sized them appropriately.
If your database is open when you are doing the defrag it will corrupt
your
data every time. With a 3rd party utility when the database is closed
would
have to be tested to see if it would even work. Make sure that you take
a
backup before you try this though.
*** METALINK.US 10-MAR-2001 00:58:03 GMT ***
New info : Friday March 9, 2001 4:56pm california time
That really doesn't help much, and seems to possibly
contradict the conventional wisdom of NT SysAdmns, which is
to defrag a file system constantly.
Please comment on the Oracle-l listserv post that started
the discussion:
(thanks!!! ep)
-----excerpt----
|Using a little utility called contig I noticed that the Oracle
| 8.1.6 datafileson my test NT server are quite fragmented, an
| average of 177 fragments perfile, 118 fragments for the OEM
| repository datafile. The poor utilitycouldn't do anything with
| the database files, they are too large perhaps.
|
|These were created on an empty server, 8i release 2 went on it
| after a defrag,then the OEM. This is on a hard disk with 1.2G of
| free space, none of thedatafiles come close to that.
|
| Why so many fragments? Oracle created thosefiles in one pass,
| does NT write randomly to disk or what?
|
| Won't thishave an impact on my NT database's performance?
|
| Oracle says tablespacefragmentation is not a big deal, but
| fragmentation at the OS level matters.Supposedly that's why NT
| and WndowsXX came with defragmentation tools.
|
|???
|
| Is there a registry setting somewhere to tell NT to write
| contiguouslyto disk?
---end---
*** CHFREEMA.US 12-MAR-2001 15:40:53 GMT, 12-MAR-01 Local ***
The only way to defrag oracle files is to do an export/import. Any 3rd
party
tool or OEM's defrag option will amount to the same thing. They will
take an
export and then import the object back in and that will defrag the file.
Datafiles, if sized correctly, should never need to be defragged. If you
are
concerned that there is fragmentation on your oracle file take an
export.
------- End of forwarded message -------
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Eric D. Pierce
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kevin Kostyszyn
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paul Drake
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
