Saturday, May 17, 2008

ORA-00313: open failed for members of log group

Error Description:
------------------------

Today one of my developer said that I am getting the following error from database. He send me the message with error number,
ORA-00257: archiver error. Connect internal only, until freed.

I immediately look for V$RECOVERY_FILE_DEST as per ORA-00257 Archiver Error and I saw that there is enough space available there. So, I look for alert log. There is the error entry,

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


Cause of The Problem:
-------------------------------

The developer has ran a huge insert in my database which made full of the redo log.

Solutions of The Problem:
--------------------------------

Solution 1)Use Nologging operation while doing huge DML operation.And insert make as as usual.

ALTER TABLE TABLE_NAME NOLOGGING;
run DML statement and then
ALTER TABLE TABLE_NAME LOGGING;


Solution 2)Increase the redo log size.
ALTER DATABASE ADD LOGFILE '/oradata/redo1.log' SIZE 100M;

Solution 3)Tune the statements.
Tune the DML statement to use less redo, or divide the statement into smaller ones will also help to avoid error.

1 comment:

  1. Arju Bhai, Face Book e dekhe ekane aslam. besh kajer mone hocche. aste aste dekbo..

    ReplyDelete