ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 之 闪回操作

Oracle 之 闪回操作

原创 Linux操作系统 作者:shilei1 时间:2012-06-24 05:43:28 0 删除 编辑

转自lideguo1979专栏 

闪回(Flashback)

SQL> /*

SQL> 错误操作:

SQL> 1. 错误的delete一条记录,并且commit

SQL> 2. 错误地删除了一个表: drop table

SQL> 3. 查询某个表的历史记录(所有已经提交了的历史记录)

SQL> 4. 错误地执行了一个事务

SQL>

SQL> 对应闪回的四种类型:

SQL> 1. 闪回表

SQL> 2. 闪回删除

SQL> 3. 闪回版本查询

SQL> 4. 闪回事务查询

SQL> 5. 闪回数据库

SQL> */

SQL> host cls

 

SQL> --闪回的时间参数

SQL> conn / as sysdba

已连接。

SQL> show user

USER 为 "SYS"

SQL> show parameters undo;

 

l闪回表,实际上是将表中的数据快速恢复到过去的一个是焦点或者系统改变号SCN上。实现表的闪回,需要使用到与撤销表空间相关的undo信息,通过show parameter undo命令可以了解这些信息。

l

l用户对表数据的修改操作,都记录在撤销表空间中,这为表的闪回提供了数据恢复的基础。例如,某个修改操作在提交后被记录在撤销表空间中,保留时间为900秒,用户可以在这900秒的时间内对表进行闪回操作,从而将表中的数据恢复到修改之前的状态。

 

 

NAME                                 TYPE        VALUE                         

undo_management                      string     AUTO                          

undo_retention                       integer    900                           

undo_tablespace                      string     UNDOTBS1                      

SQL> -- 将900秒改为20分钟

SQL> alter system set undo_retention=1200 scope=both;

 

系统已更改。

 

SQL> show parameters undo;

 

NAME                                TYPE        VALUE                         

undo_management                      string     AUTO                          

undo_retention                       integer    1200                          

undo_tablespace                      string     UNDOTBS1                      

SQL> /*

SQL> scope的取值: session spfile(参数文件) both

SQL> */

SQL> --为scott授予闪回的权限

SQL> grant flashback any table to scott;

 

授权成功。

 

SQL> conn scott/tiger

已连接。

SQL> show user

USER 为 "SCOTT"

SQL> host cls

 

SQL> --第一种类型闪回:闪回表

 

SQL> create table flashback_table1

 2  (fid number,fnamevarchar2(10));

 

表已创建。

 

SQL> insert into flashback_table1 values(&fid,'&fname');

输入 fid 的值:  1

输入 fname 的值:  Tom

 

 

SQL> commit;

 

提交完成。

 

SQL> select * from flashback_table1;

 

      FID FNAME                                                                

        1 Tom                                                                 

        2 Mary                                                                

        3 Mike                                                                

 

SQL> --记录当前的系统时间(SCN)

SQL> select sysdate 时间, timestamp_to_scn(sysdate) SCN fromdual;

 

时间                  SCN                                                      

21-9月 -11        4354761                                                      

 

SQL> delete from flashback_table1 wherefid=2;

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from flashback_table1;

 

      FID FNAME                                                               

---------- ----------                                                          

        1 Tom                                                                 

        3 Mike                                                                

 

SQL> --执行闪回表

SQL> flashback table flashback_table1 toscn 4354761;

flashback table flashback_table1 to scn4354761

                *

第 1 行出现错误:

ORA-08189: 因为未启用行移动功能, 不能闪回表

 

 

SQL> select rowid,fid,fname fromflashback_table1;

 

ROWID                     FID FNAME                                            

------------------ ---------- ----------                                       

AAANh7AAEAAAAGnAAA          1 Tom                                              

AAANh7AAEAAAAGnAAC          3 Mike                                             

 

SQL> --开启行移动功能

SQL> alter table flashback_table1 enable row movement;

 

表已更改。

 

SQL> flashback table flashback_table1 to scn 4354761;

 

闪回完成。

 

SQL> select rowid,fid,fname fromflashback_table1;

 

ROWID                     FID FNAME                                            

------------------ --------------------                                       

AAANh7AAEAAAAGnAAB          1 Tom                                              

AAANh7AAEAAAAGnAAD          2 Mary                                             

AAANh7AAEAAAAGnAAE          3 Mike                                              

闪回表语法:

 

FLASHBACK TABLE [schema.]

TO 

{[BEFORE DROP [RENAME TO table]] 

[SCN|TIMESTAMP]expr

[ENABLE|DISABLE]TRIGGERS}

 

