oracle redo各種狀態（inactive、active、current）損壞的處理方式

1.inactive狀態的redo損壞

如果這個日誌是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;

資料庫已更改。

2.active狀態的redo損壞

存在歸檔直接使用歸檔恢復即可

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.

3.current狀態redo損壞

檢視隱藏引數
col name for a30
col VALUE for a10
col DESCRIB for a40
set lines 200
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%';

設定隱藏引數:(預設是FALSE)

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.

生產資料庫需謹慎，建議在專業dba幫助下操作。