不完全恢复分类：
基于时间点的恢复：主动恢复到过去某个时间点。time-based
基于改变的恢复：恢复到某个scn。change-based和第一个相似，主动恢复到某个时间点。
基于取消的恢复：被动恢复到某个scn。cancel-based由于日志缺失。
基于备份的controlfile的恢复：用于表空间的恢复。
如何发现时间点：log miner。
例：某用户做了如下误操作（先删了14条数据，再插入3条错误数据），需恢复至删数之前
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
SQL> delete from scott.emp1;
14 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into scott.emp1 select * from scott.emp where rownum<=3;
3 rows created.
SQL> commit;
Commit complete.
使用log miner找到错误的时间点（详见上一篇日志 oracle logminer）为：
491275（scn） 2014-04-09 21:41:43（time)
下面开始做基于时间点的不完全恢复
案例一：基于时间点的不完全恢复
--查看数据文件信息
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--关库后转储所有数据文件
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/system01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/users01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
--启库至mount状态，基于时间点不完全恢复
SQL> startup mount
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> recover database until time '2014-04-09 21:41:43';
Media recovery complete.
注意：以上恢复语句也可使用基于scn号方式：recover database until change 491275
--开库，查看恢复后的表数据情况
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
由此可见，emp1表不完全恢复成功。
注意：不完全恢复的情况，开库时需要resetlogs。
案例二：基于cancel的不完全恢复
场景：删除当前日志组所有成员、数据文件及归档信息，然后实现基于cancel的不完全恢复
--查看emp1表对应的表空间信息
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='EMP1';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT EMP1 LXTBS
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/PROD
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
--查看scott.emp1表信息，并做三步插入操作，分别对应三种模式为提交归档、只提交不归档、不提交不归档
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
28
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
42
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
--查看数据文件、日志及归档信息
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
SQL> col member for a50;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
6 rows selected.
SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/home/oracle/PROD/1_16_827176743.dbf
/home/oracle/PROD/1_17_827176743.dbf
/home/oracle/PROD/1_18_827176743.dbf
/home/oracle/PROD/1_19_827176743.dbf
/home/oracle/PROD/1_20_827176743.dbf
/home/oracle/PROD/1_21_827176743.dbf
/home/oracle/PROD/1_22_827176743.dbf
/home/oracle/PROD/1_23_827176743.dbf
/home/oracle/PROD/1_24_827176743.dbf
/home/oracle/PROD/1_25_827176743.dbf
/home/oracle/PROD/1_26_827176743.dbf
NAME
--------------------------------------------------
/home/oracle/PROD/1_27_827176743.dbf
/home/oracle/PROD/1_28_827176743.dbf
/home/oracle/PROD/1_1_844469007.dbf
/home/oracle/PROD/1_2_844469007.dbf
15 rows selected.
--非法关库后，删除对应数据文件、当前日志组所有成员及最后归档日志信息
[oracle@localhost ~]$ rm /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
[oracle@localhost ~]$ rm /u01/app/oracle/oradata/PROD/disk1/redo03.log
[oracle@localhost ~]$ rm /u01/app/oracle/oradata/PROD/disk2/redo03.log
[oracle@localhost ~]$ rm /home/oracle/PROD/1_2_844469007.dbf
--启库（失败）、查看具体文件损坏信息
SQL> startup
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf'
SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 UNKNOWN ERROR
2 UNKNOWN ERROR
3 UNKNOWN ERROR
4 UNKNOWN ERROR
5 FILE NOT FOUND
由此可见，数据库只报file 5文件破坏，先尝试基于部分数据文件损坏的完全恢复（详见之前日记“手工完全恢复演练”此部分内容）
--尝试做数据文件的完全恢复，先转储破坏的数据文件file 5
[oracle@localhost ~]$ cd /home/oracle/prod_bak
[oracle@localhost prod_bak]$ ls
cold_bak cold_bak.sql cold_cmd.sql hot_bak hot_bak.sql hot_cmd.sql
[oracle@localhost prod_bak]$ cd cold_bak
[oracle@localhost cold_bak]$ ls
control01.ctl lxtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@localhost cold_bak]$ pwd
/home/oracle/prod_bak/cold_bak
[oracle@localhost cold_bak]$ cp lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
--恢复数据文件
SQL> recover datafile 5;
ORA-00279: change 470112 generated at 04/09/2014 20:31:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_18_827176743.dbf
ORA-00280: change 470112 for thread 1 is in sequence #18
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 470212 generated at 04/09/2014 20:35:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_19_827176743.dbf
ORA-00280: change 470212 for thread 1 is in sequence #19
ORA-00279: change 470327 generated at 04/09/2014 20:37:05 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_20_827176743.dbf
ORA-00280: change 470327 for thread 1 is in sequence #20
ORA-00279: change 470359 generated at 04/09/2014 20:39:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_21_827176743.dbf
ORA-00280: change 470359 for thread 1 is in sequence #21
ORA-00279: change 470364 generated at 04/09/2014 20:39:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_22_827176743.dbf
ORA-00280: change 470364 for thread 1 is in sequence #22
ORA-00279: change 470489 generated at 04/09/2014 20:41:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_23_827176743.dbf
ORA-00280: change 470489 for thread 1 is in sequence #23
ORA-00279: change 470563 generated at 04/09/2014 20:47:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_24_827176743.dbf
ORA-00280: change 470563 for thread 1 is in sequence #24
ORA-00279: change 470689 generated at 04/09/2014 20:49:23 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_25_827176743.dbf
ORA-00280: change 470689 for thread 1 is in sequence #25
ORA-00279: change 470830 generated at 04/09/2014 20:51:19 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_26_827176743.dbf
ORA-00280: change 470830 for thread 1 is in sequence #26
ORA-00279: change 470953 generated at 04/09/2014 20:53:29 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_27_827176743.dbf
ORA-00280: change 470953 for thread 1 is in sequence #27
ORA-00279: change 491108 generated at 04/09/2014 21:38:28 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_28_827176743.dbf
ORA-00280: change 491108 for thread 1 is in sequence #28
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01112: media recovery not started
由此可见，恢复数据文件失败，且从上面的信息可知，当前日志组成员丢失，无法进行完全恢复，只能做不完全恢复（前提是要转储所有的数据文件）
--重新关库，转储所有的数据文件
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@localhost cold_bak]$ pwd
/home/oracle/prod_bak/cold_bak
[oracle@localhost cold_bak]$ ls
control01.ctl lxtbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
[oracle@localhost cold_bak]$ cp lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf
[oracle@localhost cold_bak]$ cp sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
[oracle@localhost cold_bak]$ cp system01.dbf /u01/app/oracle/oradata/PROD/disk3/system01.dbf
[oracle@localhost cold_bak]$ cp undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
[oracle@localhost cold_bak]$ cp users01.dbf /u01/app/oracle/oradata/PROD/disk3/users01.dbf
--恢复数据库（until cancel方式，因为日志丢失的情况只能用此方式）
SQL> recover database until cancel
ORA-00279: change 470112 generated at 04/09/2014 20:31:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_18_827176743.dbf
ORA-00280: change 470112 for thread 1 is in sequence #18
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
由此可见，因为是不完全恢复，emp1表只恢复到最后一次备份时的状态。
注意：一般来说，open resetlogs之后，最好重做备份（10g之前一定要重做备份），在此我们会做一下冷热备（代码省略）。
案例三、基于旧的控制文件不完全恢复
场景：误删了表空间，因此新的控制文件中无此表空间及对应数据文件的记录，因此恢复时需基于旧的控制文件来做
--查看logminer是否开启，转储控制文件
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
SUPPLEME SUP SUP
-------- --- ---
YES NO NO
SQL> alter database backup controlfile to trace;
Database altered.
--将控制文件转储至具体位置
SQL> alter database backup controlfile to '/home/oracle/contrl01.bak';
Database altered
--继续拿scott.emp1表做三步插入操作
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
14
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> alter system archive log current;
System altered.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
28
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
42
SQL> insert into scott.emp1 select * from scott.emp;
14 rows created.
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
--在此打开系统时间显示
SQL> set time on;
16:48:51 SQL> select sysdate from dual;
SYSDATE
---------
10-APR-14
Elapsed: 00:00:00.04
--删除lxtbs表空间后，查询表空间及数据文件信息
16:50:27 SQL> drop tablespace lxtbs including contents and datafiles;
Tablespace dropped.
Elapsed: 00:00:03.47
16:50:49 SQL> select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
SYSAUX PERMANENT ONLINE
UNDOTBS UNDO ONLINE
TEMPTS TEMPORARY ONLINE
USERS PERMANENT ONLINE
Elapsed: 00:00:00.41
16:51:19 SQL> col name for a50;
16:51:25 SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
3 /u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
Elapsed: 00:00:00.02
注意：这会当前控制文件中没有记录此数据文件与表空间的信息，因此无法使用当前控制文件做恢复，只能使用旧的控制文件做恢复
--使用logminer，找到误操作的时间点
--先查看当前日志组及成员信息、归档信息
16:51:32 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
1 1 4 104857600 512 2 NO CURRENT 470976
10-APR-14 2.8147E+14
2 1 2 104857600 512 2 YES INACTIVE 470713
10-APR-14 470828 10-APR-14
3 1 3 104857600 512 2 YES INACTIVE 470828
10-APR-14 470976 10-APR-14
Elapsed: 00:00:00.01
16:56:23 SQL> col member for a50;
16:56:31 SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
6 rows selected.
Elapsed: 00:00:00.04
16:56:35 SQL> select name from v$archived_log;
NAME
--------------------------------------------------
/home/oracle/PROD/1_16_827176743.dbf
/home/oracle/PROD/1_17_827176743.dbf
/home/oracle/PROD/1_18_827176743.dbf
/home/oracle/PROD/1_19_827176743.dbf
/home/oracle/PROD/1_20_827176743.dbf
/home/oracle/PROD/1_21_827176743.dbf
/home/oracle/PROD/1_22_827176743.dbf
/home/oracle/PROD/1_23_827176743.dbf
/home/oracle/PROD/1_24_827176743.dbf
/home/oracle/PROD/1_25_827176743.dbf
/home/oracle/PROD/1_26_827176743.dbf
NAME
--------------------------------------------------
/home/oracle/PROD/1_27_827176743.dbf
/home/oracle/PROD/1_28_827176743.dbf
/home/oracle/PROD/1_1_844469007.dbf
/home/oracle/PROD/1_2_844469007.dbf
/home/oracle/PROD/1_1_844532679.dbf
/home/oracle/PROD/1_2_844532679.dbf
/home/oracle/PROD/1_3_844532679.dbf
18 rows selected.
Elapsed: 00:00:00.06
--执行logmnr语句
execUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/u01/app/oracle/oradata/PROD/disk1/redo01.log', -
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/home/oracle/PROD/1_3_844532679.dbf', -
OPTIONS => DBMS_LOGMNR.ADDFILE);
17:11:32 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
17:11:52 > DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
17:11:53 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
Elapsed: 00:00:00.00
17:12:48 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='LXTBS' order by scn;
no rows selected
Elapsed: 00:00:00.13
17:13:18 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents where sql_redo like '%drop%';
USERNAME SCN TIMESTAMP
------------------------------ ---------- -------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
UNKNOWN 471033 2014-04-10 16:50:34
drop table "SCOTT"."EMP1" cascade constraints purge;
UNKNOWN 471378 2014-04-10 16:50:35
drop tablespace lxtbs including contents and datafiles;
Elapsed: 00:00:00.12
17:15:17 SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
由此可见drop tablespace lxtbs的时间点为471378 2014-04-10 16:50:35
与之前操作的时间点2014-04-10 16:50:27相比要晚几秒（这是因为写此sql时会耗点时间），接下来做基于旧控制文件的不完全恢复
--关库，转储所有数据文件及旧的控制文件
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/system01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/sysaux01.dbf /u01/app/oracle/oradata/PROD/disk4
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/PROD/disk5
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/users01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@localhost prod_bak]$ cp /home/oracle/prod_bak/hot_bak/lxtbs01.dbf /u01/app/oracle/oradata/PROD/disk3
[oracle@localhost ~]$ cd /home/oracle
[oracle@localhost ~]$ ls
afiedt.buf contrl01.bak Desktop ed.hup oracle_11g.tar.gz oradiag_oracle PROD prod_bak
[oracle@localhost ~]$ cp contrl01.bak /u01/app/oracle/oradata/PROD/disk1/control01.ctl
[oracle@localhost ~]$ cp contrl01.bak /u01/app/oracle/oradata/PROD/disk2/control01.ctl
[oracle@localhost ~]$ cp contrl01.bak /u01/app/oracle/oradata/PROD/disk3/control01.ctl
--启库至nomount状态，基于旧控制文件恢复
SQL> startup mount;
ORACLE instance started.
Total System Global Area 836976640 bytes
Fixed Size 1339740 bytes
Variable Size 490737316 bytes
Database Buffers 339738624 bytes
Redo Buffers 5160960 bytes
Database mounted.
SQL> recover database until time '2014-04-10 16:50:35' using backup controlfile;
ORA-00279: change 470718 generated at 04/10/2014 16:37:30 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_2_844532679.dbf
ORA-00280: change 470718 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 470828 generated at 04/10/2014 16:39:12 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_3_844532679.dbf
ORA-00280: change 470828 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/PROD/1_2_844532679.dbf' no longer needed for this recovery
ORA-00279: change 470976 generated at 04/10/2014 16:46:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_4_844532679.dbf
ORA-00280: change 470976 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/PROD/1_3_844532679.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/PROD/1_4_844532679.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
注意：这会报错，是因为auto只找了归档日志，下面需要再做一次恢复，一次次给联机日志文件即可。
--查看联机日志文件信息，再做恢复
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
6 rows selected.
Elapsed: 00:00:00.15
SQL> recover database until time '2014-04-10 16:50:35' using backup controlfile;
ORA-00279: change 470976 generated at 04/10/2014 16:46:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/PROD/1_4_844532679.dbf
ORA-00280: change 470976 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo01.log
Log applied.
Media recovery complete.
注意：这会恢复成功了。
--开库并查看emp1表记录数
SQL> alter database open resetlogs;
Database altered.
Elapsed: 00:01:31.86
SQL> select count(1) from scott.emp1;
COUNT(1)
----------
56
Elapsed: 00:00:00.16
注意：恢复成功后scott.emp1表为56条记录，而不是42条，这是因为虽然最后一次插入emp1表数据未提交，但因后面是drop talbespace的ddl操作，会对之前的dml操作做隐式提交，这里恢复后也可能是42条（有可能ddl之前，dml操作回滚了）。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-1165008/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
