Data Versioning
New to the Oracle Database 10g are expanded Flashback capabilities. See Chapter 10 of the Oracle High Availability Architectures and Best Practices product documentation for details. The Flashback Versioning feature allows the user to view data as it has evolved over time. Depending on your auditing requirements, this may be precisely what you need.
Flashback Version Query
To illustrate this capability, refresh the data table and issue a 10 percent raise for everyone in the table:
scott@KNOX10g> -- Refresh data
scott@KNOX10g> TRUNCATE TABLE emp_copy;
Table truncated.
scott@KNOX10g> INSERT INTO emp_copy
2 SELECT * FROM emp;
14 rows created.
scott@KNOX10g> COMMIT ;
Commit complete.
scott@KNOX10g> -- Give everyone a 10% raise
scott@KNOX10g> UPDATE emp_copy
2 SET sal = sal * 1.1;
14 rows updated.
scott@KNOX10g> COMMIT ;
Behind the scenes, it appears the database has logged the updates. To access the data, you can use the following:
scott@KNOX10g> -- Show database record of values
scott@KNOX10g> COL ename format a6
scott@KNOX10g> COL sal format a8
scott@KNOX10g> COL "Start" format a12
scott@KNOX10g> COL "End" format a12
scott@KNOX10g> COL "XID" format a17
scott@KNOX10g> COL operation format a9
scott@KNOX10g> SELECT ename,
2 TO_CHAR (sal) sal,
3 DECODE (versions_operation,
4 'I', 'Insert',
5 'U', 'Update',
6 'D', 'Delete') operation,
7 versions_xid "XID",
8 TO_CHAR (versions_starttime,
9 'MM/DD HH24:MI') "Start",
10 TO_CHAR (versions_endtime,
11 'MM/DD HH24:MI') "End"
12 FROM emp_copy
13 VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
14 WHERE deptno = 20
15 ORDER BY 1, 2;
ENAME SAL OPERATION XID Start End
------ -------- --------- ----------------- ------------ ------------
ADAMS 1100 04/20 16:16
ADAMS 1210 Update 04000F00390B0000 04/20 16:16
FORD 3000 04/20 16:16
FORD 3300 Update 04000F00390B0000 04/20 16:16
JONES 2975 04/20 16:16
JONES 3272.5 Update 04000F00390B0000 04/20 16:16
SCOTT 3000 04/20 16:16
SCOTT 3300 Update 04000F00390B0000 04/20 16:16
SMITH 800 04/20 16:16
SMITH 880 Update 04000F00390B0000 04/20 16:16
10 rows selected.
The pseudocolumns and syntax that asks the database for the versioned data are in bold. This shows the old data, the new data, the type or operation, when the operation occurred, and that the update was part of the same transaction. Alternatively, the start and end values can be based on SCN.
Flashback Transaction Query
You can now use another feature called Flashback Transaction Query. The transaction ID returned in the preceding query can be used to get additional information stored in the FLASHBACK_TRANSACTION_QUERY view:
system@KNOX10g> COL table_owner format a11
system@KNOX10g> COL table_name format a10
system@KNOX10g> COL operation format a10
system@KNOX10g> COL logon_user format a10
system@KNOX10g> SELECT DISTINCT table_owner,
2 table_name,
3 operation,
4 logon_user
5 FROM flashback_transaction_query
6 WHERE xid =
7 HEXTORAW ('04000C00340B0000')
8 AND table_name IS NOT NULL;
TABLE_OWNER TABLE_NAME OPERATION LOGON_USER
----------- ---------- ---------- ----------
SCOTT EMP_COPY UPDATE SCOTT
The Flashback capabilities, which allow you to restore data very efficiently, are discussed in the Oracle High Availability document. The view stores the SQL statements needed to recover the data to its original state, as you can see:
system@KNOX10g> SELECT undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW ('0400150012010000')
4 AND table_name IS NOT NULL
5 AND ROWNUM <= 1;
UNDO_SQL
---------------------------------------------------------------
update "SCOTT"."EMP_COPY" set "SAL" = '1300' where ROWID =
AAAMa1AAEAAACQUAAN';
If you wanted to recover the original salary values, you could run the following:
system@KNOX10g> -- current data
system@KNOX10g> SELECT ename, sal FROM scott.emp_copy
2 WHERE deptno = 20;
ENAME SAL
---------- ----------
SMITH 880
JONES 3272.5
SCOTT 3300
ADAMS 1210
FORD 3300
system@KNOX10g> -- recover data
system@KNOX10g> DECLARE
2 l_undo_sql VARCHAR2 (32767);
3 BEGIN
4 FOR rec IN
5 (SELECT undo_sql
6 FROM flashback_transaction_query
7 WHERE xid = HEXTORAW ('0400150012010000')
8 AND table_name IS NOT NULL)
9 LOOP
10 l_undo_sql := REPLACE (rec.undo_sql, ';', '');
11 EXECUTE IMMEDIATE l_undo_sql;
12 END LOOP;
13 14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
system@KNOX10g> SELECT ename, sal FROM scott.emp_copy
2 WHERE deptno = 20;
ENAME SAL
---------- ----------
SMITH 800
JONES 2975
SCOTT 3000
ADAMS 1100
FORD 3000
The ability to recover the data isn’t limited to the transactions. The database supports additional flashback modes: Flashback Table, which allows you to recover an entire table to a point in time; Flashback Drop, which allows you to restore tables that were dropped; and Flashback Database, which recovers the entire database to a point in time. All of these may prove beneficial if a hacker attacks your database and starts corrupting or destroying data.
All of the flashback technology, except Flashback Database, is built on Oracle’s multiversion read consistency implementation. This means there is no auditing performance penalty. Oracle is always “logging” the changes. The flashback operations are a new way of exploiting an implementation design that has been available for several years.
| Note |
|
No comments:
Post a Comment