Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 1 (10.1) Part Number B10734-01 |
|
|
View PDF |
This chapter describes how to use the flashback features of Oracle to retrieve lost data in data recovery scenarios. This chapter includes the following sections:
Oracle Flashback Technology provides a set of features that support viewing and rewinding data back and forth in time. The flashback features offer the capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repair to recover from logical corruptions while the database is online.
DROP
TABLE
statement.Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.
See Also:
|
In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN'
had been deleted from your EMPLOYEE
table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.
Querying the past state of the table is achieved using the AS OF
clause of the SELECT
statement. For example, the following query retrieves the state of the employee record for 'JOHN
' at 9:30AM, April 4, 2003:
SELECT * FROM EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN';
Restoring John's information to the table EMPLOYEE requires the following update:
INSERT INTO employee (SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN');
The missing row is re-created with its previous contents, with minimal impact to the running database.
See Also:
|
Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.
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 of data.
For more information on Automatic Undo Management, see Oracle Database Administrator's Guide.
The prerequisites for performing a FLASHBACK TABLE
operation are as follows:
FLASHBACK ANY TABLE
system privilege or you must have the FLASHBACK
object privilege on the table.SELECT
, INSERT
, DELETE
, and ALTER
privileges on the table.FLASHBACK TABLE
operation.FLASHBACK TABLE
statement. You can enable row movement with the following SQL statement:
ALTER TABLE table ENABLE ROW MOVEMENT;
The following SQL*Plus statement performs a FLASHBACK TABLE
operation on the table employee
:
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');
The employee
table is restored to its state when the database was at the time specified by the timestamp.
You can also specify the target point in time for the FLASHBACK TABLE
operation using an SCN:
FLASHBACK TABLE employee TO SCN 123456;
The default for a FLASHBACK TABLE
operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS
clause of the FLASHBACK TABLE
statement, as shown in this example:
FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' ENABLE TRIGGERS;
The following scenario is typical of the kind of logical corruption where Flashback Table could be used:
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. Someone accidentally deleted the record for "JOHN" between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, as shown in this example:
FLASHBACK TABLE EMPLOYEES TO TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS') ENABLE TRIGGERS;
See Also:
Oracle Database SQL Reference for a simple Flashback Table scenario |
Oracle Flashback Drop reverses the effects of a DROP TABLE
operation. The intention behind this feature is to provide users with a recovery mechanism for an accidental drop of a table. Flashback Drop is substantially faster than other recovery mechanisms (such as point-in-time recovery) and also does not lead to any loss of recent transactions.
When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database. The Flashback Drop operation recovers the table from the recycle bin.
To understand how to use Oracle Flashback Drop, you must also understand how the recycle bin works, and how to access and manage its contents.
This section covers the following topics:
The recycle bin is a logical container for all dropped tables and their dependent objects. When a table is dropped, the database will store the table, along with its dependent objects in the recycle bin so that they can be recovered later. Dependent objects which are stored in the recycle bin include indexes, constraints, triggers, nested tables, LOB segments and LOB index segments.
Tables are placed in the recycle bin along with their dependent objects whenever a DROP TABLE statement is executed. For example, this statement places the EMPLOYEE_DEMO table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:
SQL> DROP TABLE EMPLOYEE_DEMO; Table Dropped
The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. You can explicitly purge a table or other object from the recycle bin with the SQL*Plus PURGE
statement, as described in "Purging Objects from the Recycle Bin". If you are sure that you will not want to recover a table later, you can drop it immediately and permanently, instead of placing it in the recycle bin, by using the PURGE
option of the DROP TABLE
statement, as shown in this example:
DROP TABLE employee_demo PURGE;
Even if you do not purge objects from the recycle bin, the database purges objects from the recycle bin to meet tablespace space constraints. See "Recycle Bin Capacity and Space Pressure" for more details.
Recycle bin objects are not counted as used space. If you query the space views to obtain the amount of free space in the database, objects in the recycle bin are counted as free space.
Dropped objects still appear in the views USER_TABLES
, ALL_TABLES
, DBA_TABLES
, USER_INDEX
, ALL_INDEX
and DBA_INDEX
.A new column, DROPPED
, is set to YES
for these objects. You can use the DROPPED
column in queries against these views to view only objects that are not dropped.
To view only objects in the recycle bin, use the USER_RECYCLEBIN
and DBA_RECYCLEBIN
views, described later in this chapter.
When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts that may arise in the following circumstances:
The assigned names are globally unique and are used to identify the objects while they are in the recycle bin. Object names are formed as follows:
BIN$$globalUID$version
where:
globalUID
is a globally unique, 24 character long identifier generated for the object.version
is a version number assigned by the databaseThe recycle bin name of an object is always 30 characters long.
Note that the globalUID
used in the recycle bin name is not readily correlated with any externally visible piece of information about the object or the database.
You can view the contents of the recycle bin using the SQL*Plus command SHOW RECYCLEBIN
.
SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- --------------------------------- ------------ ------------------- EMPLOYEE_DEMO BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2003-06-11:17:08:54
The ORIGINAL NAME
column shows the orignal name of the object, while the RECYCLEBIN NAME
column shows the name of the object as it exists in the recycle bin. Use the RECYCLEBIN NAME
when issuing queries against tables in the recycle bin.
The database also provices two views for obtaining information about objects in the recycle bin:
This example uses the views to determine the original names of dropped objects:
SQL> SELECT object_name as recycle_name, original_name, object_type FROM recyclebin; RECYCLE_NAME ORIGINAL_NAME OBJECT_TYPE -------------------------------- --------------------- ------------- BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 EMPLOYEE_DEMO TABLE BIN$JKS983293M1dsab4gsz/I249==$0 I_EMP_DEMO INDEX BIN$NR72JJN38KM1dsaM4gI348as==$0 LOB_EMP_DEMO LOB BIN$JKJ399SLKnaslkJSLK330SIK==$0 LOB_I_EMP_DEMO LOB INDEX
You can query objects that are in the recycle bin, just as you can query other objects, if these three conditions are met:
This example shows the required syntax:
SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";
(Note the use of quotes due to the special characters in the recycle bin name.)
You can also use Oracle Flashback Query on tables in the recycle bin (again, assuming that you have the privileges described previously).
There is no fixed amount of space pre-allocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the recycle bin.
The rules that govern how long an object is retained in the recycle bin and how and when space is reclaimed are explained in this section.
Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace. This condition is referred to as space pressure. Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota on it.
Oracle never automatically reclaims space or overwrites objects in the recycle bin unless forced to do so in response to space pressure.
When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.
Actual purging of objects is done only as needed to meet ongoing space pressure, that is, the databases purges the minimum possible number of objects selected for purging to meet immediate needs for space. This policy serves two purposes:
Dependent objects such as indexes on a table are selected for purging before the associated table (or other required segment).
If space pressure is due to an individual user's quota on a tablespace being exhausted, the recycle bin purges objects belonging to the tablespace which count against that user's space quotas.
For AUTO EXTEND-able tablespaces, objects are purged from the recycle bin to reclaim space before datafiles are extended.
The recycle bin operates at the object level, in terms of tables, indexes, and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately satisfy space pressure, it can happen that some but not all segments of an object are reclaimed. When this happens, any segments of the object not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time space pressure arises.
In such a case, the partially-reclaimed object can no longer be removed from the recycle bin with Flashback Drop. (For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.)
Use the FLASHBACK
TABLE
... TO
BEFORE
DROP
statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. This can be obtained from either the DBA_RECYCLEBIN
or USER_RECYCLEBIN
view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK
TABLE
... TO
BEFORE
DROP
statement, you need the same privileges you need to drop the table.
The following example restores the BIN$KSD8DB9L345KLA==$0 table, changes its name back to hr.int_admin_emp
, and purges its entry from the recycle bin:
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP;
You can also use the table's original name in the Flashback Drop operation:
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
You can assign a new name to the restored table by specifying the RENAME TO
clause. For example:
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP RENAME TO hr.int2_admin_emp;
You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:
CREATE TABLE EMP ( ...columns ); # EMP version 1 DROP TABLE EMP; CREATE TABLE EMP ( ...columns ); # EMP version 2 DROP TABLE EMP; CREATE TABLE EMP ( ...columns ); # EMP version 3 DROP TABLE EMP;
In such a case, each table EMP
is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP
statement with the original name of the table, as shown in this example:
FLASHBACK TABLE EMP TO BEFORE DROP;
The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO
clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3; FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2; FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;
Note that the last table dropped is the first one to be retrieved.
You can also retrieve any table you want from the recycle bin, regardless of any such name collisions, by using the table's unique recycle bin name.
The PURGE
command is used to permanently purge objects from the recycle bin. Once purged, objects can no longer be retrieved from the bin using Flashback Drop.
There are a number of forms of the PURGE statement, depending on exactly which objects you want to purge from the recycle bin
See Also:
Oracle Database SQL Reference for more information on the |
The PURGE TABLE
command purges an individual table and all of its dependent objects from the recycle bin. This example shows the syntax, using the table's original name:
PURGE TABLE EMP;
You can also use the recycle bin name of an object with PURGE TABLE
:
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
If you have created and dropped multiple tables with the same orignal name, then when you use the PURGE TABLE
statement the first table dropped will be the one to be purged.
For example, consider the following series of CREATE TABLE
and DROP TABLE
statements:
CREATE TABLE EMP; # version 1 of the table DROP TABLE EMP; # version 1 dropped CREATE TABLE EMP; # version 2 of the table DROP TABLE EMP; # version 2 dropped CREATE TABLE EMP; # version 3 of the table DROP TABLE EMP; # version 3 dropped
There are now three EMP
tables in the recycle bin. If you execute PURGE TABLE EMP
several times, the effect is as described here:
PURGE TABLE EMP; # version 1 of the table is purged PURGE TABLE EMP; # version 2 of the table is purged PURGE TABLE EMP; # version 3 of the table is purged
Note that this is the opposite of the behavior of FLASHBACK TABLE... TO BEFORE DROP
, where using the original name of the table retrieves the most recently dropped version from the recycle bin.
You can use PURGE INDEX
to purge just an index for a table, while keeping the base table in the recycle bin. The syntax for purging an index is as follows:
PURGE INDEX "BIN$GTE72KJ22H9==$0";
By purging indexes from the recycle bin, you can reduce the chance of space pressure, so that dropped tables can remain in the recycle bin longer. If you retrieve a table from the recycle bin using Flashback Drop, you can rebuild the indexes after you retrieve the table.
You can use the PURGE TABLESPACE
command to purge all dropped tables and other dependent objects from a specific tablespace. The syntax is as follows:
PURGE TABLESPACE hr;
You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:
PURGE TABLESPACE hr USER scott;
The PURGE RECYCLEBIN
command purges the contents of the recycle bin for the currently logged-in user.
PURGE RECYCLEBIN;
It purges all tables and their dependent objects for this user, along with any other indexes owned by this user but not on tables owned by the user.
If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:
PURGE DBA_RECYCLEBIN;
When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.
If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS
clause with DROP TABLESPACE
.
When a user is dropped, any objects belonging to the user that are not in the recycle bin are dropped immediately, not placed in the recycle bin. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.
When you drop a cluster, all tables in the cluster are purged. When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.
This section summariezes the system privileges required for the operations related to Flashback Drop and the recycle bin.
DROP
Any user with drop privileges over the object can drop the object, placing it in the recycle bin.
FLASHBACK TABLE... TO BEFORE DROP
Privileges are tied to the privileges for DROP
. That is, any user who can drop an object can perform Flashback Drop.
PURGE
Privileges are tied to the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.
SELECT
for objects in the Recycle Bin
Users must have SELECT
and FLASHBACK
privileges over an object in the recycle bin to be able to query the object in the recycle bin. Any users who had the SELECT
privilege over an object before it was dropped continue to have the SELECT
privilege over the object in the recycle bin.Users must have FLASHBACK
privilege to query any object in the recycle bin, because these are objects from a past state of the database.
It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK
DATABASE
command) and SQL*Plus (by means of the FLASHBACK
DATABASE
statement), lets you quickly recover the entire database from logical data corruptions or user errors.
It is similar to conventional point in time recovery in its effects, allowing you to return a database to its state at a time in the recent past. Flashback Database is, however, much faster than point-in-time recovery, because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.
To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.
From that time on, at regular intervals, the database copies images of each altered block in every datafile into flashback logs stored in the flash recovery area. These block images can later be re-used to reconstruct the datafile contents as of any moment at which logs were captured.
To restore a database to its state at some past target time using Flashback Database, each block is restored to its contents as of the flashback logging time most immediately prior to the desired target time, and then changes from the redo logs are applied to fill in changes between the time captured by the flashback logs and the target time. Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. In practice, however, redo logs are often kept much longer than flashback logs, so this requirement is not a real limitation.
The time required to perform Flashback Database is largely a function of how far back the target time is and the number of blocks changed, rather than the volume of individual updates to the database.
Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:
Finally, it is important to note that the flashback retention target is a target, not an absolute guarantee that Flashback Database will be available. If your flash recovery area is not large enough to hold both required files such as archived redo logs and other backups, flashback logs may be deleted to make room in the flash recovery area for these required files. If you discover that Oracle has discarded flashback logs required to reach your desired target time for Flashback Database, you can always use traditional point-in-time recovery instead to achieve a similar result.
The requirements for enabling Flashback Database are:
ARCHIVELOG
mode, because archived logs are used in the Flashback Database operation.To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET
initialization parameter and issue the ALTER
DATABASE
FLASHBACK ON
statement. Follow the process outlined here.
SQL> SELECT STATUS FROM V$INSTANCE;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
SQL> ALTER DATABASE FLASHBACK ON;
By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:
SQL> ALTER TABLESPACE test1 FLASHBACK OFF;
You can re-enable flashback logging for a tablespace later with this command:
SQL> ALTER TABLESPACE test1 FLASHBACK ON;
Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK
DATABASE
.
You can disable flashback logging for the entire database with this command:
SQL> ALTER DATABASE FLASHBACK OFF;
You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment. See Oracle Data Guard Concepts and Administration for details.
The setting of the DB_FLASHBACK_RETENTION_TARGET
initialization parameter determines, indirectly, how much flashback log data the database should keep. This limit is contingent upon sufficient space existing in the flash recovery area. The size of flashback logs can vary considerably, however, depending on the locality of database changes during a given flashback logging interval.
The V$FLASHBACK_DATABASE_LOG
view can help you decide how much space to add to your flash recovery area for flashback logs. After you have enabled the Flashback Database feature and allowed the database to generate some flashback logs, run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
An estimate of disk space needed to meet the current flashback retention target is calculated, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
to your flash recovery area size, to hold the dataabase flashback logs.
Space usage in the flash recovery area is always balanced among backups and archived logs which must be kept according to the retention policy, and other files like flashback logs and backups already moved to tape but still cached on disk. If you have not allocated enough space in your flash recovery area to store your flashback logs and still meet your other backup retention requirements, flashback logs may be deleted from the recovery area to make room for other required files. In such situations you will still be able to use point-in-time recovery to revert your database to a previous state.
At any given time, the earliest point in time to which you can actually rewind your database by using Flashback Database can be determined by querying the V$FLASHBACK_DATABASE_LOG
view as shown in this example:
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
If the results of this query indicate that you cannot reach your intended flashback retention target, increase the size of your flash recovery area to accomodate more flashback logs than value indicated by V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE
. Also, when you are deciding whether to use Flashback Database instead of point-in-time recovery, this value will tell you whether you can reach your desired target time before you start the Flashback Database operation.
Maintaining flashback logs imposes comparatively limited overhead on an Oracle database instance. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.
To achieve good performance for large production databases with Flashback Database enabled, Oracle Corporation recommends the following:
LOG_BUFFER
to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.The overhead of turning on logging for Flashback Database depends on the read-write mix of the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)
The best way to monitor system usage due to flashback logging is to take performance statistics using the Oracle Statspack. For example, if you see "flashback buf free by RVWR
" as the top wait event, it indicates that Oracle cannot write flashback logs very quickly. In such a case, you may want to tune the file system and storage used by the flash recovery area, possibly using one of the methods described in "Performance Tuning for Flashback Database".
The V$FLASHBACK_DATABASE_STAT
view (described in Oracle Database Reference) shows the bytes of flashback data logged by the database. Each row in the view shows the statistics accumulated (typically over the course of an hour). The FLASHBACK_DATA
and REDO_DATA
columns describe bytes of flashback data and redo data written respectively during the time interval, while the DB_DATA
column describe bytes of data blocks read and written. Note that FLASHBACK_DATA
and REDO_DATA
correspond to sequential writes, while DB_DATA
corresponds to random reads and writes.
Because of the difference between sequential I/O and random I/O, a better indication of I/O overhead is the number of I/O operations issued for flashback logs. The following statistics in V$SYSSTAT
can tell you the number of I/O operations your instance has issued for various purposes:
See Oracle Database Reference for more details on columns in the V$SYSSTAT
view.
SQL> SELECT CURRENT_SCN FROM V$DATABASE; SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
rman TARGET /
FLASHBACK DATABASE
command to return the database to a prior TIME
, SCN
, or archived log SEQUENCE
number. If you configured sbt
channels, RMAN automatically restores archived logs from tape as needed during the Flashback Database operation. For example:
RMAN> FLASHBACK DATABASE TO SCN 46963; RMAN> FLASHBACK DATABASE TO SEQUENCE 5304; RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24); RMAN> FLASHBACK DATABASE TO TIME timestamp('2002-11-05 14:00:00'); RMAN> FLASHBACK DATABASE TO TIME to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
When the Flashback Database operation completes, you can evaluate the results by opening the database read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';
At this point you have several options:
ALTER DATABASE OPEN RESETLOGS
.
RMAN> ALTER DATABASE OPEN RESETLOGS
RECOVER DATABASE UNTIL
to bring the database forward, or perform FLASHBACK DATABASE
again with an SCN further in the past. You can completely undo the effects of your flashback operation by performing complete recovery of the database:
RMAN> RECOVER DATABASE;
RECOVER DATABASE
to return the database to the present time and re-import the data using the Oracle import utility that corresponds to the export utility you used.Note that, as with point-in-time recovery, you lose all updates to the database after the target SCN for the Flashback Database operation.
The FLASHBACK DATABASE
command in SQL*Plus takes essentially the same options and performs essentially the same behavior as FLASHBACK DATABASE
as performed in RMAN. The chief difference is that RMAN, being aware of backups of your database files, can restore from backup automatically any needed archived logs required for the Flashback Database process. When using FLASHBACK DATABASE
in SQL*Plus, all files required to complete the operation must already be present on disk.
The following scenario shows how one might use the various flashback features of Oracle (Oracle Flashback Query, Oracle Flashback Transaction Query, Oracle Flashback Table, and Oracle Flashback Database) to recover from a data loss due to a user or application error.
At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE table. This employee was present in the table at 14:00, when she last checked. This means that someone accidentally deleted "JOHN" from the table between 14:00 and 17:00. The HR administrator re-inserts the missing employee row into the EMPLOYEE table with the following use of Flashback Query:
INSERT INTO employee SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN';
She can find out more information about when "JOHN" was deleted, the transaction which deleted "JOHN", and the user who deleted "JOHN" by using Flashback Version Query and Flashback Transaction Query as follows:
SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY WHERE xid IN (SELECT versions_xid FROM employee VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS') and TO_TIMESTAMP('2003-04-04 17:00:00', 'YYYY-MM-DD HH:MI:SS') WHERE name = 'JOHN');
If at this time she discovers many other logical data errors in the EMPLOYEE table, she can recover the whole table to the state at 14:00 by using Flashback Table:
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');
Finally, if many other tables also contain errors due to transactions during the same interval, flashback database can return the entire database to its state before the errors:
FLASHBACK DATABASE TO TIME to_timestamp('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');
For more examples of using flashback features to recover from user errors, see Oracle High Availability Architecture and Best Practices.