Best Practices: Data Warehouse in Archivelog mode?
By Rekha Balwada on Mar 23, 2011
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:
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.