Oracle RMAN 11g Backup and Recovery (130 page)

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 point in time in the past for which 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('2009-06-27 04:27: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 Oracle Enterprise Manager

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

OEM 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 Versions Query, which also is referred to as row history. Flashback Versions Query provides the ability to look at every version of a row that existed within a specified timeframe. So, you provide a query to look at a row, and a timeframe 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, to determine what may be at the root of the problem.

Chapter 15: Surviving User Errors: Flashback Technologies
381

RMAN Workshop:
Explore Flashback Versions Query

Workshop Notes

This workshop has you build a few tables and populate them with a few dummy rows so that you can watch Flashback Versions Query in action. The following is the DDL and DML for the WOODSCREW table and indices. This code also builds 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');

382
Part III: Using RMAN Effectively

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 the OEM console’s database home page and go to Schema | Tables. This opens 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 Go. Then, you can select the WOODSCREW table, and choose View Data from the Actions drop-down list. After you click View Data, OEM will display the view shown in the following illustration. Note that the value for column HEAD_CONFIG is Phillips for all rows.

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 the following illustration that two rows now have Slot instead of Phillips.

Chapter 15: Surviving User Errors: Flashback Technologies
383

Step 4.
Within the woodscrew business organization, it was determined that the screws with scr_id 1001 are
not
slot-headed, 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, choose Flashback Versions Query from the Actions list, and then click Go. This takes you to the Perform Object Level Recovery Wizard.

Step 5.
In the wizard, we need to provide the parameters of our flashback query. First, choose all columns by selecting Move All under Step 1 of the wizard. Click the Next button from the right side of the page. Under Step 2, specify a clause that isolates a single row. We will use the following WHERE clause:

where scr id 1001 and manufactr id 'Tommy Hardware'

After specifying the clause and clicking the Next button, we see the different versions of the row, along with the option to select which SCN to recover back in time to (should we so decide).

Here, we see the insert and the update as two transactions. We can click the Transaction ID to view the specific transactions (we’ll discuss the function Flashback Transaction later in this chapter).

384
Part III: Using RMAN Effectively

Step 6.
Review the different operations that have occurred against this row in the database, and determine which row may be in error. From this view, we can continue with the wizard and perform a flashback of the table.

Step 7.
If you want proof that OEM is working hard for you, click Back to go to the Recovery: Row History Filter page. At the bottom is a button for Show Flashback Versions Query SQL; after clicking this, you will see the SQL you are blissfully ignoring, as shown next.

Flashback Table

Perhaps the most compelling function of the Flashback Technology is the ability to simply revert a table to a previous point in time in a simple and straightforward fashion. The ability to perform point-in-time recovery on a table or group of tables has often been the grounds by which entire clone databases are built—just so that a single table could be extracted and then imported back into production. With Flashback Table, unnecessary cloning operations can be put to pasture.

Flashback Table employs the same mechanisms as Flashback Query—with information stored in the undo segments, Oracle can rewind a database one transaction at a time to put the table back the way it was at a specified time in the past. Because the Flashback Table operation depends on undo, the same restrictions apply here as they do to Flashback Versions Query: you can only flashback a table as far back as the undo segments allow you.

In addition to undo, the ability to flashback a table requires you to enable row movement for the table. Row movement was initially put in place as a function of partitioned tables, which allowed an updated row to move to the appropriate partition if the update changed the partition key value. Flashback Table employs row movement to assist in the rewind operations. To enable row movement, use the following
alter table
command:

alter table woodscrew enable row movement;

Flashback Table cannot save you from all user errors. Certain DDL operations that occur against a table cannot be undone. Most importantly, you cannot flashback a table to before a
truncate table
operation, because a
truncate
does not produce any undo—that is why
truncate
exists, versus a
delete * from table
. Also, Flashback Table cannot be used for a dropped table (use Flashback Drop for that—see the section “Flashback Drop”).