-
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
-
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.
-
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
-
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
-
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
-
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?
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|