第六章：手工不完全恢復(fù)

1、不完全恢復(fù)的特點(diǎn)：讓整個(gè)database 回到過(guò)去某個(gè)時(shí)間點(diǎn)，不能避免數(shù)據(jù)丟失

2、不完全恢復(fù)（Incomplete recover） 使用環(huán)境：
?????? 1）過(guò)去的某個(gè)時(shí)間點(diǎn)重要的table被破壞
?????? 2）在做完全恢復(fù)時(shí)，丟失了歸檔日志或當(dāng)前 online redo log
?????? 3）當(dāng)誤刪除了表空間（有備份）
?????? 4）丟失了所有的控制文件，用備份的控制文件恢復(fù)
???????
????????
3、不完全恢復(fù)的類(lèi)型：
????? 1）基于時(shí)間點(diǎn)或 基于change （scn）的不完全恢復(fù)：用于恢復(fù)過(guò)去時(shí)間點(diǎn)誤操作的table
????? 2）基于cancel ：用于歸檔日志或當(dāng)前redo log 丟失，不能做完全恢復(fù)
????? 3）基于備份的controlfile：用于表空間的恢復(fù)
?????

4、不完全恢復(fù)的操作步驟：
????? 1）先對(duì)現(xiàn)在的database做全備
????? 2）通過(guò)logmnr 找到誤操作的時(shí)間點(diǎn)
????? 3）轉(zhuǎn)儲(chǔ)所有的datafile
????? 4）在mount狀態(tài)下，對(duì)database做recover，恢復(fù)到過(guò)去的時(shí)間點(diǎn)
????? 5）將恢復(fù)出來(lái)的table做邏輯備份
????? 6）再對(duì)database做完全恢復(fù)
??????
5、logminer 工具的使用
???? -------對(duì)redo log 進(jìn)行挖掘，找出在某個(gè)時(shí)間點(diǎn)所作的DDL 或DML 操作（包括：時(shí)間點(diǎn)、datablock scn 、sql語(yǔ)句）
?? 1）對(duì)DML 分析
???
?? 04:56:30 SQL> select * from test;???????????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
???????? 1
???????? 2
???????? 3
???????? 4
???????? 5
???????? 6
???????? 7
???????? 8

8 rows selected.

04:56:40 SQL> delete from test;?????????????????????????????????????????????????????????????????????????????????????????????????????????

8 rows deleted.

04:56:48 SQL> commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Commit complete.

04:56:50 SQL> insert into test values (111);????????????????????????????????????????????????????????????????????????????????????????????

1 row created.

04:57:44 SQL> insert into test values (222);????????????????????????????????????????????????????????????????????????????????????????????

1 row created.

04:57:47 SQL> insert into test values (333);????????????????????????????????????????????????????????????????????????????????????????????

1 row created.

04:57:49 SQL> commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Commit complete.

04:57:51 SQL> select * from test;???????????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
?????? 111
?????? 222
?????? 333

04:57:57 SQL>?

04:57:01 SQL> select * from v$log;??????????????????????????????????????????????????????????????????????????????????????????????????????

??? GROUP#??? THREAD#? SEQUENCE#????? BYTES??? MEMBERS ARC STATUS?????????? FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
???????? 1????????? 1????????? 4?? 52428800????????? 1 NO? CURRENT??????????????? 1255988 16-AUG-11
???????? 2????????? 1????????? 2?? 52428800????????? 1 YES INACTIVE?????????????? 1252334 16-AUG-11
???????? 3????????? 1????????? 3?? 52428800????????? 1 YES INACTIVE?????????????? 1255986 16-AUG-11

04:57:06 SQL> alter system archive log current;?????????????????????????????????????????????????????????????????????????????????????????

System altered.

04:57:21 SQL> select * from v$log;??????????????????????????????????????????????????????????????????????????????????????????????????????

??? GROUP#??? THREAD#? SEQUENCE#????? BYTES??? MEMBERS ARC STATUS?????????? FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
???????? 1????????? 1????????? 4?? 52428800????????? 1 YES ACTIVE???????????????? 1255988 16-AUG-11
???????? 2????????? 1????????? 5?? 52428800????????? 1 NO? CURRENT??????????????? 1259738 17-AUG-11
???????? 3????????? 1????????? 3?? 52428800????????? 1 YES INACTIVE?????????????? 1255986 16-AUG-11

04:57:22 SQL>

2）啟用logmnr
-------添加database補(bǔ)充日志
04:59:50 SQL> alter database add supplemental log data;????????????????????????

