當前日誌損壞解決一例

上周四，增加了一組redo log，下了alter system switch logfile之後，資料庫突然down掉。察看日誌，發現如下錯誤：

Errors in file f:"lczhis"dump"bdump"lczhis_arc0_2468.trc:

ORA-19504: failed to create file "F:"LCZHIS"ARCHIVE"ARC001_055776657082840.ARC"

ORA-27040: file create error, unable to create file

OSD-04001: invalid logical block size (OS 512)

 

ARC0: Error 19504 Creating archive log file to 'F:"LCZHIS"ARCHIVE"ARC001_055776657082840.ARC'

ARCH: Archival stopped, error occurred. Will continue retrying

Thu Oct 16 17:38:26 2008

Errors in file f:"lczhis"dump"bdump"lczhis_arc0_2468.trc:

ORA-16038: log 2 sequence# 282840 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 2 thread 1: 'E:"REDO"REDO02.LOG'

 

Thu Oct 16 17:38:30 2008

Errors in file f:"lczhis"dump"bdump"lczhis_lgwr_2384.trc:

ORA-00366: log 1 of thread 1, checksum error in the file header

ORA-00312: online log 1 thread 1: 'F:"LCZHIS"REDO"REDO01.LOG'

 

我試圖startup database,結果失敗，報如下錯誤

 

ALTER DATABASE OPEN

Thu Oct 16 17:39:11 2008

Beginning crash recovery of 1 threads

 parallel recovery started with 3 processes

Thu Oct 16 17:39:11 2008

Started redo scan

Thu Oct 16 17:39:11 2008

Errors in file f:"lczhis"dump"udump"lczhis_ora_880.trc:

ORA-00366: log 1 of thread 1, checksum error in the file header

ORA-00312: online log 1 thread 1: 'F:"LCZHIS"REDO"REDO01.LOG'

察看資料庫，發現是當前日誌。

然後就使出18班武藝，比如：

SQL〉startup mount

SQL〉alter database clear logfile group 1;

SQL> alter database clear unarchived logfile group 1;

SQL> recover database using backup controlfile until cancel;

SQL> alter session set events '10015 trace name adjust_scn level 1';

SQL> alter database open resetlogs;

甚至隱含參數

_allow_resetlogs_corruption=true                                                                         

_corrupted_rollback_segments=true                                                                        

_offline_rollback_segments=true

 

均以失敗告終，報如下錯誤類似：

Media Recovery Log F:"LCZHIS"ARCHIVE"ARC001_055776657082841.ARC

Errors with log F:"LCZHIS"ARCHIVE"ARC001_055776657082841.ARC

ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Thu Oct 16 22:34:05 2008

ALTER DATABASE RECOVER    CONTINUE DEFAULT 

ARCH: Warning. Log sequence in archive filename wrapped

to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.

Old log archive with same name might be overwritten.

Thu Oct 16 22:34:05 2008

Media Recovery Log F:"LCZHIS"ARCHIVE"ARC001_055776657082841.ARC

Errors with log F:"LCZHIS"ARCHIVE"ARC001_055776657082841.ARC

ORA-308 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT ...

Thu Oct 16 22:34:05 2008

ALTER DATABASE RECOVER CANCEL

ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

Thu Oct 16 22:34:44 2008

ALTER DATABASE RECOVER database until cancel 

Thu Oct 16 22:34:44 2008

Media Recovery Start

 parallel recovery started with 3 processes

Thu Oct 16 22:34:56 2008

ARCH: Warning. Log sequence in archive filename wrapped

to fix length as indicated by %S in LOG_ARCHIVE_FORMAT.

Old log archive with same name might be overwritten.

ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...

Thu Oct 16 22:35:36 2008

ALTER DATABASE RECOVER    LOGFILE 'F:"lczhis"redo"redo01.log' 

Thu Oct 16 22:35:36 2008

Media Recovery Log F:"lczhis"redo"redo01.log

Thu Oct 16 22:35:36 2008

Errors in file f:"lczhis"dump"udump"lczhis_ora_1860.trc:

ORA-00367: checksum error in log file header

ORA-00334: archived log: 'F:"LCZHIS"REDO"REDO01.LOG'

 

Errors with log F:"lczhis"redo"redo01.log

ORA-367 signalled during: ALTER DATABASE RECOVER    LOGFILE 'F:"lczhis"redo"redo01.log' ...

Thu Oct 16 22:35:36 2008

ALTER DATABASE RECOVER CANCEL

ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

Thu Oct 16 22:36:46 2008

周五晚上失眠，本打算switch logfile之後再備份的，這下可好，8年的資料呀，網上可用的方法試個邊，均失敗。按說當前日誌損壞應該不難，怎麼就這麼個結果呢。打電話給瀋陽的一位專家，計劃用最後一根稻草—dul。

突然想到一個問題，之前在上這套san時，好像當LUN超過2T時，在LUN上建庫會失敗，其原因是redo log 無法建立，是否是這個原因呢？記得當時已經找盤陣原廠解決了這個問題。

用UltraEdit開啟損壞的redo log 1及其他庫完好的redo log，發現問題所在，

圖一

圖二 

SCN的儲存地址不同，我檢查了好幾台庫的log，發現正常的都是圖二的樣子，這說明，我在這個盤陣上修複庫失敗的原因，不是方法不對，而是盤陣無法建立正常的redo log及archive log。

趕快讓負責SAN的同事，將一個未超過兩T的LUN劃給我這台資料庫，然後將所有資料庫複寫到新的LUN上，修改盤符，將原來的F盤改為D，而將新的LUN改為F，然後再用以上武藝，仍然失敗，1.3T的資料，COPY了整整一天才完，結果失敗，是否我真要用DUL了？

我複製的是在探索資料庫down 掉之後我備份的那份，不死心，決定將原庫直接複製過來，再試一次，過程如下：

1． 複製Down掉的原庫所有檔案到新的LUN

2． 刪掉所有記錄檔

3． SQL〉startup mount

4． SQL〉recover database using backup controlfile until cancel;

5． SQL〉cancel

6．   SQL> alter database open resetlogs;

資料庫竟然open了，真是太興奮了。

至於第一次用新LUN失敗的原因，估計是當時備份的資料庫檔案有誤。 

備份重於一切。

如果預算允許，盡量買大廠出的盤陣，那些便宜貨，經過的測試少，真是害人。