Solved

How to delete Oracle redo logs, no recovery necessary

Posted on 2008-06-11
8
7,288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not able to drop or recreate an Oracle stored procedure 1 67
Error in creating a view. 8 50
how to use l_instance in the host command 7 34
Shared Service Environment 2 53
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 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