不完全恢复 set until scn scn编号 set until time|timestamp ... 最常用的 set until logseq 不完全恢复示例： sql conn scott/tiger connected. sql select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual; to_char(sysdate,yy ------------------- 201
不完全恢复
set until scn
set until time|timestamp ... 最常用的
set until logseq
不完全恢复示例：
sql> conn scott/tiger
connected.
sql> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
to_char(sysdate,'yy
-------------------
2014-11-06 18:32:14
sql> drop table emp purge;
table dropped.
删除该表后做不完全恢复才能使该表执行查询，否则不行的噢噢
获取恢复时间点可以采用日志挖掘的方式：
sql> select group#,status from v$log;group# status---------- ----------------1 inactive2 inactive3 currentsql> select member from v$logfile;member--------------------------------------------------------------------------------/u01/oracle/oradata/jadl10g/redo03.log/u01/oracle/oradata/jadl10g/redo02.log/u01/oracle/oradata/jadl10g/redo01.logsql> exec dbms_logmnr.add_logfile('/u01/oracle/oradata/jadl10g/redo03.log');pl/sql procedure successfully completed.sql> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);pl/sql proc【本文来自鸿网互联 (http://www.68idc.cn)】edure successfully completed.sql> select scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') time,sql_redo from v$logmnr_contents where table_name='emp';scn time---------- -------------------sql_redo--------------------------------------------------------------------------------483041 2014-11-06 18:32:29drop table emp purge;sql> exec dbms_logmnr.end_logmnr;pl/sql procedure successfully completed.[oracle@oracle ~]$ rman target /recovery manager: release 10.2.0.5.0 - production on thu nov 6 19:37:08 2014copyright (c) 1982, 2007, oracle. all rights reserved.connected to target database: jadl10g (dbid=2011530396)rman> shutdown immediateusing target database control file instead of recovery catalogdatabase closeddatabase dismountedoracle instance shut downrman> startup mountconnected to target database (not started)oracle instance starteddatabase mountedtotal system global area 599785472 bytesfixed size 2098112 bytesvariable size 163580992 bytesdatabase buffers 427819008 bytesredo buffers 6287360 bytesrman> run{2> set until time to_date('2014-11-06 18:32:28','yyyy-mm-dd hh24:mi:ss'); --- set until scn 1222223> restore database;4> recover database;5> alter database open resetlogs;6> }executing command: set until clausestarting restore at 06-nov-14allocated channel: ora_disk_1channel ora_disk_1: sid=155 devtype=diskchannel ora_disk_1: starting datafile backupset restorechannel ora_disk_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/oracle/oradata/jadl10g/system01.dbfrestoring datafile 00002 to /u01/oracle/oradata/jadl10g/undotbs01.dbfrestoring datafile 00003 to /u01/oracle/oradata/jadl10g/sysaux01.dbfrestoring datafile 00004 to /u01/oracle/oradata/jadl10g/users01.dbfrestoring datafile 00005 to /u01/oracle/oradata/jadl10g/example01.dbfchannel ora_disk_1: reading from backup piece /u01/oracle/flash_recovery_area/jadl10g/backupset/2014_11_06/o1_mf_nnndf_tag20141106t160359_b5pbvjts_.bkpchannel ora_disk_1: restored backup piece 1piece handle=/u01/oracle/flash_recovery_area/jadl10g/backupset/2014_11_06/o1_mf_nnndf_tag20141106t160359_b5pbvjts_.bkp tag=tag20141106t160359channel ora_disk_1: restore complete, elapsed time: 00:00:45finished restore at 06-nov-14starting recover at 06-nov-14using channel ora_disk_1starting media recoverymedia recovery complete, elapsed time: 00:00:04finished recover at 06-nov-14database opened
此时执行如下操作发现该表是存在的：
select * from scott.emp;