又一次REDO損壞恢復

上午：9：52在切換歸檔時掉電：
? Thread 1 advanced to log sequence 14574 (LGWR switch)

? Current log# 3 seq# ? 14574 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log

Sun Sep 22 09:52:20 2019

Archived Log entry 14327 added for thread 1 sequence 14573 ID ? 0x5b557622 dest 1:

在下午啟動時報錯：

ALTER DATABASE OPEN

Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55829.trc:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ? '/home/oracle/app/oracle/oradata/orcl/system01.dbf'

ORA-1113 signalled during: ALTER DATABASE OPEN...

Sun Sep 22 16:53:32 2019

Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_55832.trc:

ORA-00314: log 1 of thread 1, expected sequence# ? 14575 doesn't match 14572

ORA-00312: online log 1 thread 1: ? '/home/oracle/app/oracle/oradata/orcl/redo01.log'

Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_55832.trc:

ORA-00314: log 2 of thread 1, expected sequence# ? 14576 doesn't match 14573

ORA-00312: online log 2 thread 1: ? '/home/oracle/app/oracle/oradata/orcl/redo02.log'

Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_55832.trc:

ORA-00322: log 3 of thread 1 is not current copy

ORA-00312: online log 3 thread 1: ? '/home/oracle/app/oracle/oradata/orcl/redo03.log'

分析過程及處理

一檢查alert_sid.log日志，發現已經進行過異常恢復

二關數據庫，備份所有數據文件，REDO，CONTROL到另一個目錄

三檢查文件壞塊

dbv ? file=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf blocksize=8192

dbv ? file=/home/oracle/app/oracle/oradata/orcl/system01.dbf blocksize=8192

dbv ? file=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf blocksize=8192

…………………………….

dbv file=/home/oracle/app/oracle/oradata/orcl/TIP_AREA_CONTROL_temp.DBF ? blocksize=8192

四檢查文件頭和文件SCN號

select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 365579306

2 365579306

3 365579306

4 365579306

5 365579306

6 365579306

7 365579306

8 365579306

9 365579306

10 365579306

11 365579306

12 365579306

?

select file#,checkpoint_change# from v$datafile;

?

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 365579302

2 365579302

3 365579302

4 365579302

5 365579302

6 365579302

7 365579302

8 365579302

9 365579302

10 365579302

11 365579302

12 365579302

五嘗試非常規手段恢復

alter system set ? control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl' ? scope=spfile;

alter system set undo_management =manual scope=spfile;

alter system set undo_tablespace =system ? scope=spfile;

alter system ? set "_allow_resetlogs_corruption"=TRUE scope=spfile;

RECOVER ? DATABASE until cancel;

alter database open resetlogs報錯:

Mon Sep 23 00:31:08 2019

Media Recovery failed with error 16433

Recovery Slave PR00 previously exited with exception ? 283

ORA-283 signalled during: ALTER DATABASE RECOVER ? DATABASE until cancel ...

Incident 2693065 created, dump file: ? /home/oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_2693065/orcl_ora_37627_i2693065.trc

ORA-00600: internal error code, arguments: [2662], [0], ? [365579314], [0], [365598509], [12583040], [], [], [], [], [], []

六 建控制文件

CREATE CONTROLFILE SET DATABASE "orcl" ? RESETLOGS FORCE LOGGING NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 ? '/home/oracle/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

GROUP 2 ? '/home/oracle/app/oracle/oradata/orcl/redo02.log' SIZE 50M,

GROUP 3 ? '/home/oracle/app/oracle/oradata/orcl/redo03.log' size 50M

DATAFILE

'/home/oracle/app/oracle/oradata/orcl/system01.dbf',

'/home/oracle/app/oracle/oradata/orcl/users01.dbf',

'/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',

'/home/oracle/app/oracle/oradata/orcl/TIP_TIP_PROJECT.DBF',

'/home/oracle/app/oracle/oradata/orcl/TIP_AREA_CONTROL.DBF',

'/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',

'/home/oracle/app/oracle/oradata/orcl/OA_HY.DBF',

'/home/oracle/app/oracle/oradata/orcl/yzb_data.dbf',

'/home/oracle/app/oracle/oradata/orcl/ogg01.dbf',

'/home/oracle/app/oracle/oradata/orcl/GUARDGATEWAY.DBF',

'/home/oracle/app/oracle/oradata/tipoa3_data.DBF',

'/home/oracle/app/oracle/oradata/oa3.DBF'

CHARACTER SET ZHS16GBK

;

七調整 SCN

SQL> oradebug poke ? 0x06001AE70 4 365998509

BEFORE: [06001AE70, ) = 00000000

AFTER: [06001AE70, 06001AE74) = 15D0B1AD

oradebug poke 0x06001AE74 4365998509

SQL> oradebug dumpvar sga kcsgscn_

kcslf kcsgscn_ [06001AE70, 06001AEA0) = 15D0B1AD 15D0B1AD 00000000 ? 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AX50 ? 00000000

oradebug poke 0x06001AE70 8366998509

alter database open resetlogs;

?

至此打開成功