We’ve been working with Oracle long enough to see the same database implementation and administration mistakes being made over and over. Some of the mistakes were made when the database was set up; others are ongoing poor habits. This is the first in a series about Oracle database mistakes that seem more common in the smaller Oracle shops, usually shops that don’t have an experienced Oracle resource overseeing the database. So … let’s get started.
Mistake #1: Not multiplexing online redo logs
When a user saves a transaction to the database, the transaction is written first from database memory to the online redo logs. Online redo logs are simply a series of files that store database change records. At some point later, the change is written from database memory to the “real” database files.
Sometimes the database needs the information in the redo logs, such as when the database crashes before the database files can get written from memory, or when there is a catastrophic loss of one or more datafiles. In either case, the information in the datafiles is transactionally “older” than the information in the redo logs. The database will “replay” the transactions in the redo logs onto the datafiles in order to bring the datafiles back up to date. If you cannot bring the datafiles up to date (in other words, if you’ve lost your redo logs), you will either not be able to open your database or you will have to choose to lose some transactions. That’s how important the online redo logs are! (I’m oversimplifying, to more easily illustrate the point.)
You can configure your redo logs to multiplex (write more than one copy at the same time). This is advisable because, even if one copy gets corrupted or deleted, the other copy might still be OK. Failure to multiplex your online redo logs introduces a critical single point of failure for your database.
A further protection would be to keep the multiplexed copies in separate locations. This would protect you in case a single directory or file system gets damaged or lost; you would still have the redo log copies in the other directory/file system.
Also consider having at least one copy in a different location than your datafiles. That way, even if you lose everything in the datafile location, you’ll still have a copy of the online redo logs to contribute to your rollforward recovery. Without the online redo logs, you might not be able to recover all your transactions.
Are your online redo logs multiplexed to a good variety of locations?