oracle数据库恢复（oracle数据库恢复备份数据）

1、根据时间点去找表的快照

select * from sj_jbsj

AS OF TIMESTAMP TO_TIMESTAMP('2013-12-13 16:10:25','YYYY-MM-DD HH24:MI:SS');

2、将快照创建到一张缓存表中

create table guohaotemp2 as

select * from sj_jbsj

AS OF TIMESTAMP TO_TIMESTAMP('2013-12-13 16:10:25','YYYY-MM-DD HH24:MI:SS')

3、将表中的数据更新为缓存表中是数据

update sj_jbsj set jkzzb=(select jkzzb from guohaotemp2 where guohaotemp2.jh=sj_jbsj.jh)

ok

参考：

1.查询

SQL> select * from gametable ;

2.删除

SQL> delete from gametable where GAMEID=1008;

1 row deleted.

SQL> commit;

Commit complete.

3.使用FlashBack查询(过5分钟)

SQL> select * from tdcs_layout AS OF TIMESTAMP TO_TIMESTAMP('2009-04-16 09:10:25','YYYY-MM-DD HH24:MI:SS');

4.使用FlashBack查询恢复表行数据

SQL> INSERT INTO gametable SELECT * FROM gametable AS OF TIMESTAMP

2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')

3 WHERE GAMEID=1008;

1 row created.

SQL> COMMIT;

Commit complete.

Oracle 9i中FlashBack闪回查询操作实例

进行闪回查询必须设置自动回滚段管理，在init.ora设置参数UNDO_MANAGEMENT=AUTO，参数UNDO_RETENTION=n，决定了能往前闪回的最大

时间，值越大就需要越多Undo空间。

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

1.查询

SQL> select * from gametable ;

GAMEID NAME STATUS CREATIOND

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

HTTPPORT HTTPADDRESS TCPPORT

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

DESCRIPTION

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

1008 帝国时代 0 26-MAR-05

0 0

1009 麻将 0 26-MAR-05

0 0

2 rows selected.

2.删除

SQL> delete from gametable where GAMEID=1008;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from gametable ;

GAMEID NAME STATUS CREATIOND

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

HTTPPORT HTTPADDRESS TCPPORT

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

DESCRIPTION

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

1009 麻将 0 26-MAR-05

0 0

2 rows selected.

3.使用FlashBack查询(过5分钟)

SQL> select GAMEID from gametable AS OF TIMESTAMP

2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')

3 WHERE GAMEID=1008;

GAMEID

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

1008

SQL> select GAMEID from gametable ;

GAMEID

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

1009

2 rows selected.

4.使用FlashBack查询恢复表行数据

SQL> INSERT INTO gametable SELECT * FROM gametable AS OF TIMESTAMP

2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')

3 WHERE GAMEID=1008;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select GAMEID from gametable ;

GAMEID

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

1009

1008

2 rows selected.

SQL> select * from gametable ;

GAMEID NAME STATUS CREATIOND

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

HTTPPORT HTTPADDRESS TCPPORT

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

DESCRIPTION

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

1009 麻帝国将 0 26-MAR-05

0 0

1008 小时代 0 26-MAR-05

0 0

GAMEID NAME STATUS CREATIOND

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

HTTPPORT HTTPADDRESS TCPPORT

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

DESCRIPTION

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

2 rows selected.

SQL>