RE: Drop Tablespace failing

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com>
Date: Thu, 12 Oct 2000 11:59:07 +0530
Message-Id: <10646.119037@fatcity.com>

Thanks to ALL for your responses . Problem Overcome as follows :-

1. Used the NON-Documented parameter to mark the rollback segment as Corrupt & COMMENTED ( #) out ALL the Other Rollback segmenst :-

_corrupted_rollback_segments=migr
# ROLLBACK_SEGMENTS = ( TBA_ROLL_1,
# TBA_ROLL_2,
# TBA_ROLL_3)

2) Database Opened Normally
3) Ensured that the Corresponding Datafile Containing the Rollback segment Already Dropped Off ( Check only)
4) Dropped the rollback segment "migr"
SVRMGR> drop public rollback segment migr; Statement processed

5) Dropped the Corresponding Tablespace
SVRMGR> drop tablespace tmp_migr including contents;

NOTE - Had tried the parameter _offline_rollback_segments=migr too BEFORE but that had failed
to cause the Tablespace to be Dropped Off  

6) shutdown the Database
7) REMOVED the Parameters Added in Step 1) Above
8) Brought Up the Database Normally
9) Export of Database Successful

10) NEW Database to be Created & import back to be Done because Corresponding Database has become inconsistent (Due to usage of NON-Documented parameter)

> -----Original Message-----
> From: VIVEK_SHARMA
> Sent: Thursday, October 12, 2000 1:10 AM
> To: VIVEK_SHARMA; 'ORACLE-L_at_fatcity.com'; 'oracledba_at_lazydba.com'
> Cc: 'kaygopal_at_yahoo.com'; 'gajav_at_yahoo.com'
> Subject: RE: Drop Tablespace failing
>
>
> Some more info :-
> SVRMGR> select * from v$recover_file;
> FILE# ONLINE ERROR CHANGE# TIME
> ---------- ------- ------------------ ---------- --------------------
> 81 OFFLINE 7846247 09/27/00 18:02:27
> 1 row selected.
>
>
> > -----Original Message-----
> > From: VIVEK_SHARMA
> > Sent: Wednesday, October 11, 2000 11:44 PM
> > To: 'ORACLE-L_at_fatcity.com'; 'oracledba_at_lazydba.com'
> > Cc: 'kaygopal_at_yahoo.com'; 'gajav_at_yahoo.com'
> > Subject: Drop Tablespace failing
> >
> >
> > Following Sequence of events
> > 1) DMLs happening on a Database with a Particular Rollback Segment
> Online
> > 2) Machine Shutdown Abnormally
> > 3) Upon Booting Machine , Database Open failing with the Message like
> > File size Differs for "/in1backup2/db3/bm2f/migr_roll_1_bm2f.dbf" at
> O.S.
> > & Database Levels
> > 4) Dropped the Datafile as it Contains Only one Rollback Segment which
> was
> > Online during Crash
> > alter database datafile /in1backup2/db3/bm2f/migr_roll_1_bm2f.dbf
> OFFLINE
> > DROP;
> > Statement processed
> > 5) Database Open Succeeded
> > 6) drop of Corresponding Tablespace Failing :-
> > SVRMGR> drop tablespace tmp_migr including contents;
> > drop tablespace tmp_migr including contents
> > *
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-00376: file 81 cannot be read at this time
> > ORA-01110: data file 81: '/in1backup2/db3/bm2f/migr_roll_1_bm2f.dbf'
> >
> > NOTE
> > 1) Export Failing with the Oracle Errors
> > 2) SVRMGR> select * from sys.dba_data_files
> > 2> order by tablespace_name,bytes;
> > FILE_NAME
> > FILE_ID TABLES
> > PACE_NAME BYTES BLOCKS STATUS
> >
> --------------------------------------------------------------------------
> > ------ ---------- ------
> > ------------------------ ---------- ---------- ---------
> > ORA-00376: file 81 cannot be read at this time
> > ORA-01110: data file 81: '/in1backup2/db3/bm2f/migr_roll_1_bm2f.dbf'
> >
>
> --------
> Think you know someone who can answer the above question? Forward it to
> them!
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
Received on Thu Oct 12 2000 - 01:29:07 CDT