Questo articolo è un copia e incolla dell’articolo ufficiale che trovate qui:
http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
A Fresh Look at Auditing Row Changes
By Connor McDonald
Triggers can provide auditing information, but there’s a future in flashback.
Barbara is looking forward to a long-overdue vacation. She’s been working extremely hard to get a suite of new features for the company’s human resources (HR) application through construction, testing, and finally deployment into the production environment. Satisfaction ratings from the HR department are high, and no serious bugs have been reported.
But Barbara’s vacation plans are about to be stymied. As often happens in IT projects, some requirements get pushed to the side during the construction phase, with the intent of tackling them later. Now, an email titled “Urgent: Need to capture all row changes for audit compliance” arrives in Barbara’s inbox. Audit functions, although important from a security and compliance perspective, are typically invisible to application stakeholders and are hardly the exciting part of any functionality showcase. So the auditing requirements for Barbara’s application enhancements were pushed aside early in the construction phase, only to resurface as an emergency postimplementation requirement.
Fortunately for Barbara, auditing row changes is a common need throughout the IT industry. A quick web search reveals the most prevalent solution: for each table in her application, there should be a partnering audit table to hold the history of row changes, plus some additional metadata.
For example, for the EMPLOYEES table in Barbara’s application:
SQL> desc EMPLOYEES Name Null? Type ————————————————————————————— ———————— ———————————— EMPLOYEE_ID NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) VACATION_BALANCE NUMBER(6,2)
there will be an appropriately named equivalent table for capturing any row changes:
SQL> desc AUDIT_EMPLOYEES Name Null? Type ————————————————————————————— ———————— ———————————— AUD_WHO VARCHAR2(20) AUD_WHEN DATE AUD_OPERATION VARCHAR2(1) AUD_MODULE VARCHAR2(30) EMPLOYEE_ID NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) VACATION_BALANCE NUMBER(6,2)
Some additional metadata columns are present in the AUDIT_EMPLOYEES table for capturing who performed the change, when the change occurred, what operation was performed (INSERT, UPDATE, or DELETE), and from which program or module the change originated. Barbara’s application is already live in the production environment, so the function for populating the AUDIT_EMPLOYEES table must be transparent to the existing code. Thus, database triggers are the natural choice.
Listing 1 shows the trigger Barbara has built to capture all changes to the EMPLOYEES table.
Code Listing 1: Audit data capture trigger for EMPLOYEES
SQL> create or replace
2 trigger TRG_AUDIT_EMPLOYEES
3 after insert or update or delete on EMPLOYEES
4 for each row
5 declare
6 l_operation varchar2(1) :=
7 case when updating then 'U'
8 when deleting then 'D'
9 else 'I' end;
10 begin
11 if updating or inserting then
12 insert into AUDIT_EMPLOYEES
13 (aud_who
14 ,aud_when
15 ,aud_operation
16 ,aud_module
17 ,employee_id
18 ,first_name
19 ,last_name
20 ,email
21 ,phone_number
22 ,hire_date
23 ,job_id
24 ,salary
25 ,commission_pct
26 ,manager_id
27 ,department_id)
28 values
29 (user
30 ,sysdate
31 ,l_operation
32 ,sys_context('USERENV','MODULE')
33 ,:new.employee_id
34 ,:new.first_name
35 ,:new.last_name
36 ,:new.email
37 ,:new.phone_number
38 ,:new.hire_date
39 ,:new.job_id
40 ,:new.salary
41 ,:new.commission_pct
42 ,:new.manager_id
43 ,:new.department_id);
44 else
45 insert into AUDIT_EMPLOYEES
46 (aud_who
47 ,aud_when
48 ,aud_operation
49 ,aud_module
50 ,employee_id
51 ,first_name
52 ,last_name
53 ,email
54 ,phone_number
55 ,hire_date
56 ,job_id
57 ,salary
58 ,commission_pct
59 ,manager_id
60 ,department_id)
61 values
62 (user
63 ,sysdate
64 ,l_operation
65 ,sys_context('USERENV','MODULE')
66 ,:old.employee_id
67 ,:old.first_name
68 ,:old.last_name
69 ,:old.email
70 ,:old.phone_number
71 ,:old.hire_date
72 ,:old.job_id
73 ,:old.salary
74 ,:old.commission_pct
75 ,:old.manager_id
76 ,:old.department_id);
77 end if;
78 end;
79 /
Trigger created.
A quick series of tests confirms the correct operation of the trigger, so Barbara forges ahead and creates auditing tables and associated triggers for all the tables in the HR application.
Hidden Costs
Barbara’s vacation plans remind her that one of the tasks performed each night on the EMPLOYEES table is an update of the vacation balance for all employees in the organization. Before her auditing changes went live in the production environment, this task was efficient—it had to be, because it temporarily locks all rows in the EMPLOYEES table.
SQL> update EMPLOYEES 2 set VACATION_BALANCE = VACATION_BALANCE + 1; 219136 rows updated. Elapsed: 00:00:03.01
A few seconds is an acceptable lock time. In the rare instance that anyone updates a single employee record outside of business hours, that person would experience only a slight pause—nothing that would be cause for alarm. But now that Barbara’s auditing changes have been implemented, a problem has surfaced. The vacation adjustment takes significantly longer:
SQL> update EMPLOYEES 2 set VACATION_BALANCE = VACATION_BALANCE + 1; 219136 rows updated. Elapsed: 00:00:17.92
Barbara performs a trace on the vacation update to gauge the impact of auditing. Listing 2 shows the TKprof-formatted output of the trace file.
Code Listing 2: TKprof output from trace of vacation adjustment
update EMPLOYEES
set VACATION_BALANCE = VACATION_BALANCE + 1
call count cpu elapsed disk query current rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse 1 0.00 0.00 0 1 0 0
Execute 1 15.61 17.47 0 2380 223618 219136
Fetch 0 0.00 0.00 0 0 0 0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total 2 15.61 17.48 0 2381 223618 219136
INSERT INTO AUDIT_EMPLOYEES (AUD_WHO ,AUD_WHEN ,AUD_OPERATION ,AUD_MODULE ,
EMPLOYEE_ID ,FIRST_NAME ,LAST_NAME ,EMAIL ,PHONE_NUMBER ,HIRE_DATE ,JOB_ID ,
SALARY ,MANAGER_ID ,DEPARTMENT_ID ,VACATION_BALANCE)
VALUES
(USER ,SYSDATE ,:B12 ,SYS_CONTEXT('USERENV','MODULE') ,:B1 ,:B2 ,:B3 ,:B4 ,
:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 )
call count cpu elapsed disk query current rows
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 219136 8.60 8.71 0 5865 245856 219136
Fetch 0 0.00 0.00 0 0 0 0
——————— —————— ———————— —————————— —————————— —————————— —————————— ——————————
total 219137 8.60 8.71 0 5865 245856 219136
From the trace file, Barbara observes that the INSERT statement for the audit table added 8.71 seconds of elapsed time to the process. It was called 219,136 times—once for every changed row, even though only a single UPDATE statement was issued. This also incurs the same number of context switches between the SQL and PL/SQL engines, further adding to the overall elapsed time. Barbara’s auditing changes are not as transparent to the application as they initially appeared to be. She needs a fix…fast.
To reduce the context switching, Barbara must refactor the audit trigger code to use bulk binding. This is not a trivial undertaking, because row changes need to be buffered as they occur and then applied in one pass after the issuing data manipulation language (DML) is complete. In earlier versions of Oracle Database, this would mandate a PACKAGE definition along with three separate triggers. But as of Oracle Database 11g, a single compound trigger can be used to provide the required functionality.
Listing 3 shows the compound trigger Barbara built to improve the efficiency of her audit capture function.
Code Listing 3: Compound trigger for audit capture
SQL> create or replace
2 trigger TRG_AUDIT_EMPLOYEES
3 for insert or update or delete
4 on EMPLOYEES compound trigger
5
6 --
7 -- an array structure to buffer all the row changes
8 --
9 type t_row_list is
10 table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
11
12 l_audit_rows t_row_list;
13
14 l_operation varchar2(1) :=
15 case when updating then 'U'
16 when deleting then 'D'
17 else 'I' end;
18
19 before statement is
20 begin
21 --
22 -- initialize the array
23 --
24 l_audit_rows.delete;
25 end before statement;
26
27 after each row is
28 begin
29 --
30 -- at row level, capture all the changes into the array
31 --
32 l_audit_rows(l_audit_rows.count+1).aud_who := sys_context('USERENV','SESSION_USER');
33 l_audit_rows(l_audit_rows.count).aud_when := sysdate;
34 l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
35 l_audit_rows(l_audit_rows.count).aud_module := sys_context('USERENV','MODULE');
36
37 if updating or inserting then
38 l_audit_rows(l_audit_rows.count).employee_id := :new.employee_id;
39 l_audit_rows(l_audit_rows.count).first_name := :new.first_name;
40 l_audit_rows(l_audit_rows.count).last_name := :new.last_name;
41 l_audit_rows(l_audit_rows.count).email := :new.email;
42 l_audit_rows(l_audit_rows.count).phone_number := :new.phone_number;
43 l_audit_rows(l_audit_rows.count).hire_date := :new.hire_date;
44 l_audit_rows(l_audit_rows.count).job_id := :new.job_id;
45 l_audit_rows(l_audit_rows.count).salary := :new.salary;
46 l_audit_rows(l_audit_rows.count).manager_id := :new.manager_id;
47 l_audit_rows(l_audit_rows.count).department_id := :new.department_id;
48 l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
49 else
50 l_audit_rows(l_audit_rows.count).employee_id := :old.employee_id;
51 l_audit_rows(l_audit_rows.count).first_name := :old.first_name;
52 l_audit_rows(l_audit_rows.count).last_name := :old.last_name;
53 l_audit_rows(l_audit_rows.count).email := :old.email;
54 l_audit_rows(l_audit_rows.count).phone_number := :old.phone_number;
55 l_audit_rows(l_audit_rows.count).hire_date := :old.hire_date;
56 l_audit_rows(l_audit_rows.count).job_id := :old.job_id;
57 l_audit_rows(l_audit_rows.count).salary := :old.salary;
58 l_audit_rows(l_audit_rows.count).manager_id := :old.manager_id;
59 l_audit_rows(l_audit_rows.count).department_id := :old.department_id;
60 l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
61 end if;
62 end after each row;
63
64 after statement is
65 begin
66 --
67 -- then at completion, do a single insert of all the rows into our audit table
68 --
69
70 forall i in 1 .. l_audit_rows.count
71 insert into AUDIT_EMPLOYEES
72 values l_audit_rows(i);
73 l_audit_rows.delete;
74 end after statement;
75
76 end;
77 /
Trigger created.
Barbara re-examines the performance of the vacation adjustment with her new trigger in place.
SQL> update EMPLOYEES 2 set VACATION_BALANCE = VACATION_BALANCE + 1; 219136 rows updated. Elapsed: 00:00:04.01
Her audit capture now imposes dramatically reduced overhead. A trace of the process confirms the benefit of bulk binding in this instance. Listing 4 shows that the number of calls to INSERT into the AUDIT_EMPLOYEES table has shrunk from more than 200,000 down to the optimal value, 1.
Code Listing 4: TKprof output from trace of vacation adjustment, with new compound trigger
update EMPLOYEES set VACATION_BALANCE = VACATION_BALANCE + 1 call count cpu elapsed disk query current rows ——————— —————— ———————— —————————— —————————— —————————— —————————— —————————— Parse 1 0.01 0.00 0 1 0 0 Execute 1 3.41 3.47 0 2365 224004 219136 Fetch 0 0.00 0.00 0 0 0 0 ——————— —————— ———————— —————————— —————————— —————————— —————————— —————————— total 2 3.43 3.47 0 2366 224004 219136 INSERT INTO AUDIT_EMPLOYEES VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 , :B15 ) call count cpu elapsed disk query current rows ——————— —————— ———————— —————————— —————————— —————————— —————————— —————————— Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.43 0.51 0 3306 21864 219136 Fetch 0 0.00 0.00 0 0 0 0 ——————— —————— ———————— —————————— —————————— —————————— —————————— —————————— total 2 0.43 0.51 0 3306 21864 219136
Barbara’s auditing enhancements to the HR application are back on track, as are her vacation plans. Still, Barbara has a nagging concern about the amount of potential code maintenance overhead she has introduced. In the future, every time a structural change is made to the HR application, the auditing triggers will need to be refactored to accommodate that change. And Barbara’s fix does not address another potential pitfall with the trigger-based solution: although bulk binding improves performance and resolves the context-switching overhead, it also introduces the risk of excessive memory consumption. Because all rows changed by the issuing DML are buffered before being processed at statement completion, a significant amount of session memory can be consumed. This could strain the database server—or, worse, it could crash the application session with “ORA-4030: Out of process memory” errors.
Listing 5 shows the memory consumption Barbara’s bulk binding incurs when the vacation adjustment runs. The session requires more than 160 MB of program global area (PGA) memory. This may be acceptable for a single session running a nightly process such as the vacation update, but it obviously would not scale to a scenario where hundreds or thousands of sessions have a similar requirement.
Code Listing 5: Session memory consumption for the vacation adjustment with bulk binding
--
-- First we check the current ceiling of PGA memory before
-- the vacation adjustment
--
SQL> select st.sid, s.name, st.value
2 from v$statname s, v$sesstat st
3 where st.statistic# = s.statistic#
4 and st.sid = sys_context('USERENV','SID')
5 and s.name = 'session pga memory max';
SID NAME VALUE
—————————— ————————————————————————————————————————————————————— ———————
6 session pga memory max 2508344
1 row selected.
SQL> update EMPLOYEES
2 set VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
--
-- Then we check the same statistics once the adjustment has completed
--
SQL> select st.sid, s.name, st.value
2 from v$statname s, v$sesstat st
3 where st.statistic# = s.statistic#
4 and st.sid = sys_context('USERENV','SID')
5 and s.name = 'session pga memory max';
SID NAME VALUE
—————————— ————————————————————————————————————————————————————— ———————
6 session pga memory max 164430744
To obviate that risk, the trigger can be adjusted to perform bulk binding in batches throughout the DML processing, as shown in Listing 6. But with both code maintenance overhead and complexity increasing, Barbara is keen to find a simpler solution.
Code Listing 6: Trigger to keep PGA consumption in check, still using bulk binding
SQL> create or replace
2 trigger TRG_AUDIT_EMPLOYEES
3 for insert or update or delete
4 on EMPLOYEES compound trigger
5
6 --
7 -- an array structure to buffer all the row changes
8 --
9 type t_row_list is
10 table of AUDIT_EMPLOYEES%rowtype index by pls_integer;
11
12 l_audit_rows t_row_list;
13
14 l_operation varchar2(1) :=
15 case when updating then 'U'
16 when deleting then 'D'
17 else 'I' end;
18
19 procedure insert_logged_so_far is
20 begin
21 forall i in 1 .. l_audit_rows.count
22 insert into AUDIT_EMPLOYEES
23 values l_audit_rows(i);
24 l_audit_rows.delete;
25 end;
26
27 before statement is
28 begin
29 --
30 -- initialize the array
31 --
32 l_audit_rows.delete;
33 end before statement;
34
35 after each row is
36 begin
37 --
38 -- at row level, capture all the changes into the array
39 --
40 l_audit_rows(l_audit_rows.count+1).aud_who := sys_context('USERENV','SESSION_USER');
41 l_audit_rows(l_audit_rows.count).aud_when := sysdate;
42 l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
43 l_audit_rows(l_audit_rows.count).aud_module := sys_context('USERENV','MODULE');
44
45 if updating or inserting then
46 l_audit_rows(l_audit_rows.count).employee_id := :new.employee_id;
47 l_audit_rows(l_audit_rows.count).first_name := :new.first_name;
48 l_audit_rows(l_audit_rows.count).last_name := :new.last_name;
49 l_audit_rows(l_audit_rows.count).email := :new.email;
50 l_audit_rows(l_audit_rows.count).phone_number := :new.phone_number;
51 l_audit_rows(l_audit_rows.count).hire_date := :new.hire_date;
52 l_audit_rows(l_audit_rows.count).job_id := :new.job_id;
53 l_audit_rows(l_audit_rows.count).salary := :new.salary;
54 l_audit_rows(l_audit_rows.count).manager_id := :new.manager_id;
55 l_audit_rows(l_audit_rows.count).department_id := :new.department_id;
56 l_audit_rows(l_audit_rows.count).vacation_balance := :new.vacation_balance;
57 else
58 l_audit_rows(l_audit_rows.count).employee_id := :old.employee_id;
59 l_audit_rows(l_audit_rows.count).first_name := :old.first_name;
60 l_audit_rows(l_audit_rows.count).last_name := :old.last_name;
61 l_audit_rows(l_audit_rows.count).email := :old.email;
62 l_audit_rows(l_audit_rows.count).phone_number := :old.phone_number;
63 l_audit_rows(l_audit_rows.count).hire_date := :old.hire_date;
64 l_audit_rows(l_audit_rows.count).job_id := :old.job_id;
65 l_audit_rows(l_audit_rows.count).salary := :old.salary;
66 l_audit_rows(l_audit_rows.count).manager_id := :old.manager_id;
67 l_audit_rows(l_audit_rows.count).department_id := :old.department_id;
68 l_audit_rows(l_audit_rows.count).vacation_balance := :old.vacation_balance;
69 end if;
70
71 --
72 -- bulk bind every 1000 rows to keep the memory down
73 --
74 if l_audit_rows.count > 1000 then
75 insert_logged_so_far;
76 end if;
77 end after each row;
78
79 after statement is
80 begin
81 --
82 -- then at completion, pick up the remaining rows
83 --
84 if l_audit_rows.count > 0 then
85 insert_logged_so_far;
86 end if;
87 end after statement;
88
89 end;
90 /
Trigger created.
SQL> select st.sid, s.name, st.value
2 from v$statname s, v$sesstat st
3 where st.statistic# = s.statistic#
4 and st.sid = sys_context('USERENV','SID')
5 and s.name = 'session pga memory max';
SID NAME VALUE
—————————— ————————————————————————————————————————————————————— ———————
6 session pga memory max 2508344
1 row selected.
SQL> update EMPLOYEES
2 set VACATION_BALANCE = VACATION_BALANCE + 1;
219136 rows updated.
Elapsed: 00:00:05.31
SQL> select st.sid, s.name, st.value
2 from v$statname s, v$sesstat st
3 where st.statistic# = s.statistic#
4 and st.sid = sys_context('USERENV','SID')
5 and s.name = 'session pga memory max';
SID NAME VALUE
—————————— ————————————————————————————————————————————————————— ———————
6 session pga memory max 2508344
1 row selected.
A Better Way
The use of triggers for capturing row changes is common throughout the Oracle application development community. But few developers display Barbara’s diligence in ensuring that bulk binding caters to multirow DML statements, and even fewer explore potential alternatives.
Barbara peruses the Oracle Database Advanced Application Developer’s Guide and comes across Oracle Flashback technology. The Flashback Query feature catches her eye: “Oracle Flashback Query retrieves data as it existed at some time in the past.” Since any time could be nominated, Barbara infers that it should be doable to see every possible state of a database table, which sounds very much like the auditing requirement she has just implemented. In the same section of the documentation, she also discovers Oracle Flashback’s Oracle Flashback Data Archive feature: “Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime. It is no longer necessary to build this intelligence into your application. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.” This sounds like a perfect match for Barbara’s audit needs, so she builds a Flashback Data Archive test case to examine its features.
First she allocates some storage to hold her archive data:
SQL> create tablespace SPACE_FOR_ARCHIVE 2 datafile 'C:\ORACLE\DB12\ARCH_SPACE.DBF' 3 size 50G; Tablespace created.
Next, she creates a new structure, called a flashback archive, which defines the retention duration of the row change history for any table placed into it.
SQL> CREATE FLASHBACK ARCHIVE longterm 2 TABLESPACE space_for_archive 3 RETENTION 1 YEAR; Flashback archive created
And then she simply associates her HR tables with the flashback archive.
SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM; Table altered.
“So far, so good,” thinks Barbara. However, it is not immediately apparent where the audited row changes are located or how she is meant to access them. Listing 7 shows that some new objects have been created within Barbara’s schema. Their structure suggests they are related to Flashback Data Archive in some way, but she finds it unlikely that she’s supposed to query those tables directly.
Code Listing 7: Tables created to support Flashback Data Archive
SQL> select * from tab; TNAME TABTYPE CLUSTERID ———————————————————————————————————————— ——————— ————————— EMPLOYEES TABLE SYS_FBA_DDL_COLMAP_102596 TABLE SYS_FBA_HIST_102596 TABLE SYS_FBA_TCRV_102596 TABLE SQL> desc SYS_FBA_DDL_COLMAP_102596 Name Null? Type ————————————————————————————— ———————— —————————————————— STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OPERATION VARCHAR2(1) COLUMN_NAME VARCHAR2(255) TYPE VARCHAR2(255) HISTORICAL_COLUMN_NAME VARCHAR2(255) SQL> desc SYS_FBA_HIST_102596 Name Null? Type ————————————————————————————— ———————— —————————————————— RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OPERATION VARCHAR2(1) EMPLOYEE_ID NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME VARCHAR2(25) EMAIL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE DATE JOB_ID VARCHAR2(10) SALARY NUMBER(8,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) VACATION_BALANCE NUMBER(6,2) SQL> desc SYS_FBA_TCRV_102596 Name Null? Type ————————————————————————————— ———————— —————————————————— RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OP VARCHAR2(1)
To determine whether the flashback archive is working as expected, Barbara tries a test query examining the EMPLOYEES table at a past point in time. The execution plan in Listing 8 confirms her hypothesis that the new tables that were created are related to Flashback Data Archive and are accessing historical data.
Code Listing 8: Execution plan for tables associated with Flashback Data Archive
SQL> set autotrace traceonly explain SQL> select * from EMPLOYEES AS OF TIMESTAMP SYSDATE-3; Execution Plan ————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | ————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 9399 | | 1 | VIEW | | 9399 | | 2 | UNION-ALL | | | |* 3 | FILTER | | | | 4 | PARTITION RANGE SINGLE| | 1054 | |* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_102596 | 1054 | |* 6 | FILTER | | | | 7 | MERGE JOIN OUTER | | 8345 | | 8 | SORT JOIN | | 203 | |* 9 | TABLE ACCESS FULL | EMPLOYEES | 203 | |* 10 | SORT JOIN | | 230K| |* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_102596 | 230K| —————————————————————————————————————————————————————————————————
Seeing a point-in-time state of the EMPLOYEES table is useful, but Barbara needs more than that. She needs to be able to see every point-in-time state of a row in the table to build up a history of changes and satisfy her audit requirements. With each update to the table, a new “version” of a row comes into existence, and Barbara needs to list every one of those versions. Having reviewed Oracle Flashback technology features in the documentation, Barbara knows that she can use the Flashback Version Query feature of Oracle Flashback to achieve this. Listing 9 shows how the VERSIONS BETWEEN syntax lists all the historical versions of a single employee record (EMPLOYEE_ID = 100) and how the VERSIONS_STARTTIME and VERSIONS_OPERATION pseudocolumns can be used to track the transaction time and operation performed on each row.
Code Listing 9: Flashback Version Query
SQL> select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
2 VERSIONS_STARTTIME TS,
3 nvl(VERSIONS_OPERATION,'I') OP
4 from EMPLOYEES
5 versions between timestamp timestamp '2016-01-11 08:20:00' and systimestamp
6 where EMPLOYEE_ID = 100
7 order by EMPLOYEE_ID, ts;
EMPLOYEE_ID FIRST_NAME JOB_ID VACATION_BALANCE TS O
——————————— —————————————————— —————————— ———————————————— ——————————————————————————————————— —
100 Steven AD_PRES 45.76 11-JAN-16 08.27.55.000000000 AM U
100 Steven AD_PRES 46.76 12-JAN-16 08.28.07.000000000 AM U
100 Steven AD_PRES 47.76 13-JAN-16 08.53.56.000000000 AM U
100 Steven AD_PRES 48.76 14-JAN-16 08.53.56.000000000 AM U
100 Steven AD_PRES 49.76 15-JAN-16 08.53.56.000000000 AM U
Barbara is excited about her progress. She can now list a history of row changes for the EMPLOYEES table, and no triggers are required. The vacation update routine will run at the same swift speed as it did before her audit changes were implemented.
But there is still an outstanding issue. Barbara’s original trigger-based audit implementation picked up additional metadata—namely, who did the change and what program was being executed. That data is not present in the Flashback Data Archive tables. Barbara doesn’t want to add those metadata columns to each of the base tables in her application, because that would break a primary implementation requirement for auditing—that it should be transparent to the existing application.
Luckily for Barbara, her application is deployed on Oracle Database 12c. Flashback Data Archive has been extended to enable the capture of context-level information automatically and without changes to the base tables. Barbara contacts her administrator and requests context-level tracking to be enabled for Flashback Data Archive:
SQL> exec dbms_flashback_archive.set_context_level('ALL');
PL/SQL procedure successfully completed.
Now, when rows are inserted, updated, or deleted in the tables Barbara has associated with a flashback archive, any context information in the standard USERENV context (as well as any custom user-defined contexts) will be captured for each transaction. From her trigger-based audit implementation, Barbara knows that she needs the MODULE and SESSION_USER attributes from the USERENV context. She performs some sample DMLs against the EMPLOYEES table and then checks to ensure that the context-level metadata is being collected. Listing 10 shows that the capture of context information for each transaction is working and Barbara’s implementation of the audit requirement with Flashback Data Archive is complete.
Code Listing 10: Flashback Version Query with context information
SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
2 VERSIONS_STARTTIME TS,
3 dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
4 dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
5 from EMPLOYEES
6 versions between timestamp timestamp '2016-01-11 08:20:00' and systimestamp
7 where EMPLOYEE_ID = 100
8 order by EMPLOYEE_ID, ts;
EMPLOYEE_ID FIRST_NAME VACATION_BALANCE TS WHO PROGRAM
——————————— ———————————— ———————————————— —————————————————————— ———————————— ————————
100 Steven 52.76 11-JAN-16 10.04.03 BARBARA SQL*Plus
100 Steven 51.76 11-JAN-16 11.14.03 BARBARA SQL*Plus
100 Steven 53.76 11-JAN-16 12.05.06 BARBARA SQL*Plus
100 Steven 54.76 11-JAN-16 13.01.06 BARBARA SQL*Plus
100 Steven 55.76 11-JAN-16 14.23.09 BARBARA SQL*Plus
Although Barbara initially implemented the auditing requirements with a trigger-based approach, she does not need to abandon the audited row changes she has already collected. Using the DBMS_FLASHBACK_ARCHIVE package, she can import her captured audit changes into the new Flashback Data Archive tables and then drop her old auditing tables and triggers.
Conclusion
The use of triggers for auditing is common and is still often regarded as the only possible solution for capturing row changes on Oracle Database tables. However, in Oracle Database 11.2.0.4 and above, auditing requirements can now be met with Flashback Data Archive—a simple, more efficient, and more secure solution.