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
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.