This discussion is archived
10 Replies Latest reply: Aug 23, 2013 12:24 AM by Anand GXS RSS

Unable to delete the Redolog of Thread 2 in RAC

882548 Newbie
Currently Being Moderated
Hi,

Oracle 10gR2 ( 10.2.0.4), Rac Environment, Instance name orcl1, orcl2

Recently the orcl2 got crashed so we rebuilt the machine and orcl2 node and added the node again back to cluster

There was a failed attempt in DBCA (Addition instance part) where the redolog05 & 06 got created in ASM.

We created instance orcl02 as thread 3 instead of thread 2 and successfully added the node to cluster.

The redolog of thread 2 (redo05, redo06) created due to the failed DBCA add instance activity in unable to delete from the v$log entries.

GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ----------------------------------------------------------------- ----- ---------- ---------- ---------
1 1 +ORCLDG3/orcl/redo01.log YES ACTIVE 50
2 1 +ORCLDG3/orcl/redo02.log NO CURRENT 50
5 2 +ORCLDG3/orcl/redo05.log YES UNUSED 50
6 2 +ORCLDG3/orcl/redo06.log NO CURRENT 50
8 3 +ORCLDG3/orcl/redo08.log NO CURRENT 50
9 3 +ORCLDG3/orcl/redo09.log YES INACTIVE 50

When i tried to disable the thread 2 i am getting the below errors, as the redo log 05, 06 are not physically present in the ASM

SQL> alter database disable thread 2;
alter database disable thread 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 6 of thread 2
ORA-00312: online log 6 thread 2: '+ORCLDG3/orcl/redo06.log'
ORA-17503: ksfdopn:2 Failed to open file +ORCLDG3/orcl/redo06.log
ORA-15173: entry 'redo06.log' does not exist in directory 'orcl'

Please help in removing the Thread 2 redo log files as this warning are getting written in the alert log every second and fills the mount point.