Database altered.

----------查詢?nèi)罩?#xff08;歸檔和當(dāng)前日志）
04:57:21 SQL> select * from v$log;??????????????????????????????????????????????????????????????????????????????????????????????????????

??? GROUP#??? THREAD#? SEQUENCE#????? BYTES??? MEMBERS ARC STATUS?????????? FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
???????? 1????????? 1????????? 4?? 52428800????????? 1 YES ACTIVE???????????????? 1255988 16-AUG-11
???????? 2????????? 1????????? 5?? 52428800????????? 1 NO? CURRENT??????????????? 1259738 17-AUG-11
???????? 3????????? 1????????? 3?? 52428800????????? 1 YES INACTIVE?????????????? 1255986 16-AUG-11

05:00:46 SQL> select member from v$logfile;????????????????????????????????????????????????????????????????????????

MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/prod/redo03.log
/u01/app/oracle/oradata/prod/redo02.log
/u01/app/oracle/oradata/prod/redo01.log

05:00:47 SQL> select name from v$archived_log;??

NAME
------------------------------------------------------------------------------------------------------------------------
/disk1/arch/prod/arch_38_1_758481658.log
/disk1/arch/prod/arch_39_1_758481658.log
/disk1/arch/prod/arch_40_1_758481658.log
/disk1/arch/prod/arch_41_1_758481658.log
/disk1/arch/prod/arch_42_1_758481658.log
/disk1/arch/prod/arch_43_1_758481658.log
/disk1/arch/prod/arch_44_1_758481658.log
/disk1/arch/prod/arch_45_1_758481658.log
/disk1/arch/prod/arch_46_1_758481658.log
/disk1/arch/prod/arch_2_1_759309442.log
/disk1/arch/prod/arch_3_1_759309442.log
/disk1/arch/prod/arch_4_1_759309442.log

46 rows selected.

----------添加日志，分析
05:00:56 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/prod/arch_4_1_759309442.log',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

05:02:58 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo02.log',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

----------執(zhí)行l(wèi)ogmnr 分析
05:02:58 SQL>execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.
?
?--------查詢分析結(jié)果
?
?05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';????????????????????????????????????????????????????????????????

Session altered.
?????
05:06:13 SQL> COL SQL_REDO FOR A50???????????????????????????????????????????????????????????????????????????????????????????????????????
05:06:21 SQL> R??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? 1* select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST'

USERNAME????????????????????????????? SCN TIMESTAMP?????????? SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '4' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAA';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '5' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAB';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '6' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAC';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '7' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAD';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '8' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAE';

05:06:25 SQL>?
?????
---------結(jié)束日志分析

05:06:25 SQL> execute dbms_logmnr.end_logmnr;???????????????????????????????????????????????????????????????????????????????????????????

PL/SQL procedure successfully completed.

?

2）對(duì)DDL 操作分析

04:57:57 SQL> drop table test purge;????????????????????????????????????????????????????????????????????????????????????????????????????

Table dropped.

05:09:39 SQL> create table test (id int ) tablespace test;??????????????????????????????????????????????????????????????????????????????

Table created.

05:09:55 SQL> insert into test values (1) ;?????????????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:10:01 SQL> commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Commit complete.

---------設(shè)置logmnr 參數(shù)，存放數(shù)據(jù)字典文件
[oracle@work prod]$ mkdir /home/oracle/logmnr

05:11:28 SQL> show parameter utl????????????????????????????????????????????????????????????????????????????????????????????????????????

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines?????????????? string
utl_file_dir???????????????????????? string
05:11:31 SQL> alter system set utl_file_dir='/home/oracle/logmnr' scope=spfile;?????????????????????????????????????????????????????????

System altered.

05:11:48 SQL> startup force??????????????????????????????????????????????????????????????????????????????????????????????????????????????
ORACLE instance started.

Total System Global Area? 314572800 bytes
Fixed Size????????????????? 1219184 bytes
Variable Size????????????? 79693200 bytes
Database Buffers????????? 230686720 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
Database opened.
05:12:08 SQL> show parameter utl????????????????????????????????????????????????????????????????????????????????????????????????????????

NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines?????????????? string
utl_file_dir???????????????????????? string????? /home/oracle/logmnr
05:12:13 SQL>

---------建立數(shù)據(jù)字典文件dict.ora
05:12:13 SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);???????????????????????????

PL/SQL procedure successfully completed.

