No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 Introduction to Flashback Technology Manju Narem 01-Aug-2010 No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 Flashback Technology Oracle Flashback Technology provides a set of new features to view and recover data from past. The Flashback features offer the capability to recover from logical corruptions while the database is online. With Flashback Technology recovery operates just on the changed data, so the time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle9i introduced Flashback Query to provide a simple yet powerful mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database. Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. New Flashback features provided in Oracle Database 10g: Flashback Database Flashback Drop Flashback Table Flashback Version Query Flashback Transaction Query According to the Oracle documentation, Flashback technologies are applicable in repairing the following user errors. Erroneous or malicious DROP TABLE statements Erroneous or malicious update, delete or insert transactions Erroneous or malicious batch job or wide-spread application errors Flashback Query Flashback Query is introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature. The Flashback Query mechanism is most effective when you use Automatic Undo Management. New in Oracle Database 10g Release 1 is the ability to query data in the past for more than 5 days if the UNDO_RETENTION is set for greater then 5 days. Oracle will maintain the undo for that period of time as long as the Undo Tablespace datafiles are allocated enough disk space. Steps required to ensure a database is enabled to use the Flashback features that are dependent upon undo information: No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 1. Ensure that the database is using an undo tablespace. Setting the UNDO_MANAGEMENT initialization parameter to AUTO specifies this. 2. Set the UNDO_RETENTION initialization parameter to a value that causes undo to be kept for a length of time that allows success of your longest query back in time or to recover from human errors. 3. To guarantee that unexpired undo will not be overwritten, set the RETENTION GUARANTEE clause for the undo tablespace. Apart from above, In order to use Flashback queries you need 1. FLASHBACK privilege for all or a subset of objects 2. Execute privileges on the dbms_flashback package The Flashback Query mechanism is flexible enough to be used in many situations. You can: query data as it existed in the past. compare current data with past data. You can compare individual rows or do more complex comparisons such as finding the intersection or union. recover deleted or changed data. There are two approaches to use the flashback queries. One is using timestamp and the other is using SYSTEM CHANGE NUMBER -SCN -to identify the point we want to go back to. Both approaches uses the AS OF clause as well as an Oracle supplied package DBMS_FLASHBACK. Flashback Query with AS OF clause: For example if we want to recover data we have accidentally deleted for some employee records from the EMPLOYEE table and have committed the transaction. SQL> INSERT INTO Employee (SELECT * FROM Employee AS OF TIMESTAMP (’20-AUG-07 9:40:50’,’DD-MON-YY HH24:MI:SS’)); We can use either timestamp (as shown in above query) or SCN to tell the system how far to go back. The SCN number can be obtained before the transaction is initiated by using the GET_SYSTEM_CHANGE_NUMBER function of the DBMS_FLASHBACK utility as follows. You do not have to be in the flashback mode to run this statement. SQL> SELECT DBMS_FLASHBACK.Get_System_Change_Number FROM Dual; SQL> INSERT INTO Employee (SELECT * FROM Employee AS OF SCN 10500401199); We have recovered the data this time using the SCN number Flashback query Using the DBMS_FLASHBACK package: No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 Before Oracle 9i release 2, the only way to use the flashback query feature was through the use of package DBMS_FLASHBACK. To use this package, the user had to specify the intention to enter the flashback mode by supplying the time to which the user wished to go back to. To enter the flashback mode use the ENABLE_AT_TIME function of the DBMS_FLASHBACK package. To resume normal operation use DISABLE function. Eg: 1 DECLARE 2 CURSOR emp_cur IS 3 SELECT * FROM EMPLOYEE; 4 e_rec emp_cur%rowtype; 5 BEGIN 6 DBMS_FLASHBACK.ENABLE_AT_TIME (’20-AUG-07 9:40:50’); 7 open emp_cur; 8 DBMS_FLASHBACK.DISABLE; 9 LOOP 10 fetch emp_cur into e_rec; 11 EXIT WHEN emp_cur%NOTFOUND; 12 INSERT INTO Employee VALUES 13 (e_rec.emp_id, 14 e_rec.first_name, 15 e_rec.last_name, 16 e_rec.dept_id); 17 END LOOP; 18 CLOSE emp_cur; 19 COMMIT; 20 END; To recover data into out Employee table using the SCN number instead of time as shown above, replace line 6 with the line below. DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (10500401199); Until we exit flashback query mode by issuing DBMS_FLASHBACK.DISABLE DML statements INSERT, UPDATE, or DELETE are not allowed. If a DML statement has been issued, it must be committed before we can enter the flashback mode. FLASHBACK mode can be entered only at the beginning of a transaction. Flashback Table Flashback Table provides the ability to recover a table or a set of tables to a specified point in time easily when online. Flashback Table restores the tables with its associated attributes, current indexes, triggers and constraints. It does not require to find and restore application specific properties. No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 Flashback Table Performs the restore operation online Restores all data in a specified table to a previous point in time described by a timestamp or SCN Automatically restores all of the table attributes that are necessary for an application to function with the flashed back table like indexes, triggers etc Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication are flashed back. Maintains data integrity as specified by constraints. Oracle preserves all dependent objects and the referential integrity. Provides the ability to revert it back to its original state even after a flashback operation. EG: FLASHBACK TABLE Employee TO TIMESTAMP (’20-AUG-07 9:40:50’,'DD-MON-YY HH24: MI: SS'); Before discussing FLASHBACK DROP, I will discuss about RECYCLE BIN RECYCLE BIN The Recycle Bin is a virtual container where all dropped objects reside. The dropped objects occupy the same space as when they were created. Once the tables are dropped any associated objects such as indexes, constraints, nested tables, and other dependant objects are prefixed with BIN$, they are not moved. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view. EG: SELECT Object_Name, Original_Name, Type FROM User_Recyclebin; Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. You can delete a single item in Recycle Bin or empty all items using PURGE EG: PURGE TABLE Employee; --Single Item Purge PURGE recyclebin; --All Items Purge The Recycle Bin objects are counted against a user's quota. Objects in the Recycle Bin will be automatically purged by the space reclamation process if a user creates a new table or adds data that causes their quota to be exceeded. the tablespace needs to extend its file size to accommodate create/insert operations. No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 Flashback Drop Flashback Drop provides a safety when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin. If you drop any thing, you can undrop it with Flashback Drop. EG: FLASHBACK TABLE Employee TO BEFORE DROP; Flashback Database Flashback Database is faster than traditional point-in-time recovery. The traditional recovery method uses backups and redo log files; Flashback Database uses a new type of log file called the Flashback Database log. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database. Flashback Database reduces the time required to recover the database to a point in time. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database. Once Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs. You can enable Flashback Database using the following steps: 1. Make sure the database is in archive mode. 2. Configure the recovery area by setting the two parameters: DB_RECOVERY_FILE_DEST DB_RECOVERY_FILE_DEST_SIZE 3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature: SQL> STARTUP MOUNT EXCLUSIVE; SQL> ALTER DATABASE FLASHBACK ON; 4. Set the Flashback Database retention target: DB_FLASHBACK_RETENTION_TARGET To enable Flashback Database, Issue the following command: SQL> select flashback_on from v$database; No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 To disable disable Flashback Database, issue the following command SQL> ALTER DATABASE FLASHBACK OFF; Monitor logging in the Flashback Database logs: SQL> select begin_time, flashback_data, db_data, redo_data, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_stat; Monitor the Flashback Database retention target: SQL> select * from v$flashback_database_log; Note: The default value for flashback retention time is 1400 minutes. The destination and the size of the recovery area are setup using the db_recovery_file_dest and b_recovery_file_dest_size initializatin parameters. SQL> select estimated_flashback_size from v$flashback_database_log; Flashback Versions Query Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that existed between the time the query was issued and a point in time in the past. It is achived by using Automatic Undo Management. For any table, a new row version is created every time the COMMIT statement is executed. The Flashback Versions Query returns a row for each version of the row that existed in the time interval you specify The Flashback Versions Query is invoked by using the VERSIONS BETWEEN clause of the SELECT statement. It takes two forms as follows: VERSIONS BETWEEN TIMESTAMP [lower bound] AND [upper bound]; or VERSIONS BETWEEN SCN [lower bound] AND [lower bound]. The lower and upper boundaries can either be specific timestamps/SCNs or the keywords MINVALUE and MAXVALUE. These keywords instruct Oracle to retrieve all available data versions. The age of the data available is determined by the undo_retention parameter. Oracle provides a variety of metadata with each version of our data. The metadata is exposed via a number of pseudo-columns that we can use with our flashback version queries. These pseudo-columns are as follows: No.1, 68/10, 3rd & 4th Floor, 1st Main, 1st Cross, Off Koramangala Ring Road, Bangalore 560 047. India info@dtech-education.com info@divewebbgroup.com 9663247255 9886104025 9741431318 VERSIONS_STARTTIME (start timestamp of version); VERSIONS_STARTSCN (start SCN of version); VERSIONS_ENDTIME (end timestamp of version); VERSIONS_ENDSCN (end SCN of version); VERSIONS_XID (transaction ID of version); and VERSIONS_OPERATION (DML operation of version). Eg: SQL> SELECT z, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION FROM EMPLOYEE VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE ORDER BY VERSIONS_ENDTIME; Flashback Transaction Query Flashback Transaction Query is used to identify other changes that were done by the transaction and to request the undo SQL to reverse those changes, once you identify an erroneous transaction. The FLASHBACK_TRANSACTION_QUERY view gives transaction history and undo SQL. This includes the SQL required to reverse each change. Queries against this view are documented as "flashback transaction queries" and require the SELECT ANY TRANSACTION system privilege. Eg: SQL> SELECT xid,operation, undo_sql FROM flashback_transaction_query WHERE table_owner = USER AND table_name = 'EMPLOYEE' ORDER BY start_timestamp; The VERSIONS_XID pseudo-column gives us the key to this view (the XID column) for specific versions of data. The UNDO_SQL column shows us the reversal of every change we made. Note: Oracle 10g provides two conversion functions, TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP to convert between timestamp and SCN. Timestamps are mapped to SCNs with a precision of approximately 3 seconds (in 9i this was 5 minutes). SYSTIMESTAMP and LOCALTIMESTAMP functions are used to capture timestamps, we can capture SCNs using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER function. Summary Flashback revolutionizes recovery by operating on just the changed data and on other hand, there will be no down time needed. Flashback technology removes the complexity of recovery while decreasing the time it takes to recover from unpredictable human errors. Manju