Should Oracle *online* redo logs be backed up?

by Nick Pierpoint   Last Updated July 17, 2017 10:00 AM

Obviously archived redo logs are backed up as frequently as required, but does it make any sense to backup online redo logs? Is the online redo log an open and active file such that copying it in this state is a waste of time (same as backing up an open database)?

If the online redo log hasn't switched for 20 minutes (so that the log was available for archiving) then if the system fails and media recovery is required and you haven't backed up the online redo log, surely you've just lost the last 20 minutes of transactions?

I know there are other, more robust ways of transporting redo information to ensure nothing is lost, but I'm still interested in the state of the online redo log files.

Thanks for your time.

Clarifying Update...

There is a regular background process running regularly that ships off archived log files and online log files. I don't have much control of this process. Are the online log files usable, or could they be corrupt if the log was being written to when the backup was taken?

Tags : backup oracle


Answers 6


If your online logs are available upon restart and they are not corrupted, Oracle should be able to recognise them and replay them as well as the archived ones. The only things that will be lost are the transactions not yet commited at the time of the crash.

Of course if your hard drives crash, you will lost everything that has not be backed up. That's why you should have some mirroring or better have the redo logs stored on different disks/controllers/computers.

Benoit
Benoit
September 08, 2009 08:55 AM

The recommendation is that you duplex / multiplex your online redo logs. That way they are written to two different physical disks. If one fails, your database will stop (as it can't write to the redo logs). You shutdown abort, then bring up the database on the surviving redo log(s), then you can restart the database with all the multiplexing when you've resolved the problem.

Gary
Gary
September 08, 2009 10:43 AM

If you want to 'flush' the current redo log to ensure you have an archive of it you can issue an

alter system switch logfile;

from SQLPLUS or RMAN

This will force the archival of the current log which you can then backup off site.

Another option would be to use smaller redo logs so that the natural transaction rate of your database causes a log switch and archive at in interval you are more comfortable with.

"alter system switch logfile" does not force the archival of the current log, it only switches to the next log from the current making it available for archival. "alter system archive log current" does force the archive of the current log.

Cephas
Cephas
September 09, 2009 01:57 AM

Another thing to consider is a standby database. You can configure that to have the log writer write redo to the listener on a remote system with a standby database (real-time apply). This is done in near real time. The benefit is minimal downtime in the case of a system failure with no recovery needed. Just activate the standby database and go.

The recommendations about mirroring your redo log groups on separate disks are excellent advice. Typically, only one of your log groups is active at a time - the rest are either archived already or waiting for archival. You wouldn't want to copy the active one - it would likely be useless in a recovery scenario.

In short, no you can't copy live redo logs while the db is running and hope to have anything of value when you're done.

DCookie
DCookie
September 10, 2009 20:46 PM

It depends what you mean by Backup. If you mean it in the traditional sense then DCookie gave the correct answer of "short, no you can't copy live redo logs while the db is running and hope to have anything of value when you're done.". On the other hand there are newer backup type technologies that can take a point in time snap of volumes. If they can snap your data files, control files, and redo logs as of the time point in time then you do have something useful. If this data were to be restored when the database is brought up it would behave as though the power were pulled at the time of the snap. Any uncommitted transactions would be rolled back, but no committed transactions would be lost up to the point at which the snap was made.

Leigh Riffel
Leigh Riffel
October 04, 2010 18:17 PM

Just want to mention that you will not lose 20 minutes of transactions you will only lose transactions that are beyond the last checkpoint.

Regards.

Jundi
Jundi
July 17, 2017 09:13 AM

Related Questions


Restore Oracle 9 backup on Oracle 11 server?

Updated November 23, 2016 08:00 AM



Oracle RMAN full backup due to gaps in ARCHIVE LOGs

Updated October 23, 2015 09:00 AM