Re: Datafile for rollback segment

Hi
Thanks for making it clear to me. My understanding was wrong and it didn't strike me to make the rollback segments offline before i made their datafile offline.

Thanks,
Ashok

oratune_at_msn.com (David Fitzjarrell) wrote in message news:<32d39fb1.0210100934.7f38ffce_at_posting.google.com>...
> Jayaraman,
>
> Explain to me why taking active rollback segments out of the picture
> by taking the datafile they reside in offline won't cause problems. I
> can't understand your logic, or lack thereof.
>
> Let us say I have my RBS tablespace, and it has two datafiles. I've
> already created a number of rollback segments that populate both
> files. Now, as a matter of 'academic interest', I want to create my
> next segment in a specific datafile so I take the other RBS datafile
> offline. I attempt to create my new rollback segment and am met with
> an ORA-03113, most likely due to an attempt to access a rollback
> segment that no longer 'exists' -- well, it DOES exist in the 'mind'
> of Oracle, since it was never taken offline, but it's no longer
> accessible because you've taken the datafile 'away'. You have no way
> of knowing, in advance, which rollback segment Oracle will choose for
> the next transaction, and it may well be one of the segments resident
> in the now-offline datafile. In any event you didn't stop to take the
> rollback SEGMENTS offline before you took the file offline, so Oracle
> still believes them to be usable. Since you were trying to create
> segment RBS3 in your 'empty' datafile the error naturally follows
> since segments RBS1 and RBS2 [your only non-SYSTEM rollback segments]
> were taken out of the picture unceremoniously when their datafile was
> made inaccessible.
>
> Finding WHERE rollback segments reside is fairly easy:
>
> select distinct e.segment_name, f.file_name
> from dba_extents e, dba_data_files f
> where e.tablespace_name = 'RBS'
> and f.file_id = e.file_id;
>
> From there it's a simple task to offline the affected rollback
> segments prior to taking the datafile offline. Once Oracle realises
> that some segments are not available it won't try to use them and find
> them missing.
>
> David Fitzjarrell
>
> ashok_jayaraman_at_yahoo.com (Jayaraman Ashok) wrote in message news:<7ca2852.0210092114.3313270a_at_posting.google.com>...
> > Hi Richard
> >
> > Thanks for your views. But i believe that taking a rollback segment
> > datafile offline with online rollback segments should not cause any
> > problem as it is shown in your test case on 9i. Also i had no other
> > transactions running on the database and my session was the only
> > session.May be if you can test it on 817 database and tell me it would
> > be very useful. This is what i did.
> >
> > SQL> select file#,substr(name,1,40) "FileName",bytes/1048576,status
> > from v$datafile;
> >
> > FILE# FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > -------
> > 1 /data1/oradata/msys/system01.dbf 250
> > SYSTEM
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > ONLINE
> > 3 /data1/oradata/msys/user01.dbf 50
> > ONLINE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > ONLINE
> >
> > SQL> select file_id,substr(file_name,1,40)
> > "FileName",bytes/1048576,status from
> > 2 dba_data_files;
> >
> > FILE_ID FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > ---------
> > 1 /data1/oradata/msys/system01.dbf 250
> > AVAILABLE
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > AVAILABLE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > AVAILABLE
> > 3 /data1/oradata/msys/user01.dbf 50
> > AVAILABLE
> >
> > SQL> select file_id,substr(segment_name,1,10)
> > "RBSName",initial_extent/1048576
> > 2 "Initial",next_extent/1048576 "Next",min_extents
> > "Minextents",status
> > 3 from dba_rollback_segs;
> >
> > FILE_ID RBSName Initial Next Minextents STATUS
> > ---------- ---------- ---------- ---------- ----------
> > ----------------
> > 1 SYSTEM .0546875 .0546875 2 ONLINE
> > 2 RBS1 .5 .5 10 ONLINE
> > 4 RBS2 1 1 2 ONLINE
> >
> > SQL> select tablespace_name,file_id,bytes/1048576 "Size" from
> > dba_free_space
> > 2 where tablespace_name = 'RBS';
> >
> > TABLESPACE_NAME FILE_ID Size
> > ------------------------------ ---------- ----------
> > RBS 2 43.8984375
> > RBS 4 298.976563
> >
> > SQL> select * from v$rollname;
> >
> > USN NAME
> > ---------- ------------------------------
> > 0 SYSTEM
> > 1 RBS1
> > 2 RBS2
> >
> > SQL> show user
> > USER is "SYSTEM"
> > SQL> alter database datafile '/data1/oradata/msys/rbs01.dbf' offline;
> >
> > Database altered.
> >
> > SQL> create public rollback segment rbs3 tablespace rbs
> > 2 storage(initial 1m next 1m);
> > create public rollback segment rbs3 tablespace rbs
> > *
> > ERROR at line 1:
> > ORA-03113: end-of-file on communication channel
> >
> >
> > SQL> conn system/manager
> > Connected.
> > SQL> select file#,substr(name,1,40) "FileName",bytes/1048576,status
> > from v$datafile;
> >
> > FILE# FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > -------
> > 1 /data1/oradata/msys/system01.dbf 250
> > SYSTEM
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > RECOVER
> > 3 /data1/oradata/msys/user01.dbf 50
> > ONLINE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > ONLINE
> >
> > SQL> select file_id,substr(file_name,1,40)
> > "FileName",bytes/1048576,status from
> > 2 dba_data_files;
> >
> > FILE_ID FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > ---------
> > 1 /data1/oradata/msys/system01.dbf 250
> > AVAILABLE
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > AVAILABLE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > AVAILABLE
> > 3 /data1/oradata/msys/user01.dbf 50
> > AVAILABLE
> >
> > SQL> select file_id,substr(segment_name,1,10)
> > "RBSName",initial_extent/1048576
> > 2 "Initial",next_extent/1048576 "Next",min_extents
> > "Minextents",status
> > 3 from dba_rollback_segs;
> >
> > FILE_ID RBSName Initial Next Minextents STATUS
> > ---------- ---------- ---------- ---------- ----------
> > ----------------
> > 1 SYSTEM .0546875 .0546875 2 ONLINE
> > 2 RBS1 .5 .5 10 ONLINE
> > 4 RBS2 1 1 2 ONLINE
> >
> > SQL> conn internal;
> > Connected.
> > SQL> recover datafile '/data1/oradata/msys/rbs01.dbf';
> > Media recovery complete.
> > SQL> select file#,substr(name,1,40) "FileName",bytes/1048576,status
> > from v$datafile;
> >
> > FILE# FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > -------
> > 1 /data1/oradata/msys/system01.dbf 250
> > SYSTEM
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > OFFLINE
> > 3 /data1/oradata/msys/user01.dbf 50
> > ONLINE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > ONLINE
> >
> > SQL> alter database datafile '/data1/oradata/msys/rbs01.dbf' online;
> >
> > Database altered.
> >
> > SQL> select file#,substr(name,1,40) "FileName",bytes/1048576,status
> > from v$datafile;
> >
> > FILE# FileName BYTES/1048576
> > STATUS
> > ---------- ---------------------------------------- -------------
> > -------
> > 1 /data1/oradata/msys/system01.dbf 250
> > SYSTEM
> > 2 /data1/oradata/msys/rbs01.dbf 50
> > ONLINE
> > 3 /data1/oradata/msys/user01.dbf 50
> > ONLINE
> > 4 /data1/oradata/msys/rbs02.dbf 300
> > ONLINE
> >
> > SQL> spool off
> >
> >
> > Cheers,
> > Ashok
> >
> >
> >
> >
> >
> > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<70Vo9.49433$g9.142701_at_newsfeeds.bigpond.com>...
> > > Hi Jayarman,
> > >
> > > I performed this test on 9.2 (not 8.1.7 I know).
> > >
> > > SQL> create tablespace rbs
> > > 2 datafile 'c:\Bowie\rbs01.dbf' size 1m, 'c:\Bowie\rbs02.dbf' size 1m
> > > 3 uniform size 64k;
> > >
> > > Tablespace created.
> > >
> > > SQL> alter database datafile 'c:\Bowie\rbs01.dbf' offline;
> > >
> > > Database altered.
> > >
> > > SQL> create rollback segment rbs01
> > > 2 tablespace rbs
> > > 3 storage (initial 64K next 64K minextents 10);
> > >
> > > Rollback segment created.
> > >
> > > SQL> create rollback segment rbs02
> > > 2 tablespace rbs
> > > 3 storage (initial 64K next 64K minextents 20);
> > > create rollback segment rbs02
> > > *
> > > ERROR at line 1:
> > > ORA-01659: unable to allocate MINEXTENTS beyond 5 in tablespace RBS
> > >
> > > So I had no problems creating a rollback segment initially and failing to
> > > create one if there's insufficient space in the remaining data file.
> > >
> > > As it should be.
> > >
> > > However ...
> > >
> > > If you already have rollback segments created and online in the tablespace,
> > > your database would be in all sorts of trouble. Why ? Because by taking the
> > > datafile offline portions of the rollback segments effectively become
> > > unusable causing transactions and the such to crash. And as the offline is
> > > effectively an offline immediate, Oracle doesn't attempt a checkpoint so the
> > > datafile can only be brought back via a recovery. And as it contains an
> > > active rollback segment, you can only perform such a recovery with a closed
> > > (mounted) database.
> > >
> > > Can fully test this on a 8.1.7 database at the moment but I suspect this is
> > > what is causing the grief.
> > >
> > > Make sense ?
> > >
> > > Cheers
> > >
> > > Richard
> > >
> > >
> > >
> > > "Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message
> > > news:7ca2852.0210081855.1df7c51c_at_posting.google.com...
> > > > Hi HJR
> > > >
> > > > I did it purely for academic interest. Since when a tablespace has 2
> > > > datafiles and if one of them is online, you are allowed to create a
> > > > table on that tablespace, then why not the same for a rollback, as
> > > > both are creating only segments. More clarification / explanation is
> > > > requested from all.
> > > >
> > > > Cheers,
> > > > Ashok
> > > >
> > > >
> > > >
> > > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:<k7xo9.48490$g9.138655_at_newsfeeds.bigpond.com>...
> > > > > Can I just ask why you care where the rollback segment is physically
> > > > > created?
> > > > >
> > > > > HJR
> > > > >
> > > > >
> > > > > "Jayaraman Ashok" <ashok_jayaraman_at_yahoo.com> wrote in message
> > > > > news:7ca2852.0210080027.22123f38_at_posting.google.com...
> > > > > > Hi
> > > > > > I have two datafiles for rollback segment tablespace and have enough
> > > > > > free space on both the datafiles. Now to create a rollback segment in
> > > > > > one specific datafile i offline the other datafile and try to create
> > > > > > the rollback segment, which naturally must create it on the online
> > > > > > datafile. But it is failing with ORA-3113 and i have to do media
> > > > > > recovery to bring it back online. What is wrong here ? The setup is
> > > > > > Oracle8.1.7.0.0 on Solaris7.
> > > > > >
> > > > > > Cheers,
> > > > > > Ashok
Received on Thu Oct 10 2002 - 20:50:16 CDT