Re: nologging operations and restore

On May 9, 5:59 pm, hopehope_123 <hopehope_..._at_yahoo.com> wrote:
> Hi ,
>
> I have a large datawarehouse system , running in archive log mode.
> Every night , an etl process runs and inserts/merges large amount of
> data. During the day no other updates are done.
>
> We take data backups by using rman everyday. If a failure occurs ,
> it is enough to return last nights backup. Since incremental data is
> in txt files, application can rerun the sqls.
>
> So , my question is : is it possible to restore the db successfully
> without making the tables which nologging dmls are run , unusable?
>
> Consider the following scenario:
>
> monday at 06:00 pm. level 0 data backup is done. it finished at 11:00
> pm.
> monday at 07:00 pm , i run : insert /*+append*/ into
> a_billion_rows_table select * from ten_rows_table; commit;
> this is completed at 10pm.
>
> tuesday db server crashed. i want to restore the db by using the
> monday level 0 backup. i dont care about the ten_rows insertion , i
> can rerun it because it is in txt files. is it possible to restore the
> db without making the whole billion_table unusable?
> does nologging insert statements running during the backup prevent
> this?
>
> if i restore the db , and recover , nologging operation is not
> recovered and the whole table becomes unusable just because of the 10
> records.
>
> i am thinking of a backup strategy :
>
> level 0 backup on mondays
> level 1 backup on other days.
>
> use nologging inserts/merges etc , without considering the backup
> times. ( when data backup is run , i also do nologging operations)
>
> if i need to restore the db , return to the one day before , open db
> with resetlogs , and miss only the last days update , without any
> table corruption ,
>
> does this work?
>
> Kind Regards,
> hope

Are you familiar with the "backup not backed up" rman option? I've used this in Archive logged Warehouse situations exactly the same as yours.

Other than that, I think you would be ok, but I get uneasy with level 1 and complete recovery after nologging operation. I've had trouble with this in some situations when batches fail and timing are off. "Backup Not Backed Up" was very useful in preventing this kind of trouble. Received on Wed May 09 2007 - 21:19:07 CDT