Tag Archives: ora-00312

ORA-00742: Log read detects

ORA-00742: Log read detects ( corrupt Redo header )

Oracle database is not open, when i tried to open the database it’s crashed, upto mount state its working fine.
When try to open fire it throw the following error in alert log:

Error in Alert Log File:

Errors in file E:\ORACLE12C\diag\rdbms\ic\ic\trace\ic_ora_4408.trc:
ORA-00742: Log read detects lost write in thread 1 sequence 195 block 37645
ORA-00312: online log 3 thread 1: 'E:\ORACLE12C\ORADATA\IC\REDO03B.LOG'
ORA-00312: online log 3 thread 1: 'E:\ORACLE12C\ORADATA\IC\REDO03A.LOG'
Wed Mar 15 18:20:39 2017
ARCH: All Archive destinations made inactive due to error 742
Committing creation of archivelog 'D:\ORACLE\ARCHIVELOGS\ARC195_934509462_1.ARC' (error 742)

Solution 1:
1.You can try to clean the archive redo log with following commands:

alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 3;

2. Switch the logfile for verify:

alter system switch logfile;

Solution 2:
Its seems to be redo log file corrupted during instance crash recovery or something else case.

1. Tried for drop the redo log and create new one

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
When fired upper command, it show me archiving is not completed for redo file,its not dropped my logfile group.

2. Recover the database with following command:

SQL>RECOVER DATABASE UNTIL CANCEL;
Media recovery complete.

3. open the database:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL> alter database open resetlogs;
Database altered.

4. Check the redo log files and fire switch command for more sure.

select MEMBER from v$logfile;SELECT GROUP#, MEMBERS, STATUS, ARCHIVED FROM V$LOG;

alter system switch logfile;

Advertisements

ORA-00313 : Lost redo logs file of the database in Oracle

ORA-00313: open failed for members of log group 1 of thread 1

During open the database got the following error:

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/oradata/KSDQA/redo01.log'
ORA-27037: unable to obtain file status

Step 1. Start the database at mount state:

Start the database.
startup mount;

Step 2: Check the location of redo log and add new log files

alter database add logfile group 4 '/u02/oradata/KSDQA/redo04.log' size 250M;
alter database add logfile group 5 '/u02/oradata/KSDQA/redo05.log' size 250M;
alter database add logfile group 6 '/u02/oradata/KSDQA/redo06.log' size 250M;

Step 3: Start the database in open mode:

alter database open;
-- IF not then try with resetlog option:
alter database open resetlogs;

Note: If even not start database then go to step 4
 
Step 4 : If database is not up then proceed with following steps:

create pfile from spfile;

Step 5: Take a cold backup of database before proceed:
Note: Take full backup of all datafiles, means copy the current datafiles into new location
 
Step 6: Edit the pfile and add the following parameters:

*.undo_management='MANUAL'
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE

Remove the *.Undo_management='AUTO' 

Step 7: Start database with new pfile

startup mount pfile=location

Step 8: Start the following recover command:

recover database using BACKUP CONTROLFILE until cancel;
Ask for auto,cancel option:
CANCEL

Step 9: Finally open the database with resetlogs:

alter database open resetlogs;

Step 10: Checked the resetlog file are created in location

select * from v$logfile;

Step 11: Shutdown the database:

Shutdown immediate;

Step 12: Start with spfile:

Start with spfile
Startup

Step 13: Remove the extra created group

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;