ITPub博客

首页 > 数据库 > Oracle > [20211012]ORA-12850.txt

[20211012]ORA-12850.txt

原创 Oracle 作者:lfree 时间:2021-10-12 10:46:01 0 删除 编辑

[20211012]ORA-12850.txt

--//放假前的事情一直没时间关注,新上线生产系统,一直没时间看,执行ash_wait_chains.sql遇到ORA-12850问题.

1.环境:
SYS@127.0.0.1:17101/PPPPP> @ ver
SYS@127.0.0.1:17101/PPPPP> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate

-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
        AND sample_time BETWEEN trunc(sysdate) AND sysdate
                                                   *
ERROR at line 22:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

--//使用百度检索,ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated
--//真的应该把百度检索XXX掉,什么垃圾,浪费大量的时间.

--//围绕ora-12850的bug N多,因为要打开并行,应该与这样有关,但是都不是很准确.
--//我想既然是tanelpoder工具,应该有人先比我遇到这个问题.找到作者的链接:



SYS@127.0.0.1:17101/PPPPP> ALTER SESSION SET "_with_subquery"=INLINE;
Session altered.

SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate

-- Display ASH Wait Chain Signatures script v0.2 BETA by Tanel Poder ( http://blog.tanelpoder.com )
        AND sample_time BETWEEN trunc(sysdate) AND sysdate
                                                   *
ERROR at line 22:
ORA-12850: Could not allocate slaves on all specified instances: 2 needed, 0 allocated

--//不行!!理论讲应该可以,不知道为什么,不过我看到作者结尾的内容:

In my case I didn't end up using the ALTER SESSION command as I don't want my troubleshooting scripts to alter session
settings when possible. I first tried to add the opt_param hint, but since I have multiple WITH subquery blocks, just
adding this parameter into one of them would not work. I ended up adding the INLINE hint into all WITH subuquery blocks
to work around this bug.

I have uploaded the latest v0.6 versions of ash_wait_chains.sql and dash_wait_chains.sql to my Oracle troubleshooting
scripts GitHub repo.

在我的情况下,我最终没有使用更改会话命令,因为我不希望我的故障排除脚本尽可能更改会话设置。我首先尝试添加opt_param提示,
但由于我有多个子查询块,只是将这个参数添加到其中一个中是不起作用的。最后,我将内联提示添加到所有的子查询块中,以解决这个
错误。

--//作者更新ash_wait_chains.sql and dash_wait_chains.sql的版本.我下载执行一切Ok.
--//我仅仅修改一行
COL wait_chain FOR A300 WORD_WRAP

COL wait_chain FOR A190 WORD_WRAP
--//大师的显示器显示宽度真大....

SYS@127.0.0.1:17101/PPPPP> @ tpt/ash/ash_wait_chains username||':'||program2||':'||event2 1=1  trunc(sysdate) sysdate

-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS #Blkrs WAIT_CHAIN                                                                                    FIRST_SEEN          LAST_SEEN
------ ---------- ---------- ------ --------------------------------------------------------------------------------------------- ------------------- -------------------
  20%        6296         .2      4 -> IMCJK:(JDBC Thin Client) :ON CPU                                                           2021-10-12 00:00:30 2021-10-12 10:20:36
  15%        4570         .1      1 -> SYS:(PSPn) :ON CPU                                                                         2021-10-12 00:42:55 2021-10-12 09:47:46
  11%        3491         .1    213 -> XXXYYY_HIS:(XXXYYY.EXE) :ON CPU                                                            2021-10-12 00:00:10 2021-10-12 10:20:34
  11%        3467         .1    199 -> XXXYYY_HIS:(XXXYYY.exe) :ON CPU                                                            2021-10-12 00:00:01 2021-10-12 10:20:35
   5%        1472          0      1 -> SYS:(DBRM) :ON CPU                                                                         2021-10-12 00:00:38 2021-10-12 10:20:29
   4%        1355          0      1 -> SYS:(PMAN) :ON CPU                                                                         2021-10-12 00:00:23 2021-10-12 10:20:05
   3%         816          0      1 -> SYS:(CKPT) :control file sequential read                                                   2021-10-12 00:00:04 2021-10-12 10:19:34
   3%         770          0    150 -> XXXYYY_HIS:(wnwp.exe) :ON CPU                                                              2021-10-12 00:00:15 2021-10-12 10:20:29
   2%         653          0      6 -> XXXYYY_PPI:(wnwp.exe) :ON CPU                                                              2021-10-12 00:02:04 2021-10-12 10:18:32
   2%         637          0      1 -> SYS:(DIAn) :ON CPU                                                                         2021-10-12 00:00:02 2021-10-12 10:17:41
   1%         453          0     38 -> XXXYYY:(bsupd.exe) :SQL*Net more data to client                                            2021-10-12 00:12:36 2021-10-12 10:20:33
   1%         448          0      1 -> SYS:(RMSn) :control file sequential read                                                   2021-10-12 00:00:12 2021-10-12 10:20:19
   1%         401          0      6 -> LBJH:(JDBC Thin Client) :ON CPU                                                            2021-10-12 00:00:40 2021-10-12 10:16:09
   1%         367          0     10 -> SYS:(Wnnn) :latch free  -> [idle blocker 1,5741,36606 (oracle@oda1 (SMCO))]                2021-10-12 00:01:00 2021-10-12 10:20:15
   1%         305          0     12 -> SYS:(Wnnn) :latch free  -> [idle blocker 2,5741,17785 (oracle@oda2 (SMCO))]                2021-10-12 00:00:58 2021-10-12 10:17:48
   1%         272          0      2 -> SYS:(LGnn) :log file parallel write                                                        2021-10-12 00:05:07 2021-10-12 10:19:47
   1%         242          0      1 -> SYS:(GENn) :ON CPU                                                                         2021-10-12 00:07:24 2021-10-12 09:57:44
   1%         217          0     87 -> XXXYYY:(XXXYYY.exe) :ON CPU                                                                2021-10-12 00:13:03 2021-10-12 10:20:37
   1%         208          0      1 -> SYS:(MMON) :ON CPU                                                                         2021-10-12 00:05:33 2021-10-12 10:17:46
   1%         203          0      1 -> SYS:(CKPT) :Disk file Mirror Read                                                          2021-10-12 00:00:40 2021-10-12 10:15:46
   1%         174          0     88 -> XXXYYY:(XXXYYY.EXE) :ON CPU                                                                2021-10-12 00:00:54 2021-10-12 10:18:39
   1%         158          0      1 -> SYS:(CKPT) :ON CPU                                                                         2021-10-12 00:04:34 2021-10-12 10:20:28
   1%         157          0      1 -> SYS:(PMON) :ON CPU                                                                         2021-10-12 00:11:44 2021-10-12 10:20:20
   0%         144          0     68 -> SYS:(MZnn) :ON CPU                                                                         2021-10-12 00:00:02 2021-10-12 10:16:06
   0%         138          0      1 -> SYS:(LMHB) :ON CPU                                                                         2021-10-12 00:00:55 2021-10-12 10:16:26
   0%         129          0      1 -> SYS:(LMON) :ON CPU                                                                         2021-10-12 00:11:41 2021-10-12 10:16:07
   0%         110          0     63 -> XXXYYY_HIS:(autoservice.exe) :ON CPU                                                       2021-10-12 00:02:00 2021-10-12 10:19:01
   0%         100          0     45 -> XXXYYY_EMR:(XXXYYY.exe) :ON CPU                                                            2021-10-12 00:07:23 2021-10-12 10:19:51
   0%          92          0     31 -> XXXYYY:(XXXYYY.exe) :log file sync  -> [idle blocker 1,4230,46563 (oracle@oda1 (LGWR))]    2021-10-12 00:05:30 2021-10-12 10:19:58
   0%          80          0      1 -> SYS:(WnnD) :latch free  -> [idle blocker 2,5741,17785 (oracle@oda2 (SMCO))]                2021-10-12 00:07:20 2021-10-12 10:19:22
30 rows selected.
--//username=IMCJK .

SYS@127.0.0.1:17101/PPPPP> @ ashtop sql_id,event,machine username='IMCJK' sysdate-1 sysdate
    Total
  Seconds     AAS %This   SQL_ID        EVENT                        MACHINE FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ---------------------------- ------- ------------------- -------------------
     1959      .0   13% | c3jafyjuwt13b                              H3C-APM 2021-10-11 10:25:29 2021-10-12 10:20:53
      679      .0    4% | f454ryjfx6syf                              H3C-APM 2021-10-11 10:25:56 2021-10-12 10:21:16
      557      .0    4% | 8fm0xfacp0b0g                              H3C-APM 2021-10-11 10:25:37 2021-10-12 10:20:59
      528      .0    3% | 1g11ms1r6bnuj                              H3C-APM 2021-10-11 10:25:35 2021-10-12 10:20:56
      526      .0    3% | 6sbq34x7ckff7                              H3C-APM 2021-10-11 10:25:36 2021-10-12 10:20:58
      524      .0    3% | f6d0fpgm1w2sw                              H3C-APM 2021-10-11 10:25:42 2021-10-12 10:21:04
      521      .0    3% | czkw1ncpthxy4                              H3C-APM 2021-10-11 10:25:34 2021-10-12 10:15:35
      516      .0    3% | 2v09t9vyy6zk6                              H3C-APM 2021-10-11 10:25:50 2021-10-12 10:20:52
      514      .0    3% | 0tha0zcyf9maq                              H3C-APM 2021-10-11 10:25:38 2021-10-12 10:21:00
      513      .0    3% | 8ss7js42xzp05                              H3C-APM 2021-10-11 10:25:41 2021-10-12 10:21:03
      510      .0    3% | b282h3vx1nh1j                              H3C-APM 2021-10-11 10:25:40 2021-10-12 10:21:02
      509      .0    3% | 7m6szm4t720j0                              H3C-APM 2021-10-11 10:25:43 2021-10-12 10:21:05
      509      .0    3% | 9w8scutvwbjaw                              H3C-APM 2021-10-11 10:25:47 2021-10-12 10:21:09
      508      .0    3% | 5ub6g7qwaf35x                              H3C-APM 2021-10-11 10:25:33 2021-10-12 10:20:54
      505      .0    3% | 2sq2bmkwuz6at                              H3C-APM 2021-10-11 10:25:44 2021-10-12 10:21:06
      504      .0    3% | 30a5bma58q1w7                              H3C-APM 2021-10-11 10:25:53 2021-10-12 10:21:15
      501      .0    3% | az4ju0qgum193                              H3C-APM 2021-10-11 10:25:39 2021-10-12 10:21:01
      500      .0    3% | fpamfm2pkznu1                              H3C-APM 2021-10-11 10:25:49 2021-10-12 10:20:51
      499      .0    3% | d946h5sr5gt69                              H3C-APM 2021-10-11 10:25:46 2021-10-12 10:21:08
      496      .0    3% | 5mwanf0c830mj                              H3C-APM 2021-10-11 10:25:45 2021-10-12 10:21:07
      495      .0    3% | 0zrwxj39q7u3w                              H3C-APM 2021-10-11 10:25:51 2021-10-12 10:21:13
      494      .0    3% | ck5qb9zs2n34g                              H3C-APM 2021-10-11 10:25:48 2021-10-12 10:21:10
      493      .0    3% | 18q3m92yk5zg5                              H3C-APM 2021-10-11 10:25:52 2021-10-12 10:21:14
      186      .0    1% | 5t9zzqmqdyxbg                              H3C-APM 2021-10-11 10:40:34 2021-10-12 10:20:55
      177      .0    1% | 27m1sf1nknfz2                              H3C-APM 2021-10-11 10:25:54 2021-10-12 10:20:56
      177      .0    1% | cyfdvynj0mtc8                              H3C-APM 2021-10-11 10:25:58 2021-10-12 10:15:38
      163      .0    1% | 9yfzqfdw2yhs4                              H3C-APM 2021-10-11 10:45:48 2021-10-12 08:45:28
       84      .0    1% | 1j262t18zrpxt                              H3C-APM 2021-10-11 11:06:18 2021-10-12 09:45:57
       74      .0    0% | ahatnp9sk1b5s                              H3C-APM 2021-10-11 10:56:10 2021-10-12 10:21:12
       49      .0    0% | 6uz4za48wf6j7                              H3C-APM 2021-10-11 10:36:04 2021-10-12 09:31:04
       47      .0    0% | 68k7ckt95ttcf                              H3C-APM 2021-10-11 10:35:58 2021-10-12 10:10:57
       45      .0    0% | 7ksrtc8rzpawc                              H3C-APM 2021-10-11 10:35:56 2021-10-12 09:50:55
       39      .0    0% | 22356bkgsdcnh                              H3C-APM 2021-10-11 11:35:31 2021-10-12 09:15:54
       30      .0    0% | 0gb620m9hwp4s                              H3C-APM 2021-10-11 10:26:13 2021-10-12 10:16:07
       29      .0    0% | czd2z83tzauux                              H3C-APM 2021-10-11 10:30:36 2021-10-12 10:15:57
       29      .0    0% | d78ubma8q6xj2                              H3C-APM 2021-10-11 11:06:15 2021-10-12 10:16:14
       29      .0    0% | dq6bzb5xdk3h7                              H3C-APM 2021-10-11 10:30:28 2021-10-12 09:35:29
       26      .0    0% | 3mubmnaquyqcj                              H3C-APM 2021-10-11 10:30:55 2021-10-12 08:56:16
       25      .0    0% | gzg2phr7fjs2j ASM file metadata operation  H3C-APM 2021-10-11 11:20:31 2021-10-12 07:05:53
       23      .0    0% | 99v4t515j5j56                              H3C-APM 2021-10-11 10:45:57 2021-10-12 10:16:17
       23      .0    0% |                                            H3C-APM 2021-10-11 12:01:07 2021-10-12 09:56:07
       22      .0    0% | 9h4w5m54fq46f                              H3C-APM 2021-10-11 11:06:14 2021-10-12 10:16:13
       22      .0    0% | c2ypbq9ac2qw5                              H3C-APM 2021-10-11 11:00:44 2021-10-12 09:31:03
       19      .0    0% | d0h6nu0uwfjtx                              H3C-APM 2021-10-11 11:06:02 2021-10-12 10:05:42
       18      .0    0% | 63d8azd63u566                              H3C-APM 2021-10-11 12:31:06 2021-10-12 09:46:08
       13      .0    0% | gwt7tu3383grt                              H3C-APM 2021-10-11 14:36:08 2021-10-12 09:25:50
       12      .0    0% | 5r14h528vkacs                              H3C-APM 2021-10-11 12:30:31 2021-10-12 09:35:54
       11      .0    0% | 8sxz1p1238fyh                              H3C-APM 2021-10-11 10:25:57 2021-10-12 09:55:36
       10      .0    0% | 21t4z1r0k4cyd control file sequential read H3C-APM 2021-10-11 10:40:49 2021-10-12 08:15:28
       10      .0    0% | 7mcwvm1xxb7wf                              H3C-APM 2021-10-11 17:00:56 2021-10-12 09:45:37
50 rows selected.

--//华为监控的东西.拜托华为的研发写个监控不要乱写,不然真丢中国人的脸.看了其中一些监控sql语句,大部分都是忽悠人的东西,对于问题的分析解决毫无实际价值.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2795478/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3085
  • 访问量
    6808090