Transaction Management with LogMiner and Flashback Data Archive

LogMiner is an often ignored yet very powerful tool in the Oracle Database. It is used to extract DML statements from the redo log files—the original SQL that caused the transaction and even the SQL that can undo the transactions. In Oracle Database 11g, however, Oracle Enterprise Manager has a graphical interface to extract transaction from the redo logs using LogMiner, which makes it extremely easy to use the tool to examine and rollback transactions. (Note: As in previous versions, you can continue to use the DBMS_LOGMNR package to perform command line-driven log mining if you wish.)

Let's see an example how this is done. To enable log mining, Oracle DBA need only minor supplemental logging enabled for the database or at least the table. Flashback Transaction requires primary key logging. To enable it for the entire database, issue the following commands:

SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered.Now, consider the following statements issued by an application against your database:
SQL> insert into res values (100002,sysdate,12,1);

1 row created.

SQL> commit;

Commit complete.
SQL> update res set hotel_id = 13 where res_id = 100002;
1 row updated.

SQL> commit;

Commit complete.
SQL> delete res where res_id = 100002;

1 row deleted.

SQL> commit;

Commit complete.

Read on full article at http://Oracleonline.info/