Recover from file with rollback segments
Bernd Boecker, December 05, 2001 - 4:59 am UTC
Hello Tom,
I appreciate your comments much. Here a further possibility for recovery a file with rollback segments:
Error scenario:
1. set transaction use rollback segment rb1;
2. INSERTS into's...
3. SHUTDOWN ABORT; (simulate Media errors)
4. Delete file rb1.ora (Tablespace RB1 with segment rb1 );
5. Backup of the file copy
Recover:
1. comment out INIT.ORA ROLLBACK_SEGMENT parameter , so ORACLE does not try to find the incorrect segment rb1
2. STARTUP MOUNT
3. ALTER DATABASE DATAFILE rb1.ora' OFFLINE;
4. ALTER DATABASE OPEN # now we are in business
5. CREATE ROLLBACK SEGMENT rbtemp TABLESPACE SYSTEM; # We need Temporary RBS for further steps;
6. ALTER ROLLBACK SEGMENT rbtemp ONLINE;
7. RECOVER TABLESPACE RB1;
8. ALTER TABLESPACE RB1 ONLINE;
9. ALTER ROLLBACK SEGMENT rb1 ONLINE;
10. ALTER ROLLBACK SEGMENT rbtemp OFFLINE;
11. DROP ROLLBACK SEGMENT rbtemp;
Result: Successfully rollback uncommitted Transactions, no suspect instance.
Best regards
Bernd
December 05, 2001 - 4:41 pm UTC
yes, that is standard archivelog mode backup recovery procedures -- if "backup of the file copy" means "restore rb1.ora from the last backup".
You simply recovered the datafile. In the question here, they start with "we are in noarchivelog mode" meaning there is no opportunity for recovery from media failure.
Your Help Needed ! ! !
Riaz Shahid, May 09, 2003 - 3:26 am UTC
Dear Tom !
I am running my database in NonArchivelog mode. One of datafiles of tablespace RBS got corrupted (as shown below). So i want to drop the rbs tablespace and re-create it but i am unable to do so as shown below:
C:\>svrmgrl
Oracle Server Manager Release 3.1.7.0.0 - Production
Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production
SVRMGR> connect / as sysdba
Connected.
SVRMGR> shutdown abort;
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 178436124 bytes
Fixed Size 75804 bytes
Variable Size 104882176 bytes
Database Buffers 73400320 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> select count(*) from cr.gstt05;
COUNT(*)
----------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter database datafile 'E:\ORACLE\ORADATA\STARR\RBS01.DBF' offline;
Statement processed.
SVRMGR> alter tablespace rbs offline;
alter tablespace rbs offline
*
ORA-01539: tablespace 'RBS' is not online
SVRMGR> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace
SVRMGR> alter tablespace rbs online
2> ;
alter tablespace rbs online
*
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter rollback segment rbs0 offline;
alter rollback segment rbs0 offline
*
ORA-01598: rollback segment 'RBS0' is not online
Note that i tried the media recovery but it asks for archived logs (that are not available since database was running in Noarchivelog mode). Please advise what should o do in order to re-create the tablespace RBS (and its datafiles). I have no physical backup for this database. However, i've logical backup for this database.
Moreover, how can i find the tables that are affected from this tablespace, using the data dictionary ?
(I am using 8.1.7.2.1 on Windows 2000 Server)
Waiting for your expert advice...
Riaz
May 09, 2003 - 1:23 pm UTC
you are, as they say, in a really bad place.
I hope you have cold backups.
You made a decision a long time ago that data loss is OK, when it happens (not if, but when) we are OK with that.
RBS is a vital integral, needed and necessary component of the database. You cannot simply "slice it out". We need it. It is part of our "inner being" if you will.
You cannot drop that rbs because it contains active rollback data (it was being used).
Time to contact support and open a tar -- or just goto your last full cold backup (hope you tested it)
Oh, I just read "and I've no backups" -- my expert advice -- contact support, they'll go over your options which are somewhat limited as there were active transactions in this rbs. All options will lead to "rebuild database" in all probability.
What about RBS corrupted during online tablespace backup??
Paulo Neto, August 30, 2004 - 6:05 pm UTC
Tom,
In our case, the server crashes during online tablespace backup. Then we recover database with the latest redo log files. The instance opened but after that we-ve got a lot of ORA-00600 error messages in alert.log (ORA-00600: internal error code, arguments: [4142], [65535], [], [], [], [], [], [] ).
This is what we´ve done :
- Set RB2 segment as corrupted
- Killed the pending transaction
- Restarted the database succesfully,
Since that (a week ago) the ORA-0600 disapears. So I ask you :
Is our instance in a suspect state ? If yes, how can we open our instance again in a non-suspected state without export/import procedures ?
August 30, 2004 - 6:28 pm UTC
the first thing you should have done, immediately, would have been to log a support call -- before using magical, undocumented, not understood init.ora parameters.
support would be able to help you out. you should never stuff an "_" parameter in your init.ora without support -- I don't do it. It is totally "not a good idea(tm)". I understand it makes people feel 'smart' cause they read about it on a web page, but it is alot like giving a kid whose never seen a gun a loaded gun to play with.
the sad part is, there may well have been a way out without something so drastic. you are in a bad position now.
since it seems you used the corrupted rollback segments parameter (which screams in every internal note "this is the last thing you want to do" (not the first).
the notes go onto say "now, hopefully you can export major portions of your database, cause the data is sort of in an "unknown" state here".
that is, you need to rebuild your database.
there is no magic to fix the mistake here, you've done a "not so good thing". Your database is suspect. Your *data integrity* even more so -- straight down to your data dictionary.
What about RBS corrupted during online tablespace backup??
Paulo Neto, August 31, 2004 - 9:30 am UTC
Tom, here goes some additional information for your knowledge. We did, open a support call at Oracle, however they offered us 2 ways out of this situation: restore and exp/imp, both solutions r not usefull for us (they take either too long or involve data loss). Thats why we did procede with this solution. At the moment our database is workinbg properly without any warnnings or problems. Is there any kind of database analyzis for us to run, we do not have time to exp/imp our database.
August 31, 2004 - 1:12 pm UTC
restore of a single tablespace takes too long?!? kidding right?
you suffered data loss and support should have mentioned that (have a tar number? I'd be interested in the conversation).
You must rebuild if you want to have "good stuff".
a restore cannot possibly take *too long* -- the alternative here is data loss?
you are backing up for a reason?