If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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.

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

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