首页 » GoldenGate, ORACLE, ORACLE [C]系列 » Oracle12c R2注意事项: Active DataGuard logon fail with ORA-00604& ORA-04024

Oracle12c R2注意事项: Active DataGuard logon fail with ORA-00604& ORA-04024

这是一套12c  R2 4-nodes Oracle RAC on RHEL 7的环境,已安装0417 RU。 该库有一套Phyical DataGard, 同时也是GoldenGate的target端,存在一个replicat 进程同步数据。 一日收到该数据库归档空间(in ASM) DiskgrouP 使用率告警,后分析刚上线没多久就趟了一个雷。这里简单记录过程。

— DB alert log

2019-04-12 17:31:43.791000 +08:00
krsd_check_stuck_arch: stuck archiver condition cleared
Unable to create archive log file '+ARCHDG'
Errors in file /oracle/app/oracle/diag/rdbms/anbob/anbob2/trace/anbob2_arc1_64728.trc:
ORA-19504: failed to create file "+ARCHDG"
ORA-17502: ksfdcre:4 Failed to create file +ARCHDG
ORA-15041: diskgroup "ARCHDG" space exhausted
ARC1: Error 19504 Creating archive log file to '+ARCHDG'
krsd_check_stuck_arch: stuck archiver: insufficient local LADs
krsd_check_stuck_arch: stuck archiver condition declared

— 手动清理日志

RMAN> list archive log all;
...
RMAN> delete archivelog until time "sysdate-1";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7428 instance=anbob2 device type=DISK
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1887.326.1005219643 thread=1 sequence=1887
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1888.277.1005226879 thread=1 sequence=1888
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1889.324.1005230519 thread=1 sequence=1889
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1890.302.1005232111 thread=1 sequence=1890
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1891.269.1005233425 thread=1 sequence=1891
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1892.259.1005234787 thread=1 sequence=1892
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1893.332.1005237411 thread=1 sequence=1893
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+ARCHDG/anbob/ARCHIVELOG/2019_04_10/thread_1_seq_1894.341.1005240287 thread=1 sequence=1894

TIP:
当然该库有DG,目录的日志还未同步应用,虽然强制(with force option)删除影响了DG环境,后期还要补救过于麻烦,当然主库恢复业务优先, 确认了一下ASM DISKGROUP可用空间和归档日志日生成量,决定先临时修改归档路径到DATA所在的ASM DISKGROUP。

SQL> @asmdg

GROUP_NUMBER NAME                           SECTOR_SIZE LOGICAL_SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V     CON_ID
------------ ------------------------------ ----------- ------------------- ---------- -------------------- ----------- ------ ---------- ---------- ----------- ------------ ----------------------- -------------- ------------- ------------------------------------------------------------ ------------------------------------------------------------ - ----------
           1 ARCHDG                                 512                 512       4096              4194304 CONNECTED   EXTERN    1048576        384           0      1048192                       0            384             0 12.2.0.1.0                                                   10.1.0.0.0                                                   N          0
           2 DATADG                                 512                 512       4096              4194304 CONNECTED   EXTERN    9437184    1141244           0      8295940                       0        1141244             0 12.2.0.1.0                                                   10.1.0.0.0                                                   N          0
           3 MGMTDG                                 512                 512       4096              4194304 MOUNTED     EXTERN     102400      66152           0        36248                       0          66152             0 12.2.0.1.0                                                   10.1.0.0.0                                                   N          0
           4 OCRDG                                  512                 512       4096              4194304 MOUNTED     NORMAL      10240       9084           0         1156                    2048           3518             0 12.2.0.1.0                                                   10.1.0.0.0                                                   Y          0

SQL> @logswith
  THREAD# date        Day total  h00  h01  h02  h03  h04  h05  h06  h07  h08  h09  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
