Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to delete Oracle redo logs, no recovery necessary

Posted on 2008-06-11
8
Medium Priority
?
7,322 Views
Last Modified: 2012-06-21
Hi,

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:
'/oradata/data/redo01.log'

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
0
Comment
Question by:nms-devel
  • 5
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 21761162
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.
0
 

Author Comment

by:nms-devel
ID: 21762730
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).

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 21762878
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'
shutdown

get a new good backup

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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:nms-devel
ID: 21763429
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;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
         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:
'/mnt/storage/oracle3/oradata/data/redo01.log

I miss mysql-like "reset slave, reset master" at dealing with binary logs ;-).
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21763834
can you do

recover database manual

and then cancel immediately?
0
 

Author Comment

by:nms-devel
ID: 21764030
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
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 21769939
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 21972469
why a "C"?  I would have helped more to earn the "A"
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question