Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: more 8.1: defrag the drive

Re: more 8.1: defrag the drive

From: mmccaws2 <mmccaws_at_comcast.net>
Date: 13 Feb 2007 23:23:02 -0800
Message-ID: <1171437782.828214.96650@v33g2000cwv.googlegroups.com>


On Jan 26, 10:58 am, "joel garry" <joel-ga..._at_home.com> wrote:
> On Jan 26, 9:41 am, "mmccaws2" <mmcc..._at_comcast.net> wrote:
>
>
>
> > On Jan 26, 2:23 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
>
> > > On Jan 25, 3: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.
>
> > > > ThanksIf it were me, I would search for the listener log. You will likely
> > > find that the listener log is quite a large file, yet started life as a
> > > very small file. As the file grew in size, it became severely
> > > fragmented. To see if this file is the source of most of the 40,000
> > > fragments on the drive, shut down the listener when few people are
> > > using the system (hint: search for LSNRCTL in the documentation), and
> > > copy the listener log file to another drive. After the file is copied,
> > > delete it from its original location. If you then copy the listener
> > > log file back to its original location, the file will be defragmented
> > > automatically, assuming that there is a large enough area of adjacent
> > > free space on the drive and the drive is formatted with NTFS. After
> > > the file is copied back, restart the listener. Note: you do not _need_
> > > to copy the listener log file back to its original location, as a new
> > > file will be automatically created.
>
> > > After fixing the listener log file, you should see the number of file
> > > fragments drop considerably. If the number of file fragments still
> > > appears to be a problem (and no users are connected to the databases
> > > AND you have a good backup), connect to each database using SQLPLUS and
> > > execute SHUTDOWN IMMEDIATE; Once each database is down, shut down the
> > > listener, and all Oracle related services in the Control Panel Services
> > > applet. Copy (do not cut and paste) the Oracle related folders to
> > > another drive. Once copied, verify that the files have been moved,
> > > then delete the Oracle related folders from their original locations.
> > > Then copy the Oracle related folders from the other drive back to their
> > > original locations. Once complete, restart the server (easier to
> > > explain and accomplish than restarting all Oracle related items).
>
> > > No defrag is needed in the above approach. The only operation with
> > > risk is the copy of the Oracle related files to another drive, followed
> > > by a deleted.
>
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.Thanks for the details. I took the advice of some of the other
> > posters, and have been making queries to see if I can find out what if
> > there is a particular entry source that is most common. I have found
> > on item. Now I'm trying to get the vendor to provide how the table
> > fields are created so I can better determine the entries. I found that
> > one name appeared in over 200,000 entries of the half meg records.
> > Since this table was truncated earlier this week, that seems worth
> > looking into.
>
> > I'll discuss your aproach with the SE that has been helping me.
>
> Charles made some good suggestions. (I tend to forget about the
> listener
> log since my configuration rarely uses the listener, and in other
> configurations
> I like to rotate the log automatically).
>
> Truncating a table releases space within the tablespace, but does not
> remove data file space from the disk.
>
> A couple of important things:
>
> It is possible for user objects to be put into the system tablespace.
> This
> is a very bad thing. Use the following query:
>
> select index_name,owner from dba_indexes where tablespace_name =
> 'SYSTEM'
> and owner not in ('SYS','SYSTEM')
> /
>
> If there is anything there owned by any users, you need to get a DBA in
> there
> immediately to start fixing it. Also check table_name from dba_tables.
>
> If you've made many thousands of extents in DMT tablespaces, that makes
> certain tables in the dictionary very large. While the space can be
> reused
> after releasing (like with a truncate), it is possible that these
> tables can wind up
> sparsely populated and cause performance issues at odd times.
>
> The way to determine if a tablespace is DMT or LMT:
>
> select tablespace_name, extent_management from dba_tablespaces;
>
> If extent_management is LOCAL, that's an LMT, a good thing, you don't
> have to worry about extents much. Otherwise, you need a dba.
>
> (Lurkers note, some of the above is specific to 8i, though it can apply
> to later versions, there are additional considerations).
>
> jg
> --
> @home.com is bogus.
> Googlebomb still works:http://accordionguy.blogware.com/blog/_archives/2007/1/26/2684989.html

It went well.

Thanks Received on Wed Feb 14 2007 - 01:23:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US