闪回（关于闪回表）

最新推荐文章于 2022-07-17 19:43:49 发布

conglu8291

最新推荐文章于 2022-07-17 19:43:49 发布

阅读量241

点赞数

文章标签：数据库

--整理以前的学习笔记

闪回表是基于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

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