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

 
Method 1:
 

1) Start the database in mount state
 

SQL> startup mount;
 

2) Recover the database.
 

SQL> recover database;
 

If you come across below error
 

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 
 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBTEST\SYSTEM01.DBF’
 

then, do the following
 

1)
 
SQL> recover database using backup controlfile until cancel;
 
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
 
ORA-00289: suggestion :
 
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
 
ORA-00280: change 766152 for thread 1 is in sequence #14
 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc
 
ORA-00310: archived log contains sequence 10; sequence 14 required
 
ORA-00334: archived log:
 
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_10_8n43no4v_.arc'
 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 
ORA-01195: online backup of file 1 needs more recovery to be consistent
 
ORA-01110: data file 1:
 
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'
 

2)
 
SQL> recover database using backup controlfile until cancel;
 
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
 
ORA-00289: suggestion :
 
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
 
ORA-00280: change 766152 for thread 1 is in sequence #14
 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc
 
ORA-00310: archived log contains sequence 11; sequence 14 required
 
ORA-00334: archived log:
 
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc'
 

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
 
ORA-01195: online backup of file 1 needs more recovery to be consistent
 
ORA-01110: data file 1:
 
'/u01/app/oracle/oradata/DUPDB/datafile/o1_mf_system_7qm3ck4o_.dbf'
 

3)
 
SQL> recover database using backup controlfile until cancel;
 
ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1
 
ORA-00289: suggestion :
 
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arc
 
ORA-00280: change 766152 for thread 1 is in sequence #14
 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875owh_.arc
 
ORA-00279: change 769526 generated at 03/16/2013 12:48:13 needed for thread 1
 
ORA-00289: suggestion :
 
/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_15_%u_.arc
 
ORA-00280: change 769526 for thread 1 is in sequence #15
 
ORA-00278: log file
 
'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875
 
owh_.arc' no longer needed for this recovery
 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 
cancel
 
Media recovery cancelled.
 

3) Open the database in resetlog mode
 

SQL> alter database open resetlogs;
 

4) Check the status
 

SQL> select instance_name, status from v$instance;
 

INSTANCE_NAME    STATUS
 
------------------------   -------------
 
DUPDB                  OPEN
 

SQL> select name, open_mode from v$database;
 

NAME      OPEN_MODE
 
----------    ------------------
 
DUPDB     READ WRITE


Method 2:
 

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

/link/ora-01194-file-1-needs-more-recovery-to-be-consistent