Common Oracle Database Misconceptions
Jeremiah Wilton – jwilton@speakeasy.net
Introduction
The halls of the companies and institutions using Oracle’s database echo with the slightly raised voices of DBAs arguing over the finer points of Oracle administration. Disputes arise because there are two kinds of DBAs. Some DBAs base their methods on recommendations they read or heard somewhere, without completely understanding the reasons why. Other DBAs will do nothing without hard facts to support their methods. These latter DBAs believe that absent proof positive, even Oracle’s own documentation is not to be entirely trusted. Many of the standard practices, blanket recommendations, warnings, admonitions and advice included in the bulky third-party literature surrounding Oracle, and even in Oracle’s documentation, are based on opinions formed in a narrow environment, limited in scope and scale. Such advice may be heeded, but only upon understanding the reasoning behind the advice, and its applicability to the user’s environment.
The consequence of large numbers of Oracle professionals taking advice without understanding it has been a great profusion of theories and ideas with no basis in either fact or experience. These theories have a way of spreading like wildfire, in the form of “common wisdom” and “best practices.” Unsound ideas abound only because not enough people challenge them.
This document is a collection of short articles, some accompanied by proofs, that attempt to point out, then debunk, a number of common misconceptions and misunderstandings, as well as just plain bad advice, that few people have stepped forward to challenge.
Backup and Recovery
Misconception: Hot backup mode stops writing to the datafiles
Misconception: Media recovery will be required in the case of instance failure during backup mode
Misconception: Always ‘switch logfile’ before/after online backups
Misconception: Cold backup once a week as a “baseline”
Misconception: Export is a good way to back up the database
Redo and Rollback
Misconception: ‘shutdown abort’ is bad
Misconception: “Snapshot too old” can be avoided by using “set transaction use rollback segment.”
Misconception: Big batch jobs should use a ‘dedicated’ rollback segment
Misconception: Checkpoint not complete – misguided solutions
Misconception: NOLOGGING turns off redo logging
Misconception: After activating a standby, the primary requires re-copying to become the standby
General Practices
Misconception: Lots of extents are bad
Misconception: Listeners must be started before the instance