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
二 用旧控制文件恢复后,数据库的恢复步骤(交互方式)
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
2781355949 2781355949
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
2781347284 2781355949
2781347284 2781355949
2781347284 2781355949
2781347284 2781355949
2781347284 2781355949
2781347284 2781355949
2781347284 2781355949
2781348210 2781355949
2781347284 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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
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。