How to delete Oracle redo logs, no recovery necessary


my Oracle system has crashed badly so redo logs are corrupt. It is a development database so data is of no importance.
What I want is to just use the database server again.
Due to redo log corruption I cannot even open database, because it wants to reaply transactions and fails every time. I don't care for the data, just want to be able to use the database server.

I wish I could just set some option on Oracle to COMPLETELY DISABLE redo logs and then DELETED them and start using database again.

I tried playing with recovery and clearlogs, but i got stuck with 3 transactions left :-(.

SQL> recover database until time '2008-06-11';
ORA-00283: recovery session canceled due to errors
ORA-00314: log 1 of thread 1, expected sequence# 9053 doesn't match 9050
ORA-00312: online log 1 thread 1:

SQL>alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/mnt/storage/oracle3/oradata/data/system01.dbf'

Its like a running in circles.

Thank you in advance
Who is Participating?
sdstuberConnect With a Mentor Commented:
you can,  try this...

startup mount
alter database noarchivelog (if it was in archivelog mode)
alter database add logfile member 'new_redo_file_full_path' to group 'your_redo_group_number'
alter database drop logfile member 'old_redo_file'

get a new good backup

alter database archivelog (if it was in archivelog mode originally)

if you really don't care about the data at all.  Simply drop the database and recreate it.  Doing so will also ensure any other corruption you haven't seen beyond the redo logs is cleared out as well.
nms-develAuthor Commented:
Well, data is not important, but I have about 50 tablespaces x 50tables each, and while row data is not important, all of the structures like tablespaces and tables are. So if a few collumns are missing or some fields aren't correct, it doesn't matter. But recreating 50 tablespaces can take time and populating them also.

I find it strange not able to turn off or reset redo logs from sqlplus when database is mounted (and not open).

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

nms-develAuthor Commented:
Looks promising but unfortunately it fails when adding new group member. I have two groups, one file in each (redo01.log and redo02.log). Group #1 is current.

SQL> select * from V$LOG;
         2          1          0   52428800          1 NO  UNUSED     100725789 11-06-2008 15:47:33
         1          1       9053   52428800          1 NO  CURRENT 100766349 11-06-2008 16:20:13

SQL> alter database add logfile member '/mnt/storage/oracle3/oradata/data/redo01-2.log' to group 1;
alter database add logfile member '/mnt/storage/oracle3/oradata/data/redo01-2.log' to group 1
ERROR at line 1:
ORA-00314: log 1 of thread 1, expected sequence# 9053 doesn't match 9050
ORA-00312: online log 1 thread 1:

I miss mysql-like "reset slave, reset master" at dealing with binary logs ;-).
can you do

recover database manual

and then cancel immediately?
nms-develAuthor Commented:
recover database manual?

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  1220460 bytes
Variable Size             234881172 bytes
Database Buffers         1895825408 bytes
Redo Buffers               15556608 bytes
Database mounted.
SQL> recover database manual;
ORA-00905: missing keyword
sdstuberConnect With a Mentor Commented:
oops "manual" was intended as an adjective,  not a command.  
 I typed what I was thinking (do it manually), not the syntax to use.  :)

recover database until cancel
why a "C"?  I would have helped more to earn the "A"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.