[ORACLE] Redo log file status are CLEARING or CLEARING_CURRENT after Restore.

Hello everyone.

Did you have this problem after restore?

Ohhh my good! WTF?

The first command "alter database open resetlogs" is abnormally abrupted leaving the redo log file status as CLEARING or CLEARING_CURRENT in controlfile.

I'll show you:

SELECT

    l.INST_ID, 

    l.THREAD#, 

    l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, 

    l.FIRST_CHANGE#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, 

    l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, 

    l.CON_ID

FROM gv$log l

JOIN gv$logfile lf ON 

            l.INST_ID = lf.INST_ID and 

    l.GROUP# = lf.GROUP#

ORDER BY 1,2,3;

The solution is simple, but always remember:

• This step-by-step worked for me, but it may not work for you.
• This post is for help you, but never take my word for it, test it well before use.

Execute the clear unarchived command:

    alter database clear unarchived logfile group 1;

    alter database clear unarchived logfile group 2;

    alter database clear unarchived logfile group 3;

    alter database open resetlogs;

Here you done!

Or, you can recreate a controlfile. If you want to know more about this solution, check the note ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (Doc ID 1352133.1).

A quick and simple tip, but one that helps a lot.

Regards

Mario