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.