DBA Hub

📋Steps in this guide1/9

Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)

Check out the latest enhancements to flashback data archive (FDA) in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
15 views
1

Licensing Changes (All Database Versions for Free)

In previous releases, flashback data archive used compression features from the advanced compression option, so it could only be used with enterprise edition and the advanced compression option. In Oracle 12c, the default when creating flashback data archives is no compression, so it is available for free in all editions of the database. This new default setting has also been back-ported to 11.2.0.4, making it free in all editions that can be patched to that level.
2

User-Context Tracking

Although FDA has always been able to track the changes to data, it was not able to provide a complete audit of the changes unless columns were present in the table to indicate who made those changes. In Oracle 12c, the contents of the contexts available from calls, including , can be stored along with the data changes, allowing for a complete audit of both the data changes and the user environment setting. This allows FDA to be used in place of custom audit tables within applications. The following code creates a new tablespace and an FDA with a 1 year retention period. The privilege granted on the FDA to the user. Information about the FDA is displayed using the and views. The following queries are available as fda.sql and fda_ts.sql . Create a table in the test user schema and associate it with the FDA. Information about the FDA and the associated table is displayed using the view. The following query is available as fda_tables.sql . To make sure the context information is stored with the transaction data, we need to use the procedure, passing one of the following parameter values. - : Only basic auditing attributes from the context are stored. - : All contexts available to the user via the function are stored. - : No context information is stored. In this case use , so we capture the and custom context values. Remember to wait at least 15 seconds between creating the FDA associated table and committing any DML or information may be lost. Create a custom context and management package for use in the test. Insert and amend some data. Between each action we alter the value and alter the value in our custom context. Check the context information is working as expected. The following flashback version query shows the history of the data changes in the table, including the value from the stored information. Stored context information is retrieved using the function, which requires the transaction ID (XID), context namespace and parameter name. Some of the flashback version query pseudocolumns have been commented out to reduce the size of the output. Remember, if you want to drop the table, you must first remove it from the FDA. Some of the following examples make use of this table, so delay dropping it until you have worked through them.

Code/Command (click line numbers to comment):

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE fda_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
ALTER USER test QUOTA UNLIMITED ON fda_ts;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
  QUOTA 10G RETENTION 1 YEAR;

GRANT FLASHBACK ARCHIVE ON fda_1year TO test;
GRANT FLASHBACK ARCHIVE ADMINISTER TO test;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;

GRANT CREATE ANY CONTEXT TO test;

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN create_time FORMAT A20
COLUMN last_purge_time FORMAT A20

SELECT owner_name,
       flashback_archive_name,
       flashback_archive#,
       retention_in_days,
       TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
       TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
       status
FROM   dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;

OWNER_NAME           FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME      STATUS
-------------------- ---------------------- ------------------ ----------------- -------------------- -------------------- -------
SYS                  FDA_1YEAR                               1               365 06-JAN-2015 19:30:57 06-JAN-2015 19:30:57 DEFAULT

SQL>


SET LINESIZE 150

COLUMN flashback_archive_name FORMAT A22
COLUMN tablespace_name FORMAT A20
COLUMN quota_in_mb FORMAT A11

SELECT flashback_archive_name,
       flashback_archive#,
       tablespace_name,
       quota_in_mb
FROM   dba_flashback_archive_ts
ORDER BY flashback_archive_name;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_MB
---------------------- ------------------ -------------------- -----------
FDA_1YEAR                               1 FDA_TS               10240

SQL>

CONN test/test@pdb1

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT tab_1_pk PRIMARY KEY (id)
) FLASHBACK ARCHIVE fda_1year;

-- Use ALTER TABLE to associate an existing table.
-- ALTER TABLE tab1 FLASHBACK ARCHIVE fda_1year;

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
ORDER BY owner_name, table_name;

OWNER_NAME           TABLE_NAME           FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS
-------------------- -------------------- ---------------------- -------------------- -------------
TEST                 TAB1                 FDA_1YEAR              SYS_FBA_HIST_95999   ENABLED

SQL>

CONN sys@pdb1 AS SYSDBA

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

CONN test/test@pdb1

CREATE OR REPLACE CONTEXT test_context USING test_ctx_api;

CREATE OR REPLACE PACKAGE test_ctx_api AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2);

END test_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY test_ctx_api AS
 
PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2) AS
BEGIN
  DBMS_SESSION.set_context('test_context', LOWER(p_name), p_value);
END;

END test_ctx_api;
/

CONN test/test@pdb1

EXEC DBMS_SESSION.set_identifier('Peter Pan');
EXEC test.test_ctx_api.set_value('my_attribute','First Action');

INSERT INTO tab1 VALUES (1, 'ONE');
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Parker');
EXEC test_ctx_api.set_value('my_attribute','Second Action');

UPDATE tab1
SET    description = 'TWO'
WHERE  id = 1;
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Rabbit');
EXEC test_ctx_api.set_value('my_attribute','Third Action');

UPDATE tab1
SET    description = 'THREE'
WHERE  id = 1;
COMMIT;

