分类目录归档：Oracle

RFS[22349]: Database mount ID mismatch

发表于 2023 年 11 月 6 日 由 惜分飞

今天检查数据库发现一套主库上面报大量RFS[22349]: Database mount ID mismatch之类的错误

[oracle@hisdb2 trace]$ tail -f alert_hisdb2.log 
LNS: Standby redo logfile selected for thread 2 sequence 133319 for destination LOG_ARCHIVE_DEST_2
Mon Nov 06 20:56:09 2023
RFS[27080]: Assigned to RFS process 15518
RFS[27080]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27081]: Assigned to RFS process 16299
RFS[27081]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:57:09 2023
RFS[27082]: Assigned to RFS process 16319
RFS[27082]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)

这种错误表示rfs进程在传输的时候检测到database mount id不匹配(也就是认为这个库不是主库该传输或者接受日志的),出现类似这样的错误,大概率是由于这个dg成员(主库和备库)中应该有不匹配的库,回想这个库近期的操作,突然想到对这个主库在虚拟化平台的备库做了一次克隆,然后吧克隆库激活作为测试库的操作.基于此种情况,先判断该库是否和上次克隆的库之间创建有会话连接

--主库
[oracle@hisdb2 trace]$ netstat -natp|grep 192.168.106.63
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.115.12:52785        192.168.106.63:1521         ESTABLISHED -                   
tcp        0      0 192.168.115.14:1521         192.168.106.63:21598        TIME_WAIT   - 

--克隆库
[oracle@HIS_DG ~]$ netstat -atpn|grep 192.168.115.12
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 ::ffff:192.168.106.63:1521  ::ffff:192.168.115.12:52785 ESTABLISHED 6126/oraclehisdb

两个库之间确实存在会话,检查克隆库相关的dg配置

[oracle@HIS_DG ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 21:01:05 2023

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter fal;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string      HISDBDG
fal_server                           string      HISDB
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> show parameter archive;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     1800
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                 DEST valid_for=(all_logfiles,a
                                                 ll_roles) db_unique_name=hisdb
                                                 dg
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
log_archive_dest_2                   string      service=hisdb lgwr async valid
                                                 _for=(online_logfiles,primary_
                                                 role) db_unique_name=hisdb

该库的dg配置没有清除,当该库变为主库时,log_archive_dest_2刚好指向主库,从而使得主库上出现了类似RFS[22349]: Database mount ID mismatch的错误.处理方法是清除掉克隆库上面dg备库相关配置

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> alter system set fal_server='';

System altered.

主库日志中未再出现类似错误

Mon Nov 06 20:48:08 2023
RFS[22349]: Assigned to RFS process 7383
RFS[22349]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
[oracle@hisdb1 trace]$ tail -f alert_hisdb1.log 
RFS[22352]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 20:58:09 2023
RFS[22353]: Assigned to RFS process 14958
RFS[22353]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22354]: Assigned to RFS process 18580
RFS[22354]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:01:09 2023
RFS[22355]: Assigned to RFS process 18598
RFS[22355]: Database mount ID mismatch [0x7afb8c50:0x70b2cf7b] (2063305808:1890766715)
Mon Nov 06 21:03:28 2023
Thread 1 advanced to log sequence 129769 (LGWR switch)
  Current log# 9 seq# 129769 mem# 0: +DATA/hisdb/onlinelog/group_9.276.976991877
  Current log# 9 seq# 129769 mem# 1: +FRA/hisdb/onlinelog/group_9.2334.976991877
Mon Nov 06 21:03:28 2023
Archived Log entry 524082 added for thread 1 sequence 129768 ID 0x70864b41 dest 1:
Mon Nov 06 21:03:28 2023
LNS: Standby redo logfile selected for thread 1 sequence 129769 for destination LOG_ARCHIVE_DEST_2

事后在MOS上有一篇文档供参考:Database mount ID mismatch ORA-16009: invalid redo transport destination (Doc ID 1450132.1)

发表在 Data Guard | 评论关闭

触发器找出密码错误应用

发表于 2023 年 11 月 1 日 由 惜分飞

经常会遇到修改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)
发表在 Oracle 开发 | 标签为 | 评论关闭

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

发表于 2023 年 10 月 19 日 由 惜分飞

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
发表在 Oracle | 标签为 , | 评论关闭