flashback query

转贴一下，是Fenng 在这篇文章的另外一个帖子里提供的关于flashback的资料：

关于 undo tablespace 中的 flashback

在9i中，若使用undo tablespace则oracle自动管理回滚段，通过设置 undo_retention 可在undo tablespace中保留这么长时间的数据，这样可以避免 snapshot  too old 错误，同时也可以通过 flashback 而得到某个时间点之前的数据，但这里要强调的几点是：

1：  flashback 功能不支持DDL语句，如果已经truncate掉的数据是不能找回来的
2： 看起来是这样的
oracle 每隔5分钟会将产生的 SCN 对应一个 TIME 做记录
也就是说通常只记录了SCN,但是每5分钟会记录 SCN and TIME
当采用 time 来做flashback 的时候就有可能产生偏差
 关于 5分钟之内的问题
前面说了一点，是跟这里面的记录有关
SQL> desc sys.smon_scn_time
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
THREAD                                             NUMBER
TIME_MP                                            NUMBER
TIME_DP                                            DATE
SCN_WRP                                            NUMBER
SCN_BAS                                            NUMBER

看来SMON 大约每5分钟左右 被唤醒一次
对数据库进行一些整理，可能其中涉及到  undo  tablespaceSMON是5分钟左右激一次，
SCN是随时都在变化的，一般来说SCN是连续的。
如何查出某个时间段之间所有SCN？
请关注实验4 ！！！！

实验1： 使用flashback 功能

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                36501397

SQL> insert into tf values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_flashback.enable_at_system_change_number(36501397);

PL/SQL procedure successfully completed.

SQL> select * from tf;

         A
----------
         1
         2


实验2： flashback 不支持 DDL

接着上面的步骤
SQL> truncate table tf;

Table truncated.

SQL>  exec dbms_flashback.enable_at_system_change_number(36501397);

PL/SQL procedure successfully completed.

SQL>  select * from tf;
select * from tf
               *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>  exec dbms_flashback.disable;


实验3 ：  我们创建一个表，立即看看效果，所有过程在5分钟以内，很段的时间内完成的,我们将无法查询数据！

SQL> drop table tf;

Table dropped.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                36509390

SQL> create table tf( a number);

Table created.

SQL> insert into tf values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                36509490

SQL> insert into tf values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec  dbms_flashback.enable_at_system_change_number(36509390);

PL/SQL procedure successfully completed.

SQL> select * from tf;
select * from tf
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.

SQL>  exec  dbms_flashback.enable_at_system_change_number(36509490);

PL/SQL procedure successfully completed.

SQL> select * from tf;
select * from tf
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.


实验四：通过时间来做,我们仔细看下面的时间和数据的关系！

SQL>  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:04    创建表之前的时间  time1

SQL> create table test1 (a number);

Table created.

SQL>  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:20   创建表之后还没有插入数据的时间 time2

SQL> insert into test1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:09:43    创建表之后5分钟内插入了1条记录 time3

SQL> insert into test1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_lock.sleep(300);   休眠5分钟


PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2003-04-26 17:15:55   休眠后再插入数据 time4


SQL> insert into test1 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:04','yyyy-mm-dd hh24:mi:ss'));    time1

PL/SQL procedure successfully completed.

SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.
SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:20','yyyy-mm-dd hh24:mi:ss'));   time2

PL/SQL procedure successfully completed.

SQL> select * from test1;

no rows selected

SQL>  exec  dbms_flashback.disable;


SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:09:43','yyyy-mm-dd hh24:mi:ss'));   time3

PL/SQL procedure successfully completed.

SQL> select * from test1;

no rows selected

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL>  exec  dbms_flashback.enable_at_time(to_date('2003-04-26 17:15:55','yyyy-mm-dd hh24:mi:ss'));   time4

PL/SQL procedure successfully completed.

SQL> select * from test1;

         A
----------
         1
         2

SQL>  exec  dbms_flashback.disable;

PL/SQL procedure successfully completed.

其实我们可以看出

使用 SCN 和 TIME 对于 5分钟以内创建的表的 提示信息是有差异的！

SCN 会提示 ORA-01466: unable to read data - table definition has changed
而 time 则提示 no rows selected

按照oracle的说法

time 使用的时候，oracle 是 choose a  该指定时间的5分钟以内的某个 scn   
使用 scn 的时候，是精确的版本控制

通过两个描述的信息来看： 应该是 time 是不精确控制

但为什么两者产生这个信息描述的差异？time看来是认为表存在但无数据
scn 认为是 5分钟内创建的表根本就不存在

总之来说呢，SCN 再精确，对于5分钟之内创建的表也无能为力

至于为什么会这样，我想可能有深层次的原因，是不是跟
fast_start_mttr_target               integer 300   有关?

附上 oracle document 一段文字

Flashback Query
Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).

Flashback query uses Oracle's multiversion read-consistency capabilities to restore data by applying undo as needed. Administrators can configure undo retention by simply specifying how long undo should be kept in the database. Using flashback query, a user can query the database as it existed this morning, yesterday, or last week. 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.

You set the date and time you want to view. Then, any SQL query you execute 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.

With the AS OF SQL clause, you 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.

For example, 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 subqueries. Operations such as INSERT TABLE AS SELECT and CREATE TABLE AS SELECT can be used with table decoration in the subqueries 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.

See Also:
Oracle9i SQL Reference for information on the AS OF clause


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 ther 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 a 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 permitted while an object is being 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. You can take the output from a flashback query and perform an undo yourself in many circumstances.
Flashback query does not tell you what changed. LogMiner does that.
Flashback query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. You can in theory 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 you 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.

--------------------------------------------------------------------------------


Some Uses of 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 enviornments, it could be used for extraction of data as of a consistent point in time from OLTP systems.

在 exp 中应用 flashback

exp  user/pass  .......    flashback_scn = SCN


exp  user/pass .......    flashback_time = '时间'
时间 格式可以通过 NLS_DATE_FORMAT 环境变量随意设置