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 ->
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 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;