Re: Oracle 8i database reorg

  • From: Tanel Põder <tanel.poder.003@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 13 May 2004 20:04:39 +0300

Hi,

Well, last time I had to do a reorg with short downtime, we had the problem
that export/import would have been too slow, but there was no extra
temporary space to use alter table move to a new temporary tablespace.

So basically we did:

1) full backup
2) export with rows=n to save the index definitions
3) disable primary & unique key constraints, drop all indexes
4) recreate index tablespaces
5) alter table x move tablespace indx parallel nologging
6) recreate table tablespaces
7) alter table x move tablespace data parallel nologging
8) extract index definitions from exportfile & modify the index creation
commands to include parallel & nologging, then ran the script to build the
indexes

Since we were on raw devices, recreating tablespaces with all its datafiles
serially would have taken too much time (the raw devices have to be
formatted), thus I created all tablespaces initially with only one datafile
and added all the rest of datafiles into this tablespace simultaneously
(multiple alter tablespace add datafile commands ran in different sessions).

So this is one way to do the reorg with small downtime, you may want to use
some variation of it depending on your needs.

Note that when doing a nologging operation such is move or index build, do
verify first whether this operation is actually nologging (by comparing
session statistics before & after test operation), I had a problem in ver
8.1.6.3 that one of the operations wasn't nologging despite my nologging
clause in DDL command. I don't remember with which operation I actually saw
it but after altering relevant operations to nologging, then the nologging
operation actually worked.

Tanel.

----- Original Message ----- 
From: "Aaron Sentell" <asentell1@xxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, May 13, 2004 6:06 PM
Subject: Oracle 8i database reorg


> I have a 30 GB Oracle 8.1.7 database that I would like to reorg and at the
same time modify to use locally managed tablespaces. This is the first time
I will be attempting something like this. Database downtime is not a big
issue, but I still want to do it as quickly and efficiently as possible.
I've read a 1999 technical document from the IOUG web site that describes
how to do this using export/import, but I was wondering if anyone has any
other methodologies that they prefer? BTW, I need the tablespace names to
remain the same.
>
> Thanks,
> Aaron
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: