FlashBack Architecture
There are a number of flashback levels
row level | flashback query, flashback versions query, flashback transaction query |
table level | flashback table, flashback drop |
database level | flashback database |
Oracle 10g has several error-correction techniques that use undo data, however they are only available if you use automatic undo management (AUM),
There are two other flashback technologies that do not use the undo data, they use flashback logs and recyclebin instead.
DBMS_FLASHBACK, flashback table query, flashback transaction query, flashback version query and select .. as of .. statements all use the undo segments. Flashback database uses the flashback logs and flashback drop uses the recycled bin.
When using flashback, if any operations violate a constraint the flashback operation will be rolled back, you can disable constraints but it’s probably not a good idea. If you have a table using a foreign key it is a good idea to flashback both tables. Flashback technology requires you to lock the whole table if it cannot it will fail immediately.
RMAN can only do flashback database and no other flashback technology.
Flashback Query
Using flashback query involves using a select statement with an AS OF clause. you can select data from a past point in time. If you get a ORA-08180 it means that the data is no longer available in the undo segments.
Privilege | grant flashback on table_test to pvalle; grant flashback on any table to pvalle; |
Flashback query (time) | select * from employees as of timestamp select employee_id, name from hr.employee as of timestamp (systimestamp - interval '15' minute); |
Flashback query (SCN) | select * from employees as of scn 4542; Note: using a scn will put you with 3 secs if you need to be dead accurate use timestamps instead. |
Take the whole session back in time | grant execute on dbms_flashback to test01; execute dbms_flashback.enable_at_time(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')); execute dbms_flashback.disable; |
Reinserting | insert into employees insert into employees select * from employees as of scn = 4542; |
List the flashback entries | select * from flashback_transaction_query; |
Obtain a time from an SCN | select scn_to_timestamp(1408640) as ts from dual; |
Obtain SCN from time point | select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual; |
What is in the undo tablespace | select begin_time, end_time, tuned_undoretention from v$undostat; Note: the time is in seconds |
Flashback Version Query
Flashback version query provides you with all the versions of a row between two points in time or SCN, this is useful if you want to audit a table finding out what happened to a row. However there are some points to remember:
The limitations of flashback version query are:
The most useful columns to obtain are below:
Flashback version query (time) | select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn, versions_operation as operation, empname |
Flashback version query (SCN) | select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn, versions_operation as operation, empname from employees versions between scn minvalue and maxvalue where emp_id = 863; |
Obtain a time from an SCN | select scn_to_timestamp(1408640) as ts from dual; |
Obtain SCN from time point | select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual; |
Flashback Transaction Query
Identifies which transaction or transactions were responsible for a certain change in a table's data during a specified time period. Basically it queries the flashback_transaction_query view. It provides the SQL code that will undo the change, flashback transaction query can use an index path to retrieve data instead of reading the entire redo log file.
Flashback transaction considerations:
Consider setting the retention guarantee option for the undo tablespace, this will ensure that the unexpired data in the undo segments is preserved.
Flashback transaction query will contain the following columns
If you have chained rows or use clustered tables then oracle recommends that you should turn on supplemental logging on at the database level.
Privilege | grant select any transaction to pvalle; |
Supplemental logging | alter database add supplemental log data; |
Display undo segments | select operation, undo_sql, table_name from flashback_transaction_query; |
Flashback transaction query | select operation, undo_sql, table_name Note: this will give you the SQL to reserver the change that was applied to the database,just cut and paste to use. |
Obtain a time from an SCN | select scn_to_timestamp(1408640) as ts from dual; |
Obtain SCN from time point | select timestamp_to_scn(to_timestamp('03-12-2007 08:00:00', 'DD-MM-YYYY HH:MI:SS')) as scn from dual; |
You use flashback version query to obtain the xid and then use the xid in the flashback transaction query statement
flashback version and flashback transaction query | select versions_xid as xid, versions_startscn as start_scn, versions_endscn as end_scn, versions_operation as operation, XID start_scn end_scn operation empname salary Now use the XID in the flashback transaction query to obtain the SQL to undo the change. select xid, start_scn, commit_scn, operation, logon_user, undo_sql XID start_scn commit_scn operation user undo_sql |
Flashback Table
There are two distinct table related flashback table features in oracle, flashback table which relies on undo segments and flashback drop which lies on the recyclebin not the undo segments.
Flashback table lets you recover a table to a previous point in time, you don't have to take the tablespace offline during a recovery, however oracle acquires exclusive DML locks on the table or tables that you are recovering, but the table continues to be online.
When using flashback table oracle does not preserve the ROWIDS when it restores the rows in the changed data blocks of the tables, since it uses DML operations to perform its work, you must have enabled row movement in the tables that you are going to flashback, only flashback table requires you to enable row movement.
If the data is not in the undo segments then you cannot recover the table by using flashback table, however you can use other means to recover the table.
Restriction on flashback table recovery
Privilege | grant flashback on table_test to pvalle; grant flashback on any table to pvalle; |
Enable row movement | alter table test enable row movement; |
Display row movement | select table_name, row_movement from user_tables; |
Flashback table (SCN) | flashback table test to scn 4587309871; |
Flashback table (time) | flashback table employees to timestamp to_timestamp flashback table employees to timestamp (systimestamp - interval '15' minute); flashback table employess to timestamp (sysdate - 1); flashback table employees, depts to timestamp (sysdate - 1); |
Enable triggers | flashback table employees to timestamp (sysdate -1) enable triggers; Note: Oracle disables triggers by default when falshing back a table |
Flashback drop lets you reinstate previously dropped tables exactly as it was before the drop, below is a table of what is kept where when a table is dropped:
If two tables exist in the recyclebin with the same name the newest one will be restored unless you state which one you want to restore. If you restore a table it is removed from the recyclebin.
Recover | flashback table <table_name> to before drop |
Recover and rename | flashback table <table_name> to before drop rename to <new name>; Note: use if table name already exists |
Recover same name table | flashback table 'BIN$hfkjdshfkhs' to before drop; |
List recycle bin | select object_name, original_name, type from user_recyclebin; select object_name, original_name, type from dba_recyclebin; select * from recyclebin; (shows only current user info) show recyclebin; |
drop table completely | drop table <table_name> purge; |
drop table from r/bin | purge table <table_name> Note: table will be gone from recyclebin |
truncate table | truncate table Note: table will not be in the recyclebin |
drop user | drop user test cascade Note: will not store anything in the recyclebin |
purge recyclebins | purge recyclebin; (purge user recyclebin) purge dba_recyclebin;(purges all recyclebins) purge tablespace test user test03; (purge test03 from tablespace test) |
List dropped table contents | select * from "BIN$NwM/FEjuSUORrgxHUPR3WA==$0"; Note: the double quotes |
Naming Convention | BIN$globalUID$version |
Space pressure on a tablespace will cause it to purge the recyclebins of the users within that tablespace, it is based on a FIFO order. When a tablespace has the auto extend feature turned on it will clear down the recyclebin first, then auto extend.
Limitations on flashback drop:
The database can be taken back in time by reversing all work done sequentially. The database must be opened with resetlogs as if an incomplete recovery has happened. This is ideal if you have a database corruption (wrong transaction, etc) and require the database to be rewound before the corruption occurred. If you have media or a physical problem a normal recovery is required.
Flashback database is not enabled by default, when enabled flashback database a process (RVWR – recovery Writer) copies modified blocks to the flashback buffer. This buffer is then flushed to disk (flashback logs). Remember the flashback logging is not a log of changes but a log of the complete block images. Not every changed block is logged as this would be too much for the database to cope with, so only as many blocks are copied such that performance is not impacted. Flashback database will construct a version of the data files that is just before the time you want. The data files probably will be in a inconsistent state as different blocks will be at different SCN’s, to complete the flashback process, Oracle then uses the redo logs to recover all the blocks to the exact time requested thus synchronizing all the data files to the same SCN. Archiving mode must be enabled to use flashback database. An important note to remember is that Flashback can never reserve a change only to redo them.
The advantage in using flashback database is speed and convenience with which you can take the database back in time.
You can use rman, sql and Enterprise manager to flashback a database. If the flash recovery area does not have enough room the database will continue to function but flashback operations may fail. It is not possible to flashback one tablespace, you must flashback the whole database. If performance is being affected by flashback data collection turn some tablespace flashbacking off.
You cannot undo a resized data file to a smaller size. When using ‘backup recovery area’ and ‘backup recovery files’ controlfiles , redo logs, permanent files and flashback logs will not be backed up.
Enable | select log_mode from v$database; (must use archivelog mode) |
Monitoring | alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; |
Flashback Buffer | select * from v$sgastat where name like 'flashback%'; |
Flashback database example | startup mount; When happy……………… alter database open resetlogs; |
Flashback using RMAN | flashback database to time = to_date('15-02-07 10:00:00', 'dd-mm-yy hh24:mi:ss'); |
Tablespace Flashback Configuration |
|
Turn flashback on (tablespace) | alter tablespace <tablespace_name> flashback on; (must be in mount mode) |
Turn flashback off (tablespace) | alter tablespace <tablespace_name> flashback off; (this can be done in open or mount mode) |
Display tablespaces with flashback | select name, flashback_on from v$tablespace; |
Flashback space usage | select * from v$flash_recovery_area_usage; |
Note: if one or more tablespaces are not generating flashback data, then before carrying out a flashback operation the files making up the tablespace must be taken offline. Offline files are ignored by recover and flashback. Remember that you must make these files to the same point as the flashback otherwise the database will not open.
Flashback Recovery Area
The alert log and DBA_OUTSTANDING_ALERTS will hold status information regarding the flash recovery area. You can use the commands backup copy or backup for flash recovery area. Controlfiles and redo logs are permanently stored in the flash recovery area.
Monitoring | select * from v$recovery_file_dest; Note: this details space_used, space_limit, space_reclaimable, # of files |
What is using the Flashback recovery area | select * from v$flash_recovery_area_usage; |
Backing up flashback area | rman> backup recovery area; rman> backup recovery files; |
Using Restore Points
There is a new enhancement to recovery techniques by allowing you to create restore points, a restore point is alias for an SCN, which elimates the need to research and record SCN's or timestamps which you need for flashback database and flashback table operations, all you need to do is refer to the restore point when recovering.
The restore point does not guarantee that the data will remain in the flashback logs necessary for a flashback database operation to succeed under all circumstances. By creating a restore point that will guarantee that all data will be preserved, you can be assured that the restore will be successful, remember that you will need enough space in the flash recovery area to hold the necessary data. A guaranteed restore point does not depend on flashback logs, thus you can create a guaranteed restore point with flashback logging turned off, they use a logging mechanism that's similar to flashback logs but it's separate from them, thus if you are using guaranteed restore points it better to turn off flashback logging otherwise you may end up filling up the flash recovery area.
One note is that you can only restore back to the restore point, you cannot restore back to a point in time using restore points, you must then use the backups and archived logs to do a point in time recovery.
Not Guaranteed |
|
create | create restore point large_update; |
remove | drop restore point large_update; |
Guaranteed |
|
create guaranteed restore point | create restore point test_guarantee guarantee flashback database; |
remove guaranteed restore point | drop restore point test_guarantee; |
Other Operations |
|
Using a restore point | flashback database to restore point large_update; flashback table test to restore point table_update; |
Displaying restore points | select name, scn, storage_size, time, guarantee_flashback_database from v$restore_point; |
Flashback databse running | select flashback_on from v$database; Note: you should get a restore point only reply if using restore points |