重庆思庄oracle技术分享-清理SYS$SYS.KUPC监听服务

今天巡检数据库，发现SCAN监听下面多了很多KUPC开头的监听，如下：

Service “-MGMTDBXDB” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “86b638145ff27a6fe053f706e80a81f1” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126005244_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126011348_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126011354_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126011840_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126012214_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$C_1_20201126034030_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126005244_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126011348_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126011354_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126011840_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126012214_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “SYS$SYS.KUPC$S_1_20201126034030_0.sztechDB” has 1 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Service “_mgmtdb” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “b4ed216bdbc59217e0532864a8c07b67” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “gimr_dscrep_10” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “sztechdb” has 2 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Instance “sztechdb2”, status READY, has 1 handler(s) for this service…
Service “sztechdbXDB” has 2 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Instance “sztechdb2”, status READY, has 1 handler(s) for this service…
The command completed successfully

检查一下服务：
select name,CREATION_DATE ,SESSION_STATE_CONSISTENCY from v$services where SESSION_STATE_CONSISTENCY=‘DYNAMIC’;

NAME CREATION_DAT SESSION_STATE_CONSIS

---

SYS.KUPCKaTeX parse error: Double subscript at position 4: C_1_̲20201126012214_…S_1_20201126011348_0 26-NOV-20 DYNAMIC
SYS.KUPCKaTeX parse error: Double subscript at position 4: S_1_̲20201126034030_…S_1_20201126011840_0 26-NOV-20 DYNAMIC
SYS.KUPCKaTeX parse error: Double subscript at position 4: S_1_̲20201126011354_…C_1_20201126011840_0 26-NOV-20 DYNAMIC
SYS.KUPCKaTeX parse error: Double subscript at position 4: C_1_̲20201126034030_…S_1_20201126005244_0 26-NOV-20 DYNAMIC
SYS.KUPCKaTeX parse error: Double subscript at position 4: C_1_̲20201126011348_…C_1_20201126011354_0 26-NOV-20 DYNAMIC
SYS.KUPC$S_1_20201126012214_0 26-NOV-20 DYNAMIC

NAME CREATION_DAT SESSION_STATE_CONSIS

---

SYS.KUPC$C_1_20201126005244_0 26-NOV-20 DYNAMIC

这么多，这个服务名一看，应该是数据泵被杀掉后留下的服务

尝试进行删除：

begin
DBMS_SERVICE.DELETE_SERVICE(service_name=>‘SYS.KUPC$C_1_20201126012214_0’);
end;
/

begin
*
ERROR at line 1:
ORA-44319: Cannot delete AQ service
ORA-06512: at “SYS.DBMS_SERVICE_ERR”, line 57
ORA-06512: at “SYS.DBMS_SERVICE”, line 461
ORA-06512: at line 2
失败了，看来不能直接删除，是队列服务。

使用队列删除：

EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => ‘SYS.KUPC$C_1_20201126012214_0’);
PL/SQL procedure successfully completed.

成功了，

照样把其他一起删除。

SELECT ‘EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>’‘’ || name || ‘’‘);’ as rmservice
FROM gv$active_services WHERE name like ‘%KUPC%’;

RMSERVICE

EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPCKaTeX parse error: Double subscript at position 4: S_1_̲20201126011348_…S_1_20201126034030_0’);
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPCKaTeX parse error: Double subscript at position 4: S_1_̲20201126011840_…S_1_20201126011354_0’);
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPCKaTeX parse error: Double subscript at position 4: C_1_̲20201126011840_…C_1_20201126034030_0’);
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPCKaTeX parse error: Double subscript at position 4: S_1_̲20201126005244_…C_1_20201126011348_0’);
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPCKaTeX parse error: Double subscript at position 4: C_1_̲20201126011354_…S_1_20201126012214_0’);
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>‘SYS.KUPC$C_1_20201126005244_0’);

11 rows selected.

再去看服务：
SQL> select name from gv$services;

NAME

SYS$BACKGROUNDSYS$USERS
sztechdbXDB
sztechdb
SYS$BACKGROUNDSYS$USERS
sztechdbXDB
sztechdb

8 rows selected.

已经没有那些烦人的服务了

[grid@hisdb02 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-MAY-2022 15:35:03

Copyright © 1991, 2022, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER

Alias LISTENER_SCAN1
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 24-APR-2022 21:46:02
Uptime 35 days 17 hr. 49 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.9.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/hisdb02/listener_scan1/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.2)(PORT=1521)))
Services Summary…
Service “-MGMTDBXDB” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “86b638145ff27a6fe053f706e80a81f1” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “_mgmtdb” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “b4ed216bdbc59217e0532864a8c07b67” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “gimr_dscrep_10” has 1 instance(s).
Instance “-MGMTDB”, status READY, has 1 handler(s) for this service…
Service “sztechdb” has 2 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Instance “sztechdb2”, status READY, has 1 handler(s) for this service…
Service “sztechdbXDB” has 2 instance(s).
Instance “sztechdb1”, status READY, has 1 handler(s) for this service…
Instance “sztechdb2”, status READY, has 1 handler(s) for this service…
The command completed successfully
[grid@hisdb02 ~]$

至此，所有的kupc监听服务全部删除。