"_allow_resetlogs_corruption" recovery....

Please DO NOT use this in any production environment without Oracle Support...

*** Using this parameter should be your LAST option as you do not have any proper backups to bring your database online ***

One of our database was kind of un-opened mode due to system tablespace datafile corruption (for any reason like restored from old backup etc.,)

We are OK to loose some data and some tablespaces which were created later point than the SCN that system tablespace has. So, used the parameter "_allow_resetlogs_corruption" an un-documented (repeat Oracle will not support your database if you recover your database with this option without their support) parameter. Below these errors shows the use of this parameter and I was able to recover database and open.

Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/oradata/db_name/datafile//system.282.757162799'

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradata/db_name/datafile/system.282.757162799'

Use of the parameter:


SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1469792256 bytes
Fixed Size                  2121312 bytes
Variable Size            1107296672 bytes
Database Buffers          352321536 bytes
Redo Buffers                8052736 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 19 was created in the future of incomplete recovery
ORA-01110: data file 19: '/oradata/db_name/datafile/file2.dbf'

I know for sure I do not need to worry about this datafile as I have data that I can put back in so marking it as OFFLINE.

SQL> alter database datafile 19 offline;

Database altered.

Now Open with resetlogs and you should be good. Take a FULL Backup ASAP.

SQL> alter database open resetlogs;

Database altered.

SQL>

Once again, Please do not use this parameter in any production or critical databases without Oracle Support and recommendation otherwise you will end up with inconsistency database.