查看: 17617|回复: 25

[精华] 关于 undo tablespace 中的 flashback

[复制链接]
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
发表于 2003-4-26 17:29 | 显示全部楼层 |阅读模式
在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 的时候就有可能产生偏差


请关注实验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.

SQL>
论坛徽章:
23
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2003-4-26 17:32 | 显示全部楼层
学习。

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2003-4-26 17:35 | 显示全部楼层

其实我们可以看出

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

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

使用道具 举报

回复
论坛徽章:
23
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2003-4-26 17:36 | 显示全部楼层
那么这个差异是什么原因呢?

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2003-4-26 17:44 | 显示全部楼层

按照oracle的说法

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

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

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

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

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

使用道具 举报

回复
论坛徽章:
23
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2003-4-26 17:51 | 显示全部楼层
如果把fast_start_mttr_target integer,比如说改成120,再试验的结果呢?

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2003-4-26 17:56 | 显示全部楼层

这个任务交给你?

我仅仅是根据时间的巧合来做猜测而已

不知道具体是不是这样子的呢

改了这个参数也可能不发生变化,但这也不能说明跟这个因素无关,呵呵

不过能证明有关那自然更好了

使用道具 举报

回复
论坛徽章:
23
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2003-4-26 17:59 | 显示全部楼层
呵呵,好呀。

只是我现在手头没有9i,也没有多余的机器,可能暂时做不了。

如果有机会做了的话,我再贴出来。

使用道具 举报

回复
论坛徽章:
23
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:58马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2003-4-26 19:05 | 显示全部楼层
我有机会的话,也做一下,有结论就贴出来。

使用道具 举报

回复
论坛徽章:
86
ITPUB元老
日期:2005-02-28 12:57:002012新春纪念徽章
日期:2012-01-04 11:49:542012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20咸鸭蛋
日期:2012-05-08 10:27:19版主8段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24
 楼主| 发表于 2003-4-27 12:00 | 显示全部楼层

附上 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.

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表