Tips and wisdom from 15 years of DBA experience

Monday, November 10, 2008

Treat your archive logs right

Back 'em up
Although this should be part of every backup strategy, backing up your archive logs may require careful thought to create the correct amount of coverage. Let's say you do a Full on Monday then incrementals throughout the week. You store a year of backups, but nightly you remove all archive logs older than 7 days. Disaster strikes on Tuesday, and your most recent full is corrupt. You go back to your prior week's full, apply the incrementals up through Sunday evening. You have deleted the archive logs to bridge you from Sunday to sysdate-7, so that is as far as you can go forward. More careful scrutiny of your first and second lines of defense would have revealed the importance of archive logs as a second line of defense.

Reduce redo, increase security

Audit the processes that generate the most redo on your systems. If you reduce the amount of redo, you will increase the likelyhood of making archive log backups a viable option. You also decrease your mean time to recover. Any DBA who has not been paged in the middle of the night due to a disk hosting archive logs about to fill has not lived - this is another reason for auditing your processes that generate redo.

Export NOLOGGING tables

Make sure that any table marked NOLOGGING truly needs to be. If so, and the data in that table cannot be quickly derived from other data in the database, export the table. That way, even after rolling through archive logs, you will be able to import the tables. If any NOLOGGING operations are performed on a table, after rolling archive across those operations, the "recovered" blocks will be marked corrupt. You will need to truncate that table, then import.

No comments: