oracle 11g flashback data archive setup and should be using SCN for constant results

Every day we are getting more and more pressure to keep the historical data. We can do that by writing multiple scipts or at application level writing logic to do so.

But oracle can do this for you.

Problem: We have a table and there are lots of insert, update and deletes happening all times which is causing historic data to disappear from the table once the change is done. How can we use oracle to solve this problem.

Solution: Oracle offers a solution with version 11g and it is called flashback data archive. Once setup it will move the changes to the table to another table and manage it for the retention period.

How to setup Flash back archive:

1. Create a tablespace to hold flashback data archives
2. Create flashback archive with some retention (users need FLASHBACK ARCHIVE ADMINISTER privilege to create this archive.)
3. alter table to start archiving changes.
4. select data with AS OF {SCN of TIMESTAMP}
5. Done

Steps:

SQL> create tablespace fbdata datafile '/u01/app/oracle/oradata/new_location/fbdata01.dbf' size 2G;
10:33:34 SQL>  create flashback archive fb_harvey tablespace fbdata retention 10 year;
Flashback archive created.
10:33:48 SQL> col FLASHBACK_ARCHIVE_NAME for a30
10:33:52 SQL> select flashback_archive_name, flashback_archive#,
10:33:52   2  retention_in_days,
10:33:52   3  status
10:33:52   4  from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME         FLASHBACK_ARCHIVE# RETENTION_IN_DAYS STATUS
------------------------------ ------------------ ----------------- -------
FB_HARVEY                                       1              3650
10:33:52 SQL>
10:33:52 SQL> set lines 60
10:34:06 SQL> desc hdhillon.status;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 NO                                     NUMBER
 TYPE                                   VARCHAR2(10 CHAR)
10:34:13 SQL> set lines 270
10:34:16 SQL> select * from hdhillon.status;
no rows selected
10:34:29 SQL> select * from dba_flashback_archive_tables;
no rows selected
10:34:40 SQL>
10:34:40 SQL> alter table hdhillon.status flashback archive fb_harvey;
Table altered.
10:34:45 SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME   FLASHBACK_ARCHIVE_NAME   ARCHIVE_TABLE_NAME   STATUS

---------- ------------ ------------------------ -------------------- --------

STATUS     HDHILLON     FB_HARVEY                SYS_FBA_HIST_129223  ENABLED

10:34:49 SQL>
10:35:00 SQL> conn hdhillon/*****
Connected.
10:35:16 SQL>
11:13:56 SQL> insert into status values(1,'working');
1 row created.
11:14:16 SQL>
11:14:16 SQL> commit;
Commit complete.
11:14:18 SQL> col current_timestamp for a40
11:14:18 SQL> select current_scn, current_timestamp(2) , no, type from v$database , status t;
CURRENT_SCN CURRENT_TIMESTAMP(2)                                                                NO TYPE
----------- --------------------------------------------------------------------------- ---------- ----------
  132432317 14-MAR-14 11.14.22.43 AM +11:00                                                      1 working
11:14:22 SQL>
11:18:20 SQL> delete from status where no =1;
1 row deleted.
11:18:39 SQL> commit;
Commit complete.
11:18:43 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
no rows selected
11:18:51 SQL> insert into status values(2,'Error');
1 row created.
11:19:02 SQL> commit;
Commit complete.
11:19:05 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
CURRENT_SCN TS                                          NO TYPE
----------- ----------------------------------- ---------- ----------
  132437486 14-MAR-14 11.19.12.15 AM +11:00              2 Error
11:19:12 SQL>
11:19:48 SQL> update status set type = 'Warning' where no =2;
1 row updated.
11:21:01 SQL> commit;
Commit complete.
11:21:03 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
CURRENT_SCN TS                                          NO TYPE
----------- ----------------------------------- ---------- ----------
  132437561 14-MAR-14 11.21.07.62 AM +11:00              2 Warning
11:21:07 SQL> 

Now selecting data:

11:22:14 SQL> select * from status as of SCN 132432317;
        NO TYPE
---------- ----------
         1 working
11:35:00 SQL> select * from status as of SCN 132437486;
        NO TYPE
---------- ----------
         2 Error
11:37:14 SQL> select * from status as of SCN 132437561;
        NO TYPE
---------- ----------
         2 Warning
11:37:24 SQL> 

My Observations:

I have noticed that if we use as of timestamp to select data from table the we are not getting consistent results below are some samples:

11:19:38 SQL> select timestamp_to_SCN (to_timestamp('14-MAR-14 11:19:13' , 'DD-MON-YY HH24:MI:SS') ) from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('14-MAR-1411:19:13','DD-MON-YYHH24:MI:SS'))
-------------------------------------------------------------------------
                                                                132437487
11:20:06 SQL> select * from status as of SCN 132437487;
        NO TYPE
---------- ----------
         2 Error
11:20:22 SQL> select * from status as of timestamp(to_timestamp('14-MAR-14 11:19:13' , 'DD-MON-YY HH24:MI:SS'));
        NO TYPE
---------- ----------
         1 working
11:20:26 SQL>

11:21:10 SQL> 

11:21:10 SQL> select * from status as of SCN 132437561;
        NO TYPE
---------- ----------
         2 Warning
11:21:16 SQL> select * from status as of timestamp(to_timestamp('14-MAR-14 11:21:08' , 'DD-MON-YY HH24:MI:SS'));
        NO TYPE
---------- ----------
         1 working
11:21:28 SQL>