Steps to used _allow_resetlogs_corruption parameter
Note: Oracle is not recommend to use the parameter _allow_resetlogs to force fully up the database. If you do not find any other solution to up the database then only you can try this parameter. But before use this take a cold backup of your all data files so that in case you need to old stage you can able to provide them.
Step 1: Create PFILE from SPFILE.
Create pfile='D:\oracle\init.ora' from spfile;
Step 2: Edit the following pfile parameter.
*.undo_management='MANUAL'
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE
Step 3: Shutdown the database.
shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Step 4: Mount the database with PFILE which edited.
startup mount pfile='/u01/app/oracle/10gee/dbs/initOID1POPR.ora';
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Step 5: Show the parameter of UNDO and PFILE:
show parameter undo
NAME TYPE VALUE
--------------- ------- -------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS
SQL> show parameter pfile
NAME TYPE VALUE
------- ------- ---------
spfile string
Step 6: Start the Recover process of database and choose cancel.
SQL> recover database using BACKUP CONTROLFILE until cancel;
ORA-00279: change 427952212 generated at 03/20/2013 21:04:01 needed for thread1
ORA-00289: suggestion : /archive/OID1POPR/1_27397_690902190.dbf
ORA-00280: change 427952212 for thread 1 is in sequence #27397
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
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: '/u02/oradata/OID1POPR/system01.dbf'
ORA-01112: media recovery not started
Step 7: It will take some time and end with below message.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Step 8: Exit the SQLPLUS and open new session
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
Step 9: Open the New session
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 22 13:35:47 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 1174405720 bytes
Database Buffers 385875968 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
Pingback: ORA-01194: file 1 needs more recovery to be consistent | Smart way of Technology