Re: Database with NOLOGGING everywhere

Michael,

Assuming your data load batches are running periodically in certain maintenance windows, you should be able to employ incrementally updated standby or incrementally updated backups (this is I reckon only 10g) or plain incremental backup strategy when you take incremental backup right after data load with NOLOGGING is completed.

The only problem is that being in 9i, you won't be able to use fast incremental backup because block change tracking is the new feature of 10g.
To clarify, what's incremental standby is...

You take full backup and initialize your standby (or shadow if you wish) database. Then you take incremental backups periodically and apply them on this shadow/standby database. So instead of archivelogs you are using incremental backups to roll it forward.

The process should be manageable if you can control the schedule of NOLOGGING data load batches and synchronize with you incremental backups. If you have disaster in the middle of data load - you would have consistent backup at the end of previously completed data load and should be able to restart data load batches.

By the way, some data warehouses are running with only weekly full backups and rolling forward is based on re-running the batches assuming it's possible (i.e. source data for batches is preserved).

Another option might be to look into backup solutions provided by storage vendors.

Alex

On 6-Apr-08, at 2:09 PM, Kline.Michael wrote:

> I’ve got a 1.08TB warehouse that is pretty much all “batch mode”.
>
> Because of that “batching” they have almost 80-95% of the production
> tables set to NOLOGGING, including many at the tablespace level.
>
> We normally rely on shadow copies, but the database is archivelog.
>
> Given all the NOLOGGING, what could I possibly hope to “recover” if
> I ever had to use those archivelogs????
>
> The shadow copy is going to naturally be a COLD backup, I would
> think that would make the archivelogs practically useless.
>
> Mind you, I may be able to copy a datafile from the shadow copy, and
> then apply archive logs, but again, with all the NOLOGGING defined,
> what can I hope for in the recovery? I guess I can pick up the table
> creates and all, but the data would have to be all repopulated
> through the batch runs?
>
> I inherited this one.
>
> This is at 9.2.0.6, but when we do the next conversion, I think I’m
> going to push for a “more normal” archivelog + LOGGING
> configuration, RMAN backups and some shadow copy for other things
> that database has to do.
>
> Thoughts? Comments?
>
> Looking for someone who may have been caught in this situation and
> how you managed. That good old “Been there done that, ended up doing
> …” type thing. Been very fortunate thus far.
>
> Michael Kline
>
>
>
>
>
> LEGAL DISCLAIMER
> The information transmitted is intended solely for the individual or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or
> other use of or taking action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited.
> If you have received this email in error please contact the sender
> and delete the material from any computer.
>
> SunTrust and Seeing beyond money are federally registered service
> marks of SunTrust Banks, Inc.
> [ST:XCL]

--
http://www.freelists.org/webpage/oracle-l