Can we recover from Lost Rollback tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Can we recover from Lost Rollback tablespace

Hybrid View

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Angry Can we recover from Lost Rollback tablespace

    hi all ,
    this is just a test Scenario. We have a test database and we had taken a backup leaving the RBS tablespace together with the controlfile. Now the database is crashed , i.e someone by mistake replace the current files with the old backup. So the controlfile and datafiles are all in sync without the RBS datfile cos it an newer version than the others.
    Now can we so something about it .By droping this tablespace or is there a method to recover it. Cos when i tried to take the datafile offline drop and open the database then i try ot create another rollbacksegment it does not allow. Cos it needs an online rollback segment.

    I dont think so the database can be recovered,
    Is there any method

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  2. #2
    Join Date
    May 2001
    Posts
    736
    Check the following scenarios

    a) Recovery of missing datafile with rollback segment The tricky part here is if you are performing online recovery. Otherwise you can just use the recover datafile command. Now, if you are performing an online recovery, you must first ensure that in the init.ora file, you remove the parameter rollback_segments. Otherwise, oracle will want to use those rollback segments when opening the database, but can't find them and wont open. Until you recover the datafiles that contain the rollback segments, you need to create some temporary rollback segments in order for new transactions to work. Even if other rollback segments are ok, they will have to be taken offline. So, all the rollback segments that belong to the datafile need to be recovered. If all the datafiles belonging to the tablespace rollback_data were lost, you can now issue a recover tablespace rollback_data. Next bring the tablespace online and check the status of the rollback segments by doing a select segment_name, status from dba_rollback_segs; You will see the list of rollback segments that are in status Need Recovery. Simply issue alter rollback segment online command to complete. Don't forget to reset the rollback_segments parameter in the init.ora.

    b) Recovery of missing datafile without rollback segment There are three ways to recover in this scenario, as mentioned above. 1. recover database 2. recover datafile 'c:\orant\database\usr1orcl.ora' 3. recover tablespace user_data

    c) Recovery with missing online redo logs Missing online redo logs means that somehow you have lost your redo logs before they had a chance to archived. This means that crash recovery cannot be performed, so media recovery is required instead. All datafiles will need to berestored and rolled forwarded until the last available archived log file is applied. This is thus an incomplete recovery, and as such, the recover database command is necessary. (i.e. you cannot do a datafile or tablespace recovery). As always, when an incomplete recovery is performed, you must open the database with resetlogs. Note: the best way to avoid this kind of a loss, is to mirror your online log files.

  3. #3
    Join Date
    Aug 2002
    Posts
    176
    Originally posted by akhadar
    Check the following scenarios

    a) Until you recover the datafiles that contain the rollback segments, you need to create some temporary rollback segments in order for new transactions to work. Even if other rollback segments are ok, they will have to be taken offline. So, all the rollback segments that belong to the datafile need to be recovered. .
    Dear Akhadar
    Thanks for your reply
    But here all my rollback segments other than the system are inside the missing datafile? so i dont think there ia a possibility of recovering them.

    By temporary rollback segments you meant to creat additional rollback segments right? but this could not be done cause once i give the command create rollback segment into another tablespace it tells it requires to read the missing datafile..

    So i think we should always have additonal rollback segments into someother tablespace or have tow tablespaces for rollback segments for safety purpose and assign those rollback segments when you have situation like these. then create new rollback segments into a new tablespace and drop the tablespace with errors...

    am I correct ??

    Cheers
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    ok moving the rbs dbf toanother name
    [oracle@rac1 lnx817]$ mv rbs01.dbf rbs01.dbf.old
    
    now watch....
    
    sys@LNX817>startup
    ORACLE instance started.
    
    Total System Global Area   36724896 bytes
    Fixed Size                    73888 bytes
    Variable Size              20094976 bytes
    Database Buffers           16384000 bytes
    Redo Buffers                 172032 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
    ORA-01110: data file 3: '/u02/oradata/lnx817/rbs01.dbf'
    
    sys@LNX817>alter database datafile '/u02/oradata/lnx817/rbs01.dbf' offline drop;
    
    Database altered.
    
    sys@LNX817>alter database open;
    
    Database altered.
    
    sys@LNX817>drop tablespace rbs;
    drop tablespace rbs
    *
    ERROR at line 1:
    ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
    
    sys@LNX817>drop tablespace rbs including contents;
    
    Tablespace dropped.
    
    sys@LNX817>shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    sys@LNX817>startup
    ORACLE instance started.
    
    Total System Global Area   36724896 bytes
    Fixed Size                    73888 bytes
    Variable Size              20094976 bytes
    Database Buffers           16384000 bytes
    Redo Buffers                 172032 bytes
    Database mounted.
    Database opened.
    
    sys@LNX817>create tablespace rbs 
      2  datafile '/u02/oradata/lnx817/rbs01.dbf'
      3  size 10M autoextend on next 1M maxsize 50M
      4 default storage (initial 128k next 128k pctincrease 0 maxextents 1024);
    
    Tablespace created.
    
    sys@LNX817>create public rollback segment rbs01 tablespace rbs;
    
    Rollback segment created.
    
    sys@LNX817>c.01.02
      1* create public rollback segment rbs02 tablespace rbs
    sys@LNX817>r
      1* create public rollback segment rbs02 tablespace rbs
    
    Rollback segment created.
    
    sys@LNX817>c.02.03
      1* create public rollback segment rbs03 tablespace rbs
    sys@LNX817>r
      1* create public rollback segment rbs03 tablespace rbs
    
    Rollback segment created.
    
    sys@LNX817>c.03.04
      1* create public rollback segment rbs04 tablespace rbs
    sys@LNX817>r
      1* create public rollback segment rbs04 tablespace rbs
    
    Rollback segment created.
    
    sys@LNX817>alter rollback segment rbs01 online;
    
    Rollback segment altered.
    
    sys@LNX817>c.01.02
      1* alter rollback segment rbs02 online
    sys@LNX817>r
      1* alter rollback segment rbs02 online
    
    Rollback segment altered.
    
    sys@LNX817>c.02.03
      1* alter rollback segment rbs03 online
    sys@LNX817>r
      1* alter rollback segment rbs03 online
    
    Rollback segment altered.
    
    sys@LNX817>c.03.04
      1* alter rollback segment rbs04 online
    sys@LNX817>r
      1* alter rollback segment rbs04 online
    
    Rollback segment altered.
    
    sys@LNX817> select segment_name, status from dba_rollback_segs;
    
    SEGMENT_NAME                   STATUS
    ------------------------------ ----------------
    SYSTEM                         ONLINE
    RBS01                          ONLINE
    RBS02                          ONLINE
    RBS03                          ONLINE
    RBS04                          ONLINE

    now, I am not sure the difference of your scenario and mine

  5. #5
    Join Date
    Aug 2002
    Posts
    176
    Dear Pando
    Thanks for your reply ..
    But i have an active rollback segment in that datafile. all my rollback segments are only in that datafile except for the system.
    So it doen not allow me to drop the tablespace as well as create new tablespace or rollback segments

    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    SQL>
    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 23897356 bytes
    Fixed Size 70924 bytes
    Variable Size 6971392 bytes
    Database Buffers 16777216 bytes
    Redo Buffers 77824 bytes
    Database mounted.
    SQL> alter database datafile 'g:\oracle\oradata\fatma\rbs01.dbf' offline drop;

    Database altered.

    SQL> alter database open;

    Database altered.

    SQL> drop tablespace rbs including contents;
    drop tablespace rbs including contents
    *
    ERROR at line 1:
    ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace


    SQL> select s.username,t.xidusn,t.ubafil,t.ubablk from v$session s , v$transac
    on t where s.saddr= t.ses_addr;

    no rows selected

    SQL> select n.name from v$rollname n ,v$rollstat s where n.usn=s.usn;

    NAME
    ------------------------------
    SYSTEM

    SQL> alter rollback segment rbs0 offline;
    alter rollback segment rbs0 offline
    *
    ERROR at line 1:
    ORA-01598: rollback segment 'RBS0' is not online


    SQL> drop rollback segment rbs0;
    drop rollback segment rbs0
    *
    ERROR at line 1:
    ORA-01545: rollback segment 'RBS0' specified not available



    SQL> alter rollback segment rbs0 online;
    alter rollback segment rbs0 online
    *
    ERROR at line 1:
    ORA-01636: rollback segment 'RBS0' is already online


    SQL> drop rollback segment rbs0;
    drop rollback segment rbs0
    *
    ERROR at line 1:
    ORA-01545: rollback segment 'RBS0' specified not available


    SQL>


    SQL> create tablespace rbs2
    2 datafile 'g:\oracle\rbs01.dbf'
    3 size 10M autoextend on next 1M maxsize 50M
    4 default storage (initial 128k next 128k pctincrease 0 maxextents 1024)
    create tablespace rbs2
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'G:\ORACLE\ORADATA\FATMA\RBS01.DBF'


    SQL> create rollback segment rbs22 tablespace users;
    create rollback segment rbs22 tablespace users
    *
    ERROR at line 1:
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'G:\ORACLE\ORADATA\FATMA\RBS01.DBF'


    SQL> create public rollback segment rbs22 tablespace users;
    create public rollback segment rbs22 tablespace users
    *
    ERROR at line 1:
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'G:\ORACLE\ORADATA\FATMA\RBS01.DBF'


    SQL>

    Can this be overcome by any other method. Or can i remove this active rollback segments...

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well I only read till offline drop part

    my question is, who on earth would issue that command without losing the datafile as I showed in my test?

  7. #7
    Join Date
    Aug 2002
    Posts
    176
    who on earth would issue that command without losing the datafile as I showed in my test?
    oops ! sorry its my mistake to give the command...

    But is there a way to solve this scenario?

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  8. #8
    Join Date
    Aug 2002
    Posts
    176
    Even if i try to remove this datafile


    SQL> startup
    ORACLE instance started.

    Total System Global Area 23897356 bytes
    Fixed Size 70924 bytes
    Variable Size 6971392 bytes
    Database Buffers 16777216 bytes
    Redo Buffers 77824 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
    ORA-01110: data file 6: 'G:\ORACLE\ORADATA\FATMA\RBS02.DBF'


    SQL> alter database datafile 'g:\oracle\oradata\fatma\rbs02.dbf' offline drop;

    Database altered.

    SQL> alter database open;

    Database altered.

    SQL> drop tablespace rbs;
    drop tablespace rbs
    *
    ERROR at line 1:
    ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


    SQL> drop tablespace rbs including contents;
    drop tablespace rbs including contents
    *
    ERROR at line 1:
    ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace


    SQL>


    is there a method to remove the active rollback segment...

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    yes? look v$transaction and see which peep is doing DML and kill him?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width