Eric,
That's a very interesting tap dance that OTS did for you. As usual they
seem very reluctant to take a position with regards to a third party tool. At
anyrate, assuming that you did "correctly" size your DB during initial creation
& good old MicroSlop did not do anything strange they are correct there should
be no need to use a defrag utility. But on the other hand I don't want to try
and count the number of hard drives I've seen that aren't fragmented, especially
if MicroSlop has control of your swap file. Also I've seen a very significant
increase in performance after defragmenting a hard drive both from the OS and
Oracle's perspective. I would agree that a cold backup before & after the
utility is used would be a most prudent action & I would not use the utility
while the DB is up and running.
Dick Goulet
____________________Reply Separator____________________
Author: "Eric D. Pierce" <[EMAIL PROTECTED]>
Date: 3/12/2001 11:27 AM
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:
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).
