Steps to used parameter allow resetlogs corruption in oracle

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.

 

Advertisements

One thought on “Steps to used parameter allow resetlogs corruption in oracle

  1. Pingback: ORA-01194: file 1 needs more recovery to be consistent | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.