Data Warehouse Fault Tolerance Part 3: Restoring

Hopefully you've read the introduction, Part 1, and Part 2. Those posts detailed methods for building fault-tolerant ETL code, with a strong bias in favor of using Oracle Database features. Now I'll drill into the backup and recovery aspect of data warehousing fault tolerance, and tackle the age-old question of whether to ARCHIVELOG or NOARCHIVELOG in a BI/DW environment.

When I engage with clients that have a data warehouse operating in NOARCHIVELOG mode, their usual reasoning for this decision is a perceived performance gain. This makes sense on the surface... because NOARCHIVELOG prevents the generation of all that unwanted and unneeded REDO, right?

Not exactly. There is misconception about what NOARCHIVELOG actually means, and hopefully, I can clear that up with a demonstration. I have a database in NOARCHIVELOG, and I'll test to see whether my statements generate REDO:

SQL> SELECT log_mode
  2    FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

1 row selected.

Elapsed: 00:00:00.00
SQL>
SQL> CREATE TABLE target.sales
  2      AS SELECT *
  3           FROM sh.sales
  4          WHERE 1=0;

Table created.

Elapsed: 00:00:00.59
SQL>
SQL> SET autotrace on statistics
SQL>
SQL> INSERT INTO target.sales
  2         SELECT *
  3           FROM sh.sales;

918843 rows created.

Elapsed: 00:00:02.92

Statistics
----------------------------------------------------------
       1897  recursive calls
      40779  db block gets
       7062  consistent gets
       1585  physical reads
   38832896  redo size
        742  bytes sent via SQL*Net to client
        958  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     918843  rows processed

SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:01.32
SQL>
SQL> INSERT /*+ APPEND */ INTO target.sales
  2         SELECT *
  3           FROM sh.sales;

918843 rows created.

Elapsed: 00:00:06.00

Statistics
----------------------------------------------------------
       1042  recursive calls
       5581  db block gets
       2874  consistent gets
       1052  physical reads
      92108  redo size
        732  bytes sent via SQL*Net to client
        975  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
     918843  rows processed

SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL>  
The regular insert statement generated 38M of REDO in a NOARCHIVELOG database. Interesting. And the INSERT /*+ APPEND */ statement generated only 92K. Though it would appear that neither of these statements actually executed in NOLOGGING mode, the truth is that the APPEND statement did. All statements generate a little bit of REDO, because updates to the data dictionary are always logged. So why do regular inserts generate REDO on a NOARCHIVELOG database? There is a myth in the Oracle world that NOARCHIVELOG means that no REDO is generated, but that is not the case. Choosing NOARCHIVELOG mode simply means that we are foregoing the option to use media recovery (restoring datafiles, rolling forward). Think about it: REDO is not simply for media recovery, it's also for crash recovery. If all REDO generation was suspended, Oracle wouldn't be able to open after a simple server crash. In NOARCHIVELOG mode, there are situations where we can suspend most of the REDO generated, and one of those situations involves using the INSERT /*+ APPEND */ statement. So why would the database allow these NOLOGGING operations? Because direct-path operations write blocks directly into datafiles, bypassing the buffer cache. We wouldn't have to rely on the online REDO logs to recover those transactions, and so Oracle allows us to minimize the REDO generated. So if you have your database in NOARCHIVELOG mode for performance reasons, but you are using ETL tools that don't support true direct-path writes on Oracle (a lot of the third-party tools don't), or you are using cursor-based, row-by-row load scenarios, the same amount of REDO is generated if the database was in ARCHIVELOG mode. The only thing gained from operating in this manner is the privilege of having to shut down the database whenever a backup is needed. Perhaps another myth that gets perpetuated is that we can't have the best of both worlds, but in fact we can. We can minimize the amount of REDO generated, we can operate in ARCHIVELOG mode, we can backup our database in online mode, and we would be able to restore from that backup. The solution: NOLOGGING tables and indexes. I'll put the database in ARCHIVELOG mode, and rerun the test case above with one small change: I'll change the table to be NOLOGGING:
SQL> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             343935360 bytes
Database Buffers           71303168 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> alter database
  2  archivelog;

Database altered.

SQL> alter database
  2  open;

Database altered.

SQL> SELECT log_mode
  2    FROM v$database;

LOG_MODE
------------
ARCHIVELOG

1 row selected.

Elapsed: 00:00:00.06
SQL>
SQL> ALTER TABLE target.sales
  2        nologging;

Table altered.

Elapsed: 00:00:01.02
SQL>
SQL> SET autotrace on statistics
SQL>
SQL> INSERT INTO target.sales
  2         SELECT *
  3           FROM sh.sales;

918843 rows created.

Elapsed: 00:00:02.47

Statistics
----------------------------------------------------------
      15560  recursive calls
      33573  db block gets
      13861  consistent gets
       6260  physical reads
   38289752  redo size
        740  bytes sent via SQL*Net to client
        958  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
        154  sorts (memory)
          0  sorts (disk)
     918843  rows processed

SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:01.45
SQL>
SQL> INSERT /*+ APPEND */ INTO target.sales
  2         SELECT *
  3           FROM sh.sales;

918843 rows created.

Elapsed: 00:00:03.51

Statistics
----------------------------------------------------------
          1  recursive calls
       4628  db block gets
       1718  consistent gets
         59  physical reads
       8072  redo size
        732  bytes sent via SQL*Net to client
        975  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     918843  rows processed

SQL>
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.03
SQL> 
We get the exact same behavior with a NOLOGGING table in ARCHIVELOG mode than we did with NOARCHIVELOG mode. But is having the database in ARCHIVELOG mode of any value when all of our ETL processes are NOLOGGING? We can perform an online backup, but would we even be able to restore from that backup if we have transactions that executed as NOLOGGING? The answer is "yes" and "yes". We just need one small change to our backup strategy: a well-placed incremental backup. To increase the performance of our incremental backup, we need to create a block change tracking file. The database keeps a list of all changed blocks so that RMAN will know exactly what to backup during an incremental:
SQL> alter database enable block change tracking
  2  using file '/oracle/oradata/bidw1/change_blocks.bct';

Database altered.

Elapsed: 00:00:02.16
SQL> select * from
  2  v$block_change_tracking;

STATUS       | FILENAME                                 |      BYTES
------------ | ---------------------------------------- | ----------
ENABLED      | /oracle/oradata/bidw1/change_blocks.bct  |   11599872

1 row selected.

Elapsed: 00:00:00.01
SQL>  
We start by taking the initial incremental level 0 backup:
RMAN> backup incremental
2> level 0 database
3> plus archivelog;

Starting backup at 11-FEB-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=47 STAMP=710646180
input archived log thread=1 sequence=19 RECID=48 STAMP=710646955
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T015555_5q7bhw0c_.bkp tag=TAG20100211T015555 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 11-FEB-10

Starting backup at 11-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/bidw1/system01.dbf
input datafile file number=00002 name=/oracle/oradata/bidw1/sysaux01.dbf
input datafile file number=00003 name=/oracle/oradata/bidw1/undotbs01.dbf
input datafile file number=00004 name=/oracle/oradata/bidw1/users01.dbf
input datafile file number=00005 name=/oracle/oradata/bidw1/example01.dbf
input datafile file number=00007 name=/oracle/oradata/bidw1/target01.dbf
input datafile file number=00006 name=/oracle/oradata/bidw1/tdrep01.dbf
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp tag=TAG20100211T015557 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:26
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_ncsn0_TAG20100211T015557_5q7btbnf_.bkp tag=TAG20100211T015557 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 11-FEB-10

Starting backup at 11-FEB-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=49 STAMP=710647302
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T020143_5q7btr8x_.bkp tag=TAG20100211T020143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-FEB-10

RMAN>

Now I'll load the SALES table with another INSERT /*+ APPEND */ to make sure we have a NOLOGGING operation since our last backup.

SQL> insert /*+ APPEND */
  2  into target.sales
  3  select * from
  4  sh.sales;

918843 rows created.

Elapsed: 00:00:21.06

Statistics
----------------------------------------------------------
       2780  recursive calls
       6081  db block gets
       2434  consistent gets
       5442  physical reads
     136036  redo size
       1536  bytes sent via SQL*Net to client
       1155  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
     918843  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.07
SQL> 

This is the step in our process that requires a slight change to our backup and recovery strategy: we should get an incremental level 1 backup as soon as the load is complete. This will physically backup all blocks that have been affected by the load, and we wouldn't need to logically apply the REDO logs that are missing the NOLOGGING operations. Since we have changed block tracking, this step will be extremely fast, and I recommend that the ETL process flow or main driving script execute the backup as the very last step in the batch load.

RMAN> backup incremental
2> level 1 database
3> plus archivelog;

Starting backup at 11-FEB-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=47 STAMP=710646180
input archived log thread=1 sequence=19 RECID=48 STAMP=710646955
input archived log thread=1 sequence=20 RECID=49 STAMP=710647302
input archived log thread=1 sequence=21 RECID=50 STAMP=710648694
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T022455_5q7d67t6_.bkp tag=TAG20100211T022455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-FEB-10

Starting backup at 11-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/oradata/bidw1/system01.dbf
input datafile file number=00002 name=/oracle/oradata/bidw1/sysaux01.dbf
input datafile file number=00003 name=/oracle/oradata/bidw1/undotbs01.dbf
input datafile file number=00004 name=/oracle/oradata/bidw1/users01.dbf
input datafile file number=00005 name=/oracle/oradata/bidw1/example01.dbf
input datafile file number=00007 name=/oracle/oradata/bidw1/target01.dbf
input datafile file number=00006 name=/oracle/oradata/bidw1/tdrep01.dbf
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp tag=TAG20100211T022457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_ncsn1_TAG20100211T022457_5q7d6t16_.bkp tag=TAG20100211T022457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-FEB-10

Starting backup at 11-FEB-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=22 RECID=51 STAMP=710648715
channel ORA_DISK_1: starting piece 1 at 11-FEB-10
channel ORA_DISK_1: finished piece 1 at 11-FEB-10
piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_annnn_TAG20100211T022515_5q7d6vg7_.bkp tag=TAG20100211T022515 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-FEB-10

RMAN>

Now, let's see if we can restore:

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     422670336 bytes

Fixed Size                     1336960 bytes
Variable Size                356518272 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6094848 bytes

RMAN> restore database;

Starting restore at 11-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/bidw1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/bidw1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/bidw1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/bidw1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /oracle/oradata/bidw1/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oracle/oradata/bidw1/tdrep01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oracle/oradata/bidw1/target01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp
channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd0_TAG20100211T015557_5q7bhz1o_.bkp tag=TAG20100211T015557
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:06:37
Finished restore at 11-FEB-10

RMAN> recover database;

Starting recover at 11-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oracle/oradata/bidw1/system01.dbf
destination for restore of datafile 00002: /oracle/oradata/bidw1/sysaux01.dbf
destination for restore of datafile 00003: /oracle/oradata/bidw1/undotbs01.dbf
destination for restore of datafile 00004: /oracle/oradata/bidw1/users01.dbf
destination for restore of datafile 00005: /oracle/oradata/bidw1/example01.dbf
destination for restore of datafile 00006: /oracle/oradata/bidw1/tdrep01.dbf
destination for restore of datafile 00007: /oracle/oradata/bidw1/target01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp
channel ORA_DISK_1: piece handle=/oracle/flash_recovery_area/BIDW1/backupset/2010_02_11/o1_mf_nnnd1_TAG20100211T022457_5q7d6cgv_.bkp tag=TAG20100211T022457
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 11-FEB-10

RMAN> alter database open;

database opened

RMAN> 

So that's it for the Three "R"'s. I had a lot of fun revisiting the "operations" side of the house, and logging in as SYSDBA again. It's amazing how it all just came back to me... I didn't have to look at the manuals at all. Okay... maybe once.

Subscribe to Rittman Mead

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!

Subscribe to Our
Monthly Newsletter!

* indicates required