Category Archives: Oracle

触发器找出密码错误应用

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：触发器找出密码错误应用

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

经常会遇到修改oracle业务用户密码之后,由于部分应用密码没有被正确修改(忘记修改,或者修改错误了),导致数据库被hang或者用户被锁的情况,对于这样的情况,通过一个logon触发器可以快速找到

create or replace trigger logon_denied_to_alert
after servererror on database
declare
 message   varchar2(120);
 IP        varchar2(15);
 v_os_user varchar2(80);
 v_module  varchar2(50);
 v_action  varchar2(50);
 v_pid     varchar2(10);
 v_sid     number;
 v_username  varchar2(50);
 v_suser      varchar2(50);
begin
 IF (ora_is_servererror(1017)) THEN
   if sys_context('userenv', 'network_protocol') = 'tcp' then
     IP := sys_context('userenv', 'ip_address');
   else
     select distinct sid into v_sid from sys.v_$mystat;
     SELECT p.SPID
       into v_pid
       FROM V$PROCESS p, V$SESSION v
      WHERE p.ADDR = v.PADDR
        AND v.sid = v_sid;
   end if;
   v_os_user := sys_context('userenv', 'os_user');
   v_username := sys_context('userenv', 'CURRENT_USER');
   v_suser := SYS_CONTEXT('USERENV','SESSION_USER');
   dbms_application_info.READ_MODULE(v_module, v_action);
   message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||
              ' logon denied from '||v_username||' '||v_suser||' '|| nvl(IP, v_pid) || ' ' || v_os_user ||
              ' with ' || v_module || ' ' || v_action;
   sys.dbms_system.ksdwrt(2, message);
 end if;
end;
/

测试

SQL> create user test identified by oracle;

User created.

SQL> grant dba to test;

Grant succeeded.

直接本地登录

SQL> conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

---alert日志记录
Wed Nov 01 23:15:04 2023 logon denied from SYS  12886 oracle with sqlplus@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)

通过tns登录

SQL> conn  test/oracl1@172.27.54.81:1522/orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

---alert日志报错
Wed Nov 01 23:31:51 2023 logon denied from SYS  172.27.54.81 oracle with sqlplus@iZbp1hx0enix3hix1kvyrxZ (TNS V1-V3)

11.2.0.4最新patch信息（扩展补丁）–202310

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：11.2.0.4最新patch信息（扩展补丁）–202310

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

11.2.0.4 Database Patch Set Update

Release Date Version Download link Included in Windows Bundle
17-Oct-2023 11.2.0.4.231017 (Oct 2023) Database Patch Set Update (DB PSU) Patch 35574075 * Out of Error Correction
18-Jul-2023 11.2.0.4.230718 (Jul 2023) Database Patch Set Update (DB PSU) Patch 35269283 * Out of Error Correction
18-Apr-2023 11.2.0.4.230418 (Apr 2023) Database Patch Set Update (DB PSU) Patch 34998337 * Out of Error Correction
17-Jan-2023 11.2.0.4.230117 (Jan 2023) Database Patch Set Update (DB PSU) Patch 34677698 * Out of Error Correction
18-Oct-2022 11.2.0.4.221018 (Oct 2022) Database Patch Set Update (DB PSU) Patch 34386237 * Patch 34474433
19-Jul-2022 11.2.0.4.220719 (Jul 2022) Database Patch Set Update (DB PSU) Patch 34057724 * Patch 33883353
19-Apr-2022 11.2.0.4.220419 (Apr 2022) Database Patch Set Update (DB PSU) Patch 33711103 * Patch 33776715
18-Jan-2022 11.2.0.4.220118 (Jan 2022) Database Patch Set Update (DB PSU) Patch 33477185 * Patch 33488457
19-Oct-2021 11.2.0.4.211019 (Oct 2021) Database Patch Set Update (DB PSU) Patch 33128584 * Patch 33174351
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Database Patch Set Update (DB PSU) Patch 32758711 * Patch 32775108
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Database Patch Set Update (DB PSU) Patch 32328626 * Patch 32392141

11.2.0.4 Grid Infrastructure Patch Set Update

