如果Oracle 10g 数据库中误删除了一个表中的记录,例如删除了一个非常重要的雇员信息,并且该事务已经提交了,如何快速找回误删除的数据呢?
这种情况下可以利用Oracle 10g 数据库的闪回功能实现找回丢失的数据。具体的说就是行闪回。
行级闪回是指获取先前某个时间点或某个SCN值时表的行数据。行级闪回有两种方式,一是闪回到某个时间点,二是闪回到某个SCN号。
实验如下:
首先以SYS用户连接数据库,查询数据库是否开启了闪回功能。
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
SQL> |
如果没有开启闪回功能,则需要SHUTDOWN IMMEDIATE数据库,然后将数据库加载到MOUNT状态,使用ALTER DATABASE FLASHBACK ON 开启数据库闪回功能。
还需要设置参数db_flashback_retention_target。实例如下:
SQL> shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup mount; ORACLE 例程已经启动。
Total System Global Area 289406976 bytes Fixed Size 1248600 bytes Variable Size 92275368 bytes Database Buffers 192937984 bytes Redo Buffers 2945024 bytes 数据库装载完毕。
SQL> select name,log_mode,open_mode,flashback_on from v$database;
NAME LOG_MODE OPEN_MODE FLASHBACK_ON --------- ------------ ---------- ------------------ MYORACLE ARCHIVELOG MOUNTED NO
SQL> alter database flashback on;
数据库已更改。
SQL> alter system set db_flashback_retention_target=3600;
系统已更改。
SQL> alter database open;
数据库已更改。 |
以SCOTT用户连接数据库,在EMP表中插入一些数据:
SQL> conn scott/tiger; 已连接。 SQL> insert into myemp(empno,ename,job,sal) values(8007,'zhangsan','clerk',3000);
已创建 1 行。
SQL> insert into myemp(empno,ename,job,sal) values(8087,'zhangsan','clerk',3000);
已创建 1 行。
SQL> commit;
提交完成。
SQL> update myemp set ename='maliu' where empno=8087;
已更新 1 行。
SQL> commit;
提交完成。 |
做一个查询
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,ename 2 from myemp 3 versions between timestamp minvalue and maxvalue 4 order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME ------------------------------ ------------------------------ ---------------- - ---------- 10-10月-08 06.41.55 下午 08000E00B7020000 I zhangsan 10-10月-08 06.41.55 下午 10-10月-08 06.42.31 下午 08000E00B7020000 I zhangsan 10-10月-08 06.42.31 下午 06002F 00B6020000 U maliu JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME ------------------------------ ------------------------------ ---------------- - ---------- JAMES FORD MILLER WARD lisi SMITH ALLEN
已选择18行。 |
执行删除操作:
SQL> delete from myemp where empno=8087;
已删除 1 行。
SQL> commit;
提交完成。 |
执行查询可以看到:
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,ename 2 from myemp 3 versions between timestamp minvalue and maxvalue 4 order by versions_starttime; |
执行查询可以看到如下结果:
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,ename
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ENAME
|
在这个查询中可以看到一些非常重要的信息,显示了对表所执行的操作。VERSIONS_STARTTIME列、VERSIONS_ENDTIME列、VERSIONS_XID列等等都是伪列,其中V列表示对表做了什么操作(I表示INSERT、U表示UPDATE、D表示DELETE);VERSIONS_XID列显示了更改事务标识符。有关该事务的更多信息可以在视图FLASHBACK_TRANSACTION_QUERY中进行查询。
SQL> select undo_sql from flashback_transaction_query 2 where xid=' 07002C 00BA020000';
UNDO_SQL -------------------------------------------------------------------------------------------------------------- Insert into "SCOTT"."MYEMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('8087','maliu','clerk',NULL,NULL,'3000',NULL,NULL); |
经过查询,可以看到被删除的行在插入时的语句,这样就可以使用这个语句进行恢复数据了。
flashback_transaction_query视图包含了一些非常重要的信息,例如提交操作的时间标记和SCN、查询开始时的SCN、时间标记以及其他一些信息等。可以通过查看这个视图的结构获得更多的详细信息。
SQL> desc flashback_transaction_query; 名称 ------------------------------------------ XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL |
最后需要注意的问题:
对表数据更新一段时间后,只有在初始化参数UNDO_RETENTION设置的时间内才可以查询到表的数据更改记录。而且,只有初始化参数UNDO_MANAGEMENT设置为AUTO后才能使用闪回查询。
SQL> conn / as sysdba; 已连接。 SQL> show parameter UNDO_RETENTION;
NAME TYPE VALUE ------------------------------------ ----------- -------- undo_retention integer 900 SQL> show parameter UNDO_MANAGEMENT;
NAME TYPE VALUE ------------------------------------ ----------- -------- undo_management string AUTO SQL> |