Perhaps the most difficult outage situations are those tricky logical errors introduced by the users themselves—a user updates the wrong table or updates the wrong values, a developer thinks she is logged into the test system but is actually logged into the production system, or a user omits the where clause on a delete or update statement and 100,000 rows vanish or are logically corrupted.
In environments where everyone works at a frenetic pace, and the company is not yet large enough to have clear lines of demarcation between production, development, and test environments, it is often impractical to restrict access altogether to the production environments. This article examines a few Oracle technologies that can help a DBA protect against user errors.
Oracle Technology Checkpoint: Flashback Query and Flashback Table
Fortunately, the flashback features allowing recovery from serious errors are built into the database. The Flashback Query feature allows undo data stored in the Undo Tablespace to be read so that the DBA or anyone with appropriate access can “go back in time” and query the data as it existed before the incident. The Flashback Table feature also allows quick recovery if a table is inadvertently dropped, by maintaining a recycle bin, whereby dropped objects are essentially renamed and stored until space is needed. The DBA needs to ensure that enough storage exists to retain a sufficient amount of available undo, and a sufficient amount of free space must be available in user tablespaces to allow for upkeep of the recycle bin.
Beyond that, it is a matter of educating the developers and other users about the capabilities of Oracle Flashback technologies. The key is catching errors quickly—so full disclosure is important. Flashback technologies rely on data storage, and storage resources are not infinite. As long as errors are uncovered within a reasonable time period, the DBA staff should be able to quickly recapture this type of data, and in some cases, the developers or users may be able to correct their own mistakes.
Again with the Flashback Database
Sometimes, however, serious database errors are tough to overcome. Typically, user errors do not occur in a vacuum, and an erroneous update can occur alongside hundreds of correct updates. Pretty soon, the bad data is buried by thousands of additional updates. How can you find just one of those transactions among thousands? Can you “rewind” the entire database back to a previous point in time? The answer is yes. Flashback Database is an important feature in a Maximum Availability Architecture (MAA) plan, and it can in fact “rewind” the database to allow for “do-overs.” So in extreme cases, when Flashback Query is not enough, Flashback Database can help the MAA DBA retain his or her sanity in a world gone mad. Oracle
Leave a Reply