Recovery after loss of INACTIVE redo log files

We are going to see the steps for recover the db when we lose redo log files which are INACTIVE.

 1.Check for the redolog files in the database.

SQL>  select a.group#,a.archived,a.thread#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group# order by a.group#;

    GROUP# ARC    THREAD# MEMBER                                             STATUS

---------- --- ---------- -------------------------------------------------- ----------------

         1 YES          1 /data1/noasmdb/redofiles/redo_g01a.log             INACTIVE

         1 YES          1 /data1/noasmdb/redofiles/redo_g01b.log             INACTIVE

         2 NO           1 /data1/noasmdb/redofiles/redo_g02b.log             CURRENT

         2 NO           1 /data1/noasmdb/redofiles/redo_g02a.log             CURRENT

         3 YES          1 /data1/noasmdb/redofiles/redo_g03b.log             INACTIVE

         3 YES          1 /data1/noasmdb/redofiles/redo_g03a.log             INACTIVE

6 rows selected.

2.Now, we will delete the redo log files of group# 1 which are inactive from OS level.

[oracle@vm1 redofiles]$ ls -ltrh

total 601M

drwxr-x--- 5 oracle asmadmin 4.0K Feb  8 13:09 NOASMDB

-rw-r----- 1 oracle asmadmin 101M Feb 12 10:06 redo_g01b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 10:06 redo_g01a.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 10:29 redo_g02b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 10:29 redo_g02a.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:33 redo_g03b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:33 redo_g03a.log

[oracle@vm1 redofiles]$ rm redo_g01b.log

[oracle@vm1 redofiles]$ rm redo_g01a.log

3. We will perform switch logs manually. When we do a 2^nd switch log, the session will not hang and database operates fine but we will get error message in alert log files.

Errors in file /u01/app/oracle/diag/rdbms/noasmdb/noasmdb/trace/noasmdb_arc3_5939.trc:

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

ORA-00312: online log 1 thread 1: '/data1/noasmdb/redofiles/redo_g01b.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 1 thread 1: '/data1/noasmdb/redofiles/redo_g01a.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 4. Next check for the logfile is archived or not.

select a.group#,a.archived,a.thread#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group# order by a.group#;

5. If we shutdown the database, we cannot open the database (For testing, In live production system, don’t shut down the database, just go to step 6 .

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  263049216 bytes

Fixed Size                  2227456 bytes

Variable Size             176161536 bytes

Database Buffers           79691776 bytes

Redo Buffers                4968448 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 9619

Session ID: 125 Serial number: 5

SQL> select instance_status from v$instance;

ERROR:

ORA-03114: not connected to ORACLE

 6. So, start up the database in mount mode

SQL> startup mount

ORACLE instance started.

Total System Global Area  263049216 bytes

Fixed Size                  2227456 bytes

Variable Size             176161536 bytes

Database Buffers           79691776 bytes

Redo Buffers                4968448 bytes

Database mounted.

7. Once it is in mount mode, clear the unarchived redo log file for group 1;

SQL> alter database clear unarchived logfile group 1;

Database altered.

Wed Feb 12 11:44:14 2014

Completed: alter database clear unarchived logfile group 1

If the log file was archived, use the following.

alter database clear unarchived logfile group 1

8. Now, if you check the status of the redo log file, then we will see the status as UNUSED  and files are created automatically at the OS level.

SQL> select a.group#,a.archived,a.thread#,b.member,a.status from v$log a, v$logfile b where a.group#=b.group# order by a.group#;

    GROUP# ARC    THREAD# MEMBER                                                            STATUS

---------- --- ---------- ----------------------------------------------------------------- ----------------

         1 YES          1 /data1/noasmdb/redofiles/redo_g01a.log                            UNUSED

         1 YES          1 /data1/noasmdb/redofiles/redo_g01b.log                            UNUSED

         2 YES          1 /data1/noasmdb/redofiles/redo_g02b.log                            INACTIVE

         2 YES          1 /data1/noasmdb/redofiles/redo_g02a.log                            INACTIVE

         3 NO           1 /data1/noasmdb/redofiles/redo_g03b.log                            CURRENT

         3 NO           1 /data1/noasmdb/redofiles/redo_g03a.log                            CURRENT

6 rows selected.

drwxr-x--- 5 oracle asmadmin 4.0K Feb  8 13:09 NOASMDB

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:44 redo_g02b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:44 redo_g02a.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:44 redo_g01b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:44 redo_g01a.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:45 redo_g03b.log

-rw-r----- 1 oracle asmadmin 101M Feb 12 11:45 redo_g03a.log