---------- ----------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
         1 02 APR 2019 Tue    30    2    0    0    2    0    2    0    2    2    2    0    2    0    0    2    0   10    0    2    0    0    2    0    0
         2 02 APR 2019 Tue    44    2    4    0    0    0    0    2    2    2    2    2    2    0    2    4    2    8    2    2    2    2    2    0    0
         3 02 APR 2019 Tue    36    2    2    0    0    0    2    0    2    2    2    0    2    0    2    2    2   10    2    0    2    0    2    0    0
         4 02 APR 2019 Tue    56    2    4    0    2    0    2    2    2    6    6    4    2    0    2    2    4    8    2    2    2    0    2    0    0
         1 03 APR 2019 Wed    24    0    0    0    0    0    4    2    0    2    2    2    2    2    0    2    2    0    2    2    0    0    0    0    0
         2 03 APR 2019 Wed    42    0    0    0    0    0    0    2    0    2    4    4    4    4    4    2    4    2    4    2    2    0    2    0    0
         3 03 APR 2019 Wed    36    0    0    0    0    0    0    2    0    2    4    2    4    4    2    4    2    2    4    2    0    2    0    0    0
         4 03 APR 2019 Wed    50    2    0    0    0    2    0    2    2    6    6    6    2    4    2    4    2    2    4    0    2    2    0    0    0
         1 04 APR 2019 Thu    22    2    0    0    0    0    4    0    0    2    2    2    2    0    2    0    2    0    2    0    2    0    0    0    0
         2 04 APR 2019 Thu    34    2    0    0    0    0    2    0    0    2    2    2    4    2    2    4    2    2    2    2    0    2    2    0    0
         3 04 APR 2019 Thu    32    2    0    0    0    0    0    2    0    2    2    4    2    2    4    2    2    2    2    0    2    0    0    2    0
         4 04 APR 2019 Thu    48    2    0    2    0    0    2    2    0    6    6    6    4    2    2    2    2    2    2    2    2    0    2    0    0
         1 05 APR 2019 Fri    20    0    2    0    0    0    2    0    2    2    2    2    0    0    2    0    0    2    0    0    2    0    0    2    0
         2 05 APR 2019 Fri    28    0    0    0    0    0    0    2    0    4    2    2    0    2    2    2    0    2    2    0    2    2    2    2    0
         3 05 APR 2019 Fri    20    0    0    0    0    0    0    2    0    4    2    0    2    0    2    0    0    2    2    0    0    2    0    2    0
         4 05 APR 2019 Fri    42    0    2    0    2    0    0    2    2   10    6    2    0    2    2    2    0    2    0    2    0    2    2    0    2
         1 06 APR 2019 Sat    46    0    0    2    2    2    4    4    0    4    4    6    4    4    2    4    2    0    0    2    0    0    0    0    0
         2 06 APR 2019 Sat    82    0    0    6    2    2    4    6    4    2    8   12    8   12    4    2    2    2    2    0    2    0    0    2    0
         3 06 APR 2019 Sat    64    0    0    4    4    2    4    2    2    4    2    4    6    8    8    8    2    0    0    2    0    0    2    0    0
         4 06 APR 2019 Sat    80    0    2    2    2    4    2    4    4    8    4    8   10    6    6    4    8    0    2    2    0    0    2    0    0
         1 07 APR 2019 Sun    14    0    0    0    0    2    2    0    0    2    2    0    2    0    0    0    2    0    0    0    0    0    2    0    0
         2 07 APR 2019 Sun    18    0    0    0    0    0    0    2    0    2    0    2    0    2    0    2    2    0    2    0    2    0    0    2    0
         3 07 APR 2019 Sun    12    0    0    0    0    0    0    2    0    2    0    2    0    0    2    0    2    0    0    2    0    0    0    0    0
         4 07 APR 2019 Sun    32    0    2    0    0    2    0    2    2    4    4    4    2    0    2    0    2    2    0    2    0    0    2    0    0
         1 08 APR 2019 Mon    18    0    0    0    0    2    2    0    0    4    2    2    0    0    2    0    0    2    0    0    0    0    0    2    0
         2 08 APR 2019 Mon    30    0    2    0    0    0    0    2    0    6    2    2    2    0    2    2    2    2    0    2    0    2    0    2    0
         3 08 APR 2019 Mon    18    2    0    2    0    0    0    2    0    2    4    0    0    2    0    0    2    0    0    2    0    0    0    0    0
         4 08 APR 2019 Mon    38    0    2    2    0    2    0    2    2    6    8    4    0    2    0    2    0    2    0    2    0    2    0    0    0
         1 09 APR 2019 Tue    12    0    0    0    0    0    2    0    0    2    2    2    0    0    0    0    2    0    0    0    0    0    2    0    0
         2 09 APR 2019 Tue    18    0    0    0    0    0    0    2    0    2    0    2    2    0    2    0    2    2    0    2    0    0    2    0    0
         3 09 APR 2019 Tue    14    0    2    0    0    0    0    0    2    0    2    2    0    0    2    0    0    0    2    0    0    0    0    2    0
         4 09 APR 2019 Tue    30    2    0    0    0    2    0    2    2    4    4    4    0    2    0    2    0    2    0    2    0    0    2    0    0
         1 10 APR 2019 Wed    34    0    0    0    0    0    4    0    0    4    2    6    2    0    2    2    6    2    2    2    0    0    0    0    0
         2 10 APR 2019 Wed    58    0    0    0    0    0    2    0    0    6    4    6    4    2    2    4   16    6    2    2    0    0    2    0    0
         3 10 APR 2019 Wed    46    0    0    0    0    0    0    0    2    4    2    4    4    2    0    4   10    4    4    2    2    2    0    0    0
         4 10 APR 2019 Wed    66    0    0    2    0    0    0    2    2    8    6    6    6    2    2    6   10    4    4    4    0    0    2    0    0
         1 11 APR 2019 Thu    52    0    0    2    2    0    6    2    2    2    6    6    4    4    2    4    4    2    4    0    0    0    0    0    0
         2 11 APR 2019 Thu   106    0    0    4    2    2    4    4    4    6   12   12   10   10    8    6    6    6    6    0    2    0    0    2    0
         3 11 APR 2019 Thu    90    0    0    4    4    2    2    4    0    8    8   10    8    8    6    8    8    4    4    0    2    0    0    0    0
         4 11 APR 2019 Thu   110    0    2    0    2    4    2    4    2   10   12   10   12    6    6   10    8   10    6    0    2    0    0    2    0
         1 12 APR 2019 Fri    22    0    0    2    0    0    2    0    0    4    2    4    2    0    0    2    2    2    0    0    0    0    0    0    0
         2 12 APR 2019 Fri    36    0    0    0    0    2    0    0    0    4    6    4    4    0    2    4    4    4    2    0    0    0    0    0    0
         3 12 APR 2019 Fri    28    0    0    2    0    0    0    0    0    4    4    4    2    2    0    2    4    4    0    0    0    0    0    0    0
         4 12 APR 2019 Fri    54    0    0    2    0    2    0    2    0   10    8    8    6    0    0    6    4    6    0    0    0    0    0    0    0
                           -----      ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
