| 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. Configuring Flashback Table 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. Flashback Table Using Automatic Undo Remember, if an active transaction needs undo space and the undo space does not have any free space available, then the system starts reusing undo space as needed. If needed, use the RETENTION GUARANTEE clause to specify that all unexpired undo data will be preserved. |
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. Flashback Table in Action You can use one of the following tools to flashback the table to a point in time or a specific SCN: 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: 1. | Grant the necessary privileges for flashback (if needed).
Row Movement with Flashback Table Oracle does not allow you to enable row movement on a table and then flashback a table to a time prior to issuing the enabling row-movement command. If you plan to use Flashback Table, you need to make sure to enable row movement on your target tables as early as possible. If you ever hit this limitation, you will need to use the flashback transaction query command, covered later in the Flashback Transaction Query section. |
SQL> grant flashback, select, insert, delete, alter on flashtest to stroupe;
or
SQL> grant flashback any table to stroupe;
| 2. | Enable row movement on any candidate tables:
SQL> alter table flashtest enable row movement;
| 3. | Gather necessary information for flashback operation
SQL> select * from flashtest; ID ---------- 1000 2000 3000 4000 SQL> select current_scn from v$database; CURRENT_SCN ----------- 771390
| 4. | Introduce table-level errors:
SQL> delete from flashtest where id in (2000,4000); SQL> commit; SQL> select * from flashtest; ID ---------- 1000 3000
| 5. | Invoke Flashback Table to correct these user errors:
SQL> flashback table flashtest to scn 771390; Flashback complete. SQL> select * from flashtest; ID ---------- 1000 2000 3000 4000
Alternatively, you could also use the time-based FLASHBACK TABLE command:
[View full width] SQL> flashback table flashtest to timestamp to_timestamp('2005-03-15 11:05:01','YYYY-MM-DD  HH:MI:SS');
What Is SYS_TEMP_FBT? When using the Flashback Table feature, a global temporary table called SYS_TEMP_FBT is created in your schema unconditionally. This required table is used to track internal flashback information. To remove this table, simply truncate, and then drop the table when you are finished with your flashback operation. Currently in 10g Release 1, Oracle support is tracking this issue as bug 3076151, which is fixed with 10g Release 2. |
|
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. 
|
Troubleshooting Flashback Table 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: 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;
|
