Best Practices: Data Warehouse in Archivelog mode?

Recently I saw this post from Stewart Bryson and I think it is a very interesting topic. One of the topics Stewart discusses is running a Data Warehouse in Archivelog mode and its effects on things like data loading.

One of the leading reasons often cited for not having a Data Warehouse in NOARCHIVELOG mode is the performance impact of Redo generation. But does NOARCHIVELOG mode really mean no Redo is generated? How can you avoid generating redo and still have a resilient recovery strategy for your Data Warehouse?

Running a Data Warehouse in ARCHIVELOG mode allows the redo logs to be archived and therefore allowing you to do online backups and recover from both media and instance failure. Running the database in NOARCHIVELOG mode has the following consequences:


  • Oracle does not archive the filled online redo log files before reusing them in the cycle

  • Only cold backups are allowed, which means the system must be shutdown completely before a backup can be taken.

  • Typically, the only media recovery option is to restore the whole database, which causes the loss of all transactions since the last backup.

If your overall business strategy requires little or no downtime then the best practice is to put the database in ARCHIVELOG mode to provide online backups and point-in-time recovery options.

To reduce redo generation you can use Direct-Path INSERT into table with the nologging attribute for bulk-data operations. When you are running in this mode data is not written to the redo log (or more precisely, only a small set of metadata is written to the redo log). See example below:

 

nologgingvlogging_redo3.png

 

Note: Be aware that a nologging operation cannot be recovered using conventional recovery methods, because the necessary data to support the recovery was never written to the log file. You must do a backup after direct load operations have completed.

As I said earlier Stewart covered a lot more than just this topic in his in series of posts on fault tolerance. Best practices for backup and recovery of an Oracle Data Warehouse can be found here. More information on Direct-Insert and loading tables is located here.

Comments:

Post a Comment:
Comments are closed for this entry.
About

The data warehouse insider is written by the Oracle product management team and sheds lights on all thing data warehousing and big data.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
4
5
6
7
8
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today