Re: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?

Hi!

Great you got everything working fine.

Just a note, that in truly high available systems, it's reasonable to wait until all long-running queries, which were started before offlining rollback segments, have ended before you actually drop the offlined rollback segments. Oracle is able to read offline rollback segments for read consistency, but if you drop them, you might get ORA-1555's during your operation and you definitely don't want to see those in HA system.

Tanel.

• Original Message ----- From: "Guang Mei" <gmei_at_incyte.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, July 23, 2003 10:54 PM Subject: RE: convert RBS tablespace from DMT to LMT (Oracle 8173) steps?

> I just finished this conversion by using Ron's method: looping through
> RBS00X (X from 1 to 6):
>
>
> alter rollback segment RBS00X offline;
> drop rollback segment RBS00X;
>
> create rollback segment RBS00X storage(initial 2048K next 2048K minextents
> 2 ) tablespace RBSlmt;
> alter rollback segment RBS00X online;
>
> select count(*) from all_objects where status = 'INVALID';
> select SEGMENT_NAME ,STATUS from dba_rollback_segs;
>
>
> I did not encount any problems.
>
> Guang
>
>
> -----Original Message-----
> Daniel Fink
> Sent: Wednesday, July 23, 2003 3:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Oracle will not let you drop a rollback segment if there are active
> transactions using it. However, it will allow you to offline the segment
and
> no new transactions can use it. I don't recall the exact status in
> v$rollstat, but I think it may say pending offline.
>
> As for the commit across cursors, the minute you commit, your transaction
> has ended, even if the cursor is still open. When you issue the next
> statement, you start a new transaction, which should assign you to a new
> rbs. The one you were using is still offline. Of course, if you
> online/assign/offline the rbs after each commit, you are reusing the rbs.
I
> have not tested this, so I may be wrong (Thoughts, Kirti?). However, you
may
> step on your own "free segment." Additionally, during the time
> period of the online/assign/offline, there is a chance that another
> transaction will be assigned to the rbs.
>
> Daniel
>
> Thomas Day wrote:
> >
> > How will you make sure that there are no active segments in your current
> > rollback segments before you remove the datafiles with the OS? Even
after
> > you take the rollback segments off-line, Oracle will continue to use
them
> > as long as they have an active segment.
> >
> > BTW --- One way to help to avoid the ORA-01555 when you are committing
> > across an open cursor is to take the rollback segment that that cursor
is
> > using off-line. No one else will come in a step on the "free" segment
but
> > your session will continue to process just fine until you close the
> cursor.
> > At least that's been my experience.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: gmei_at_incyte.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jul 23 2003 - 17:45:41 CDT