---------查看日志信息
05:13:46 SQL> col name for a50???????????????????????????????????????????????????????????????????????????????????????????????????????????
05:13:52 SQL> r??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? 1* select name,sequence# from v$archived_log
NAME??????????????????????????????????????????????? SEQUENCE#
-------------------------------------------------- ----------
/disk1/arch/prod/arch_38_1_758481658.log?????????????????? 38
/disk1/arch/prod/arch_39_1_758481658.log?????????????????? 39
/disk1/arch/prod/arch_40_1_758481658.log?????????????????? 40
/disk1/arch/prod/arch_41_1_758481658.log?????????????????? 41
/disk1/arch/prod/arch_42_1_758481658.log?????????????????? 42
/disk1/arch/prod/arch_43_1_758481658.log?????????????????? 43
/disk1/arch/prod/arch_44_1_758481658.log?????????????????? 44
/disk1/arch/prod/arch_45_1_758481658.log?????????????????? 45
/disk1/arch/prod/arch_46_1_758481658.log?????????????????? 46
/disk1/arch/prod/arch_2_1_759309442.log???????????????????? 2
/disk1/arch/prod/arch_3_1_759309442.log???????????????????? 3
/disk1/arch/prod/arch_4_1_759309442.log???????????????????? 4
/disk1/arch/prod/arch_5_1_759309442.log???????????????????? 5

47 rows selected.
???
???? 05:14:24 SQL> select * from v$log;??????????????????????????????????????????????????????????????????????????????????????????????????????

??? GROUP#??? THREAD#? SEQUENCE#????? BYTES??? MEMBERS ARC STATUS?????????? FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
???????? 1????????? 1????????? 4?? 52428800????????? 1 YES INACTIVE?????????????? 1255988 16-AUG-11
???????? 2????????? 1????????? 5?? 52428800????????? 1 YES INACTIVE?????????????? 1259738 17-AUG-11
???????? 3????????? 1????????? 6?? 52428800????????? 1 NO? CURRENT??????????????? 1280754 17-AUG-11

? 05:14:38 SQL> col member for a60?????????????????????????????????????????????????????????????????????????????????????????????????????????
05:14:43 SQL>?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? 1* select group#,member from v$logfile

??? GROUP# MEMBER
---------- ------------------------------------------------------------
???????? 3 /u01/app/oracle/oradata/prod/redo03.log
???????? 2 /u01/app/oracle/oradata/prod/redo02.log
???????? 1 /u01/app/oracle/oradata/prod/redo01.log

05:14:43 SQL>???

-------------添加日志分析

05:14:43 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/oradata/prod/redo03.log',options=>dbms_logmnr.new);?????????

PL/SQL procedure successfully completed.

05:20:59 SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/prod/arch_5_1_759309442.log',options=>dbms_logmnr.addfile);?????

PL/SQL procedure successfully completed.

?
?------------執(zhí)行分析

05:16:08 SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);????

PL/SQL procedure successfully completed.

----------查看分析結(jié)果

05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';????????????????????????????????????????????????????????????????

Session altered.

05:22:55 SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents??????????????????????????????????????????????????????????????
05:22:57?? 2? WHERE USERNAME ='SCOTT' and lower(sql_redo) like '%table%';???????????????????????????????????????????????????????????????

USERNAME????????????????????????????? SCN TIMESTAMP?????????? SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
SCOTT???????????????????????????? 1260664 2011-08-17 05:09:38 drop table test purge;
SCOTT???????????????????????????? 1260698 2011-08-17 05:09:55 create table test (id int ) tablespace test;

05:23:33 SQL>

05:06:25 SQL> execute dbms_logmnr.end_logmnr;???????????????????????????????????????????????????????????????????????????????????????????

PL/SQL procedure successfully completed.

不完全恢復(fù)案例：

案例1：

------------恢復(fù)過(guò)去某個(gè)時(shí)間點(diǎn)誤操作的table
1）基于時(shí)間點(diǎn)
05:06:13 SQL> COL SQL_REDO FOR A50???????????????????????????????????????????????????????????????????????????????????????????????????????
05:06:21 SQL> R??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
? 1* select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name='TEST'

USERNAME????????????????????????????? SCN TIMESTAMP?????????? SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '4' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAA';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '5' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAB';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '6' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAC';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '7' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAD';

