BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Using Oracle Flashback Query

Oracle Tips by Burleson Consulting

In the chart in Figure 15.1, several types of recovery, such as table-level recovery, can be dependent upon using flashback query. In essence, flashback query allows a Remote DBA to recover data that has been deleted, revert data that has been updated to its former state, and undo inserts, as long as the data involved is still available in the undo structures in the undo tablespace. This retention of values in the undo tablespace is based on the amount of available space in the undo tablespace and the UNDO_RETENTION initialization parameter. If there is enough space in the undo tablespace, then the data contained in the undo segments will not be overwritten until at least UNDO_RETENTION (which defaults to 900 in seconds) amount of time has passed.

This new capability, to go back in time via the undo retention feature, allows Remote DBAs to be able to fix those oops-type errors, such as deleting all the rows in a table, updating all the rows in a table, dropping a table, and other such things that we Remote DBAs "never" do.

In order to use the flashback query, option the following initialization parameters must be set:

UNDO_MANAGEMENT. Set to AUTO.

UNDO_RETENTION. Leave at default (900) or set to a precalculated value.

UNDO_SUPPRESS_ERRORS. Set to FALSE.

UNDO_TABLESPACE. Set to the name of the undo tablespace (default is UNDOTBS).

In addition to setting the initialization parameters, any user who wants to use the DBMS_FLASHBACK package must have the EXECUTE privilege on the DBMS_FLASHBACK package. The DBMS_FLASHBACK package is useful only if a snapshot of the proper data has been maintained before it is needed. Therefore, I suggest that any major transaction should include a call to the DBMS_FLASHBACK.GET_SYSTEM_CHANGE _NUMBER (RETURN NUMBER); function and that the SCN returned be stored in a temporary table until the transaction has been verified. This will allow the Remote DBA to use the DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE (query_scn IN NUMBER); procedure to reset the database to the values held at the SCN before any changes occurred. Once the database session has been reset to the SCN just prior to the transaction, the Remote DBA can open a cursor to retrieve the required data to rebuild a table or repair damaged entries and then issue a call to DBMS_FLASHBACK.DISABLE; to return the database session to normal mode. Once in normal mode, any data placed into a cursor prior to the return to normal mode will stay at the pre-SCN values and allow the Remote DBA to recover the information. In procedural form this would be:

1. In the transaction that will be causing changes to data, create a small temporary table, and after a preliminary COMMIT, place the current SCN into the table using a call to the DBMS_ FLASHBACK.GET_SYSTEM_ CHANGE_NUMBER (RETURN NUMBER); function call and INSERT command; commit the entry.

2. Execute the transaction that changes data.

3. Review changes; if improper, then issue a call to DBMS_FLASHBACK. ENABLE_AT_SYSTEM_CHANGE (query_scn IN NUMBER); to enable flashback processing for the current session at the SCN just prior to the change.

4. Open the required cursors to retrieve the proper versions of the data from the flashback database image.

5. Use a call to DBMS_FLASHBACK.DISABLE to disable the flashback database image and return to normal (current) mode.

6. Use the data in the cursors to restore the proper information into the damaged table or restore the deleted table.

A simple test of the flashback query using time-based flashback processing is shown in Listing 15.7, and the Oracle9i Supplied PL/SQL Packages and Types Reference, Release 1 (9.0.1), Part Number A89852-02, manual has other excellent PL/SQL examples of this process.

LISTING 15.7 Use of the flashback query option to restore table data.

First, just for this test, let's create a user, normally you could just grant EXECUTE on the DBMS_FLASHBACK to your transaction user. 

SQL> CREATE USER test_flash IDENTIFIED BY flash
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp
  4  QUOTA UNLIMITED ON users;
User created. 
SQL> GRANT CONNECT, RESOURCE TO test_flash;
Grant succeeded. 
SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO test_flash;
Grant succeeded.

Now, we connect to the test user and create and populate a test table.

SQL> CONNECT test_flash/flash
Connected. 

SQL> CREATE TABLE test_fb(t number(5), "COMMENT" varchar2(32))
SQL> / 

Table created.

