Re: more 8.1: defrag the drive

On Jan 25, 11:08 am, "joel garry" <joel-ga..._at_home.com> wrote:
> On Jan 25, 12:27 am, "mmccaws2" <mmcc..._at_comcast.net> wrote:
>
> > The drive where the 8.1 Oracle database files reside is about 70% full,
> > about 5GB drive. The disk analysis shows that there is over 40k
> > fragments on the drive. One of the tables is about 2Gb. Coworker has
> > a good suggestion that may reduce down time, it sounds good. I'm
> > looking for whether or not it's fraught with potential problems.
>
> > let me know what you think
>
> > 1st - shut down app and stop Oracle
> > 2nd - copy two largest files(combined about 3Gb) to another drive.
> > 3rd - (on Windows 2000 server) defrag drive using windows defrag
> > 4th - copy two files back to drive
> > and at last restart oracle and app
>
> > So, there should not be any problems with the database.Some interesting comments in this thread:http://groups.google.com/group/comp.databases.oracle.server/browse_th...
>
> I had a distinct memory that Windows defragging was not something one
> wanted to do after creating an Oracle datafile, as one of the the
> filesystem types would randomly scatter the sectors about in the worst
> possible manner, or something like that, but I can't seem to find any
> references so maybe I just imagined it.
>
> jg
> --
> @home.com is bogus.
> Email intended to create a paper trail of plausible deniability...http://www.signonsandiego.com/uniontrib/20070125/news_1b25prgn.html

Well you folks are right, I'd have to loan-in an body that has Oracle DB experience. So I'm no expert.

here is the thing

virtually no DB maintenance has been done on this platform over the 3 to 4years. So what I do know is that the main table space hasn't grown much and is between 2 and 3 GB with 22,000 fragments. What had happened is that some table spaces kept on filling up and that quick fix was to increase the upper max range, while tell it to grow no more than 10% at a time. the admin dbdump ... (because I can't see the rest in the screen) has 49,000 fragments in 230MB.

So the Oracle application is installed on its own drive about 5GB. The application log table in Oracle has been filling 2GB up in about 3 to 4 weeks. There are many application errors logging to this table, some application rule error type errors (ie the application rightly creates an error saying that a configuration rule has been violated -- not a Oracle db error) and some '0:Database operation failed ' reported by the application. These messages are found in the MS event view application error utility.

So one administrator tells me that moving the oracle table file to another drive then copying back will not cause a problem. Another administrator with a *nix background thought that copying the db table file from one drive to another, defrag, then copy back could cause problems in the table. (Actually that was what I thought. However the second administrator was convinced this was not the case). Hence, because I never got anything more than the "this is oracle DB" class, I suspect I need the advice from Oracle DB administrators.

So here I am. In the short term, I need to know that this is an acceptable practice or not. In the long run I need to learn for myself which is the best practice. And document the heck of the steps on performing it. If anyone has a link for me to view that would help.

I can run queries to your heart's content. Let me know which table to query to determine if there is anything going wrong with Oracle, like too many fragments. My database gets a lot of change and produces lots of information, it may just mean that I need a routine to trim the logs that the application generates.

I do have access to MS SQL administrator whom can help when he has time. However, even though DB are similar, I don't know if there is administratively something so different that only Oracle DB are familiar with in pinpointing the problems. Experience is the best education.

So looking forward to hearing from you. I'll look at the link above in the mean time.

Mike Received on Thu Jan 25 2007 - 14:46:21 CST