Re: Confusion why online redo logs should never be backed up.

Hi Jared,

I've re-read this other post and I remain totally unconvinced for the need to backup online redo logs.

Basically the argument here is that if one were to perform a cold backup of a database for the purpose of a subsequent full restore, the online redo logs would be required in the event of the backup being inconsistent.

I readily admit I might be missing something here but rather than focusing on the usefulness of these online redo log backups, shouldn't we questioning why on earth we have an inconsistent cold backup ? Taking a consistent backup is easy, it's a fundamental skill of any DBA or Sys Admin and it implies a startling level of incompetence failing to complete such a basic but vital task. Recommending backups of online redo logs in case one has an inconsistent cold backup is a little like recommending that one packs a collection of foreign language guides in case one catches the wrong plane when travelling to Sydney (ending up in Bangladesh instead). Sure the Bangladesh language guide might prove useful but wouldn't it have been easier to catch the right plane instead :)

Although backing up online logs might appear a trivial exercise, at a site I currently work at, one database has 12 sets of 1/2G multiplexed online logs. That's an additional 12G of backup resources and time. For what ? Protection in case we stuff up our offline backups and we want to fully restore the database without the availability of archived logs. Ummm, I think not ...

Points I would make to anyone still listening are:

1. Ensure you know how to backup a database. My little rule here would be if you don't have total confidence in your backups then you can't possibly expect to have total confidence in your future career as a DBA.
2. Backups of online redo logs are totally redundant
3. Learn to use RMAN and make this non-issue even more of an non-issue
4. A noarchivelog database that recovers via a full restore and opens with resetlogs does *NOT* require a fresh backup. If anything untoward occurs between backups simply repeat the full restore. That's why the database is in noarchivelog mode. Therefore restoring any online redo logs is again totally redundant.
5. A side issue but I've seen this mentioned a few times already in this thread. If you ever perform an incomplete recovery and so are forced to re-open the database with resetlogs, you do *NOT* have to perform a fresh backup. Your *existing backups*, along with all subsequent archived redo logs are all you need to fully recover the database in case of subsequent failure between backups. Yes as fresh backup does simplify the process but if you can't afford the extra 12 hours down time such a backup might entail, then just open the database and learn (and test, test) the well documented (since 7.3) process of recovering a database through a resetlogs. Suggestions that you must backup a database after a resetlogs are a nonsense.

My thoughts on why I've never backed up an online redo log ...

Cheers

Richard

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 23, 2003 7:41 AM

> > Can you think of a scenario where the backed up online redolog file will
> > help you completely recover a database?
>
> Sure I can, been there, done that.
>
> Cold backup of a database that was shutdown with either
> shutdown abort, but more likely, a windoze platform where
> the database was shutdown by stopping the service, and the
> registry entries are not setup correctly for that.
>
> Yes, I know the database should not be backed up in that state.
>
> I've seen it happen anyway. For instance, when someone sends
> me 200 gig of tapes from another site, and the database is
> shutdown via MS Cluster Services, which are not properly
> shutting down the database.
>
> The backed up online logs will be necessary.
>
> I've run into this at other times as well.
>
> I'm not saying you must always backup online logs. You certainly
> wouldn't do so during a hot backup.
>
> There are times though that they are handy.
>
> I've also read the Oracle docs on it, and don't agree.
>
> Jared
>
>
>
>
>
>
> Rajesh.Rao_at_jpmchase.com
> Sent by: root_at_fatcity.com
> 05/22/2003 12:42 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Confusion why online redo logs should never be
backed up.
>
>
>
> Can you think of a scenario where the backed up online redolog file will
> help you completely recover a database?
>
> You dont back it up, coz u dont need it. And, as you ritely pointed out,
> there are pitfalls to backing it up.
>
> Regards
> Raj
>
>
>
>
> "Koivu, Lisa"
> <Lisa.Koivu_at_Fairfieldre To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> sorts.com> cc:
> Sent by: Subject: RE:
> Confusion why online redo logs should never be backed
> root_at_fatcity.com up.
>
>
> 05/22/2003 03:12 PM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> Hi Rick,
>
> You want to rely on your os backup for backing up the online redo logs.
> The reason for this is, you don't want to overwrite your online redo logs
> with something not current (from your db backup, for example) when you are
> performing a recovery. To do so would cause an incomplete recovery.
>
> It's confusing. I didn't believe it at first either.
>
> And list, if I'm wrong, please set me straight.
>
> Lisa Koivu
> Oracle Database MONKEY
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063
> Office: 954-935-4117
> Fax: 954-935-3639
> Cell: 954-683-4459
>
>
> -----Original Message-----
> Sent: Thursday, May 22, 2003 2:27 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi All,
>
> In Oracle8i Backup & Recovery Handbook pg 13 it states "Finally, online
> redo log files should never be backed up....So, it is good practice not to
> back up online log files for ANY kind of backup.
>
> Can someone clearly explain why this is recommended?
>
> Thanks
> Rick
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).