maximum                      110         4    6    4    4    6    6    4   10   12   12   12   12    8   10   16   10    6    4    2    2    2    2    2

TIP:
当然近两日的归档量也有明显增长。 修改归档路径后主库很快得以恢复, 归档没有清理任务么? 不,我们当然有,还有两套。一套用于删除主备已应用归档,一套用于备库使用率达80%强置清理。 还有主库的使用率告警,当然这次告警没提醒出来问题又出在层层的审批上,不在讨论范围。

主库归档满原因是因为主库归档没删,主库归档没删原因是因为备库没应用,没应用原因是因为没有传到备库, 没传备库原因是因为备库归档也满了,备库的归档满又是什么原因呢? 下一步查看我们的清理脚本日志。

*************************
zzz ***Fri Apr 12 17:00:03 CST 2019
Current archive location usage:100%
Note: Current DG USAGE: 100

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 12 17:00:03 2019

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 2
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x21FC81808
The current working directory: /home/oracle
Shell: /home/oracle/sdbo/archivelog_clear.sh
current user: oracle
current ORACLE_SID: anbob1
2019-04-12 17:20:01
Current archivelog mode: Archive
archive destination : +ARCHDG
archive destination type : ASM

Note:
我们的备库清理调度也出现在使用率过高, 归档在ASM中,调用了RMAN清理, 但是rman在连接数据库时提示ORA-604和ORA-4024,手动sqlplus登录备库的node1, 同样提示该错误.

oracle@kdanbob1:/home/oracle>  ora  

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 12 18:00:39 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

