Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn how to create a flashback archive that allows you to automatically track and archive transaction data changes.
1. We query the existing datafiles and which tablespace they belong to.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [Primary-1] SQL> desc dba_data_files; Name Null? Type ----------------------------------------- -------- ---------------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) [Primary-1] SQL> select file_id, file_name, tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME ---------- ------------------------------------------------------------ ------------------------------ 1 +DATA/primary/datafile/system.256.965571365 SYSTEM 2 +DATA/primary/datafile/sysaux.257.965571365 SYSAUX 3 +DATA/primary/datafile/undotbs1.258.965571365 UNDOTBS1 4 +DATA/primary/datafile/users.259.965571365 USERS 5 +DATA/primary/datafile/example EXAMPLE 6 +DATA/primary/datafile/undotbs2.265.965571593 UNDOTBS2 6 rows selected. |
2. We create a new tablespace to hold the Historical Data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [Primary-1] SQL> create tablespace fda_tbs datafile '+DATA' size 1G; Tablespace created. [Primary-1] SQL> select file_id, file_name, tablespace_name from dba_data_files order by 1; FILE_ID FILE_NAME TABLESPACE_NAME ---------- ------------------------------------------------------------ ------------------------------ 1 +DATA/primary/datafile/system.256.965571365 SYSTEM 2 +DATA/primary/datafile/sysaux.257.965571365 SYSAUX 3 +DATA/primary/datafile/undotbs1.258.965571365 UNDOTBS1 4 +DATA/primary/datafile/users.259.965571365 USERS 5 +DATA/primary/datafile/example EXAMPLE 6 +DATA/primary/datafile/undotbs2.265.965571593 UNDOTBS2 7 +DATA/primary/datafile/fda_tbs.275.976967985 FDA_TBS 7 rows selected. |
3. We create Flashback Data Archive.
1 2 3 4 5 6 | [Primary-1] SQL> create flashback archive fda1 tablespace fda_tbs quota 100M retention 1 year; Flashback archive created. [Primary-1] SQL> ! ps -ef |grep fbda | grep -v grep oracle 1105 1 0 12:04 ? 00:00:00 ora_fbda_primary1 |
4. We select the table to be archived.
1 2 3 | [Primary-1] SQL> alter table hr.employees flashback archive fda1; Table altered. |
5. Some updates are made in 2 tables, FDA and non-FDA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 12:54:12 [Primary-1] SQL> update hr.departments set department_name='IT' where department_id=210; 1 row updated. 12:54:52 [Primary-1] SQL> commit; Commit complete. 12:54:54 [Primary-1] SQL> update hr.employees set salary=100 where employee_id=200; 1 row updated. 12:55:38 [Primary-1] SQL> commit; Commit complete. |
6. We check whether the data is received by making queries from FDA and non-FDA tables.
1 2 3 4 5 6 7 8 9 10 11 | 13:53:59 [Primary-1] SQL> select department_name from hr.departments as of timestamp to_timestamp('2018-05-24 12:54:00','YYYY-MM-DD HH24:MI:SS') where department_id=210; DEPARTMENT_NAME ------------------------------ IT Support 13:54:21 [Primary-1] SQL> select salary from hr.employees as of timestamp to_timestamp('2018-05-24 12:54:00','YYYY-MM-DD HH24:MI:SS') where employee_id=200; SALARY ---------- 6000 |
7. It is checked again after a few days and the result is observed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [Primary-1] SQL> set time on 09:08:45 [Primary-1] SQL> select department_name from hr.departments as of timestamp to_timestamp('2018-05-24 12:54:00','YYYY-MM-DD HH24:MI:SS') where department_id=210; select department_name from hr.departments as of timestamp to_timestamp('2018-05-24 12:54:00','YYYY-MM-DD HH24:MI:SS') where department_id=210 * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_1254879796$" too small 09:08:48 [Primary-1] SQL> select salary from hr.employees as of timestamp to_timestamp('2018-05-24 12:54:00','YYYY-MM-DD HH24:MI:SS') where employee_id=200; SALARY ---------- 6000 |
8. Undo_Retention parameter is checked.
1 2 3 4 5 | 13:54:52 [Primary-1] SQL> show parameter undo_retention NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 |
Even though it was 15 minutes, it was brought from UNDO. We will check again later.
9. We get information about FDA from Dictionary Views.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | [Primary-1] SQL> desc dba_flashback_archive; Name Null? Type OWNER_NAME VARCHAR2(30) FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) FLASHBACK_ARCHIVE# NOT NULL NUMBER RETENTION_IN_DAYS NOT NULL NUMBER CREATE_TIME TIMESTAMP(9) LAST_PURGE_TIME TIMESTAMP(9) STATUS VARCHAR2(7) [Primary-1] SQL> column FLASHBACK_ARCHIVE_NAME format a30 [Primary-1] SQL> select OWNER_NAME, FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS, CREATE_TIME, STATUS from dba_flashback_archive; OWNER_NAME FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS CREATE_TIME STATUS ------------------------------ ------------------------------ ----------------- --------------------------------------------------------------------------- ------- SYS FDA1 365 24-MAY-18 12.04.24.000000000 PM [Primary-1] SQL> desc DBA_FLASHBACK_ARCHIVE_TS; Name Null? Type FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) FLASHBACK_ARCHIVE# NOT NULL NUMBER TABLESPACE_NAME NOT NULL VARCHAR2(30) QUOTA_IN_MB VARCHAR2(40) [Primary-1] SQL> select FLASHBACK_ARCHIVE_NAME, TABLESPACE_NAME, QUOTA_IN_MB from DBA_FLASHBACK_ARCHIVE_TS; FLASHBACK_ARCHIVE_NAME TABLESPACE_NAME QUOTA_IN_MB ------------------------------ ------------------------------ ---------------------------------------- FDA1 FDA_TBS 100 [Primary-1] SQL> desc DBA_FLASHBACK_ARCHIVE_TABLES; Name Null? Type TABLE_NAME NOT NULL VARCHAR2(30) OWNER_NAME NOT NULL VARCHAR2(30) FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255) ARCHIVE_TABLE_NAME VARCHAR2(53) STATUS VARCHAR2(13) [Primary-1] SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME, STATUS from DBA_FLASHBACK_ARCHIVE_TABLES; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------- ------------- EMPLOYEES HR FDA1 SYS_FBA_HIST_88047 ENABLED |
