Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups

oracle

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?

Best Answer

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

Related Solutions

Move Oracle database to new server

As a side note, rman is very fast when backing up to solid state disks. If you can get your hands on some put them in a raid you'll probably back up and restore in probably less than 30 minutes.

In either event, here's how to move the db to another host with hardly any downtime. Here's the overview:

1. do a full rman backup.

2. copy everything in the flash recovery area to the new host

3. go through the process of restoring the db to the new host.
   a. There are a handful of steps to do this.  It's not too hard.
   b. First the full backup is restored.
   c. Then each archive log is applied to the new instance.
   d. When all of the archive logs are have been applied, the db is opened.

Keep the production db up and running while you are doing steps 1,2,3a,3b. This may take a few hours if you have slow disks/network.

Once the level 0 backup is restored, apply the archive logs sitting in the flash recovery area which you copied in step 2.

At this point, all you need to do put the production db in read only mode and then copy and apply the last set of archive logs which were created after you copied the FRA. This should be very fast.

Then open the db and change clients so they now use the new db.

Oracle nologging impact for REDO Generation

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL.

But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by Elapsed Time. If there's a lot of REDO then one of those statements may stick out. For example, if the top statement is an UPDATE, DELETE, MERGE (UPDATE), or an INSERT without a hint (or with a bad hint).

Possibly the top statements are all CTAS or INSERT /*+ APPEND */. In that case, look at the explain plan for each of those top statements, for example by using select * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR')); If the execution plan says LOAD AS SELECT then it's using direct path inserts and is OK. If it says LOAD TABLE CONVENTIONAL, that's the problem statement.

Possibly they are all ok, but the REDO is generated by the indexes. The only way an index will not generate REDO is if the index is set to NOLOGGING and if the index is built after the INSERT. Index NOLOGGING only applies to index DDL. You may need to drop/disable and recreate/rebuild indexes to reduce REDO.

In my experience, the most likely causes of an INSERT statement not running in direct-path write are:

Foreign key indexes enabled on the target table, except when reference partitioning is used.
Bad hint syntax. For example, if there's a space before the plus sign, like this: /* +. Or if the hint is placed in a slightly wrong position, like this INSERT INTO /*+ append */ ....
Weird data types that aren't supported. I can't remember exactly, but I think with LOBs there are some extra limitations, like you may need to have partitioning.
Triggers on the target table.
There are likely some other reasons I'm forgetting.

Post the entire SQL statement and execution plan and we may be able to help investigate.

If none of that helps, than maybe it is an AWR bug. 15GB per second is very high, but not impossible. I wouldn't be too surprised if an Exadata system could achieve that.

Best Answer

Related Solutions

Move Oracle database to new server

Oracle nologging impact for REDO Generation

Related Question