Can we recover from Lost Rollback tablespace

Printable View

04-30-2003, 05:25 AM
Rohit

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 :confused:

Thanks
04-30-2003, 05:46 AM
akhadar

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.
04-30-2003, 09:27 AM
Rohit

Quote:

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
04-30-2003, 09:58 AM
pando

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
04-30-2003, 10:44 AM
Rohit

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
04-30-2003, 11:01 AM
pando

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?
04-30-2003, 11:42 AM
Rohit

Quote:

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
04-30-2003, 12:17 PM
Rohit

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
04-30-2003, 02:46 PM
pando

yes? look v$transaction and see which peep is doing DML and kill him?