[20211012]ORA-12850.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。