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.

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

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: {<RET>=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.

8) 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: {<RET>=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.

Hope this helps !!

9 thoughts on “Recovering database after loosing active redo log file after archiving”

Jacky says:

June 17, 2008 at 12:55 am

Hi Advait,
could you please explain why the first recover can’t open log 3, but the second recover can?
can we lose both log 3 and log 1, or say as long as they are archived?

thanks.

[i]
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

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
[/i]

advait says:

June 17, 2008 at 6:03 am

Hi Jacky,

The first recover until cancel is expecting sequence # 3 for recovery. So it automatically search for archive log “/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC”. This name is generated by Oracle, thinking that it will find sequence # 3 in this file. But we know that sequence # 3 is the current sequence number and it will be present in current online redo log file. So we have to provide that file manually.

Hope this clears the doubt.

Regards,

Advait Deo

Jacky says:

June 17, 2008 at 6:21 pm

Sorry, Advait, I guess I am too slow on this one.

for log 3, because it has been archived, so it should be in ‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC’, why couldn’t rman find it? i couldn’t see why we need to provide it manually and we actually didn’t. what we provided was log 2 (/dy/oracle/product/db10g/dbf/redo02.log)

advait says:

June 18, 2008 at 10:35 am

Hi Jacky,

We are taking here about sequence 3 and not log 3. Our sequence 3 is redo log 2. Note that sequence of redo log files keep on increasing after each log switch in a round robin fashion. Now for recovery Oracle is looking for sequence 3 and not redo log 3. By default sequence 3 (If archived) will be in archive log file ARCH_657248900_1_3.ARC. But in this case sequence 3 is for current redo log file and hence not yet archived. So Oracle was not able to find the same in archive log location.

Also we are not using RMAN here, we are doing it manually. So we need to provide the location for current redo log file having sequence # 3. This was redo log 2.

Hope this helps !!

Regards,

Advait Deo

Jacky says:

June 18, 2008 at 7:17 pm

Thanks Advait.
I was confused by the redo log group 1-3 and sequence 1-3!

advait says:

June 19, 2008 at 5:16 pm

No Probs Jacky !!! I am glad you got it !!

Cheers

Advait Deo

NPD says:

October 16, 2008 at 2:20 pm

Step no. 9 doesn’t want to work. What should I do now?

Thanx.

CanbRN says:

September 9, 2009 at 1:49 am

All my REDO log files are full; How can I reset or re-use the same space?

manoranjan says:

July 29, 2011 at 8:25 am

what happens when current redo log is lost ?