表空间闪回实例

一、ORACLE闪回原理和配置：

如果Oracle10g数据库中误删除了一个表中的记录，例如删除了一个非常重要的雇员信息，并且该事务已经提交了，如何快速找回误删除的数据呢？

这种情况下可以利用Oracle10g数据库的闪回功能实现找回丢失的数据。具体的说就是行闪回。

行级闪回是指获取先前某个时间点或某个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; 已选择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 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 下午 10-10月-08 06.44.25 下午 06002F00B6020000 U maliu 10-10月-08 06.44.25 下午 07002C00BA020000 D maliu

在这个查询中可以看到一些非常重要的信息，显示了对表所执行的操作。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='07002C00BA020000'; 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>

二、闪回具体实例：

时间点:time1

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
152713033
SQL> select count(*) from testvarchar;
COUNT(*)
----------
10000000

操作如下：
SQL> delete testvarchar;
10000000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from testvarchar;
COUNT(*)
----------
0

时间点：time2
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
152883734

用Flashback功能查询以前的数据

SQL> select count(*) from testvarchar as of scn 152713033;

COUNT(*)
----------
10000000
SQL>
用flashback直接恢复表
SQL> flashback table testvarchar to scn 152713033;
flashback table testvarchar to scn 152713033
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
--或使用时间点闪回：
--FLASHBACK TABLE LICZ.SALES TO TIMESTAMP to_timestamp('2009-04-30 13:26:49', 'YYYY-MM-DD HH24:MI:SS');

SQL> alter table testvarchar enable row movement;

Table altered.

这个命令的作用是，允许Oracle 修改分配给行的rowid。在Oracle 中，插入一行时就会为它分配一个rowid，而且这一行永远拥有这个rowid。闪回表处理会对EMP 完成DELETE，并且重新插入行，这样就会为这些行分配一个新的rowid。要支持闪回就必须允许Oracle 执行这个操作

SQL> flashback table testvarchar to scn 152713033;
Flashback complete.
SQL> select count(*) from testvarchar;
COUNT(*)
----------
10000000
SQL>