Another new flashback feature introduced with the Oracle 10g database is Flashback Table. Flashback Table, much like Flashback Database, allows you to recover data to a specific point in time without restoring from a backup; however, the recovery is handled at the table level. When the Flashback Table feature is invoked, the data stored in the tables and all objects that are related to the table (indexes, constraints, triggers, and so on) are also restored. This new feature is extremely useful when a user accidentally inserts, updates, or deletes the wrong rows in a table or even accidentally drops a table if he or she has the proper privileges to do so.
In previous versions of the database, the recovery operation for a single table or multiple tables involved a full point-in-time recovery, restoring specific tables from a clone, importing from a previous table-level export, utilizing Oracle LogMiner to extract the SQL from the undo column to “rewind” the table to a specific point, or in some cases utilizing the 9i flashback query operation to capture the previous version of the target data in a user-defined cursor and then correcting any errors as needed. Depending on your specific situation, any of the recovery tasks listed could become a long and labor-intensive operation. Now, with the 10g Flashback Table feature, you can easily and quickly recover any database table from accidental drops or modifications, and in some cases without the involvement of a DBA.
To perform its necessary operations, Flashback Table utilizes the data from your undo segments. The undo tablespace (as you may have learned from Oracle 9i) utilizes the UNDO_RETENTION initialization parameter to specify the amount of committed undo data that should be retained in the database. Because UNDO_RETENTION determines the recovery window of the Flashback Table feature, you should set this parameter based on your flashback requirements or user needs.
In addition to setting the correct undo parameters, the Flashback Table feature also requires you to enable row movement on each target table. In previous versions, row movement was used to enable or disable the migration of a row to a new partition if its key was updated. Flashback Table utilizes row movement to aid in the recovery of table data from the defined undo segments.
When you have configured your undo settings properly and enabled row management for your target table, the last prerequisite for using Flashback Table is to grant the necessary privileges.
You must grant the FLASHBACK object privilege or FLASHBACK ANY TABLE system privilege to any user who wants to utilize the Flashback Table feature. In addition, the flashback user must also have the SELECT, INSERT, DELETE, and ALTER TABLE object privileges on the target table.
You can use one of the following tools to flashback the table to a point in time or a specific SCN:
FLASHBACK TABLE SQL command
Enterprise Manager 10g Flashback Table wizard
As stated previously, you must enable automatic undo management, set your UNDO_RETENTION parameter to a reasonable setting, enable row management on your target tables, and grant the necessary FLASHBACK privileges. Depending on your recovery condition, you will need to choose either an SCN or time-based flashback time. To determine the appropriate flashback time for your operation, you can use flashback versions query and flashback transaction query. Both of these new flashback features are discussed later in this chapter in the sections “Flashback Versions Query” and “Flashback Transaction Query” respectively.
Following is a brief example of using the FLASHBACK TABLE command via SQL:
You can also use the Flashback Table feature via Enterprise Manager. Following is a step-by-step example of how to use Flashback Table with OEM:
1. | From the OEM home page, choose the Maintenance tab, and then click the Perform Recovery link under Backup/Recovery. |
2. | The Perform Recovery page opens, as shown in Figure 17.7. From the Object Type drop-down menu, select Tables. Figure 17.7. The 10g OEM Perform Recovery home page. |
3. | When the page is refreshed, you will need to choose between flashing back existing tables or dropped tables, as shown in Figure 17.8. Click on the Flashback Existing Tables option, and click Next. Figure 17.8. Specifying table recovery type from the 10g OEM Recovery wizard. |
4. | When the Perform Recovery: Point-in-time page appears (see Figure 17.9), you will need to specify the point in time to which to recover. You can enter in a table so Oracle can evaluate row changes and decide the best point in time to recover to, or you can enter in a known time or SCN. For the purpose of this example, we will choose a time to recover. Figure 17.9. Specifying table recovery options from the 10g OEM Recovery wizard. |
5. | The Perform Recovery: Flashback Tables page opens, as shown in Figure 17.10. Choose the table you wish to flashback and click Next. Figure 17.10. Specifying tables to use flashback table recovery from the 10g OEM Recovery wizard. |
6. | If any dependencies exist, you will be prompted with the Dependencies Option page. Choose the best option for managing your table dependencies. If you do not have any dependencies with your table, you will bypass this page, and the Perform Recovery: Review page will appear (see Figure 17.11). From here, you can review the changes you are about to enter. Also from this page you can click the Show Row Changes button to see what you are about to recover as well as the SQL that OEM will use to perform the recovery for you. When confirmed, click Submit to invoke Flashback Table recovery. Figure 17.11. Flashback Table Summary from the 10g OEM Recovery wizard. |
7. | After the recovery is complete, a confirmation page will appear, telling you that the specified table was recovered (see Figure 17.12). From here, click OK to return to the Maintenance window. Figure 17.12. Confirmation page for Flashback Table Recovery. |
Even though the Flashback Table feature is fairly straightforward, there are a few limitations worth noting. Currently in Oracle 10g Release 1, you cannot use the Flashback Table feature for the following:
Any table in the SYS schema
Tables that are part of a cluster
Materialized views
Advanced Queuing (AQ) tables
Static data dictionary Tables
System tables
Nested tables
Remote tables via database links
Partitions of a table
Temporary tables
Most commands are supported by Flashback Table. That said, if the following DDL commands are issued on supported tables, the Flashback Table command does not work:
ALTER TABLE .. DROP COLUMN
ALTER TABLE .. DROP PARTITION
CREATE CLUSTER
TRUNCATE TABLE
ALTER TABLE .. MOVE
If you do run into this limitation, you will receive the ORA-01455 error message: Unable to read data – table definition has changed:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 816801 SQL> truncate table flashtest; Table truncated. SQL> flashback table flashtest to scn 816801; flashback table flashtest to scn 816801 * ERROR at line 1: ORA-01466: unable to read data - table definition has changed
As previously noted, when you use Flashback Table to recover a table or tables to a specific point in time, all associated objects with the table data are also restored, such as indexes, constraints, triggers, and so on. There are, however, a few gotchas with associated table objects when using the Flashback Table feature:
The FLASHBACK TABLE command will fail if one of your referential integrity constraints is violated. Because your FLASHBACK TABLE command did fail, the table remains as it was prior to your issuing the command.
By default, all triggers are disabled for the duration of the Flashback Table operation. After the operation has completed, all triggers are returned to the state they were in prior to the flashback. You can, however, use the ENABLE TRIGGERS option of the FLASHBACK TABLE command to override this default behavior.
SQL> flashback table flashtest to scn 816851 enable triggers;
3.137.164.241