CONN test/test@pdb1

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
COLUMN session_user FORMAT A20
COLUMN client_identifier FORMAT A20
COLUMN my_attribute FORMAT A20
SET LINESIZE 200

SELECT versions_startscn,
       --versions_starttime, 
       versions_endscn,
       --versions_endtime,
       versions_xid,
       versions_operation,
       description,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute
FROM   tab1 
       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP
WHERE  id = 1
ORDER BY versions_startscn;

 VERSIONS_STARTSCN    VERSIONS_ENDSCN VERSIONS_XID     V DESCRIPTION SESSION_USER         CLIENT_IDENTIFIER    MY_ATTRIBUTE
------------------ ------------------ ---------------- - ----------- -------------------- -------------------- --------------------
           2536699            2536792 060010000B0A0000 I ONE         TEST                 Peter Pan            First Action
           2536792            2536826 0A00110076060000 U TWO         TEST                 Peter Parker         Second Action
           2536826                    020003005B080000 U THREE       TEST                 Peter Rabbit         Third Action

SQL>

CONN sys@pdb1 AS SYSDBA

ALTER TABLE test.tab1 NO FLASHBACK ARCHIVE;
DROP TABLE test.tab1 PURGE;
3

Export/Import Table History

The package contains routines to allow the history of a specified table to be exported and imported. Check the name of the archive associated with the table of interest. To export the history data, call the procedure, passing the owner and name of the FDA backed table whose history you want to export. A table called is created. Populate the table with data from the archive you identified previously. Once exported, the table can be renamed if you wish. If the data needs to be loaded into another schema, it can be transferred using any of the usual methods, for example data pump. You can also manually amend the contents to load information from alternative audit sources into the FDA. The contents of a history table can be loaded into the FDA using the procedure. The owner and name of the FDA-backed table are specified, along with the name of the history table and options for how any existing history data is handled.

Code/Command (click line numbers to comment):

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
47
48
49
50
51
52
53
54
55
56
SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
ORDER BY owner_name, table_name;

OWNER_NAME           TABLE_NAME           FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS
-------------------- -------------------- ---------------------- -------------------- -------------
TEST                 TAB1                 FDA_1YEAR              SYS_FBA_HIST_77672   ENABLED

SQL>

CONN test/test@pdb1

BEGIN
  DBMS_FLASHBACK_ARCHIVE.create_temp_history_table(
    owner_name1 => 'TEST',
    table_name1 => 'TAB1');
END;
/

DESC temp_history
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 RID                                                            VARCHAR2(4000)
 STARTSCN                                                       NUMBER
 ENDSCN                                                         NUMBER
 XID                                                            RAW(8)
 OPERATION                                                      VARCHAR2(1)
 ID                                                             NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

SQL>

INSERT /*+ APPEND */ INTO temp_history
SELECT * FROM SYS_FBA_HIST_77672;

COMMIT;

BEGIN
  DBMS_FLASHBACK_ARCHIVE.import_history (
    owner_name1       => 'TEST',
    table_name1       => 'TAB1', 
    temp_history_name => 'TEMP_HISTORY', -- Default Setting.
    options           => DBMS_FLASHBACK_ARCHIVE.NODELETE); -- Allowable values: NODROP, NOCOMMIT, NODELETE
END;
/
4

Database Hardening

Oracle database 12c includes a new feature called database hardening, which simplifies the management of flashback data archive for groups of tables, collectively known as applications. The management of applications is done using the package. Create the following tables, which will represent the application tables. Create a new application using the procedure. Add the tables to the application using the procedure. The application is built, but the tables are not currently associated with the FDA as the application is not enabled. Calling the procedure enables FDA for all the tables in the application. Calling the procedure disables FDA for all the tables in the application. Tables can be removed from the application ( ) and the application dropped ( ) as follows.

Code/Command (click line numbers to comment):

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
CONN test/test@pdb1

CREATE TABLE app_tab1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 5;

ALTER TABLE app_tab1 ADD CONSTRAINT app_tab1_pk PRIMARY KEY (id);

CREATE TABLE app_tab2 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 5;

ALTER TABLE app_tab2 ADD CONSTRAINT app_tab2_pk PRIMARY KEY (id);

CONN test/test@pdb1

BEGIN
  DBMS_FLASHBACK_ARCHIVE.register_application(
    application_name       => 'MY_APP',
    flashback_archive_name => 'FDA_1YEAR');
END;
/


CONN sys@pdb1 AS SYSDBA

COLUMN appname FORMAT A20
COLUMN faname FORMAT A20

SELECT a.appname,
       b.faname
FROM   sys_fba_app a
       JOIN sys_fba_fa b ON a.fa# = b.fa#;

APPNAME              FANAME
-------------------- --------------------
MY_APP               FDA_1YEAR

SQL>

CONN test/test@pdb1

BEGIN
  DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
    application_name => 'MY_APP',
    table_name       => 'APP_TAB1',
    schema_name      => 'TEST');

  DBMS_FLASHBACK_ARCHIVE.add_table_to_application (
    application_name => 'MY_APP',
    table_name       => 'APP_TAB2',
    schema_name      => 'TEST');
