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
Comments
Tags: database, file 1 needs more recovery to be consistent, Ora Error, ORA-01194, ORA-01194: file 1 needs more recovery to be consistent, Oracle Apps, Oracle Database, Oracle DBA, oracle recovery, oracle recovery to be consistent, recovery to be consistent