redo log deleted without arhiving yet(Urgent)

[DeltaFun]

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'

[stecal]

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.

[sambavan]

Try this:

       ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

       or

       ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3
       UNRECOVERABLE DATAFILE;

Sam

[stecal]

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.

[sambavan]

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)

     Please do the following and post us the result:

         SELECT * FROM v$log;
          
         SELECT * FROM v$logfile;

To better undestand the problem.
Sam

[DeltaFun]

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.

[stecal]

It takes but a moment to make more redo logs. Good think it wasn't a production database.