Recreate lost redo log or controlfile

What you do if you lose a online redo log or controlfile (just one or one member of a group)

Tests with 11g and dNFS.

Example 1: After a controlfile is lost you will see the following messages in alert log and after ~3 seconds the db will shutdown itself

Tue Oct 20 13:37:20 2009
Direct NFS: Read FAIL status  70
Direct NFS: NFS3ERR 70 error encountered. path fas-0002 mntport 4046 nfsport 2049
Errors in file /oracle/admin/diag/rdbms/testdb1/TESTDB1/trace/TESTDB1_lgwr_11804.trc:
ORA-00202: control file: '/oracle/oradata/TESTDB1/control2/control02.ora'
ORA-17500: ODM err:KGNFS READ FAIL
Errors in file /oracle/admin/diag/rdbms/testdb1/TESTDB1/trace/TESTDB1_lgwr_11804.trc:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '/oracle/oradata/TESTDB1/control2/control02.ora'
ORA-17500: ODM err:KGNFS READ FAIL
LGWR (ospid: 11804): terminating the instance due to error 204
Instance terminated by LGWR, pid = 11804

Solution: Copy the other controlfile or adapt the parameter control_files

Example 2: A online redo log member is lost. you will see the following msg in alert log each time a log switch happens, but db is still running

Tue Oct 20 13:51:14 2009
Direct NFS: write FAILED 70
Direct NFS: NFS3ERR 70 error encountered. path fas-0002 mntport 4046 nfsport 2049
Errors in file /oracle/admin/diag/rdbms/testdb1/TESTDB1/trace/TESTDB1_lgwr_12637.trc:
ORA-00345: redo log write error block 55 count 1
ORA-00312: online log 2 thread 1: '/oracle/oradata/TESTDB1/redo2/log2.dbf'
ORA-17500: ODM err:KGNFS WRITE FAIL
Errors in file /oracle/admin/diag/rdbms/testdb1/TESTDB1/trace/TESTDB1_lgwr_12637.trc:
ORA-00346: log member marked as STALE and closed
ORA-00312: online log 2 thread 1: '/oracle/oradata/TESTDB1/redo2/log2.dbf'

Solution: switch the logfiles until the group is no more active, then drop it and recreate the member:

select status,member from v$logfile;
STATUS  MEMBER
------- ------------------------------------------------------------
        /oracle/oradata/TESTDB1/redo1/log1.dbf
        /oracle/oradata/TESTDB1/redo2/log1.dbf
        /oracle/oradata/TESTDB1/redo1/log2.dbf
INVALID /oracle/oradata/TESTDB1/redo2/log2.dbf
alter system switch logfile;
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/TESTDB1/redo2/log1.dbf';
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/TESTDB1/redo2/log1.dbf' TO GROUP 1;
About these ads

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s