END;
/


CONN sys@pdb1 AS SYSDBA

COLUMN appname FORMAT A20
COLUMN table_owner FORMAT A20
COLUMN table_name FORMAT A20

SELECT a.appname,
       c.owner AS table_owner,
       c.object_name As table_name
FROM   sys_fba_app a
       JOIN sys_fba_app_tables b ON a.app# = b.app#
       JOIN dba_objects c ON b.obj# = c.object_id
ORDER BY 1,2,3;

APPNAME              TABLE_OWNER          TABLE_NAME
-------------------- -------------------- --------------------
MY_APP               TEST                 APP_TAB1
MY_APP               TEST                 APP_TAB2

SQL>

CONN test/test@pdb1

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
WHERE  table_name LIKE 'APP_TAB%'
ORDER BY owner_name, table_name;

no rows selected

SQL>

CONN test/test@pdb1

BEGIN
  DBMS_FLASHBACK_ARCHIVE.enable_application(
    application_name => 'MY_APP');
END;
/

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
WHERE  table_name LIKE 'APP_TAB%'
ORDER BY owner_name, table_name;

OWNER_NAME           TABLE_NAME           FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS
-------------------- -------------------- ---------------------- -------------------- -------------
TEST                 APP_TAB1             FDA_1YEAR              SYS_FBA_HIST_96008   ENABLED
TEST                 APP_TAB2             FDA_1YEAR              SYS_FBA_HIST_96010   ENABLED

SQL>

CONN test/test@pdb1

BEGIN
  DBMS_FLASHBACK_ARCHIVE.disable_application(
    application_name => 'MY_APP');
END;
/

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
WHERE  table_name LIKE 'APP_TAB%'
ORDER BY owner_name, table_name;

no rows selected

SQL>

BEGIN
  DBMS_FLASHBACK_ARCHIVE.remove_table_from_application(
    application_name => 'MY_APP',
    table_name       => 'APP_TAB1',
    schema_name      => 'TEST');

  DBMS_FLASHBACK_ARCHIVE.remove_table_from_application(
    application_name => 'MY_APP',
    table_name       => 'APP_TAB2',
    schema_name      => 'TEST');

  DBMS_FLASHBACK_ARCHIVE.drop_application(
    application_name => 'MY_APP');
END;
/
5

FDA Optimization (Compression)

If you are licensed for the advanced compression option in enterprise edition, you can take advantage of the clause when using flashback data archive. Using optimization enables Advanced Row Compression, Advanced LOB Compression, Advanced LOB Deduplication, segment-level compression tiering, and row-level compression tiering for the specified FDA. The following example shows how the clause works.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
-- Default and equivalent optimization.
CREATE FLASHBACK ARCHIVE fda_2year_no_opt TABLESPACE fda_ts RETENTION 2 YEAR;
CREATE FLASHBACK ARCHIVE fda_3year_no_opt TABLESPACE fda_ts RETENTION 3 YEAR NO OPTIMIZE DATA;

-- Including optimization.
CREATE FLASHBACK ARCHIVE fda_4year_opt TABLESPACE fda_ts RETENTION 4 YEAR OPTIMIZE DATA;

-- Remove.
DROP FLASHBACK ARCHIVE fda_4year_opt;
DROP FLASHBACK ARCHIVE fda_3year_no_opt;
DROP FLASHBACK ARCHIVE fda_2year_no_opt;
6

Hybrid Columnar Compression (HCC) Support

Flashback data archive is now fully supported against tables using Hybrid Columnar Compression (HCC), whether on Exadata or other supported storage platforms.
7

Multitenant Support (CDB/PDB) (12.1.0.2 Onward)

Flashback data archive was not supported in multitenant environments in the initial release of Oracle database 12c. Support for multitenant environments was introduced in patchset 12.1.0.2, as described here . All the examples in this article are performed against a multitenant database.
8

Relevant SYS Tables

The SYS owned tables relevant to flashback data archive are shown below.

Code/Command (click line numbers to comment):

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
SELECT table_name
FROM   dba_tables
WHERE  owner = 'SYS'
AND    table_name LIKE '%FBA%'
ORDER BY table_name;

TABLE_NAME
----------------------------
SYS_FBA_APP
SYS_FBA_APP_TABLES
SYS_FBA_BARRIERSCN
SYS_FBA_COLS
SYS_FBA_CONTEXT
SYS_FBA_CONTEXT_AUD
SYS_FBA_CONTEXT_LIST
SYS_FBA_DL
SYS_FBA_FA
SYS_FBA_PARTITIONS
SYS_FBA_PERIOD
SYS_FBA_TRACKEDTABLES
SYS_FBA_TSFA
SYS_FBA_USERS
SYS_MFBA_NCHANGE
SYS_MFBA_NROW
SYS_MFBA_NTCRV
SYS_MFBA_STAGE_RID
SYS_MFBA_TRACKED_TXN

SQL>
9

Flashback Time Travel (19c)

From 19c onward some of the flashback functionality has been slightly rebranded as "Flashback Time Travel". Typically this is a combination of flashback data archive and queries. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!