lschema:模式名,一般为用户名。

lTO TIMESTAMP:系统邮戳,包含年、月、日、时、分、秒。

lTO SCN:系统更改号,

lENABLE TRIGGERS:表示触发器恢复以后为enable状态,而默认为disable状态。

lTO BEFORE DROP:表示恢复到删除之前。

lRENAME TO table:表示更换表名。

 

SQL> --闪回表的思想:将表回退到过去的一个时间上

SQL> --问题:不知道过去时间,怎么办?

SQL> host cls

 

SQL> --闪回删除

SQL> --Oracle的回收站

SQL> select * from tab;

 

TNAME                          TABTYPE CLUSTERID                              

------------------------------ -----------------                              

DEPT                           TABLE                                           

EMP                            TABLE                                           

BONUS                          TABLE                                           

SALGRADE                       TABLE                                           

FLASHBACK_TABLE                TABLE                                           

FLASHBACK_TABLE1               TABLE                                           

SYS_TEMP_FBT                   TABLE                                           

 

已选择7行。

 

 

删除表。

drop table FLASHBACK_TABLE;

查看回收站

show recyclebin;

清空回收站

purge recyclebin;

彻底删除

drop table FLASHBACK_TABLE1 purge;

 

回收站只对普通用户有效

删除表

drop table test123;

show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME            OBJECT TYPE  DROP TIME    

TEST123        BIN$w6NYaQ1jSZ6uzO2jUQ/ALw==$0 TABLE   2011-09-21:10:35:32

SQL> --闪回删除:从回收站中取回表

SQL> flashback table test123 to before drop;

 

闪回完成。

 

SQL> --通过回收站中的名称闪回删除

 

SQL> flashback table"BIN$9JMrb6kbRCON287lDV+5dA==$0" to before drop;

 

闪回完成。

 

SQL> host cls

闪回删除:回收站(recyclebin)简介

 

l回收站是所有被删除对象及其相依对象的逻辑存储容器,例如当一个表被删除(drop)时,该表及其相依对象并不会马上被数据库彻底删除,而是被保存到回收站中。

l

l回收站将用户执行的drop操作记录在一个系统表中,也就是将被删除的对象写到一个数据字典中。如果确定不再需要该对象,可以使用purge命令对回收站进行清空。

l

l被删除的对象的名字可能是相同的,例如用户创建了一个test表,使用drop命令删除该表后,又创建了一个test表,这时,如果再次删除该表就会导致向回收站中添加了两个相同的表。

l

回收站中对象的命名规则

 

l为了确保添加到回收站中的对象的名称都是唯一的,系统会对这些保存到回收站中的对象进行重命名,重命名的格式如下:

l

       BIN$globalUID$version

l

•其中: BIN表示RECYCLEBIN;globalUID是一个全局唯一的、24个字节长的对象,该标识与原对象名没有任何关系;version指数据库分配的版本号。

 

SQL> -- 闪回重名的表

SQL> select * from tab;

 

TNAME                          TABTYPE CLUSTERID                              

------------------------------ -----------------                               

DEPT                           TABLE                                           

EMP                            TABLE                                           

BONUS                          TABLE                                            

SALGRADE                       TABLE                                           

TEST123                        TABLE                                           

SYS_TEMP_FBT                   TABLE                                            

 

已选择6行。

 

SQL> drop table TEST123;

 

表已删除。

 

SQL> select * from tab;

 

TNAME                          TABTYPE CLUSTERID                              

------------------------------ -----------------                              

DEPT                           TABLE                                           

EMP                            TABLE                                           

BONUS                          TABLE                                           

SALGRADE                       TABLE                                           

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE                                           

SYS_TEMP_FBT                   TABLE                                           

 

已选择6行。

 

SQL> create table test123(tid number);

 

表已创建。

 

SQL> insert into test123 values(1);

 

已创建 1 行。

 

SQL> insert into test123 values(2);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> drop table test123;

 

表已删除。

 

SQL> --查询回收站:应该有两个重名的test123

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME               OBJECT TYPE  DROP TIME         

---------------------------------------------- ------------

TEST123          BIN$HDhYGEaXTayAnETFAUoaNw==$0 TABLE        2011-09-21:10:41:59

TEST123          BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE        2011-09-21:10:41:17

SQL> --闪回这两个表

SQL> flashback table test123 to beforedrop;

 

闪回完成。

 

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME               OBJECT TYPE  DROP TIME         

---------------------------------------------- ------------

TEST123          BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE        2011-09-21:10:41:17

SQL> --闪回同名的表,先闪回最后删除那个

SQL> select * from tab;

 

