Re: more 8.1: defrag the drive

On Jan 25, 12:46 pm, "mmccaws2" <mmcc..._at_comcast.net> wrote:
> 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.htmlWell 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.

Well, some things are difficult to say. First off, 8i can mean a lot of things.
We need to know exactly which patch level you are at (ie, 8.1.7.4.5). Second,
in that time frame you could specify either DMT (Dictionary Managed Tablespaces, the "old" way) or LMT (Locally Managed Tablespaces). With respect to segmentation and numbers of extents, it is a big deal with DMT's, less of a deal with LMT's, though tens of thousands may be an issue.

8i databases do require some maintenance, and exactly what needs to be determined by a DBA. 8i has been desupported, though if a support contract has been maintained continuously some support may be forthcoming - and you need support to get patches. The general consensus is that one should upgrade to something from this century.

>
> 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.

What application would that be?

>
> 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.

Well, the initial questions would be, is anyone complaining about anything?
Are there messages besides log file changes in the alert log (you need to find and examine the alert log, it wil probably be named something like
alert<SID>.log, where <SID> is your instance identifier, perhaps ORCL. It
may be under %ORACLE_HOME% somewhere.) You should be running in archivelog mode, in which case you need to be familiar with how and when to backup, and when you can discard online archived logs. If you are
not running in archivelogmode and depending on exports for backups, you may have a problem.

>
> 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.

They are so different, that only Oracle DBA's can deal with them. Experience is what you call it when you screw it up and lose the data, not the best education at all. Sometimes not so good for your financial well-being, either.

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

Don't listen to anything I say about Windows, I'm a unix bigot. There are helpful Windows people about, listen to them instead. orawin.info and dizwell.com are perhaps a couple of them. I'll spare you the anti-MS rants, but I'm sure you can imagine them. There is also some windows-specific documentation on tahiti.oracle.com.

jg

--
@home.com is bogus.
Hey, I even used to use an Alpha-NT box to warm my feet...