TOTAL RECALL - Flashback Data Archive

Administer Flashback Data Archive - FBA

1.      Documentation Tahiti ->

            Oracle Database 11g Release 2 (11.2) ->

                        Masters Book List ->

                                    Advanced Application Developer's Guide ->

                                                12 Using Oracle Flashback Technology ->

                                                            Using Flashback Data Archive (Oracle Total Recall)

2.     Flashback Data Archive, also known as Oracle Total Recall, allows recording and automatically clear all changes that occur in a set of tables in an Oracle 11g DB. Also you can check the data in these tables at any point in time up to a certain maximum defined by us (RETENTION).

By default, FBA (Flashback Data Archive) is disabled for all tables. We can turn to any table as long as the following conditions are met:

· We must have the FLASHBACK ARCHIVE privilege on that table
· The table cannot be of type NESTED, CLUSTER, TEMPORARY, or EXTERNAL REMOTE
· The table cannot contain columns or LONG NESTED

The privilege that allows us to manipulate a FBA setting is "FLASHBACK ARCHIVE ADMINISTER" but we will use SYSDBA so we should not worry about it.

3.      We perform tests on the HR diagram. First we need to configure FBA and enable any table.

- The first thing you need is a TBS where it will store the data of FBA

CREATE TABLESPACE FBA DATAFILE '/u01/app/oacle/oradata/OCM/Tbsfba01.dbf' SIZE500M;

- Create the file by default to FBA

CREATE FLASHBACK ARCHIVE DEFAULT FLA1 TABLESPACE FBA QUOTA 1500M RETENTION 2 YEAR;

CREATE FLASHBACK ARCHIVE FLA2 TABLESPACE TBS1 QUOTA 300M RETENTION 8 MONTH ;

SELECT * FROM DBA_FLASHBACK_ARCHIVE;

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS ;

- Setting Default Flashback Archive to FLA1

ALTER FLASHBACK ARCHIVE FLA1 SET DEFAULT ;

- Activate FBA for the EMPLOYEES table

ALTER TABLE HR.EMPLOYEES FLASHBACK ARCHIVE;

SELECT *

FROM DBA_FLASHBACK_ARCHIVE_TABLE

WHERE TABLE_NAME=’EMPLOYEES’

AND OWNER_NAME=’HR’ ;

- We can see the configuration of FBA in the following views

SELECT * FROM DBA_FLASHBACK_ARCHIVE;

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TS;

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES;

4.      Now we can perform tests on the EMPLOYEES table.

- Get the current date and time to keep track of our tests

- Eg " 2014/12/22 01:53:29"

- We may also use the SNA DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

SELECT SYSDATE FROM DUAL;

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

- We modify some records

- We delete an employee

DELETE FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=192;

COMMIT;

-- - Modify the salary of a worker repeatedly

UPDATE HR.EMPLOYEES SET SALARY=12000 WHERE EMPLOYEE_ID=200;

COMMIT;

UPDATE HR.EMPLOYEES SET SALARY=12500 WHERE EMPLOYEE_ID=200;

COMMIT;

UPDATE HR.EMPLOYEES SET SALARY=12550 WHERE EMPLOYEE_ID=200;

COMMIT;

- We consulted on the historical data stored in FBA

- Let's see what workers have left the company since we activate FBA

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME

  FROM HR.EMPLOYEES

  AS OF TIMESTAMP TO_TIMESTAMP('2014/12/22 01:53:29','YYYY/MM/DD HH24:MI:SS')

MINUS

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME

  FROM HR.EMPLOYEES;

- We get all salary increments Lisa Ozer ( EMPLOYEE_ID = 200 )

- In this example we get all versions between two dates

- And also get the versions_starttime VERSIONS_STARTSCN pseudcolumnas

COL VERSIONS_STARTTIME FORMAT A40

SELECT     VERSIONS_STARTTIME,

           VERSIONS_STARTSCN,

           FIRST_NAME,

           LAST_NAME,

           SALARY

  FROM HR.EMPLOYEES VERSIONS BETWEEN TIMESTAMP

  TO_TIMESTAMP('2014/12/22 01:53:29','YYYY/MM/DD HH24:MI:SS') AND

  SYSTIMESTAMP

WHERE

  EMPLOYEE_ID=200;

- We can also view the data on a given SCN

SELECT FIRST_NAME, LAST_NAME, SALARY

  FROM HR.EMPLOYEES

  AS OF SCN 6750989

WHERE

  EMPLOYEE_ID=200;

5.     FBA allows us to capture a recording scheme:

Add , Delete , Rename or Edit a column

• Delete or Truncate a partition

• Rename or truncate a table ( a table with FBA Delete fails with error ORA- 55610 )

Some changes (eg:  MOVE / SPLIT / CHANGE PARTITIONS ) methods require DBMS_FLASHBACK_ARCHIVE package to do it. Consider for example , how we can detach and attach a table at historical case we have to perform any of these operations.

- Create a clone EMPLOYEES table with a CONSTRAINT

CREATE TABLE HR.EMPLOYEES_FBA AS SELECT * FROM HR.EMPLOYEES;

ALTER TABLE HR.EMPLOYEES_FBA ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);

- Activate FBA on it

ALTER TABLE HR.EMPLOYEES_FBA FLASHBACK ARCHIVE;

- We modify a record

UPDATE HR.EMPLOYEES_FBA SET SALARY=10000 WHERE EMPLOYEE_ID=203;

COMMIT;

- Disabling an CONSTRAINT

ALTER TABLE HR.EMPLOYEES_FBA DISABLE CONSTRAINT EMPLOYEE_PK;

- Back -enable (ERROR ORA- 55610 )

ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;

- To enable CONSTRAINT need to detach the tablet of their historical

- First let's get what historical associated table

- Eg " SYS_FBA_HIST_65433 "

SELECT * FROM DBA_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME='EMPLOYEES_FBA';

- Table disassociate

EXEC DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('HR','EMPLOYEES_FBA');

- Enable CONSTRAINT

ALTER TABLE HR.EMPLOYEES_FBA ENABLE CONSTRAINT EMPLOYEE_PK;

- Associate the table to resume operations FBA

EXEC DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA('HR','EMPLOYEES_FBA');

6.     Cleaning environment..

- You can purge the data from a file FBA (optional )

ALTER FLASHBACK ARCHIVE FLA1 PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1'DAY);

- We disable FBA tables used for

ALTER TABLE HR.EMPLOYEES NO FLASHBACK ARCHIVE;

ALTER TABLE HR.EMPLOYEES_FBA NO FLASHBACK ARCHIVE;

- We delete the file FBA

DROP FLASHBACK ARCHIVE FLA1;

- We delete the TBS

DROP TABLESPACE FBA INCLUDING CONTENTS AND DATAFILES;

- We delete the temporary table EMPLOYEES_FBA

DROP TABLE HR.EMPLOYEES_FBA;