分类目录归档：ORA-xxxxx

ORA-600 3417故障处理

发表于 2022 年 9 月 27 日 由 惜分飞

数据库突然报ORA-600 3417错误

Mon Sep 26 06:42:51 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_7984.trc  (incident=176185):
ORA-00600: 内部错误代码, 参数: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\incident\incdir_176185\orcl2_lgwr_7984_i176185.trc
Mon Sep 26 06:42:54 2022
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_7984.trc:
ORA-00600: 内部错误代码, 参数: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
LGWR (ospid: 7984): terminating the instance due to error 470

节点2异常之后,节点1由于跨节点实例恢复导致异常

Mon Sep 26 06:44:26 2022
Instance recovery: looking for dead threads
Beginning instance recovery of 1 threads
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
 parallel recovery started with 31 processes
Started redo scan
Completed redo scan
 read 887 KB redo, 348 data blocks need recovery
Started redo application at
 Thread 2: logseq 9907, block 1980
Recovery of Online Redo Log: Thread 2 Group 3 Seq 9907 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_3.265.1078882689
  Mem# 1: +OCR/orcl/onlinelog/group_3.259.1078882689
Completed redo application of 0.32MB
Completed instance recovery at
 Thread 2: logseq 9907, block 3755, scn 231951271
 338 data blocks read, 348 data blocks written, 887 redo k-bytes read
Mon Sep 26 06:44:36 2022
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_7972.trc  (incident=208205):
ORA-00600: 内部错误代码, 参数: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
Mon Sep 26 06:44:38 2022
Reconfiguration started (old inc 14, new inc 16)
List of instances:
 1 2 (myinst: 1) 
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Mon Sep 26 06:44:38 2022
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Mon Sep 26 06:44:38 2022
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Sep 26 06:44:38 2022
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Mon Sep 26 06:44:38 2022
 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info 
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
ORA-600 occurred during recovery, instance will be terminated
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_smon_7972.trc:
ORA-00600: 内部错误代码, 参数: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
System state dump requested by (instance=1,osid=7972 (SMON)),summary=[abnormal instance termination].
SMON (ospid: 7972): terminating the instance due to error 600
Mon Sep 26 06:44:43 2022
ORA-1092 : opitsk aborting process
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl1\trace\orcl1_diag_6956_20220926064442.trc:
ORA-00601: ??????
Mon Sep 26 06:44:46 2022
opiodr aborting process unknown ospid (6688) as a result of ORA-1092

再次重启实例无法正常启动报ORA-600 3417错误

Completed: ALTER DATABASE   MOUNT
Mon Sep 26 08:08:34 2022
alter database open
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Mon Sep 26 08:08:36 2022
LGWR: STARTING ARCH PROCESSES
Mon Sep 26 08:08:36 2022
ARC0 started with pid=39, OS id=5004 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Sep 26 08:08:37 2022
ARC1 started with pid=38, OS id=3568 
Mon Sep 26 08:08:37 2022
ARC2 started with pid=41, OS id=3308 
Mon Sep 26 08:08:37 2022
ARC3 started with pid=42, OS id=8180 
Mon Sep 26 08:08:37 2022
ARC4 started with pid=43, OS id=7768 
Mon Sep 26 08:08:37 2022
ARC5 started with pid=44, OS id=4628 
Mon Sep 26 08:08:37 2022
ARC6 started with pid=45, OS id=6920 
Mon Sep 26 08:08:37 2022
ARC7 started with pid=46, OS id=7960 
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_7924.trc(incident=400186):
ORA-00600: ??????, ??: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
ARC7: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Sep 26 08:08:39 2022
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl2\trace\orcl2_lgwr_7924.trc:
ORA-00600: ??????, ??: [3417], [3], [0], [1], [0], [3], [3], [], [], [], [], []
LGWR (ospid: 7924): terminating the instance due to error 470
Mon Sep 26 08:08:44 2022
ORA-1092 : opitsk aborting process

故障比较明显,数据库两个节点故障之后,启动其中一个节点,已经完成了实例恢复,但是无法open,通过10046进行跟踪发下你open过程没有执行任何语句直接导致win服务异常,然后终止
111

通过进一步分析确认是redo组异常

SQL> select group#,sequence#,status,thread# from v$log;

    GROUP#  SEQUENCE# STATUS              THREAD#
---------- ---------- ---------------- ----------
         1      10837 CURRENT                   1
         2          0 UNUSED                    1
         3       9907 CURRENT                   2
         4          0 UNUSED                    2

进一步查询数据文件是否正常

SQL> set pages 10000
SQL> set numw 16
SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,last_change#,
  4  count(*) ROW_NUM
  5  FROM v$datafile
  6  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME         LAST_CHANGE#          ROW_NUM

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

ONLINE           231971314 2022-09-26 06:44:37        231971314               44

SYSTEM           231971314 2022-09-26 06:44:37        231971314                1



SQL> set numw 16
SQL> col CHECKPOINT_TIME for a40
SQL> set lines 150
SQL> set pages 1000
SQL> SELECT status,
  2  to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,chec
kpoint_change#,
  3  count(*) ROW_NUM
  4  FROM v$datafile_header
  5  GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh
24:mi:ss'),fuzzy
  6  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#
 ROW_NUM
