Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!arclight.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: 10 Oct 2002 18:50:16 -0700 Organization: http://groups.google.com/ Lines: 325 Message-ID: <7ca2852.0210101750.3091e25d@posting.google.com> References: <7ca2852.0210080027.22123f38@posting.google.com> <7ca2852.0210081855.1df7c51c@posting.google.com> <70Vo9.49433$g9.142701@newsfeeds.bigpond.com> <7ca2852.0210092114.3313270a@posting.google.com> <32d39fb1.0210100934.7f38ffce@posting.google.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 1034301017 12455 127.0.0.1 (11 Oct 2002 01:50:17 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 11 Oct 2002 01:50:17 GMT Xref: newsfeed1.easynews.com comp.databases.oracle.server:163808 X-Received-Date: Thu, 10 Oct 2002 18:50:36 MST (news.easynews.com) 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@msn.com (David Fitzjarrell) wrote in message news:<32d39fb1.0210100934.7f38ffce@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@yahoo.com (Jayaraman Ashok) wrote in message news:<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" 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