ORA-01194: file 1 needs more recovery to be consistent

Error log:

[oracle@localhost ~]$ sqlplus

Enter user-name: / as sysdba

SQL> shutdown abort;

SQL> startup nomount;

 SQL> alter database mount;

 SQL> alter database open;

 SQL> recover database using backup controlfile until cancel;

ORA-01507: database not mounted

SQL> shutdown abort;

 SQL> startup mount;

 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: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

Workaround for this error is to provide all the available archive log files to the recovery:

SQL> recover database using backup controlfile until cancel;

...

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Startup database in mount mode:

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.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

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

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

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.

Now try resetlogs:

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

When database started successfully and up then perform the following steps

Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. The steps are:

1. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> show parameter control;

SQL> CREATE UNDO TABLESPACE undo datafile '/u01/app/oracle/oradata/sa/undo_df1.dbf' size 200m autoextend on maxsize 30G;

Tablespace created.

SQL> alter system set undo_tablespace = undo scope=spfile;

System altered.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

 Now bounce your database.

SQL> shutdown immediate

SQL> startup

Cheers!!  Database started successfully.

Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME    STATUS       COUNT(*)

------------------------------ ---------------- ----------

UNDOTBS1                    OFFLINE                  10

SYSTEM                         ONLINE                    1

UNDO                           ONLINE                   10

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

sql> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name=UNDOTBS1;

sql> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';

For example:

sql> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

Verify and then drop:

sql> drop tablespace [tablespace_name] including contents and datafiles;

For example:

sql> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME    STATUS      COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                       ONLINE                    1

UNDO                           ONLINE                   10

SQL>