Flashback Query

---

If you recognize Flashback Query from Oracle9i, you're right: there was some Flashback Query functionality that existed in Oracle 9i. In Oracle Database 10g, that functionality has been expanded and simplified to allow you better access. Read: no more PL/SQL interface. Now, it's all built in into SQL so you don't have to program a PL/SQL block to look at historical versions of a row.

Flashback and the Undo Segment: A Love Story

The first two types of flashback-Flashback Query and Flashback Table-have their functionality based entirely on technology that has existed in the Oracle database for years: the undo segments (or the segments formerly known as rollback). Undo segments exist in order to undo transactions that have not been committed. In the past, a committed transaction could not be undone because the associated before image of the row in the rollback was freed up to be overwritten.

This is still true: when you commit a transaction, the extent in the undo segment that contains the before image of the row is freed up to be overwritten. However, changes in the way undo space was used in Oracle 9i mean that all new transactions look for unused space in the undo tablespace before overwriting previously used segments. Even then, it always goes to the oldest remaining extents first. This means that before images of rows in the database last far longer than they ever have in the past.

This is all very good news, and in Oracle 9i and later, Oracle put it to use with the Flashback Query. Now the DBA can actually control how long he or she wants the undo extents to remain before they are overwritten. After doing so, we can put undo to good use-to help us undo committed transactions that were mistakes.

The ability to query or change objects back to a certain time in the past is predicated on how long our undo extents can remain in the undo tablespace before they are overwritten. Undo extents are used by new transactions based on space pressure in the undo tablespace. Basically, Oracle will not overwrite undo extents until it has exhausted all other possibilities first-that is, until every extent in the undo tablespace has been utilized. Then, it finds the oldest extent and overwrites it. The threshold for how far back you can use a Flashback Query or Flashback Table is set by how long Oracle can go from the time a transaction is committed until the time that undo extents for that transaction get overwritten. The period from committed transaction to undo extent being overwritten is the flashback window.

There are plenty of factors that go into determining the flashback window, but the most important is your transaction load. You can view statistics for undo usage with the view V$UNDOSTAT. Each row in this view represents the number of undo blocks utilized for a ten-minute period. Running a few analyses of this view through peak usage should provide a decent template by which to guide your settings for undo.

Setting Undo Parameters for Flashback Query and Flashback Table

