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

Method 1:

Start the database in mount state

SQL> startup mount;

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:ORACLEPRODUCT10.2.0ORADATADBTESTSYSTEM01.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 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arcORA-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_.arcORA-00310: archived log contains sequence 10; sequence 14 requiredORA-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 belowORA-01195: online backup of file 1 needs more recovery to be consistentORA-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 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arcORA-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_.arcORA-00310: archived log contains sequence 11; sequence 14 requiredORA-00334: archived log:'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_14/o1_mf_1_11_8n43qq5j_.arc'

3)SQL> recover database using backup controlfile until cancel;ORA-00279: change 766152 generated at 03/16/2013 12:12:04 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_14_%u_.arcORA-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_.arcORA-00279: change 769526 generated at 03/16/2013 12:48:13 needed for thread 1ORA-00289: suggestion :/u01/app/oracle/flash_recovery_area/DUPDB/archivelog/2013_03_16/o1_mf_1_15_%u_.arcORA-00280: change 769526 for thread 1 is in sequence #15ORA-00278: log file'/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_03_16/o1_mf_1_14_8n875owh_.arc' no longer needed for this recovery

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

Open the database in resetlog mode

SQL> alter database open resetlogs;

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 immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.

SQL> startup mountORACLE instance started.

Total System Global Area 530288640 bytesFixed Size 2131120 bytesVariable Size 310381392 bytesDatabase Buffers 209715200 bytesRedo Buffers 8060928 bytesDatabase mounted.

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

SQL> shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.

SQL> startup mountORACLE instance started.

Total System Global Area 530288640 bytesFixed Size 2131120 bytesVariable Size 310381392 bytesDatabase Buffers 209715200 bytesRedo Buffers 8060928 bytesDatabase 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