2

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 & 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?

Improve this question
2
  • 1
    A1. ALTER DATABASE will wait for NOLOGGING operations to complete. A2. I do not think so. All the database has to be consistent in time. But I may be wrong here. A3. Of course they can. Incremental/full backups works with datafiles not with tables or other objects. Applying archivelog after restore could invalidate table if NOLOGGING operation will be encountered. If there are not much data in NOLOGGING tablespace maybe you can expdp relevant tables?
    – Mindaugas Riauba
    Commented Apr 8, 2016 at 13:26
  • Thanks - see conversation under 1st answer. Let me know if you have any thoughts - given tablespaces A, B, C with FORCE_LOGGING = YES and D with FORCE_LOGGING=NO, how to recover A, B, & C to time-of-failure and D to time-of-last-backup (avoiding ORA-26040 for tables in D). Thanks for clarifying usability of incremental - not sure what caused me to doubt that.
    – KevinKirkpatrick
    Commented Apr 8, 2016 at 14:42

1 Answer 1

Reset to default
1

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

Improve this answer
3
  • Another comment indicates that FORCE_LOGGING will wait on running operations; I'll work out a way to trial this. If you're correct I'll run rman report unrecoverable after each backup (and instruct to app devs to avoid ETL during backup window). For TSPITR, it sounds like it rewinds... so I think I'd need TSPITR on D to "run it back" to the SCN of latest backup. But it seems both TSPITR or flashback table may balk at NOLOGGING op in archive logs - it sounds like recovery of TS D may just need to be done independent then imported.
    – KevinKirkpatrick
    Commented Apr 8, 2016 at 14:20
  • @KevinKirkpatrick Yes it waits for nologging operation to complete, my sentence gave wrong meaning may be.
    – atokpas
    Commented Apr 8, 2016 at 14:25
  • Just realized - maybe simplest recovery: 1) recover db to backup 2) export TS D 3) recover db to time-of-failure (pprobably leaving many ORA-26040 in TS D) 4 Drop TS D, restore from export. Thoughts/improvements? Will update answer and accept once settled.
    – KevinKirkpatrick
    Commented Apr 8, 2016 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.