ORA-01194 and ORA-01110

ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\GEET\ORADATA\TEST\SYSTEM01.DBF'

Steps to follow-


- Backup the controlfile to make the changes.
backup controlfile to trace;

- Make the necessary changes in the control file

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\APP\GEET\ORADATA\TEST\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'C:\APP\GEET\ORADATA\TEST\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 'C:\APP\GEET\ORADATA\TEST\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  'C:\APP\GEET\ORADATA\TEST\SYSTEM01.DBF',
  'C:\APP\GEET\ORADATA\TEST\SYSAUX01.DBF',
  'C:\APP\GEET\ORADATA\TEST\UNDOTBS01.DBF',
  'C:\APP\GEET\ORADATA\TEST\USERS01.DBF',
  'C:\APP\GEET\ORADATA\TEST\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;

- copy the datafiles to the primary location
- startup database nomount;
- execute controlfile script
- alter database open resetlogs;

ORA-01194 and ORA-01110

ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\GEET\ORADATA\TEST\SYSTEM01.DBF'


Steps to follow

- SQL> shut immediate;

ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

- Add the below parameter to the pfile.
_allow_resetlogs_corruption = true

- Startup the database using pfile.
SQL> startup mount pfile='C:\app\Geet\product\11.2.0\dbhome_2\database\inittest..ora

- Open the database with resetlogs.
SQL> alter database open resetlogs;

- Shutdown the database
SQL> shut immediate;

- Remove the parameter _alllow_resetlogs_corruption = true form pfile.
- Startup the database normally

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 12 10:17:25 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

remove the paameter

SQL> startup
ORACLE instance started.

Total System Global Area 3390558208 bytes
Fixed Size                  2180464 bytes
Variable Size            1862273680 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16154624 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 test
GEET-PC
11.2.0.1.0        12-JAN-13 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO