Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01113: file 1 needs media recovery and ORA-01122: database file 1 failed verification check

User_7L9DTApr 13 2014 — edited Apr 21 2014

Coming from this post ( ORA-01113: file 1 needs media recovery )

Platform: AIX 5.3
EBS 11.5.9
DB 9i

no archive log mode / test environment / no backup

System had two power failures and after the power failure, control file inconsistency was reported which was resolved by following the thread ( ) and then this error came up

Starting Database again gave this error








oraprod@clodb:/home/oraprod>./startup.sh






You are running addbctl.sh version 115.8










Starting the database PROD ...










SQL*Plus: Release 9.2.0.3.0 - Production on Sun Apr 13 21:55:17 2014






Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.






Connected to an idle instance.


ORACLE instance started.






Total System Global Area  615482512 bytes


Fixed Size                   742544 bytes


Variable Size             436207616 bytes


Database Buffers          167772160 bytes


Redo Buffers               10760192 bytes


Database mounted.


ORA-01122: database file 1 failed verification check


ORA-01110: data file 1: '/oradata/proddata/system01.dbf'


ORA-01207: file is more recent than controlfile - old controlfile










Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production


With the Partitioning, OLAP and Oracle Data Mining options


JServer Release 9.2.0.3.0 - Production






addbctl.sh: exiting with status 0










You are running addlnctl.sh version 115.6






Logfile: /oratech/proddb/9.2.0/appsutil/log/PROD_clodb/addlnctl.txt






Starting listener process PROD ...










addlnctl.sh: exiting with status 0

Followed the instructions on this link for ( Oracle Newbie DBA / Apps DBA: ORA-01122: database file 1 failed verification check   )

ORA-01122: database file 1 failed verification check











oraprod@clodb:/oratech/proddb/9.2.0>sqlplus /nolog






SQL*Plus: Release 9.2.0.3.0 - Production on Sun Apr 13 22:43:59 2014






Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.






SQL> connect sys/sys as sysdba


Connected.


SQL> alter database backup controlfile to trace;






Database altered.






SQL> show parameter user_dump_dest;






NAME                                 TYPE        VALUE


------------------------------------ ----------- ------------------------------


user_dump_dest                       string      /oratech/proddb/9.2.0/admin/PR


                                                 OD_clodb/udump


SQL> startup nomount;


ORA-01081: cannot start already-running ORACLE - shut it down first


SQL> shutdown immediate;


ORA-01109: database not open










Database dismounted.


ORACLE instance shut down.


SQL> startup nomount;


ORACLE instance started.






Total System Global Area  615482512 bytes


Fixed Size                   742544 bytes


Variable Size             436207616 bytes


Database Buffers          167772160 bytes


Redo Buffers               10760192 bytes


SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  NOARCHIVELOG


--  SET STANDBY TO MAXIMIZE PERFORMANCE


  2      MAXLOGFILES 32


  3    4      MAXLOGMEMBERS 5


  5      MAXDATAFILES 512


  6      MAXINSTANCES 8


  7      MAXLOGHISTORY 7260


  8  LOGFILE


  9    GROUP 1 (


10      '/oradata/proddata/log01a.dbf',


11      '/oradata/proddata/log01b.dbf'


12    ) SIZE 10M,


13    GROUP 2 (


14      '/oradata/proddata/log02a.dbf',


15      '/oradata/proddata/log02b.dbf'


16    ) SIZE 10M,


17    GROUP 3 (


18      '/oradata/proddata/log03a.dbf',


19      '/oradata/proddata/log03b.dbf'


20    ) SIZE 20M


21  -- STANDBY LOGFILE


22  DATAFILE


23    '/oradata/proddata/system01.dbf',


{snipped many names of the files}


  '/oratech/applsysd.dbf'


390  CHARACTER SET AR8ISO8859P6


;391  392  393  394  395  396  397  398  399  400  401  402  403  404  405  406  407  408  409  410  411  412  413  414  415  416  417  418  419  420  421  422  423  424  425  426  427  428  429  430  431  432  433  434  435  436  437  438  439  440  441  442  443  444  445  446  447






Control file created.






SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;


ORA-00279: change 3914776964 generated at 04/09/2014 13:27:50 needed for thread


1


