【Oracle】Oracle 10g利用闪回挽救误删的数据
我们在开发和运维过程中,经常遇到数据被误删除的情况。无论是在应用开发中的Bug,还是修改数据的时候,如果提交了错误数据修改结果,会带来很多问题。一般来说,一旦提交commit事务,我们是不能获取到之前的数据情况,除非使用较复杂的数据恢复手段,利用备份数据恢复。
但是在Oracle中,可以使用其闪回FlashBack特性来解决这个问题。首先,声明一点,闪回Flashback的范围很大,包括数据库、表、数据均是可以Flashback的,但是机制差别很大。本文说的是简单的数据flashback,用来快速的挽救回我们的数据。
首先构建实验环境:
n 查看数据库版本信息
C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on 星期五 1月 13 13:34:27 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn scott/tiger@kyy_231 已连接。 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for 32-bit Windows: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production |
n 构建一张实验数据表
CREATE TABLE SCOTT.TB_EMPLOYEE( EMP_ID VARCHAR2(24), EMP_NAME VARCHAR2(24), EMP_DEPTNO VARCHAR2(24) ) INSERT INTO SCOTT.TB_EMPLOYEE VALUES( 'EMP001', 'TOM', '001' ); INSERT INTO SCOTT.TB_EMPLOYEE VALUES( 'EMP002', 'JACK', '002' ); INSERT INTO SCOTT.TB_EMPLOYEE VALUES( 'EMP003', 'LUCY', '003' ); |
n 查看实验表的数据,定位时间信息
SQL> SELECT * FROM SCOTT.TB_EMPLOYEE; EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; TO_CHAR(SYSDATE,'YY ------------------- 2012-01-13 14:14:38 |
n 如果我们此时误删除了数据,并且将删除结果提交。
SQL> DELETE SCOTT.TB_EMPLOYEE; 已删除3行。 SQL> COMMIT; 提交完成。 SQL> SELECT * FROM TB_EMPLOYEE; 未选定行 |
n 这时,虽然我们已经commit了删除事务,但是仍可以指定一个时间点,获取到那个时间点的数据。
SQL> SELECT * FROM SCOTT.TB_EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2012-01-13 14:14:38','yyyy-mm-dd hh24:mi:ss'); EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 |
n 删除的数据集合又可以查询到。这样恢复数据的思路有了,可以将结果集合直接插入回数据表。
SQL> INSERT INTO SCOTT.TB_EMPLOYEE SELECT * FROM SCOTT.TB_EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2012-01-13 14:14:38','YYYY-MM-DD HH24:MI:SS'); 已创建3行。 SQL> COMMIT; 提交完成。 SQL> SELECT * FROM SCOTT.TB_EMPLOYEE; EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 |
可以看到,利用Oracle闪回特性数据已经恢复了。
结论:Oracle在进行处理的时候,对过去提交过的数据,是保存过一个镜像的,并且与一个SCN相对应。所谓SCN(System Commit Number),就相当于Oracle系统中的时钟,每次进程会话commit一次,相当于推进一次scn值。相对于时钟,SCN对Oracle数据库至关重要。
对于数据,Oracle是可以保存多个版本的。每个版本是和对应的SCN相关联。我们利用闪回,可以一定程度的查找回过去一个时间SCN的数据版本。而SCN是一个绝对整数,如:
--ORACLE 9i及以上版本,获得系统当前SCN的方法如下: SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 14349701 |
n 闪回标准的做法应为:
SQL> SELECT * FROM SCOTT.TB_EMPLOYEE AS OF SCN 14349100; 未选定行 SQL> SELECT * FROM SCOTT.TB_EMPLOYEE AS OF SCN 14349050; EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 |
但是这样做,存在不方便的问题。我们就需要不断的试算合适的SCN取值,相对而言,时间日期较容易理解。使用as of timestamp更加容易。
所谓“No free lunch”,使用闪回是受到一些限制的。受到系统参数的限制。闪回是一个系统配置,需要系统参数的支持。
SQL> SHOW PARAMETER UNDO NAME TYPE VALUE ------------------------------------ ----------- ---------------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 |
在自动管理Undo的情况下,undo_retention表示支持闪回的秒数,默认为15分钟。但是,要注意,这只是一个近似值,实际上要根据系统繁忙程度和其他很多因素来决定闪回的时间。实际上,如果超过了闪回15分钟,可以获得结果。
如果闪回的时间过长,保存的SCN版本已经消失,系统会报错。
SQL> SELECT * FROM SCOTT.TB_EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2012-01-07 14:14:38','yyyy-mm-dd hh24:mi:ss'); SELECT * FROM SCOTT.TB_EMPLOYEE AS OF TIMESTAMP TO_TIMESTAMP('2012-01-07 14:14:38','yyyy-mm-dd hh24:mi:ss') 第 1 行出现错误: ORA-08180: 未找到基于指定时间的快照 |
提到闪回FLASHBACK,要注意Oracle的闪回技术是由几个相对独立的技术实现的。
l 数据库级别闪回:flashback database。将整个数据库快速恢复到一个时间点;
l 数据表级别闪回:将数据表全部恢复到过去的一个时间点上,或者对已经删除掉的数据表恢复;
l 行级别闪回:设置指定的时间点,可以查询到该时间点的特定数据行;
l 事务闪回:可以将按照事务的单位,将数据库变更闪回;
上面的例子是数据行级别的闪回,可以将制定时间的数据行导出。下面实验数据表级别的闪回。
在实际开发和维护中,我们有时候会遇到把数据表drop掉的情况。过去这种情况,我们只能通过之前保留的备份,进行不完全的恢复。这样的工作量很大也很麻烦。从Oracle10g起,引入了回收站的机制,将drop掉的数据表保存在回收站中。当发现误删除的时候,可以通过回收站回收数据表。
回收站机制类似于我们在Windows上的回收站。在windows中,当我们选择删除一个文件时,本质上并没有将文件从硬盘上删除,只是将文件以一种形式改名,这样就能从回收站中看到。
Oracle的回收站也是采用同样的原理。下面我们做一个简单的实验。
n 首先,确定系统参数。在Oracle10g中,有一个参数recyclebin,控制数据表回收站机制的启动和关闭。
SQL> conn sys/a1b2c3@kyy_231 as sysdba; 已连接。 SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on --当recyclebin的值为on的时候,表示开启回收站功能 |
n 现在以scott用户登录,删除一张表:
SQL> conn scott/tiger@kyy_231; 已连接。 SQL> select * from scott.tb_employee; EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 SQL> drop table scott.tb_employee; 表已删除。 |
n 在sqlplus中,使用show recyclebin命令,可以看到当前回收站的情况信息。(注意:只能在sqlplus/sqlplusw中查看到)
SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TB_EMPLOYEE BIN$w+Q7fI9/QhWCRa1+/mSWRg==$0 TABLE 2012-01-13:14:55:02 |
记录的内容显而易见,有一个对象原名为TB_EMPLOYEE,在回收站中的名称为“BIN$…”。原来的对象类型为数据表,是在XXX时间被删除。
n 如果这个时候确定说删除是一个错误,需要恢复,简单的使用flashback命令,就可以了。
SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TB_EMPLOYEE BIN$w+Q7fI9/QhWCRa1+/mSWRg==$0 TABLE 2012-01-13:14:55:02 SQL> flashback table scott.tb_employee to before drop; 闪回完成。 SQL> select * from scott.tb_employee; EMP_ID EMP_NAME EMP_DEPTNO ------------------------ ------------------------ ------------------------ EMP001 TOM 001 EMP002 JACK 002 EMP003 LUCY 003 |
那么,如果我们不需要这个对象了,需要完全的删除。我们需要怎么做呢?而且,在windows的回收站里,文件在清空回收站之前是还会占用硬盘空间的。Oracle的回收站里面既然保存数据,那么必然要消耗数据库空间。
n 我们可以使用purge命令,对回收站的信息进行清除。
SQL> drop table scott.tb_employee; 表已删除。 SQL> purge table scott.tb_employee; 表已清除。 SQL> show recyclebin; SQL> flashback table scott.tb_employee to before drop; flashback table scott.tb_employee to before drop 第 1 行出现错误: ORA-38305: 对象不在回收站中 |
同时,purge命令还提供了不同的操作粒度,如下:
l purge table <t>; 对指定的数据表进行清除purge;
l purge tablespace <tbsname>; 对指定的表空间回收站进行purge;
l purge tablespace <tbsname> user <username>;对指定表空间回收站中指定用户schema对象进行purge操作;