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 5013 1764609 1764609
-------資料檔案
select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
hxfil :數據文件編號
fhcsq：數據文件頭部記錄的控製文件seq號
fhscn：數據文件頭部的scn號
fhrba_seq：數據文件頭部rba地址中的日誌序列號
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 5010 1764609 122
2 5010 1764609 122
3 5010 1764609 122
4 5010 1764609 122
------------------------------------------------------------------------以上是合理狀況
但是有一次接收到一個錯誤以下訊息
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
1.以下來解決這個問題...
先查看目前訊息
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 4992 1763521 1764292
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 5013 1764971 122
2 5013 1764971 122
3 5013 1764971 122
4 5013 1764971 122
上面查看控制檔案sequence碼是4992 ,但是資料檔案是5013 ,已經錯誤了我以上說法
所以必須要透過幾個方式來還原
>>>>>還原
透過recover 讓控制檔案將號碼追到
SQL> recover database using backup controlfile;
ORA-00279: change 1764292 generated at 07/20/2018 02:51:01 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_119_fo1qk3
t5_.arc
ORA-00280: change 1764292 for thread 1 is in sequence #119
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1764603 generated at 07/20/2018 02:53:23 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_120_fo1qk5
3f_.arc
ORA-00280: change 1764603 for thread 1 is in sequence #120
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_119_fo1qk
3t5_.arc' no longer needed for this recovery
ORA-00279: change 1764606 generated at 07/20/2018 02:53:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_121_fo1qk7
p2_.arc
ORA-00280: change 1764606 for thread 1 is in sequence #121
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_120_fo1qk
53f_.arc' no longer needed for this recovery
ORA-00279: change 1764609 generated at 07/20/2018 02:53:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_122_%u_.ar
c
ORA-00280: change 1764609 for thread 1 is in sequence #122
ORA-00278: log file
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_121_fo1qk
7p2_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_122_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
>>找不到122 這檔案 (可能在redo 內)
透過以下方式去查找
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump redohdr 3
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3310.trc
查看了一下
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3310.trc
發現編號122在 redo2內
name #2: /u01/app/oracle/orcl/redo02.log
descrip:"Thread 0001, Seq# 0000000122, SCN 0x0000001aed01-0xffffffffffff"
>>>恢復122 編號redo
SQL> recover database using backup controlfile;
ORA-00279: change 1764609 generated at 07/20/2018 02:53:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_122_%u_.ar
c
ORA-00280: change 1764609 for thread 1 is in sequence #122
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/orcl/redo02.log
Log applied.
Media recovery complete.
>>>開啟資料庫 --正常
SQL> alter database open resetlogs;
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 5052 1765211 1764975
SQL> select hxfil,fhcsq,fhscn,fhrba_seq,fhcpc from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ FHCPC
---------- ---------- ---------------- ---------- ----------
1 5040 1764975 1 138
2 5040 1764975 1 138
3 5040 1764975 1 138
4 5040 1764975 1 137
>>>證明如果controlfile 編號比資料檔案還低 就會出現這種錯誤
--------------------------------------------------------------------------------------------第二種方式恢復(透過Rman)
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
BACKUP 5087 1765499 1764975
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 5120 1766028 8
2 5120 1766028 8
3 5120 1766028 8
4 5120 1766028 8
這邊至少要到編號8...
>>rman target
RMAN> recover database
2> ;
Starting recover at 2018/07/20 03:31:26
Starting implicit crosscheck backup at 2018/07/20 03:31:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 2018/07/20 03:31:27
Starting implicit crosscheck copy at 2018/07/20 03:31:27
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 2018/07/20 03:31:27
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_6_fo1s8mgw_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_4_fo1s8l6p_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_3_fo1s8kjs_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_7_fo1s8n3o_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_2_fo1s8gkq_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_5_fo1s8ls9_.arc
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_2_fo1s8gkq_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_3_fo1s8kjs_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_4_fo1s8l6p_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_5_fo1s8ls9_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_6_fo1s8mgw_.arc
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_7_fo1s8n3o_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/orcl/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_2_fo1s8gkq_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_3_fo1s8kjs_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_4_fo1s8l6p_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_5_fo1s8ls9_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_6_fo1s8mgw_.arc thread=1 sequence=6
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_07_20/o1_mf_1_7_fo1s8n3o_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/orcl/redo02.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:00
Finished recover at 2018/07/20 03:31:28
RMAN> alter database open resetlogs
2> ;
database opened
SQL> select controlfile_type,controlfile_sequence#,controlfile_change#,checkpoint_change# from v$database;
CONTROL CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------- --------------------- ------------------- ------------------
CURRENT 5177 1766416 1766032
SQL> select hxfil,fhcsq,fhscn,fhrba_seq from x$kcvfh;
HXFIL FHCSQ FHSCN FHRBA_SEQ
---------- ---------- ---------------- ----------
1 5163 1766032 1
2 5163 1766032 1
3 5163 1766032 1
4 5163 1766032 1
>>>完成