The guidelines for using Flashback Query demand that you first have automatic undo enabled-no rollback segments are allowed. (Okay, that's a lie. It is feasible to use flashback operations with old-school rollback segments, but Oracle discourages it and so do we. There is no reason to try and set up rollback segments manually anymore.) Oracle is best left to control undo management using new algorithms that emphasize retention of transactional history-algorithms that do not exist in rollback segments. Therefore, you need to set UNDO_MANAGEMENT = AUTO in the pfile or spfile. Second, set your UNDO_TABLESPACE parameter to point to which tablespace will handle undo duties. Finally, you set UNDO_RETENTION = value in seconds. This sets the desired length of time to keep undo segments around.

Performing Flashback Query

Performing a Flashback Query of a table is simple, now that it has been integrated into SQL. All you need to know is the time in the past when you would like to view the contents of a table, and then you plug it into your query:

select scr_id, head_config from ws_app.woodscrew as of timestamp   to_timestamp('2003-12-01 09:40:00','YYYY-MM-DD HH:MI:SS')   where scr_id=1001;     SCR_ID HEAD_CONFIG --------- --------------------      1001 Phillips       1001 Phillips 

You can also use an SCN qualifier, if you know the System Change Number (SCN) of the change you are looking for:

select scr_id, head_config from ws_app.woodscrew  as of scn 751652 where scr_id=1001;     SCR_ID HEAD_CONFIG --------- --------------------      1001 Slot       1001 Slot   

Flashback Versions Query with Enterprise Manager

Implementing Flashback Query-and its relatives, Flashback Transaction Query and Flashback Versions Query-is far simpler when you use Enterprise Manager (EM). EM allows you to quickly turn a Flashback Query into an operation that can undo a user-induced error, whether through a Flashback Table or through applying the undo SQL for the bad transaction.

Enterprise Manager combines the best features of multiple technologies to provide a user interface that helps you get answers quickly. Underneath the covers, it uses transaction queries to build a more complete investigation into what logical errors have occurred. The first of these is flashback version query, which also is referred to as row history. Version query provides the ability to look at every version of a row that existed within a specified time frame. So, you provide a query to look at a row, and a time frame that you want to review, and Oracle returns a list of every iteration that row has been through. This allows you to see a row morph over time in order to determine what may be at the root of the problem.

HA Workshop: Exploring Flashback Versions Query

Workshop Notes

This workshop requires a few tables be built, and populated with a few dummy rows, so that you can watch Flashback Versions Query in action. Here is the DDL and DML for the Woodscrew table and indices. We also build a secondary table with rows for future use in Flashback Drop and Flashback Database. You are obviously not compelled to use our simplistic little test here, and could easily test with existing dummy tables in your system.

 create table woodscrew (  scr_id           number not null,  manufactr_id     varchar2(20) not null,  scr_type         varchar2(20),  thread_cnt       number,  length           number,  head_config      varchar2(20)); alter table woodscrew add primary key  (scr_id, manufactr_id) using index; create index woodscrew_identity on woodscrew (scr_type, thread_cnt, length, head_config); create table woodscrew_inventory (  scr_id           number not null,  manufactr_id     varchar2(20) not null,  warehouse_id     number not null,  locale           varchar2(20),  count            number,  lot_price        number); insert into woodscrew values ( 1000, 'Tommy Hardware', 'Finish', 30, 1.5, 'Phillips'); insert into woodscrew values ( 1000, 'Balaji Parts, Inc.', 'Finish', 30, 1.5, 'Phillips'); insert into woodscrew values ( 1001, 'Tommy Hardware', 'Finish', 30, 1, 'Phillips'); insert into woodscrew values ( 1001, 'Balaji Parts, Inc.', 'Finish', 30, 1, 'Phillips'); insert into woodscrew values ( 1002, 'Tommy Hardware', 'Finish', 20, 1.5, 'Phillips'); insert into woodscrew values ( 1002, 'Balaji Parts, Inc.', 'Finish', 20, 1.5, 'Phillips'); insert into woodscrew values ( 1003, 'Tommy Hardware', 'Finish', 20, 1, 'Phillips'); insert into woodscrew values ( 1003, 'Balaji Parts, Inc.', 'Finish', 20, 1, 'Phillips'); insert into woodscrew_inventory values ( 1000, 'Tommy Hardware', 200, 'NORTHEAST', 3000000, .01); insert into woodscrew_inventory values ( 1000, 'Tommy Hardware', 350, 'SOUTHWEST', 1000000, .01); insert into woodscrew_inventory values ( 1000, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1500000, .015); insert into woodscrew_inventory values ( 1005, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1700000, .017); commit;

Step 1.  Open up the Oracle Database 10g Enterprise Manager console and click on Administration | Schema | Tables. This opens up a view of all tables in the schema of the user you have logged in as. You can change this to the owner of the Woodscrew table by changing the Schema to the user and then clicking on Go. Then you can select the Woodscrew table and, from the drop-down list from Actions, choose View Data. Note in Figure 9-1 that the value for column HEAD_CONFIG is 'Phillips' for all rows.

Figure 9-1: Navigating to the table and viewing contents

Step 2.  Change rows in the table to reflect a different head configuration for the woodscrews.

update woodscrew set head_config= 'Slot'  where scr_id=1001;  commit;   

Step 3.  View the new data in the table. From Tables, select the Woodscrew table, choose View Data from the Actions drop-down list, and then click Go. Note in Figure 9-2 that two rows now have 'Slot' instead of 'Phillips.'

Figure 9-2: Slot-headed screws

Step 4.  It has been determined that the screws with scr_id 1001 are not slotheaded, but rather Phillips. There has been a logical corruption introduced into the database. Let's review a single row and see what versions the row has been through. From the Tables view, select the Woodscrew table; then from the Actions list, choose Flashback by Row Versions and then click Go.

Step 5.  From here, we need to provide the parameters of our Flashback Query. First, choose all columns by selecting Move All under Step 1. Under Step 2, we need to specify a clause that isolates a single row. We will use the following WHERE clause:

where scr_id=1001 and manufactr_id='Tommy Hardware' 

We then choose the time frame for our row history exploration. You can choose to Show All Row History or specify a specific time frame. All Row History shows all versions of the row that still exist in the undo tablespace. In Figure 9-3, we see the results of our query, with the transaction that updated the two rows.

Figure 9-3: All versions of the row

Step 6.  From this view, we can continue with the wizard and perform a flashback of the table. But we now know all the different operations that have occurred against this row in the database.

Step 7.  If you want proof that Enterprise Manager is working hard for you, click back to the Recovery: Row History Filter page. At the bottom is a button for Show Flashback Versions Query SQL, as shown in Figure 9-4. Click on this button and take a look at the SQL you are blissfully ignoring.

Figure 9-4: Flashback Versions Query SQL

Flashback Transaction Query

There's always more than one way to skin a cat, and there's more than one way to organize a manhunt for bad data in the database. Flashback Transaction Query allows you to look at all changes made by a specific transaction, or all transactions in a certain time frame. Then you can go in and undo just a subset of the transaction, instead of the entire transaction being undone and then redone.

Again, the best way to use Transaction Query is through Enterprise Manager. Save yourself the grief and log into the EM Console for the database and use the same set of operations described in the HA Workshop 'Exploring Flashback Versions Query.'

Flashback Transaction Query is compelling because it allows the HADBA to review a bad transaction in its entirety, even though the window into the error may be only a single row. For instance, if we found a row of our Woodscrew table had been deleted, we could look up that row in Flashback Versions Query. Then, we can get the Transaction ID for the DELETE operation and see how many other rows were deleted at the same time. This provides a means by which to get a look at the full scope of the problem.

But we can also take it a step further, as Oracle has built into Enterprise Manager the ability to review the UNDO SQL statement for each row affected in the transaction. So, for the delete against Woodscrew, each deleted row would be displayed with an INSERT statement that would replace the missing row in the table. In this fashion, you can correct an error without necessarily undoing the entire transaction- perhaps you meant to delete one row, but your SQL was incorrect and you deleted two rows. With EM, you can undo one delete and leave the other in place. Let's take a look.

HA Workshop: Explore Flashback Transaction Query

Workshop Notes

This workshop is an extension of the workshop 'Exploring Flashback Versions Query' in the previous section. The same Woodscrew table is used.

Step 1.  Introduce a fault into the Woodscrew table.

SQL> delete from woodscrew where scr_id='1001'; commit;

Step 2.  Someone querying for a one-inch finish woodscrew manufactured by Tommy notices that the screw has been deleted from the database, even though these are still offered by the company to customers. What happened? We turn to the EM Administration | Schema | Tables page. Choose the owner of the Woodscrew table and click on Go.

Step 3.  Select the Woodscrew table; then from the Actions list, choose Flashback Transaction History and click Go.

Step 4.  Choose all columns, and then put a WHERE clause in to set the boundary of our inquiry. We will use the WHERE clause of the user who encountered the fault:

where scr_id=1001 and manufactr_id='Tommy Hardware'

Then choose a time frame (we use All Row History for this example) and click Next.

Step 5.  You are looking at a row history report, as you would for Flashback Row History. To review the transaction, click on the transaction ID for the DELETE operation. You are now looking at the entire transaction that included the delete of our specified row, as shown in Figure 9-5. What is useful is that we can note here any other rows that were also impacted by this transaction, to determine if we need to fix just the one row or multiple rows.

Figure 9-5: Transaction information

Step 6.  You will also note in Figure 9-5 that the undo SQL is provided for each row change that occurred as part of the transaction. This allows us to undo part of the transaction instead of the entire transaction, if desired. We can simply cut and paste the undo SQL into SQL*plus to undo the corruption.

SQL> insert into "WS_APP"."WOODSCREW" ("SCR_ID","MANUFACTR_ID","SCR_TYPE", "THREAD_CNT","LENGTH","HEAD_CONFIG") values ('1001','Tommy Hardware','Finish','30','1','Slot');

---