Oracle闪回技术.doc

Oracle闪回技术闪回(Flashback)错误操作：1. 错误的delete一条记录，并且commit2. 错误地删除了一个表: drop table3. 查询某个表的历史记录(所有已经提交了的历史记录)4. 错误地执行了一个事务对应闪回的四种类型:1. 闪回表-针对delete方式删除数据可进行回滚2. 闪回删除-针对drop方式删除整张表进行回滚3. 闪回版本查询4. 闪回事务查询5. 闪回数据库 注意：1、delete删除表数据可以闪回，因为他是只删除数据，表没变；但是truncate删除表所有数据无法闪回，因为truncate原理是先删除原先整个表，在重新建立一个结构相同的表，该表是新创建的，无法回滚了。1、对用户进行闪回设置与授权SQL -闪回的时间参数cmd下 sqlplus system/admin已连接。SQL show userUSER 为 SYSSQL show parameters undo; l闪回表，实际上是将表中的数据快速恢复到过去的一个是焦点或者系统改变号SCN上。实现表的闪回，需要使用到与撤销表空间相关的undo信息，通过show parameter undo命令可以了解这些信息。ll用户对表数据的修改操作，都记录在撤销表空间中，这为表的闪回提供了数据恢复的基础。例如，某个修改操作在提交后被记录在撤销表空间中，保留时间为900秒，用户可以在这900秒的时间内对表进行闪回操作，从而将表中的数据恢复到修改之前的状态。 NAME TYPE VALUE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 - 将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(参数文件) bothSQL */SQL -为scott授予闪回的权限SQL grant flashback any table to scott; 授权成功。 SQL conn scott/tiger已连接。SQL show userUSER 为 SCOTTSQL host cls 2、闪回delete方式的表数据删除表中数据前先设置时间戳scn，删除好后如果后悔就利用这个时间戳scn回滚，一定要记住这个时间戳scn。本方法只针对delete删除表数据起作用。SQL create table flashback_table1 (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 -记录当前的系统时间(SCN)SQL select sysdate 时间, timestamp_to_scn(sysdate) SCN from dual; 时间 SCN 21-9月 -11 4354761 SQL delete from flashback_table1 where fid=2; 已删除 1 行。 SQL commit; 提交完成。 SQL select * from flashback_table1; FID FNAME - - 1 Tom 3 Mike SQL -执行闪回表SQL flashback table flashback_table1 to scn 4354761;flashback table flashback_table1 to scn4354761 *第 1 行出现错误:ORA-08189: 因为未启用行移动功能, 不能闪回表 SQL select rowid,fid,fname from flashback_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 from flashback_table1; ROWID FID FNAME - - AAANh7AAEAAAAGnAAB 1 Tom AAANh7AAEAAAAGnAAD 2 Mary AAANh7AAEAAAAGnAAE 3 Mike 闪回表语法： FLASHBACK TABLE schema.TO BEFORE DROP RENAME TO table SCN|TIMESTAMPexpr ENABLE|DISABLETRIGGERS schema：模式名，一般为用户名。TO TIMESTAMP：系统邮戳，包含年、月、日、时、分、秒。TO SCN：系统更改号，ENABLE TRIGGERS：表示触发器恢复以后为enable状态，而默认为disable状态。TO BEFORE DROP：表示恢复到删除之前。RENAME TO table：表示更换表名。 SQL -闪回表的思想：将表回退到过去的一个时间上SQL -问题：不知道过去时间，怎么办？SQL host cls 3、从Oracle回收站中闪回drop方式的删除利用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; 回收站只对普通用户有效删除表SQL drop table test123;SQL show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME TEST123 BIN$w6NYaQ1jSZ6uzO2jUQ/ALw=$0 TABLE 2011-09-21:10:35:32-闪回删除：从回收站中取回表SQL flashback table test123 to before drop; 闪回完成。 SQL -通过回收站中的名称闪回删除 SQL flashback table BIN$9JMrb6kbRCON287lDV+5dA=$0 to before drop; 闪回完成。 SQL host cls闪回删除：回收站（recyclebin）简介 回收站是所有被删除对象及其相依对象的逻辑存储容器，例如当一个表被删除(drop)时，该表及其相依对象并不会马上被数据库彻底删除，而是被保存到回收站中。回收站将用户执行的drop操作记录在一个系统表中，也就是将被删除的对象写到一个数据字典中。如果确定不再需要该对象，可以使用purge命令对回收站进行清空。被删除的对象的名字可能是相同的，例如用户创建了一个test表，使用drop命令删除该表后，又创建了一个test表，这时，如果再次删除该表就会导致向回收站中添加了两个相同的表。回收站中对象的命名规则：为了确保添加到回收站中的对象的名称都是唯一的，系统会对这些保存到回收站中的对象进行重命名，重命名的格式如下： BIN$globalUID$version例：BIN$9JMrb6kbRCON287lDV+5dA=$0其中： 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 -查询回收站：应该有两个重名的test123SQL show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME - -TEST123 BIN$HDhYGEaXTayAnETFAUoaNw=$0 TABLE 2011-09-21:10:41:59TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg=$0 TABLE 2011-09-21:10:41:17-闪回这两个表SQL flashback table test123 to before drop; 闪回完成。 SQL show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME - -TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg=$0 TABLE 2011-09-21:10:41:17 -闪回同名的表，先闪回最后删除那个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 before drop;flashback table test123 to before drop*第 1 行出现错误:ORA-38312: 原始名称已被现有对象使用 SQL -重命名SQL flashback table test123 to before drop 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; 闪回删除: 需要考虑的事情闪回删除对下列表无效: 在SYSTEM 表空间内的表 用精细审计的数据库或 虚拟的私人数据库 属于字典管理的表空间 由于空间不足已经被手动或自动删除的表以下依赖不被保护: 位图索引 表之前删掉的索引 FLASHBACK TABLE命令作为单一的事务执行，会得到一个单一的DML锁 表的统计数据不会被闪回 当前的索引和从属的对象会被维持闪回表操作: 系统表不能被闪回 不能跨越DDL操作 会被写入警告日志 产生撤销和重做的数据 4、闪回版本查询SQL create table versions_table(vid number,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 问题：11. 如何获取前面三个版本？2. 如何获取该表过去的一个时间? 闪回版本查询，提供了一个审计行改变的查询功能，通过它可以查找到所有已经提交了的行记录。其语法格式如下：Select column_name,column_name,.from table_nameVersions between SCN|TIMESTAMP expr|MINVALUE and epxr|MAXVALUE as of SCN|TIMESTAMPexpr;备注：其中：column_name列名；table_name表名；between.and时间段；SCN系统改变号；TIMESTAMP时间戳；ASOF表示恢复单个版本;MAXVALUE最大值；MINVALUE最小值；expr指定一个值或者表达式。 SQL -执行闪回版本查询，获取所有的版本信息SQL select vid,vname,versions_operation,versions_starttime,versions_endtime 2 from versions_table 3 versions between timestamp minvalue and maxvalue 4 order by 1,4;SQL col VERSIONS_STARTTIME for a20SQL col VERSIONS_ENDTIME for a20SQL /SQL col vname for a8SQL /SQL col VERSIONS_STARTTIME for a30SQL col VERSIONS_ENDTIME for a30SQL /SQL set linesize 150SQL / VID VNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME - - - - - 1 Tom I 21-9月 -11 11.01.09 上 午 2 Mary I 21-9月 -11 11.01.24 上午 21-9月 -11 11.02.00 上 午 2 Mary123 U 21-9月 -11 11.02.00 上 午 3 Mike I 21-9月 -11 11.01.39 上 午 SQL /*SQL selectvid,vname,versions_operation,versions_starttime,versions_endtimeSQL from versions_tableSQL versions between timestamp minvalueand maxvalueSQL order by 1,4;SQL */ SQL -通过闪回版本查询，可以获取某个表过去已经提交了的历史版本信息SQL host cls 5、闪回事务查询SQL create table transaction_table(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; 提交完成。闪回事务查询实际上闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销一个已经提交的事务。实现闪回事务查询，需要先了解flashback_transaction_query视图，从该视图中可以获取事务的历史操作记录以及撤销语句(UNDO_SQL)。使用闪回事务查询，可以了解某个表的历史操作记录，这个操作记录对应一个撤销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给scottSQL conn / as sysdba已连接。SQL grant select any transaction to scott; 授权成功。 SQL conn scott/tiger已连接。SQL /*SQL 闪回事务查询的步骤:1SQL 1. 通过闪回版本查询获取事务号(xid)SQL 2. 通过xid查询Flashback_transaction_query，获取undo_sqlSQL */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 VERS