Oracle Redo丟失恢復方案
介紹
Oracle Redo 損壞分三種情況:unused 狀態日誌損壞 inactive 狀態日誌損壞 active 狀態日誌損壞 current 狀態日誌損壞
恢復
與inactive 狀態日誌損壞
如果這個日誌是inactive, 手動執行clearing 操作:
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
第 1 行出現錯誤:
ORA-00350: 日誌 2 ( 例項 orcl 的日誌, 執行緒 1) 需要歸檔
ORA-00312: 聯機日誌 2 執行緒 1:
F:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
執行如下操作:
SQL> alter database clear unarchived logfile group 2;
資料庫已更改。
狀態日誌損壞
存在歸檔直接使用歸檔恢復即可.
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.
狀態日誌損壞
常規情況:
設定隱藏引數:
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.
如若出現與 SCN 相關 ORA-00600 錯誤使用以下推進 SCN 方式進行處理
推進scn 修復
1. 檢視當前資料庫的Current SCN
SYS@orcl> select current_scn||'' from v$database;
CURRENT_SCN||''
--------------------------------------------------------------------------------
4563483988
可以看到當前SCN 是4563 483988 ，我現在想推進SCN ，在10w 級別，也就是4563483988 標紅數字修改為指定值。
2. 重新啟動資料庫到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 左右： 說明：實驗發現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
推進scn 修復
計算方式
Lowest_scn+event level * 1000000
檢視當前資料庫SCN:
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
12796139551520
2. 新增event 以及引數
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
alter system set event='21307096 trace name context forever,level 3' scope=spfile;
3. 啟動資料庫
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
推進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 進位制後的值，本次將最高位增加一位數
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@redhat19c11 ~]$ ps -ef|grep LOCAL=YES
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 <
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
重啟資料庫, 也可正常開啟資料庫
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23825935/viewspace-2917472/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- Oracle 目錄許可權丟失故障恢復Oracle
- 【ASK_ORACLE】Oracle表決磁碟丟失後的恢復方法Oracle
- oracle丟失的是所有的redo日誌組Oracle
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- 伺服器資料丟失了怎麼恢復/分割槽丟失恢復教程伺服器
- 硬碟資料丟失如何恢復？硬碟
- 分割槽丟失資料恢復資料恢復
- 硬碟資料丟失原因和解決方案/資料恢復方法硬碟資料恢復
- 【北亞資料恢復】IBM System Storage儲存lvm資訊丟失資料恢復方案資料恢復IBMLVM
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- chkdsk 後資料丟失的恢復方法
- 伺服器RAID資料丟失恢復伺服器AI
- OMV資料恢復NAS陣列丟失資料恢復陣列
- 電腦檔案丟失資料恢復資料恢復
- 如何恢復伺服器資料丟失伺服器
- 伺服器資料恢復方法-RAID資訊丟失解決方案伺服器資料恢復AI
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- 北亞資料恢復-WINDOWS還原系統後原分割槽丟失的資料恢復方案資料恢復Windows
- 華為OceanStor S6800T伺服器分割槽丟失恢復方案伺服器
- 基於linux系統，fsck後資料丟失的資料恢復方案Linux資料恢復
- 丟失的隨身碟檔案如何恢復？
- 【資料庫資料恢復】斷電導致Oracle資料庫資料丟失的資料恢復案例資料庫資料恢復Oracle
- Oracle recover current redo ORA-00600:[4193] (oracle 故障恢復current redo日誌ORA-00600:[4193]報錯）Oracle
- 伺服器資料恢復案例之RAID資訊丟失資料恢復伺服器資料恢復AI
- u盤資料丟失怎麼恢復？有效的恢復方法在這裡
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- 【BBED】丟失歸檔檔案情況下的恢復
- 伺服器(RAID1)資料丟失恢復伺服器AI
- 伺服器(RAID)資料丟失瞭如何恢復？伺服器AI
- dg丟失歸檔，使用rman增量備份恢復
- 電腦硬碟資料丟失後怎麼恢復？硬碟資料恢復技巧教程硬碟資料恢復
- win10 ppt檔案丟失怎麼恢復_win10 ppt文件丟失如何找回Win10
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 伺服器儲存金蝶資料庫丟失恢復伺服器資料庫
- Omni Recover for Mac如何恢復所有丟失的iPhone資料MaciPhone
- 【伺服器資料恢復】MDisk重建導致vdisk丟失，上層Oracle資料庫不可用的資料恢復案例伺服器資料恢復Oracle資料庫
- 虛擬機器資料丟失恢復過程記錄虛擬機