1、删除后如何恢复
SQL> drop tableSALGRADE;
Table dropped.
SQL> showrecyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
SALGRADE BIN$OlFnNzmB77zgUKjAZA8L4w==$0TABLE 2016-08-18:11:44:47
SQL> FLASHBACKTABLE salgrade TO BEFORE DROP
2 /
Flashback complete.
如上,从回收站将删除后的表恢复
2、更新表后做了update操作,如何恢复,这里需要用到闪回
(1)问题描述:
SQL> update empset sal=sal+1;
14 rows updated.
SQL> commit;
Commit complete.
如上,更新完emp表之后做了提交
(2)查询闪回中记录的emp表的上一个版本对应的时间戳
SQL> selectversions_starttime from emp versions between scn minvalue and maxvalue whereempno = 7566;
VERSIONS_STARTTIME
---------------------------------------------------------------------------
18-AUG-16 12.03.43PM
可以进一步确认中间的变动:
(3)利用闪回恢复数据
SQL> alter tableemp enable row movement;
Table altered.
SQL> FLASHBACKTABLE emp TO TIMESTAMP to_timestamp('18-AUG-16 12.03.43','dd-mm-yyhh24.mi.ss');
Flashback complete.
SQL> alter tableemp disable row movement;
Table altered.
3、实例恢复:
如上图,回退没做数据不会丢失,但是前滚没做,数据一定会丢失
(1)多路复用控制文件
SQL> select namefrom v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/wyzc10g/control01.ctl
/u01/oracle/oradata/wyzc10g/control02.ctl
/u01/oracle/oradata/wyzc10g/control03.ctl
上图有 控制文件,但是并没有分开存放
(2)创建控制文件路径,并修改系统参数
Mkdir/u01/oracle/flash_recovery_area/WYZC10G
SQL> ALTER SYSTEMSETcontrol_files='/u01/oracle/oradata/wyzc10g/control01.ctl','/u01/oracle/flash_recovery_area/WYZC10G/control02.ctl'SCOPE=SPFILE;
关闭数据库,然后将控制文件拷贝到上述flash_recovery_area目录
hp cp/u01/oracle/oradata/wyzc10g/control01.ctl/u01/oracle/flash_recovery_area/WYZC10G/control02.ctl
而后启动数据库即可