Regards
  • 1. Re: Unable to delete the Redolog of Thread 2 in RAC
    FreddieEssex Pro
    Currently Being Moderated
    Same issue on this forum a couple of days ago with the solution:

    Re: unable to disable a thread htat belongs to a long gone RAC node
  • 2. Re: Unable to delete the Redolog of Thread 2 in RAC
    882548 Newbie
    Currently Being Moderated
    The method suggested in the thread is not working, please find the below errors,


    SQL> alter database clear logfile group 5;

    SQL> alter database clear logfile group 6;
    alter database clear logfile group 6
    *
    ERROR at line 1:
    ORA-00350: log 6 of instance orcl2 (thread 2) needs to be archived
    ORA-00312: online log 6 thread 2: '+orclDG3/orcl/redo06.log'

    SQL> alter database drop logfile group 5;
    alter database drop logfile group 5
    *
    ERROR at line 1:
    ORA-01567: dropping log 5 would leave less than 2 log files for instance orcl2 (thread 2)
    ORA-00312: online log 5 thread 2: '+orclDG3/orcl/redo05.log'


    SQL> alter database drop logfile group 6;
    alter database drop logfile group 6
    *
    ERROR at line 1:
    ORA-01623: log 6 is current log for instance orcl2 (thread 2) - cannot drop
    ORA-00312: online log 6 thread 2: '+orclDG3/orcl/redo06.log'


    SQL> alter database disable thread 2;
    alter database disable thread 2
    *
    ERROR at line 1:
    ORA-00313: open failed for members of log group 6 of thread 2
    ORA-00312: online log 6 thread 2: '+orclDG3/orcl/redo06.log'
    ORA-17503: ksfdopn:2 Failed to open file +orclDG3/orcl/redo06.log
    ORA-15173: entry 'redo06.log' does not exist in directory 'orcl'

    Any ideas,
  • 3. Re: Unable to delete the Redolog of Thread 2 in RAC
    FreddieEssex Pro
    Currently Being Moderated
    Log file group 5 was unused. Log file group 6 is current so you can't drop it.

    You can switch logfile and re-try, and then switch and re-try again etc until you can clear it.

    Try:
    alter system archive log current;
    Edited by: Freddie Essex on 03-May-2013 12:03
  • 4. Re: Unable to delete the Redolog of Thread 2 in RAC
    882548 Newbie
    Currently Being Moderated
    The current instance name is orcl1 and the node which got failed earlier is orcl2, so we tried adding the instance orcl2 to the cluster but while at DBCA adding instance part the first attempt of adding orcl2 to cluster got failed.

    So We created instance orcl02 as thread 3 instead of thread 2 and successfully added the node to cluster.the redo06, redo05 are part of the instance orcl2(thread2).

    We have presently the current node orcl1 and new node added orcl02.

    If i issue the alter system archive log current this will archive the current redolog of the orcl1 right !!!

    hope you are clear on issue i am facing the redo log files of thread 2 were physically deleted accidentally before few days!!! How to remove those entries from the gv$log without re-creating the control file ?
  • 5. Re: Unable to delete the Redolog of Thread 2 in RAC
    FreddieEssex Pro
    Currently Being Moderated
    If i issue the alter system archive log current this will archive the current redolog of the orcl1 right !!!
    Wrong - alter system archive log current won't just archive the redolog of orcl1.
    hope you are clear on issue i am facing the redo log files of thread 2 were physically deleted accidentally before few days!!!
    I'm very clear on the issue you are facing.....thanks for asking.
  • 6. Re: Unable to delete the Redolog of Thread 2 in RAC
    rarain Explorer
    Currently Being Moderated
    Hi,

    Please clear the logfile with unarchived option and then disable the redo thread and then you can drop the redo log group.

    alter database clear unarchived logfile group 6;

    Thanks
  • 7. Re: Unable to delete the Redolog of Thread 2 in RAC
    882548 Newbie
    Currently Being Moderated
    All,


    Raised a TAR with ORACLE support, the only way to overcome this issue is to re-create the controlfile in RESETLOGS option .
  • 8. Re: Unable to delete the Redolog of Thread 2 in RAC
    dba-hyd Pro
    Currently Being Moderated
    select THREAD#, STATUS, ENABLED from v$thread;

    THREAD# STATUS ENABLED
    ---------- ------ --------
    1 OPEN PUBLIC
    2 CLOSED PRIVATE

    SQL> select group# from v$log where THREAD#=2;
    GROUP#
    ----------
    5
    6
    7
    8

    alter database disable thread 2;

    alter database drop logfile group 5
    *
    ERROR at line 1:
    ORA-00350: log 5 of instance PIEU2 (thread 2) needs to be archived
    ORA-00312: online log 4 thread 2: 'F:\FLASH_RECOVERY_AREA\ONLINELOG\O1_MF_5_8108KQ9T_.LOG;'

    SQL> alter database clear unarchived logfile group 5;

    SQL> alter database drop logfile group 5;
    SQL> alter database drop logfile group 6;
    SQL> alter database drop logfile group 7;
    SQL> alter database drop logfile group 8;

    SQL> select THREAD#, STATUS, ENABLED from v$thread;
    THREAD# STATUS ENABLED
    ---------- ------ --------
    1 OPEN PUBLIC
  • 9. Re: Unable to delete the Redolog of Thread 2 in RAC
    2cd28c97-0178-4c6a-95b5-2bf80124a6fd Newbie
    Currently Being Moderated

    alter database clear unarchived logfile group 5;

    is the only way to clear and drop the "closed, private" redo logs.

    the questions will be:

    What is the status of the thread when you remove a node pertaining to that thread

    thanks,

    shans

  • 10. Re: Unable to delete the Redolog of Thread 2 in RAC
    Anand GXS Newbie
    Currently Being Moderated

    Hi,

          Are you still facing the issue?

     

    This is my understanding of your problem.

    You would like to remove logfiles of group 5 and 6 from thread 2 of 3 node rac setup?

    Please provide the current status from v$log;

    As per the above conversation try this and ensure the status in v$log is in inactive alter system archive log current; That's when you can drop a lgofile. Also create a logfile2(2 for an instance) before dropping 5 and 6.

     

    Thanks,

    Anand

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points