RMAN full db backup vs incremental level 0 backup
All these years I have believed that an incremental level 0 backup will backup all blocks in a datafile except the ones Oracle has never written to and is pretty much the same as a full database backup except that the level 0 incremental is part of an incremental strategy.
Having said the above, I would expect that after a NOLOGGING bulk load on a table I could do a level 0 incremental backup and when the level 0 backup set is restored I should be able to access the data that was inserted with NOLOGGING OPTION.
We ran into a situation today ..........
-some staging tables were loaded with NOLOGGING OPTION
-a level 0 incremental load was done after that
-level 0 incremental backup set was restored to a test environment
- select * from (table that was loaded with bulk load option)
returns ORA-26040: Data block was loaded using the NOLOGGING option
If a level0 incremental actually copies all the datablocks why would it care about something that was loaded with a nologging option before the backup ??(will a full database backup as opposed to a level 0 incremental get us past this problem)???
if you have any inputs will be great to know , in the meantime I will have to knock support doors as well.......
Having said the above, I would expect that after a NOLOGGING bulk load on a table I could do a level 0 incremental backup and when the level 0 backup set is restored I should be able to access the data that was inserted with NOLOGGING OPTION.
We ran into a situation today ..........
-some staging tables were loaded with NOLOGGING OPTION
-a level 0 incremental load was done after that
-level 0 incremental backup set was restored to a test environment
- select * from (table that was loaded with bulk load option)
returns ORA-26040: Data block was loaded using the NOLOGGING option
If a level0 incremental actually copies all the datablocks why would it care about something that was loaded with a nologging option before the backup ??(will a full database backup as opposed to a level 0 incremental get us past this problem)???
if you have any inputs will be great to know , in the meantime I will have to knock support doors as well.......
posted by Vidya Balasubramanian at
12:39 PM
9 Comments:
is the table still in nologging mode?
try to select * from all_tables where logging='NO' to see if it is one of those.
Same as you, I would have said that a inc0 backup after a nologging bulk load and a restore to a time after the last db-inc0 would not generate that mistake
hth
Laurent
By
Laurent Schneider, at 2:02 PM
checked that - no objects with NOLOGGING option - I am really surprised - we confirmed the load was before the level 0 incremental backup and we are trying to restore from the level 0 incremental backup set
By
Vidya Balasubramanian, at 2:59 PM
Any update from Oracle Support? What is your version?
By
Laurent Schneider, at 1:33 AM
figured it out.
Daily level1 Incrementals at 11.00 PM and level 0 incrementals on Sunday at 6.00 AM(the level 0 incremental completed a little after 6.00 AM)
The restore was set to "Duplicate Target DB until 6.00 AM Sunday"
RMAN used the backup set at 11.00 PM and applied archive logs to bring it forward to 6.00AM - and obviously i had no archive logs for the nologging data
(i was not crazy when I thought a level 0 incremental should backup nologging Data, but was dumb that i missed the timestamp on the completion time of the level 0 backup)
there is still one outstanding issue with RMAN - Monday morning you add a datafile , nightly level1 increments dont pickup the new TBS only level0 increments - support is still looking into this.
By
Vidya Balasubramanian, at 4:37 PM
automatic file creation is a 10gR1 feature, are you using 10g?
10gR1 rman new features
By
Laurent Schneider, at 11:14 AM
a few months away from 10g - but for now on 9i
By
Vidya Balasubramanian, at 1:51 PM
in 9i, there is no automatic file creation, if the backup is oldest than the latest datafile creation, you have to create the file manually (alter database create datafile) before restore.
By
Laurent Schneider, at 9:03 AM
I created the table(is in nologging mode) and then loaded it with data by using sql loader.
Then i taken the backup using rman with inremental level 0.
Then i shutdown the database. Then i deleted the only one file in users tablespace.
then i mount the database then make the dbf file in users tablespace offline , then i open the database , then i restored and recovered the users tablespace data file.
Then I found all data with in the new in which earlier i loaded it with sqlldr.
Summary:
I have not faced the problem that you faced with incremental level 0
By
Vinod Kumar, at 3:29 AM
I created the table(is in nologging mode) and then loaded it with data by using sql loader.
Then i taken the backup using rman with inremental level 0.
Then i shutdown the database. Then i deleted the only one file in users tablespace.
then i mount the database then make the dbf file in users tablespace offline , then i open the database , then i restored and recovered the users tablespace data file.
Then I found all data with in the new in which earlier i loaded it with sqlldr.
Summary:
I have not faced the problem that you faced with incremental level 0
By
Vinod Kumar, at 3:30 AM
Post a Comment
<< Home