关于flashback能否闪回trunc、purge表的验证
近日，在Q群中，有网友说flashback无法闪回purge之后的表，我觉得不信，于是做一下验证，
顺便测试一下truncate表的flashback。
环境：RHEL4.4 + RAC 10.2.0.1
别名 alias sqldb='export ORACLE_SID=DB1;rlwrap sqlplus / as sysdba'
本次实验时间线：
SCN(1278840) --> truncate --> SCN(1278918) --> purge --> SCN(1278954)
[oracle@node1 ~]$ sqldb
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 20:19:06 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SYS:153@DB.DB1 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:153@DB.DB1 > startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 83888372 bytes
Database Buffers 79691776 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS:152@DB.DB1 > alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance.
注解：RAC环境下，本实例shutdown，但是还有另一个节点以read write模式操作数据库，
需要在另一节点执行 shutdown 之后，再进行以下的实验步骤。
关闭另一节点之后的数据库状态如下：
SYS:152@DB.DB1 > select open_mode from gv$database;
OPEN_MODE
----------
MOUNTED
SYS:152@DB.DB1 > alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
注解：flashback 必须要启用介质恢复，也就是启动archive mode
使用如下命令启用 archive mode
SYS:152@DB.DB1 > alter database archivelog;
Database altered.
SYS:152@DB.DB1 > alter database flashback on;
Database altered.
SYS:152@DB.DB1 > alter database open
2 ;
Database altered.
SYS:152@DB.DB1 > exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
启用之后的集群状态，后续实验中，一直保持DB2实例关闭状态。
[oracle@node1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....B1.inst application ONLINE ONLINE node1
ora....B2.inst application OFFLINE OFFLINE
ora.DB.db application ONLINE ONLINE node1
ora....SM1.asm application ONLINE ONLINE node1
ora....E1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application ONLINE ONLINE node1
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip application ONLINE ONLINE node1
ora....SM2.asm application ONLINE ONLINE node2
ora....E2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application ONLINE ONLINE node2
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip application ONLINE ONLINE node2
[oracle@node1 ~]$ sqldb
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 20:32:32 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SYS:152@DB.DB1 > conn hongye/hongye
Connected.
HONGYE:152@DB.DB1 > select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
HONGYE:152@DB.DB1 > create table t_trunc as select rownum id,'name'||rownum name from dba_users where rownum<10;
Table created.
HONGYE:152@DB.DB1 > select * from t_trunc;
ID NAME
---------- --------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
9 rows selected.
HONGYE:152@DB.DB1 > create table t_purge as select * from t_trunc;
Table created.
HONGYE:152@DB.DB1 > select * from t_purge;
ID NAME
---------- --------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
9 rows selected.
获取第一个 SCN，完整数据时的SCN
HONGYE:152@DB.DB1 > select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1278840
HONGYE:152@DB.DB1 > truncate table t_trunc;
Table truncated.
获取第二个 SCN，已经truncate，但是没有drop ... purge
HONGYE:152@DB.DB1 > select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1278918
HONGYE:152@DB.DB1 > drop table t_purge purge;
Table dropped.
获取第三个 SCN，已经truncate，也drop ... purge了
HONGYE:152@DB.DB1 > select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1278954
HONGYE:152@DB.DB1 > shutdown immediate;
ORA-01031: insufficient privileges
HONGYE:152@DB.DB1 > conn /as sysdba
Connected.
SYS:152@DB.DB1 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
下面进行恢复实验：
本次实验时间线：
SCN(1278840) --> truncate --> SCN(1278918) --> purge --> SCN(1278954)
第一次 flashback，恢复到第三个 SCN，已经truncate和purge，此时理论上数据是无法恢复的。
SYS:152@DB.DB1 > startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS:152@DB.DB1 > flashback database to scn 1278954;
flashback database to scn 1278954
*
ERROR at line 1:
ORA-38748: cannot flashback data file 1 - file is in use or recovery
ORA-01110: data file 1: '+DG_DATA/db/datafile/system.262.767181411'
注解：如果出现此错误，说明另一个节点在操作数据库，需要shutdown。
SYS:152@DB.DB1 > flashback database to scn 1278954;
Flashback complete.
以 read only 方式打开数据库，以便发现恢复时间点不对时，重新恢复。
SYS:152@DB.DB1 > alter database open read only;
Database altered.
SYS:152@DB.DB1 > conn hongye/hongye
Connected.
HONGYE:152@DB.DB1 > select * from t_purge;
select * from t_purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
HONGYE:152@DB.DB1 > select * from t_trunc;
no rows selected
经过查询，实际结果与理论上的结果一致，继续进行下面的实验。
第二次 flashback，恢复到第二个 SCN，已经truncate，但还没有purge，
此时理论上t_trunc数据是无法恢复的，而t_purge数据可以恢复。
HONGYE:152@DB.DB1 > conn /as sysdba
Connected.
SYS:152@DB.DB1 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:152@DB.DB1 > startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS:152@DB.DB1 > flashback database to scn 1278918;
Flashback complete.
SYS:152@DB.DB1 > alter database open read only;
Database altered.
SYS:152@DB.DB1 > select * from hongye.t_trunc;
no rows selected
SYS:152@DB.DB1 > select * from hongye.t_purge;
ID NAME
---------- --------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
9 rows selected.
经过查询，实际结果与理论上的结果一致，继续进行下面的实验。
第三次 flashback，恢复到第一个 SCN，已经truncate，但还没有purge，
此时理论上t_trunc和t_purge数据都是可以恢复。
SYS:152@DB.DB1 > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:152@DB.DB1 > startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS:152@DB.DB1 > flashback database to scn 1278840;
Flashback complete.
SYS:152@DB.DB1 > alter database open read only;
Database altered.
SYS:152@DB.DB1 > select * from hongye.t_purge;
ID NAME
---------- --------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
9 rows selected.
SYS:152@DB.DB1 > select * from hongye.t_trunc;
ID NAME
---------- --------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6
7 name7
8 name8
9 name9
9 rows selected.
经过查询，实际结果与理论上的结果一致，继续进行下面的实验。
疑问：现在已经恢复到 SCN = 1278840，是否可以恢复到 SCN = 1278954 呢？
也就是能不能在时间线上往后进行 flashback 呢？
尝试恢复到 SCN = 1278954
SYS:152@DB.DB1 > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:152@DB.DB1 > startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SYS:152@DB.DB1 > flashback database to scn 1278954;
Flashback complete.
SYS:152@DB.DB1 > alter database open read only;
Database altered.
SYS:152@DB.DB1 > select * from hongye.t_trunc;
no rows selected
SYS:152@DB.DB1 > select * from hongye.t_purge;
select * from hongye.t_purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
进过查询，是可以往后进行flashback的，此时，数据库又恢复到类似没有进行flashback的状态了。
由于数据是实验数据，故不再进行恢复了，直接以resetlogs方式打开数据库了。
SYS:152@DB.DB1 > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS:152@DB.DB1 > startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
注解：flashback 之后，如果确定恢复到正确的状态了，那么必须使用 resetlogs 方式打开数据库。
SYS:152@DB.DB1 > alter database open resetlogs;
Database altered.
SYS:152@DB.DB1 >
实验证明：flashback是可以恢复 truncate 和 purge 的表的。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24465008/viewspace-711796/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24465008/viewspace-711796/
扫一扫
500
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
