共計(jì) 5096 個(gè)字符,預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。
行業(yè)資訊
數(shù)據(jù)庫
noarchive 數(shù)據(jù)庫 current redo log 損壞且沒有備份如何應(yīng)對
今天就跟大家聊聊有關(guān) noarchive 數(shù)據(jù)庫 current redo log 損壞且沒有備份如何應(yīng)對,可能很多人都不太了解,為了讓大家更加了解,丸趣 TV 小編給大家總結(jié)了以下內(nèi)容,希望大家根據(jù)這篇文章可以有所收獲。
昨天,一臺 oracle 數(shù)據(jù)庫由于磁盤問題,造成 current
redo log 損壞,down 機(jī)后無法啟動數(shù)據(jù)庫。
環(huán)境:windows2003 + oracle 9i
操作如下:
startup mount;
create pfile from spfile;
shudown immediate;
關(guān)閉當(dāng)前數(shù)據(jù)庫后,將所有 oracle 下 \ora92\ ,\oradata\ yw \ 目錄拷貝到出來,將密碼文件和 pfile 備份。
在另一臺計(jì)算機(jī)上安裝了一個(gè)新的 oracle,并且重建庫,實(shí)例名和數(shù)據(jù)庫名與前相同 —–yw。
用備份的 \oradata\yw\ 覆蓋新庫的 \ora\data\yw\.
覆蓋原來的密碼文件和 pfile.
在 pfile 中增加:
__ALLOW_RESETLOGS_CORRUPTION=TRUE
SQL startup mount pfile=c:\oracle\ora92\database\INITyw.ora;
ORACLE instance started.
Total System Global Area 2483027968 bytes
Fixed Size 2074760 bytes
Variable Size 1090520952 bytes
Database Buffers 1375731712 bytes
Redo Buffers 14700544 bytes
Database mounted.
SQL recover database until cancel;
輸入 cancel
SQL alter database open resetlogs;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL alter database open;
查看 alert_yw.log 文件:
RESETLOGS after incomplete recovery UNTIL CHANGE 9431879262014
Resetting resetlogs activation ID 3761166285 (0xe02ed7cd)
Online log 2 of thread 1 was previously cleared
Online log 3 of thread 1 was previously cleared
Mon Dec 21 14:43:32 2009
Assigning activation ID 3761145403 (0xe02e863b)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: D:\ORACLE\ORADATA\NMYW\REDO01.LOG
Successful open of redo thread 1.
Mon Dec 21 14:43:32 2009
SMON: enabling cache recovery
Mon Dec 21 14:43:32 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_265156.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù):
[2662], [2196], [131080003], [2196], [131139845], [8388617], [], []
Mon Dec 21 14:43:32 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_265156.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù): [2662], [2196], [131080003], [2196], [131139845], [8388617], [], []
Mon Dec 21 14:43:32 2009
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 265156
ORA-1092 signalled during: alter database open resetlogs…
Mon Dec 21 14:48:33 2009
USER: terminating instance due to error 1092
Instance terminated by USER, pid = 265156
SQL connect / as sysdba;
SQL startup mount pfile=c:\oracle\ora92\database\INITyw.ora;
SQL select file#,checkpoint_change# from v$datafile;
發(fā)現(xiàn)所有的 datafile 的 checkpoint_scn 都是大得出奇:
利用 10015 事件進(jìn)行 adjust_scn
level 1 = 1billion (12 位整數(shù))
SQL alter session set events 10015 trace name adjust_scn level 1
SQL alter database open ;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL alter database open;
查看 alert_yw.log 文件:
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_269012.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù): [2256], [0], [1073741824],[2196],[131100005], [], [], []
Mon Dec 21 15:18:11 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_269012.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù): [2256], [0], [1073741824], [2196], [131100005], [], [], []
level = (2196+1) *4=8788
SQL alter session set events 10015 trace name adjust_scn level 8788
SQL alter database open;
報(bào)錯(cuò):
查看 alert_yw.log 文件:
Debugging event used to advance scn to 9436043149312
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Dictionary check beginning
Dictionary check complete
Mon Dec 21 15:50:52 2009
SMON: enabling tx recovery
Mon Dec 21 15:50:52 2009
Database Characterset is ZHS16GBK
Mon Dec 21 15:50:54 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_273712.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù): [4193], [38371], [38432], [], [], [], [], []
Mon Dec 21 15:50:54 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_p000_272924.trc:
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], []
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\NMYW\REDO02.LOG
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\NMYW\REDO02.LOG
Mon Dec 21 15:50:56 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_p000_272924.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: D:\ORACLE\ORADATA\NMYW\REDO03.LOG
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], []
Mon Dec 21 15:50:56 2009
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORADATA\NMYW\REDO02.LOG
Mon Dec 21 15:50:56 2009
Errors in file d:\oracle\admin\nmyw\udump\nmyw_ora_273712.trc:
ORA-00607: 當(dāng)更改數(shù)據(jù)塊時(shí)出現(xiàn)內(nèi)部錯(cuò)誤
ORA-00600: 內(nèi)部錯(cuò)誤代碼,參數(shù): [4193], [38371], [38432], [], [], [], [], []
報(bào) ora-00600 [4193] 錯(cuò)誤,說明是 undo 信息出錯(cuò),將 undo datafile offline 掉即可:
SQL alter database datafile c:\oracle\oradata\yw\undotbs01.ora offline drop ;
SQL alter database open ;
Database opened.
利用 exp 將數(shù)據(jù)導(dǎo)出。重新建庫導(dǎo)入,一切 ok.
在最后一步如果出現(xiàn) ora-600 [4193] [4194] 錯(cuò)誤,如下解決:
ORA-600 的 4194 和 4193 錯(cuò)誤,根據(jù)錯(cuò)誤信息的看來是 Oracle 進(jìn)行恢復(fù)的過程中出現(xiàn)了問題。查詢 METALINK,發(fā)現(xiàn)是 REDO 中的回滾記錄和 UNDO 中的不一致造成的。嘗試使用隱含參數(shù)_CORRUPTED_ROLLBACK_SEGMENTS 來打開數(shù)據(jù)庫。在剛才的建立的 inittest08.ora 初始化文件中添加下面的信息:
undo_management= MANUAL
_corrupted_rollback_segments=(_SYSSMU1 ,_SYSSMU2 ,_SYSSMU3 ,_SYSSMU4 ,_SYSSMU5 ,_SYSSMU6 ,_SYSSMU7 ,_SYSSMU8 ,_SYSSMU9 ,_SYSSMU10 ,_SYSSMU11 ,_SYSSMU12 ,_SYSSMU13 ,_SYSSMU14 ,_SYSSMU15 ,_SYSSMU16 ,_SYSSMU17 ,_SYSSMU18 ,_SYSSMU19 ,_SYSSMU20 ,_SYSSMU21 ,_SYSSMU22 ,_SYSSMU23 ,_SYSSMU24 ,_SYSSMU25 ,_SYSSMU26 ,_SYSSMU27 ,_SYSSMU28 ,_SYSSMU29 ,_SYSSMU30 ,_SYSSMU31 ,_SYSSMU32 ,_SYSSMU33 ,_SYSSMU34 ,_SYSSMU35 ,_SYSSMU36 ,_SYSSMU37 ,_SYSSMU38 ,_SYSSMU39 ,_SYSSMU40 ,_SYSSMU41)
看完上述內(nèi)容,你們對 noarchive 數(shù)據(jù)庫 current redo log 損壞且沒有備份如何應(yīng)對有進(jìn)一步的了解嗎?如果還想了解更多知識或者相關(guān)內(nèi)容,請關(guān)注丸趣 TV 行業(yè)資訊頻道,感謝大家的支持。