Can I Clear Current Redo Logfile while it’s required for recovery??

Normally we need to clear redo log if its corrupted and oracle is not able to reuse it. It may already be archived or may not be. In both cases we need to clear it to allow oracle instance to re-use it. You have to make active redolog to inactive by manual checkpoint or by switching logfile (if possible).

Commands would be

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group number>;

ALTER DATABASE CLEAR LOGFILE GROUP <group number>;

In case of corruption in current redo log following two scenarios could take place

1) Database instance crashed with Ora- 600 error; You can’t do much as now you need to check your last successful backup and all archives/incremental backup to perform incomplete recovery.

or

2)  Database still up and running (chances are less but possible); So could we try to clear the corrupted current redo log??

Let’s try to mimic the situation (except corruption)

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 16 52428800 512 1 NO CURRENT 1124097 31-JUL-12 2.8147E+14
2 1 14 52428800 512 1 YES ACTIVE 1121359 24-JUL-12 1122827 25-JUL-12
3 1 15 52428800 512 1 YES ACTIVE 1122827 25-JUL-12 1124097 31-JUL-12

SQL> create table abc as (select * from dba_extents);

Table created.

SQL> delete abc;

9145 rows deleted.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 16 52428800 512 1 NO CURRENT 1124097 31-JUL-12 2.8147E+14
2 1 14 52428800 512 1 YES INACTIVE 1121359 24-JUL-12 1122827 25-JUL-12
3 1 15 52428800 512 1 YES ACTIVE 1122827 25-JUL-12 1124097 31-JUL-12

Now we are sure that current redo still have changes required for instance recovery. And finally I am going to trigger the command to clear current redo log (group 1)

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance mask11g (thread 1)
ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/mask11g/redo01.log’

That was totally expected behavior because redo still contain information required to recover the instance in case instance crashed, so oracle will not allow any attempt to clear current redo log file.

In this case simply try to switch logfile to push current logfile into Active/Inactive state and then clear the corrupted redolog.

MOS note: Recovering After Loss of Redo Logs (Doc ID 390646.1). It’s listing all possible scenarios including manual recovery using undocumented parameters as well.

Advertisements

One Response to Can I Clear Current Redo Logfile while it’s required for recovery??

Share Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: