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ù)庫已更改。
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ù)庫的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ù)庫到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
我這里直接把十萬位的"4"改為"9"了，相當(dāng)于推進(jìn)了50w左右： 說明：實(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不斷增長，所以這里查到的值略大一些。
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ù)庫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ù)庫
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è)試庫是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修改為剛才查出來的值
(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ù)庫,也可正常打開數(shù)據(jù)庫
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ù)庫，并妥善處理歸檔日志，可以有效降低因日志損壞導(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中基于hint的3種執(zhí)行計(jì)劃控制方法詳細(xì)介紹
這篇文章主要介紹了Oracle中基于hint的3種執(zhí)行計(jì)劃控制方法詳細(xì)介紹,它們分別是OUTLINE（大綱）、SQL PROFILE（概要文件）、SQL BASELINE（基線）,文中包含大量實(shí)例,需要的朋友可以參考下2014-07-07
本篇文章是對(duì)Oracle查詢優(yōu)化的基本準(zhǔn)則進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
oracle 11g的警告日志和監(jiān)聽日志的刪除方法
這篇文章主要介紹了oracle 11g的警告日志和監(jiān)聽日志的刪除方法,需要的朋友可以參考下2014-07-07
Oracle系統(tǒng)，即是以O(shè)racle關(guān)系數(shù)據(jù)庫為數(shù)據(jù)存儲(chǔ)和管理作為構(gòu)架基礎(chǔ)，構(gòu)建出的數(shù)據(jù)庫管理系統(tǒng)。世界第一個(gè)支持SQL語言的商業(yè)數(shù)據(jù)庫，定位于高端工作站，以及作為服務(wù)器的小型計(jì)算機(jī)，Oracle公司的整個(gè)產(chǎn)品線包括數(shù)據(jù)庫服務(wù)器、企業(yè)商務(wù)應(yīng)用套件、應(yīng)用開發(fā)和決策支持工具2014-08-08
And,Where使用提示以及用+進(jìn)行左關(guān)聯(lián)的提示及注意事項(xiàng)
先左關(guān)聯(lián)后在過濾假如關(guān)聯(lián)的結(jié)果里面B.b3=null那么你在where后面在加B.b3=2那么結(jié)果中B.b3肯定是沒有null的情況的,也就是說用+進(jìn)行左關(guān)聯(lián)沒有用leftjoin靈活待后續(xù)看是否有什么好的解決方案2013-02-02
這篇文章主要介紹了Oracle怎么壓縮表空間,壓縮表空間可能需要一些時(shí)間，具體取決于表空間的大小和數(shù)據(jù)庫的負(fù)載，因此，在執(zhí)行此操作之前，請(qǐng)確保已備份數(shù)據(jù)庫以防萬一，需要的朋友可以參考下2023-06-06
Oracle常見錯(cuò)誤診斷...2007-03-03
這篇文章主要為大家詳細(xì)介紹了Linux 7下腳本安裝配置oracle 11g r2教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-04-04
Oracle數(shù)據(jù)庫中,表空間是存儲(chǔ)數(shù)據(jù)對(duì)象的關(guān)鍵結(jié)構(gòu),管理表空間包括監(jiān)控其大小并根據(jù)需要進(jìn)行擴(kuò)展,以確保數(shù)據(jù)庫運(yùn)行高效,查看表空間大小,可以通過SQL查詢或使用Oracle Enterprise Manager,擴(kuò)展表空間的方法有手動(dòng)增加數(shù)據(jù)文件2024-10-10
Oracle中PL/SQL復(fù)合數(shù)據(jù)類型
這篇文章介紹了Oracle中PL/SQL的復(fù)合數(shù)據(jù)類型，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05