Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Datafile for rollback segment

Re: Datafile for rollback segment

From: Jayaraman Ashok <ashok_jayaraman_at_yahoo.com>
Date: 9 Oct 2002 22:14:42 -0700
Message-ID: <7ca2852.0210092114.3313270a@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 - 00:14:42 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US