ORA-07445 opiaba—绑定变量超过65535导致实例crash

数据库异常报ORA-07445 opiaba,ORA-00600 17147错,导致实例crash

Wed Mar 15 09:48:06 2023
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x187B953, opiaba()+639] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ora_169909.trc  (incident=446531):
ORA-07445: 出现异常错误: 核心转储 [opiaba()+639] [SIGSEGV] [ADDR:0x0] [PC:0x187B953] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/incident/incdir_446531/xifenfei3_ora_169909_i446531.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 15 09:48:07 2023
Dumping diagnostic data in directory=[cdmp_20230315094807], requested by (instance=3, osid=169909), summary=[incident=446531].
Wed Mar 15 09:48:08 2023
Sweep [inc][446531]: completed
Sweep [inc2][446531]: completed
Wed Mar 15 09:48:33 2023
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_pmon_161557.trc  (incident=440035):
ORA-00600: internal error code, arguments: [17147], [0x4AFC25D0C8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/incident/incdir_440035/xifenfei3_pmon_161557_i440035.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 15 09:48:33 2023
Dumping diagnostic data in directory=[cdmp_20230315094833], requested by (instance=3, osid=161557 (PMON)), summary=[incident=440035].
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_pmon_161557.trc:
ORA-00600: internal error code, arguments: [17147], [0x4AFC25D0C8], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 161557): terminating the instance due to error 472
Wed Mar 15 09:48:34 2023
opiodr aborting process unknown ospid (170089) as a result of ORA-1092

对应的trace文件信息

*** 2023-03-15 09:09:34.862
*** SESSION ID:(1858.63187) 2023-03-15 09:09:34.862
*** CLIENT ID:() 2023-03-15 09:09:34.862
*** SERVICE NAME:(xifenfei) 2023-03-15 09:09:34.862
*** MODULE NAME:(JDBC Thin Client) 2023-03-15 09:09:34.862
*** ACTION NAME:() 2023-03-15 09:09:34.862
 
Dump continued from file: /u01/app/oracle/diag/rdbms/xifenfei/xifenfei3/trace/xifenfei3_ora_116886.trc
ORA-07445: 出现异常错误: 核心转储 [opiaba()+639] [SIGSEGV] [ADDR:0x0] [PC:0x187B953] [SI_KERNEL(general_protection)] []

========= Dump for incident 326049 (ORA 7445 [opiaba()+639]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x187B953, opiaba()+639] [flags: 0x0, count: 1]
Registers:
%rax: 0x00000046fa212588 %rbx: 0x00000046762ee798 %rcx: 0x0000000000003200
%rdx: 0x0000000000000000 %rdi: 0x0000000000007340 %rsi: 0x0000000000007340
%rsp: 0x00007fffffff41d0 %rbp: 0x00007fffffff4240  %r8: 0x00000044a8599f00
 %r9: 0x0000000000000099 %r10: 0x0000000000000b34 %r11: 0x00000046fa211720
%r12: 0x00000046fa5c7488 %r13: 0x0000000000000000 %r14: 0x0200000046fa2125
%r15: 0x0000000000000005 %rip: 0x000000000187b953 %efl: 0x0000000000010202
  opiaba()+625 (0x187b945) jmp 0x187b982
  opiaba()+627 (0x187b947) movzbl 0x2c(%rbx),%r15d
  opiaba()+632 (0x187b94c) mov %r14,-0x60(%rbp)
  opiaba()+636 (0x187b950) mov %rax,%r14
> opiaba()+639 (0x187b953) movswq 0xa(%r14),%rcx
  opiaba()+644 (0x187b958) cmp %ecx,%r15d
  opiaba()+647 (0x187b95b) jne 0x187b976
  opiaba()+649 (0x187b95d) mov 0x18(%rbx),%rdi
  opiaba()+653 (0x187b961) lea 0xc(%r14),%rsi

*** 2023-03-15 09:09:34.863
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=5cvg8vqsbr8j6) -----
BEGIN  
            UPDATE
             t_xifenfei 
             SET valid_param=:1 , comm_addr1 = :2 ,
                password=:3 , customer_type = :4 ,
                comm_no=:5 , date_grade_no = :6 ,
                date_grade_flag=:7 , curve_config_no = :8 ,
                curve_config_flag=:9 , baudrate = :10 ,
                meas_order=:11 , meter_no = :12 ,
                kind_no=:13 , tariff_count = :14 ,
                meter_digits = :15 , import_user=:16 ,
                readmeter_flag = :17 ,status_code=:18  
            WHERE meter_id = :19 
         ; 
