8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Flashback and LogMiner Enhancements in Oracle Database 11g Release 1

The Flashback Query feature was first introduced in Oracle 9i. Oracle 10g improved Flashback Query and introduced several new features. The improvements to Flashback technology have continued in Oracle 11g, which has also improved the usability of Logminer, another long standing feature.

Related articles.

LogMiner Enhancements

In previous versions of Oracle the LogMiner viewer was a separate Java based console, but in Oracle 11g it has been incorporated into Enterprise Manager and integrated with the new Flashback Transaction feature, making it simple to recover transactions that have had an undesirable affect. The logminer functionality is accessed using the "View and Manage Transactions" link on the "Availability" tab.

View And Manage Transactions Link

The section on Flashback Transaction will make use of this feature, so there is little point discussing more here.

Flashback Transaction

As the name implies, Flashback Transaction allows the changes made by a transaction to be undone, optionally including changes made by dependent transactions. The best way to explain this is to see it in action. For this we will be using the new LogMiner screens in Enterprise Manager.

LogMiner requires minimal supplemental logging, but Flashback Transaction requires primary key logging. These can be turned on for the database using the following commands.

CONN sys/password AS SYSDBA

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

In order to test the LogMiner and Flashback Transaction functionality we need some transactions to view. First, create a test user.

CONN sys/password AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user
  QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO test_user;

Next, connect to the test user, create a test table and perform a few transactions against it.

CONN test_user/test_user

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT INTO test_tab (id, description) VALUES (1, 'Description for 1');
COMMIT;

INSERT INTO test_tab (id, description) VALUES (2, 'Description for 2');
COMMIT;

UPDATE test_tab SET description = 'Tree' WHERE  id = 1;
COMMIT;

UPDATE test_tab SET description = 'Field' WHERE  id = 2;
COMMIT;

DELETE FROM test_tab WHERE id = 1;
COMMIT;

Log into Enterprise Manager and click the "View and Manage Transactions" link on the "Availability" tab.

View And Manage Transactions Link

Enter a suitable date range that is likely to pick up the transactions we've just performed and enter the table name of "TEST_USER.TEST_TAB". You can customize the query further by editing the WHERE clause in the "Advanced Query" section. Clicking the "Info Icon" on the page displays a description of the V$LOGMNR_CONTENTS, including its columns and some useful functions for querying them. For this example, we are just going to ignore this section and click the "Continue" button.

LogMiner Query Options

Wait while the contents of the online and archived redo logs are searched for transactions matching the search criteria. The resulting page shows the transactions that match our search criteria. Click on the transaction ID associated with the first insert operation, in this case it is listed as "TEST_USER.TEST_TAB (1 ins)".

LogMiner Results

The "Transaction Details" page shows the full insert statement. Click the "Flashback Transaction" button.

Transaction Details

Confirm the flashback request by clicking the "Yes" button.

Confirmation

The insert operation we originally selected had two dependent transactions, the update and delete, so we need to decide how this flashback should deal with the dependents. The text currently states that the "Nonconflict only" option is set. We want to change that, so click the "Change Recovery Option" button.

Show Dependentcies

Select the "Cascade" option and click the "OK" button.

Change Recovery Option

The "Show Dependencies" screen now states the "Cascade" option is set. Click the "Next" button.

Show Dependencies

The "Review" screen allows us to take a look at the state of the database before commiting the operation. To check the current state of the data, enter a query in the "Execute SQL" section and click the "Execute SQL" button. To view the undo SQL script, click the "Show Undo SQL Script" button. Click the "Finish" button to commit the flashback operation.

Review

Click the "OK" button on the subsequent result screen.

Result

You are then returned to the "Transaction Details" screen. Clicking the "OK" button returns you to the "LogMiner Results" screen. Clicking the "Done" button will return you to the top level "Availability" tab screen.

The flashback transaction functionality is also available using the TRANSACTION_BACKOUT procedure of the DBMS_FLASHBACK package. The procedure accepts multiple transaction IDs or names, along with an OPTIONS parameter to indicate how dependent transactions should be treated.

Transactions are always processed in reverse order of their commit times.

