Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to delete Oracle redo logs, no recovery necessary

Posted on 2008-06-11
8
Medium Priority
?
7,317 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 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

610 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