…………
         ; 
            UPDATE
             t_xifenfei 
             SET valid_param=:94982 , comm_addr1 = :94983 ,
                password=:94984 , customer_type = :94985 ,
                comm_no=:94986 , date_grade_no = :94987 ,
                date_grade_flag=:94988 , curve_config_no = :94989 ,
                curve_config_flag=:94990 , baudrate = :94991 ,
                meas_order=:94992 , meter_no = :94993 ,
                kind_no=:94994 , tariff_count = :94995 ,
                meter_digits = :94996 , import_user=:94997 ,
                readmeter_flag = :94998 ,status_code=:94999  
            WHERE meter_id = :95000 
         ;END;

写了一个begin end,里面对于同一个update语句进行多次绑定变量实现批量提交功能,绑定变量的数量达到95000个,远超oracle官方限制的65535的极限,触发类似Bug 12578873 ORA-7445 [opiaba] when using more than 65535 bind variables
20230315132517


处理方法减少绑定变量数量,不能超过65535个

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

Buffer I/O error on dev故障数据库恢复

客户对虚拟化环境进行物理机加内存,结果发现数据库服务器无法正常启动,报Buffer I/O error on dev错误
buffer io error


通过对底层虚拟化磁盘进行分析,确定vg为centos,数据所在的磁盘组为root
20230313213019

进一步恢复其中数据
20230313233918

然后顺利打开数据库,数据0丢失

