If you got an error “ORA-01194: file 1 needs more recovery to be consistent” after the SQL command “alter database open resetlogs” you should check the related solution before trying anything else…
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: ‘G:\ORACLE\<SID>\SAPDATA1\SYSTEM_1\SYSTEM.DATA1′
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> desc v$recover_file
Name Null? Type
—————————————– ——– —————————-
FILE# NUMBER
ONLINE VARCHAR2(7)
ONLINE_STATUS VARCHAR2(7)
ERROR VARCHAR2(18)
CHANGE# NUMBER
TIME DATE
SQL> select min(change#),max(change#) from v$recover_file;
MIN(CHANGE#) MAX(CHANGE#)
———— ————
751133853 751133853
SOLUTION ;
Add following line to INIT.ORA file
_allow_resetlogs_corruption=true
SQL> shutdown immediate
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.6168E+10 bytes
Fixed Size 2271072 bytes
Variable Size 2181040288 bytes
Database Buffers 1.3959E+10 bytes
Redo Buffers 26329088 bytes
Database mounted.
SQL>alter database open resetlogs;
Database altered.
Thanks a lot for wonder full solution
I have done but with “alter database open resetlogs upgrade;”
my database has been opened but in restricted mode and I wont allow me to open database normal
what should I do
Please help me
Excellent and simple solution thanks for the help
I am glad if you solved your problem…Best Regards..
Hello Muhammad ,
If your current database version is newer than the backup datafiles then upgrade
steps must be taken ;
Run the upgrade script
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
Close the current sqlplus connection and open new one.
Try to shutdown and open DB
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
If system can do this and situation fits your structure then execute the post upgrade steps.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
Thank you very much. I have only added one more command inbetween as I am using SPfile.
I have created a SPfile from Pfile after adding the line _allow_resetlogs_corruption=true.
My database altered to OPEN. Thank you very much.
Hi Buddy
I was trying to copy data files from a live instance and & to created database by creating a new control file manually.
Whole my last three attempts I had to give up as I was always stuck by “ORA-01194: file 1 needs more recovery to be consistent”. Well not anymore, I know I cannot do this with a production instance, yet, managing to bring up an instance using such a hack gives an awesome feeling. Thank you
Hi Raj ,
On prod system, take the database backup mode via sql command “ALTER DATABASE BEGIN BACKUP;”
Copy all the data files from PROD to TEST , changed PROD database mode to end backup via “ALTER DATABASE END BACKUP;” , if you are using the same SID then just recover the database with following commands ;
SQL>startup nomount
SQL>alter database mount;
SQL>recover database using backup controlfile until cancel;
(Provide archive files)
…
SQL>Cancel
Media recovery cancelled.
SQL>alter database open resetlogs;
If you are going to change your SID , then create a trace file from controlfile on PROD via following SQL command ;
SQL>alter database backup controlfile to trace;
Edit the controlfile trace and change SID (IF needed file locations) then ;”
SQL> startup nomount
execute the tracefile as script like below ;
CREATE CONTROLFILE REUSE SET DATABASE “
RESETLOGS NOARCHIVELOG
.
.
.
Control file created
SQL> alter database open;