Oracle Redo日志損壞挽救的詳細(xì)攻略
更新時(shí)間：2025年02月27日 08:46:48 作者：施嘉偉
Oracle Redo損壞分四種情況:unused狀態(tài)日志損壞 inactive狀態(tài)日志損壞 active狀態(tài)日志損壞 current狀態(tài)日志損壞,針對(duì)不同狀態(tài)的日志損壞,處理方式有所不同,下面將逐一介紹,需要的朋友可以參考下
一 介紹
1.1 介紹
Oracle Redo損壞分四種情況:unused狀態(tài)日志損壞 inactive狀態(tài)日志損壞 active狀態(tài)日志損壞 current狀態(tài)日志損壞。針對(duì)不同狀態(tài)的日志損壞，處理方式有所不同，下面將逐一介紹。
二 恢復(fù)
2.1 unused與inactive狀態(tài)日志損壞
如果這個(gè)日志是inactive,手動(dòng)執(zhí)行clearing操作:
SQL> alter database clear logfile group 2; alter database clear logfile group 2 * 第 1 行出現(xiàn)錯(cuò)誤: ORA-00350: 日志 2 (實(shí)例 orcl 的日志, 線程 1) 需要?dú)w檔 ORA-00312: 聯(lián)機(jī)日志 2 線程 1: F:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
執(zhí)行如下操作:
SQL> alter database clear unarchived logfile group 2;
數(shù)據(jù)庫(kù)已更改。
2.2 active狀態(tài)日志損壞
存在歸檔直接使用歸檔恢復(fù)即可..
SYS@orcl11g>recover database until cancel; --指定恢復(fù)的時(shí)間點(diǎn)(如果不知道，就是untill cancel)
ORA-00279: change 1763218 generated at 06/24/2021 12:02:00 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_74_816622368.dbf
ORA-00280: change 1763218 for thread 1 is in sequence #74
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_74_816622368.dbf
ORA-00279: change 1769094 generated at 06/24/2021 13:34:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_75_816622368.dbf
ORA-00280: change 1769094 for thread 1 is in sequence #75
ORA-00278: log file '/u01/app/oracle/arch/1_74_816622368.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl11g/redo01.log --指定current日志
Log applied.
Media recovery complete.
2.3 Current狀態(tài)日志損壞
常規(guī)情況:
設(shè)置隱藏參數(shù):
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SYS@orcl11g> recover database until cancel;
ORA-00279: change 1789650 generated at 06/24/2021 13:40:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_2_818948248.dbf
ORA-00280: change 1789650 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_2_818948248.dbf
ORA-00279: change 1789904 generated at 06/24/2021 13:41:02 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_3_818948248.dbf
ORA-00280: change 1789904 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/1_2_818948248.dbf' no longer needed
for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl11g/system01.dbf'
SYS@orcl11g> alter database open resetlogs;
Database altered.
如若出現(xiàn)與SCN相關(guān) ORA-00600錯(cuò)誤使用以下推進(jìn)SCN方式進(jìn)行處理
2.3.1 Poke推進(jìn)scn修復(fù)
1.查看當(dāng)前數(shù)據(jù)庫(kù)的Current SCN
SYS@orcl> select current_scn||'' from v$database; CURRENT_SCN||'' -------------------------------------------------------------------------------- 4563483988
可以看到當(dāng)前SCN是4563483988，我現(xiàn)在想推進(jìn)SCN，在10w級(jí)別，也就是4563483988標(biāo)紅數(shù)字修改為指定值。
2.重新啟動(dòng)數(shù)據(jù)庫(kù)到mount階段
SYS@orcl> shutdown abort ORACLE instance shut down. SYS@orcl> startup mount ORACLE instance started. Total System Global Area 1235959808 bytes Fixed Size 2252784 bytes Variable Size 788529168 bytes Database Buffers 436207616 bytes Redo Buffers 8970240 bytes Database mounted.
3.使用oradebug poke推進(jìn)SCN
我這里直接把十萬(wàn)位的"4"改為"9"了，相當(dāng)于推進(jìn)了50w左右： 說(shuō)明：實(shí)驗(yàn)發(fā)現(xiàn)oradebug poke 推進(jìn)的SCN值，既可以指定十六進(jìn)制的0x11008DE74，也可以直接指定十進(jìn)制的4563983988。
SYS@orcl> oradebug setmypid
Statement processed.
SYS@orcl> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SYS@orcl> select to_char(checkpoint_change#, 'XXXXXXXXXXXXXXXX') from v$database;
TO_CHAR(CHECKPOINT_CHANGE#,'XXXXXX
----------------------------------
110013C41
SYS@orcl> oradebug poke 0x06001AE70 8 4563983988
BEFORE: [06001AE70, 06001AE78) = 00000000 00000000
AFTER: [06001AE70, 06001AE78) = 1008DE74 00000001
SYS@orcl> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 1008DE74 00000001 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SYS@orcl> alter database open;
Database altered.
SYS@orcl> select current_scn||'' from v$database;
CURRENT_SCN||''
--------------------------------------------------------------------------------
4563984271
可以看到已經(jīng)成功將SCN推進(jìn)到4563983988，SCN不斷增長(zhǎng)，所以這里查到的值略大一些。
4.舉例ORA-600[2662]錯(cuò)誤下poke計(jì)算方式
A data block SCN is ahead of the current SCN. The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN stored in a UGA variable. If the SCN is less than the dependent SCN then we signal the ORA-600 [2662] internal error. ARGUMENTS: Arg [a] Current SCN WRAP Arg [b] Current SCN BASE Arg [c] dependent SCN WRAP Arg [d] dependent SCN BASE Arg [e] Where present this is the DBA where the dependent SCN came from.
計(jì)算方式:
ORA-00600: internal error code, arguments: [2662], [2], [1424107441], [2], [1424142235], [8388617], [], [] select 2*power(2,32)+1424142235 from dual; 10014076827 ORA-00600: internal error code, arguments: [2662], [2], [1424142249], [2], [1424142302], [8388649], [], [] select 2*power(2,32)+1424143000 from dual; 10014077592
總結(jié)公式：c * power(2,32) + d {+ 可適當(dāng)加一點(diǎn)，但不要太大！}
c代表：Arg [c] dependent SCN WRAP
d代表：Arg [d] dependent SCN BASE
2.3.2 12c event 21307096推進(jìn)scn修復(fù)
1.計(jì)算方式
Lowest_scn+event level * 1000000
查看當(dāng)前數(shù)據(jù)庫(kù)SCN:
SQL> select to_char(current_scn) from v$database; TO_CHAR(CURRENT_SCN) ---------------------------------------- 12796139551520
2.添加event以及參數(shù)
alter system set "_allow_resetlogs_corruption"=true scope=spfile; alter system set event='21307096 trace name context forever,level 3' scope=spfile;
3.啟動(dòng)數(shù)據(jù)庫(kù)
SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1660944384 bytes
Fixed Size 8793448 bytes
Variable Size 889193112 bytes
Database Buffers 754974720 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12796139551734 generated at 04/20/2022 11:13:44 needed for
thread 1
ORA-00289: suggestion :
/app/oracle/product/12.2.0/db_1/dbs/arch1_1_1102504135.dbf
ORA-00280: change 12796139551734 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
12796142552279
SCN成功推進(jìn)300w
2.3.3 gdb推進(jìn)scn修復(fù)
Session 1:
查詢當(dāng)前scn:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2910718245
查詢當(dāng)前SCN轉(zhuǎn)成16進(jìn)制后的值：
SQL> select to_char(2910718245,'xxxxxxxxxxxx') from dual;
TO_CHAR(29107
-------------
ad7e0925
查詢預(yù)修改的SCN轉(zhuǎn)換成16進(jìn)制后的值，本次將最高位增加一位數(shù)
SQL> select to_char(3910718245,'xxxxxxxxxxxx') from dual;
TO_CHAR(39107
-------------
e918d325
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kscn8 kcsgscn_ [060017E98, 060017EA0) = AD7E093B 00000000
需要注意的是，060017E98是SCN BASE值，AD7E093B是當(dāng)前的SCN值，可以理解為060017E98是一個(gè)代號(hào)x，當(dāng)前的x等于AD7E093B，待會(huì)兒我們修改SCN值的時(shí)候，就會(huì)需要指定060017E98這個(gè)值等于多少。
Session 2:
oracle 9824 9730 0 Feb22 ? 00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 18621 8636 0 01:18 pts/1 00:00:00 grep --color=auto LOCAL=YES
oracle 20109 20105 0 Feb15 ? 00:00:13 oracletestdb19c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
本次測(cè)試庫(kù)是orcl，因此選9824
[oracle@redhat19c11 ~]$ gdb $ORACLE_HOME/bin/oracle 9824
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-114.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
-------------------------------------------
-------------------------------------------
(gdb) set *((int *) 0x060017E98) = 0xe918d32--->將SCN BASE修改為剛才查出來(lái)的值
(gdb) quit
A debugging session is active.
Inferior 1 [process 9824] will be detached.
Quit anyway? (y or n) y
Detaching from program: /oracle/app/product/19.3.0/db_1/bin/oracle, process 9824
返回session1查詢,修改成功:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 3910718287
重啟數(shù)據(jù)庫(kù),也可正常打開數(shù)據(jù)庫(kù)
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 603979776 bytes Database Buffers 1845493760 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. SQL> select current_scn from v$database; CURRENT_SCN ----------- 3910719415
總結(jié)
Oracle Redo 日志損壞的恢復(fù)方法取決于日志的狀態(tài)。對(duì)于 Unused 和 Inactive 狀態(tài)的日志，通?？梢灾苯忧宄?；Active 狀態(tài)的日志需要結(jié)合歸檔日志進(jìn)行恢復(fù)；而 Current 狀態(tài)的日志損壞最為嚴(yán)重，可能需要基于最新的備份進(jìn)行完整恢復(fù)。合理配置日志管理策略，定期備份數(shù)據(jù)庫(kù)，并妥善處理歸檔日志，可以有效降低因日志損壞導(dǎo)致的數(shù)據(jù)丟失風(fēng)險(xiǎn)。
以上就是Oracle Redo日志損壞挽救的詳細(xì)攻略的詳細(xì)內(nèi)容，更多關(guān)于Oracle Redo日志損壞的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Oracle數(shù)據(jù)庫(kù)如何將表的某一列所有值用逗號(hào)隔開去重后合并成一行
在做系統(tǒng)時(shí)經(jīng)常會(huì)遇到在?個(gè)字段中,?逗號(hào)或其他符號(hào)分隔存儲(chǔ)多個(gè)信息,這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)如何將表的某一列所有值用逗號(hào)隔開去重后合并成一行的相關(guān)資料,需要的朋友可以參考下2023-12-12
Oracle表關(guān)聯(lián)更新幾種方法小結(jié)
這篇文章主要介紹了Oracle表關(guān)聯(lián)更新幾種方法,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),具有一定的參考借鑒價(jià)值,對(duì)大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-05-05
Oracle占用內(nèi)存過(guò)高的問(wèn)題及解決
這篇文章主要介紹了Oracle占用內(nèi)存過(guò)高的問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
Oracle sqlldr導(dǎo)入一個(gè)日期列實(shí)例詳解
這篇文章主要介紹了Oracle sqlldr導(dǎo)入一個(gè)日期列實(shí)例詳解的相關(guān)資料,需要的朋友可以參考下2017-06-06
ORACLE 11g從 11.2.0.1升級(jí)到11.2.0.4 詳細(xì)實(shí)戰(zhàn)教程
這篇文章主要介紹了ORACLE 11g從 11.2.0.1升級(jí)到11.2.0.4 詳細(xì)實(shí)戰(zhàn)教程,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-03-03
windows系統(tǒng)下進(jìn)入oracle數(shù)據(jù)庫(kù)兩種方法
這篇文章主要給大家介紹了關(guān)于windows系統(tǒng)下進(jìn)入oracle數(shù)據(jù)庫(kù)兩種方法的相關(guān)資料,Oracle數(shù)據(jù)庫(kù)是一款非常流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),它被廣泛用于企業(yè)級(jí)應(yīng)用中,為了使用Oracle數(shù)據(jù)庫(kù),我們需要先進(jìn)入數(shù)據(jù)庫(kù)系統(tǒng)中,需要的朋友可以參考下2023-09-09
oracle初始化參數(shù)設(shè)置...2007-03-03
Oracle數(shù)據(jù)庫(kù)壞塊問(wèn)題從預(yù)防到恢復(fù)的完整指南
Oracle數(shù)據(jù)庫(kù)在對(duì)數(shù)據(jù)塊執(zhí)行讀寫操作時(shí),會(huì)自動(dòng)進(jìn)行一致性檢查,包括驗(yàn)證數(shù)據(jù)塊的類型、地址信息、SCN號(hào)（系統(tǒng)更改號(hào)）以及頭部與尾部的匹配性,若檢查發(fā)現(xiàn)信息不一致,該數(shù)據(jù)塊將被標(biāo)記為壞塊,所以本文大家介紹了Oracle數(shù)據(jù)庫(kù)壞塊問(wèn)題預(yù)防和恢復(fù),需要的朋友可以參考下2025-09-09
Oracle數(shù)據(jù)庫(kù)空間回收從診斷到優(yōu)化實(shí)戰(zhàn)指南詳細(xì)教程
文章詳細(xì)介紹了Oracle數(shù)據(jù)庫(kù)空間回收的全過(guò)程,包括空間診斷、高水位線處理、數(shù)據(jù)清理、數(shù)據(jù)文件收縮、存儲(chǔ)配置優(yōu)化和自動(dòng)化運(yùn)維體系,通過(guò)這些方法,可以有效解決Oracle數(shù)據(jù)庫(kù)空間膨脹問(wèn)題,降低存儲(chǔ)成本,感興趣的朋友一起看看吧2025-11-11
oracle net manager 數(shù)據(jù)傳輸安全步驟詳解
oracle net manager來(lái)加密客戶端與數(shù)據(jù)庫(kù)之間或中間件與 數(shù)據(jù)庫(kù)之間的網(wǎng)絡(luò)傳輸數(shù)據(jù)。這篇文章主要介紹了oracle net manager 數(shù)據(jù)傳輸安全 ,需要的朋友可以參考下2018-09-09