Skip to Main Content
  • Questions

  • Is NONLOGGING LOAD followed by INSERT into (logged) table recoverable ?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: June 15, 2017 - 4:22 pm UTC

Last updated: June 16, 2017 - 1:37 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Having spent a while searching the AskTom archives, I cannot find an answer to this.

In order for the following data-pipeline to be recoverable ( from a site-failure ) : do ALL operations have to be LOGGING or can we assume that only the final movement of data into the target (logged) table will be required to make it recoverable ?

2-steps :
1) SQLLDR direct-path loads into a NOLOGGED staging table
2) Standard SQL DML INSERT from staging-table into (logged) normal table.

AFAIK step(1) would be unrecoverable but would step(2) be recoverable ? We are assuming that step(2) would generate a lot of REDO , and would this REDO contain enough information to re-apply those INSERTS ? Or are they lost ?
Specifically I am thinking of failure where the PRIMARY is lost , DataGuard Zero Data Loss is in effect and so the STANDBY does indeed have the REDO.

Thanks in advance


and Connor said...

The subsequent logged operations do not automatically "fix" any nologging operation you have performed.

If you have DataGuard, then it is most likely (but not guaranteed) to have FORCE LOGGING set to yes on your database. Which means "nologging" operations are in fact logged.

You can confirm this with

select FORCE_LOGGING from v$database

If this is NOT set, then after a nologging operation, the datafile(s) affected should be backed up, and typically DataGuard apply would halt, because you need to transmit the datafile(s) affected to the DataGuard node to get the standby back in sync.

It's easy to test 


SQL> create table t nologging as select * from dba_objects where 1=0;

Table created.

SQL> insert /*+ APPEND */ into t select * from dba_objects;

77978 rows created.

SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                              114580  <=== small
redo size for lost write detection                          0
redo size for direct writes                              2112

3 rows selected.

SQL>
SQL> alter database force logging;

Database altered.

SQL> insert /*+ APPEND */ into t select * from dba_objects;

77978 rows created.

SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                            12613764  <== large
redo size for lost write detection                          0
redo size for direct writes                          12463664

3 rows selected.




We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions