Oracle 10g Availability Enhancements, Part 3: FLASHBACK Enhancements - Page 2
January 27, 2005
Rewinding Tables with FLASHBACK TABLE
While Flashback Version Query and Flashback Transaction Query offer the capability to retrieve the state of a table's rows at a prior point in time, Oracle 10g also offers the ability to restore an entire table to an earlier state within the boundaries of available UNDO data via the FLASHBACK TABLE command.
To illustrate this, I will create a new table in the HR demo schema called APPLICANTS that I will use to record information about each person applying for a job. I will use a row-level trigger and a sequence to automatically increment the primary key column, APPLICANTS.APPLICANT_ID, whenever a new entry is added to the table.
Listing 3.6 shows the DDL and DML statements necessary to create and populate this table initially. Once the new table was populated, I recorded the maximum SCN (2177093) just before I issued a series of additional INSERT statements shown in Listing 3.7.
I will issue the FLASHBACK TABLE command shown in Listing 3.8 to bring the table back to its initial state. Note that I set the table's ENABLE ROW MOVEMENT parameter to TRUE before attempting to "rewind" the table.
Prerequisities. Before I can execute a FLASHBACK TABLE command, there are some precursors:
Caveats. Even though FLASHBACK TABLE offers some slick new capabilities, some warnings are in order:
Restoring a Dropped Table: FLASHBACK DROP and the Recycle Bin
Rounding out the new logical flashback features, Oracle 10g offers the capability to recover from one of the most destructive "accidental" operations that can happen to any Oracle database: the complete removal of a table via the DROP TABLE command. To facilitate this new feature, Oracle 10g has added a new storage area to the database called the Recycle Bin where dropped objects are retained until the object is either recovered via a Flashback Drop operation, or until the object is purged from the Recycle Bin.
Peering Into the Recycle Bin. Every time a table is dropped, it is assigned a unique object identifier in the Recycle Bin. This 30-character-long object identifier is in the format BIN$$globalUID$version, where globalUID is a 24-character globally unique identifier for the dropped object, and version is assigned for each version of the dropped object.
Oracle 10g provides several methods to view the Recycle Bin's contents and identify which tables have been dropped:
Viewing Different Versions of Dropped Tables. Even if a table is created and dropped several times, all of the different iterations of the dropped table and its dependent objects are retained in the Recycle Bin until they are purged. Using the dropped table's object identifier, I can query directly against the dropped table's data in the Recycle Bin, thus allowing me to determine exactly which version of the table should be recovered. I will demonstrate this feature in an upcoming recovery example.
Listing 3.9 displays several examples of querying the Recycle Bin for its current status.
Recycle Bin Space Pressure and Automatic Purging. Oracle 10g automatically manages the contents of the Recycle Bin to insure there is enough space to store any dropped tables and their related objects. Unfortunately, this also means that there is no way to predict when Oracle may need to purge objects from the Recycle Bin.
Oracle will keep objects in the Recycle Bin until it can no longer allocate new extents in the tablespace where the dropped objects originally resided without expanding the tablespace. This situation is known as space pressure. When space pressure demands that Recycle Bin space be reclaimed, Oracle will purge the oldest objects first (i.e., first-in, first-out basis), and it will purge a dropped table's dependent objects first (e.g. indexes, triggers) before it purges the table itself.
Manually Purging Recycle Bin Objects. Versions of objects that no longer need to be retained can also be purged manually via the following commands, in order of increasing destructiveness to the Recycle Bin:
See Listing 3.10 for examples of these commands.
Example: Restoring a Dropped Table. As long as the table and its dependent objects are still present in the Recycle Bin, the table can be recovered using the FLASHBACK TABLE <table name> TO BEFORE DROP; command. To illustrate the power of this new feature, I have dropped the HR.APPLICANTS table created in the previous FLASHBACK TABLE example and then purged the entire Recycle Bin via the PURGE DBA_RECYCLEBIN; command.
Next, I recreated the table via the code in Listing 3.6, and loaded with the first set of applicants. I dropped it again, created it again and loaded it with the first set as well as the second set of applicants (Listing 3.7). Finally, I dropped the table a third time, reloaded it with the first and second set of applicants, added a third set of applicants (Listing 3.11), and dropped it once again. This left three distinct iterations of the table to experiment against. Here are the results stored in the Recycle Bin after these operations:
SQL> TTITLE 'Current Recycle Bin Contents' SQL> COL object_name FORMAT A30 HEADING 'Object Name' SQL> COL type FORMAT A8 HEADING 'Object|Type' SQL> COL original_name FORMAT A20 HEADING 'Original Name' SQL> COL droptime FORMAT A20 HEADING 'Dropped On' SQL> SELECT 2 object_name 3 ,type 4 ,original_name 5 ,droptime 6 FROM dba_recyclebin 7 WHERE owner = 'HR' 8 ; Current Recycle Bin Contents Object Object Name Type Original Name Dropped On ------------------------------ -------- -------------------- -------------------- BIN$GXKs4x3zS+6aEyHIbjIO0g==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:04:25 _IDX BIN$0YQGF9xpTgOPRqsqfuHtNA==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:03:36 _IDX BIN$lpBfdfPSQfai8dZoa/DHUw==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:03:36 BIN$TdmwJaPjSIu5XTGn2vmweQ==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:04:25 BIN$eUzM3ZWMTQefYskd+7kAqw==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:04:25 BIN$ldrmjTN0R8K8qyRRCmSsxw==$0 TABLE APPLICANTS 2005-01-3:19:04:25 BIN$RSEdFMhCRcqCv5g7lYss6A==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:03:36 BIN$992SjQhHRlqHZHB4Aa/dWQ==$0 TABLE APPLICANTS 2005-01-3:19:03:36 BIN$tqINHzsMRT6EfbsgiD8eFQ==$0 INDEX APPLICANTS_LAST_NAME 2005-01-3:19:06:56 _IDX BIN$877/hfooRKuuiVAKjsE7Jg==$0 INDEX APPLICANTS_PK_IDX 2005-01-3:19:06:56 BIN$vLf00KzHSpGbMjkhnJETUw==$0 TRIGGER TR_BRIU_APPLICANTS 2005-01-3:19:06:56 BIN$xJfp8JRWQ9KalGzPUw9Ygg==$0 TABLE APPLICANTS 2005-01-3:19:06:56 12 rows selected.
By querying directly against the Recycle Bin using the dropped table's object identifier, I can confirm that the second iteration of the HR.APPLICANTS table has exactly 30 rows:
SQL> -- Most recent iteration of HR.APPLICANTS SQL> SELECT COUNT(*) FROM "BIN$xJfp8JRWQ9KalGzPUw9Ygg==$0"; COUNT(*) ---------- 45 SQL> -- Second-most recent iteration of HR.APPLICANTS SQL> SELECT COUNT(*) FROM "BIN$ldrmjTN0R8K8qyRRCmSsxw==$0"; COUNT(*) ---------- 30 SQL> -- Third-most recent iteration of HR.APPLICANTS SQL> SELECT COUNT(*) FROM "BIN$992SjQhHRlqHZHB4Aa/dWQ==$0"; COUNT(*) ---------- 16
Oracle retrieves the most recently-dropped table first, so if I issued a FLASHBACK TABLE hr.applicants TO BEFORE DROP; Oracle would restore the iteration with 45 rows. Since I want to restore only the iteration with 30 rows, I will issue a FLASHBACK TABLE <object_identifier> TO BEFORE DROP; command to insure that I have restored the desired copy of the table:
FLASHBACK TABLE "BIN$ldrmjTN0R8K8qyRRCmSsxw==$0" TO BEFORE DROP;
Alternatively, I can restore a different iteration of the table as with a different object name:
FLASHBACK TABLE "BIN$992SjQhHRlqHZHB4Aa/dWQ==$0" TO BEFORE DROP RENAME TO applicants_1;
Oracle 10g's new Logical Flashback features significantly expand an Oracle DBA's abilities to recover data, transactions and database objects that have been lost with a minimum of effort. When these new features are used in conjunction with each other and with the FLASHBACK DATABASE features described in the previous article, just about any data loss situation can be forestalled. The next article the final one in this series -- will concentrate on additional availability enhancements implemented as part of DataGuard and Logminer.
References and Additional Reading
While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:
B10734-01 Oracle Database Backup and Recovery Advanced User's Guide
B10735-01 Oracle Database Backup and Recovery Basics
B10750-01 Oracle Database New Features Guide
B10759-01 Oracle Database SQL Reference
B10770-01 Oracle Database Recovery Manager Reference