Solved

How to delete Oracle redo logs, no recovery necessary

Posted on 2008-06-11
8
7,252 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 73

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 73

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now