Oracle Database Discussions

David, why do you think you need to reset the online redo logs? The only time I can think when you should do this is after performing a point in time database recovery. See the SQL manual for the command "alter database open reset logs" but again do not attempt this until you have verified that this is really a necessary action.

To do not do this if you are just trying to limit the log miner output.

HTH -- Mark D Powell --

Mark,

Right now I have to sit for a minute or two as data not importnat to me scrolls by in order to get to info I want to see.

I am trying to monitor what a COTS system is doing to DB so I can replicate it.

This is a dummy server since it is on my Laptop and not important so recovery is not a big issue to me at all.

Aren't redo logs just for point in time recovery? If I have a full export of my db why would I need to maintain these anyway? Agian being that I come from a Sybase world it wipes the logs out after a full db backup occurs anyway if you specify dump db with truncate etc... Does Oracle work this in a totally different way?

Thank you for all your help.

Miller

If it is only a test DB, do a shutdown immediate, then a startup mount, then issue alter database open resetlogs.Do all this as sysdba.Make sure all tablespaces and datafiles are online as well (can be corrupted if tablespaces are online and some of there datafiles are offline.)

I recieve an error:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery.

Thanks,
Miller

Resetlogs is a drastic command - didn't think you would get this error.There is a clear logfile command, but I'm hazy on exact syntax - alter database clear logfile n or alter system clear logfile n where n is the number of the redo log group.Try searching otn or metalink for correct syntax.

Apologies,
The command is
Alter database clear logfile group N where n is the number of a redo log group.However I think this command can only be used on standby databases, but it's worth a try.

Rgds
Ger

David, the redo logs are for crash recovery. The archived redo logs are for media recovery. If you have a backup then you can shutdown your database, recover all the database data files from the backup, startup mount, alter the database open resetlogs and be on your way using a point in time copy, that is, the cold backup.

If you make a hot backup then the archieved redo logs must be at least partiacally applied.

The question is what you are trying to gain from clearing the redo logs. The changes reflected in the logs would already be applied to your db. To get rid of them you would have to perform a recovery. If you just do not want to see the data in log miner then clear out log miner (separate copy of log data) and continue operations.

Being that it is a private db you could always delete it and go thought the process you used to build it again.

HTH -- Mark D Powell --

Hi David,

Here's a command you can try logged in as sysdba.
1. Shutdown the database normally.(no abort or immediate)
2. Issue "alter database open resetlogs"

Cheers
Ray

I appologize but I am not following this:

If I bring DB down then I get told:
ORA-01034 ORACLE not available ... when I try to issue ALTER database command...

Sorry for rookie knowledge here but I am trying to learn..

Thanks,
Miller

The alter database command generally updates information in the control files. To update information in the control files the database instance must be running.

emphasis added [from prior post] >> If you have a backup then you can shutdown your database, recover all the database data files from the backup, STARTUP MOUNT, alter the database open resetlogs and be on your way using a point in time copy, that is, the cold backup. <<

David, before performing any backup and recover activities you really need to read the Backup and Recovery manual chapters on manual recovery unless you have the ability to recreate the database from scratch and just start over.

HTH -- Mark D Powell --

Log Miner is a fairly industrial-strength tool.

There is a neat Appendix in Tom Kyte's book to show you how to get the most out of it.

What's wrong with SQL_TRACE and reading the individual application trace files ?

you do NOT want to reset yourlogs.

As was mentioned in a previous post, the redo logs contain all transactions in your database during the period that individual log was "ACTIVE". when a log switch occurs, the log is archived PROVIDED you are running in archive log mode. there is no need to clear or reset your logs. the unnecessary data you are seeing is/was necessary up to the time that the logswitch occurred.

appears you tried to do as one responder mentioned and do a resetlogs and you got the appropriate error...You cannot resetlogs unless you have done an incomplete recovery which implies you had a database failure of some sort and could not completely recover your database to the point in time that it failed