We have an Oracle database, one data file (users tablespace) of the instance got corrupt and another data file (containing application data) was deleted due to some issue.
The database is in ARCHIVELOG mode, but we neither have data file backup of both data files nor the pertaining archive logs. Hence, I guess we cannot go ahead with the recovery of data files. I am unable to open the database as the data files are not available and face.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
I am just able to STARTUP MOUNT the database
I wanted to ask if we can somehow we can open the database by taking both the tablespaces offline after STARTUP MOUNT the database instance. Or if there is any other way I can start the database.
There was a power issue and the server got abruptly shutdown. When we tried to restart the database and found that we were facing ORA-01589: must use RESETLOGS or NORESETLOGS option for database open. On further analysis we found that 2 of datafiles were creating problem as one got corrupt other deleted as mentioned. As of now if I try to restart the database it says one datafile which belongs to users tablespace need recovery. I wanted to confirm as if we can start the instance by bringing the tablespace offline.
I tried:
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
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: '/flowsystems/oradata02/flsta02/system01.dbf'
I don't have any archive logs.
Best Answer
What you can try is the following while your database is in the MOUNTED estate
and the same for the application tablespace you lost.
NOTE:If there is no backup and no way to restoring the lost datafiles, what you can do is backup any other important datafile/tablespace and recreate the database. I think it will be the less painful way to get a fully working database.