| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: (Fwd) TAR# 1280667.996:Can NT/Win2k server OS defrag uti
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
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
Oracle Worldwide Support
Incident Tracking System
----------------------------------------------------------------------------
--
TAR#: 1280667.996 Reported: 10-MAR-2001
(CUS-3027991)
Assigned: DATASRVW (CHFREEMA.US) Updated: 12-MAR-2001
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 serverReceived on Mon Mar 12 2001 - 17:10:28 CST
(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:PierceED_at_csus.edu 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 -> PierceED_at_csus.edu *** 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: PierceED_at_csus.edu 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: ListGuru_at_fatcity.com (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: kevin_at_dulcian.com 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: ListGuru_at_fatcity.com (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: drake_at_psscorp.com 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: ListGuru_at_fatcity.com (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).
 |
 |