ERROR at line 23:
ORA-00604: error occurred at recursive SQL level 5
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x21FC81808

同时登录备库的2,3,4节点提示ORA-01075错误

oracle@kdanbob3:/home/oracle>  ora  

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 12 18:07:37 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-01075: you are currently logged on

Tip:
备库目前无业务,为了恢复备库应用,尝试重启了数据库。节点1可以shutdown ,其它节点通过kill pmon。再次重启恢复正常。

# standby node1 db alert log

2019-04-12T08:28:08.372598+08:00
ARC5: Archiving not possible: error count exceeded
2019-04-12T08:33:08.438192+08:00
Unable to create archive log file '+ARCHDG'
2019-04-12T08:33:08.438508+08:00
Errors in file /oracle/app/oracle/diag/rdbms/stdanbob/anbob1/trace/anbob1_arc1_11549.trc:
ORA-19504: failed to create file "+ARCHDG"
ORA-17502: ksfdcre:4 Failed to create file +ARCHDG
ORA-15041: diskgroup "ARCHDG" space exhausted
ARC1: Error 19504 Creating archive log file to '+ARCHDG'
2019-04-12T08:33:08.455993+08:00
ARC2: Archiving not possible: error count exceeded
2019-04-12T08:33:08.470123+08:00
ARC3: Archiving not possible: error count exceeded
2019-04-12T08:33:52.231688+08:00
Non critical error ORA-48913 caught while writing to trace file "/oracle/app/oracle/diag/rdbms/stdanbob/anbob1/trace/anbob1_w00a_11310.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [1024000] reached

