ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于flashback能否闪回trunc、purge表的验证

关于flashback能否闪回trunc、purge表的验证

原创 Linux操作系统 作者:红叶DBA 时间:2011-11-22 21:20:25 0 删除 编辑
关于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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: RAC安装设置脚本
请登录后发表评论 登录
全部评论

注册时间:2010-08-19

  • 博文量
    54
  • 访问量
    69895