-
I lost datafiles of rollback segment of Test DB and it is closed now => I can not drop the tablespace.
Database was not in archive log mode, but lossing of last changes is OK.
What is the best way to get database opened?
P.S.
So far I used 'CREATE CONTROLFILE' with entries for lost files removed, but got an error on 'ALTER DATABASE OPEN RESETLOGS':
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'G:\ORACLE\ORADATA\ORCC\SYSTEM01.DBF'
-
When you don't mind loosing recent data, if you have last night's backup, it might be OK to restore it.
-
Since you aren't in archivelog mode, your best bet is to restore from your last cold backup.
-
I have no cold backup (DB crashed before it was made but after it was created and application schema installed)
So is there a method to get rid of this error
"ORA-01194: file 1 needs more recovery to be consistent "?
What does is actually indicate?
-
have you tried alter database datafile xxxxx offline drop?
-
do not do what pando suggested unless otherwise you want to blow the tablespace off.
***ORACLENOTE***
"alter database datafile xxxxx offline drop" is not meant for removing the datafile at all. Its meant for removing datafile with the intention of dropping the tablespace.
******************
I am sorry to say guys. Oracle Don't mention this where the syntax is given as caution and lot people confused with this syntax.
-
well if lnd didnt shutdown the database with abort option he might as well drop the datafile with offline drop option then open the database and drop the rbs tablespace, at least with this he might have a chance to open the database...
-
Actually, the database (and OS) crashed.
The status is that I have all datafiles+control file except of rollback tablespace.
P.S. Generally this recovery is not a big deal, I just want to save some work and make some things more clear regarding db recovery.
What I do not understand is
why Oracle requires recovery for datafile when I try to open database. What in general should happen for this condition?
All considerations including some view into Oracle internals is welcommed.
-
startup mount
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\RBS01.DBF' OFFLINE DROP;
alter database open;
DROP TABLESPACE RBS INCLUDING CONTENTS;
CREATE TABLESPACE RBS
DATAFILE 'D:\ORACLE\ORADATA\ORCL\RBS02.DBF'
.........
.........
shutdown immediate
rename file D:\ORACLE\ORADATA\ORCL\RBS02.DBF to D:\ORACLE\ORADATA\ORCL\RBS01.DBF on NT
startup mount
alter database rename file
'D:\ORACLE\ORADATA\ORCL\RBS02.DBF'
to
'D:\ORACLE\ORADATA\ORCL\RBS01.DBF'
alter database open;
-
By the way, do a cold backup before you try.
