oracle用控制文件旧备份恢复后数据库恢复总结

一 oracle是如何判断控制文件的新旧

1 正常情况下

控制文件seq#(controlfile_sequence#) 大于等于数据文件头部记录的控制文件seq#(fhcsq)

控制文件 scn(controlfile_change#)大于等于数据文件头部scn(fhscn)

如下所示：

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

CURRENT 16344 2781355383 2781355383

SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;

hxfil :数据文件编号

fhcsq：数据文件头部记录的控制文件seq号

fhscn：数据文件头部的scn号

fhrba_seq：数据文件头部rba地址中的日志序列号

HXFIL FHCSQ FHSCN FHRBA_SEQ

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

1 16343 2781355383 22

2 16343 2781355383 22

3 16343 2781355383 22

4 16343 2781355383 22

5 16343 2781355383 22

6 16343 2781355383 22

7 16343 2781355383 22

8 16343 2781355383 22

11 16343 2781355383 22

12 16343 2781355383 22

13 16343 2781355383 22

11 rows selected.

2 用旧控制文件恢复后的情况如下：

RMAN> restore controlfile from '/oracle/app/db1/dbs/01nve87c_1_1';

Starting restore at 14-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

output filename=/oracle/CRM2/CRM/control01.ctl

output filename=/oracle/CRM2/CRM/control02.ctl

Finished restore at 14-JAN-13

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

BACKUP 16294 2781355207 2781347284

SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;

HXFIL FHCSQ FHSCN FHRBA_SEQ

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

1 16343 2781355383 22

2 16343 2781355383 22

3 16343 2781355383 22

4 16343 2781355383 22

5 16343 2781355383 22

6 16343 2781355383 22

7 16343 2781355383 22

8 16343 2781355383 22

11 16343 2781355383 22

12 16343 2781355383 22

13 16343 2781355383 22

通过上面两个查询不难发现：

controlfile_sequence #=16294 小于数据文件头部的 fhcsq=16343

controlfile_change#=2781355207小于数据文件头部的fhscn=2781355383

注意在用旧控制文件恢复后有以下两点需要注意：

1 CONTROLFILE_CHANGE#=2781355207此值位于哪个归档low scn 和next scn间决定恢复应用归档的起始seq号。

2 数据文件头部的FHRBA_SEQ=22 决定了恢复应用的最后一个归档seq号为22

3 用旧备份控制文件恢复后数据库应该注意两点

a 联机日志序号倒退

SQL> select group#,archived,sequence#,status from v$log;

GROUP# ARC SEQUENCE# STATUS

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

1 YES 8 INACTIVE

2 NO 9 CURRENT

6 YES 6 INACTIVE

4 YES 7 INACTIVE

5 YES 5 INACTIVE

3 YES 4 INACTIVE

6 rows selected.

b 控制文件头部标记该控制文件为备份控制文件

DUMP OF CONTROL FILES, Seq # 16296 = 0x3fa8

V10 STYLE FILE HEADER:

Compatibility Vsn = 169869568=0xa200100

Db ID=3601019238=0xd6a33166, Db Name='CRM'

Activation ID=0=0x0

Control Seq=16296=0x3fa8, File size=450=0x1c2

File Number=0, Blksiz=16384, File Type=4 BACKUP CONTROL

二用旧控制文件恢复后，数据库的恢复步骤（交互方式）

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 2781355207 generated at 01/14/2013 20:28:34 needed for thread 1

ORA-00289: suggestion : /oracle/archive/1_9_804715506.dbf

ORA-00280: change 2781355207 for thread 1 is in sequence #9

注意此处值2781355207来自于旧控制文件恢复后的controlfile_change# 值，该值在哪个归档的low scn 和next scn范围内，则决定恢复应用归档的开始seq号

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: change 2781355359 generated at 01/14/2013 22:50:15 needed for thread

ORA-00289: suggestion : /oracle/archive/1_10_804715506.dbf

ORA-00280: change 2781355359 for thread 1 is in sequence #10

ORA-00278: log file '/oracle/archive/1_9_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355361 generated at 01/14/2013 22:50:16 needed for thread

ORA-00289: suggestion : /oracle/archive/1_11_804715506.dbf

ORA-00280: change 2781355361 for thread 1 is in sequence #11

ORA-00278: log file '/oracle/archive/1_10_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355363 generated at 01/14/2013 22:50:19 needed for thread

ORA-00289: suggestion : /oracle/archive/1_12_804715506.dbf

ORA-00280: change 2781355363 for thread 1 is in sequence #12

ORA-00278: log file '/oracle/archive/1_11_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355365 generated at 01/14/2013 22:50:20 needed for thread

ORA-00289: suggestion : /oracle/archive/1_13_804715506.dbf

ORA-00280: change 2781355365 for thread 1 is in sequence #13

ORA-00278: log file '/oracle/archive/1_12_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355367 generated at 01/14/2013 22:50:20 needed for thread

ORA-00289: suggestion : /oracle/archive/1_14_804715506.dbf

ORA-00280: change 2781355367 for thread 1 is in sequence #14

ORA-00278: log file '/oracle/archive/1_13_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355369 generated at 01/14/2013 22:50:21 needed for thread

ORA-00289: suggestion : /oracle/archive/1_15_804715506.dbf

ORA-00280: change 2781355369 for thread 1 is in sequence #15

ORA-00278: log file '/oracle/archive/1_14_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355371 generated at 01/14/2013 22:50:22 needed for thread

ORA-00289: suggestion : /oracle/archive/1_16_804715506.dbf

ORA-00280: change 2781355371 for thread 1 is in sequence #16

ORA-00278: log file '/oracle/archive/1_15_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355373 generated at 01/14/2013 22:50:22 needed for thread

ORA-00289: suggestion : /oracle/archive/1_17_804715506.dbf

ORA-00280: change 2781355373 for thread 1 is in sequence #17

ORA-00278: log file '/oracle/archive/1_16_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355375 generated at 01/14/2013 22:50:23 needed for thread

ORA-00289: suggestion : /oracle/archive/1_18_804715506.dbf

ORA-00280: change 2781355375 for thread 1 is in sequence #18

ORA-00278: log file '/oracle/archive/1_17_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355377 generated at 01/14/2013 22:50:24 needed for thread

ORA-00289: suggestion : /oracle/archive/1_19_804715506.dbf

ORA-00280: change 2781355377 for thread 1 is in sequence #19

ORA-00278: log file '/oracle/archive/1_18_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355379 generated at 01/14/2013 22:50:24 needed for thread

ORA-00289: suggestion : /oracle/archive/1_20_804715506.dbf

ORA-00280: change 2781355379 for thread 1 is in sequence #20

ORA-00278: log file '/oracle/archive/1_19_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355381 generated at 01/14/2013 22:50:25 needed for thread

ORA-00289: suggestion : /oracle/archive/1_21_804715506.dbf

ORA-00280: change 2781355381 for thread 1 is in sequence #21

ORA-00278: log file '/oracle/archive/1_20_804715506.dbf' no longer needed for

this recovery

ORA-00279: change 2781355383 generated at 01/14/2013 22:50:27 needed for thread

ORA-00289: suggestion : /oracle/archive/1_22_804715506.dbf

ORA-00280: change 2781355383 for thread 1 is in sequence #22

ORA-00278: log file '/oracle/archive/1_21_804715506.dbf' no longer needed for

this recovery

ORA-00308: cannot open archived log '/oracle/archive/1_22_804715506.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

注意：

1 这里提示22号归档不存在，以及结合数据文件头部rba的seq号为22不难推断seq号为22应该为恢复控制文件前数据库的当前联机日志。

2 通过oradebug dump redohdr 3 转储日志头部信息找出恢复控制文件前，数据库的日志信息，过程如下：

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump redohdr 3

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/admin/CRM/udump/crm_ora_5103.trc

下面为截取trace文件的一部分信息：

LOG FILE #1:

(name #1) /oracle/app/db1/dbs/log1CRM.dbf

(name #2) /oracle/CRM2/CRM/redo01b.log

descrip:"Thread 0001, Seq# 0000000020, SCN 0x0000a5c81d73-0x0000a5c81d75"

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

LOG FILE #2:

(name #11) /oracle/app/db1/dbs/log2CRM.dbf

(name #12) /oracle/CRM2/CRM/redo02b.log

descrip:"Thread 0001, Seq# 0000000021, SCN 0x0000a5c81d75-0x0000a5c81d77"

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

LOG FILE #3:

(name #9) /oracle/CRM2/CRM/redo03.log

(name #10) /oracle/CRM2/CRM/redo03b.log

descrip:"Thread 0001, Seq# 0000000022, SCN 0x0000a5c81d77-0xffffffffffff"

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

LOG FILE #4:

(name #3) /oracle/CRM2/CRM/redo04.log

(name #4) /oracle/CRM2/CRM/redo04b.log

descrip:"Thread 0001, Seq# 0000000019, SCN 0x0000a5c81d71-0x0000a5c81d73"

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

LOG FILE #5:

(name #7) /oracle/CRM2/CRM/redo05.log

(name #8) /oracle/CRM2/CRM/redo05b.log

descrip:"Thread 0001, Seq# 0000000017, SCN 0x0000a5c81d6d-0x0000a5c81d6f"

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

LOG FILE #6:

(name #5) /oracle/CRM2/CRM/redo06.log

(name #6) /oracle/CRM2/CRM/redo06b.log

descrip:"Thread 0001, Seq# 0000000018, SCN 0x0000a5c81d6f-0x0000a5c81d71"

根据以上信息不难推断恢复控制文件前数据库当前日志序列号如下

LOG FILE #5 seq=17；

LOG FILE #6 seq=18；

LOG FILE #4 seq=19；

LOG FILE #1 seq=20；

LOG FILE #2 seq=21；

LOG FILE #3 seq=22；该日志为当前联机日志，肯定未归档，所以我们进行交互式恢复时指定日志文件名/oracle/CRM2/CRM/redo03.log即可。

输入auto恢复后验证如下：

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

BACKUP 16323 2781355383 2781347284

SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;

HXFIL FHCSQ FHSCN FHRBA_SEQ FHCPC

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

1 16319 2781355383 22 1838

2 16319 2781355383 22 1838

3 16319 2781355383 22 1831

4 16319 2781355383 22 1837

5 16319 2781355383 22 1836

6 16319 2781355383 22 734

7 16319 2781355383 22 1836

8 16319 2781355383 22 284

11 16319 2781355383 22 498

12 16319 2781355383 22 418

13 16319 2781355383 22 390

11 rows selected.

通过以上两个查询不难发现：

Controlfile_sequence#=16323 大于数据文件头部记录的控制文件seq号 fhcsq=16319

Controlfile_change#=2781355383等于数据文件头部的scn值fhscn=2781355383

但实际上恢复还没有结束，数据库还有不一致的地方，我们还需要归档或日志继续推进。如下转储控制文件后整理表格：

SQL> oradebug setmypid

Statement processed.

SQL> oradebug dump controlf 4;

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/admin/CRM/udump/crm_ora_5856.trc

编号	检查点计数值		scn
数据文件编号	数据文件头部记录的检查点计数值	控制文件记录数据文件头部的检查点计数值	数据文件头部scn	控制文件记录数据文件stop scn
1	1838	1826	2781355383	Null
2	1838	1826	2781355383	Null
3	1831	1819	2781355383	Null
4	1837	1825	2781355383	Null
5	1836	1824	2781355383	Null
6	734	722	2781355383	Null
7	1836	1824	2781355383	Null
8	284	272	2781355383	Null
11	498	486	2781355383	Null
12	418	406	2781355383	Null
13	390	378	2781355383	Null

SQL> recover database using backup controlfile;

ORA-00279: change 2781355383 generated at 01/14/2013 22:50:27 needed for thread 1

ORA-00289: suggestion : /oracle/archive/1_22_804715506.dbf

ORA-00280: change 2781355383 for thread 1 is in sequence #22

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/oracle/CRM2/CRM/redo03.log 手动输入seq号为22的日志文件

Log applied.

Media recovery complete.

注意：可以不用转储日志文件头部，来推恢复控制文件前据库的日志序列号。可以反复尝试输入各个日志组文件直到Media recovery complete为止。

验证：

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

BACKUP 16353 2781355949 2781347284

SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;

HXFIL FHCSQ FHSCN FHRBA_SEQ FHCPC

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

1 16349 2781355949 22 1840

2 16349 2781355949 22 1840

3 16349 2781355949 22 1833

4 16349 2781355949 22 1839

5 16349 2781355949 22 1838

6 16349 2781355949 22 736

7 16349 2781355949 22 1838

8 16349 2781355949 22 286

11 16349 2781355949 22 500

12 16349 2781355949 22 420

13 16349 2781355949 22 392

11 rows selected.

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#

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

2781355949 2781355949

11 rows selected.

SQL> alter database open resetlogs;

Database altered.

三用旧控制文件恢复后，数据库的恢复步骤（rman下恢复）

1 启动库到nomount状态：

RMAN> startup force nomount;

released channel: ORA_DISK_1

Oracle instance started

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 96471936 bytes

Database Buffers 218103808 bytes

Redo Buffers 6365184 bytes

2 从旧的控制文件备份恢复控制文件：

RMAN> restore controlfile from '/oracle/app/db1/dbs/01nve87c_1_1';

Starting restore at 15-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/oracle/CRM2/CRM/control01.ctl

output filename=/oracle/CRM2/CRM/control02.ctl

Finished restore at 15-JAN-13

3 启动数据库到加载状态：

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

4 recover 数据库：

RMAN> recover database;

Starting recover at 15-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=211 devtype=DISK

starting media recovery

archive log thread 1 sequence 17 is already on disk as file /oracle/CRM2/CRM/redo05.log

archive log thread 1 sequence 18 is already on disk as file /oracle/CRM2/CRM/redo06.log

archive log thread 1 sequence 19 is already on disk as file /oracle/CRM2/CRM/redo04.log

archive log thread 1 sequence 20 is already on disk as file /oracle/CRM2/CRM/redo01b.log

archive log thread 1 sequence 21 is already on disk as file /oracle/CRM2/CRM/redo02b.log

archive log thread 1 sequence 22 is already on disk as file /oracle/CRM2/CRM/redo03.log

archive log filename=/oracle/archive/1_9_804715506.dbf thread=1 sequence=9

archive log filename=/oracle/archive/1_10_804715506.dbf thread=1 sequence=10

archive log filename=/oracle/archive/1_11_804715506.dbf thread=1 sequence=11

archive log filename=/oracle/archive/1_12_804715506.dbf thread=1 sequence=12

archive log filename=/oracle/archive/1_13_804715506.dbf thread=1 sequence=13

archive log filename=/oracle/archive/1_14_804715506.dbf thread=1 sequence=14

archive log filename=/oracle/archive/1_15_804715506.dbf thread=1 sequence=15

archive log filename=/oracle/archive/1_16_804715506.dbf thread=1 sequence=16

archive log filename=/oracle/CRM2/CRM/redo05.log thread=1 sequence=17

archive log filename=/oracle/CRM2/CRM/redo06.log thread=1 sequence=18

archive log filename=/oracle/CRM2/CRM/redo04.log thread=1 sequence=19

archive log filename=/oracle/CRM2/CRM/redo01b.log thread=1 sequence=20

archive log filename=/oracle/CRM2/CRM/redo02b.log thread=1 sequence=21

archive log filename=/oracle/CRM2/CRM/redo03.log thread=1 sequence=22

media recovery complete, elapsed time: 00:00:02

Finished recover at 15-JAN-13

注意

1 rman恢复步骤中调用了9-16号归档，以及所有联机日志。而我们在sql下采用交互方式恢复时，调用了9-21号归档外加我们推算出的seq号为22号的联机日志

2 对比不难发现rman的恢复步骤比sql下的交互方式简单多，起码不用我们转储日志头部找之前数据库当前联机日志文件。

5 验证

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

BACKUP 16353 2781355949 2781347284

SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;

HXFIL FHCSQ FHSCN FHRBA_SEQ FHCPC

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

1 16349 2781355949 22 1840

2 16349 2781355949 22 1840

3 16349 2781355949 22 1833

4 16349 2781355949 22 1839

5 16349 2781355949 22 1838

6 16349 2781355949 22 736

7 16349 2781355949 22 1838

8 16349 2781355949 22 286

11 16349 2781355949 22 500

12 16349 2781355949 22 420

13 16349 2781355949 22 392

11 rows selected.

SQL> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#

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

2781347284 2781355949

2781348210 2781355949

2781347284 2781355949

11 rows selected.

SQL> alter database open resetlogs;

Database altered.

四如果在恢复过程中丢失了归档如何处理

1 旧控制文件恢复后查询相关信息如下：

SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;

CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#

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

BACKUP 16427 2781361723 2781361700

SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;

HXFIL FHCSQ FHSCN FHRBA_SEQ

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

1 16518 2781361775 32

2 16518 2781361775 32

3 16518 2781361775 32

4 16518 2781361775 32

5 16518 2781361775 32

6 16518 2781361775 32

7 16518 2781361775 32

8 16518 2781361775 32

11 16518 2781361775 32

12 16518 2781361775 32

13 16518 2781361775 32

注意

a CONTROLFILE_CHANGE#=2781361723 此值位于哪个归档low scn 和next scn间决定恢复应用归档的seq号。

b 数据文件头部的FHRBA_SEQ=32 决定了恢复需应用的最后一个归档seq号为32

2 恢复过程如下：

SQL> recover database using backup controlfile;

ORA-00279: change 2781361723 generated at 01/15/2013 12:41:34 needed for thread

ORA-00289: suggestion : /oracle/archive/1_9_804762490.dbf

ORA-00280: change 2781361723 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2781361731 generated at 01/15/2013 12:42:30 needed for thread

ORA-00289: suggestion : /oracle/archive/1_10_804762490.dbf

ORA-00280: change 2781361731 for thread 1 is in sequence #10

ORA-00278: log file '/oracle/archive/1_9_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361733 generated at 01/15/2013 12:42:31 needed for thread

ORA-00289: suggestion : /oracle/archive/1_11_804762490.dbf

ORA-00280: change 2781361733 for thread 1 is in sequence #11

ORA-00278: log file '/oracle/archive/1_10_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361735 generated at 01/15/2013 12:42:32 needed for thread

ORA-00289: suggestion : /oracle/archive/1_12_804762490.dbf

ORA-00280: change 2781361735 for thread 1 is in sequence #12

ORA-00278: log file '/oracle/archive/1_11_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361737 generated at 01/15/2013 12:42:33 needed for thread

ORA-00289: suggestion : /oracle/archive/1_13_804762490.dbf

ORA-00280: change 2781361737 for thread 1 is in sequence #13

ORA-00278: log file '/oracle/archive/1_12_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361739 generated at 01/15/2013 12:42:33 needed for thread

ORA-00289: suggestion : /oracle/archive/1_14_804762490.dbf

ORA-00280: change 2781361739 for thread 1 is in sequence #14

ORA-00278: log file '/oracle/archive/1_13_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361741 generated at 01/15/2013 12:42:34 needed for thread

ORA-00289: suggestion : /oracle/archive/1_15_804762490.dbf

ORA-00280: change 2781361741 for thread 1 is in sequence #15

ORA-00278: log file '/oracle/archive/1_14_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361743 generated at 01/15/2013 12:42:35 needed for thread

ORA-00289: suggestion : /oracle/archive/1_16_804762490.dbf

ORA-00280: change 2781361743 for thread 1 is in sequence #16

ORA-00278: log file '/oracle/archive/1_15_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361745 generated at 01/15/2013 12:42:35 needed for thread

ORA-00289: suggestion : /oracle/archive/1_17_804762490.dbf

ORA-00280: change 2781361745 for thread 1 is in sequence #17

ORA-00278: log file '/oracle/archive/1_16_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361747 generated at 01/15/2013 12:42:36 needed for thread

ORA-00289: suggestion : /oracle/archive/1_18_804762490.dbf

ORA-00280: change 2781361747 for thread 1 is in sequence #18

ORA-00278: log file '/oracle/archive/1_17_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361749 generated at 01/15/2013 12:42:36 needed for thread

ORA-00289: suggestion : /oracle/archive/1_19_804762490.dbf

ORA-00280: change 2781361749 for thread 1 is in sequence #19

ORA-00278: log file '/oracle/archive/1_18_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361751 generated at 01/15/2013 12:42:37 needed for thread

ORA-00289: suggestion : /oracle/archive/1_20_804762490.dbf

ORA-00280: change 2781361751 for thread 1 is in sequence #20

ORA-00278: log file '/oracle/archive/1_19_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361753 generated at 01/15/2013 12:42:38 needed for thread

ORA-00289: suggestion : /oracle/archive/1_21_804762490.dbf

ORA-00280: change 2781361753 for thread 1 is in sequence #21

ORA-00278: log file '/oracle/archive/1_20_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361755 generated at 01/15/2013 12:42:38 needed for thread

ORA-00289: suggestion : /oracle/archive/1_22_804762490.dbf

ORA-00280: change 2781361755 for thread 1 is in sequence #22

ORA-00278: log file '/oracle/archive/1_21_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361757 generated at 01/15/2013 12:42:39 needed for thread

ORA-00289: suggestion : /oracle/archive/1_23_804762490.dbf

ORA-00280: change 2781361757 for thread 1 is in sequence #23

ORA-00278: log file '/oracle/archive/1_22_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361759 generated at 01/15/2013 12:42:39 needed for thread

ORA-00289: suggestion : /oracle/archive/1_24_804762490.dbf

ORA-00280: change 2781361759 for thread 1 is in sequence #24

ORA-00278: log file '/oracle/archive/1_23_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361761 generated at 01/15/2013 12:42:40 needed for thread

ORA-00289: suggestion : /oracle/archive/1_25_804762490.dbf

ORA-00280: change 2781361761 for thread 1 is in sequence #25

ORA-00278: log file '/oracle/archive/1_24_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361763 generated at 01/15/2013 12:42:41 needed for thread

ORA-00289: suggestion : /oracle/archive/1_26_804762490.dbf

ORA-00280: change 2781361763 for thread 1 is in sequence #26

ORA-00278: log file '/oracle/archive/1_25_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361765 generated at 01/15/2013 12:42:41 needed for thread

ORA-00289: suggestion : /oracle/archive/1_27_804762490.dbf

ORA-00280: change 2781361765 for thread 1 is in sequence #27

ORA-00278: log file '/oracle/archive/1_26_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361767 generated at 01/15/2013 12:42:42 needed for thread

ORA-00289: suggestion : /oracle/archive/1_28_804762490.dbf

ORA-00280: change 2781361767 for thread 1 is in sequence #28

ORA-00278: log file '/oracle/archive/1_27_804762490.dbf' no longer needed for

this recovery

ORA-00279: change 2781361769 generated at 01/15/2013 12:42:42 needed for thread

ORA-00289: suggestion : /oracle/archive/1_29_804762490.dbf

ORA-00280: change 2781361769 for thread 1 is in sequence #29

ORA-00278: log file '/oracle/archive/1_28_804762490.dbf' no longer needed for

this recovery

ORA-00308: cannot open archived log '/oracle/archive/1_29_804762490.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

11 rows selected.

恢复退出，这里提示找不到29号归档，正常情况应该应用完31号归档，提示找不到32号归档。

3备份控制文件找出重建控制文件语句：

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/oracle/app/admin/CRM/udump/crm_ora_6501.trc

SQL> alter database backup controlfile to trace;

Vi 编辑/oracle/app/admin/CRM/udump/crm_ora_6501.trc文件找出控制文件重建语句

语句如下：

[oracle@oracle ~]$ cat control.txt

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

'/oracle/app/db1/dbs/log1CRM.dbf',

'/oracle/CRM2/CRM/redo01b.log'

) SIZE 200M,

GROUP 2 (

'/oracle/app/db1/dbs/log2CRM.dbf',

'/oracle/CRM2/CRM/redo02b.log'

) SIZE 50M,

GROUP 3 (

'/oracle/CRM2/CRM/redo03.log',

'/oracle/CRM2/CRM/redo03b.log'

) SIZE 200M,

GROUP 4 (

'/oracle/CRM2/CRM/redo04.log',

'/oracle/CRM2/CRM/redo04b.log'

) SIZE 200M,

GROUP 5 (

'/oracle/CRM2/CRM/redo05.log',

'/oracle/CRM2/CRM/redo05b.log'

) SIZE 200M,

GROUP 6 (

'/oracle/CRM2/CRM/redo06.log',

'/oracle/CRM2/CRM/redo06b.log'

) SIZE 200M

DATAFILE

'/oracle/test/system1.dbf',

'/oracle/test/zxb.dbf',

'/oracle/test/sysaux01.dbf',

'/oracle/test/users01.dbf',

'/oracle/test/zxa.dbf',

'/oracle/test/test1.dbf',

'/oracle/test/zxc.dbf',

'/oracle/test/undotbs1.dbf',

'/oracle/test/jiujian1.dbf',

'/oracle/test/undotbs4.dbf',

'/oracle/test/test2.dbf'

CHARACTER SET ZHS16GBK

;

4 重建及恢复过程如下：

SQL> shutdown abort;

ORACLE instance shut down.

SQL> @/oracle/control.txt

ORACLE instance started.

Total System Global Area 322961408 bytes

Fixed Size 2020480 bytes

Variable Size 96471936 bytes

Database Buffers 218103808 bytes

Redo Buffers 6365184 bytes

Control file created.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

------------------------------------------------------完--------------------------------------------------

总结：

1 用控制文件的旧备份恢复控制文件后，值CONTROLFILE_CHANGE#位于哪个归档low scn 和next scn值之间则该归档的seq号为恢复应用起始归档。

2 用控制文件的旧备份恢复控制文件后，如果数据文件没有用旧文件恢复过，则其头部的FHRBA_SEQ=32 决定了恢复需应用的最后一个归档seq号。

3 通过对比sql下交互式恢复和rman下恢复，不难发现rman下恢复比较简单，不用我们去推恢复控制文件之前数据库的日志seq号。

4 oracle判断控制文件新旧的详细机制我不得而知，不过如果是旧的控制文件控制文件的seq号一定会小于数据文件头部记录的控制文件seq号，转储控制文件也可以发现控制文件头部被标记为backup controlfile。