ORA-00289: suggestion : /oratech/proddb/9.2.0/dbs/arch1_10090.dbf


ORA-00280: change 3914776964 for thread 1 is in sequence #10090










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}






ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;


ORA-00279: change 3914776964 generated at 04/09/2014 13:27:50 needed for thread


1


ORA-00289: suggestion : /oratech/proddb/9.2.0/dbs/arch1_10090.dbf


ORA-00280: change 3914776964 for thread 1 is in sequence #10090










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


AUTO


ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










SQL> ALTER DATABASE OPEN RESETLOGS;


ALTER DATABASE OPEN RESETLOGS


*


ERROR at line 1:


ORA-01113: file 1 needs media recovery


ORA-01110: data file 1: '/oradata/proddata/system01.dbf'










SQL> recover database until cancel using backup controlfile;


ORA-00279: change 3914776964 generated at 04/09/2014 13:27:50 needed for thread


1


ORA-00289: suggestion : /oratech/proddb/9.2.0/dbs/arch1_10090.dbf


ORA-00280: change 3914776964 for thread 1 is in sequence #10090










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


AUTO


ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










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: '/oradata/proddata/system01.dbf'










SQL> recover database until cancel using backup controlfile;


ORA-00279: change 3914776964 generated at 04/09/2014 13:27:50 needed for thread


1


ORA-00289: suggestion : /oratech/proddb/9.2.0/dbs/arch1_10090.dbf


ORA-00280: change 3914776964 for thread 1 is in sequence #10090










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


/oratech/proddb/9.2.0/dbs/arch1_10089.dbf


ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10089.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}






ORA-00308: cannot open archived log '/oratech/proddb/9.2.0/dbs/arch1_10090.dbf'


ORA-27037: unable to obtain file status


IBM AIX RISC System/6000 Error: 2: No such file or directory


Additional information: 3










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: '/oradata/proddata/system01.dbf'










SQL> recover database until cancel using backup controlfile;


ORA-00279: change 3914776964 generated at 04/09/2014 13:27:50 needed for thread


1


ORA-00289: suggestion : /oratech/proddb/9.2.0/dbs/arch1_10090.dbf


ORA-00280: change 3914776964 for thread 1 is in sequence #10090










Specify log: {<RET>=suggested | filename | AUTO | CANCEL}


/oradata/proddata/log01a.dbf


ORA-00310: archived log contains sequence 10089; sequence 10090 required


ORA-00334: archived log: '/oradata/proddata/log01a.dbf'










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: '/oradata/proddata/system01.dbf'










SQL> Alter database recover datafile /oradata/proddata/system01.dbf


  2  ;


Alter database recover datafile /oradata/proddata/system01.dbf


                                *


ERROR at line 1:


ORA-02236: invalid file name










SQL> Alter database recover datafile  '/oradata/proddata/system01.dbf';


Alter database recover datafile  '/oradata/proddata/system01.dbf'


*


ERROR at line 1:


ORA-00283: recovery session canceled due to errors


ORA-01610: recovery using the BACKUP CONTROLFILE option must be done










SQL> 


SQL> select * from v$log;






    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS


---------- ---------- ---------- ---------- ---------- --- ----------------


FIRST_CHANGE# FIRST_TIM


------------- ---------


         1          1          0   10485760          2 YES UNUSED


            0






         2          1          0   10485760          2 YES UNUSED


            0






         3          1          0   20971520          2 YES INVALIDATED


            0






SQL> select * from v$logfile;






    GROUP# STATUS  TYPE


---------- ------- -------


MEMBER


--------------------------------------------------------------------------------


         3 STALE   ONLINE


/oradata/proddata/log03a.dbf






         3 STALE   ONLINE


/oradata/proddata/log03b.dbf






         2 STALE   ONLINE


/oradata/proddata/log02a.dbf










    GROUP# STATUS  TYPE


---------- ------- -------


MEMBER


--------------------------------------------------------------------------------


         2 STALE   ONLINE


/oradata/proddata/log02b.dbf






         1 STALE   ONLINE


/oradata/proddata/log01a.dbf






         1 STALE   ONLINE


/oradata/proddata/log01b.dbf










6 rows selected.






SQL>

Any thoughts how I can resolve this problem.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2014
Added on Apr 13 2014
#general-database-discussions
17 comments
16,581 views