Oracle has a number of products and features that provide high availability in
cases of unplanned or planned downtime. These include Fast-Start Fault
Recovery, Real Application Clusters (RAC), Recovery Manager (RMAN), backup and recovery
solutions, Oracle Flashback, partitioning,
Oracle Data Guard, LogMiner, multiplexed redolog files and online
reorganization.
To correct problems caused by logical data corruptions or user errors, we can use Oracle Flashback.
To correct problems caused by logical data corruptions or user errors, we can use Oracle Flashback.
Flashback
is possible only in Locally Managed Tablespace(LMTS).
Following are the Flashback options we have in Oracle database:
- Flashback
Query
(from Oracle 9i)
- Flashback Table
(from Oracle 10g)
- Flashback
Drop
(from Oracle 10g)
- Flashback
Version Query (from Oracle 10g)
- Flashback Transaction
Query (from Oracle 10g)
- Flashback
Database (from Oracle 10g)
- Flashback Data
Archive (from Oracle 11g)
- Flashback
Transaction (from Oracle 11g)
Flashback Query useful to view the data at a
point-in-time in the past. This can be used (only) to view and reconstruct lost
data that was deleted or changed by accident.
Flashback Table useful to recover a table to a point-in-time in the past without restoring a backup. Flashback Table is a push button solution to restore the contents of a table to a given point-in-time. An application on top of Flashback Query can achieve this, but with less efficiency.
Flashback Drop provides a way to restore accidentally dropped tables. This will be done with the help of Recyclebin feature.
Flashback Version Query uses undo data stored in the database to view the changes to one or more rows along with all the metadata of the changes.
Flashback Transaction Query useful to examine changes to the database at the transaction level. As a result, we can diagnose problems, perform analysis and audit transactions.
Flashback Database useful to bring database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because we do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Flashback Database applies to the entire database. It requires configuration and resources, but it provides a fast alternative to performing incomplete database recovery.
Flashback Data Archive - from Oracle 11g, flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.
Flashback Table useful to recover a table to a point-in-time in the past without restoring a backup. Flashback Table is a push button solution to restore the contents of a table to a given point-in-time. An application on top of Flashback Query can achieve this, but with less efficiency.
Flashback Drop provides a way to restore accidentally dropped tables. This will be done with the help of Recyclebin feature.
Flashback Version Query uses undo data stored in the database to view the changes to one or more rows along with all the metadata of the changes.
Flashback Transaction Query useful to examine changes to the database at the transaction level. As a result, we can diagnose problems, perform analysis and audit transactions.
Flashback Database useful to bring database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because we do not need to restore the backups. This in turn results in much less downtime following data corruption or human error. Flashback Database applies to the entire database. It requires configuration and resources, but it provides a fast alternative to performing incomplete database recovery.
Flashback Data Archive - from Oracle 11g, flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.
Rules
in order to Flashback
- DBA must have
enabled undo tablespace, with
appropriate retention period.
- User must have
realized the mistake before the retention period.
- User must not
exited from the session.
- Even frontend
must be enabled with flashback option (package).
When
to Use Oracle Flashback
(Flashback
Database Vs Flashback Table)
Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability, and faster restoration.
Flashback Table uses information in the undo tablespace to restore the table. This provides significant benefits over media recovery in terms of ease of use, availability, and faster restoration.
Flashback Database and Flashback Table differ in granularity, performance, and restrictions. For a primary database, consider using Flashback Database rather than Flashback Table in the following situations:
- A user error
affected the whole database.
- A user error
affected a table or a small set of tables, but the impact of reverting
this set of tables is not clear because of the logical relationships
between tables.
- A user error
affected a table or a small set of tables, but using Flashback Table would
fail because of its DDL restrictions.
- Flashback
Database works through all DDL operations, whereas Flashback Table does
not. Flashback Database moves the entire database back in time,
constraints are not an issue, whereas they are with Flashback Table.
Flashback Table cannot be used on a standby database.
Flashback Query in
Oracle
Flashback Query was introduced in Oracle 9i
Oracle Flashback Query allows us to view and repair historical data. We can perform queries on the database as of a certain time or specified system change number (SCN).
Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information, i.e. undo information for committed transactions, can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.
Using Flashback Query, we can query the database as it existed this morning, yesterday, or last week (if undo_retention parameter is set appropriately). The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
Flashback Query was introduced in Oracle 9i
Oracle Flashback Query allows us to view and repair historical data. We can perform queries on the database as of a certain time or specified system change number (SCN).
Flashback Query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information, i.e. undo information for committed transactions, can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size.
Using Flashback Query, we can query the database as it existed this morning, yesterday, or last week (if undo_retention parameter is set appropriately). The speed of this operation depends only on the amount of data being queried and the number of changes to the data that need to be backed out.
Note:
If
Oracle’s default locking is overridden at any level, the database administrator
or application developer should ensure that the overriding locking procedures
operate correctly. The locking procedures must satisfy the following criteria:
data integrity is guaranteed, data concurrency is acceptable, and deadlocks are
not possible or are appropriately handled.
You set the date and time you want to view. Then, any SQL query you run operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
You set the date and time you want to view. Then, any SQL query you run operates on data as it existed at that time. If you are an authorized user, then you can correct errors and back out the restored data without needing the intervention of an administrator.
SQL>
select * from dept as of timestamp sysdate-3/1440;
With the AS OF sql clause, we can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot e.g. suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
DML and DDL operations can use table decoration to choose snapshots within sub queries. Operations such as CREATE TABLE AS SELECT and INSERT TABLE AS SELECT can be used with table decoration in the sub queries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.
With the AS OF sql clause, we can choose different snapshots for each table in the query. Associating a snapshot with a table is known as table decoration. If you do not decorate a table with a snapshot, then a default snapshot is used for it. All tables without a specified snapshot get the same default snapshot e.g. suppose you want to write a query to find all the new customer accounts created in the past hour. You could do set operations on two instances of the same table decorated with different AS OF clauses.
DML and DDL operations can use table decoration to choose snapshots within sub queries. Operations such as CREATE TABLE AS SELECT and INSERT TABLE AS SELECT can be used with table decoration in the sub queries to repair tables from which rows have been mistakenly deleted. Table decoration can be any arbitrary expression: a bind variable, a constant, a string, date operations, and so on. You can open a cursor and dynamically bind a snapshot value (a timestamp or an SCN) to decorate a table with.
SQL>
create table emp_old select * from emp as of timestamp sysdate-1;
Flashback Query Benefits
■ Application Transparency
Packaged applications, like report generation tools that only do queries, can run in Flashback Query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because there is a consistent version of the database as of the Flashback Query time.
■ Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using Flashback Query, there are no costs for logging that would be incurred by explicit versioning.
■ Online Operation
Flashback Query is an online operation. Concurrent DMLs and queries from other sessions are allowed while an object is queried inside Flashback Query. The speed of these operations is unaffected. Moreover, different sessions can flash back to different Flashback times or SCNs on the same object concurrently. The speed of the Flashback Query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.
■ Easy Manageability
There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.
Notes:
Flashback Query Benefits
■ Application Transparency
Packaged applications, like report generation tools that only do queries, can run in Flashback Query mode by using logon triggers. Applications can run transparently without requiring changes to code. All the constraints that the application needs to be satisfied are guaranteed to hold good, because there is a consistent version of the database as of the Flashback Query time.
■ Application Performance
If an application requires recovery actions, it can do so by saving SCNs and flashing back to those SCNs. This is lot easier and faster than saving data sets and restoring them later, which would be required if the application were to do explicit versioning. Using Flashback Query, there are no costs for logging that would be incurred by explicit versioning.
■ Online Operation
Flashback Query is an online operation. Concurrent DMLs and queries from other sessions are allowed while an object is queried inside Flashback Query. The speed of these operations is unaffected. Moreover, different sessions can flash back to different Flashback times or SCNs on the same object concurrently. The speed of the Flashback Query itself depends on the amount of undo that needs to be applied, which is proportional to how far back in time the query goes.
■ Easy Manageability
There is no additional management on the part of the user, except setting the appropriate retention interval, having the right privileges, and so on. No additional logging has to be turned on, because past versions are constructed automatically, as needed.
Notes:
- Flashback Query
does not undo anything. It is only a query mechanism. We can take the
output from a Flashback Query and perform an undo in many circumstances.
- Flashback Query
does not tell us what changed, LogMiner does that.
- Flashback Query
can undo changes and can be very efficient if we know the rows that need
to be moved back in time. We can use it to move a full table back in time,
but this is very expensive if the table is large since it involves a full
table copy.
- Flashback Query
does not work through DDL operations that modify columns, or drop or
truncate tables.
- LogMiner is very
good for getting change history, but it gives changes in terms of deltas
(insert, update, delete), not in terms of the before and after image of a
row. These can be difficult to deal with in some applications.
When
to Use Flashback Query
■ Self-Service Repair
Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current table.
■ E-mail or Voice Mail Applications
You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.
■ Account Balances
You can view account prior account balances as of a certain day in the month.
■ Packaged Applications
Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.
In addition, Flashback Query could be used after examination of audit information to see the before image of the data. In DSS environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
■ Self-Service Repair
Perhaps you accidentally deleted some important rows from a table and wanted to recover the deleted rows. To do the repair, you can move backward in time and see the missing rows and re-insert the deleted row into the current table.
■ E-mail or Voice Mail Applications
You might have deleted mail in the past. Using Flashback Query, you can restore the deleted mail by moving back in time and re-inserting the deleted message into the current message box.
■ Account Balances
You can view account prior account balances as of a certain day in the month.
■ Packaged Applications
Packaged applications (like report generation tools) can make use of Flashback Query without any changes to application logic. Any constraints that the application expects are guaranteed to be satisfied, because users see a consistent version of the Database as of the given time or SCN.
In addition, Flashback Query could be used after examination of audit information to see the before image of the data. In DSS environments, it could be used for extraction of data as of a consistent point in time from OLTP systems.
No comments :
Post a Comment