taping oracle online redo logs?

[Michael Schulte zur Sur]

Hi all,

we are about to get a high availability HP system with lots of safety built in and I am asked, whether we want to tape the online redologs (besides archive logs).
Is there any virtue to it? I can remember vaguely Oracle saying, this procedure is not supported.

Any oppinion would be appreciated.

Michael

[Graham Cameron_1]

Not sure what you mean by "taping" ??

BTW the online redo logs are the same as the archive logs.

In any database, archiving or not, the online redo logs get cyclically reused.
In an archivelog database, they get copied to the archive log area also.
So provided you are capturing the archive logs, to tape or wherever, there is no point in also copying the redo logs.

On the other hand, it is good practise to use redo log groups, with >1 file per group split across disks, so that if 1 disk fails you still have the good redo log on the other disk. This capability is built into Oracle.

Hope this helps.

-- Graham

[Steven E. Protter]

You can do hot backups of your database using rman. Those hot backups can go directly to disk and then tape via the tar cv /backup_dir command. That will default to tape.

What you really need to do for reliability is mirror the redo logs to different physical disks. Also spread your control files to multiple physical disks as well.

The bottom line here is perfect reliability is nearly impossible. You can guard against common issues, but if your system board quits or your scsi subsystem fries, you're offline. Unless you use SErvice Guard.

SEP

[Michael Schulte zur Sur]

Hi,

there will be more than one member for each redo log group. 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?

thanks for your time,

Michael.

[Yogeeraj_1]

hi,

with the hot backup you don't need the online redo log files!

hope this helps!
regards
yogeeraj

[Michael Schulte zur Sur]

But dumping the whole database is very likely to be much more data. I just want to minimize the data lost between log switches.

greetings,

Michael

[Stan_17]

Hi Michael,

It depends on how you plan to take backups. i meant coldbackup (offline) or hotbackups (online) of the database.

If coldbackups then i'd say backup everything including redologs and archivelogs.

If hotbackups, then i'd say backup everything except online redologs, since these backups are done online they are always inconsistent. besides there is no begin backup and end backup commands for redologs like we have for datafiles. so you can't have a consistent copy of redolog unless the db is shutdown. if they are inconsistent, then its of no use for recovery. so why backup ?

Nowadays, almost all production systems run with some sort of RAID so the disk failures are very seldom. In fact multiplexing and mirroring online redologs on to different disks will almost remove the single point of failure. so you always have access to redologs to recover the database.

-
hth,
Stan

[Michael Schulte zur Sur]

Hi Stan,

backup is a completely different story. Of course we will have a backup too and since this machine is going to have some sort of emc and several access paths, that is not a concern. What I want to know is. Can Oracle use a redolog, saved by unix command, while it is open, or not, when I have to recover.

greetings,

Michael

[Stan_17]

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

As i said earlier, to backup (in your terms saved by unix command ) redologs totally depends on how you backup the database. ie, are you saving the files when the database is down or the db is online.

if offline, then yes backup redologs, they are consistent, hence useful for recovery, if online, then don't backup, they are inconsistent so no use for recovery. make sense!

[SteveKirby]

"Is there any virtue to writing redologs to tape?"
The short answer is not in my opinion.

The long answer is that all transactions are written to the redo log .. when the redo log gets full (or you force a switch) then the log gets written to disk as a Archive log.

Technically you could write all transactions that hit the redo log for real-time recovery (vs. 5 min - 1 day depending on how often the ARchivelogs are written) directly to tape... I have never done it, but the theory is sound.

[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...

[Alexander M. Ermes]

Hi Michael.
Why not using archivelog. store the offline logs on different disks, compress them every twenty minutes and put them on the backup as well ?
I have attached a script for compressing offline redo logs.
Other idea is to do a fll database backup once a week and backup the offline redo logs every day.
Just my 0.02 â ¬
Rgds
Alexander M. Er

[Michael Schulte zur Sur]

Hi Nelson,

thank you for your sympathy. However I am talking about saving the redolog before it is finished. Once the log switch succeded, yous should be safe.

Hi Alexander,

thanks for the script. We will of course be taking any ordinary measure to secure the data. Depending on the speed of the disk subsystem, a copy could be done in a blink of an eye, without forcing to many log switches onto Oracle.

thanks

Michael

[Henrik Palmqvist]

Hi Michael,

"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 ;-)."

Damn... I knew I wouldn't be able to mask my true identity for long in this forum... ;-)
No, I do not work for Oracle.

I'm not saying that Oracle tech personell can't do anything with a copied online redo log, I'm just saying that it is not a supported backup method.


"I posted the question at a metalink forum and not even an Oracle export said so, as you do."

He did actually.
Mark Bobak from Oracle says:
* "Backing up online redo logs is never necessary."
* "In case of hot backup, Oracle provides no mechanism to make a clean, consistent copy [of the online redo log]."
* "There are really no recovery scenarios where you'd need a backed up copy of online redo."

Which is not so different from what I and others in this thread has stated earli

[Michael Schulte zur Sur]

Hi Henrik,

thanks for your answer. I was always unsure, whether it could work or not, but since this is a complicated subject, not many can say for sure, whether it works or not, because "not supported" does not allways mean "not working" ;-). As I said before, we will have to discuss alternatives for a tighter data safety.

thanks for everyone,

Michael

[John Palmer]

Hi Michael,

You are proposing a very dodgy strategy.

That quote from Oracle says it all:
'Oracle provides no mechanism to make a clean, consistent copy [of the online redo log]'

How can you be sure that the copy you have taken is in any way consistent? Oracle writes to these logs in a disciplined fashion and ensures that the updates are actually on disk before committing a transaction. This is contrary to (for instance) datafile updates which may only be updated in the UNIX buffer cache and may take some considerable time to be flushed to disk. This strategy is crucial to the way that Oracle manages recovery. Your copy method would have to work in a similar way, cp or dd for instance would use the buffer cache.

The ONLY way that you can do what you want is as suggested earlier to get Oracle to switch the logfile (you've been given good advice).

The scenario that you are trying to guard against is one of total disaster - all datafiles and redo log copies lost. If you implement a strategy of switching redo logs every 10 minutes say and ftp'ing them to an offsite server then you have the ability to recover to within 10 minutes of the disaster. It's going to take you quite a while to do the recovery so I'd say that that wasn't bad in the circumstances.

Regards,
John