Recovering database after loosing active redo log file after archiving

Recovering database after loosing active redo log file after archiving

We are considering the scenario where we have the database in archive log mode and we have lost one of the active redo log file.

lets say that right now database is working fine and we are just starting it. All the files (Controlfiles, datafiles and online redo log files are intact).

1) Starting the database

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes
Database mounted.
Database opened.

2) Archive log status

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 3
3) Redo log group 2 is the current redo log and other two are active, meaning that they are required for recovery. When we say they are required for recovery means that the content has not been flushed to datafiles from database buffer cache. DBWR is yet to do that. This happens when checkpoint has just started and DBWR is yet to perform its job.

But at the same time you can see that these active redo log files has been archived by ARCH process. This is important for us.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 2 104857600 1 YES ACTIVE
4384517 13-JUN-08

2 1 3 104857600 1 NO CURRENT
4384519 13-JUN-08

3 1 1 104857600 1 YES ACTIVE
4379112 13-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
1 ONLINE
/dy/oracle/product/db10g/dbf/redo01.log
NO

3 ONLINE
/dy/oracle/product/db10g/dbf/redo03.log
NO

GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—

2 ONLINE
/dy/oracle/product/db10g/dbf/redo02.log
NO

4) Lets remove one of the active redo log file.

bash-2.05$ rm /dy/oracle/product/db10g/dbf/redo03.log

5) Shut abort

bash-2.05$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jun 13 02:16:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shut abort
ORACLE instance shut down.

6) Startup the database. This will fail as it wont be able to recover because it will not be able to access redo log group 3.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/dy/oracle/product/db10g/dbf/redo03.log’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

7) Now we can recover database until cancel because just “recover database” will not be able to recover since the active redo log file is missing. Even though this file is archived, database wont be aware of this.

SQL> recover database until cancel;
ORA-00279: change 4380738 generated at 06/13/2008 02:12:35 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_1.ARC
ORA-00280: change 4380738 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 4384517 generated at 06/13/2008 02:15:30 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_2.ARC
ORA-00280: change 4384517 for thread 1 is in sequence #2
ORA-00278: log file
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_1.ARC’ no longer
needed for this recovery

ORA-00279: change 4384519 generated at 06/13/2008 02:15:31 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC
ORA-00280: change 4384519 for thread 1 is in sequence #3
ORA-00278: log file
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_2.ARC’ no longer
needed for this recovery

ORA-00308: cannot open archived log
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/dy/oracle/product/db10g/dbf/system01.dbf’

Here it has applied all the archived log files, but still this is not recovered completely. This is where you should supply the current online redo log file.

Recover until cancel again and supply current redo log file which is redo log 2.

SQL> recover database until cancel;
ORA-00279: change 4384519 generated at 06/13/2008 02:15:31 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC
ORA-00280: change 4384519 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
/dy/oracle/product/db10g/dbf/redo02.log
Log applied.
Media recovery complete.

9) Once media recovery is complete, open database in reset log mode.

SQL> alter database open resetlogs;

Database altered.

SQL>

We were able to recover here after loosing the active redo log file, because that active redo log file was archived by archiver process.

This is a complete recovery and there is no data loss.