闪回(关于闪回表)

时间:2021-04-27 19:47:23

--整理以前的学习笔记


闪回表是基于undo data的


SQL> conn emr3/emr3@112
已连接。
SQL> flashback table hl_binglimb to timestamp(to_date('2012-10-18 23:59:59','yyy
y-mm-dd hh24:mi:ss'));
flashback table hl_binglimb to timestamp(to_date('2012-10-18 23:59:59','yyyy-mm-
dd hh24:mi:ss'))
                *
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table hl_binglimb enable row movement;

表已更改。

SQL> flashback table hl_binglimb to timestamp(to_date('2012-10-18 23:59:59','yyyy-mm-dd hh24:mi:ss'));--格式有问题
flashback table hl_binglimb to timestamp(to_date('2012-10-18 23:59:59','yyyy-mm-dd hh24:mi:ss'))
                *
第 1 行出现错误:
ORA-08186: 指定的时间戳无效

SQL> flashback table hl_binglimb to timestamp (systimestamp-interval '30' minute);--最后通过这个解决

闪回完成。
具体格式再看下

e25494-Administrator's Guide.pdf(20-53)
e10642-Backup and Recovery User's Guide.pdf(18-4)


下面是我的具体实验(在查看了联机文档后)
在flashback table之前最好先查下当前的SCN,以防要恢复回来
SELECT CURRENT_SCN FROM   V$DATABASE;


确认当前的undo_retention参数,查看可以恢复到多久以前的数据
SQL> show parameter undo_retention --900秒(15分钟)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
不过这个参数是指undo data被保存的最短时间,实际可能比这个时间长
(which is the minimal time for which undo data is kept )


conn hr/hr
SQL> create table t(id number,name varchar2(10));
Table created.
SQL> insert into t values(1,'a');
1 row created.
SQL> insert into t values(2,'b');
1 row created.
SQL> insert into t values(3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    2188848
SQL> select sysdate from dual;
SYSDATE
---------
18-OCT-12
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2012-10-18 20:36:07
SQL> create restore point good_for_now;----针对当前时间或SCN创建还原点

create restore point good_for_now
*
ERROR at line 1:
ORA-01031: insufficient privileges


conn /as sysdba
grant  flashback any table to hr;
(
http://psoug.org/reference/restore_points.html
To create a normal restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege. To create a guaranteed restore

point, you must have the SYSDBA system privileges.To view or use a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY

TABLE system privilege or the SELECT_CATALOG_ROLE role.
)


conn hr/hr
SQL> create restore point good_for_now;
Restore point created.

SQL> truncate table t;--现在我对t执行破坏性操作
Table truncated.

SQL> select * from t;
no rows selected

SQL> select current_scn from v$database;--得到当前的SCN,以防恢复回来
CURRENT_SCN
-----------
    2189961


现在我开始flashback table,但是我在闪回之前,还要进行一个操作,就是允许行移动

Row movement must be enabled on the table, which indicates that rowids will 
change after the flashback occurs.--闪回前后rowid会发生变化

alter table t enable row movement;


下面开始闪回

SQL> flashback table t to SCN 2188848;
flashback table t to SCN 2188848
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


----现在就遇到了问题,由于我之前进行了truncate操作(DDL语句,所以我不能进行闪回了,见下面联机文档中的描述
The structure of the table must not have been changed between the current time 
and the target flash back time.
The following DDL operations change the structure of a table: upgrading, moving, 
or truncating a table; adding a constraint to a table, adding a table to a cluster; 
modifying or dropping a column; adding, dropping, merging, splitting, 
coalescing, or truncating a partition or subpartition (except adding a range 
partition))
更具体的请参考e10642-Backup and Recovery User's Guide.pdf(18-4)中有关flashback table的先决条件


下面重新来一遍:
SQL> select * from t;

no rows selected

SQL> insert into t values(1,'a');

1 row created.

SQL> insert into t values(2,'b');

1 row created.

SQL> insert into t values(3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2190214

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

TO_CHAR(SYSDATE,'YY
-------------------
2012-10-18 20:59:07

SQL> drop restore point good_for_now;

Restore point dropped.

SQL> create restore point good_for_now;

Restore point created.
SQL> delete from t;--破坏性操作

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t;

no rows selected

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2190448


下面闪回:
SQL> flashback table t to SCN 2190214;--为啥又出错呢?原来以前做实验的时候删除了一个临时文件,且所在的test目录没了,Oracle启动的时候没法自动

创建临时文件
flashback table t to SCN 2190214
                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u01/oradata/orcl/test/temp02.dbf'


解决办法:
我重新创建test目录,然后sys用户关闭数据库,重启startup,这样temp02.dbf就重新创建起来了(临时文件丢失了,可以在Oracle启动的时候自动创建回来,只

要所在的目录还在)


[oracle@mylinux ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 18 21:08:12 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> flashback table t to SCN 2190214;

Flashback complete.

SQL> select * from t;--闪回成功

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

SQL> flashback table t to SCN 2190448;--恢复回来

Flashback complete.

SQL> select * from t;

no rows selected

SQL> flashback table t to restore point good_for_now;--闪回成功

Flashback complete.

SQL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c

SQL> flashback table t to SCN 2190448;--恢复回来

Flashback complete.

SQL> select * from t;

no rows selected

SQL> flashback table t to timestamp to_timestamp('2012-10-18 20:59:07','yyyy-mm-dd hh24:mi:ss');--闪回成功

Flashback complete.

SQL> select * from t;

        ID NAME
---------- ----------
         1 a
         2 b
         3 c


--还有种格式是这样的,闪回15分钟前的

flashback table t to timestamp (systimestamp-interval '15' minute);

原文链接:http://blog.itpub.net/26524307/viewspace-1061407/