|
|
View PDF |
|
|
View PDF |
Th is chapter describes how to use the flashback features of Oracle to retrieve lost data in data recovery scenarios. This chapter inclu des 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 t he capability to query past versions of schema objects, query historical data, analyze database changes, or perform self-service repa ir to recover from logical corruptions while the database is online.
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 primari ly for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the info rmation required to reconstruct data as it stood at a past time and examine the record of changes since that past time.
<
font face="Arial, Helvetica, sans-serif">See Also:
|
In a data recovery context, it is useful to b
e 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, i
f appropriate, re-insert the lost data in the database.
Querying the past state of the tabl
e is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves th
e state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:
SELECT * F ROM EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') < /a> WHERE name = 'JOHN';
Restoring John's information to the ta ble 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 D BA 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 recove ry operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, trigger s and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the co ntents 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 Or acle Database Administrator's Guide.
The prerequisites for performing a FLASHBACK TABLE operation are as follows:
FLASHBACK ANY TABLE system privilege or you must ha
ve the FLASHBACK object privilege on the table.INSERT, DELETE, and ALTER privileges on the table.FLASHBACK TABLE operation.FLASHBACK TABLE statement. You can enable row m
ovement with the following SQL statement:
ALTER TABLE table ENABLE RO W MOVEMENT;
The following SQL*Plus st
atement performs a FLASHBACK TABLE operation on the table employee:
< /a>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 d
atabase was at the time specified by the timestamp.
You can also specify the target point i
n time for the FLASHBACK TABLE operation using an SCN:
FLASHBACK TABLE employe e 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 retur
ns 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 <
code>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:
< a name="1032476">At 17:00 an HR administrator discovers that an employee "JOHN" is missing from the EMPLOYEE tabl e. 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','Y YYY-MM-DD HH:MI:SS') ENABLE TRIGGERS;
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. F
lashback Drop is substantially faster than other recovery mechanisms (such as point-in-time recovery) and also does not lead to any l
oss 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 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 depen
dent 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: 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 Ev
en if you do not purge objects from the recycle bin, the database purges objects from the recycle bin to meet tablespace space constr
aints. See "Recycle Bin Capacity and Space Pressure" for more d
etails. 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 To view only objects in the recycle bin, use the When a table and its dependent objects are moved to the recycle bin, they are assigned unique na
mes, 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 n
ames are formed as follows: where: The recycle bin name of an object is always 30 characters long. Note that the You can view the contents of the recycle bin using the SQL*Plus command The 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 dr
opped objects: 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:
What is the Recycle Bin?
How Tables and Other Objects Are Placed in the Rec
ycle Bin
SQL
> DROP TABLE EMPLOYEE_DEMO;
Table Dropped
PURGE statemen
t, as described in "Purging Objects from the Recycle Bin". If y
ou are sure that you will not want to recover a table later, you can drop it immediately and permanently, instead of placing it in th
e recycle bin, by using the PURGE option of the DROP TABLE statement, as shown in this example:DROP TABLE employee_demo PURGE;
USER_TABLES, ALL_TABLES, DBA_TABLES, 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 dro
pped.USER_RECYCLEBIN and
<
a name="1016981">
BIN$$globalUID$versi
on
globalUID is a globally unique, 24 character long identifier generated for the object.version is a version number assigned by the databaseglobalUID used in the recycle bin name is not readily correlated with any external
ly visible piece of information about the object or the database.<
font face="Arial, Helvetica, sans-serif" color="#330099">Viewing and Querying Objects in the Recycle Bin
<
a name="1016994">
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
ORIGINAL NAME column shows the orignal name of the object, whil
e 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.SQL> SELECT object_name as recycle_name, original_name, object_type
FROM recyclebin;
RECYCLE_NAME ORIGINAL_NAME
OBJECT_TYPE
-------------------------------- --------------------- -------------
<
/a>BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 EMPLOYEE_DEMO TABLE
BIN$JKS983293M1dsab4gsz/I249==$0 I_EMP_DEMO
INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0 LOB_EMP_DEMO LOB
BIN$JKJ399
SLKnaslkJSLK330SIK==$0 LOB_I_EMP_DEMO LOB INDEX
SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";
<
p class="BP">(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 previo usly).
There is no fixed amount of sp ace pre-allocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will re main in the recycle bin.
The rules that govern how long an object is retained in the recycl e 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 tables pace 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, bu t the user may have exhausted his or her quota on it.
Oracle never automatically reclaims s pace 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 autom atic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects droppe d are the first selected for purging.
Actual purging of objects is done only as needed to m eet ongoing space pressure, that is, the databases purges the minimum possible number of objects selected for purging to meet immedia te needs for space. This policy serves two purposes:
Dependent objects such as indexes on a table are selected for purgin g before the associated table (or other required segment).
If space pressure is due to an i ndividual user's quota on a tablespace being exhausted, the recycle bin purges objects belonging to the tablespace which count agains t that user's space quotas.
For AUTO EXTEND-able tablespaces, objects are purged from the r ecycle 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 segm ents associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the databas e 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 tempor ary 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 on e 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 eith
er 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 e
ntry 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 operati on:
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 ex
ample:
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 recy cle 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 EMP TO BEFORE DROP;
Th
e 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:
FLASHBA CK 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 c an 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 comman
d is used to permanently purge objects from the recycle bin. Once purged, objects can no longer be retrieved from the bin using Flash
back Drop.
There are a number of forms of the PURGE statement, depending on exactly which o bjects 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 s
tatement the first table dropped will be the one to be purged.
For example, consider the fo
llowing series of CREATE TABLE and DROP TABLE statements:
CREATE TABLE EMP; # version 1 of the table DROP TABLE EMP; # version 1 dropped CREATE TABL E EMP; # version 2 of the table DROP TABLE EMP; # version 2 dropped CREATE TABLE EM P; # 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 ta ble 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 rec
ently dropped version from the recycle bin.
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 sp ace pressure, so that dropped tables can remain in the recycle bin longer. If you retrieve a table from the recycle bin using Flashba ck Drop, you can rebuild the indexes after you retrieve the table.
You can use the PURGE TABLESPACE command to purge all dropped tables and oth
er 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 de pendent 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 y ou 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 immedi ately, 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 ta
blespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPAC
E.
When a user is dropped, any objects belonging to the user that are not in the rec ycle 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 y ou 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 relat ed to Flashback Drop and the recycle bin.
DROP
<
p>
Any user with drop privileges over the object can drop the object, placing it in the recy cle 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 obj ects from the recycle bin.
SELECT for objects in the Recycle
Bin
Users must have SELECT and FLASHBACK privileges over an o
bject 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 reclaime d due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
Oracle Flashback Database, access
ible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus (by means of the FLASHB
ACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user error
s.
It is similar to conventional point in time recovery in its effects, allowing you to ret urn a database to its state at a time in the recent past. Flashback Database is, however, much faster than point-in-time recovery, be cause 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 fla shback retention target, to specify how far back into the past you want to be able to restore your database with Flashback D atabase.
From that time on, at regular intervals, the database copies images of each altere d 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 content s 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 t ime 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.
Bec ause Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the follo wing limitations:
Finally, it is important to note that the flashback retention target is a target, not an absolute guarantee that Flashback Data base will be available. If your flash recovery area is not large enough to hold both required files such as archived redo logs and ot her backups, flashback logs may be deleted to make room in the flash recovery area for these required files. If you discover that Ora cle has discarded flashback logs required to reach your desired target time for Flashback Database, you can always use traditional po int-in-time recovery instead to achieve a similar result.
To enable Flashback Database, set the D
B_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK O
N statement. Follow the process outlined here.
SQ L> SELECT STATUS FROM V$INSTANCE;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARG ET=4320;
SQL> ALTER DATABASE FLASHBACK ON;
By default, flashback logs are generated for all permanent tablespaces. If you w ish, you can reduce overhead by disabling flashback logging specific tablespaces:
SQL> A LTER 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 tablespac
e, 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 Da tabase not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one t o perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Dat a Guard environment. See Oracle Data Guard Concep ts and Administration for details.
The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how m
uch 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 log
ging 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 y
ou 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 calcu
lated, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBAC
K_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 k ept 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 re tention 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 vi
ew as shown in this example:
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
If the results o
f this query indicate that you cannot reach your intended flashback retention target, increase the size of your flash recovery area t
o accomodate more flashback logs than value indicated by V$FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE. Also, when y
ou are deciding whether to use Flashback Database instead of point-in-time recovery, this value will tell you whether you can reach y
our desired target time before you start the Flashback Database operation.
The best way to monitor system usage due to flashback logging is to ta
ke performance statistics using the Oracle Statspack. For example, if you see "flashback buf free by RVWR" as the top wa
it 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$FLASHBAC
K_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.
Beca
use 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 issu
ed 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:
| Column Name | Column Meaning< /font> |
|---|---|
|
Physical write I/O request |
The number of write operations issued for writing data blocks |
| <
/a>
physical read I/O request |
The number of read o perations issued for reading data blocks |
|
redo writes |
The number of write oper ations issued for writing to the redo log. |
|
flashback log writes |
The number of write operations issued for writing to flashback logs. |
See Oracle Database Reference for more details on columns in the V$SYSSTAT view.
SQL> SELECT CURRENT_SCN FROM V$DATABASE; SQL> S ELECT 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, o
r archived log SEQUENCE number. If you configured sbt channels, RMAN automatically restores archived logs f
rom tape as needed during the Flashback Database operation. For example:
RMAN> FLASHBACK D ATABASE TO SCN 46963; RMAN> FLASHBACK DATABASE TO SEQUENCE 5304; RMAN> FLASHBACK DATABASE TO TIME (SYSDATE-1/24); RMAN> FL ASHBACK 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 d atabase read-only and run some queries to check whether your Flashback Database has returned the database to the desired state.
< pre class="CE">RMAN> SQL 'ALTER DATABASE OPEN READ ONLY'; < /a>At this point you have several options:
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 exp
ort 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 sam
e 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 fo
r the Flashback Database process. When using FLASHBACK DATABASE in SQL*Plus, all files required to complete the operatio
n must already be present on disk.
The following scenario shows how one might use the various flashback features of Oracle (Oracle Flashback Q uery, 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 a ccidentally 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 INT O 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 us er who deleted "JOHN" by using Flashback Version Query and Flashback Transaction Query as follows:
SELECT commit_timestamp , logon_user FROM FLASHBACK_TRANSACTION_QUERY WHER E 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', 'Y YYY-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_timesta mp('2003-04-04 14:00:00', 'YYYY-MM-DD HH:MI:SS');
For more exampl es of using flashback features to recover from user errors, see Oracle High Availability Architecture and Best Practices.
|
 Copyright © 2003 Oracle Corporation All Ri ghts Reserved. |
|
