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

 

 


 

 

 

        
 

 Oracle DBMS_FLASHBACK
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

At the beginning of this chapter, various use case scenarios for the data pump export and import were mentioned. Namely, that a DBA might need the ability to restore collections of tables to some pristine state on a regular interval. In the prior section, creating restructures were illustrated where a backup or before image might be handy in case of problems during mid-restructuring. The truth is that there are many cases where the DBA would like the ability to restore a logical portion of the database back in time or to a prior SCN. But the problem is that physical backups and restores are very complex and operate more at the physical database level. Few applications are generally worthy of their own backup and recovery strategy using the physical tools to build logical or application based restore points.

 

However, a long time ago Oracle introduced a great concept in the SQL and PL/SQL languages called the SAVEPOINT. This was the ability to issue a database state bookmark within the application code such that one could rollback to an application based logical point in time. This was a useful technique but never really saw extensive usage. Nevertheless, it was a good concept if only it would have extended to database objects and/or even the database level itself. Well, now it does and it is Oracle’s flashback technology.

 

Oracle flashback technology essentially lets the DBA create SAVEPOINT like bookmarks to restore to either objects or the entire database. In some respects, it is a great short term point-in-time recover technique, rather than going to a full blown backup and restore. Plus, its usage has been made so integrated, seamless and easy that it is sure to see heavy usage as time goes on. It is truly a definite must-have tool for the DBA’s tool belt.

 

There are six flashback technologies, in chronological order of their appearance, whose topics will be examined in more detail:

  • Flashback Queries (9i)

  • Flashback Drop (10g)

  • Flashback Tables

  • Flashback Database

  • Flashback Transaction (11g)

  • Flashback Archives (Oracle Total Recall)

Furthermore, unlike other features covered in this chapter, it will be beneficial to learn how to utilize these various flashback technology capabilities via OEM, SQL commands and the PL/SQL API.

 

Note:  Part of the reason for covering all the flashback technologies here, including a recap of older ones, is to hopefully lead the reader along the historical path of flashback technology development and, therefore, to perceive that each step was built on the foundations of those prior.

Flashback Queries

Oracle 9i introduced the concept of the flashback query. This can be called the “Back to the Future” or time machine type query  where Oracle lets DBAs make some queries in the present as if from a database state in the not too distant past.

 

The black magic that makes this possible are UNDO tablespaces and automatic UNDO management and Oracle now treats those UNDO blocks as first-rate data based upon the undo_retention parameter. Using these, Oracle does its best to retain UNDO data. One can even force that availability via the UNDO tablespace RETENTION GUARANTEE option.

 

In its simplest form, merely add an AS OF clause to the SELECT statement to request the current execution be performed as if it were run at some prior specified time or system change number like looking backwards in time.

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

------------------------

                  353026

 

SQL> update movies.customer set zip='99999';

 

62 rows updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select firstname, lastname, zip from movies.customer as of scn 353026 where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

-------------------- ------------------------------ -----

Flozell              Adams                          75063

Troy                 Aikman                         75063

Larry                Allen                          75063

Eric                 Bjornson                       75063

 

The entire Oracle session can also be enabled to enter a “time tunnel” or “time warp” so that nothing has to be added to the SELECT command to see such historical data. In that case, simply enable and disable the flashback effect as shown here via the PL/SQL packages found in dbms_flashback.

 

SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

-------------------- ------------------------------ -----

Flozell              Adams                          99999

Troy                 Aikman                         99999

Larry                Allen                          99999

Eric                 Bjornson                       99999

 

SQL>

SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(353026)

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

-------------------- ------------------------------ -----

Flozell              Adams                          75063

Troy                 Aikman                         75063

Larry                Allen                          75063

Eric                 Bjornson                       75063

 

SQL> execute DBMS_FLASHBACK.DISABLE

 

PL/SQL procedure successfully completed.

     


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

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.