Oracle10g 中的flashback

 

http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm

http://download-west.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

http://www.oracleadvice.com/10g/10g_flashback.htm

Oracle 10g 中的flashback的功能太强大了。

Flashback Features

The flashback suite of features enables fast recovery from logical or user errors. Flashback is not for restoring or recovering the database from physical or environmental failures such as a missing datafile or a crashed disk. Typical applications of flashback features are to query an older version of a table after commiting a DML statement or to ‘undrop’ a table. The flashback database feature will be indispensable in development and testing environments.

As most of the flashback capability relies on UNDO information, it is necessary to be able to guarantee that UNDO is retained. As per 9i, Oracle10g does not automatically guarantee UNDO retention. The undo_retention parameter is still just a target, not a limit. If you expect to make use of the flashback features you must closely monitor space usage of the UNDO tablespace or issue the following command:

[ CREATE | ALTER ] TABLESPACE <undots> RETENTION GUARANTEE;


This table summarizes the various flashback capabilities in Oracle10g

Flashback Feature

init.ora parameter

Level

Description

Database

db_flashback_retention_target

Database

Flashback logs stored in flash recovery area

Drop (Recycle Bin)

N/A

Table

Use a virtual recycle bin to retrieve and query dropped tables and indexes

Table

undo_retention

Table

Use UNDO segments to reconstruct previous versions of a table

Query Versions

undo_retention

Row

Use UNDO segments to reconstruct previous versions of row(s)

Transaction Query

undo_retention

Transaction

Use UNDO segments to reconstruct previous versions of row(s) and mine the SQL that changed them

Time Mapping Granularity
Oracle internally maps SCNs to clock times. This mapping information is accurate to 3 seconds and can go back as far as the undo_retention parameter. It is useful to know of 2 functions that perform the conversion:

SCN_TO_TIMESTAMP( <scn> );

TIMESTAMP_TO_SCN( <timestamp> );


Navigate to the OEM page
OEM Database Control -> Maintenance -> Backup/Recovery -> Configure Recovery Settings



Flashback Database

Some interesting new commands are available for use but the database must be mounted, not open:
SQL> FLASHBACK DATABASE TO SCN <scn>;

SQL> FLASHBACK DATABASE TO BEFORE SCN <scn>;

SQL> FLASHBACK DATABASE TO TIMESTAMP <timestamp>;
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP <timestamp>;

SQL> FLASHBACK DATABASE TO SEQUENCE <log sequence number>;

You must open the database with a RESETLOGS after a flashback.

Some rules and restrictions for using the flashback database command:
1. A flash recovery area must have been configured to store flashback database logs. The time taken to do a flashback depends on the number of system changes, not the size of the DB.
2. You must have archived redo logs going all the way back to the time you are flashing back to. If you flashback too far, use the archived redo logs to roll forward as per a regular recovery.
4. The physical database must be intact (ie. no missing datafiles). Flashback cannot be applied to datafiles that have shrank, extended or been dropped.
5. You cannot flashback to a point before a restored or recreated controlfile
6. You cannot flashback to a point before an open resetlogs

A new background process RVWR (Recovery Writer) is responsible for writing the contents of a flashback buffer to flashback logs in the flash recovery area (db_recovery_file_dest). The flashback buffer will be at least twice the size of the log_buffer and contains images of altered datafile blocks.


Configuring Flashback Database

db_flashback_retention_target
init.ora parameter : the period in minutes to retain flashback logs. Whether or not this can be achieved depends on the size of the flash recovery area. Oracle will automatically remove flashback database logs if the flash recovery area comes under space pressure and there is a danger that there will be no room to write archived redo logs there.

o Set init.ora parameter db_flashback_retention_target to a time in minutes.
o Database must be in ARCHIVELOG mode and MOUNTED, not open
o Issue command ALTER DATABASE FLASHBACK ON;

ALTER DATABASE FLASHBACK OFF automatically deletes flashback logs from the flash recovery area.


Flashback can be turned off for individual tablespaces, so long as they are OFFLINE.

SQL> ALTER TABLESPACE <tablespace> FLASHBACK [ ON | OFF ];


Dictionary Views