SQL> INSERT INTO test_fb values (1, 'inserted at '||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'))
SQL> /

1 row created.

SQL> c/1/2
  1* INSERT INTO test_fb values (2, 'inserted at '||to_char(sysdate,'dd-mon-yyyy
hh24:mi:ss'))
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM test_fb;
 

         T COMMENT
---------- --------------------------------                                    
         1 inserted at 04-dec-2001 15:04:44
         2 inserted at 04-dec-2001 15:05:20                                      

Now let's create a table to track a date where we do our mayhem. The date type flashback will go to the nearest 5 minute interval before the specified date.

SQL> CREATE TABLE keep_date (date_scn_tracking date);

Table created.

Now we will disable flashback processing. Just to make sure we are in the "current" database environment.

SQL> EXECUTE DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

We now insert our timestamp (note that the new timestamp datatype is supported as well, but since we are going to the nearest 5 minute interval it seems a bit of overkill).

SQL> INSERT INTO keep_date values (sysdate);

1 row created.

SQL> commit;

Commit complete.

Now let's do something to our test table.

SQL> DELETE FROM test_fb WHERE t=1;

1 row deleted.

SQL> COMMIT;

Commit complete.

Just to show you I didn't stuff the card up my sleeve:

SQL> SELECT * FROM test_fb;

         T COMMENT 
---------- --------------------------------                                    
         2 inserted at 04-dec-2001 15:05:20                                      

Now commit to end any transactions and start us fresh:

SQL> COMMIT;

Commit complete.

Create the recovery program. Notice how we get the date value back form the storage table, use it to set our snapshot into the flashback environment, then get the row we need and then disable flashback to bring us back into the current database, we then re-insert the record and everything is back to the way it was (except row order is changed, but hey, we can't have everything can we?)

SQL> declare
  2  restore_scn date;
  3  begin
  4  select date_scn_tracking into restore_scn from keep_date;
  5  dbms_flashback.enable_at_time(restore_scn);
  6  end;
  7  / 

PL/SQL procedure successfully completed. 

Look, nothing up my sleeve and Presto!

SQL> SELECT * FROM test_fb;

         T COMMENT                                                             
---------- --------------------------------                                    
         1 inserted at 04-dec-2001 15:04:44                                    
         2 inserted at 04-dec-2001 15:05:20                                    

SQL> declare
   2  cursor c1 is
  3  select * from test_fb where t=1;
  4  c1_rec c1%rowtype;
  5  begin
  6  open c1;
  7  dbms_flashback.disable;
  8  fetch c1 into c1_rec;
  9  insert into test_fb values(c1_rec.t,c1_rec.comment);
 10  commit;
 11* end;
SQL> /
 
PL/SQL procedure successfully completed.

Now let's see what the table looks like.

SQL> SELECT * FROM test_fb;

         T COMMENT                                                             
---------- --------------------------------                                    
         2 inserted at 04-dec-2001 15:05:20
         1 inserted at 04-dec-2001 15:04:44                                      

Everything is back, except of course the row order is different. Just to be sure:

SQL> EXECUTE DBMS_FLASHBACK.DISABLE;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_fb;

         T COMMENT
---------- --------------------------------                                     
         2 inserted at 04-dec-2001 15:05:20                                    
         1 inserted at 04-dec-2001 15:04:44                                    

Further Remote DBA Reading

The Remote DBA is encouraged to obtain and review the following resources:

Oracle9i Backup and Recovery Concepts, Release 1 (9.0.1), Part Number A90133-02, Oracle Corporation, June 2001.

Oracle9i Database Utilities, Release 1 (9.0.1), Part Number A90192-01, Oracle Corporation, June, 2001.

Oracle9i Recovery Manager User's Guide, Release 1 (9.0.1), Part Number A90135-01, Oracle Corporation, June 2001.

Oracle9i Supplied PL/SQL Packages and Types Reference, Release 1 (9.0.1), Part Number A89852-02, Oracle Corporation, June 2001.

PL/SQL User's Guide and Reference, Release 9.0.1, Part Number A89856-01, Oracle Corporation, June 2001.

 
See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter