I have always enjoyed the teaching and wisdom of Dr. Steven Covey (especially if he does not litigate for derivative works!). He has a real knack for capturing introspective how-to lessons detailing the simplicity of living a good and productive life.
In homage to Dr. Covey’s amazing work, I’d like to narrow the scope, but offer lessons with a similar impact for database administrators – Expanding on the inobviously obvious to illuminate the good path to success.
Habit One - Multiplex and Mirror Everything!
Multiplex and mirror all of your critical files – Is there a reason not to? Today’s SANs have gone a long way to provide for redundancy and reduce I/O contention, but they are definitely not an excuse to abandon this basic key to database survivability!
The SAN Trap: SANs are often used as a panacea for data availability. However, have you taken a close look at your SAN to determine how robust and survivable it really is?
- How many LUNS are your files spread across?
- What RAID level are you using and how many simultaneous disk failures will it take to make your files irretrievable? (Anything under 20% incurs quite a bit of risk).
- Do you have redundant controllers?
- Redundant switches?
Even the most survivable storage setup is still vulnerable to logical corruption, and the vastly more common, human error (“I just deleted all the .LOG files to save some space!”).
Conversely, for very slim installs, you may only have a single disk or LUN – While there is greatly increased risk when in such a situation, reality dictates that sometimes the circumstances are unavoidable. Until you can grow your storage footprint, multiplexing and mirroring (across directories) becomes even more critical as
Mirroring and multiplexing your control files, redo logs, archived redo logs, and RMAN backups will significantly increase the likelihood of a successful recovery, should the need arise (See Habit 5 – Preparation). The procedure is extremely easy, and the files generally take up very little space, if properly scaled and tuned to your needs.
Here are some best practices for you to tailor to your needs:
- Control Files: Multiplex two to three times and mirror over two to three disks/LUNs/directories
- Redo Logs: Three to four members per group with two to three groups spread across disks/LUNs/directories
- Archived Redo Logs: Mandatory mirroring between at least 2 disks/LUNs/directories
- RMAN Backup Files: Mirror between at least two disks/LUNsdirectories
- SPFILE: Periodically create a PFILE from the SPFILE and archive it, along with your backups and control file snapshots
A database administrator worth their salt NEVER loses data, and the best way to maintain this is to avoid a position where data loss is likely. Mirroring and Multiplexing are one of our most effective tools to reduce risk.
Brian Fedorko