Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups I'd like to stand up a database which offers a single tablespace for NOLOGGING operations (all other perm tablespaces set with FORCE_LOGGING=YES).  A conventional weekend full-backup + daily incremental should guarantee that database is recoverable to time-of-failure, except NOLOGGING tables in FORCE_LOGGING=NO tablespace.  What I'd like to guarantee is that all NOLOGGING tables are recoverable to daily incremental backup (with applications responsible for re-playing any ETL processing between backup and failure).

However, I understand that if NOLOGGING DML operation is running during incremental backup, the backup will have corrupt blocks - and entire NOLOGGING table (or partition thereof) may need to be truncated after recovery.  I'd like to avoid this, but without application &amp; dba scheduling / coordination.

To avoid NOLOGGING operations from running during backup, my idea is that the backup script (full and incremental) is wrapped with FORCE_LOGGING On/Off commands:
1) ALTER DATABASE FORCE LOGGING;
2) Take backup
3) ALTER DATABASE NO FORCE LOGGING;  

Questions:
Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs?  Will DML proceed (and backup get NOLOGGING corruption on affected tables)?  Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)?  Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?
Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"
Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?
The first principle to remember is, do not make a backup when a NOLOGGING operation is occurring. Oracle Database does not currently enforce this rule, so DBAs must schedule the backup jobs and the ETL jobs such that the NOLOGGING operations do not overlap with backup operations.



The above excerpt is from Oracle Documentation.


  Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs? Will DML proceed (and backup get NOLOGGING corruption on affected tables)? Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)? Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?


The FORCE_LOGGING=YES will be applied for subsequent operations only not for running operation.


  Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"


No, However you may use Tablespace Point-in-Time Recovery (TSPITR) for tablespace A,B and C.


  Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?


Yes(But not tables, for tables you can use Flashback). After restoring full/level 0 backup Oracle will apply incremental backups.

For details about NOLOGGING and Backup Strategy:
Plan for NOLOGGING Operations in Your Backup/Recovery Strategy