nologging operations and restore

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 Received on Wed May 09 2007 - 16:59:43 CDT