# trace file anbob1_w00a..
— call stack 已格式化
sed -n ‘/Call Stack Trace/,/call_stack_dump/p’|sed -n ‘5,/*/p’|cut -c1-29|sed ‘/^[[:space:]]*$/d’|awk -F”(” ‘{printf $1 ” <-” } NR%5==0{printf “\n”}’

*** 2019-04-05T11:24:48.744777+08:00
*** SESSION ID:(11237.49015) 2019-04-05T11:24:48.744825+08:00
*** CLIENT ID:() 2019-04-05T11:24:48.744833+08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2019-04-05T11:24:48.744840+08:00
*** MODULE NAME:(KTSJ) 2019-04-05T11:24:48.744848+08:00
*** ACTION NAME:(KTSJ Slave) 2019-04-05T11:24:48.744856+08:00
*** CLIENT DRIVER:() 2019-04-05T11:24:48.744863+08:00

---- Call Stack Trace -----

ksedst <-kxsGetRuntimeLock <kkscsCheckCursor <-
kkscsSearchChildList <-kksfbc <-kkspsc0 <-kksParseCursor <-
<-opiosq0 <-opiall0 <-opikpr <-opiodr <-
rpidrus <-skgmstack <-rpidru <-rpiswu2 <-kprball <-
kqdGetBundledCursor call <- <-kqdobr_new <-kqrReadFromDB <-kqrpre1 <-
kkdlSetTableVersion call <- <-kkdlgstd <-kkmfcbloCbk <-kkmpfcbk <-
qcsprfro <-qcsprfro_tree <-qcsprfro_tree <-qcspafq <-qcspqbDescendents <-
<-qcspqb <-kkmdrv <-opiSem <-opiprs <-
kksParseChildCursor <-rpiswu2 <-kksLoadChild <-kxsGetRuntimeLock <-
<-kksfbc <-kkspsc0 <-kksParseCursor <-
opiosq0 <-opiall0 <-opikpr <-opiodr <-rpidrus <-
skgmstack <-rpidru <-rpiswu2 <-kprball <-kqdGetBundledCursor call <-
<-kqdobr_new <-kqrReadFromDB <-kqrpre1 <-kkdlSetTableVersion call <-
<-kkdlgstd <-kkmfcbloCbk <-kkmpfcbk <-qcsprfro <-
qcsprfro_tree <-qcsprfro_tree <-qcspafq <-qcspqbDescendents <-
qcspqb <-kkmdrv <-opiSem <-opiDeferredSem <-
opitca <-kksFullTypeCheck <-rpiswu2 <-kksLoadChild <-
kxsGetRuntimeLock <-kksfbc <-kkspsc0 <-kksParseCursor <-
<-opiosq0 <-kpooprx <-kpoal8 <-opiodr <-
kpoodrc <-rpiswu2 <-kpoodr <-upirtrc <-kpurcsc <-
kpuexec <-OCIStmtExecute <-ktslj_segmon <-ktslj_lobmon <-ktsj_task_switch <-
<-ktsj_execute_task <-ktsj_slave_main <
ksvrdp_int <-opirip <-opidrv <-sou2o <-opimai_real <-
ssthrdmain <-main <-__libc_start_main <-+245 <-_start <-

Maximum map count configured per process:  65530
===================================================
PROCESS STATE
-------------
Process global information:
     process: 0x3327efe60, call: 0x2169daef0, xact: (nil), curses: 0x353bc5da8, usrses: 0x343c899d0
     in_exception_handler: no
  ----------------------------------------
  SO: 0x3327efe60, type: 2, owner: (nil), flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
   proc=0x3327efe60, name=process, file=ksu.h LINE:15729, pg=0, conuid=0
  (process) Oracle pid:52, ser:205, calls cur/top: 0x2169daef0/0x27ecc5ad8
            flags : (0x2) SYSTEM  icon_uid:0 logon_pdbid=0
            flags2: (0x30),  flags3: (0x10) 
            call error: 0, sess error: 0, txn error 0
            intr queue: empty
    (post info) last post received: 307 0 3
                last post received-location: ksl2.h LINE:4497 ID:kslpsr
                last process to post me: 0x302838628 1 2
                last post sent: 0 0 85
                last post sent-location: kso2.h LINE:1054 ID:ksoreq_reply
                last process posted by me: 0x302838628 1 2
                waiter on post event: 0
    (latch info) hold_bits=0x0 ud_influx=0x0
    (osp latch info) hold_bits=0x0 ud_influx=0x0
    Process Group: DEFAULT, pseudo proc: 0x302cd0920
    O/S info: user: oracle, term: UNKNOWN, ospid: 81006 
    OSD pid info: 
    KGL-UOL (Process state object)
    KGX Atomic Operation Log 0x3327f0b28
     Mutex (nil)(0, 0) idn 0 oper NONE(0)
     FSO mutex uid 65534 efd 0 whr 0 slp 0
...
    KGX Atomic Operation Log 0x3327f0e68
     Mutex (nil)(0, 0) idn 0 oper NONE(0)
     FSO mutex uid 65534 efd 0 whr 0 slp 0
    ----------------------------------------
    SO: 0x2931f29d8, type: 91, owner: 0x3327efe60, flag: INIT/-/-/-/0x00 if: 0x1 c: 0x1
     proc=0x3327efe60, name=KTSJ state object, file=ktsjcts.h LINE:915, pg=0, conuid=0
    KTSJProc Type: Slave - 7
KTSJTASK ptr:0x2a46365e8
tid:1  class:LOB Monitor  status:0x3(READY/RUN/-/-/-/-)
tdata:(nil) tdatal:0
lastrun:0 lastfin:0 currun:0 nextrun:0
con_uid:0 flag:0x0(-)
    ----------------------------------------
    SO: 0x343c899d0, type: 4, owner: 0x3327efe60, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
     proc=0x3327efe60, name=session, file=ksu.h LINE:15737, pg=0, conuid=0
    (session) sid: 11237 ser: 49015 trans: (nil), creator: 0x3327efe60
              flags: (0x51) USR/- flags2: (0x80409) -/-/INC
              flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
              DID: 0001-0034-00000A780001-0034-00000A79, short-term DID: 
              txn branch: (nil)
              edition#: 0              user#/name: 0/SYS
              oct: 3, prv: 0, sql: 0x27ff93570, psql: 0x22e2381c8
              stats: 0x26fd158c8, PX stats: 0x11089e04
    service name: SYS$BACKGROUND
    Current Wait Stack:
      Not in wait; last wait ended 0.245902 sec ago 
    Wait State:
      fixed_waits=0 flags=0x21 boundary=(nil)/-1
    Session Wait History:
        elapsed time of 0.245932 sec since last wait
     0: waited for 'PGA memory operation'
        =0x10000, =0x1, =0x0
        wait_id=40 seq_num=41 snap_id=1
        wait times: snap=0.000016 sec, exc=0.000016 sec, total=0.000016 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.004171 sec of elapsed time
     1: waited for 'ges resource directory to be unfrozen'
        =0x0, =0x0, =0x0
        wait_id=39 seq_num=40 snap_id=1
        wait times: snap=0.000001 sec, exc=0.000001 sec, total=0.000001 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.000159 sec of elapsed time
     2: waited for 'PGA memory operation'
        =0x10000, =0x1, =0x0
        wait_id=38 seq_num=39 snap_id=1
        wait times: snap=0.000008 sec, exc=0.000008 sec, total=0.000008 sec
        wait times: max=infinite
        wait counts: calls=0 os=0
        occurred after 0.001252 sec of elapsed time
...
    ----------------------------------------
## search 2169daef0
                ----------------------------------------
                SO: 0x25a8dd8f0, type: 3, owner: 0x200c75980, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
                 proc=0x3327efe60, name=call, file=ksu.h LINE:15733, pg=0, conuid=0
                (call) sess: cur 353bc5da8, rec 353bc5da8, usr 343c899d0; flg:40 fl2:1; depth:6
                svpt(xcb:(nil) sptn:0x1d uba: 0x00000000.0000.00 uba: 0x00000000.0000.00)
                  ----------------------------------------
                  SO: 0x2169daef0, type: 3, owner: 0x25a8dd8f0, flag: INIT/-/-/-/0x00 if: 0x3 c: 0x3
                   proc=0x3327efe60, name=call, file=ksu.h LINE:15733, pg=0, conuid=0
                  (call) sess: cur 353bc5da8, rec 353bc5da8, usr 343c899d0; flg:0 fl2:1; depth:7
                  svpt(xcb:(nil) sptn:0x1f uba: 0x00000000.0000.00 uba: 0x00000000.0000.00)
                  ----------------------------------------
                  SO: 0x21f8b2c98, type: 102, owner: 0x25a8dd8f0, flag: INIT/-/-/-/0x00 if: 0x1 c: 0x1
                   proc=0x3327efe60, name=row cache enqueues, file=kqr.h LINE:2319, pg=0, conuid=0
                  row cache enqueue: count=1 session=0x343c899d0 object=0x21f9e9d28, mode=S
                  savepoint=0x1d
                  type=MULTI-INSTANCE instance locked=T handle=0x367b752c0
                  row cache parent object: addr=0x21f9e9d28 cid=8(dc_objects) conid=0 conuid=0
                  hash=6e50015a typ=61 transaction=(nil) flags=00008000 inc=1, pdbinc=1
                  objectno=0 ownerid=0 nsp=1
                  name=OBJ$
                  own=0x21f9e9df8[0x21f8b2d58,0x21f8b2d58] wat=0x21f9e9e08[0x21f9e9e08,0x21f9e9e08] mode=S req=N
                  status=-/-/-/-/-/-/-/-/LOADING
                  instance lock=QI 6e50015a 1f898cb3
                  set=0, complete=FALSE

TIP:
KTSJ ==> Kernel Transaction Space Job,   应该是空间预分配相关的进程,在12.1.0.2时引入,有_enable_space_preallocation 控制。

接下来在MOS中查找,不难确认符合现在 2438982.1 中提到的已知BUG。

12.2.0.1 Active DataGuard AKA ADG ORA-4024 self-deadlock detected while trying to mutex pin cursor ( Doc ID 2438982.1 )
Bug 27716177 – ADG: ORA-04021:ORA-04024:ROW CACHE ENQUEUE AGAINST DC_OBJECTS:OBJ$ <==closed as following duplicate unpublished bug
BUG 28423598 – GOLDENGATE AUTH CAUSES ACTIVE DG TO BE UNUSABLE UNTIL BOUNCE

貌似是因为OGG的认证问题,导致ADG hang 一致在等待dc_objects(OBJ$)的row cache enq。 临时解决也是重启后恢复。

目前已提供one-off Patch 28423598 ,下载对应平台的补丁安装,目前已合成到19.1 (19C)的主版本中。

打赏

, ,

对不起,这篇文章暂时关闭评论。