????????????????????????????????? 1259691 2011-08-17 04:56:50 delete from "SCOTT"."TEST" where "ID" = '8' and RO
????????????????????????????????????????????????????????????? WID = 'AAAM3bAACAAAAA/AAE';
??????????????????????????????????????????????????????????????
???? ------通過(guò)以上logmnr 分析，將test表恢復(fù)到delete 之前（time：2011-08-17 04:56:50）
?????
?????
???? 05:33:23 SQL> select * from test;???????????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
???????? 1
?????????
? ----------test 表現(xiàn)有的數(shù)據(jù)
??
----------將database啟動(dòng)到mount ，進(jìn)行restore 和recover
05:35:38 SQL> conn /as sysdba????????????????????????????????????????????????????????????????????????????????????????????????????????????
Connected.
05:35:46 SQL>?
05:35:46 SQL> shutdown immediate?????????????????????????????????????????????????????????????????????????????????????????????????????????
Database closed.
Database dismounted.
ORACLE instance shut down.
05:36:14 SQL>?
05:36:14 SQL> startup mount??????????????????????????????????????????????????????????????????????????????????????????????????????????????
ORACLE instance started.

Total System Global Area? 314572800 bytes
Fixed Size????????????????? 1219184 bytes
Variable Size????????????? 71304592 bytes
Database Buffers????????? 239075328 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
05:37:18 SQL>?
----------restore 所有的datafile?
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

---------基于時(shí)間的恢復(fù)（時(shí)間點(diǎn)為logmnr查詢的時(shí)間）

05:40:13 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';????????????????????????????????????????????????????????????????

Session altered.
05:41:05 SQL> recover database until time '2011-08-17 04:56:50';?????????????????????????????????????????????????????????????????????????
Media recovery complete.

查看告警日志：
ALTER DATABASE RECOVER? database until time '2011-08-17 04:56:50'??
Wed Aug 17 05:41:10 2011
Media Recovery Start
Wed Aug 17 05:41:10 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
? Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Wed Aug 17 05:41:12 2011
Incomplete Recovery applied until change 1259690
Wed Aug 17 05:41:12 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER? database until time '2011-08-17 04:56:50'??
Wed Aug 17 05:41:59 2011

-----------驗(yàn)證

05:41:13 SQL> alter database open resetlogs;????????????????????????????????????????????????????????????????????????????????????????????

Database altered.

05:42:23 SQL> select * from scott.test;?????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
???????? 1
???????? 2
???????? 3
???????? 4
???????? 5
???????? 6
???????? 7
???????? 8

8 rows selected.

案例2：
-----------恢復(fù)過(guò)去某個(gè)時(shí)間點(diǎn)誤操作的表
1）基于change （scn）
05:45:41 SQL> truncate table scott.test;????????????????????????????????????????????????????????????????????????????????????????????????

Table truncated.

05:45:52 SQL> insert into scott.test values (11);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:46:05 SQL> insert into scott.test values (22);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:46:07 SQL> insert into scott.test values (33);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:46:09 SQL> commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Commit complete.

05:46:11 SQL> select * from scott.test;?????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
??????? 11
??????? 22
??????? 33

05:46:16 SQL>

--------通過(guò)logmr 分析出誤操作的scn
05:52:30 SQL> select current_scn from v$database;??????? //datablock 記錄的scn????????????????????????????????????????????????????????????????????????????????

CURRENT_SCN
-----------
??? 1260285
????
?------------test 表里的記錄
?05:52:12 SQL> select * from scott.test;?????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
???????? 1
???????? 2
???????? 3
???????? 4
???????? 5
???????? 6
???????? 7
???????? 8

8 rows selected

05:52:47 SQL> truncate table scott.test;????????????????????????????????????????????????????????????????????????????????????????????????

Table truncated

05:54:18 SQL> insert into scott.test values (11);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:54:22 SQL> insert into scott.test values (22);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:54:24 SQL> insert into scott.test values (33);???????????????????????????????????????????????????????????????????????????????????????

1 row created.

05:54:25 SQL> commit;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????

Commit complete.

05:54:26 SQL>

進(jìn)行基于change的恢復(fù)：
---------在mount狀態(tài)，進(jìn)行restore 和recover
-------restore 所有的datafile
05:46:16 SQL> startup force mount????????????????????????????????????????????????????????????????????????????????????????????????????????
ORACLE instance started.

Total System Global Area? 314572800 bytes
Fixed Size????????????????? 1219184 bytes
Variable Size????????????? 71304592 bytes
Database Buffers????????? 239075328 bytes
Redo Buffers??????????????? 2973696 bytes
Database mounted.
05:48:07 SQL>
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/

05:55:07 SQL> select file#,checkpoint_change# from v$datafile;??????????????????????????????????????????????????????????????????????????

