redo log deleted without arhiving yet(Urgent)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: redo log deleted without arhiving yet(Urgent)

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75

    redo log deleted without arhiving yet(Urgent)

    Hi, all
    I am trying to move redo log file to another disk on sun solaris,
    and I accidentally deleted logfile group 3, and I restored database back to last night' backup, but it still complains about the log file redo03.log, saying it needs to be archived. Please advise. Thanks.

    D.F.

    Oracle 9i, Sun Solaris.
    SQL> select group#,sequence#,archived,status from v$log;

    GROUP# SEQUENCE# ARC STATUS
    ---------- ---------- --- ----------------
    2 38 NO INACTIVE
    3 37 NO INACTIVE
    4 39 NO INACTIVE
    5 40 NO INVALIDATED

    SQL> alter database drop logfile group 3;
    alter database drop logfile group 3
    *
    ERROR at line 1:
    ORA-00350: log 3 of thread 1 needs to be archived
    ORA-00312: online log 3 thread 1:
    '/opt/oracle/OraHome9i/oradata/cncarc/redo03.log'


    SQL> alter system archive log all;
    alter system archive log all
    *
    ERROR at line 1:
    ORA-16038: log 3 sequence# 37 cannot be archived
    ORA-00313: open failed for members of log group of thread
    ORA-00312: online log 3 thread 1:
    '/opt/oracle/OraHome9i/oradata/cncarc/redo03.log'
    Still learning

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Boy, I'll bet you're gonna be a lot better about knowing how to recover a database after this little fiasco is over and done with. Don't any of your personal reference books mention how to do a recovery in this type of situation? All of mine do. Do you have a little black notebook with step-by-step instructions for what to do in a loss of an important file?

    Aside from that, have you looked on MetaLink (if you have access to that)? There's, what, about 20 articles on how to do a recovery.

    Have you looked at Oracle docs? They're free, and they're even correct sometimes.
    http://download-west.oracle.com/docs...o.htm#BRUMG004

    What a great learning opportunity you have before you. Take advantage of it.

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Try this:
    Code:
           ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
    
           or
    
           ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
           UNRECOVERABLE DATAFILE;
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Umm, that would be a negative. The group is gone, not just a member of the group. The key is if he needs that log for recovery (which he does, but there are no other members of that group). If it is the current online redo log file (and it is) and its information is needed for instance recovery (assuming so), then he cannot simply clear it. In this scenario, he should do an incomplete recovery and resetlogs.

    Disclaimer: the poster said he deleted the group. If he meant member, as in there is another member of that group available somewhere else, clear unarchived would apply. If he meant member and thinks that is a group (if another member of that group is still around), then that's another whack against him for not knowing what he is doing or talking about. Further, if it was the only member of the group, then he deserves two whacks for obvious reasons.
    Last edited by stecal; 07-25-2003 at 02:24 PM.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If he had done the backup, then he would be able to do an incomplete recovery on the database using the until cancel option as you mentioned. The commands that I mentioned is applicable to the all the logfiles with the status INVALID/STALE/Blank (i.e. the file is in use)

    Code:
         Please do the following and post us the result:
    
             SELECT * FROM v$log;
              
             SELECT * FROM v$logfile;
    To better undestand the problem.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    Thanks all for your help.
    I did a restore database until time ' ', and open database resetlogs. Luckily, it is dev box. I had one member for one group, which is bad, I know.
    You know when I have such issue, my brain is blank. Stecal is right, I need to practice more to be more familiar with.
    Thanks all again.

    D.F.
    Still learning

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    It takes but a moment to make more redo logs. Good think it wasn't a production database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width