Open database not properly recovered _ALLOW_RESETLOGS_CORRUPTION
                                                 Last update (2011-02-22 12:51:24)
                                                                                                                   Date added (2009-04-27 11:34:30)

Summary
Restoring and recovering oracle database is very common especially in cases you have lost datafiles. An example follows where the database is missing for example 1 datafile. The steps are:
1. startup the database with nomount
2. Connect to RMAN catalog
3. Run a script which allocates some tape channels, restore the controlfile, mount the database, restore database, recover applying the archives and redologs.
4. At the end you have to open database with resetlogs;
STARTUP NOMOUNT;
rman TARGET / CATALOG rman/****@rman
RUN {
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE';
send 'NB_ORA_CLIENT=misdb';
restore CONTROLFILE;
alter database mount;
restore database;
recover database;
release channel ch01;
release channel ch02;
release channel ch03;
release channel ch04;
}
alter database open resetlogs;
exit;
While recovering the database you will be asked for some archives. A typical message like this occurs:
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /oracle1/MIS/648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 10 needs more recovery to be consistent
ORA-01110: data file 10: ˇ/oracle/oradata/MIS/data_01.dbf˘
ORA-01112: media recovery not started
At the archivelogs apply step as you notice i pressed CANCEL

When i tried to open the database with resetlogs i got the error.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 10 needs more recovery to be consistent
ORA-01110: data file 10: ˇ/oracle/oradata/MIS/data_01.dbf˘
There is a hidden parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE which will allow us to open database even though it˘s not properly recovered.
ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
Tip: Also change the undo_management to “Manual”

After the two changes in the spfile you can open the database with:
sqlplus "/ as sysdba"
startup force
Other links
How to find all the corrupted objects in your Database
Partially restore database
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search