| |
According to many studies, 40% of application outages are caused by operator or user errors. Part of being human is making mistakes. But these errors are extremely difficult to avoid and can be particularly difficult to recover from without advance planning and the right technology. Such errors can result in "logical" data corruption, or cause downtime of one or more components of the IT infrastructure. While it is relatively simple to rectify the failure of an individual component, detection and repair of logical data corruption, such as accidental deletion of valuable data, is a time consuming operation that causes enormous loss of business productivity. Typical user-errors may include accidental deletion of valuable data, deleting the wrong data, and dropping the wrong table. Guarding Against Human Errors Oracle9i introduced Flashback Query to provide a simple, powerful and completely non-disruptive mechanism for recovering from human errors. It allows users to view the state of data at a point in time in the past without requiring any structural changes to the database. Oracle Database 10g extended the Flashback Technology to provide fast and easy recovery at the database, table, row, and transaction level. Flashback Technology revolutionizes recovery by operating just on the changed data. The time it takes to recover the error is now equal to the same amount of time it took to make the mistake. Oracle 10g Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query. Flashback technology can just as easily be utilized for non-repair purposes, such as historical auditing with Flashback Query and undoing test changes with Flashback Database. Oracle Database 11g introduces an innovative method to manage and query long-term historical data with Flashback Data Archive. This release also provides an easy, one-step transaction backout operation, with the new Flashback Transaction capability. New Features in Oracle Database 11g Flashback Data Archive Flashback Data Archive can be used to automatically track and maintain historical changes to all Oracle data in a highly application transparent, secure and efficient manner. Part of the Oracle Total Recall Option, Flashback Data Archive provides enterprises with a quick, centralized and extremely efficient solution to meet all historical data management needs. Flashback Data Archive automatically tracks every single change made to the data stored inside the database and maintains a secure, efficient and easily accessible archive of historical data. The captured historical data can be retained for as long as the business demands and is easily accessible using Flashback SQL queries. Historical data tracking can be enabled on both existing and new tables instantaneously and more importantly, in a completely application transparent manner. Flashback Data Archive presents a high-performance, storage optimized solution with a centralized management interface for satisfying data retention and change control requirements for organizations. The primary advantages of using Flashback Data Archive for historical data tracking include:
Flashback Transaction Large-scale database applications rely on complex sequences of transactions, to ensure atomicity and consistency of a group of inserts, updates, or deletes. In the event of a ‘bad’ transaction, the administrator must trail back-in-time to see what changes were effected by the transaction and ascertain any dependencies (e.g. transactions that modified the same data after the ‘bad’ transaction), to ensure that undoing the transaction preserves the original, good state of the data and any related data. Performing this type of transaction analysis can be laborious, especially for very complex applications. With Flashback Transaction, a single transaction, and optionally, all of its dependent transactions, can be flashed back with a single PL/SQL operation or by using an intuitive EM wizard to identify and flashback the problem transactions. Flashback Transaction relies on the availability of undo data and archived redo logs for the given transaction and its dependents, to backout the changes. New Features in Oracle Database 10g Release 2 Restore Points When an Oracle database point-in-time recovery operation is required, a DBA must determine a time or SCN to which the data must be rolled back. Oracle Database 10g Release 2 simplifies point in time recovery with restore points. A restore point is a user-defined name that can be substituted for an SCN or clock time when used in conjunction with Flashback Database, Flashback Table, and Recovery Manager (RMAN), and can be created at the command-line with SQL*Plus or RMAN, or through Enterprise Manager. Restore points eliminate the need to investigate the SCN or time of a transaction and provides users with the ability to bookmark a database transaction event. Guaranteed restore points ensure that sufficient flashback logs are always maintained to get back to that restore point. This means that flashback logs will not be deleted by the Flash Recovery Area, unless they are not needed for the current guaranteed restore points. These special restore points can be created before major database changes, such as a database batch job or schema upgrade, and used for flashback if the changes need to be undone. Flashback Database Through RESETLOGS Flashback Database through RESETLOGS allows flashback logs created prior to a RESETLOGS operation to be utilized for Flashback Database operations. In Oracle Database 10g Release 2, flashback logs are preserved after opening the database with RESETLOGS. This new feature is useful when a long-standing logical error is not discovered until after RESETLOGS is performed, and a flashback prior to RESETLOGS is needed. In an Oracle Data Guard environment, this capability allows a physical standby database that has been opened read-write to later flashback the changes and be converted back to a physical standby database. If a logical error is discovered after a switchover operation, the primary and standby databases can be flashed back to an SCN or a point in time prior to the switchover operation. New Features in Oracle Database 10g Release 1 Flashback Database Flashback Database quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. Flashback Database is like a 'rewind button' for your database. It provides database point in time recovery without requiring a backup of the database to first be restored. When you eliminate the time it takes to restore a database backup from tape, database point in time recovery is fast
The Flashback Database capability, accessible from both RMAN and SQL*Plus by using the FLASHBACK DATABASE command, is similar to conventional point-in-time recovery in its effects. It allows you to return a database to its state at a time in the recent past. To enable the Flashback Database capability, a DBA configures the Flash Recovery Area. The Flash Recovery Area is a new feature in Oracle Database 10g that provides a unified storage location for all recovery related files and activities in an Oracle database. Besides Flashback Database logs, the recovery area contains archived redo logs and RMAN backups. For more information on the Flash Recovery Area, consult the Oracle Backup and Recovery documentation. Oracle automatically creates and manages Flashback Logs within the Flash Recovery Area. Since the Flash Recovery Area is configured with a space quota, the Flashback Logs are subject to those disk space restrictions. The size of Flashback Logs can vary considerably, depending on the read/write ratio of database changes during a given flashback-logging interval. A copy of the old block version is written to the Flashback Log. If, over the course of a day, 10% of the database blocks are updated, then the size of Flashback Logs for 24 hours is approximately one-tenth the size of your database. The DBA may change this disk quota dynamically if more disk space is required to recover the database to an earlier time in the past. Flashback provides Data Guard with an easy-to-use method to correct user errors. Flashback Database can be used on both the primary and standby database to quickly revert the databases to an earlier point in time to back out user errors. If the administrator decides to failover to a standby database, but those user-errors were already applied to the standby database (for example, because Real Time Apply was enabled), the administrator can simply flashback the standby database to a safe point in time.
The performance overhead of enabling Flashback Database is less than 2%. While you may not be willing to sacrifice any performance overhead for your production database, think about the trade-off. If you could recover the database in minutes instead of hours, saving your company millions of dollars in lost revenue, would you then give 2% of the resources to Flashback Database? Enabling Flashback Database functionality provides the following benefits.
Flashback Table When a human or application error occurs, you want to be able to restore the state of one or more tables to a point in time before the problem occurred. Flashback Table provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. Flashback Table restores the tables while automatically maintaining its associated attributes such as - the current indexes, triggers and constraints, not requiring the DBA to find and restore application specific properties. Flashback Table alleviates the need for you to perform more complicated point in time recovery operations. The following command flashes back the ORDERS and ORDER_ITEMS tables to 2:33 PM on July 7.
Like Flashback Query, Flashback Table also relies on the undo data to recover the tables. The undo data, therefore, must be available in order for a Flashback Table to be successful. The Automatic Undo Management feature allows you to specify how long they wish to retain the undo data using the UNDO_RETENTION initialization parameter. By using this parameter and sizing the undo tablespace appropriately, DBAs can control how far back in time a table can be repaired using Flashback Table. While a DBA can use the Flashback Table feature to quickly recover from human errors, it also serves as a self-service repair tool to recover from accidental modifications or deletions. An application developer can incorporate the Flashback Table functionality into their customized application. This tool provides significant benefits over media recovery in terms of ease of use, availability and faster restoration with point-in-time object based recovery. Flashback Table
Flashback Drop What is the Recycle Bin? The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if
Dropped the wrong table? No problem. Just undrop it with Flashback Drop. Flashback Query Introduced with Oracle9i, Flashback Query provides the ability to view the data as it existed in the past. By default, operations on the database use the most recent committed data available. If you want to query the database as it was at some time in the past, you can do so with the Flashback Query feature. It lets you specify either a time or a system change number (SCN) and query using the committed data from the corresponding time. The Flashback Query mechanism is most effective when you use Automatic Undo Management. The Oracle database treats undo as a first class database object. Undo is persistent and can survive database system crash or, shutdown. It also shares the database buffer cache with other database objects for better performance. The Oracle database uses undo beyond transaction commit to provide read consistency for long running queries and also, to recover from logical corruptions. The Oracle database provides a means of explicitly specifying the amount of undo to retain. The system automatically recycles expired undo to make space for new transactions to generate undo. The choice of undo retention value depends upon the length of the long running queries and the logical corruption recovery requirements. Users can, however, choose not to specify the undo retention and allow the system to provide the best retention for the given undo space. This best retention allows for best possible coverage for the long running queries and also, to recover from logical corruptions. The default undo retention is not guaranteed. The system can use oldest un-expired undo if it runs out of expired undo to use for an ongoing transaction. New in Oracle Database 10g Release 1 is the ability to query data in the past for more than 5 days if the UNDO_RETENTION is set for greater then 5 days. Oracle will maintain the undo for that period of time as long as the Undo Tablespace datafiles are allocated enough disk space The following describes the steps required to ensure a database is enabled to use the Flashback Query and other flashback features that are dependent upon undo information:
The unique feature of Flashback Query allows you to see the data as it was in the past, then choose exactly how to process the information. You might perform an analysis and then undo the changes, or capture changed data for further processing. The Flashback Query mechanism is flexible enough to be used in many situations. You can:
Flashback Versions Query Flashback Versions Query provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management. The Flashback Versions Query is an extension to SQL that allows you to retrieve the different versions of rows in a given table that existed in a specific time interval. For any given table, a new row version is created every time the COMMIT statement is executed. The Flashback Versions Query returns a row for each version of the row that existed in the time interval you specify. You invoke the Flashback Versions Query functionality by using the VERSIONS BETWEEN clause of the SELECT statement. Flashback Versions Query offers new additional columns that provide transaction details on the row data that allows a DBA to pinpoint when and how data is changed in the Oracle database.
The Flashback Versions Query is a powerful tool for the DBA to run analysis and answer the question, 'How did this happen?' Not only can the DBA run manual analysis, but this is a powerful tool for the application's developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Flashback Transaction Query You may discover that somehow data in a table has been inappropriately changed. To research this change, you can use multiple flashback queries to view row data at specific points in time. More efficiently, you can use Flashback Versions Query feature to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. Once you identify an erroneous transaction, you can then use the Flashback Transaction Query feature to identify other changes that were done by the transaction, and to request the undo SQL to reverse those changes. The FLASHBACK_TRANSACTION_QUERY view is the means by which you obtain transaction history and undo SQL. If you need to reverse the effects of the erroneous transaction, the undo SQL statements can be manually executed, allowing easy recovery from user or application errors. Flashback Transaction Query can increase online diagnosability of problems in your database and facilitate analysis and audits of transactions. Summary Human errors are one of the predominant causes of system failure. These errors are extremely difficult to avoid and can be particularly difficult to recover without advance planning and the right technology. The 'right' technology is here: Oracle Database 11g. Why should an error that takes seconds to execute take hours or days to recovery from? It shouldn't and now it doesn't. Flashback revolutionizes recovery by operating on just the changed data. A single command surgically repairs corruptions from human errors. Flashback technology removes the complexity of recovery while decreasing the time it takes to recover from unpredictable human errors. |
Oracle Flashback Technology
最新推荐文章于 2024-08-06 19:19:19 发布