Re: Oracle 8i database reorg

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 13 May 2004 20:04:39 +0300
Message-ID: <06b201c4390c$62721150$2ffb23d5@porgand>

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:

full backup
export with rows=n to save the index definitions
disable primary & unique key constraints, drop all indexes
recreate index tablespaces
alter table x move tablespace indx parallel nologging
recreate table tablespaces
alter table x move tablespace data parallel nologging
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_at_cox.net> To: <Oracle-L_at_freelists.org> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu May 13 2004 - 13:00:20 CDT