Recreate online redo logs

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s