2

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.

migrated from stackoverflow.com Oct 28 '15 at 11:42

This question came from our site for professional and enthusiast programmers.

2

What you can try is the following while your database is in the MOUNTED estate

alter tablespace USERS offline;

and the same for the application tablespace you lost.

recover datafile 1;
alter database open;
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.

1

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

First check the open_mode of the database:

select open_mode from v$database;

If your database is mounted, then you need to open resetlogs because of a bad recovery:

alter database open resetlogs;

And then open the database:

alter database open;

You may need to execute recover database; before alter database open resetlogs;

The recovery was incomplete as the memory was not sufficient. You need to add more files and again recover the database first. Try method 2 mentioned in ORA-01194: file 1 needs more recovery to be consistent by Neeraj Gupta (reproduced below):

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> CREATE UNDO TABLESPACE undo1 datafile '<ora_data_path>undo1_1.dbf' size 200m autoextend on maxsize unlimited;

Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;
System altered.

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shutdown immediate

SQL> startup

Not the answer you're looking for? Browse other questions tagged or ask your own question.