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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle initialization in progress due to logfile corruption - startup error

BilalJan 4 2010 — edited Jan 7 2010
Hi All!

I m using Oracle Release 9.2.0.1.0. Due to power outage, it seems that one of its Redo files is corrupt and it is not getting started. My database is running in no archive mode and I donot have any backup for my data.
I have performed the following action but in vain. Please help me to get it started.

Thanks in advance.
Muhammad Bilal

--------------------------------------------------------------------------------------------------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jan 4 19:22:16 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> show user
USER is "SYS"
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 88880 change 182882946 time 01/04/2010
08:33:19
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 911 NO CURRENT
2 909 NO INACTIVE
3 910 NO INACTIVE

SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'D:\ORACLE\ORADATA\DB\REDO01.LOG';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'D:\ORACLE\ORADATA\DB\REDO01.LOG'
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'D:\ORACLE\ORADATA\DB\REDO01.LOG';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'D:\ORACLE\ORADATA\DB\REDO01.LOG'
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO01.LOG';
alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO01.LOG'
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> select member,status from v$logfile;

MEMBER STATUS
-------------------------------------------------- -------
D:\ORACLE\ORADATA\DB\REDO03.LOG STALE
D:\ORACLE\ORADATA\DB\REDO02.LOG
D:\ORACLE\ORADATA\DB\REDO01.LOG

SQL> alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO03.LOG';
Database altered.

SQL> alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO01.LOG';
alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO01.LOG'
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'

SQL> alter database clear logfile 'D:\ORACLE\ORADATA\DB\REDO02.LOG';
Database altered.

SQL> recover database until cancel;
ORA-00279: change 182763162 generated at 01/03/2010 20:00:21 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC00911.001
ORA-00280: change 182763162 for thread 1 is in sequence #911

Specify log: {<RET>=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: 'D:\ORACLE\ORADATA\DB\SYSTEM01.DBF'
ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DB\SYSTEM01.DBF'

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 88880 change 182882946 time 01/04/2010 08:33:19
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\DB\REDO01.LOG'


SQL> recover database until cancel;
ORA-00279: change 182882944 generated at 01/04/2010 08:33:10 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC00911.001
ORA-00280: change 182882944 for thread 1 is in sequence #911

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log 'D:\ORACLE\ORA92\RDBMS\ARC00911.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

ORA-00308: cannot open archived log 'D:\ORACLE\ORA92\RDBMS\ARC00911.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

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: 'D:\ORACLE\ORADATA\DB\SYSTEM01.DBF'

SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 911 NO CURRENT
2 0 NO UNUSED
3 0 NO UNUSED

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER SYSTEM CHECKPOINT GLOBAL
*
ERROR at line 1:
ORA-01109: database not open

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 182763162 generated at 01/03/2010 20:00:21 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC00911.001
ORA-00280: change 182763162 for thread 1 is in sequence #911
Specify log: {<RET>=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: 'D:\ORACLE\ORADATA\DB\SYSTEM01.DBF'
ORA-01112: media recovery not started


SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\DB\SYSTEM01.DBF'
---------------------------------------------------------------------------------------------------------------------------------

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 4 2010
Added on Jan 4 2010
#11g, #9i, #database, #dba, #general-database-discussions, #oracle, #security
15 comments
2,309 views