------- ---------------------------------------- --- ------------------ --------
--------
ONLINE  2022-09-26 06:44:37                      NO           231971314
      45

基于上述情况,对于这个库,可以直接open库,实现数据0丢失

发表在 ORA-xxxxx, Oracle | 标签为 | 留下评论

ORA-12518 ORA-27302: 错误发生在: ssthrddcr

发表于 2022 年 5 月 6 日 由 惜分飞

11.2.0.1数据库运行在win 2008机器上,进程数配置为1000,但是数据库连接数到了170多之后,就开始报ORA-12518 TNS:监听程序无法分发客户机连接
ORA-12518

检查alert日志发现后台日志报ORA-27300 ORA-27301 ORA-27302错误

Fri May 06 11:57:14 2022
Process startup failed, error stack:
Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_psp0_3052.trc:
ORA-27300: 操作系统系统相关操作: CreateThread 失败, 状态为: 8
ORA-27301: 操作系统故障消息: 存储空间不足，无法处理此命令。
ORA-27302: 错误发生在: ssthrddcr
Process J002 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file c:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_cjq0_3808.trc:

通过mos分析确认问题为IRPStackSize参数值问题
irpstacksize

参考:Ora-27300: OS System Dependent Operation:Createthread Failed With Status: 8 (Doc ID1310453.1)

发表在 ORA-xxxxx | 标签为 , , | 评论关闭

segment header异常对象删除处理

发表于 2022 年 4 月 14 日 由 惜分飞

对于某些极端情况下，segment header出现损坏的对象该如何处理,这里通过一个实验来说明这类情况该如何处理,创建表并查询相关segment信息

SQL> create tablespace t_xff datafile '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf' size 128M autoextend on;

Tablespace created.

SQL> create table t_xifenfei tablespace t_xff as select * from dba_objects;

Table created.

SQL> select header_file,header_block from dba_SEGMENTS where segment_name='T_XIFENFEI'  and owner='SYS';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5          130
----segment header 为 file 5，block 130

SQL> select   EXTENT_ID , FILE_ID ,BLOCK_ID  from dba_extents where segment_name='T_XIFENFEI'  and owner='SYS';

 EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          5        128
         1          5        136
         2          5        144
         3          5        152
         4          5        160
         5          5        168
         6          5        176
         7          5        184
         8          5        192
         9          5        200
        10          5        208
        11          5        216
        12          5        224
        13          5        232
        14          5        240
        15          5        248
        16          5        256
        17          5        384
        18          5        512
        19          5        640
        20          5        768
        21          5        896
        22          5       1024
        23          5       1152
        24          5       1280

25 rows selected.

---有25个extent信息

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5
--空闲block是从1408开始

模拟segment header损坏(通过dd破坏block)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei ~]$ 
[oracle@xifenfei ~]$ 
[oracle@xifenfei ~]$echo xifenfei.com|dd of=/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf bs=8192 conv=notrunc seek=130
0+1 records in
0+1 records out
17 bytes (17 B) copied, 5.4389e-05 s, 313 kB/s
[oracle@xifenfei ~]$ dbv file=/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Apr 13 20:29:41 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/t_xifenfei01.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x01400082 (file 5, block 130)
Bad header found during dbv: 
Data in bad block:
 type: 119 format: 7 rdba: 0x65666978
 last change scn: 0x632e.6965666e seq: 0x6f flg: 0x6d
 spare1: 0x77 spare2: 0x2e spare3: 0x0
 consistency value in tail: 0xada72301
 check value in block header: 0xf30a
 computed block checksum: 0x5eb9



DBVERIFY - Verification complete

Total Pages Examined         : 16384
Total Pages Processed (Data) : 1234
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 154
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 14995
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 6466979 (0.6466979)

查询表数据报错

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 13 20:29:48 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  734892032 bytes
Fixed Size                  2256872 bytes
Variable Size             452984856 bytes
Database Buffers          276824064 bytes
Redo Buffers                2826240 bytes
Database mounted.
Database opened.
SQL> select count(1) from t_xifenfei;
select count(1) from t_xifenfei
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'

查询extent信息为空

SQL> select   EXTENT_ID   , FILE_ID   ,BLOCK_ID  from dba_extents where segment_name='T_XIFENFEI'  and owner='SYS';

no rows selected

SQL> select header_file,header_block from dba_SEGMENTS where segment_name='T_XIFENFEI'  and owner='SYS';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5          130

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5

尝试删除表报错

SQL> drop table t_xifenfei;
drop table t_xifenfei
           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'

通过加purge删除成功

SQL> drop table t_xifenfei purge;

Table dropped.

删除表成功，但是磁盘空间未释放，通过查询确认变为一个临时段

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5


SQL> select segment_name,segment_type,owner from dba_segments where header_file=5;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       OWNER
------------------ ------------------------------
5.130
TEMPORARY          SYS

清理临时段,彻底删除segment header异常对象删除后遗症

SQL> exec dbms_space_admin.segment_corrupt('T_XFF',5,130); 

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.segment_drop_corrupt('T_XFF',5,130);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS ('T_XFF');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,owner from dba_segments where header_file=5;

no rows selected

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5        128  133169152      16256
           5
发表在 ORA-xxxxx | 标签为 , | 评论关闭