Store all consistent states of a table: FLASHBACK ARCHIVE


Before Oracle 11g it was great challenge to keep previous consistent states of a table.
One of the available method was to store the state (eg. rows of the table) In the UNDO segment. Using the following statements  or similar to them:

 ALTER SYSTEM SET UNDO_RETENTION = 3600;
 ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE ;

we can solve the problem, but the solution is VERY EXPENSIVE! Why?
Because we have to store and keep the previous data for each table that will consume huge amount of memory and unnecessary.
The solution is the FLASHBACK ARCHIVE which was introduced in Oracle11g.
Let’s consider the following scenario.
We want to keep all consistent state of our – very important – employees table.
1. Having the FLASHBACK ARCHIVE ADMINISTER system privilege we can create an archive with the following statement:

CREATE FLASHBACK ARCHIVE  archive_cl
 TABLESPACE EXAMPLE QUOTA 1 M RETENTION 1 DAY;

Sorry, one day is not enough!

ALTER FLASHBACK ARCHIVE archive_cl MODIFY RETENTION 1 MONTH;

2. We create a copy of the original employees table:

CREATE TABLE emp3
AS
SELECT * FROM employees;
SELECT COUNT(*) FROM emp3;
COUNT(*)
----------
107

Note, if we had an archived table we have to remove the archive from the table before drop it:

ALTER TABLE EMP3 NO FLASHBACK ARCHIVE ;
DROP TABLE emp3 PURGE;

3. Now we assign an archive object to the table:

ALTER TABLE EMP3  FLASHBACK ARCHIVE archive_cl;

4. It is well known  if we issue  DML statements against a table then the “old” data
will be available in the undo segment temporarly (before COMMIT;).
When the commit is processed these data must be transferred into the desgnated achive
object. We must allow  row movement for this table:

ALTER TABLE EMP3 ENABLE ROW MOVEMENT;

5. We can query the archived tables:

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME                     OWNER_NAME                     FLASHBACK_ARCHIVE_NAME                 ARCHIVE_TABLE_NAME                                    STATUS
------------------------------ ------------------------------ -------------------------------------- ----------------------------------------------------- --------
EMP3                           ORA1                           ARCHIVE_CL                             SYS_FBA_HIST_185850                                   ENABLED

6. Let’s implement a very simple transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;
EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
203 Susan      Mavris      SMAVRIS    515.123.7777        07-JUN-94          HR_REP       6500              101            40
206 William    Gietz       WGIETZ     515.123.8181        07-JUN-94          AC_ACCOUNT   8300              205           110
202 Pat        Fay         PFAY       603.123.6666        17-AUG-97          MK_REP       6000              201            20
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
200 Jennifer   Whalen      JWHALEN    515.123.4444        17-SEP-87          AD_ASST      4400              101            10

7 rows selected.

7. We can check (with a flashback query) what timestamp is suitable for us and after with FLASHBACK TABLE statement  we can withdraw a previous state of this table:

SELECT * FROM emp3 as of TIMESTAMP
SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
FLASHBACK TABLE EMP3 TO  TIMESTAMP
 SYSTIMESTAMP - INTERVAL '&Couple_of_minutes' MINUTE;
SELECT * FROM emp3;
.
.
172 Elizabeth  Bates       EBATES     011.44.1343.529268  24-MAR-99          SA_REP       7300   .15        148            80

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
173 Sundita    Kumar       SKUMAR     011.44.1343.329268  21-APR-00          SA_REP       6100   .10        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80
175 Alyssa     Hutton      AHUTTON    011.44.1644.429266  19-MAR-97          SA_REP       8800   .25        149            80
176 Jonathon   Taylor      JTAYLOR    011.44.1644.429265  24-MAR-98          SA_REP       8600   .20        149            80
177 Jack       Livingston  JLIVINGS   011.44.1644.429264  23-APR-98          SA_REP       8400   .20        149            80
178 Kimberely  Grant       KGRANT     011.44.1644.429263  24-MAY-99          SA_REP       7000   .15        149
179 Charles    Johnson     CJOHNSON   011.44.1644.429262  04-JAN-00          SA_REP       6200   .10        149            80

107 rows selected.

Let’s suppose we create an other transaction:

DELETE EMP3 WHERE EMPLOYEE_ID<200;
COMMIT;
SELECT * FROM emp3;

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL      PHONE_NUMBER        HIRE_DATE          JOB_ID     SALARY  COMM MANAGER_ID DEPARTMENT_ID
----------- ---------- ----------- ---------- ------------------- ------------------ ---------- ------ ----- ---------- -------------
201 Michael    Hartstein   MHARTSTE   515.123.5555        17-FEB-96          MK_MAN      13000              100            20
204 Hermann    Baaa        HBAER      515.123.8888        07-JUN-94          PR_REP      10000              101            70
205 Shelley    Higgins     SHIGGINS   515.123.8080        07-JUN-94          AC_MGR      12000              101           110
100 Steven     King        SKING      515.123.4567        17-JUN-87          AD_PRES     24000                             90
101 Neena      Kochhar     NKOCHHAR   515.123.4568        21-SEP-89          AD_VP       17000              100            90
102 Lex        De Haan     LDEHAAN    515.123.4569        13-JAN-93          AD_VP       17000              100            90
108 Nancy      Greenberg   NGREENBE   515.124.4569        17-AUG-94          FI_MGR      12000              101           100
114 Den        Raphaely    DRAPHEAL   515.127.4561        07-DEC-94          PU_MAN      11000              100            30
145 John       Russell     JRUSSEL    011.44.1344.429268  01-OCT-96          SA_MAN      14000   .40        100            80
146 Karen      Partners    KPARTNER   011.44.1344.467268  05-JAN-97          SA_MAN      13500   .30        100            80
147 Alberto    Errazuriz   AERRAZUR   011.44.1344.429278  10-MAR-97          SA_MAN      12000   .30        100            80
148 Gerald     Cambrault   GCAMBRAU   011.44.1344.619268  15-OCT-99          SA_MAN      11000   .30        100            80
149 Eleni      Zlotkey     EZLOTKEY   011.44.1344.429018  29-JAN-00          SA_MAN      10500   .20        100            80
150 Peter      Tucker      PTUCKER    011.44.1344.129268  30-JAN-97          SA_REP      10000   .30        145            80
151 David      Bernstein   DBERNSTE   011.44.1344.345268  24-MAR-97          SA_REP       9500   .25        145            80
156 Janette    King        JKING      011.44.1345.429268  30-JAN-96          SA_REP      10000   .35        146            80
157 Patrick    Sully       PSULLY     011.44.1345.929268  04-MAR-96          SA_REP       9500   .35        146            80
162 Clara      Vishney     CVISHNEY   011.44.1346.129268  11-NOV-97          SA_REP      10500   .25        147            80
163 Danielle   Greene      DGREENE    011.44.1346.229268  19-MAR-99          SA_REP       9500   .15        147            80
168 Lisa       Ozer        LOZER      011.44.1343.929268  11-MAR-97          SA_REP      11500   .25        148            80
169 Harrison   Bloom       HBLOOM     011.44.1343.829268  23-MAR-98          SA_REP      10000   .20        148            80
170 Tayler     Fox         TFOX       011.44.1343.729268  24-JAN-98          SA_REP       9600   .20        148            80
174 Ellen      Abel        EABEL      011.44.1644.429267  11-MAY-96          SA_REP      11000   .30        149            80

23 rows selected.

From DBA_FLASHBACK_ARCHIVE_TABLES we now the name of the archive table, let’s see the content of it:

SELECT rid, STARTSCN,ENDSCN ,xid,OPERATION, EMPLOYEE_ID , SALARY,lAST_NAME, DEPARTMENT_ID FROM SYS_FBA_HIST_185850;

RID                    STARTSCN     ENDSCN XID              O EMPLOYEE_ID SALARY LAST_NAME   DEPARTMENT_ID
-------------------- ---------- ---------- ---------------- - ----------- ------ ----------- -------------
AAAtX6AAEAAAPSTAAJ                33254659                            100  24000 King                   90
AAAtX6AAEAAAPSTAAK                33254659                            101  17000 Kochhar                90
AAAtX6AAEAAAPSTAAL                33254659                            102  17000 De Haan                90
AAAtX6AAEAAAPSTAAM                33254659                            103   9000 Hunold                 60
AAAtX6AAEAAAPSUAAV     33256562   33328409 09001C00AB5F0000 I         103   9000 Hunold                 60
AAAtX6AAEAAAPSTAAN                33254659                            104   6000 Ernst                  60
AAAtX6AAEAAAPSUAAW     33256562   33328409 09001C00AB5F0000 I         104   6000 Ernst                  60
AAAtX6AAEAAAPSTAAO                33254659                            105   4800 Austin                 60
AAAtX6AAEAAAPSUAAX     33256562   33328409 09001C00AB5F0000 I         105   4800 Austin                 60
AAAtX6AAEAAAPSTAAP                33254659                            106   4800 Pataballa              60
AAAtX6AAEAAAPSUAAY     33256562   33328409 09001C00AB5F0000 I         106   4800 Pataballa              60
AAAtX6AAEAAAPSTAAQ                33254659                            107   4200 Lorentz                60
AAAtX6AAEAAAPSUAAZ     33256562   33328409 09001C00AB5F0000 I         107   4200 Lorentz                60
AAAtX6AAEAAAPSTAAR                33254659                            108  12000 Greenberg             100
AAAtX6AAEAAAPSTAAS                33254659                            109   9000 Faviet                100
<pre>

As we can see that – according to different transactions – some rows appear two times
(eg. the employee_id <200 and later salary<10000) with different SCN’s  and some rows
apear with one SCN.
We you want to use th flashback archive feature, please always visit the
flashback archive table (in our case SYS_FBA_HIST_185850)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: