22 March, 2008

Backup the Online Redo Logs ?

On a recent oracle forum dicussion which started of with the title "Multiplex redo log files?" but really with the question "Is it a normal operation to backup redo log files as well ?", I put in a response to the "normal" assertion to "never backup redo logs".

There's probably no documentation that "says to include online redo logs as part of a backup".
However, there are situations where you _can_ (or even would want to) include your redo logs in a backup. Note that I say "a backup", not "backups". A specific, adhoc, backup may include redo logs.

1. I take a cold backup of a database and restore it to another server. I do not wish to do a RESETLOGS (why, I ask "do I HAVE to resetlogs ?"). Then, I include my redo logs in my cold backup and restore.

2. I am testing backup and recovery scenarios. I take a cold backup of the whole database as a "reference point" in all my various recovery tests.

3. I am attempting a recovery of a production database to verify data at different points in time. I know that I might have to also do a resetlogs in between, change the incarnation id, maybe even change the db_name etc. I want to be able to revert to any restored image during the few rounds of restore-and-verify-data. Those intermediate backups would well be cold backups with redo logs (after resetlogs ?)

4. I am remotely advising a DBA on recovery of a crashed database. I cannot be sure that he has verified all the archivelogs, got the current log sequence numbers etc. I know that it is likely that one or more of the online redo logs hadn't yet been archivedand I would be able to "simulate" them as archived logs during the recovery -- but only if I do ensure that the redo logs are backed up.

5. The last one or two archivelogs had not got included in the tape backup. However, since I know that the database has 4 or 5 or 6 online redo logs, those last two are still available "on-disk" in the form of "online" redo logs (which might very well be raw devices, just to make things more complicated), which haven't been "lost" in the filesystem crash.


Bottom Line : As a "general" rule, it is not necessary to backup online redo logs.As a "general" piece of advise, it _can_ be dangerous to blindly backup online redologs, even in cold backups.However, there can be *specific* cases where I know I would want to backup the online redo logs.
Remember that "online redo logs" doesn't mean exactly the same as "CURRENT" or "ACTIVE".

9 comments:

Anonymous said...

alter system archive log current?

alter system switcht logfile?

I mean the purpose is backup the "content" of Online Redo Logs Files, not "those" files?

In this case, the "reset logs" action maybe is mandatory (there are exceptions..) but the Reset log is not a big issue in 10g+ because you can go back between database
incarnations...

Sorry my english

Hemant K Chitale said...

Yes, if you are using RMAN, it can "automagically" handle recovery in the absence of Online Redo Logs.

But not all backups use RMAN. Think of Split Mirror Backups. Think of very small organisations which run Cold Backups using OS Utilities. Think of outsourcing to an IT services provider who hires "Certified DBAs" fresh out of OCP Exams (uh ! oh ! I guess these DBAs will be using "best practices" -- but won't know how to handle pre-existing installations already using Split Mirror / OS Backups / non-RMAN methods).

Hemant K Chitale said...

Also, those "alter system archive log current" and "alter system switch logfile" are options if the database is up and running. My last two scenarios are restoration + recovery of a crashed database. I might even be offsite talking to a DBA who hasn't much experience. My first advice would be to backup the online redo logs (whatever state they are in) before he begins attempting RESTORE and RECOVER commands.

DaleS said...

Split Mirror is still a very common practice to backup Oracle and is preferred in many large environments because of the time it saves and how it keeps the I/O intensive backup operation off the production system. Even when using split mirror it is preferred to use RMAN and not split the online redo logs. See "Making Split Mirror Backups with RMAN" http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10734/rcmbackp.htm#1006221 for reasons why and http://www.oracle.com/technology/products/database/asm/pdf/HDS%20PIT%20backup-recovery%20%20BP%20with%20ASM%2007-2007_0.pdf for an example.

Anonymous said...

hi Hemant,

How can we backup Online redo logs when we are on ASM. Even if we copy, the restore will have different name or numerical value.

thx

Hemant K Chitale said...

Anonymous,
You could "backup" and "restore" them to/from OS files using asmcmd.
Use CREATE CONTROLFILE to "update" the controlfile with the new names that you have when you "restore" them into ASM.

Hemant

OracleNoob said...

Hi Hemant,
I am drawn to here by your posting at
https://community.oracle.com/message/3501395#3501395

I have a database with archivelog turn on. I would like to duplicate this database to another server named B.

I shutdown the database and start it up as mounted and do a full backup of the database.

Restoring the pfile and controlfile in the backup on B, i understand that Oracle will treat the controlfile as a backup controlfile and will prompt to resetlog.

What i would like to double confirm is that -

q1) when i am doing such a backup (with the db offline) using rman, are there still any changes on the ORL, that is required to rollforward, rollback the database ?

Can i say that the ORL are technically actually not required ?

Regards

OracleNoob said...

Hi Hemant,

I am drawn to here by your post on
https://community.oracle.com/message/3501395#3501395

I have a database with archivelog mode.
I shutdown it down and started it as mounted doing a fullbackup on it using RMAN.

I would like to restore this backup on Server B.

I restore the spfile and controlfile from the backup on B and i understand that oracle will treat the controlfile as a backup controlfile and will prompt me to open resetlog.

======================
What i would like to confirm is that

- with such backup (done when database is offline), are there technically any information required from the ORL rollforward, backward the database ?

Regards,
Noob

Hemant K Chitale said...

Noob,

If the database was SHUTDOWN NORMAL || IMMEDIATE || TRANSACTIONAL, you do not need the Online Redo Logs at the restore.

Nevertheless, you have the option of copying subsequent archivelogs and applying them to the restored database *before* you OPEN the database.

You will have to OPEN RESETLOGS whether you apply subsequent archivelogs or not.


Hemant K Chitale