Recreate online redo logs after failure
The database was brought down hard when the server was rebooted. The online redo logs were wiped out and had to be recreated.
Instance restarted, errors found
The instance was restarted after the server was rebooted. The errors with the online redo logs were apparent:
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1325402312 bytes
Database Buffers 788529152 bytes
Redo Buffers 21725184 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
‘/u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_989ok9q3_.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1:
‘/u01/app/oracle/oradata/ORADB/onlinelog/o1_mf_2_989ok45f_.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
The instance cannot find the online redo logs. They are not in the file system:
SQL>!ls -lt /u01/app/oracle/oradata/ORADB/onlinelog/o1_mf_2_989ok45f_.log
Recreate the online redo logs
Shutdown the database and restart it in mount mode
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>startup mount;
The database is OMF enabled, there are two options to recreate the online logs:
alter database drop logfile group #
alter database add logfile group #
or
alter database clear logfile group #
Here I dropped and added groups 1 and 3. I started with them because they were not the current log group.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
—————- ————- ——— ———— ———
1 1 3592 52428800 512 2 NO
INACTIVE 28715010 16-JAN-14 28721218 16-JAN-14
3 1 3591 52428800 512 2 NO
INACTIVE 28701594 16-JAN-14 28715010 16-JAN-14
2 1 3593 52428800 512 2 NO
CURRENT 28721218 16-JAN-14 2.8147E+14
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
—————- ————- ——— ———— ———
1 1 0 104857600 512 2 YES
UNUSED 0 0
3 1 0 104857600 512 2 YES
UNUSED 0 0
2 1 3593 52428800 512 2 NO
CURRENT 28721218 16-JAN-14 2.8147E+14
Here I cleared logfile group 2:
SQL> alter database clear logfile group 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
———- ———- ———- ———- ———- ———- —
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
—————- ————- ——— ———— ———
1 1 3594 104857600 512 2 NO
CURRENT 28721219 16-JAN-14 2.8147E+14
3 1 0 104857600 512 2 YES
UNUSED 0 0
2 1 0 52428800 512 2 NO
UNUSED 28721218 16-JAN-14 28721219 16-JAN-14
The online logs were recreated in the file system
SQL> !ls -ltr /u01/app/oracle/oradata/ORADB/onlinelog
total 256276
-rw-r—– 1 oracle oinstall 104858112 Jan 16 11:42 o1_mf_3_9fj305xj_.log
-rw-r—– 1 oracle oinstall 104858112 Jan 16 11:43 o1_mf_1_9fj2yok2_.log
-rw-r—– 1 oracle oinstall 52429312 Jan 16 11:43 o1_mf_2_9fj325gx_.log
The database can now be opened with new online redo logs
SQL> alter database open;
Database altered.
SQL> exit