In addition to backing out the required transactions, the procedure generates a report of the actions taken, which can be viewed using the DBA_FLASHBACK_TXN_STATE and DBA_FLASHBACK_TXN_REPORT views. Based on the information present in these views, the user can decide whether to commit or rollback the flashback operation.

The previous "LogMiner Results" screen showed the second insert transaction had an ID of "060015009D030000". We can use this with the TRANSACTION_BACKOUT procedure to flashback this transaction also.

BEGIN
  DBMS_FLASHBACK.transaction_backout (numtxns => 1,
                                      xids    => xid_array('060015009D030000'),
                                      options => DBMS_FLASHBACK.cascade);
END;
/

Querying the test table shows that the row has been removed.

SQL> SELECT * FROM test_user.test_tab;

no rows selected

SQL>

We can use the transaction ID to query the DBA_FLASHBACK_TXN_STATE view.

SELECT *
FROM   dba_flashback_txn_state
WHERE  xid = '060015009D030000';

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME
---------------- ---------------- ---------------- ---------------- ------------------------------
05001800A0030000 060015009D030000 02000B00DB030000 CASCADE          SYS

1 row selected.

SQL>

The COMPENSATING_XID returned from this query is used to query the DBA_FLASHBACK_TXN_REPORT view.

COLUMN xid_report FORMAT A80
SET LONG 100000

SELECT xid_report
FROM   dba_flashback_txn_report
WHERE  compensating_xid = '05001800A0030000';

XID_REPORT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<COMP_XID_REPORT XID="05001800A0030000">
        <TRANSACTION XID="060015009D030000">
        <CHARACTERISTICS>
        </CHARACTERISTICS>
        <UNDO_SQL>
                <USQL exec="yes">
                 delete from "TEST_USER"."TEST_TAB" where "ID" = '2' and "DESCRIPTION" = 'Desc
ription for 2'
                </USQL>
        </UNDO_SQL>

XID_REPORT
--------------------------------------------------------------------------------
        <DEPENDENT_XIDS>
                <TRANSACTION XID="02000B00DB030000">
                <CHARACTERISTICS>
                </CHARACTERISTICS>
                <UNDO_SQL>
                        <USQL exec="yes">
                         update "TEST_USER"."TEST_TAB" set "DESCRIPTION" = 'Description for 2' where
"ID" = '2' and "DESCRIPTION" = 'Field'
                        </USQL>
                </UNDO_SQL>
                <DEPENDENT_XIDS>

XID_REPORT
--------------------------------------------------------------------------------
                </DEPENDENT_XIDS>
                </TRANSACTION>
        </DEPENDENT_XIDS>
        </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>update "TEST_USER"."TEST_TAB" set "DESCRIPTION" = 'Description for 2'
 where "ID" = '2' and "DESCRIPTION" = 'Field'
</EXEC_USQL>
<EXEC_USQL>delete from "TEST_USER"."TEST_TAB" where "ID" = '2' and "DESCRIPTION"
 = 'Description for 2'
</EXEC_USQL>

XID_REPORT
--------------------------------------------------------------------------------
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>


1 row selected.

SQL>

Based on this information, we can decide if the flashback operation should be commited or rolled back.

Flashback Data Archive (Oracle Total Recall)

Most flashback features work at the logical level, in that they don't directly allow you to recover an object. They simply give you a view of how the data was in the past, which in turn can be used to recover from logical corruption, such as accidental deletions. Typically, this view of the past is constructed using undo segments, which are retained for a period of time indicated by the UNDO_RETENTION parameter. Once the undo information is lost, the view of the past is lost also. At least that's how it was before Oracle 11g introduced the Flashback Data Archive functionality.

A flashback data archive is essentially an extended store of undo information, allowing some logical flashback operations to extend way back into the past with no difference in how they are coded. An individual flashback archives consists of one or more tablespaces, or parts of tablespace. Each flashback archive has a name, retention period and a quota on each associated tablespace. The database can have multiple flashback data archives, but only a single default archive. When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

The following script creates a new tablespace, then creates two flashback data archives using the CREATE FLASHBACK ARCHIVE command. The first is limited in size to 10Gig with a retention period of 1 year, while the second has an unlimited quota and a retention period of 2 years.