V$FLASHBACK_DATABASE_LOGUse to size the flashback area based on a given value for db_flashback_retention_targetRETENTION_TARGET

Target retention time in minutes db_flashback_retention_target

ESTIMATED_FLASHBACK_SIZEBased on DML changes since instance startup or most recent db_flashback_retention_target interval (whichever is shortest)FLASHBACK_SIZECurrent size in bytes of flashback data in the flash recovery area

OLDEST_FLASHBACK_SCN

OLDEST_FLASHBACK_TIMESTAMP

How far it is actually possible to flashback to

V$FLASHBACK_DATABASE_STATS

Use to estimate a realistic db_flashback_retention_target based on a given flashback area size

BEGIN_TIME

END_TIME

Hourly intervals up to the previous 24 hours

FLASHBACK_DATA

Bytes of flashback data written during the interval

DB_DATA

Bytes of database data read/written during the interval

REDO_DATA

Bytes of redo data written during the interval

ESTIMATED_FLASHBACK_SIZE

Based on DML changes for the period

Flashback Drop (Recycle Bin)

A change in default behaviour of the DROP command has been made to support a new logical structure called the recycle bin. When a DROP TABLE command is issued, Oracle no longer releases the space used by the table. Instead, the table is renamed to a system-generated 30 character unique name and all it’s extents are preserved exactly as they are in the tablespace. Oracle will only reclaim the used space when the tablespace comes under space pressure.

Some restrictions apply in that tables must reside in a non-SYSTEM tablespace that is locally managed, however dependent objects (Eg. Indexes, IOT overflow segments) can reside in dictionary managed tablespaces. Tables with Fine-Grained Access control policies cannot be saved. Likewise tables with MVIEW logs, referential integrity constraints and bitmap join indexes cannot be saved.

Dropped tables can be queried by referring to them by the 30-char name. Note that once a table has been dropped it can only be SELECTed from. No DML is allowed on dropped tables. All dependent indexes, triggers and constraints are also renamed to system-generated names. I have noticed that these names are preserved when the table is subsequently ‘undropped’ so you must remember to name them back manually.

You can view the contents of the recycle bin by looking in DBA_RECYCLEBIN or USER_RECYCLEBIN. Both are based on the RECYCLEBIN$ table. The views show all dropped objects, provided they have not yet been purged.

SQL> show recyclebin
This is a nice shortcut from within SQL*Plus. It only shows objects that can be undropped (where can_undrop=’YES’).

The recycle bin is for recovering dropped tables only. To undo a truncate table command, use either flashback database or traditional point in time recovery.


Flashback Drop and related commands

SQL> FLASHBACK TABLE <table> TO BEFORE DROP;
SQL> FLASHBACK TABLE <table> TO BEFORE DROP RENAME TO <a different table name>;

The table is removed from the recycle bin (actually just renamed back to it’s original name or the one specified). Several versions of dropped tables are possible, for example if you continually flashback and drop tables, multiple versions will be created. The flashback table command undrops the most recent version. Repeat the flashback table to go back to previous versions.


SQL> DROP TABLE <table> PURGE;

The PURGE option bypasses the recycle bin and behaves in the same way as previous database versions. Oracle will automatically purge dropped tables from the recycle bin, oldest first when space is running low in the tablespace.


SQL> PURGE [ TABLE | INDEX ] ;

The purge command removes the segment from the recycle bin and releases the extents back to the tablespace. If there is more than one version, then only the oldest version is purged.


SQL> PURGE TABLESPACE [ USER <username> ] <tablespace>;

Purges all dropped objects in the tablespace, optionally belonging to a named user. Dependent objects in other tablespaces are also dropped permanently.


SQL> DROP TABLESPACE <tablespace>;

Purges objects from the recycle bin for that tablespace. As per previous versions, it will fail if there are segments in the tablespace.


SQL> DROP TABLESPACE <tablespace> INCLUDING CONTENTS;

Drops and purges the objects in the recycle bin for that tablespace.


SQL> DROP USER <username> CASCADE;

Drops and purges all objects owned by the user.



Flashback Table

To reconstruct a table to a specific point in time or SCN in the past use the FLASHBACK TABLE command.

