Hello dear friends, this post will be about one of Oracle's Flashback technologies: Flashback Data Archive. Hope you will like it.
Flashback technologies are basically used to recover the lost data and access the historical data. I’ve mentioned you about the Flashback Database operation in one of my previous posts. In Flashback Database operation, all database operations produce flashback archive files and all database turns to SCN when going back. However, it’s generally expected that rather than all database, it should go back in certain tables. At this point, Oracle Flashback Data Archive offers a way out. With FBDA, historical change of data is stored for a given period and enables to access the data in history.
Benefits of FDBA
- Easy to configure.
- Minimum loss of performance.
- Optimum protection of foot print.
- The data is stored in the database for the given period.
- Historical data is in the compacted form which is an advantage for storage.
- Easy to query using “as of”.
- Historical data is always safe.
- Unauthorised users cannot view the historical data.
- Noone can update the historical data.
Abilities of FBDA
- Columns may be added to tracked tables.
- Automatically hinders any DDL operations that may affect table history.
- Tracking time of tables may be limited with the retention parameter.
- Automatically deletes the historical data that expired protecting retention.
- Gives a warning when the given space for flashback exceeds 90%.
- To maintain high performance, it carries as much data as it can at one time from Undo to flashback archives. This time is 5 minutes by default and it tunes itself as a result of system activities
Let’s create a simple table as a sample. Enable “row movement” in the table to run the Flashback command. Create a tablespace to store the Flashback data and create a flashback archive in that tablespace with 1 year retention parameter. Complete the fbda enable operation by defining the created flashback archive policy to the table.
SQL> create table fb_table (id number, text varchar2(20));
SQL> alter table fb_table enable row movement;
SQL> create tablespace fb_tbs datafile 'fb_tbs.dbf' size 10m;
SQL> create flashback archive f1_arch tablespace fb_tbs retention 1 year;
SQL> alter table fb_table flashback archive f1_arch;Let’s add data to test, checking the current SCN and date.
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
SQL> insert into fb_table values (1,'A');
SQL> commit;
SQL> select current_scn, controlfile_time from v$database;
SQL> insert into fb_table values (2,'B');
SQL> commit;Before the second insert in the table, run the flashback table command on SCN and this is the operation of flashback table.
SQL> select * from fb_table;
SQL> flashback table fb_table to scn <BEFORE_INSERT_SCN>;
SQL> select * from fb_table;Check the current SCN and date in the table again and add data to test. This time, use timestamp and go back to the previous date without insert operation.
SQL> select * from fb_table;
SQL> select current_scn, controlfile_time from v$database;
SQL> insert into fb_table values (3,'C');
SQL> commit;
SQL> select * from fb_table;
SQL> flashback table fb_table to timestamp <TIME_BEFORE_INSERT>;
SQL> select * from fb_table;As you see in the insert operation, it’s possible to go back both in delete and update operations. This is how flashback table operation is completed. You can query the tables and policies where FBDA is active as below.
SQL> select flashback_archive_name from dba_flashback_archive_tables where table_name='FB_TABLE';
SQL> drop table fb_table;When you try to drop a flashback table, you see an ORA-55610 error. In this case, first turn off the flashback in the table and drop it under control.
SQL> alter table fb_table no flashback archive;
SQL> drop table fb_table;If you’ve dropped a table with flashback on, you can bring it back with the command below.
SQL> flashback table fb_table to before drop;If you want to edit an existing flashback policy:
SQL> alter flashback archive f1_arch retention 2 year;If you want to change flashback policy of an existing table:
SQL> alter table fb_table flashback archive f2_arch;If you want to delete flashback archive files older than 10 days:
SQL> alter flashback archive f1_arch purge before timestamp (systimestamp - interval '10' day);If you want to query a table’s state the day before:
SQL> select * from fb_table as of timestamp sysdate-1;Hope to see you in new posts, take care.