[oracle@xifenfei u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 13 23:27:42 2023
Version 19.13.0.0.0

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

Connected to an idle instance.
 
SQL> startup
ORACLE instance started.

Total System Global Area 1.0100E+10 bytes
Fixed Size                 13625520 bytes
Variable Size            6241124352 bytes
Database Buffers         3825205248 bytes
Redo Buffers               19927040 bytes
Database mounted.
Database opened.
SQL> select status,count(1) from v$datafile group by status;

STATUS    COUNT(1)
------- ----------
SYSTEM           1
ONLINE          21
发表在 Oracle备份恢复 | 标签为 , | 留下评论

12.1.0.2最新patch—202301

Release Date Version Download link Included in Windows Bundle
17-Jan-2023 12.1.0.2.230117 (Jan 2023) Database Patch Set Update (DB PSU) Patch 34677720 NA – out of error correction

SPARC and AIX ETA is 30-Jan-2023

18-Oct-2022 12.1.0.2.221018 (Oct 2022) Database Patch Set Update (DB PSU) Patch 34386266 NA – out of error correction
19-Jul-2022 12.1.0.2.220719 (Jul 2022) Database Patch Set Update (DB PSU) Patch 34057742 12.1.0.2.220719 WIN DB BP Patch 33883271
19-Apr-2022 12.1.0.2.220419 (Apr 2022) Database Patch Set Update (DB PSU) Patch 33711081 12.1.0.2.220419 WIN DB BP Patch 33777450
18-Jan-2022 12.1.0.2.220118 (Jan 2022) Database Patch Set Update (DB PSU) Patch 33477199 12.1.0.2.220118 WIN DB BP Patch 33492893
19-Oct-2021 12.1.0.2.211019 (Oct 2021) Database Patch Set Update (DB PSU) Patch 33128590 12.1.0.2.211019 WIN DB BP Patch 33174365
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32768233 12.1.0.2.210720 WIN DB BP Patch 32774982
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328635 12.1.0.2.210420 WIN DB BP Patch 32396181
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Database Patch Set Update (DB PSU) Patch 31985579 12.1.0.2.210119 WIN DB BP Patch 32000405
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Database Patch Set Update (DB PSU) Patch 31550110 12.1.0.2.201020 WIN DB BP Patch 31658987
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Database Patch Set Update (DB PSU) Patch 31113348 12.1.0.2.200714 WIN DB BP Patch 31211574
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Database Patch Set Update (DB PSU) Patch 30700212 12.1.0.2.200414 WIN DB BP Patch 30861721
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Database Patch Set Update (DB PSU) Patch 30340202 12.1.0.2.200114 WIN DB BP Patch 30455401
15-Oct-2019 12.1.0.2.191015 (Oct 2019) Database Patch Set Update (DB PSU) Patch 29918340 12.1.0.2.191015 WIN DB BP Patch 30049606
16-Jul-2019 12.1.0.2.190716 (Jul 2019) Database Patch Set Update (DB PSU) Patch 29494060 12.1.0.2.190716 WIN DB BP Patch 29831650
16-Apr-2019 12.1.0.2.190416 (Apr 2019) Database Patch Set Update (DB PSU) Patch 29141015 12.1.0.2.190416 WIN DB BP Patch 29413116
15-Jan-2019 12.1.0.2.190115 (Jan 2019) Database Patch Set Update (DB PSU) Patch 28729169 12.1.0.2.190115 WIN DB BP Patch 28810679
16-Oct-2018 12.1.0.2.181016 (Oct 2018) Database Patch Set Update (DB PSU) Patch 28259833 12.1.0.2.181016 WIN DB BP Patch 28563501
17-Jul-2018 12.1.0.2.180717 (Jul 2018) Database Patch Set Update (DB PSU) Patch 27547329 12.1.0.2.180717 WIN DB BP Patch 27937907
17-Apr-2018 12.1.0.2.180417 (Apr 2018) Database Patch Set Update (DB PSU) Patch 27338041 12.1.0.2.180417 WIN DB BP Patch 27440294
16-Jan-2018 12.1.0.2.180116 (Jan 2018) Database Patch Set Update (DB PSU) Patch 26925311 12.1.0.2.180116 WIN DB BP Patch 27162953
17-Oct-2017 12.1.0.2.171017 (Oct 2017) Database Patch Set Update (DB PSU) Patch 26713565 12.1.0.2.171017 WIN DB BP Patch 26720785
14-Aug-2017 12.1.0.2.170814 (Jul 2017) Database Patch Set Update (DB PSU) Patch 26609783 12.1.0.2.170814 WIN DB BP Patch 26161726
18-Jul-2017 12.1.0.2.170718 (Jul 2017) Database Patch Set Update (DB PSU) Patch 25755742 12.1.0.2.170718 WIN DB BP Patch 26161724
18-Apr-2017 12.1.0.2.170418 (Apr 2017) Database Patch Set Update (DB PSU) Patch 25171037 12.1.0.2.170418 WIN DB BP Patch 25632533
17-Jan-2017 12.1.0.2.170117 (Jan 2017) Database Patch Set Update (DB PSU) Patch 24732082 12.1.0.2.170117 WIN DB BP Patch 25115951
18-Oct-2016 12.1.0.2.161018 (Oct 2016) Database Patch Set Update (DB PSU) Patch 24006101 12.1.0.2.161018 WIN DB BP Patch 24591642
19-Jul-2016 12.1.0.2.160719 (Jul 2016) Database Patch Set Update (DB PSU) Patch 23054246 12.1.0.2.160719 WIN DB BP Patch 23530387
19-Apr-2016 12.1.0.2.160419 (Apr 2016) Database Patch Set Update (DB PSU) Patch 22291127 12.1.0.2.160419 WIN DB BP Patch 22809813
19-Jan-2016 12.1.0.2.160119 (Jan 2016) Database Patch Set Update (DB PSU) Patch 21948354 12.1.0.2.160119 WIN DB BP Patch 22310559
20-Oct-2015 12.1.0.2.5 (Oct 2015) Database Patch Set Update (DB PSU) Patch 21359755 12.1.0.2 Patch 10 Patch 21821214
14-Jul-2015 12.1.0.2.4 (Jul 2015) Database Patch Set Update (DB PSU) Patch 20831110 12.1.0.2 Patch 7 Patch 21126814
14-Apr-2015 12.1.0.2.3 (Apr 2015) Database Patch Set Update (DB PSU) Patch 20299023 12.1.0.2 Patch 4 Patch 20684004
20-Jan-2015 12.1.0.2.2 (Jan 2015) Database Patch Set Update (DB PSU) Patch 19769480 12.1.0.2 Patch 1 Patch 19720843
14-Oct-2014 12.1.0.2.1 (Oct 2014) Database Patch Set Update (DB PSU) Patch 19303936 none

 

Release Date Version Download link
17-Jan-2023 12.1.0.2.230117 (Jan 2023) Grid Infrastructure Patch Set Update (GI PSU) Patch 34774519

SPARC and AIX ETA is 30-Jan-2023

18-Oct-2022 12.1.0.2.221018 (Oct 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 34535560
19-Jul-2022 12.1.0.2.220719 (Jul 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 34204576
19-Apr-2022 12.1.0.2.220419 (Apr 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 33829718
18-Jan-2022 12.1.0.2.220118 (Jan 2022) Grid Infrastructure Patch Set Update (GI PSU) Patch 33575274
19-Oct-2021 12.1.0.2.211019 (Oct 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 33248367
20-Jul-2021 12.1.0.2.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32917447
20-Apr-2021 12.1.0.2.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32495126
19-Jan-2021 12.1.0.2.210119 (Jan 2021) Grid Infrastructure Patch Set Update (GI PSU) Patch 32131261
20-Oct-2020 12.1.0.2.201020 (Oct 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31718737
14-Jul-2020 12.1.0.2.200714 (Jul 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 31305174
14-Apr-2020 12.1.0.2.200414 (Apr 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30805421
14-Jan-2020 12.1.0.2.200114 (Jan 2020) Grid Infrastructure Patch Set Update (GI PSU) Patch 30464119
15-Oct-2019 12.1.0.2.191015 (Oct 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 30070257
16-Jul-2019 12.1.0.2.190716 (Jul 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29698592
16-Apr-2019 12.1.0.2.190416 (Apr 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 29176115
15-Jan-2019 12.1.0.2.190115 (Jan 2019) Grid Infrastructure Patch Set Update (GI PSU) Patch 28813884
16-Oct-2018 12.1.0.2.181016 (Oct 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 28349311
17-Jul-2018 12.1.0.2.180717 (Jul 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27967747
17-Apr-2018 12.1.0.2.180417 (Apr 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27468957
16-Jan-2018 12.1.0.2.180116 (Jan 2018) Grid Infrastructure Patch Set Update (GI PSU) Patch 27010872
17-Oct-2017 12.1.0.2.171017 (Oct 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26635815
14-Aug-2017 12.1.0.2.170814 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 26610308
18-Jul-2017 12.1.0.2.170718 (Jul 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 25901062
18-Apr-2017 12.1.0.2.170418 (Apr 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 25434003
17-Jan-2017 12.1.0.2.170117 (Jan 2017) Grid Infrastructure Patch Set Update (GI PSU) Patch 24917825
18-Oct-2016 12.1.0.2.161018 (Oct 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 24412235
19-Jul-2016 12.1.0.2.160719 (Jul 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 23273629
19-Apr-2016 12.1.0.2.160419 (Apr 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22646084
19-Jan-2016 12.1.0.2.160119 (Jan 2016) Grid Infrastructure Patch Set Update (GI PSU) Patch 22191349
20-Oct-2015 12.1.0.2.5 (Oct 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 21523234
14-Jul-2015 12.1.0.2.4 (Jul 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20996835
14-Apr-2015 12.1.0.2.3 (Apr 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 20485724
20-Jan-2015 12.1.0.2.2 (Jan 2015) Grid Infrastructure Patch Set Update (GI PSU) Patch 19954978
14-Oct-2014 12.1.0.2.1 (Oct 2014) Grid Infrastructure Patch Set Update (GI PSU) Patch 19392646

 

Release Date Version Unix PSU Patch Windows Bundle Patch
17-Jan-2023 12.1.0.2.230117 (Jan 2023) OJVM Component Patch Set Update Patch 34763317 NA – out of error correction
18-Oct-2022 12.1.0.2.221018 (Oct 2022) OJVM Component Patch Set Update Patch 34412250 NA – out of error correction
19-Jul-2022 12.1.0.2.220719 (Jul 2022) OJVM Component Patch Set Update Patch 34086863 Patch 34185253
19-Apr-2022 12.1.0.2.220419 (Apr 2022) OJVM Component Patch Set Update Patch 33808385 Patch 33881387
18-Jan-2022 12.1.0.2.220118 (Jan 2022) OJVM Component Patch Set Update Patch 33561268 Patch 33577533
19-Oct-2021 12.1.0.2.211019 (Oct 2021) OJVM Component Patch Set Update Patch 33192628 Patch 33248785
20-Jul-2021 12.1.0.2.210720 (Jul 2021) OJVM Component Patch Set Update Patch 32876425 Patch 32905878
20-Apr-2021 12.1.0.2.210420 (Apr 2021) OJVM Component Patch Set Update Patch 32473164 Patch 32427683
19-Jan-2021 12.1.0.2.210119 (Jan 2021) OJVM Component Patch Set Update Patch 32119956 Patch 32142066
20-Oct-2020 12.1.0.2.201020 (Oct 2020) OJVM Component Patch Set Update Patch 31668915 Patch 31740134
14-Jul-2020 12.1.0.2.200714 (Jul 2020) OJVM Component Patch Set Update Patch 31219939 Patch 31465095
14-Apr-2020 12.1.0.2.200414 (Apr 2020) OJVM Component Patch Set Update Patch 30805558 Patch 31037459
14-Jan-2020 12.1.0.2.200114 (Jan 2020) OJVM Component Patch Set Update Patch 30502041 Patch 30671054
15-Oct-2019 12.1.0.2.191015 (Oct 2019) OJVM Component Patch Set Update Patch 30128197 Patch 30268189
16-Jul-2019 12.1.0.2.190716 (Jul 2019) OJVM Component Patch Set Update Patch 29774383 Patch 29837393
16-Apr-2019 12.1.0.2.190416 (Apr 2019) OJVM Component Patch Set Update Patch 29251241 Patch 29447962
15-Jan-2019 12.1.0.2.190115 (Jan 2019) OJVM Component Patch Set Update Patch 28790654 Patch 28994063
16-Oct-2018 12.1.0.2.181016 (Oct 2018) OJVM Component Patch Set Update Patch 28440711 Patch 28412299
17-Jul-2018 12.1.0.2.180717 (Jul 2018) OJVM Component Patch Set Update Patch 27923320 Patch 28135126
17-Apr-2018 12.1.0.2.180417 (Apr 2018) OJVM Component Patch Set Update Patch 27475603 Patch 27440294
16-Jan-2018 12.1.0.2.180116 (Jan 2018) OJVM Component Patch Set Update Patch 27001733 Patch 27001733
17-Oct-2017 12.1.0.2.171017 (Oct 2017) OJVM Component Patch Set Update Patch 26635845 Patch 26792364
18-Jul-2017 12.1.0.2.170718 (Jul 2017) OJVM Component Patch Set Update Patch 26027162 Patch 26182439
18-Apr-2017 12.1.0.2.170418 (Apr 2017) OJVM Component Patch Set Update Patch 25437695 Patch 25590993
17-Jan-2017 12.1.0.2.170117 (Jan 2017) OJVM Component Patch Set Update Patch 24917972 Patch 25112498
18-Oct-2016 12.1.0.2.161018 (Oct 2016) OJVM Component Patch Set Update Patch 24315824 Patch 24591630
19-Jul-2016 12.1.0.2.160719 (Jul 2016) OJVM Component Patch Set Update Patch 23177536 Patch 23515290
19-Apr-2016 12.1.0.2.160419 (Apr 2016) OJVM Component Patch Set Update Patch 22674709 Patch 22839633
19-Jan-2016 12.1.0.2.160119 (Jan 2016) OJVM Component Patch Set Update Patch 22139226 Patch 22311086
20-Oct-2015 12.1.0.2.5 (Oct 2015) OJVM Component Patch Set Update Patch 21555660 Patch 21788394
14-Jul-2015 12.1.0.2.4 (Jul 2015) OJVM Component Patch Set Update Patch 21068507 Patch 21153530
14-Apr-2015 12.1.0.2.3 (Apr 2015) OJVM Component Patch Set Update Patch 20415564 Patch 20391199
20-Jan-2015 12.1.0.2.2 (Jan 2015) OJVM Component Patch Set Update Patch 19877336 Patch 20225938
14-Oct-2014 12.1.0.2.1 (Oct 2014) OJVM Component Patch Set Update Patch 19282028 -

参考:Database 12.1.0.2 Proactive Patch Information (Doc ID 2285558.1)

发表在 Oracle安装升级 | 标签为 , | 留下评论