Re: Datafile for rollback segment

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 - 12:34:09 CDT