???? FILE# CHECKPOINT_CHANGE#
---------- ------------------
???????? 1??????????? 1260061
???????? 2??????????? 1260061
???????? 3??????????? 1260061
???????? 4??????????? 1260061
???????? 5??????????? 1260061
???????? 6??????????? 1260061
???????? 7??????????? 1260061
???????? 8??????????? 1260061
???????? 9??????????? 1260061

9 rows selected.

05:57:00 SQL> select file#,checkpoint_change# from v$datafile_header;???????????????????????????????????????????????????????????????????

???? FILE# CHECKPOINT_CHANGE#
---------- ------------------
???????? 1??????????? 1258960
???????? 2??????????? 1258960
???????? 3??????????? 1258960
???????? 4??????????? 1258960
???????? 5??????????? 1258960
???????? 6??????????? 1258960
???????? 7??????????? 1258960
???????? 8??????????? 1258960
???????? 9??????????? 1258960

9 rows selected.

----------基于change 的recover

05:57:03 SQL> recover database until change 1260285;?????????????????????????????????????????????????????????????????????????????????????
ORA-00279: change 1258960 generated at 08/17/2011 04:37:14 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_4_1_759309442.log
ORA-00280: change 1258960 for thread 1 is in sequence #4

05:57:33 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
ORA-00279: change 1259691 generated at 08/17/2011 05:41:59 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_1_1_759390119.log
ORA-00280: change 1259691 for thread 1 is in sequence #1

ORA-00279: change 1260023 generated at 08/17/2011 05:44:31 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_2_1_759390119.log
ORA-00280: change 1260023 for thread 1 is in sequence #2
ORA-00278: log file '/disk1/arch/prod/arch_1_1_759390119.log' no longer needed for this recovery

ORA-00279: change 1260025 generated at 08/17/2011 05:44:32 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_3_1_759390119.log
ORA-00280: change 1260025 for thread 1 is in sequence #3
ORA-00278: log file '/disk1/arch/prod/arch_2_1_759390119.log' no longer needed for this recovery

Log applied.
Media recovery complete.
05:57:44 SQL>

查看告警日志：

ALTER DATABASE RECOVER? database until change 1260285??
Wed Aug 17 05:57:32 2011
Media Recovery Start
Media Recovery start incarnation depth : 2, target inc# : 5, irscn : 1259690
ORA-279 signalled during: ALTER DATABASE RECOVER? database until change 1260285? ...
Wed Aug 17 05:57:38 2011
ALTER DATABASE RECOVER??? CONTINUE DEFAULT??
Wed Aug 17 05:57:38 2011
Media Recovery Log /disk1/arch/prod/arch_4_1_759309442.log
ORA-279 signalled during: ALTER DATABASE RECOVER??? CONTINUE DEFAULT? ...
Wed Aug 17 05:57:40 2011
ALTER DATABASE RECOVER??? CONTINUE DEFAULT??
Wed Aug 17 05:57:40 2011
Media Recovery Log /disk1/arch/prod/arch_1_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER??? CONTINUE DEFAULT? ...
Wed Aug 17 05:57:40 2011
ALTER DATABASE RECOVER??? CONTINUE DEFAULT??
Wed Aug 17 05:57:40 2011
Media Recovery Log /disk1/arch/prod/arch_2_1_759390119.log
ORA-279 signalled during: ALTER DATABASE RECOVER??? CONTINUE DEFAULT? ...
Wed Aug 17 05:57:41 2011
ALTER DATABASE RECOVER??? CONTINUE DEFAULT??
Wed Aug 17 05:57:41 2011
Media Recovery Log /disk1/arch/prod/arch_3_1_759390119.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1 Reading mem 0
? Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
? Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Wed Aug 17 05:57:41 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
? Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Wed Aug 17 05:57:41 2011
Incomplete Recovery applied until change 1260286
Wed Aug 17 05:57:41 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER??? CONTINUE DEFAULT?

--------驗(yàn)證

05:57:44 SQL> alter database open resetlogs;????????????????????????????????????????????????????????????????????????????????????????????

Database altered.

05:59:46 SQL> select * from scott.test;?????????????????????????????????????????????????????????????????????????????????????????????????

??????? ID
----------
???????? 1
???????? 2
???????? 3
???????? 4
???????? 5
???????? 6
???????? 7
???????? 8

8 rows selected.

本文轉(zhuǎn)自 客居天涯 51CTO博客，原文鏈接：http://blog.51cto.com/tiany/792477，如需轉(zhuǎn)載請(qǐng)自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的Oracle 备份与恢复学习笔记（6_1）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。