溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
BBED丟失歸檔文件情況下的恢復(fù)方法，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。
【BBED】丟失歸檔文件情況下的數(shù)據(jù)文件的恢復(fù)
各位技術(shù)愛好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識(shí)，~O(∩_∩)O~：
① 若丟失歸檔情況下數(shù)據(jù)文件的恢復(fù)，bbed和隱含參數(shù)（重點(diǎn)）
② 數(shù)據(jù)庫(kù)啟動(dòng)過程中的介質(zhì)恢復(fù)，scn號(hào)的關(guān)系
③ BBED如何修改文件頭
④ 歸檔和非歸檔模式下數(shù)據(jù)庫(kù)的全備
Tips：
① 若文章代碼格式有錯(cuò)亂，推薦使用QQ、搜狗或360瀏覽器，也可以下載pdf格式的文檔來查看，pdf文檔下載地址：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）
② 本篇BLOG中命令的輸出部分需要特別關(guān)注的地方我都用灰色背景和粉紅色字體來表示，比如下邊的例子中，thread 1的最大歸檔日志號(hào)為33，thread 2的最大歸檔日志號(hào)為43是需要特別關(guān)注的地方；而命令一般使用黃色背景和紅色字體標(biāo)注；對(duì)代碼或代碼輸出部分的注釋一般采用藍(lán)色字體表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZFXXDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZFXXDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有錯(cuò)誤或不完善的地方請(qǐng)大家多多指正，ITPUB留言或QQ皆可，您的批評(píng)指正是我寫作的最大動(dòng)力。
ORACLE 11g TSPITR恢復(fù)被刪除的表空間http://blog.itpub.net/26736162/viewspace-1681706/
【RMAN】RMAN腳本中使用替換變量--windows 下rman全備腳本http://blog.itpub.net/26736162/viewspace-1673725/
【TSPITR】RMAN表空間基于時(shí)間點(diǎn)的自動(dòng)恢復(fù)http://blog.itpub.net/26736162/viewspace-1671741/
【推薦】 【RMAN】rm -rf 誤操作的恢復(fù)過程http://blog.itpub.net/26736162/viewspace-1623938/
【推薦】 【RMAN】利用備份片還原數(shù)據(jù)庫(kù)(中)-附加http://blog.itpub.net/26736162/viewspace-1621938/
【推薦】 【RMAN】利用備份片還原數(shù)據(jù)庫(kù)(下)http://blog.itpub.net/26736162/viewspace-1621672/
【推薦】 【RMAN】利用備份片還原數(shù)據(jù)庫(kù)(中)http://blog.itpub.net/26736162/viewspace-1621661/
【推薦】 【RMAN】利用備份片還原數(shù)據(jù)庫(kù)(上)http://blog.itpub.net/26736162/viewspace-1621581/
【推薦】 【RMAN】RMAN跨版本恢復(fù)(下)http://blog.itpub.net/26736162/viewspace-1562583/
【推薦】 Oracle 組件 系列 小結(jié)http://blog.itpub.net/26736162/viewspace-1562441/
【推薦】 【RMAN】RMAN跨版本恢復(fù)(中)http://blog.itpub.net/26736162/viewspace-1561352/
【推薦】 【RMAN】RMAN跨版本恢復(fù)(上)http://blog.itpub.net/26736162/viewspace-1561185/
【推薦】 關(guān)于在不同版本和平臺(tái)之間進(jìn)行還原或復(fù)制的常見問題http://blog.itpub.net/26736162/viewspace-1549041/
【推薦】 undo表空間文件丟失恢復(fù)（4）--無備份無recover的情況下恢復(fù)http://blog.itpub.net/26736162/viewspace-1458787/
【推薦】 undo表空間文件丟失恢復(fù)（3）--無備份無redo的情況下恢復(fù)http://blog.itpub.net/26736162/viewspace-1458750/
【推薦】 undo表空間文件丟失恢復(fù)（2）--無備份有redo的情況下恢復(fù)http://blog.itpub.net/26736162/viewspace-1458663/
【推薦】 undo表空間文件丟失恢復(fù)（1）--有備份http://blog.itpub.net/26736162/viewspace-1458654/
【推薦】 oracle控制文件在缺失歸檔日志的情況下的恢復(fù)http://blog.itpub.net/26736162/viewspace-1426552/
【推薦】 ORACLE 只讀數(shù)據(jù)文件備份與恢復(fù)http://blog.itpub.net/26736162/viewspace-1425283/
前段時(shí)間公司小y給我們培訓(xùn)了下dul恢復(fù)truncate的數(shù)據(jù)，接下來幾天我一直在研究truncate的恢復(fù)，想總結(jié)一下truncate的恢復(fù)方法，但是碰到了BBED，可以通過BBED來恢復(fù)，可是這個(gè)工具不熟悉，之前沒用過，其實(shí)老早就聽說了這個(gè)工具，一直想學(xué)BBED，但因?yàn)橐恢睕]碰到實(shí)際用途，也一直沒有學(xué)習(xí)，現(xiàn)在碰到了就先研究了BBED了，truncate的恢復(fù)過段時(shí)間再發(fā)blog吧。
本文先給大家介紹了下數(shù)據(jù)庫(kù)啟動(dòng)過程中的介質(zhì)恢復(fù)的一些知識(shí)點(diǎn)，然后介紹了BBED修改數(shù)據(jù)文件頭的辦法推進(jìn)SCN號(hào)來實(shí)現(xiàn)完全恢復(fù)，而不用resetlogs來打開庫(kù)，利用隱含參數(shù)_allow_resetlogs_corruption來打開數(shù)據(jù)庫(kù)只是一個(gè)插曲。
我們做實(shí)驗(yàn)的時(shí)候分2種情況來實(shí)驗(yàn)，一種是linux環(huán)境，一種是aix環(huán)境，linux下我們采用bbed及隱含參數(shù)_allow_resetlogs_corruption來恢復(fù)，其中推進(jìn)scn號(hào)的時(shí)候直接推進(jìn)到最新的scn號(hào)，和其它的數(shù)據(jù)文件頭的scn號(hào)保持一致，aix環(huán)境下我們采用刪除其中的一個(gè)歸檔文件，修改scn的時(shí)候修改到丟失的歸檔文件的scn號(hào)，這樣可以模擬盡可能少的數(shù)據(jù)丟失的情況，加深對(duì)redo apply的進(jìn)一步了解。
這個(gè)章節(jié)相關(guān)知識(shí)點(diǎn)還是需要了解一下的，小麥苗已經(jīng)整理好了，和以往blog不太一樣，內(nèi)容有點(diǎn)多。
scn號(hào)與oracle數(shù)據(jù)庫(kù)恢復(fù)過程有著密切的關(guān)系，只有很好地理解了這層關(guān)系，才能深刻地理解恢復(fù)的原理，從而才能很好地解決這方面的問題。
CKPT進(jìn)程在checkpoint發(fā)生時(shí)，將當(dāng)時(shí)的SCN號(hào)寫入數(shù)據(jù)文件頭和控制文件，同時(shí)通知DBWR進(jìn)程將數(shù)據(jù)塊寫到數(shù)據(jù)文件。
CKPT進(jìn)程也會(huì)在控制文件中記錄RBA(redo block address),以標(biāo)志Recovery需要從日志中哪個(gè)地方開始。
與checkpoint相關(guān)的SCN號(hào)有四個(gè)，其中三個(gè)存在控制文件中，一個(gè)存放在數(shù)據(jù)文件頭中。
這四個(gè)分別是：
1.System Checkpoint SCN
當(dāng)checkpoint完成后，ORACLE將System Checkpoint SCN號(hào)存放在控制文件中。
我們可以通過下面SQL語(yǔ)句查詢：
select checkpoint_change# from v$database;
2.Datafile Checkpoint SCN
當(dāng)checkpoint完成后，ORACLE將Datafile Checkpoint SCN號(hào)存放在控制文件中。
我們可以通過下面SQL語(yǔ)句查詢所有數(shù)據(jù)文件的Datafile Checkpoinnt SCN號(hào)。
select name,checkpoint_change# from v$datafile;
3.Start SCN號(hào)
ORACLE將Start SCN號(hào)存放在數(shù)據(jù)文件頭中。
這個(gè)SCN用于檢查數(shù)據(jù)庫(kù)啟動(dòng)過程是否需要做media recovery.
我們可以通過以下SQL語(yǔ)句查詢：
select name,checkpoint_change# from v$datafile_header;
4.End SCN號(hào)
ORACLE將End SCN號(hào)存放在控制文件中。
這個(gè)SCN號(hào)用于檢查數(shù)據(jù)庫(kù)啟動(dòng)過程是否需要做instance recovery.
我們可以通過以下SQL語(yǔ)句查詢：
select name,last_change# from v$datafile;
在數(shù)據(jù)庫(kù)正常運(yùn)行的情況下，對(duì)可讀寫的，online的數(shù)據(jù)文件，該SCN號(hào)為NULL.
select checkpoint_change# from v$database;
SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
select file#,online_status,change# from v$recover_file;
在數(shù)據(jù)庫(kù)啟動(dòng)過程中，當(dāng)System Checkpoint SCN、Datafile Checkpoint SCN和Start SCN號(hào)都相同時(shí)，數(shù)據(jù)庫(kù)可以正常啟動(dòng)，不需要做media recovery.三者當(dāng)中有一個(gè)不同時(shí)，則需要做media recovery.
ORACLE在啟動(dòng)過程中首先檢查是否需要media recovery，然后再檢查是否需要instance recovery.
如果數(shù)據(jù)庫(kù)的正常關(guān)閉的話，將會(huì)觸發(fā)一個(gè)checkpoint，同時(shí)將數(shù)據(jù)文件的END SCN號(hào)設(shè)置為相應(yīng)數(shù)據(jù)文件的Start SCN號(hào)。當(dāng)數(shù)據(jù)庫(kù)啟動(dòng)時(shí)，發(fā)現(xiàn)它們是一致的，則不需要做instance recovery。在數(shù)據(jù)庫(kù)正常啟動(dòng)后，ORACLE會(huì)將END SCN號(hào)設(shè)置為NULL.
如果數(shù)據(jù)庫(kù)異常關(guān)閉的話，則END SCN號(hào)將為NULL.則需要做instance recovery.
為什么ORACLE會(huì)在控制文件中記錄System checkpoint SCN號(hào)的同時(shí)，還需要為每個(gè)數(shù)據(jù)文件記錄Datafile Checkpoint SCN號(hào)？
原因有二：
1.對(duì)只讀表空間，其數(shù)據(jù)文件的Datafile Checkpoint SCN、Start SCN和END SCN號(hào)均相同。
這三個(gè)SCN在表空間處于只讀期間都將被凍結(jié)。
2.如果控制文件不是當(dāng)前的控制文件，則System checkpoint會(huì)小于Start SCN或END SCN號(hào)。
記錄這些SCN號(hào)，可以區(qū)分控制文件是否是當(dāng)前的控制文件。
當(dāng)有一個(gè)Start SCN號(hào)超過了System Checkpoint SCN號(hào)時(shí)，則說明控制文件不是當(dāng)前的控制文件，因此在做recover時(shí)需要采用using backup controlfile。這是為什么需要記錄SystemCheckpoint SCN的原因之一。
這里需要一提的是，當(dāng)重建控制文件的時(shí)候，System Checkpoint SCN為0，Datafile Checkpoint SCN的數(shù)據(jù)來自于Start SCN。根據(jù)上述的描述，此時(shí)需要采用using backup controlfile做recovery.
一個(gè)表空間的數(shù)據(jù)文件損壞，使用以前的備份進(jìn)行恢復(fù)，但是需要的歸檔文件無法提供，而且該表空間存放的是歷史的數(shù)據(jù)，很少改變，但由于其它的原因（定時(shí)的往該表空間存儲(chǔ)新數(shù)據(jù)），無法變成只讀模式。
這種情況下由于缺少歸檔，數(shù)據(jù)庫(kù)無法recovery，但是跟該表空間相關(guān)的數(shù)據(jù)改變很少或者沒有，這種情況下我們可以通過改變數(shù)據(jù)文件頭的檢查點(diǎn)號(hào)，讓oracle避開對(duì)該文件的檢查，實(shí)現(xiàn)完全恢復(fù)，及時(shí)打開數(shù)據(jù)庫(kù)。
1、 數(shù)據(jù)庫(kù)異常斷電，導(dǎo)致users表空間的數(shù)據(jù)文件損壞
2、 從早期的備份中轉(zhuǎn)儲(chǔ)數(shù)據(jù)文件到目標(biāo)位置
3、 對(duì)數(shù)據(jù)庫(kù)進(jìn)行recovery，假如歸檔日志損壞，該表空間沒有被修改，但是沒有變成read only，數(shù)據(jù)庫(kù)無法做完全recovery，所以無法打開：
SQL> alter database open;
alter database open * ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/TIANJIN/users01.dbf'
SQL> recover database;
ORA-00279: change 1951719 generated at 08/25/2012 13:52:08 needed for thread 1 ORA-00289: suggestion :
/home/oracle/oracle/product/10.2.0/db_1/dbs/arch2_107_783745676.dbf
ORA-00280: change 1951719 for thread 1 is in sequence #107
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/home/oracle/oracle/product/10.2.0/db_1/dbs/arch2_107_783745676.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
4、 由于該表空間沒有被修改，歸檔日志里面應(yīng)該沒有記錄有關(guān)該表空間被改變的信息，但是oracle不知道，所以它必須要用到歸檔，才能夠進(jìn)行recovery，此時(shí)，我們的解決辦法是：把該表空間的數(shù)據(jù)文件頭的檢查點(diǎn)號(hào)改成跟控制文件記錄的一樣，那么oracle就不會(huì)做介質(zhì)恢復(fù)，只做實(shí)例恢復(fù)，則就能夠recovery成功，可以使用bbed來實(shí)現(xiàn)。
將數(shù)據(jù)庫(kù)文件copy到linux或aix的FS中，BBED修改完成后在copy回?cái)?shù)據(jù)庫(kù)的文件位置。
RBA就是redo entries在重做日志文件中所對(duì)應(yīng)的地址
A"Redo Block Address" (RBA) describes a physical location within aredo log file.
RBA 由以下三部分組成：
（1）the log file sequence number (4 bytes)
（2）the log file block number (4 bytes)
（3）the byte offset into the block at which the redo record starts (2bytes)
如：RBA[0x19.2.10] 代表 Log squence25, Block number 2 with byte offset 16.
注意這里的格式：都是16進(jìn)行。
Redo 與checkpoint 關(guān)系很大，當(dāng)設(shè)置log_checkpoints_to_alert 參數(shù)為true后，checkpoint 發(fā)生時(shí)會(huì)寫入alert log里。
項(xiàng)目 | db |
db 類型 | 單實(shí)例 |
db version | 11.2.0.3.0 |
db 存儲(chǔ) | FS |
主機(jī)IP地址/hosts配置 | 192.168.59.129 |
OS版本及kernel版本 | Linux rhel5 2.6.18-194.el5 64位 |
歸檔模式 | Archive Mode |
ORACLE_SID | ora11g |
首先我們OS級(jí)別刪除system文件，刪除所有歸檔文件，然后利用以前的備份來還原system文件，最后利用BBED修改文件頭，推進(jìn)SCN號(hào)來打開數(shù)據(jù)庫(kù)，另外一個(gè)實(shí)驗(yàn)是利用隱含參數(shù)_allow_resetlogs_corruption來打開數(shù)據(jù)庫(kù)，能正常打開數(shù)據(jù)庫(kù)就說明實(shí)驗(yàn)成功，關(guān)于丟不丟失數(shù)據(jù)這個(gè)另當(dāng)別論，多一種恢復(fù)手段總是好事。
首先，rman備份1號(hào)system數(shù)據(jù)文件：
[oracle@rhel5:/home/oracle]# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 7 17:00:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=8302811)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> backup datafile 1;
Starting backup at 2016-04-07 17:00:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:00:36
channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:49
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2016-04-07 17:03:26
channel ORA_DISK_1: finished piece 1 at 2016-04-07 17:03:29
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_ncsnf_TAG20160407T170035_cjd8lygf_.bkp tag=TAG20160407T170035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2016-04-07 17:03:29
RMAN>
刪除1號(hào)數(shù)據(jù)文件且刪除歸檔，重啟報(bào)錯(cuò)：
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:05:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g> drop table T_LHR_20160407_02 ;
drop table T_LHR_20160407_02
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@ora11g> create table T_LHR_20160407_02 as select * from all_objects;
Table created.
SYS@ora11g> update T_LHR_20160407_02 set object_id=100;
72458 rows updated.
SYS@ora11g> commit;
Commit complete.
SYS@ora11g> alter database datafile 1 offline;
alter database datafile 1 offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
====》 SYSTEM表空間不能被offline，普通文件可以，我們os級(jí)別刪除即可
SYS@ora11g> select count(1) from T_LHR_20160407_02;
COUNT(1)
----------
72458
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2217678
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2218426 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2217678 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2217678 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2217678 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2217678 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2217678 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2217678 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf NO 2218426 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2217678 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2217678 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2217678 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2217678 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2217678 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2217678 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
no rows selected
SYS@ora11g> alter system switch logfile;
/
/
System altered.
SYS@ora11g>
System altered.
SYS@ora11g>
System altered.
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2220979
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2220979 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2220979 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2220979 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2220979 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2220979 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2220979 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2220979 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf NO 2220979 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2220979 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2220979 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2220979 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2220979 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2220979 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2220979 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
no rows selected
====》切換日志后我們可以看到scn號(hào)都保持一致，都是2220979 ，下邊我們來刪除1號(hào)文件
SYS@ora11g> ! rm /u01/app/oracle/oradata/ora11g/system01.dbf
SYS@ora11g> alter system switch logfile;
System altered.
SYS@ora11g> /
System altered.
SYS@ora11g> /
====》切換日志過程中有一定幾率數(shù)據(jù)庫(kù)down掉，若沒有就shutdown abort掉，然后重啟
SYS@ora11g> shutdown abort
ORACLE instance shut down.
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2229864 bytes
Variable Size 390072728 bytes
Database Buffers 113246208 bytes
Redo Buffers 8036352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 0
SYS@ora11g>
SYS@ora11g> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SYS@ora11g>
SYS@ora11g> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4122M
recovery_parallelism integer 0
====》可以看到1號(hào)數(shù)據(jù)文件找不到，然后我們刪除歸檔，確保不能通過recover來恢復(fù)
[oracle@rhel5:/home/oracle]# cd /u01/app/oracle/fast_recovery_area/ORA11G/archivelog
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# ll
total 4
drwxr-x--- 2 oracle asmadmin 4096 Apr 7 17:09 2016_04_07
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# rm -rf *
[oracle@rhel5:/u01/app/oracle/fast_recovery_area/ORA11G/archivelog]# cd
[oracle@rhel5:/home/oracle]#
接下來，我們通過rman來還原1號(hào)數(shù)據(jù)文件，由于歸檔丟失，不能recover，若非system，undo的數(shù)據(jù)文件，我們可以offline 掉，然后就可以打開數(shù)據(jù)庫(kù)了，但是system必須online才能打開數(shù)據(jù)庫(kù)，所以目前數(shù)據(jù)庫(kù)不能打開：
[oracle@rhel5:/home/oracle]# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 7 17:10:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=8302811, not open)
RMAN> restore datafile 1;
Starting restore at 2016-04-07 17:10:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp tag=TAG20160407T170035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 2016-04-07 17:12:36
RMAN> recover datafile 1;
Starting recover at 2016-04-07 17:14:48
using channel ORA_DISK_1
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/07/2016 17:14:49
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 11 and starting SCN of 2221005 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 10 and starting SCN of 2221002 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 9 and starting SCN of 2220999 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 8 and starting SCN of 2220996 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 7 and starting SCN of 2220979 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 6 and starting SCN of 2220975 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 2220972 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 2217677 found to restore
RMAN> recover datafile 1 until sequence 3 thread 1;
Starting recover at 2016-04-07 17:20:09
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/07/2016 17:20:09
RMAN-06556: datafile 1 must be restored from backup older than SCN 2197424
====》可以看到由于歸檔丟失，不能執(zhí)行recover操作，rman的不完全恢復(fù)也不能完成，下邊通過SQL的不完全恢復(fù)試試
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SYS@ora11g> recover database using backup controlfile until cancel;
ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2218426 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ora11g> alter database open ressetlogs;
alter database open ressetlogs
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2221005
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2221005 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2218426
SYS@ora11g>
可以看到1號(hào)文件和其他文件的Ckp SCN 不同，所以數(shù)據(jù)庫(kù)不能打開，下邊分別介紹基于隱含參數(shù)_allow_resetlogs_corruption 和 BBED修改文件頭來恢復(fù)打開數(shù)據(jù)庫(kù)。
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:20:49 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SYS@ora11g> recover database using backup controlfile until cancel;
ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2218426 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2221005
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2221005 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2221005 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2221005 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2221005 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2221005 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2221005 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2221005 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2218426
SYS@ora11g>
SYS@ora11g>
SYS@ora11g> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SYS@ora11g> recover database using backup controlfile until cancel;
ORA-00279: change 2218426 generated at 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2218426 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ora11g>
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/ora11g/sysaux01.dbf'
====》 不完全恢復(fù)不能打開數(shù)據(jù)庫(kù)，只能采用隱含參數(shù)了
SYS@ora11g>
SYS@ora11g> startup force
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2229864 bytes
Variable Size 390072728 bytes
Database Buffers 113246208 bytes
Redo Buffers 8036352 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@ora11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Process ID: 15341
Session ID: 191 Serial number: 3
SYS@ora11g> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:28:54 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ora11g> startup mount
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2229864 bytes
Variable Size 390072728 bytes
Database Buffers 113246208 bytes
Redo Buffers 8036352 bytes
Database mounted.
SYS@ora11g> alter database open;
Database altered.
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2238438
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2238438 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2238438 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2238438 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2238438 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2238438 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2238438 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2238438 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf NO 2238438 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2238438 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2238438 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2238438 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2238438 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2238438 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2238438 ONLINE
7 rows selected.
====》ckp scn 一致，數(shù)據(jù)庫(kù)打開，下邊我們把_allow_resetlogs_corruption這個(gè)參數(shù)從spfile中刪除，然后備份數(shù)據(jù)庫(kù)，我們測(cè)試就把該參數(shù)刪除即可
SYS@ora11g> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.
SYS@ora11g> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
System altered.
SYS@ora11g> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora11g> startup
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2229864 bytes
Variable Size 390072728 bytes
Database Buffers 113246208 bytes
Redo Buffers 8036352 bytes
Database mounted.
Database opened.
SYS@ora11g> show parameter _allow_resetlogs_corruption
SYS@ora11g>
====》隱含參數(shù)已從spfile中清除掉了，備份數(shù)據(jù)庫(kù)即可
告警日志：
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: ALTER DATABASE OPEN...
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 2218426
Archived Log entry 46 added for thread 1 sequence 10 ID 0x94f151 dest 1:
Thu Apr 07 17:28:15 2016
Archived Log entry 47 added for thread 1 sequence 11 ID 0x94f151 dest 1:
Archived Log entry 48 added for thread 1 sequence 12 ID 0x94f151 dest 1:
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 10
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 11
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 12
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 9761105 (0x94f151)
Online log /u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Thu Apr 07 17:28:25 2016
Setting recovery target incarnation to 4
Thu Apr 07 17:28:25 2016
Assigning activation ID 9776087 (0x952bd7)
LGWR: STARTING ARCH PROCESSES
Thu Apr 07 17:28:25 2016
ARC0 started with pid=21, OS id=15440
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Apr 07 17:28:26 2016
ARC1 started with pid=22, OS id=15447
Thu Apr 07 17:28:26 2016
ARC2 started with pid=23, OS id=15450
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
Thu Apr 07 17:28:27 2016
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Apr 07 17:28:27 2016
SMON: enabling cache recovery
Thu Apr 07 17:28:27 2016
ARC3 started with pid=24, OS id=15452
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39761):
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39761/ora11g_ora_15341_i39761.trc:
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_10_cjd8vcy1_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Thu Apr 07 17:28:35 2016
Dumping diagnostic data in directory=[cdmp_20160407172835], requested by (instance=1, osid=15341), summary=[incident=39761].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 15341): terminating the instance due to error 600
Thu Apr 07 17:28:35 2016
ORA-1092 : opitsk aborting process
Thu Apr 07 17:28:35 2016
ORA-1092 : opitsk aborting process
Instance terminated by USER, pid = 15341
ORA-1092 signalled during: alter database open resetlogs...
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39762):
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39762/ora11g_ora_15341_i39762.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39763):
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39763/ora11g_ora_15341_i39763.trc
Thu Apr 07 17:28:37 2016
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-27300: OS system dependent operation:semctl failed with status: 22
ORA-27301: OS failure message: Invalid argument
ORA-27302: failure occurred at: sskgpwpost1
ORA-27303: additional information: semid = 4292611
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=39764):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_39764/ora11g_ora_15341_i39764.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Thu Apr 07 17:28:39 2016
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_15341.trc (incident=40809):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_40809/ora11g_ora_15341_i40809.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [0], [2218438], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [2218437], [0], [2218967], [12583040], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2218434], [0], [2218967], [12583040], [], [], [], [], [], []
Thu Apr 07 17:29:01 2016
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: rhel5
Release: 2.6.18-194.el5
Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileora11g.ora
System parameters with non-default values:
processes = 150
memory_target = 492M
control_files = "/u01/app/oracle/oradata/ora11g/control01.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/ora11g/control02.ctl"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4122M
_allow_resetlogs_corruption= TRUE
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=ora11gXDB)"
audit_file_dest = "/u01/app/oracle/admin/ora11g/adump"
audit_trail = "DB"
db_name = "ora11g"
open_cursors = 300
diagnostic_dest = "/u01/app/oracle"
Thu Apr 07 17:29:01 2016
PMON started with pid=2, OS id=15634
Thu Apr 07 17:29:01 2016
PSP0 started with pid=3, OS id=15636
Thu Apr 07 17:29:02 2016
VKTM started with pid=4, OS id=15641 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Thu Apr 07 17:29:02 2016
GEN0 started with pid=5, OS id=15645
Thu Apr 07 17:29:02 2016
DIAG started with pid=6, OS id=15647
Thu Apr 07 17:29:02 2016
DBRM started with pid=7, OS id=15649
Thu Apr 07 17:29:02 2016
DIA0 started with pid=8, OS id=15651
Thu Apr 07 17:29:02 2016
MMAN started with pid=9, OS id=15655
Thu Apr 07 17:29:02 2016
DBW0 started with pid=10, OS id=15657
Thu Apr 07 17:29:02 2016
LGWR started with pid=11, OS id=15660
Thu Apr 07 17:29:02 2016
CKPT started with pid=12, OS id=15662
Thu Apr 07 17:29:02 2016
SMON started with pid=13, OS id=15664
Thu Apr 07 17:29:02 2016
RECO started with pid=14, OS id=15666
Thu Apr 07 17:29:02 2016
MMON started with pid=15, OS id=15668
Thu Apr 07 17:29:02 2016
MMNL started with pid=16, OS id=15670
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Thu Apr 07 17:29:03 2016
ALTER DATABASE MOUNT
Thu Apr 07 17:29:06 2016
Sweep [inc][40809]: completed
Sweep [inc][39764]: completed
Sweep [inc][39763]: completed
Sweep [inc][39762]: completed
Sweep [inc][39761]: completed
Sweep [inc2][40809]: completed
Sweep [inc2][39764]: completed
Sweep [inc2][39763]: completed
Sweep [inc2][39762]: completed
Sweep [inc2][39761]: completed
Successful mount of redo thread 1, with mount id 9775647
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Apr 07 17:29:11 2016
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Started redo scan
Completed redo scan
read 1 KB redo, 3 data blocks need recovery
Started redo application at
Thread 1: logseq 1, block 3, scn 2218432
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 1, block 5, scn 2238435
3 data blocks read, 3 data blocks written, 1 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Thu Apr 07 17:29:11 2016
ARC0 started with pid=24, OS id=15751
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Apr 07 17:29:12 2016
ARC1 started with pid=25, OS id=15756
Thread 1 advanced to log sequence 2 (thread open)
Thu Apr 07 17:29:12 2016
ARC2 started with pid=26, OS id=15758
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/ora11g/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Thu Apr 07 17:29:12 2016
ARC3 started with pid=27, OS id=15760
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Archived Log entry 49 added for thread 1 sequence 1 ID 0x952bd7 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[15725] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:85654704 end:85655584 diff:880 (8 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Thu Apr 07 17:29:13 2016
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Apr 07 17:29:16 2016
QMNC started with pid=28, OS id=15774
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Thu Apr 07 17:29:24 2016
db_recovery_file_dest_size of 4122 MB is 18.36% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Apr 07 17:29:24 2016
Starting background process CJQ0
Thu Apr 07 17:29:24 2016
CJQ0 started with pid=31, OS id=15820
通過_allow_resetlogs_corruption跳過數(shù)據(jù)庫(kù)的一致性檢查，成功打開數(shù)據(jù)庫(kù)，但也有些案例由于各種原因即使加上該參數(shù)也不能打開庫(kù)，這個(gè)時(shí)候就得靠我們的BBED了，切記resetlogs后立即備份數(shù)據(jù)庫(kù)。
首先查看system文件頭的scn號(hào)：
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:41:50 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g> col name format a60
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2239089
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2218426 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2218426
SYS@ora11g>
SYS@ora11g> select to_char(2239089,'xxxxxxxxxxx'),to_char(2218426,'xxxxxxxxxxx') FROM DUAL;
TO_CHAR(2239 TO_CHAR(2218
------------ ------------
222a71 21d9ba ====》將十進(jìn)制轉(zhuǎn)換為十六進(jìn)制
SYS@ora11g>
當(dāng)前ckp scn是2218426(十六進(jìn)制：21d9ba)，需要修改ckp scn到2239089(十六進(jìn)制：222a71)才可以啟動(dòng)數(shù)據(jù)庫(kù)，下邊我們通過bbed來修改：
BBED編譯準(zhǔn)備代碼，編譯過程參考：
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
vi /home/oracle/file.txt
set line 9999 pagesize 9999
select file#||' '||name||' '||bytes from v$datafile;
vi /home/oracle/bbed.par
blocksize=8192
listfile=/home/oracle/file.txt
mode=edit
bbed parfile=/home/oracle/bbed.par
[oracle@rhel5:/home/oracle]# ll
total 51328
-rwxr-xr-x 1 oracle dba 56 Apr 7 09:38 bbed.par
-rwxr-xr-x 1 oracle dba 27648 Apr 7 16:13 bifile.bbd
drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 Desktop
drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 dul
-rwxr-xr-x 1 oracle dba 393 Apr 7 17:46 file.txt
drwxr-xr-x 8 oracle dba 4096 Apr 7 09:38 gdul
-rwxr-xr-x 1 oracle dba 7354 Apr 7 16:31 log.bbd
-rwxr-xr-x 1 oracle dba 52436992 Apr 7 09:38 lxtbs01.dbf
drwxr-xr-x 3 oracle oinstall 4096 Apr 7 10:41 oradiag_oracle
drwxr-xr-x 2 oracle dba 4096 Apr 7 09:38 rman_bak
[oracle@rhel5:/home/oracle]# cat bbed.par
blocksize=8192
listfile=/home/oracle/file.txt
mode=edit
[oracle@rhel5:/home/oracle]# cat file.txt
1 /u01/app/oracle/oradata/ora11g/system01.dbf 754974720
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 765460480
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 73400320
4 /u01/app/oracle/oradata/ora11g/users01.dbf 11796480
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 10485760
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 20971520
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 52428800
[oracle@rhel5:/home/oracle]# bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Apr 7 17:46:53 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 92160
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 93440
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 8960
4 /u01/app/oracle/oradata/ora11g/users01.dbf 1440
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 1280
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2560
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 6400
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> show
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /u01/app/oracle/oradata/ora11g/system01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/file.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0021d9ba
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x36277bb4
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000004
ub4 kcrbabno @504 0x0000099d
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
====》這里我們看到0x0021d9ba 這個(gè)值就是我們剛剛查到的1號(hào)數(shù)據(jù)文件的文件頭的ckp scn號(hào)，我們dump一下文件頭塊，看看是如何存儲(chǔ)的：
BBED> d /v dba 1,1 offset 484 count 64
File: /u01/app/oracle/oradata/ora11g/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
-------------------------------------------------------
bad92100 00000000 b47b2736 01000000 l ........
04000000 9d090000 1000e080 02000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
====》很奇怪，我們看到的是bad92100，而不是我們期望的0021d9ba，2個(gè)存儲(chǔ)正好相反，我們的實(shí)驗(yàn)環(huán)境是linux的，屬于little字節(jié)序，在aix下這2個(gè)值的順序是一致的，這一點(diǎn)尤其得注意，下邊我們通過modify修改的時(shí)候也必須倒置，也就是原本的2239089(十六進(jìn)制：222a71) ，修改的時(shí)候?yàn)椋?12a71，還有2個(gè)0必須省略，如下
BBED> modify /x 712a22 dba 1,1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle/oradata/ora11g/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
------------------------------------------------------------------------
712a2200 00000000 b47b2736 01000000 04000000 9d090000 1000e080 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> d /v dba 1,1 offset 484 count 64
File: /u01/app/oracle/oradata/ora11g/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
-------------------------------------------------------
712a2200 00000000 b47b2736 01000000 l q*".........
04000000 9d090000 1000e080 02000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
====》可以看到已經(jīng)修改了
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00222a71
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x36277bb4
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000004
ub4 kcrbabno @504 0x0000099d
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> sum apply ====》應(yīng)用bbed的修改
Check value for File 1, Block 1:
current = 0x2a6c, required = 0x2a6c
BBED>
查看數(shù)據(jù)文件的ckp scn號(hào)，已經(jīng)保持一致了：
SYS@ora11g> col name format a50
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2239089
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- -------------------------------------------------- ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- -------------------------------------------------- --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2239089
SYS@ora11g> recover datafile 1;
Media recovery complete.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
no rows selected
SYS@ora11g> alter database open;
Database altered.
====》 數(shù)據(jù)庫(kù)正常打開，沒有使用resetlogs方式，o(∩_∩)o 哈哈
一般情況下數(shù)據(jù)庫(kù)可以正常打開，下邊要做的就是備份數(shù)據(jù)庫(kù)，或者expdp重新組織庫(kù)，值得說明的是，我做實(shí)驗(yàn)是先利用隱含參數(shù)去恢復(fù)的，做了resetlogs操作，然后采用bbed來恢復(fù)，結(jié)果recover數(shù)據(jù)文件的時(shí)候說備份屬于一個(gè)orphan incarnation，這個(gè)其實(shí)rman里邊reset一下就好了，下邊的內(nèi)容也屬于插曲，簡(jiǎn)單記錄一下：
SYS@ora11g> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora11g/system01.dbf'
SYS@ora11g> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Thu Apr 07 17:51:50 2016
告警日志看到屬于incarnation#=3：
ALTER DATABASE RECOVER datafile 1
Media Recovery Start
Serial Media Recovery started
Datafile 1 (ckpscn 2239089) is orphaned on incarnation#=3
Media Recovery failed with error 19909
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...
下邊通過rman來reset database：
[oracle@rhel5:/home/oracle]# rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Apr 7 17:52:47 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=8302811, not open)
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 8302811 PARENT 1 2011-09-17 09:46:04
2 2 ORA11G 8302811 PARENT 995548 2016-03-21 11:53:04
3 3 ORA11G 8302811 PARENT 2157406 2016-04-07 16:44:29
4 4 ORA11G 8302811 CURRENT 2218427 2016-04-07 17:28:05
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8 Full 628.38M DISK 00:02:46 2016-04-07 17:03:22
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20160407T170035
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_nnndf_TAG20160407T170035_cjd8fn42_.bkp
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 2218426 2016-04-07 17:00:36 /u01/app/oracle/oradata/ora11g/system01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
9 Full 9.64M DISK 00:00:02 2016-04-07 17:03:27
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20160407T170035
Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2016_04_07/o1_mf_ncsnf_TAG20160407T170035_cjd8lygf_.bkp
SPFILE Included: Modification time: 2016-04-07 16:49:24
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 2218482 Ckp time: 2016-04-07 17:03:24
RMAN> reset database to incarnation 3;
database reset to incarnation 3
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 8302811 PARENT 1 2011-09-17 09:46:04
2 2 ORA11G 8302811 PARENT 995548 2016-03-21 11:53:04
3 3 ORA11G 8302811 CURRENT 2157406 2016-04-07 16:44:29
4 4 ORA11G 8302811 ORPHAN 2218427 2016-04-07 17:28:05
RMAN> exit
Recovery Manager complete.
[oracle@rhel5:/home/oracle]# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 7 17:54:33 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g> col name format a50
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2239089
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- -------------------------------------------------- ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- -------------------------------------------------- --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf NO 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf NO 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf NO 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf NO 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf NO 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf NO 2239089 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2239089
SYS@ora11g>
SYS@ora11g> recover datafile 1;
ORA-00279: change 2239089 generated at 04/07/2016 17:00:36 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-00280: change 2239089 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@ora11g> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2239089
2 ONLINE 2239089
3 ONLINE 2239089
4 ONLINE 2239089
5 ONLINE 2239089
6 ONLINE 2239089
7 ONLINE 2239089
7 rows selected.
SYS@ora11g> col name format a50
SYS@ora11g> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2239089
SYS@ora11g> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- -------------------------------------------------- ------------------ ------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 SYSTEM
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE
7 rows selected.
SYS@ora11g> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME REC CHECKPOINT_CHANGE# STATUS
---------- -------------------------------------------------- --- ------------------ -------
1 /u01/app/oracle/oradata/ora11g/system01.dbf 2239089 ONLINE
2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf 2239089 ONLINE
3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf 2239089 ONLINE
4 /u01/app/oracle/oradata/ora11g/users01.dbf 2239089 ONLINE
5 /u01/app/oracle/oradata/ora11g/test_dul01.dbf 2239089 ONLINE
6 /u01/app/oracle/oradata/ora11g/TS_TRUNCATE01.dbf 2239089 ONLINE
7 /u01/app/oracle/oradata/ora11g/lxtbs01.dbf 2239089 ONLINE
7 rows selected.
SYS@ora11g> select file#,online_status,change# from v$recover_file;
recover datafile 1;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 2239089
2 ONLINE 2239089
3 ONLINE 2239089
4 ONLINE 2239089
5 ONLINE 2239089
6 ONLINE 2239089
7 ONLINE 2239089
7 rows selected.
SYS@ora11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
====》 看來reset 數(shù)據(jù)庫(kù)后只能resetlogs方式打開數(shù)據(jù)庫(kù)了
SYS@ora11g> alter database open resetlogs;
Database altered.
SYS@ora11g>
告警日志：
Thu Apr 07 17:52:29 2016
db_recovery_file_dest_size of 4122 MB is 18.36% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Apr 07 17:54:07 2016
Setting recovery target incarnation to 3
Thu Apr 07 17:54:58 2016
ALTER DATABASE RECOVER datafile 1
Media Recovery Start
Serial Media Recovery started
ARCH: STARTING ARCH PROCESSES
Thu Apr 07 17:54:59 2016
ARC0 started with pid=21, OS id=20808
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Thu Apr 07 17:55:00 2016
ARC1 started with pid=22, OS id=20813
Thu Apr 07 17:55:00 2016
ARC2 started with pid=23, OS id=20815
Thu Apr 07 17:55:00 2016
ARC3 started with pid=24, OS id=20817
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 1 ...
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thu Apr 07 17:55:22 2016
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
Errors with log /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
Errors with log /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_04_07/o1_mf_1_4_cjd8sssx_.arc
ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
ALTER DATABASE RECOVER CANCEL
Media Recovery Canceled
Completed: ALTER DATABASE RECOVER CANCEL
Thu Apr 07 17:55:46 2016
ALTER DATABASE RECOVER datafile 1
Media Recovery Start
Serial Media Recovery started
Media Recovery failed with error 1610
ORA-283 signalled during: ALTER DATABASE RECOVER datafile 1 ...
Thu Apr 07 17:56:04 2016
alter database open
Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_20712.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 2239089
Archived Log entry 77 added for thread 1 sequence 28 ID 0x952bd7 dest 1:
Archived Log entry 78 added for thread 1 sequence 29 ID 0x952bd7 dest 1:
Archived Log entry 79 added for thread 1 sequence 27 ID 0x952bd7 dest 1:
Clearing online redo logfile 1 /u01/app/oracle/oradata/ora11g/redo01.log
Clearing online log 1 of thread 1 sequence number 28
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/ora11g/redo02.log
Clearing online log 2 of thread 1 sequence number 29
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/ora11g/redo03.log
Clearing online log 3 of thread 1 sequence number 27
Thu Apr 07 17:56:16 2016
Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 9776087 (0x952bd7)
Online log /u01/app/oracle/oradata/ora11g/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/ora11g/redo03.log: Thread 1 Group 3 was previously cleared
Thu Apr 07 17:56:16 2016
Setting recovery target incarnation to 5
Thu Apr 07 17:56:16 2016
Assigning activation ID 9778453 (0x953515)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Apr 07 17:56:16 2016
SMON: enabling cache recovery
[20712] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:87282814 end:87284294 diff:1480 (14 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Thu Apr 07 17:56:27 2016
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Apr 07 17:56:29 2016
QMNC started with pid=25, OS id=21121
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Thu Apr 07 17:56:36 2016
ORA-1 encountered when generating server alert SMG-4120
Thu Apr 07 17:56:40 2016
Completed: alter database open resetlogs
ORA-1 encountered when generating server alert SMG-4121
Thu Apr 07 17:56:47 2016
Starting background process CJQ0
Thu Apr 07 17:56:47 2016
CJQ0 started with pid=29, OS id=21198
Thu Apr 07 17:56:51 2016
Time drift detected. Please check VKTM trace file for more details.
通過隱含參數(shù)或BBED修改文件頭都可以恢復(fù)這種丟失歸檔文件情況下的數(shù)據(jù)文件的恢復(fù)，至于丟不丟失數(shù)據(jù)就看情況了，比如普通文件，被offline 掉了，也沒有任何數(shù)據(jù)更新，那么我們恢復(fù)該數(shù)據(jù)文件后就不會(huì)丟失數(shù)據(jù)。至于哪種方式更好一點(diǎn)，不能一概而論，我測(cè)試的時(shí)候可能由于多次修改多次resetlogs的緣故，單獨(dú)的隱含參數(shù)或bbed都不能成功打開庫(kù)，而是兩者結(jié)合最后修復(fù)了，所以還是得靈活運(yùn)用。
小麥苗的箴言：多做實(shí)驗(yàn)多思考！
項(xiàng)目 | db |
db 類型 | 單實(shí)例 |
db version | 11.2.0.4.0 |
db 存儲(chǔ) | FS |
主機(jī)IP地址/hosts配置 | 192.168.59.129 |
OS版本及kernel版本 | AIX 7.1 64位 |
歸檔模式 | Archive Mode |
ORACLE_SID | oralhr |
首先我們OS級(jí)別刪除system文件，刪除其中的一個(gè)歸檔文件，利用以前的備份來還原system文件，最后利用BBED修改文件頭到丟失日志的下一個(gè)日志號(hào)來模擬盡可能少的丟失數(shù)據(jù)的情況。
首先，rman備份1號(hào)system數(shù)據(jù)文件：
[ZHLHRDB2:oracle]:/home/oracle>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 11 14:30:41 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORALHR (DBID=394957205)
RMAN> list backupset;
specification does not match any backup in the repository
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
RMAN> list archivelog all;
specification does not match any archived log in the repository
RMAN> backup datafile 1;
Starting backup at 2016-04-11 14:31:42
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/app/oracle/oralhr/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-04-11 14:31:42
channel ORA_DISK_1: finished piece 1 at 2016-04-11 14:31:45
piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp tag=TAG20160411T143142 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2016-04-11 14:31:46
channel ORA_DISK_1: finished piece 1 at 2016-04-11 14:31:47
piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_ncsnf_TAG20160411T143142_cjpk6lwq_.bkp tag=TAG20160411T143142 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-04-11 14:31:47
RMAN> list backup of datafile 1;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
45 Full 657.98M DISK 00:00:03 2016-04-11 14:31:45
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20160411T143142
Piece Name: /oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp
List of Datafiles in backup set 45
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1283795 2016-04-11 14:31:42 /oracle/app/oracle/oralhr/system01.dbf
RMAN>
RMAN> exit
Recovery Manager complete.
[ZHLHRDB2:oracle]:/home/oracle>
新建一個(gè)表t_lhr_20160411_02，每次插入一條數(shù)據(jù)，然后每3次insert操作后執(zhí)行一次switch logfile的操作，保證每個(gè)歸檔文件里有3條insert的操作記錄：
[ZHLHRDB2:oracle]:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 11 14:36:12 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@oralhr> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 44
Next log sequence to archive 46
Current log sequence 46
SYS@oralhr> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest string /oracle/app/oracle
db_recovery_file_dest_size big integer 4182M
SYS@oralhr> col status for a10
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 46 CURRENT 1282753 2016-04-11 13:55:44
2 44 INACTIVE 1282665 2016-04-11 13:55:01
3 45 INACTIVE 1282745 2016-04-11 13:55:44
SYS@oralhr>
SYS@oralhr> alter system switch logfile;
System altered.
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 46 ACTIVE 1282753 2016-04-11 13:55:44
2 47 CURRENT 1283935 2016-04-11 14:37:10
3 45 INACTIVE 1282745 2016-04-11 13:55:44
====》 當(dāng)前日志序列是47號(hào)，47號(hào)我們建表和插入3條數(shù)據(jù)：
SYS@oralhr> create table t_lhr_20160411_02(id number);
Table created.
SYS@oralhr> insert into t_lhr_20160411_02 values(1);
1 row created.
SYS@oralhr>
SYS@oralhr> insert into t_lhr_20160411_02 values(2);
1 row created.
SYS@oralhr> insert into t_lhr_20160411_02 values(3);
1 row created.
SYS@oralhr> commit;
Commit complete.
SYS@oralhr> alter system switch logfile;
System altered.
====》 當(dāng)前日志序列是48號(hào)，48號(hào)我們插入3條數(shù)據(jù)：
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 46 ACTIVE 1282753 2016-04-11 13:55:44
2 47 ACTIVE 1283935 2016-04-11 14:37:10
3 48 CURRENT 1284002 2016-04-11 14:39:47
SYS@oralhr> insert into t_lhr_20160411_02 values(4);
1 row created.
SYS@oralhr> insert into t_lhr_20160411_02 values(5);
1 row created.
SYS@oralhr> insert into t_lhr_20160411_02 values(6);
1 row created.
SYS@oralhr> commit;
Commit complete.
SYS@oralhr> alter system switch logfile;
System altered.
====》 當(dāng)前日志序列是49號(hào)，49號(hào)我們插入3條數(shù)據(jù)：
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 49 CURRENT 1284016 2016-04-11 14:40:18
2 47 ACTIVE 1283935 2016-04-11 14:37:10
3 48 ACTIVE 1284002 2016-04-11 14:39:47
SYS@oralhr> insert into t_lhr_20160411_02 values(7);
1 row created.
SYS@oralhr> insert into t_lhr_20160411_02 values(8);
1 row created.
SYS@oralhr> insert into t_lhr_20160411_02 values(9);
1 row created.
SYS@oralhr> commit;
Commit complete.
SYS@oralhr> alter system switch logfile;
System altered.
====》建表操作在47號(hào)日志，47，48，49分別插入了3條記錄，t_lhr_20160411_02共9條記錄
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 49 ACTIVE 1284016 2016-04-11 14:40:18
2 50 CURRENT 1284034 2016-04-11 14:40:54
3 48 ACTIVE 1284002 2016-04-11 14:39:47
SYS@oralhr> select count(1) from t_lhr_20160411_02;
COUNT(1)
----------
9
SYS@oralhr>
SYS@oralhr> select name from v$datafile;
NAME
--------------------------------------------------------------------
/oracle/app/oracle/oralhr/system01.dbf
/oracle/app/oracle/oralhr/sysaux01.dbf
/oracle/app/oracle/oralhr/undotbs01.dbf
/oracle/app/oracle/oralhr/users01.dbf
/oracle/app/oracle/oralhr/example01.dbf
====》刪除1號(hào)文件
SYS@oralhr> ! rm /oracle/app/oracle/oralhr/system01.dbf
SYS@oralhr> alter system switch logfile;
System altered.
SYS@oralhr>
SYS@oralhr> alter system switch logfile;
System altered.
SYS@oralhr> alter system switch logfile;
System altered.
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 52 INACTIVE 1284103 2016-04-11 14:43:48
2 53 CURRENT 1284106 2016-04-11 14:43:51
3 51 INACTIVE 1284100 2016-04-11 14:43:45
====》53號(hào)日志插入一條記錄，則t_lhr_20160411_02共10條記錄
SYS@oralhr> insert into t_lhr_20160411_02 values(10);
1 row created.
SYS@oralhr> commit;
Commit complete.
SYS@oralhr> select count(1) from t_lhr_20160411_02;
COUNT(1)
----------
10
SYS@oralhr> alter system switch logfile;
System altered.
SYS@oralhr> alter system flush buffer_cache;
System altered.
SYS@oralhr> select count(1) from dba_objects;
COUNT(1)
----------
86827
SYS@oralhr> alter system flush buffer_cache;
System altered.
SYS@oralhr> alter system switch logfile;
System altered.
====》切換日志過程中有一定幾率數(shù)據(jù)庫(kù)down掉，若沒有就shutdown abort掉，然后重啟
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 55 CURRENT 1284157 2016-04-11 14:46:06
2 53 INACTIVE 1284106 2016-04-11 14:43:51
3 54 INACTIVE 1284128 2016-04-11 14:44:51
SYS@oralhr>
SYS@oralhr> shutdown abort
ORACLE instance shut down.
SYS@oralhr> startup
ORACLE instance started.
Total System Global Area 3089920000 bytes
Fixed Size 2250360 bytes
Variable Size 721422728 bytes
Database Buffers 2348810240 bytes
Redo Buffers 17436672 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/app/oracle/oralhr/system01.dbf'
SYS@oralhr> col status for a10
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ------------- -------------------
1 55 CURRENT 1284157 2016-04-11 14:46:06
3 54 INACTIVE 1284128 2016-04-11 14:44:51
2 53 INACTIVE 1284106 2016-04-11 14:43:51
SYS@oralhr>
SYS@oralhr> col name format a60
SYS@oralhr> col ERROR format a30
SYS@oralhr> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1284157
SYS@oralhr>
SYS@oralhr> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ ----------
1 /oracle/app/oracle/oralhr/system01.dbf 1284157 SYSTEM
2 /oracle/app/oracle/oralhr/sysaux01.dbf 1284157 ONLINE
3 /oracle/app/oracle/oralhr/undotbs01.dbf 1284157 ONLINE
4 /oracle/app/oracle/oralhr/users01.dbf 1284157 ONLINE
5 /oracle/app/oracle/oralhr/example01.dbf 1284157 ONLINE
SYS@oralhr>
SYS@oralhr> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ ------ ------------------ ----------
1 0 ONLINE
2 /oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE
3 /oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE
4 /oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE
5 /oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE
SYS@oralhr>
SYS@oralhr> select file#,online_status,change#,ERROR from v$recover_file;
FILE# ONLINE_STATUS CHANGE# ERROR
---------- -------------- ---------- ------------------------------
1 ONLINE 0 FILE NOT FOUND
SYS@oralhr>
SYS@oralhr>
====》可以看到1號(hào)數(shù)據(jù)文件找不到，然后我們刪除48號(hào)歸檔，確保不能通過recover來恢復(fù)
[ZHLHRDB2:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 11 14:55:39 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORALHR (DBID=394957205, not open)
RMAN> delete archivelog sequence 48;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=97 device type=DISK
List of Archived Log Copies for database with db_unique_name ORALHR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
100 1 48 A 2016-04-11 14:39:47
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_48_cjpkplfj_.arc
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_48_cjpkplfj_.arc RECID=100 STAMP=908894418
Deleted 1 objects
RMAN>
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORALHR
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
98 1 46 A 2016-04-11 13:55:44
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc
99 1 47 A 2016-04-11 14:37:10
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc
101 1 49 A 2016-04-11 14:40:18
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc
102 1 50 A 2016-04-11 14:40:54
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc
103 1 51 A 2016-04-11 14:43:45
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc
104 1 52 A 2016-04-11 14:43:48
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc
105 1 53 A 2016-04-11 14:43:51
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc
106 1 54 A 2016-04-11 14:44:51
Name: /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc
接下來，我們通過rman來還原1號(hào)數(shù)據(jù)文件，由于歸檔丟失，不能recover，若非system，undo的數(shù)據(jù)文件，我們可以offline 掉，然后就可以打開數(shù)據(jù)庫(kù)了，但是system必須online才能打開數(shù)據(jù)庫(kù)，所以目前數(shù)據(jù)庫(kù)不能打開：
[ZHLHRDB2:oracle]:/oracle>rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 11 14:58:16 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORALHR (DBID=394957205, not open)
RMAN> restore datafile 1;
Starting restore at 2016-04-11 14:58:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=97 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oralhr/system01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp
channel ORA_DISK_1: piece handle=/oracle/app/oracle/ORALHR/backupset/2016_04_11/o1_mf_nnndf_TAG20160411T143142_cjpk6gt0_.bkp tag=TAG20160411T143142
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2016-04-11 14:58:32
RMAN> recover datafile 1;
Starting recover at 2016-04-11 14:58:45
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 46 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc
archived log for thread 1 with sequence 47 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc
archived log for thread 1 with sequence 49 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc
archived log for thread 1 with sequence 50 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc
archived log for thread 1 with sequence 51 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc
archived log for thread 1 with sequence 52 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc
archived log for thread 1 with sequence 53 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc
archived log for thread 1 with sequence 54 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/11/2016 14:58:45
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 48 and starting SCN of 1284002 found to restore
====》 由于缺少48號(hào)日志不能完成recover操作，我們先將redo應(yīng)用到48號(hào)
RMAN> recover datafile 1 until sequence 48;
Starting recover at 2016-04-11 14:59:20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 46 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc
archived log for thread 1 with sequence 47 is already on disk as file /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc
archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc thread=1 sequence=46
archived log file name=/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc thread=1 sequence=47
media recovery complete, elapsed time: 00:00:00
Finished recover at 2016-04-11 14:59:20
RMAN>
可以看到由于48號(hào)歸檔丟失，不能完成recover操作。
[ZHLHRDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 11 15:06:12 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@oralhr> col name for a80
SYS@oralhr> set linesize 9999 pagesize 9999
SYS@oralhr> col FIRST_CHANGE# for 999999999999999
SYS@oralhr> SELECT NAME,
2 sequence#,
3 a.FIRST_CHANGE#
4 FROM v$archived_log a
5 WHERE resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
6 AND a.sequence# >=45
7 ORDER BY a.sequence#;
NAME SEQUENCE# FIRST_CHANGE#
-------------------------------------------------------------------------------- ---------- ----------------
45 1282745
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_46_cjpkjpd8_.arc 46 1282753
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_47_cjpkomvt_.arc 47 1283935
48 1284002
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc 49 1284016
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc 50 1284034
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc 51 1284100
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc 52 1284103
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_53_cjpkz30t_.arc 53 1284106
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_54_cjpl1g47_.arc 54 1284128
10 rows selected.
SYS@oralhr>
SYS@oralhr> col status for a10
SYS@oralhr> select GROUP# ,SEQUENCE#,STATUS,FIRST_CHANGE#,FIRST_TIME from v$log;
GROUP# SEQUENCE# STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------------- -------------------
1 55 CURRENT 1284157 2016-04-11 14:46:06
3 54 INACTIVE 1284128 2016-04-11 14:44:51
2 53 INACTIVE 1284106 2016-04-11 14:43:51
SYS@oralhr>
SYS@oralhr> col name format a60
SYS@oralhr> col ERROR format a30
SYS@oralhr> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1284157
SYS@oralhr> SELECT a.FILE#, a.NAME,a.CHECKPOINT_CHANGE#,a.LAST_CHANGE#,status FROM v$datafile a;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
---------- ------------------------------------------------------------ ------------------ ------------ ----------
1 /oracle/app/oracle/oralhr/system01.dbf 1284157 SYSTEM
2 /oracle/app/oracle/oralhr/sysaux01.dbf 1284157 ONLINE
3 /oracle/app/oracle/oralhr/undotbs01.dbf 1284157 ONLINE
4 /oracle/app/oracle/oralhr/users01.dbf 1284157 ONLINE
5 /oracle/app/oracle/oralhr/example01.dbf 1284157 ONLINE
SYS@oralhr>
SYS@oralhr> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS
---------- ------------------------------------------------------------ ------ ------------------ ----------
1 /oracle/app/oracle/oralhr/system01.dbf YES 1284002 ONLINE
2 /oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE
3 /oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE
4 /oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE
5 /oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE
SYS@oralhr> select file#,online_status,change#,ERROR from v$recover_file;
FILE# ONLINE_STATUS CHANGE# ERROR
---------- -------------- ---------- ------------------------------
1 ONLINE 1284002
SYS@oralhr>
SYS@oralhr>
====》轉(zhuǎn)換為十六進(jìn)制
SYS@oralhr> select to_char(1284002,'xxxxxxxx') cur_scn,to_char(1284016,'xxxxxxxx') target_scn,to_char(49,'xxxxxxxx') target_seq from dual;
CUR_SCN TARGET_SCN TARGET_SEQ
------------------ ------------------ ------------------
1397a2 1397b0 31
SYS@oralhr>
====》 可以看到日志應(yīng)用到了48號(hào)日志的開頭，接下來利用bbed來跳過48號(hào)，從49號(hào)的開頭開始恢復(fù)
====》我們只能先讓redo應(yīng)用到48號(hào)日志，下邊通過BBED來跳過48號(hào)，繼續(xù)恢復(fù)應(yīng)用49及后邊的日志：
[ZHLHRDB2:oracle]:/home/oracle>l
total 112
-rw-r--r-- 1 oracle dba 57 Apr 05 17:01 bbed.par
-rw-r--r-- 1 oracle dba 17408 Apr 11 11:38 bifile.bbd
-rw-r--r-- 1 oracle asmadmin 5715 Apr 06 15:34 ctl.sql
-rw-r--r-- 1 oracle dba 253 Apr 05 17:00 file.txt
-rw-r--r-- 1 oracle dba 10536 Apr 11 15:02 log.bbd
drwxr-xr-x 3 oracle dba 256 Apr 08 11:02 oracle_bk
drwxr-xr-x 4 oracle dba 256 Apr 05 16:54 rman_bak
-rw-r--r-- 1 oracle dba 757 Apr 11 10:02 rman_bk_db_archive_lhr.sh
-rw-r--r-- 1 oracle dba 1023 Apr 08 11:25 rman_bk_db_lhr.sh
[ZHLHRDB2:oracle]:/home/oracle>cat bbed.par
blocksize=8192
listfile=/home/oracle/file.txt
mode=edit
[ZHLHRDB2:oracle]:/home/oracle>cat file.txt
1 /oracle/app/oracle/oralhr/system01.dbf 786432000
2 /oracle/app/oracle/oralhr/sysaux01.dbf 513802240
3 /oracle/app/oracle/oralhr/undotbs01.dbf 94371840
4 /oracle/app/oracle/oralhr/users01.dbf 5242880
5 /oracle/app/oracle/oralhr/example01.dbf 328335360
[ZHLHRDB2:oracle]:/home/oracle>bbed parfile=bbed.par
[ZHLHRDB2:oracle]:/home/oracle>bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Mon Apr 11 15:02:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /oracle/app/oracle/oralhr/system01.dbf 96000
2 /oracle/app/oracle/oralhr/sysaux01.dbf 62720
3 /oracle/app/oracle/oralhr/undotbs01.dbf 11520
4 /oracle/app/oracle/oralhr/users01.dbf 640
5 /oracle/app/oracle/oralhr/example01.dbf 40080
BBED> show
FILE# 1
BLOCK# 1
OFFSET 0
DBA 0x00400001 (4194305 1,1)
FILENAME /oracle/app/oracle/oralhr/system01.dbf
BIFILE bifile.bbd
LISTFILE /home/oracle/file.txt
BLOCKSIZE 8192
MODE Edit
EDIT Unrecoverable
IBASE Dec
OBASE Dec
WIDTH 80
COUNT 512
LOGFILE log.bbd
SPOOL No
BBED> p kcvfhckp
struct kcvfhckp, 160 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001397a2
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x362ca0b3
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000030
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0000
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub1 kcvcpetb[8] @520 0x00
ub1 kcvcpetb[9] @521 0x00
ub1 kcvcpetb[10] @522 0x00
ub1 kcvcpetb[11] @523 0x00
ub1 kcvcpetb[12] @524 0x00
ub1 kcvcpetb[13] @525 0x00
ub1 kcvcpetb[14] @526 0x00
ub1 kcvcpetb[15] @527 0x00
ub1 kcvcpetb[16] @528 0x00
ub1 kcvcpetb[17] @529 0x00
ub1 kcvcpetb[18] @530 0x00
ub1 kcvcpetb[19] @531 0x00
ub1 kcvcpetb[20] @532 0x00
ub1 kcvcpetb[21] @533 0x00
ub1 kcvcpetb[22] @534 0x00
ub1 kcvcpetb[23] @535 0x00
ub1 kcvcpetb[24] @536 0x00
ub1 kcvcpetb[25] @537 0x00
ub1 kcvcpetb[26] @538 0x00
ub1 kcvcpetb[27] @539 0x00
ub1 kcvcpetb[28] @540 0x00
ub1 kcvcpetb[29] @541 0x00
ub1 kcvcpetb[30] @542 0x00
ub1 kcvcpetb[31] @543 0x00
ub1 kcvcpetb[32] @544 0x00
ub1 kcvcpetb[33] @545 0x00
ub1 kcvcpetb[34] @546 0x00
ub1 kcvcpetb[35] @547 0x00
ub1 kcvcpetb[36] @548 0x00
ub1 kcvcpetb[37] @549 0x00
ub1 kcvcpetb[38] @550 0x00
ub1 kcvcpetb[39] @551 0x00
ub1 kcvcpetb[40] @552 0x00
ub1 kcvcpetb[41] @553 0x00
ub1 kcvcpetb[42] @554 0x00
ub1 kcvcpetb[43] @555 0x00
ub1 kcvcpetb[44] @556 0x00
ub1 kcvcpetb[45] @557 0x00
ub1 kcvcpetb[46] @558 0x00
ub1 kcvcpetb[47] @559 0x00
ub1 kcvcpetb[48] @560 0x00
ub1 kcvcpetb[49] @561 0x00
ub1 kcvcpetb[50] @562 0x00
ub1 kcvcpetb[51] @563 0x00
ub1 kcvcpetb[52] @564 0x00
ub1 kcvcpetb[53] @565 0x00
ub1 kcvcpetb[54] @566 0x00
ub1 kcvcpetb[55] @567 0x00
ub1 kcvcpetb[56] @568 0x00
ub1 kcvcpetb[57] @569 0x00
ub1 kcvcpetb[58] @570 0x00
ub1 kcvcpetb[59] @571 0x00
ub1 kcvcpetb[60] @572 0x00
ub1 kcvcpetb[61] @573 0x00
ub1 kcvcpetb[62] @574 0x00
ub1 kcvcpetb[63] @575 0x00
ub1 kcvcpetb[64] @576 0x00
ub1 kcvcpetb[65] @577 0x00
ub1 kcvcpetb[66] @578 0x00
ub1 kcvcpetb[67] @579 0x00
ub1 kcvcpetb[68] @580 0x00
ub1 kcvcpetb[69] @581 0x00
ub1 kcvcpetb[70] @582 0x00
ub1 kcvcpetb[71] @583 0x00
ub1 kcvcpetb[72] @584 0x00
ub1 kcvcpetb[73] @585 0x00
ub1 kcvcpetb[74] @586 0x00
ub1 kcvcpetb[75] @587 0x00
ub1 kcvcpetb[76] @588 0x00
ub1 kcvcpetb[77] @589 0x00
ub1 kcvcpetb[78] @590 0x00
ub1 kcvcpetb[79] @591 0x00
ub1 kcvcpetb[80] @592 0x00
ub1 kcvcpetb[81] @593 0x00
ub1 kcvcpetb[82] @594 0x00
ub1 kcvcpetb[83] @595 0x00
ub1 kcvcpetb[84] @596 0x00
ub1 kcvcpetb[85] @597 0x00
ub1 kcvcpetb[86] @598 0x00
ub1 kcvcpetb[87] @599 0x00
ub1 kcvcpetb[88] @600 0x00
ub1 kcvcpetb[89] @601 0x00
ub1 kcvcpetb[90] @602 0x00
ub1 kcvcpetb[91] @603 0x00
ub1 kcvcpetb[92] @604 0x00
ub1 kcvcpetb[93] @605 0x00
ub1 kcvcpetb[94] @606 0x00
ub1 kcvcpetb[95] @607 0x00
ub1 kcvcpetb[96] @608 0x00
ub1 kcvcpetb[97] @609 0x00
ub1 kcvcpetb[98] @610 0x00
ub1 kcvcpetb[99] @611 0x00
ub1 kcvcpetb[100] @612 0x00
ub1 kcvcpetb[101] @613 0x00
ub1 kcvcpetb[102] @614 0x00
ub1 kcvcpetb[103] @615 0x00
ub1 kcvcpetb[104] @616 0x00
ub1 kcvcpetb[105] @617 0x00
ub1 kcvcpetb[106] @618 0x00
ub1 kcvcpetb[107] @619 0x00
ub1 kcvcpetb[108] @620 0x00
ub1 kcvcpetb[109] @621 0x00
ub1 kcvcpetb[110] @622 0x00
ub1 kcvcpetb[111] @623 0x00
ub1 kcvcpetb[112] @624 0x00
ub1 kcvcpetb[113] @625 0x00
ub1 kcvcpetb[114] @626 0x00
ub1 kcvcpetb[115] @627 0x00
ub1 kcvcpetb[116] @628 0x00
ub1 kcvcpetb[117] @629 0x00
ub1 kcvcpetb[118] @630 0x00
ub1 kcvcpetb[119] @631 0x00
ub1 kcvcpetb[120] @632 0x00
ub1 kcvcpetb[121] @633 0x00
ub1 kcvcpetb[122] @634 0x00
ub1 kcvcpetb[123] @635 0x00
ub1 kcvcpetb[124] @636 0x00
ub1 kcvcpetb[125] @637 0x00
ub1 kcvcpetb[126] @638 0x00
ub1 kcvcpetb[127] @639 0x00
ub1 kcvcpetb[128] @640 0x00
====》數(shù)據(jù)文件的scn被記錄在文件1號(hào)block偏移量484字節(jié)開始的四個(gè)字節(jié)中
BBED> d /v dba 1,1 offset 484 count 64
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
-------------------------------------------------------
001397a2 0000c828 362ca0b3 00010000 l .......(6,......
00000030 00000002 00000000 02000000 l ...0............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
====》AIX下存儲(chǔ)是正序，這個(gè)和linux正好相反
BBED> modify /x 001397b0 dba 1,1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
------------------------------------------------------------------------
001397b0 0000c828 362ca0b3 00010000 00000030 00000002 00000000 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 1, Block 1:
current = 0x0065, required = 0x0065
BBED> d /v dba 1,1 offset 484 count 64
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
-------------------------------------------------------
001397b0 0000c828 362ca0b3 00010000 l .......(6,......
00000030 00000002 00000000 02000000 l ...0............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
BBED>
SYS@oralhr> col name for a80
SYS@oralhr> set linesize 9999 pagesize 9999
SYS@oralhr> col FIRST_CHANGE# for 999999999999999
SYS@oralhr> SELECT NAME,
2 sequence#,
3 a.FIRST_CHANGE#
4 FROM v$archived_log a
5 WHERE resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
6 AND a.sequence# =49
7 ORDER BY a.sequence#;
NAME SEQUENCE# FIRST_CHANGE#
-------------------------------------------------------------------------------- ---------- ----------------
/oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc 49 1284016
SYS@oralhr> SELECT a.FILE#,a.NAME,a.RECOVER,a.CHECKPOINT_CHANGE#,status FROM v$datafile_header a;
FILE# NAME RECOVE CHECKPOINT_CHANGE# STATUS
---------- -------------------------------------------------------------------------------- ------ ------------------ ----------
1 /oracle/app/oracle/oralhr/system01.dbf YES 1284016 ONLINE
2 /oracle/app/oracle/oralhr/sysaux01.dbf NO 1284157 ONLINE
3 /oracle/app/oracle/oralhr/undotbs01.dbf NO 1284157 ONLINE
4 /oracle/app/oracle/oralhr/users01.dbf NO 1284157 ONLINE
5 /oracle/app/oracle/oralhr/example01.dbf NO 1284157 ONLINE
SYS@oralhr>
BBED> d /v dba 1,1 offset 500 count 64
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 500 to 563 Dba:0x00400001
-------------------------------------------------------
00000030 00000002 00000000 02000000 l ...0............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
====》要想跳過歸檔還需要數(shù)據(jù)文件頭塊的rba。它由seq#、log block#、偏移量(固定為16)組成，決定了數(shù)據(jù)文件從哪個(gè)歸檔日志的哪個(gè)位置開始應(yīng)用歸檔。Rba位于數(shù)據(jù)文件頭塊偏移量500處開始連續(xù)的12個(gè)字節(jié)
BBED> modify /x 00000031 dba 1,1 offset 500
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 500 to 563 Dba:0x00400001
------------------------------------------------------------------------
00000031 00000002 00000000 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> sum apply
Check value for File 1, Block 1:
current = 0x0064, required = 0x0064
BBED> modify /x 00000010 dba 1,1 offset 508
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 508 to 571 Dba:0x00400001
------------------------------------------------------------------------
00000010 02000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED>
BBED> sum apply
Check value for File 1, Block 1:
current = 0x0074, required = 0x0074
BBED> d /v dba 1,1 offset 500 count 64
File: /oracle/app/oracle/oralhr/system01.dbf (1)
Block: 1 Offsets: 500 to 563 Dba:0x00400001
-------------------------------------------------------
00000031 00000002 00000010 02000000 l ...1............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
BBED> p kcvfhckp
struct kcvfhckp, 160 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001397b0
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x362ca0b3
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000031
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0000
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub1 kcvcpetb[8] @520 0x00
ub1 kcvcpetb[9] @521 0x00
ub1 kcvcpetb[10] @522 0x00
ub1 kcvcpetb[11] @523 0x00
ub1 kcvcpetb[12] @524 0x00
ub1 kcvcpetb[13] @525 0x00
ub1 kcvcpetb[14] @526 0x00
ub1 kcvcpetb[15] @527 0x00
ub1 kcvcpetb[16] @528 0x00
ub1 kcvcpetb[17] @529 0x00
ub1 kcvcpetb[18] @530 0x00
ub1 kcvcpetb[19] @531 0x00
ub1 kcvcpetb[20] @532 0x00
ub1 kcvcpetb[21] @533 0x00
ub1 kcvcpetb[22] @534 0x00
ub1 kcvcpetb[23] @535 0x00
ub1 kcvcpetb[24] @536 0x00
ub1 kcvcpetb[25] @537 0x00
ub1 kcvcpetb[26] @538 0x00
ub1 kcvcpetb[27] @539 0x00
ub1 kcvcpetb[28] @540 0x00
ub1 kcvcpetb[29] @541 0x00
ub1 kcvcpetb[30] @542 0x00
ub1 kcvcpetb[31] @543 0x00
ub1 kcvcpetb[32] @544 0x00
ub1 kcvcpetb[33] @545 0x00
ub1 kcvcpetb[34] @546 0x00
ub1 kcvcpetb[35] @547 0x00
ub1 kcvcpetb[36] @548 0x00
ub1 kcvcpetb[37] @549 0x00
ub1 kcvcpetb[38] @550 0x00
ub1 kcvcpetb[39] @551 0x00
ub1 kcvcpetb[40] @552 0x00
ub1 kcvcpetb[41] @553 0x00
ub1 kcvcpetb[42] @554 0x00
ub1 kcvcpetb[43] @555 0x00
ub1 kcvcpetb[44] @556 0x00
ub1 kcvcpetb[45] @557 0x00
ub1 kcvcpetb[46] @558 0x00
ub1 kcvcpetb[47] @559 0x00
ub1 kcvcpetb[48] @560 0x00
ub1 kcvcpetb[49] @561 0x00
ub1 kcvcpetb[50] @562 0x00
ub1 kcvcpetb[51] @563 0x00
ub1 kcvcpetb[52] @564 0x00
ub1 kcvcpetb[53] @565 0x00
ub1 kcvcpetb[54] @566 0x00
ub1 kcvcpetb[55] @567 0x00
ub1 kcvcpetb[56] @568 0x00
ub1 kcvcpetb[57] @569 0x00
ub1 kcvcpetb[58] @570 0x00
ub1 kcvcpetb[59] @571 0x00
ub1 kcvcpetb[60] @572 0x00
ub1 kcvcpetb[61] @573 0x00
ub1 kcvcpetb[62] @574 0x00
ub1 kcvcpetb[63] @575 0x00
ub1 kcvcpetb[64] @576 0x00
ub1 kcvcpetb[65] @577 0x00
ub1 kcvcpetb[66] @578 0x00
ub1 kcvcpetb[67] @579 0x00
ub1 kcvcpetb[68] @580 0x00
ub1 kcvcpetb[69] @581 0x00
ub1 kcvcpetb[70] @582 0x00
ub1 kcvcpetb[71] @583 0x00
ub1 kcvcpetb[72] @584 0x00
ub1 kcvcpetb[73] @585 0x00
ub1 kcvcpetb[74] @586 0x00
ub1 kcvcpetb[75] @587 0x00
ub1 kcvcpetb[76] @588 0x00
ub1 kcvcpetb[77] @589 0x00
ub1 kcvcpetb[78] @590 0x00
ub1 kcvcpetb[79] @591 0x00
ub1 kcvcpetb[80] @592 0x00
ub1 kcvcpetb[81] @593 0x00
ub1 kcvcpetb[82] @594 0x00
ub1 kcvcpetb[83] @595 0x00
ub1 kcvcpetb[84] @596 0x00
ub1 kcvcpetb[85] @597 0x00
ub1 kcvcpetb[86] @598 0x00
ub1 kcvcpetb[87] @599 0x00
ub1 kcvcpetb[88] @600 0x00
ub1 kcvcpetb[89] @601 0x00
ub1 kcvcpetb[90] @602 0x00
ub1 kcvcpetb[91] @603 0x00
ub1 kcvcpetb[92] @604 0x00
ub1 kcvcpetb[93] @605 0x00
ub1 kcvcpetb[94] @606 0x00
ub1 kcvcpetb[95] @607 0x00
ub1 kcvcpetb[96] @608 0x00
ub1 kcvcpetb[97] @609 0x00
ub1 kcvcpetb[98] @610 0x00
ub1 kcvcpetb[99] @611 0x00
ub1 kcvcpetb[100] @612 0x00
ub1 kcvcpetb[101] @613 0x00
ub1 kcvcpetb[102] @614 0x00
ub1 kcvcpetb[103] @615 0x00
ub1 kcvcpetb[104] @616 0x00
ub1 kcvcpetb[105] @617 0x00
ub1 kcvcpetb[106] @618 0x00
ub1 kcvcpetb[107] @619 0x00
ub1 kcvcpetb[108] @620 0x00
ub1 kcvcpetb[109] @621 0x00
ub1 kcvcpetb[110] @622 0x00
ub1 kcvcpetb[111] @623 0x00
ub1 kcvcpetb[112] @624 0x00
ub1 kcvcpetb[113] @625 0x00
ub1 kcvcpetb[114] @626 0x00
ub1 kcvcpetb[115] @627 0x00
ub1 kcvcpetb[116] @628 0x00
ub1 kcvcpetb[117] @629 0x00
ub1 kcvcpetb[118] @630 0x00
ub1 kcvcpetb[119] @631 0x00
ub1 kcvcpetb[120] @632 0x00
ub1 kcvcpetb[121] @633 0x00
ub1 kcvcpetb[122] @634 0x00
ub1 kcvcpetb[123] @635 0x00
ub1 kcvcpetb[124] @636 0x00
ub1 kcvcpetb[125] @637 0x00
ub1 kcvcpetb[126] @638 0x00
ub1 kcvcpetb[127] @639 0x00
ub1 kcvcpetb[128] @640 0x00
BBED>
SYS@oralhr> recover datafile 1;
ORA-00279: change 1284016 generated at 04/11/2016 14:39:47 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc
ORA-00280: change 1284016 for thread 1 is in sequence #49
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1284034 generated at 04/11/2016 14:40:54 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_50_cjpkx1ry_.arc
ORA-00280: change 1284034 for thread 1 is in sequence #50
ORA-00279: change 1284100 generated at 04/11/2016 14:43:45 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_51_cjpkx4tg_.arc
ORA-00280: change 1284100 for thread 1 is in sequence #51
ORA-00279: change 1284103 generated at 04/11/2016 14:43:48 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_52_cjpkx7tg_.arc
ORA-00280: change 1284103 for thread 1 is in sequence #52
Log applied.
Media recovery complete.
SYS@oralhr>
SYS@oralhr> alter database open;
Database altered.
SYS@oralhr> select count(1) from t_lhr_20160411_02;
COUNT(1)
----------
7
SYS@oralhr>
====》 恢復(fù)完成，數(shù)據(jù)少了48號(hào)日志里的3條記錄
數(shù)據(jù)庫(kù)正常打開。
若歸檔文件只丟失了一部分，那么通過BBED修改文件頭跳過丟失的歸檔日志，從而把所有還存在的歸檔日志apply掉，這樣可以保證最小的數(shù)據(jù)丟失，所以碰到丟失歸檔的情況到底如何恢復(fù)還是得靈活運(yùn)用。
在實(shí)驗(yàn)過程中大家可以多模擬幾種場(chǎng)景，比如只修改文件頭offset484，不修改offset500的rda會(huì)出現(xiàn)什么問題，若是47號(hào)歸檔丟失(即建表的redo日志丟失)，后續(xù)的redo apply會(huì)不會(huì)出現(xiàn)問題等等，都可以多思考然后做實(shí)驗(yàn)來證明，簡(jiǎn)單記錄一下：
SYS@oralhr> recover datafile 1;
ORA-00279: change 1284016 generated at 04/11/2016 14:37:10 needed for thread 1
ORA-00289: suggestion : /oracle/app/oracle/ORALHR/archivelog/2016_04_11/o1_mf_1_49_cjpkqpsh_.arc
ORA-00280: change 1284016 for thread 1 is in sequence #49
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 1, block# 98185)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/oracle/app/oracle/oralhr/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 88595
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [98185], [6110], [], [], [], [], [], [], [], []
ORA-01112: media recovery not started
解決辦法：
recover database using backup controlfile until cancel;
alter database open resetlogs;
若還是不能解決，就加如下的隱含參數(shù)：
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
recover database using backup controlfile until cancel;
alter database open resetlogs;
startup force
alter database open resetlogs;
alter system set "_allow_resetlogs_corruption"=false scope=spfile;
alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
做完上邊的實(shí)驗(yàn)后，我們要做的就是備份數(shù)據(jù)庫(kù)，下邊小麥苗給大家貢獻(xiàn)2個(gè)基于歸檔和非歸檔的rman全備數(shù)據(jù)庫(kù)的shell腳本，注意修改黃色背景的內(nèi)容為自己的實(shí)際環(huán)境，其中非歸檔環(huán)境全備數(shù)據(jù)庫(kù)需要重啟到mount狀態(tài)，也可以使用其他的熱備腳本。
注意修改黃色部分：
export ORACLE_SID=ora11g
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
MYDATE=`date +'%Y%m%d%H%M%S'`
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
mkdir -p $BACKUP_DIR/log
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<eof</eof<>
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
sql 'alter system archive log current';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
}
EOF
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<eof</eof<>
allocate channel for maintenance type disk;
allocate channel for maintenance type sbt_tape;
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
EOF
----------------------------- 非歸檔 mount 全備
export ORACLE_SID=oralhr
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
MYDATE=`date +'%Y%m%d%H%M%S'`
BACKUP_DIR=/home/oracle/oracle_bk/$ORACLE_SID
mkdir -p $BACKUP_DIR/log
rman target / log $BACKUP_DIR/log/rman_full_$MYDATE.log append <<eof</eof<>
run
{
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database filesperset 4 format '$BACKUP_DIR/full_%n_%T_%t_%s_%p.bak';
backup spfile tag='bk_spfile_$MYDATE' format='$BACKUP_DIR/spfile_%n_%U_%T.bak';
backup archivelog all format '$BACKUP_DIR/arch_%d_%T_%s_%p.bak' delete input;
backup current controlfile format '$BACKUP_DIR/ctl_%d_%T_%s_%p.bak';
release channel c1;
release channel c2;
alter database open;
}
EOF
rman target / log $BACKUP_DIR/log/rman_delete_$MYDATE.log append <<eof</eof<>
allocate channel for maintenance type disk;
allocate channel for maintenance type sbt_tape;
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
EOF
看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
