Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!canoe.uoregon.edu!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: ashok_jayaraman@yahoo.com (Jayaraman Ashok) Newsgroups: comp.databases.oracle.server Subject: Re: Datafile for rollback segment Date: 9 Oct 2002 22:14:42 -0700 Organization: http://groups.google.com/ Lines: 276 Message-ID: <7ca2852.0210092114.3313270a@posting.google.com> References: <7ca2852.0210080027.22123f38@posting.google.com> <7ca2852.0210081855.1df7c51c@posting.google.com> <70Vo9.49433$g9.142701@newsfeeds.bigpond.com> NNTP-Posting-Host: 203.247.159.52 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1034226882 15092 127.0.0.1 (10 Oct 2002 05:14:42 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 10 Oct 2002 05:14:42 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:163688 X-Received-Date: Wed, 09 Oct 2002 22:14:38 MST (news.easynews.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" wrote in message news:<70Vo9.49433$g9.142701@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" wrote in message > news:7ca2852.0210081855.1df7c51c@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" wrote in message > news:... > > > Can I just ask why you care where the rollback segment is physically > > > created? > > > > > > HJR > > > > > > > > > "Jayaraman Ashok" wrote in message > > > news:7ca2852.0210080027.22123f38@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