taping oracle online redo logs?

[Yogeeraj_1]

hi Michael,

Note that you cannot recover the online redo logfiles that you erased (you can recover but only upto the point in time right before those logs were filled with data). Typcially you have multiplexed redo logs so that at least one copy exists somewhere -- same with control files. Only if you lose every single drive would you lose all redo. This is why it is important to have multiple archive destinations and why you should get ARCHIVES off of the machine itself to some other location.

regards
Yogeeraj

[Michael Schulte zur Sur]

Hi yogeeraj,

Let´s assume, the time for a redo log to get full is one hour, I have two independent locations. I am doing cp redo.log /savelocation/ every 10 minutes. Is Oracle able to use this copied redo.log in the case of a recover? I have got a specification asking for taping redo logs and I would like to give them a well founded answer on that.

Please bear with me, thanks

Michae

[Yogeeraj_1]

hi again!

i will definitely depend on the type of failure...


There are 2 types of failure we recover from:

o instance failure due to a software error, power failure, etc. We recover automatically from the online redo logs and no intervention is needed. This happens as part of the normal startup.

o media failure. You've lost a disk(s). We need to manually restore a backup from tape or some other location, and then apply archive and online redo logs to it to bring it upto date.

Instance failure - automatic recovery, no interventaion
Medai failure - intervention required.


Typically you will do a full recovery unless you want to do a point in time recovery (eg: to a point in time prior to you accidently dropping a table for example).

hope this helps!

regards
Yogeeraj

[George Neill]

In my time of dealing with Oracle backups when I asked for clearification of needs their requests for redo log backups always turned out to be the archived redo logs. The online redo logs are consided to be too dynamic to get a usablly consistent copy of them. This is the reason for having multiple copies of the online redo logs, since the only viable copy is a mirrored version. The mirrored copy is the accecpted best available method of protecting the online redo logs. What we do at my location is every time we start an archive log backup is to start with a script that runs the sql commands to do a "log all" and "switch log". The forces the online redo logs to be archived as of the start of the backup session.

Regards,

George

[Michael Schulte zur Sur]

Hi George,

you seem to be the first one, who really understands my core question. Is a copied redolog besides the usual safety measures helpful? There are more than one reasons, a redolog could be left behind in an undetermined state. A shutdown abort, a computer crash, etc. But this is I think as far as it will go here in this forum. I still have a parallel post in metalink. I think it should work, but you never know. If our customer wants a higher security, they will have to turn to replication.

thanks for everyone posting,

Michael

[Henrik Palmqvist]

Hi,

Sorry for getting in to the discussion at this late stage, but I do have some comments.

Copying (or backup of) the redolog is never useful unless you are doing cold backup. I'm assuming that you are doing hot backups since this is a HA system.

If you are using hot backups you should not copy the redo log. Instead you should archive the transactions that are stored in the redolog and make a backup of the archivelog. Issue 'ALTER DATABASE SWITCH LOGFILE' or 'ALTER DATABASE ARCHIVE LOG ALL' to switch logfile.

Switching logfile is a very good thing to do during the day especially if the undo buildup is low in the DB. The init.ora parameter 'ARCHIVE_LAG_TARGET' lets you specify a log switch interval. If set to 10 minutes, the database will do a log switch every 10 minutes even if the log is empty, or half-full. You could use an NFS mount to another machine and have that dir as a secondary destination (init.ora parameter 'LOG_ARCHIVE_DEST_2'?) and make it optional, so that archiving doesn't stop if the NFS mount is unavailable. Or you could do your own script and schedule from cron at a specified interval to switch logfile and then remotecopy the newly archived log.

The database can always do media recovery until the last archived log generated, if the chain of archivelogs is unbroken and you have a hotbackup as starting point.

[Michael Schulte zur Sur]

Hi Henrik,

what you write is certainly good practice, which however does not address my question.

greetings,

Michael

[Henrik Palmqvist]

Michael,

Let me clarify by adressing your different questions:

"I am asked, whether we want to tape the online redologs (besides archive logs).
Is there any virtue to it?"

If the database is started there is no point in taking backup copies of the redo log.


"Depending on how frequent the logs switch, it might be useful to make frequent snapshots of them in between. This is my concern. Is this useful at all? Can Oracle use them in a case of a recovery?"

If you want to reduce the amount of transactions lost in case of disaster, you need to archive the logs more frequently. Instead of "snapshoting" the redo log, you should force a log switch in the database and copy the newly created archive log. If you force a log switch a new archivelog will be generated, containing all transactions that was in the redo log.


"I just want to minimize the data lost between log switches."

Switch log frequently to reduce the data lost in case of failure. The time between log switches determines how much time you lose when you recover. By reducing this time (between logswitches), the amount of data lost will be reduced accordingly.


"Can Oracle use a redolog, saved by unix command, while it is open, or not, when I have to recover."

No. Copying the redo log while the database is open is pointless. The redo log can not be used for recovery if it is copied while the database is open.


"Let´s assume, the time for a redo log to get full is one hour, I have two independent locations. I am doing cp redo.log /savelocation/ every 10 minutes. Is Oracle able to use this copied redo.log in the case of a recover?"

No.
What you need to do is:
1. Archive any unarchived transactions (ie. force a log switch)
2. Copy the newly created archivelog to a safe destination.
You can schedule a job to do this every 10 minutes from cron (or use ARCHIVE_LAG_TARGET in combination with NFS).

The _archivelog_ can later be used for media recovery of your database

[Michael Schulte zur Sur]

Hi Henrik,

thank you for your detailed answers. What you write, I am aware of. The certainty, you make the statement, that Oracle can´t do anything with a redolog, that is copied, while Oracle is running, makes me think, you are one of the programmers at Oracle, who wrote that section of code ;-). I posted the question at a metalink forum and not even an Oracle export said so, as you do. This question was brought up by a customer and we still have to figure out, how much safety he really wants.

greetings,

Michae

[Alzhy]

Michael,

You're truly not alone on this.. I've had a client once who was doubly concerned about his redo logs (which IMHO do not have a need to be backed up independently or periodically). So in addition to split mirror backups (we employ VxVM and use hostbased mirroring of LUNs) we do "special" backups of redolog files at the instant the log switch occurs... our DBA created a script to specifically pinpoint what redo log has recently been completed and can be backed up...