CREATE TABLESPACE fda_ts
  DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

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

CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts
  RETENTION 2 YEAR;

Management of flashback archives falls into three distinct categories.

Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.

DROP FLASHBACK ARCHIVE fba_name;

To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used. To try this out create a test user and grant the object privilege on the first flashback archive created earlier.

CONN sys/password AS SYSDBA

CREATE USER fda_test_user IDENTIFIED BY fda_test_user
  QUOTA UNLIMITED ON users
  QUOTA UNLIMITED ON fda_ts;

GRANT CONNECT, CREATE TABLE TO fda_test_user;
GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;

If we connect to the test user we are able to create a table and associate it with the default flashback archive as follows.

CONN fda_test_user/fda_test_user

CREATE TABLE test_tab_1 (
  id          NUMBER,
  desription  VARCHAR2(50),
  CONSTRAINT test_tab_1_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE;

If we try to create a similar table, but point it at the second archive it fails, as we have no privileges on it.

CONN fda_test_user/fda_test_user

CREATE TABLE test_tab_2 (
  id          NUMBER,
  desription  VARCHAR2(50),
  CONSTRAINT test_tab_2_pk PRIMARY KEY (id)
)
FLASHBACK ARCHIVE fda_2year;
CREATE TABLE test_tab_2 (
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL>

The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.

-- Enable using the default FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE;

-- Enable using specific FBDA.
ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;

-- Disable flashback archiving.
ALTER TABLE table_name NO FLASHBACK ARCHIVE;

As with the CREATE TABLE statement, the FLASHBACK ARCHIVE object privilege must be granted on the flashback archive being used.

Once flashback archiving is enabled the table is protected, so it cannot be switched off unless you have the FLASHBACK ARCHIVE ADMINISTER system privilege, or are logged on as SYSDBA.

SQL> ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE;
ALTER TABLE test_tab_1 NO FLASHBACK ARCHIVE
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive


SQL>

In addition, there are certain DDL restrictions associated with having flashback archiving enabled in 11gR1. The following operations result in a ORA-55610 error.

In 11gR2 some of these restrictions have been removed and the following DDL is supported.

Information about flashback data archives is displayed using the %_FLASHBACK_ARCHIVE view.

CONN sys/password AS SYSDBA

COLUMN flashback_archive_name FORMAT A20

SELECT flashback_archive_name, retention_in_days, status
FROM   dba_flashback_archive;

FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYS STATUS
-------------------- ----------------- -------
FDA_2YEAR                          730
FDA_1YEAR                          365 DEFAULT

2 rows selected.

SQL>

The %_FLASHBACK_ARCHIVE_TS view displays the tablespaces and quotas associated with each flashback archive.

COLUMN flashback_archive_name FORMAT A20
COLUMN quota_in_mb FORMAT A10

SELECT flashback_archive_name, tablespace_name, quota_in_mb
FROM   dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA TABLESPACE_NAME                QUOTA_IN_M
-------------------- ------------------------------ ----------
FDA_2YEAR            FDA_TS
FDA_1YEAR            FDA_TS                         10240

2 rows selected.

SQL>

The %_FLASHBACK_ARCHIVE_TABLES view displays tables associated with each flashback archive, along with the name of the table holding the historical information.

COLUMN table_name FORMAT A15
COLUMN owner_name FORMAT A15
COLUMN flashback_archive_name FORMAT A20
COLUMN archive_table_name FORMAT A20

SELECT table_name, owner_name, flashback_archive_name, archive_table_name
FROM   dba_flashback_archive_tables;

TABLE_NAME      OWNER_NAME      FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME
--------------- --------------- -------------------- --------------------
TEST_TAB_1      FDA_TEST_USER   FDA_1YEAR            SYS_FBA_HIST_72023

1 row selected.

SQL>

Oracle database version 11.2 also includes the DBMS_FLASHBACK_ARCHIVE package, which allows you to disassociate (DISASSOCIATE_FBA) and reassociate (REASSOCIATE_FBA) a table and it's flashback data archive table. Allowing you to perform untracked DML and DDL to the base table. Any DDL applied to the base table must also be applied to the flashback data archive table or it will fail to reassociate.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.