TNAME                          TABTYPE CLUSTERID                              

------------------------------ -----------------                               

DEPT                           TABLE                                           

EMP                            TABLE                                           

BONUS                          TABLE                                           

SALGRADE                       TABLE                                           

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE                                           

SYS_TEMP_FBT                   TABLE                                           

TEST123                        TABLE                                           

 

已选择7行。

 

SQL> flashback table test123 to beforedrop;

flashback table test123 to before drop

*

第 1 行出现错误:

ORA-38312: 原始名称已被现有对象使用

 

 

SQL> --重命名

SQL> flashback table test123 to beforedrop rename to test1234;

 

闪回完成。

 

SQL> select * from tab;

 

TNAME                          TABTYPE CLUSTERID                              

------------------------------ -----------------                              

DEPT                           TABLE                                            

EMP                            TABLE                                           

BONUS                          TABLE                                           

SALGRADE                       TABLE                                            

TEST1234                       TABLE                                           

SYS_TEMP_FBT                   TABLE                                           

TEST123                        TABLE                                            

 

已选择7行。

 

SQL> select * from test123;

 

      TID                                                                     

----------                                                                     

        1                                                                     

        2                                                                     

 

SQL> select * from test1234;

 

      TID TNAME                                                               

---------- --------------------                                                

        1 Tom                                                                 

 

SQL> host cls

 

 

闪回 删除: 需要考虑的事情

l闪回删除对下列表无效:

在SYSTEM 表空间内的表

用精细审计的数据库或 虚拟的私人数据库

属于字典管理的表空间

由于空间不足已经被手动或自动删除的表

l以下依赖不被保护:

位图索引

表之前删掉的索引

lFLASHBACK TABLE命令作为单一的事务执行,会得到一个单一的DML锁

表的统计数据不会被闪回

当前的索引和从属的对象会被维持

l闪回表操作:

系统表不能被闪回

不能跨越DDL操作

会被写入警告日志

产生撤销和重做的数据

 

SQL> --闪回版本查询

SQL> creat table versions_table

SP2-0734: 未知的命令开头 "creat tabl..."- 忽略了剩余的行。

SQL> create table versions_table

 2  (vid number,

 3   vname varchar(20));

 

表已创建。

 

SQL> insert into versions_tablevalues(1,'Tom');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into versions_tablevalues(2,'Mary');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> insert into versions_tablevalues(3,'Mike');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> update versions_table setvname='Mary123' where vid=2;

 

已更新 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from versions_table;

 

      VID VNAME                                                               

        1 Tom                                                                 

        2 Mary123                                                              

        3 Mike                                                                

 

SQL> /*

SQL> 问题:1

SQL> 1. 如何获取前面三个版本?

SQL> 2. 如何获取该表过去的一个时间?

SQL> */

闪回版本查询

 

闪回版本查询,提供了一个审计行改变的查询功能,通过它可以查找到所有已经提交了的行记录。其语法格式如下:

selectcolumn_name[,column_name,...]

from table_name

versionsbetween [SCN|TIMESTAMP] [expr|MINVALUE]

                  and     [epxr|MAXVALUE] as of [SCN|TIMESTAMP]expr;

备注:

其中:column_name列名;table_name表名;between...and时间段;SCN系统改变号;TIMESTAMP时间戳;ASOF表示恢复单个版本;MAXVALUE最大值;MINVALUE最小值;expr指定一个值或者表达式。

 

 

SQL> --执行闪回版本查询,获取所有的版本信息

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

 2  from versions_table

 3  versions between timestampminvalue and maxvalue

 4  order by 1,4;

SQL> col VERSIONS_STARTTIME for a20

SQL> col VERSIONS_ENDTIME for a20

SQL> /

SQL> col vname for a8

SQL> /

SQL> col VERSIONS_STARTTIME for a30

SQL> col VERSIONS_ENDTIME for a30

SQL> /

SQL> set linesize 150

SQL> /

 

      VID VNAME    VVERSIONS_STARTTIME            VERSIONS_ENDTIME                                                                                

---------- -------- - ------------------------------

    1Tom      I 21-9月 -11 11.01.09 上午                                                                                                       

    2Mary     I 21-9月 -11 11.01.24 上午   21-9月 -11 11.02.00 上午                                                                         

    2Mary123  U 21-9月 -11 11.02.00 上午                                                                                                       

    3Mike     I 21-9月 -11 11.01.39 上午                                                                                                        

 

SQL> /*

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

SQL> from versions_table

SQL> versions between timestamp minvalueand maxvalue

SQL> order by 1,4;

SQL> */

