Oracle Redo日志損壞挽救的詳細攻略
更新時間：2025年02月27日 08:46:48 作者：施嘉偉
Oracle Redo損壞分四種情況:unused狀態(tài)日志損壞 inactive狀態(tài)日志損壞 active狀態(tài)日志損壞 current狀態(tài)日志損壞,針對不同狀態(tài)的日志損壞,處理方式有所不同,下面將逐一介紹,需要的朋友可以參考下
一 介紹
1.1 介紹
Oracle Redo損壞分四種情況:unused狀態(tài)日志損壞 inactive狀態(tài)日志損壞 active狀態(tài)日志損壞 current狀態(tài)日志損壞。針對不同狀態(tài)的日志損壞，處理方式有所不同，下面將逐一介紹。
二 恢復
2.1 unused與inactive狀態(tài)日志損壞
如果這個日志是inactive,手動執(zhí)行clearing操作:
SQL> alter database clear logfile group 2; alter database clear logfile group 2 * 第 1 行出現(xiàn)錯誤: ORA-00350: 日志 2 (實例 orcl 的日志, 線程 1) 需要歸檔 ORA-00312: 聯(lián)機日志 2 線程 1: F:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
執(zhí)行如下操作:
SQL> alter database clear unarchived logfile group 2;
數(shù)據(jù)庫已更改。
2.2 active狀態(tài)日志損壞
存在歸檔直接使用歸檔恢復即可..
SYS@orcl11g>recover database until cancel; --指定恢復的時間點(如果不知道，就是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ù):
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相關 ORA-00600錯誤使用以下推進SCN方式進行處理
2.3.1 Poke推進scn修復
1.查看當前數(shù)據(jù)庫的Current SCN
SYS@orcl> select current_scn||'' from v$database; CURRENT_SCN||'' -------------------------------------------------------------------------------- 4563483988
可以看到當前SCN是4563483988，我現(xiàn)在想推進SCN，在10w級別，也就是4563483988標紅數(shù)字修改為指定值。
2.重新啟動數(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推進SCN
我這里直接把十萬位的"4"改為"9"了，相當于推進了50w左右： 說明：實驗發(fā)現(xiàn)oradebug poke 推進的SCN值，既可以指定十六進制的0x11008DE74，也可以直接指定十進制的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
可以看到已經成功將SCN推進到4563983988，SCN不斷增長，所以這里查到的值略大一些。
4.舉例ORA-600[2662]錯誤下poke計算方式
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.
計算方式:
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
總結公式：c * power(2,32) + d {+ 可適當加一點，但不要太大！}
c代表：Arg [c] dependent SCN WRAP
d代表：Arg [d] dependent SCN BASE
2.3.2 12c event 21307096推進scn修復
1.計算方式
Lowest_scn+event level * 1000000
查看當前數(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.啟動數(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成功推進300w
2.3.3 gdb推進scn修復
Session 1:
查詢當前scn:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2910718245
查詢當前SCN轉成16進制后的值：
SQL> select to_char(2910718245,'xxxxxxxxxxxx') from dual;
TO_CHAR(29107
-------------
ad7e0925
查詢預修改的SCN轉換成16進制后的值，本次將最高位增加一位數(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是當前的SCN值，可以理解為060017E98是一個代號x，當前的x等于AD7E093B，待會兒我們修改SCN值的時候，就會需要指定060017E98這個值等于多少。
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)))
本次測試庫是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
總結
Oracle Redo 日志損壞的恢復方法取決于日志的狀態(tài)。對于 Unused 和 Inactive 狀態(tài)的日志，通?？梢灾苯忧宄?；Active 狀態(tài)的日志需要結合歸檔日志進行恢復；而 Current 狀態(tài)的日志損壞最為嚴重，可能需要基于最新的備份進行完整恢復。合理配置日志管理策略，定期備份數(shù)據(jù)庫，并妥善處理歸檔日志，可以有效降低因日志損壞導致的數(shù)據(jù)丟失風險。
以上就是Oracle Redo日志損壞挽救的詳細攻略的詳細內容，更多關于Oracle Redo日志損壞的資料請關注腳本之家其它相關文章！
相關文章
Oracle中基于hint的3種執(zhí)行計劃控制方法詳細介紹
這篇文章主要介紹了Oracle中基于hint的3種執(zhí)行計劃控制方法詳細介紹,它們分別是OUTLINE（大綱）、SQL PROFILE（概要文件）、SQL BASELINE（基線）,文中包含大量實例,需要的朋友可以參考下2014-07-07
本篇文章是對Oracle查詢優(yōu)化的基本準則進行了詳細的分析介紹，需要的朋友參考下2013-05-05
oracle 11g的警告日志和監(jiān)聽日志的刪除方法
這篇文章主要介紹了oracle 11g的警告日志和監(jiān)聽日志的刪除方法,需要的朋友可以參考下2014-07-07
Oracle系統(tǒng)，即是以Oracle關系數(shù)據(jù)庫為數(shù)據(jù)存儲和管理作為構架基礎，構建出的數(shù)據(jù)庫管理系統(tǒng)。世界第一個支持SQL語言的商業(yè)數(shù)據(jù)庫，定位于高端工作站，以及作為服務器的小型計算機，Oracle公司的整個產品線包括數(shù)據(jù)庫服務器、企業(yè)商務應用套件、應用開發(fā)和決策支持工具2014-08-08
And,Where使用提示以及用+進行左關聯(lián)的提示及注意事項
先左關聯(lián)后在過濾假如關聯(lián)的結果里面B.b3=null那么你在where后面在加B.b3=2那么結果中B.b3肯定是沒有null的情況的,也就是說用+進行左關聯(lián)沒有用leftjoin靈活待后續(xù)看是否有什么好的解決方案2013-02-02
這篇文章主要介紹了Oracle怎么壓縮表空間,壓縮表空間可能需要一些時間，具體取決于表空間的大小和數(shù)據(jù)庫的負載，因此，在執(zhí)行此操作之前，請確保已備份數(shù)據(jù)庫以防萬一，需要的朋友可以參考下2023-06-06
Oracle常見錯誤診斷...2007-03-03
這篇文章主要為大家詳細介紹了Linux 7下腳本安裝配置oracle 11g r2教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
Oracle數(shù)據(jù)庫中,表空間是存儲數(shù)據(jù)對象的關鍵結構,管理表空間包括監(jiān)控其大小并根據(jù)需要進行擴展,以確保數(shù)據(jù)庫運行高效,查看表空間大小,可以通過SQL查詢或使用Oracle Enterprise Manager,擴展表空間的方法有手動增加數(shù)據(jù)文件2024-10-10
這篇文章介紹了Oracle中PL/SQL的復合數(shù)據(jù)類型，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-05-05