Release Date Version Download link
17-Oct-2023 11.2.0.4.231017 (Oct 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35685688
18-Jul-2023 11.2.0.4.230718 (Jul 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35369867
18-Apr-2023 11.2.0.4.230418 (Apr 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 35058300
17-Jan-2023 11.2.0.4.230117 (Jan 2023) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34774506
18-Oct-2022 11.2.0.4.221018 (Oct 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34536853
19-Jul-2022 11.2.0.4.220719 (Jul 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 34204533
19-Apr-2022 11.2.0.4.220419 (Apr 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33829709
18-Jan-2022 11.2.0.4.220118 (Jan 2022) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33575261
19-Oct-2021 11.2.0.4.211019 (Oct 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 33248354
20-Jul-2021 11.2.0.4.210720 (Jul 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 32917428
20-Apr-2021 11.2.0.4.210420 (Apr 2021) Grid Infrastructure Patch Set Update (GI PSU) * Patch 32495145

11.2.0.4 OJVM Patch Set Update

Release Date Version Unix PSU Patch Windows Bundle Patch
17-Oct-2023 11.2.0.4.231017 (Oct 2023) OJVM Component Patch Set Update Patch 35685663 * Out of Error Correction
18-Jul-2023 11.2.0.4.230718 (Jul 2023) OJVM Component Patch Set Update Patch 35349843 * Out of Error Correction
18-Apr-2023 11.2.0.4.230418 (Apr 2023) OJVM Component Patch Set Update Patch 35039831 * Out of Error Correction
17-Jan-2023 11.2.0.4.230117 (Jan 2023) OJVM Component Patch Set Update Patch 34763334 * Out of Error Correction
18-Oct-2022 11.2.0.4.221018 (Oct 2022) OJVM Component Patch Set Update Patch 34412266 * Patch 34513867
19-Jul-2022 11.2.0.4.220719 (Jul 2022) OJVM Component Patch Set Update Patch 34085652 * Patch 34185082
19-Apr-2022 11.2.0.4.220419 (Apr 2022) OJVM Component Patch Set Update Patch 33829826 * Patch 33881335
18-Jan-2022 11.2.0.4.220118 (Jan 2022) OJVM Component Patch Set Update Patch 33561250 * Patch 33577510
19-Oct-2021 11.2.0.4.211019 (Oct 2021) OJVM Component Patch Set Update Patch 33192642 * Patch 33248711
20-Jul-2021 11.2.0.4.210720 (Jul 2021) OJVM Component Patch Set Update Patch 32876451 * Patch 32905855
20-Apr-2021 11.2.0.4.210420 (Apr 2021) OJVM Component Patch Set Update Patch 32671980 * Patch 32428494

再现ORA-600 4000故障处理

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：再现ORA-600 4000故障处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有一个10g的库，由于redo损坏导致无法正常recover成功
20231018111911

正常途径无法open成功,尝试强制打开库

Wed Oct 18 11:23:25 2023
alter database open resetlogs
Wed Oct 18 11:23:25 2023
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 236715370
Resetting resetlogs activation ID 4009817354 (0xef00f50a)
Wed Oct 18 11:23:26 2023
Setting recovery target incarnation to 3
Wed Oct 18 11:23:26 2023
Assigning activation ID 4271120987 (0xfe94225b)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\ORADATA\EAIS\REDO01.LOG
Successful open of redo thread 1
Wed Oct 18 11:23:26 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Oct 18 11:23:26 2023
SMON: enabling cache recovery
Wed Oct 18 11:23:27 2023
Errors in file d:\hys_db\admin\eais\udump\eais_ora_10916.trc:
ORA-00600: 内部错误代码, 参数: [4000], [9], [], [], [], [], [], []

Wed Oct 18 11:23:28 2023
Errors in file d:\hys_db\admin\eais\udump\eais_ora_10916.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [4000], [9], [], [], [], [], [], []

Wed Oct 18 11:23:28 2023
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Wed Oct 18 11:23:28 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_pmon_1764.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_reco_9628.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_smon_9884.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_ckpt_10096.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_lgwr_8932.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_dbw0_9892.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_mman_9472.trc:
ORA-00704: bootstrap process failure

Wed Oct 18 11:23:29 2023
Errors in file d:\hys_db\admin\eais\bdump\eais_psp0_7692.trc:
ORA-00704: bootstrap process failure

Instance terminated by USER, pid = 10916
ORA-1092 signalled during: alter database open resetlogs...

resetlogs失败,报ora-600 4000错误,查看相关trace文件

*** 2023-10-18 11:23:27.103
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [4000], [9], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedst+38           CALLrel  _ksedst1+0           0 1
_ksedmp+898          CALLrel  _ksedst+0            0
_ksfdmp+14           CALLrel  _ksedmp+0            3
603A816A             CALLreg  00000000             6AE8258 3
603A8550             CALLrel  603A80D8             6AE8258 4FB1090 FA0 1 8C886B0
__VInfreq__ktudba+1  CALLrel  _kgeasi+0            6AE8258 4FB1090 FA0 2 1 0 9 0
26                                                 
_ktrgcm+851          CALLrel  _ktudba+0            9 8C88B68 0 0
_ktrgtc+526          CALLrel  _ktrgcm+0            4FC753C
_kdsgrp+937          CALLrel  _ktrgtc+0            4FC753C 4FC74E0 1F1FB00
                                                   8C88CB4 240 2AED19C 1F1F71C
_kdsfbrcb+432        CALLrel  _kdsgrp+0            4FC7538 0 4FC7538
_qertbFetchByRowID+  CALLrel  _kdsfbrcb+0          4FC7538 4FDF004 0 1 0 0
4344                                               4FC74E0 22E8078 8C88E14 0
_opifch2+3104        CALL???  00000000             8DA05838 20135FC 8C890AC 1
_opifch+51           CALLrel  _opifch2+0           89 5 8C891F0
_opiodr+1099         CALLreg  00000000             5 2 8C89938
_rpidrus+178         CALLrel  _opiodr+0            5 2 8C89938 5
_rpidru+88           CALLrel  _rpidrus+0           8C894C4
_rpiswu2+426         CALLreg  00000000             8C8988C
_rpidrv+1461         CALLrel  _rpiswu2+0           90F3CCB4 0 8C89860 2 8C89880
                                                   0 8C89860 0 84DF58 84E018
                                                   8C8988C 8
_rpifch+44           CALLrel  _rpidrv+0            5 5 8C89938 8
_kqdpts+166          CALLrel  _rpifch+0            5 5 5 3 90AE2383 7 C 0 0 0 0
                                                   0 0 0 5 2 90AE237C 7 C 0 0 0
                                                   0 0 0 0
_kqrlfc+478          CALLrel  _kqdpts+0            90AE21F4
_kqlbplc+102         CALLrel  _kqrlfc+0            
_kqlblfc+240         CALLrel  _kqlbplc+0           0
_adbdrv+12911        CALLrel  _kqlblfc+0           0 8C8D034
_opiexe+11778        CALLrel  _adbdrv+0            
_opiosq0+6088        CALLrel  _opiexe+0            4 0 8C8D894
_kpooprx+232         CALLrel  _opiosq0+0           3 E 8C8D9AC A4
_kpoal8+775          CALLrel  _kpooprx+0           8C8F6CC 8C8E248 1D 1 0 A4
_opiodr+1099         CALLreg  00000000             5E 17 8C8F6C8
60FEFF8D             CALLreg  00000000             5E 17 8C8F6C8 0
_opitsk+1017         CALL???  00000000             
_opiino+1087         CALLrel  _opitsk+0            0 0
_opiodr+1099         CALLreg  00000000             3C 4 8C8FC60
_opidrv+819          CALLrel  _opiodr+0            3C 4 8C8FC60 0
_sou2o+45            CALLrel  _opidrv+0            3C 4 8C8FC60
_opimai_real+112     CALLrel  _sou2o+0             8C8FC54 3C 4 8C8FC60
_opimai+92           CALLrel  _opimai_real+0       2 8C8FC8C
_OracleThreadStart@  CALLrel  _opimai+0            
4+708                                              
74A93438             CALLptr  00000000             
76F09830             CALLreg  00000000             
76F09800             CALLrel  76F0980B             
 
--------------------- Binary Stack Dump ---------------------

通过一些技巧处理数据库正常open,导出数据完成本次恢复，参考以前恢复类似case:

重现ORA-600 4000异常
通过bbed解决ORA-00600[4000]案例
记录一次ORA-600 4000数据库故障恢复
ORACLE 8.1.7 数据库ORA-600 4000故障恢复

ORA-600 12807 修复

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-600 12807 修复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

这次阳了有点严重，客户现场打patch无法去，在家里远程值守,在电脑前面闲着就查询和重现了最近朋友和我说的他们的客户遇到ORA-600 12807的故障.查询了下mos,基本上可以确认是由于CON$.CON#达到理论最大值无法继续增加从而报该错误,参考文档:
Mechanism to Recycle Database Constraint Identifiers (Doc ID 2925056.1)
Bug 13781691 – ORA-600 [12807] if CON$.CON# very high due to bug 13784384 (Doc ID 13781691.8)
Bug 25343563 – Mechanism to Implement Constraint Identifier (con#) Recycling (Doc ID 25343563.8)
在12及其之后的版本中oracle发布了patch 25343563 并设置event启用该patch进行解决.但是如果是12c之前版本,官方没有提供直接的解决方案.最基本的解决方法就是进行数据逻辑迁移,以及避免频繁创建约束导致con$.con#消耗太大

通过试验重现该错误

SQL> create table t_xff  (id number not null,name varchar2(100) not null);
create table t_xff  (id number not null,name varchar2(100) not null)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12807], [], [], [], [], [], [], [],
[], [], [], []

SQL>  create table t_xff  (id number,name varchar2(100));

Table created.

SQL> alter table t_xff add primary key(id);
alter table t_xff add primary key(id)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [12807], [], [], [], [], [], [], [],
[], [], [], []


SQL> select con# from sys.con$ where name='_NEXT_CONSTRAINT';

      CON#
----------
4294967294

通过一些底层分析,并对部分底层基表进行patch实现数据库可以继续创建约束

SQL>  alter table t_xff add primary key(id);

Table altered.

使用临时的patch方法,可以快速的恢复业务,后续找适当时间点安排迁移.
在此提醒:对于一些创建中间对象或者临时对象频繁的系统(特别是大量主键,not null等)注意检查该值距离天花板距离,如果比较接近了最好安排一次逻辑迁移和找出来原因(是oracle bug还是应用触发)

ORA-07445: exception encountered: core dump [kdxlin()+4088]处理

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-07445: exception encountered: core dump [kdxlin()+4088]处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

abort方式关闭数据库，启动报错

Tue Sep 19 21:52:56 2023
NOTE: dependency between database orcl and diskgroup resource ora.DATA.dg is established
Tue Sep 19 21:52:57 2023
Reconfiguration started (old inc 4, new inc 6)
List of instances:
 1 (myinst: 1) 
 Global Resource Directory frozen
 * dead instance detected - domain 0 invalid = TRUE 
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Tue Sep 19 21:52:57 2023
Tue Sep 19 21:52:57 2023
 LMS 3: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Sep 19 21:52:57 2023
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Sep 19 21:52:57 2023
 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
 Post SMON to start 1st pass IR
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Tue Sep 19 21:53:05 2023
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_28917.trc  (incident=492333):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
Incident details in:/u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_492333/orcl1_ora_28917_i492333.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-600 signalled during: ALTER DATABASE MOUNT /* db agent *//* {1:34652:2} */...

重建ctl之后，尝试recover数据库报错ORA-600 3020和ORA-07445 kdxlin等错误

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [41], [3142531],
[175108995], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 41, block# 3142531, file
offset is 4268777472 bytes)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 41: '+DATA/orcl/datafile/xifenfei07.dbf'
ORA-10560: block type 'FIRST LEVEL BITMAP BLOCK'

Wed Sep 20 00:15:00 2023
ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 64 slaves
Wed Sep 20 00:15:02 2023
Recovery of Online Redo Log: Thread 2 Group 6 Seq 67008 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_6.268.942097791
Recovery of Online Redo Log: Thread 1 Group 2 Seq 81767 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_2.262.942097651
Recovery of Online Redo Log: Thread 1 Group 5 Seq 81768 Reading mem 0
  Mem# 0: +DATA/orcl/onlinelog/group_5.263.942097651
Wed Sep 20 00:15:08 2023
Hex dump of (file 41, block 3142531) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr1m_45463.trc
Reading datafile '+DATA/orcl/datafile/ts_his3bz07.dbf' for corruption at rdba: 0x0a6ff383 (file 41, block 3142531)
Reread (file 41, block 3142531) found different corrupt data (logically corrupt)
Hex dump of (file 41, block 3142531) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr1m_45463.trc
Wed Sep 20 00:15:08 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC] [PC:0x95FB582, kdxlin()+4088][flags: 0x0,count:1]
Wed Sep 20 00:15:08 2023
Exception [type: SIGSEGV, Address not mapped to object][ADDR:0xC] [PC:0x95FB582, kdxlin()+4088][flags: 0x0,count:1]
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_pr10_45419.trc  (incident=564584):
ORA-07445: exception encountered:core dump [kdxlin()+4088][SIGSEGV][ADDR:0xC][PC:0x95FB582][Address not mapped to object]
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl1/incident/incdir_564640/orcl1_pr17_45433_i564640.trc

尝试随机恢复文件，也遭遇ORA-07445 kdxlin异常

SQL> recover datafile 34;
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 34, block#
1999809)
ORA-10564: tablespace XIFENFEI
ORA-01110: data file 34: '+DATA/orcl/datafile/xifeifenfei06'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 97961
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB582] [Address not mapped to object] []

出现这种情况是由于redo和数据文件块不一致导致无法正常应用日志,人工对于异常的block进行处理,数据库open成功,然后遭遇undo回滚段异常,对其进行规避,数据库open并且稳定运行