How to forcefully open database using _allow_resetlogs_corruption - ORA-1113 signalled during: alter database open resetlogs

Force database open : _allow_resetlogs_corrution

In real world scenario many times DBA comes to a point where he can’t open a database even with resetlogs. Or when he lost current redolog file. or due to some corruption.

In many situations - recreating controlfile and open resetlogs failed even though the chaeckpoint_change# in all datafile header is having unique value.

many situations where we need to tweak more after first run. but almost all time we opened a database using below method. This is the last way to open the database as per my knowledge.

Force open steps are:

1)      Backup the database while the database is closed.

THE INSTRUCTIONS HERE ARE DESTRUCTIVE. YOU ARE STRONGLY ADVISED TO BACKUP THE

DATABASE BEFORE PROCEEDING. IF YOU DO NOT DO THIS YOU MAY LOSE THE CHANCE TO

TRY OTHER OPTIONS.

2) Disable this database from EM if running. We also need to disable RAC to avoid Auto restart.

3) If your datafiles are from different points in time, it is best to try to

use system tablespace datafiles at a similar timestamp to the OLDEST files

you have online in the database. This reduces the chance that you will get

problems during the bootstrap phase of opening the database.

4) Edit your init.ora file to change undo_management and add two parameters.

* Change UNDO_MANAGEMENT=AUTO to

UNDO_MANAGEMENT=MANUAL

* Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.

* Add

CLUSTER_DATABASE=FALSE

JOB_QUEUE_PROCESSES=0

_ALLOW_RESETLOGS_CORRUPTION = TRUE

* If you only have a spfile available, you can from the closed, nomount or the

mount stage create an init.ora file as follows:

SQL> CREATE PFILE FROM SPFILE;

Do NOT edit the SPFILE.

5) Invoke SQL*Plus, startup mount, check that correct init.ora was used and

all datafiles are in the status of online or system.

$ sqlplus "/as sysdba"

SQL> startup mount pfile = (full path / file name to init.ora)

Confirm that the hidden parameters from step 3 were used:

SQL> show parameters corrupt

You should see both hidden parameters listed. If not, the wrong init.ora

may have been modified. Do not continue until "show parameters corrupt" shows

both hidden parameters.

SQL> show parameters undo

You should see undo management set to manual. If not, the wrong init.ora

may have been modified. Do not continue until "show parameters undo" shows

undo management as manual.

Check that all files you want to open with are listed as ONLINE or as SYSTEM.

SQL> select name, file#, status from v$datafile where status not in

('SYSTEM', 'ONLINE');

If any rows are returned from the query above, bring the file(s) online with:

SQL> ALTER DATABASE DATAFILE file# ONLINE;

and repeat until there are no files in an OFFLINE status. If any file remains or

changes into "recover" status after you try to online the file proceed to step 6.

6) Perform a fake incomplete recovery then open the database with resetlogs.

SQL> recover database using backup controlfile until cancel;

WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER.

SQL> ALTER DATABASE OPEN RESETLOGS;

7) If the database opens try selecting from a table. For example:

SQL> SELECT SYSDATE FROM DUAL;

If you get a row back the database is open and "functional". If you wish, you

may try to select from a other tables to make sure the database is functional

enough for the required export.

With the database open and functional you should attempt to export the database

IMMEDIATELY. Since database is unstable, don't try another shutdown/startup unless needed.

Once you have an export the database MUST be recreated from scratch.

This means dropping and deleting ALL datafiles and creating a new database from

scratch.

A database which has been opened in this way but not rebuilt will not be

supported by Oracle. Any delay in exporting the contents or any attempt to

use the system may cause irreparable damage.

NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3

OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME

INIT.ORA FILE.

8) If the instance crashed in the open phase of step 5, check for trace files

in the background dump destination. If you find a trace file, check to see if

the trace file has an ORA-00600 [2662] or ORA-00600 [4000] error in it.

Either of these errors may also be seen in the alert.log file.

If you see the ORA-00600 [2662] or ORA-00600 [4000] error, provide Oracle Support

Services the full error message. Oracle Support Services will provide steps to advance

the SCN using a hidden parameter.

NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3

OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME

INIT.ORA FILE.

*************************************************************************

* *

* CAUTION: Once the database is open, it is imperative that you export, *

* rebuild the database, and import. *

* *

* By forcing open the database in this fashion, there is a strong *

* likelihood of logical corruption, possibly affecting the data *

* dictionary. Oracle does not guarantee that all of the data will be *

* accessible nor will it support a database that has been opened by *

* this method and that the database users will be allowed to continue *

* work. All this does is provide a way to get at the contents of the *

* database for extraction, usually by export. It is up to you to *

* determine the amount of lost data and to correct any logical *

* corruption issues. *

* *

*************************************************************************

useful script to collect the database recovery information:

spool /tmp/current_status_info_new_2.txt
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
archive log list;
select name,dbid,controlfile_type,open_mode,log_mode,checkpoint_change#,archive_change# from v$database;
select * from v$database_incarnation;
col name for a75
select * from v$restore_point;
select flashback_on from v$database;
select parallel from v$instance;
select protection_level from v$database;
select * from dba_streams_administrator;
select file#,name,status,checkpoint_change#,enabled from v$datafile;
select file#,name,status,enabled from v$tempfile;
select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,FLASHBACK_ON from v$tablespace;
select * from v$recover_file;
select * from v$backup;
select * from v$log;
select * from v$logfile;
select file#,name,recover,fuzzy,checkpoint_change#,creation_change#,checkpoint_time,creation_time,RESETLOGS_TIME,status from v$datafile_header;
select status, to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*) from v$datafile_header
group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time;
select count(*),fhsta from x$kcvfh group by fhsta;
select count(*),fhrba_seq from x$kcvfh group by fhrba_seq;
select count(*),fhscn from x$kcvfh group by fhscn;
select count(*),fhafs from x$kcvfh group by fhafs;
select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ;
select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;
select name, status , sequence#, thread#,
TO_CHAR(first_change#, '999999999999999999') as first_change#,
TO_CHAR(next_change#, '999999999999999999') next_change#,
to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') completion_time
from v$archived_log where (select min(checkpoint_change#) from v$datafile_header) between first_change# and next_change#;
spool off
exit 

I hope this helps. Happy recovery :)