SQL> FLASHBACK TABLE <table> TO SCN <scn> [ ENABLE TRIGGERS ];
SQL> FLASHBACK TABLE <table> TO TIMESTAMP <timestamp> [ ENABLE TRIGGERS ];

As well as the usual ALTER TABLE, SELECT, INSERT, UPDATE & DELETE privileges you will also need FLASHBACK privilege. In addition, ensure that row movement is enabled for the table. During the course of a flashback table, Oracle acquires exclusive DML locks on the table and disables any triggers.

You can also flashback to the future, well as far as the present at least.

Note the following restrictions:
o You cannot flashback SYSTEM or remote tables
o You cannot flashback past DDL changes such as add column
o You cannot flashback past changes to PCTFREE, INITRANS or MAXTRANS
o You cannot flashback past table shrinks
o Flashback table is an atomic operation. It completes in a single transaction. If multiple tables are specified, Oracle will either do them all or none.
o Oracle will abandon the flashback if there are any constraint violations
o Optimizer statistics are unaffected so re-gather stats if the flashback position renders the current stats stale

Set undo_retention to a suitable value and ensure that undo retention is guaranteed on the UNDO tablespace.



Flashback Query

Oracle introduced the flashback query in release 9i. It uses read-consistent data held in UNDO segments to reconstruct previous versions of table rows. The flashback query features in 10g extend this capability by being able to browse versions of the row, giving an audit trail of all DML changes to the row. In addition to this, Flashback Transaction Query can reveal the actual SQL that changed the row and even the SQL to undo the change in a similar manner to Logminer.

Flashback Query (9i, 10g) select … as of [ <timestamp> | <scn> ]
Flashback Versions (10g) select … versions as of [ <timestamp> | <scn> ]
select … versions between <timestamp> and <timestamp>;
select … versions between <scn> and <scn>;
Flasback Transaction Query (10g) FLASHBACK_TRANSACTION_QUERY view

SQL> SELECT * FROM <table> AS OF SCN 123456;
SQL> SELECT * FROM <table> AS OF TIMESTAMP SCN_TO_TIMESTAMP( 123456 );

Remember the functions SCN_TO_TIMESTAMP & TIMESTAMP_TO_SCN, they can be useful here.



Flashback Versions

Retrieve commited versions of a table’s rows between two given time points

SQL> SELECT * FROM <table> VERSIONS AS OF SCN 123456 AND 123999;
SQL> SELECT * FROM <table> VERSIONS BETWEEN SCN 123456 AND 123999;
SQL> SELECT * FROM <table> VERSIONS; -- as of current SCN

6 pseudo columns prefixed with VERSIONS_ are returned along with the table rows. There will be one row returned for each row version made during the specified time period or from the current SCN all the way back as far as undo_retention if used with no arguments. The columns are:
VERSIONS_STARTSCN SCN when row was first created. NULL if created before lower bound.
VERSIONS_STARTTIME Timestamp when row was first created
VERSIONS_ENDSCN SCN when row expired. NULL if the row is current or if the row was deleted
VERSIONS_ENDTIME Timestamp when row expired
VERSIONS_OPERATION I, D or U - insert, delete, update
VERSIONS_XID A unique transaction ID. This can be joined to the XID column on the new FLASHBACK_TRANSACTIONS_QUERY view to get the SQL to undo the change as well as other useful information.

The restrictions that apply to flashback table apply here also.



Flashback Transaction Query

A new view, FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database. In this respect it is similar to Logminer.

o Retrieve SQL code that caused unwanted changes
o Retrieve SQL code to undo unwanted changes
o Must have SELECT ANY TRANSACTION privilege
o Only retrieves committed transactions
o Use supplemental logging (as per Logminer) to retrieve names for objects that have been dropped.

（附：

对于truncate table 可以通过flashback exp 来恢复；
对于drop table 可以通过 flashback drop来恢复；
而对于insert, delete,update假如是最后一个事务的话，可以通过falshback table或者flashback query就可以解决问题；
而假如是中间事务的话，那么我们可以利用flashback transaction query，找到相应的回滚sql来回滚这个错误的事务。）