背景：搭建了一套oracle 19c主備庫（單例項非CDB,PDB）,linux7.5在斷電後（沒有進行資料庫關閉）重啟資料庫報錯如下圖，redo當前狀態下進行不完全恢復主庫後resetlogs 開啟主庫報錯繼續報錯ORA-00600: internal error code, arguments: [4193], [227], [240]，
這個相當於兩個問題了，先是恢復主庫，再解決ORA-00600: [4193]的問題。這種問題我不止遇見過一次了，多次的，斷電後啟動例項會報錯。處理如下：
報錯提示：
ORA-00742: Log read detects lost write in thread 1 sequence 52 block 398212
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ORCL/redo03.log'
一.故障處理
1.1 current redo不完全恢復
檢視redo情況:
set lin 200 pages 100
col MEMBER for a50
SELECT thread#,
a.sequence#,
a.group#,
TO_CHAR(first_change#, '9999999999999999') "SCN",
a.status,
MEMBER
FROM v$log a, v$logfile b
WHERE a.group# = B.GROUP#
ORDER BY a.sequence# DESC;
select * from v$log;
可以發現損壞的redo是第3組.
進行恢復：
recover database until cancel;
alter database open resetlogs;
select name, open_mode, database_role from v$database;
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
shutdown immediate;
startup mount;
recover database until cancel;
alter database open resetlogs;
select name, open_mode, database_role from v$database;
通過恢復之後，進行resetlogs開啟資料庫報錯：ORA-00600: internal error code, arguments: [krsi_al_hdr_update.invalid_nab_1], [4294967295], [], [], [], [], [], [], [], [], [], []
嘗試過了，_allow_resetlogs_corruption方式開啟，_allow_resetlogs_corruption = true是不受支援的引數，此引數導致資料和字典不一致，會造成資料的丟失。因此，這種情況下Oracle建議通過EXP方式匯出資料庫。重建新資料庫後，再匯入。
redo 的損壞，一般還容易伴隨以下2種錯誤：ORA-600[2662]（SCN有關）和 ORA-600[4000]（回滾段有關）。
ORACLE 推薦的如下：
*注意：開啟資料庫後，必須匯出重建資料庫並匯入。
*通過以這種方式強制開啟資料庫，有一個強大的功能*
*邏輯損壞的可能性，可能會影響資料字典。Oracle不保證所有資料都是可訪問的，也不保證該方法已經開啟了一個資料庫， 並且該資料庫使用者將被允許繼續工作。
所有這一切都提供了一種獲取資料庫內容以進行提取的方法，通常通過匯出來進行提取。取決於確定丟失的資料量並糾正任何邏輯損壞問題。
1.2 ORA-00600: [4193] 處理
報警日誌報錯如下：
Block recovery completed at rba 4.147.16, scn 0x000000000033e225
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_9677.trc (incident=113465):
ORA-00600: internal error code, arguments: [4194], [16], [29], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_113465/orcl_j001_9677_i113465.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2021-04-06T17:33:42.003838+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_9681.trc:
ORA-00600: internal error code, arguments: [4193], [203], [231], [], [], [], [], [], [], [], [], []
2021-04-06T17:33:42.004342+08:00
opidrv aborting process J003 ospid (9681) as a result of ORA-600
2021-04-06T17:33:42.257529+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2021-04-06T17:33:42.287915+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j005_9685.trc:
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
2021-04-06T17:33:42.541633+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2021-04-06T17:33:42.541798+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_9677.trc:
ORA-00600: internal error code, arguments: [4194], [16], [29], [], [], [], [], [], [], [], [], []
2021-04-06T17:33:42.542337+08:00
opidrv aborting process J001 ospid (9677) as a result of ORA-600
2021-04-06T17:33:42.923918+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j005_9685.trc (incident=115261):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_115261/orcl_j005_9685_i115261.trc
2021-04-06T17:33:42.924883+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_115261/orcl_j005_9685_i115261.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4193], [227], [240], [], [], [], [], [], [], [], [], []
2021-04-06T17:33:42.981805+08:00
經過以上操作，設定了隱含引數_allow_resetlogs_corruption = true，很遺憾，我的資料庫開啟一會兒之後還是強制關閉了。
用匯出方式也是行，這時需要看報錯：
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_3964.trc
oracle support 文件 1428786.1
https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=cld99whp9_53&_afrLoop=278414555589218#SYMPTOM
引起的原因
收縮後可能會導致撤消損壞。相同的撤消塊可能用於兩個不同的事務，從而導致多個內部錯誤，例如：
ORA-600 [4193] / ORA-600 [4194]用於新事務
ORA-600 [4137]用於事務回滾
官方的解決文件步驟如下：
Best practice to create a new undo tablespace.
This method includes segment check.
1. Create pfile from spfile to edit
SQL> Create pfile='/tmp/initsid.ora' from spfile;
2. Shutdown the instance
3. set the following parameters in the pfile /tmp/initsid.ora
undo_management = manual
event = '10513 trace name context forever, level 2'
4. SQL>>startup restrict pfile='/tmp/initsid.ora'
5. SQL>select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
This is critical - we are looking for all undo segments to be offline - System will always be online.
If any are 'PARTLY AVAILABLE' or 'NEEDS RECOVERY' - Please open an issue with Oracle Support or update the current SR. There are many options from this moment and Oracle Support Analyst can offer different solutions for the bad undo segments.
If all offline then continue to the next step
6. Create new undo tablespace - example
SQL>create undo tablespace <new undo tablespace> datafile <datafile> size 2000M;
7. Drop old undo tablespace
SQL>drop tablespace <old undo tablespace> including contents and datafiles;
8. SQL>shutdown immediate;
9 SQL>startup nomount; --> Using your Original spfile
10. Modify the spfile with the new undo tablespace name
SQL> Alter system set undo_tablespace = '<new tablespace created in step 6>' scope=spfile;
11. SQL>shutdown immediate;
12. SQL>startup; --> Using spfile
處理：
cd $ORACLE_HOME/dbs
strings spfileorcl.ora
新建一個pfile
vi new.ora
把下面這句話加入新的new.ora裡頭。
undo_management = manual event = '10513 trace name context forever, level 2'
利用新的引數檔案啟動資料庫：
startup restrict pfile='/home/oracle/new.ora';
起來了：
select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';
除了SYSTEM表空間，其他所有的表空間都是offline;
create undo tablespace undo_new datafile '/u01/app/oracle/oradata/ORCL/undo_new.dbf' size 256m;
drop tablespace UNDOTBS1 including contents and datafiles;
alter system set undo_tablespace='undo_new' scope=spfile;
啟動資料庫：
startup
啟動後資料庫可以進行正常的讀寫和測試。
如果對這麼修改的資料庫不放心，可以匯出後新建一個庫，然後倒入進去。
二.問題分析
詳細處理方式參考資料：https://www.modb.pro/db/29145
看下面trace 檔案資訊：
more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_14290.trc
ORA-00600: internal error code, arguments: [4194], [16], [29], [], [], [], [], [], [], [], [], []
Error 600 in redo application callback
Before image SCN: 0x000000000030ff74 SEQ: 0x02Dump of change vector:
CON_ID:0 TYP:0 CLS:28 AFN:4 DBA:0x0100030e OBJ:4294967295 SCN:0x000000000030ff74 SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 136 spc: 4702 flg: 0x0012 seq: 0x0104 rec: 0x1d
xid: 0x0006.00a.00000517
ktubl redo: slt: 10 wrp: 1 flg: 0x0c08 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 8842 objd: 8842 tsn: 1]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
Begin trans
prev ctl uba: 0x0100030e.0104.15 prev ctl max cmt scn: 0x000000000030dc16
prev tx cmt scn: 0x000000000030dc38
txn start scn: 0xffffffffffffffff logon user: 0
prev brb: 0x0100030d prev bcl: 0x00000000
BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x000a.006.000004b1 uba: 0x0100011f.00bc.07
flg: C--- lkc: 0 scn: 0x00000000003a520e
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c12665 hdba: 0x00c01262
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 15(0xf)
Block after image is:
buffer rdba: 0x0100030e
scn: 0x30ff74 seq: 0x02 flg: 0x04 tail: 0xff740202
frmt: 0x02 chkval: 0x7dcf type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000001121DA000 to 0x00000001121DC000
說明：
紅色標記的XID，表示當前undo block所記錄的事務xid，對應V$TRANSACTION.中的XID資訊。
xid: 0x000a.006.000004b1
0x000a--回滾段編號,16進位制的,說明該事務使用的回滾段是第10號回滾段
006--事務槽編號(slot),說明對應undo segment header中的transaction table記錄中的index是6
000004b1 --序號(同一個事務可能具有多個SCN，用於區分一個事務中的多個操作)
Doing block recovery for file 4 block 782
Resuming block recovery (PMON) for file 4 block 782
Block recovery from logseq 4, block 95 to scn 0x0000000000000000
2021-04-12T16:58:44.619643+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORCL/redo01.log
Block recovery completed at rba 0.0.0, scn 0x00000000003a51ae
Doing block recovery for file 4 block 208
Resuming block recovery (PMON) for file 4 block 208
Block recovery from logseq 4, block 95 to scn 0x00000000003a5213
2021-04-12T16:58:44.621585+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORCL/redo01.log
Block recovery completed at rba 4.97.16, scn 0x00000000003a5214
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_14290.trc (incident=130183):
ORA-00600: internal error code, arguments: [600], [ORA-00600: internal error code, arguments: [4194], [16], [29], [], [], [], [], [], [], [], [], []
], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_130183/orcl_cjq0_14290_i130183.trc
2021-04-12T16:58:44.950687+08:00
相當於恢復Doing block recovery for file 4 block 782，這裡資料嘗試去恢復這個塊，這個塊是undo資料
檔案的塊。