Flashback is not just for humans - Oracle Flashback Features

One of the most critical responsibilities of an Oracle DBA is to protect data against disasters. That includes maintaining data integrity of the database. FIPS Publication 199 “Standards for Security Categorization of Federal Information and Information Systems” defines loss of integrity as unauthorized modification and/or destruction of information. Loss of integrity may be a result of hardware failures, malicious attacks or simply unintentional accidents.

A recent study that cites human errors as the leading cause of loss of data integrity is no surprise. Human errors and accidental mistakes cause more system downtime than all other events combined. We heard the phrase “We are human” quite often. You can’t blame anybody. Accidents do happen. Mistakes do take place. An UPDATE without a WHERE clause runs. DELETE statement may have a bug. Application Code misbehaves. A support staff simply fires the wrong statement in the wrong database. Remember running a script in production which was meant for development database. Ouch! Whatever the cause maybe, an Oracle DBAs are called upon to fix data integrity issues.

It’s Oracle DBA’s responsibility to plan and implement mechanisms to recover from such accidents and disasters. A well-planned and tested backup strategy forms the primary mechanism to protect data. Oracle Flashback is one more weapon in Oracle DBA’s arsenal against such disasters. Oracle Flashback may come to your rescue in some specific scenarios. Oracle Flashback Features may have restrictions, constraints and limitations, but it’s Oracle DBA’s job to maximize the benefits that may be accrued from Oracle Flashback features.

With Oracle Flashback, you can view data as of previous point-in-time, get data update history along with metadata, recover tables to a previous point-in-time, undo transactions, etc.

From an Oracle DBA perspective, Oracle Flashback features are based on three different components:

• Flashback Features that are based on Undo Segments – Flashback Query, Flashback Version Query and Flashback Transaction fall in this category. These features rely upon data stored in undo segments.
• Flashback Features that are based on Recycle Bin – Flashback Drop feature falls in this category.
• Flashback Features that are based on Flashback Archive Logs – Flashback Database feature falls in this category. This feature relies upon flashback logs.

In this blog, I will explain what you need to do to setup flashback features that are based on undo segments.

Init.ora Parameters

Automatic Undo Management (AUM) is a pre-requisite for Oracle Flashback. With AUM, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database.

Set Undo_management init.ora parameter to AUTO to enable Automatic Undo Management.

UNDO Tablespace

This parameter is optional. If you have only one undo tablespace in your database, then you don’t need to specify this parameter. Oracle picks up the first available undo tablespace at the database startup. If you have more than one undo tablespaces, then specify the name of the undo tablespace that you want Oracle to use. If you don’t specify undo tablespace parameter and you haven’t pre-created any undo tablespace, then Oracle would start using SYSTEM tablespace as undo tablespace.

Size of Undo Tablespace

Size of undo tablespace is an important parameter that has direct impact on read consistency and flashback. The two primary objectives of the undo segments are (a) to rollback transactions and (2) to provide read consistent view of the database to the users. In addition, undo segments are used during instance recovery to rollback uncommitted transactions. You need to size undo tablespace keeping in mind these objectives. If undo tablespace is too small, then you may get dreaded “snapshot too old” errors. If undo tablespace is too large, then you may waste space.

Proper sizing of undo tablespace is not trivial. You need to know maximum undo generation rate and timings of long running queries. In addition, you need to factor in flashback requirements. Many DBAs don’t have such statistics, and hence start with reasonable undo tablespace size, and then monitor the statistics to fine tune the size if required.

If you have enough disk space, then I would recommend creating an undo tablespace as large as reasonably possible. With larger undo tablespace, you are likely to retain undo data for longer duration, and hence flashback further into past.

Undo_retention

For NON-AUTOEXTEND undo datafiles, you don’t need to specify undo_retention. If you specify, then Oracle ignores it. The database automatically tunes undo_retention parameter for the best possible retention based on system activities and undo tablespace size.

For AUTOEXTEND undo datafiles, the database attempts to maintain undo information for the period specified by this parameter. If necessary, Oracle extends datafiles to ensure that undo information is retained for the duration specified here. Please note that Oracle doesn’t automatically shrink the datafiles when undo tablespace extends and becomes too large.

Enable Row Movement

Enable Row Movement is a pre-requisite for FLASHBACK TABLE <table> TO SCN/TIMESTAMP/RESTORE POINT statement. You must execute ALTER TABLE <table> ENABLE ROW MOVEMENT to enable row movement. This clause grants Oracle permission to move rows within the table. Row movement changes row ids of the rows. FLASHBACK TABLE <table> TO SCN/TIMESTATMP/RESTORE POINT statement deletes and then inserts rows, causing row ids to change from their original values.

Please don’t confuse Row Movement with Row Migration and Row Chaining. Row Chaining occurs when Oracle cannot fit a row in one block and the row is spread across more than one Oracle data blocks. Row migration occurs when Oracle migrates a row to a new block without changing its row id.

By default, row movement is disabled. You need to explicitly enable row movement with CREATE TABLE or ALTER TABLE clauses. Please note that Oracle10g invalidates objects when you enable row movement for a table. I don’t see any reasons why row movement should not be enabled unless

• Your application stores ROWIDs for future queries and DML operations.
• Your application relies upon Oracle exceptions to prevent partition key updates.

Please note that FLASHBACK TABLE <table> TO BEFORE DROP doesn’t require row movement enabled. In addition, ALTER TABLE <table> SHRINK and Partition Key Update operations require row movement to be enabled.