SQL> --通过闪回版本查询,可以获取某个表过去已经提交了的历史版本信息

SQL> host cls

 

SQL> --闪回事务查询

SQL> create table transaction_table

 2  (tid number,tnamevarchar(20));

 

表已创建。

 

SQL> --第一个事务

SQL> insert into transaction_tablevalues(1,'Tom');

 

已创建 1 行。

 

SQL> insert into transaction_table values(2,'Mary');

 

已创建 1 行。

 

SQL> insert into transaction_tablevalues(3,'Mike');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> --第二个事务

SQL> update transaction_table settname='Mary123' where tid=2;

 

已更新 1 行。

 

SQL> delete from transaction_table wheretid=3;

 

已删除 1 行。

 

SQL> commit;

 

提交完成。

l闪回事务查询实际上闪回版本查询的一个扩充,通过它可以审计某个事务甚至撤销一个已经提交的事务。

l实现闪回事务查询,需要先了解flashback_transaction_query视图,从该视图中可以获取事务的历史操作记录以及撤销语句(UNDO_SQL)。

l使用闪回事务查询,可以了解某个表的历史操作记录,这个操作记录对应一个撤销SQL语句,如果想要撤销这个操作,就可以执行这个SQL语句。

 

Flashback_transaction_query

 

名称                            类型                        说明

 ------------------------------------------------------------------------------------------------   

 XID                           RAW(8)                     事务编号

 START_SCN                  NUMBER                    事务的开始的系统改变号

 START_TIMESTAMP           DATE                        事务的开始时间

 COMMIT_SCN                NUMBER                     事务提交时的系统改变号

 COMMIT_TIMESTAMP         DATE                       事务提交时的时间

 LOGON_USER                VARCHAR2(30)               对应的用户

 UNDO_CHANGE#             NUMBER                     撤销操作对应的编号

 OPERATION                  VARCHAR2(32)               操作

 TABLE_NAME                 VARCHAR2(256)              表

 TABLE_OWNER               VARCHAR2(32)              所有者

 ROW_ID                      VARCHAR2(19)               行号

 UNDO_SQL                   VARCHAR2(4000)            撤销事务的SQL语句

 

注意,要查询flashback_transaction_query视图的信息,需要有select any transaction的权限。

 

 

SQL> --如何撤销第二个事务? ----闪回事务查询

SQL> --授权select any transaction给scott

SQL> conn / as sysdba

已连接。

SQL> grant select any transaction toscott;

 

授权成功。

 

SQL> conn scott/tiger

已连接。

SQL> /*

SQL> 闪回事务查询的步骤:1

SQL> 1. 通过闪回版本查询获取事务号(xid)

SQL> 2. 通过xid查询Flashback_transaction_query,获取undo_sql

SQL> */

SQL> select tid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid

 2  from transaction_table

 3  versions between timestampminvalue and maxvalue

 4  order by versions_xid;

 

TID TNAME                V VERSIONS_STARTTIME             VERSIONS_ENDTIME               VERSIONS_XID                  

2 Mary123              U 21-9月 -11 11.13.24 上午                                      0400140020040000                                     

3 Mike                 D 21-9月 -11 11.13.24 上午                                      0400140020040000                                     

2 Mary                 I 21-9月 -11 11.12.39 上午       21-9月 -11 11.13.24 上午      0700250012040000                                     

1 Tom                  I 21-9月 -11 11.12.39 上午                                      0700250012040000                                      

3 Mike                 I 21-9月 -11 11.12.39 上午       21-9月 -11 11.13.24 上午      0700250012040000                                     

 

SQL> -- 执行闪回事务查询

SQL> select operation,undo_sql

 2  fromFlashback_transaction_query

 3  where xid='0400140020040000';

 

OPERATION                                                                                                                                            

--------------------------------                                                                                                                      

UNDO_SQL                                                                                                                                             

------------------------------------------------------------------------------------------------------------------------------------------------------

DELETE                                                                                                                                               

insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');                                                                          

                                                                                                                                                     

UPDATE                                                                                                                                               

update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';                                                          

                                                                                                                                                     

BEGIN                                                                                                                                                

                                                                                                                                                     

                                                                                                                                                     

 

SQL> update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';

 

已更新 1 行。

 

SQL> insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from TRANSACTION_TABLE;

 

      TID TNAME                                                                                                                                      

---------- --------------------                                                                                                                      

        1 Tom                                                                                                                                        

        2 Mary                                                                                                                                      

        3 Mike                                                                                                                                      

 

SQL> host cls

 

SQL> spool off

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/196700/viewspace-733673/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-10-10

  • 博文量
    534
  • 访问量
    6887