清理烦人的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.KUPC$C_1_20201126012214_0       26-NOV-20    DYNAMIC
SYS.KUPC$S_1_20201126011348_0       26-NOV-20    DYNAMIC
SYS.KUPC$S_1_20201126034030_0       26-NOV-20    DYNAMIC
SYS.KUPC$S_1_20201126011840_0       26-NOV-20    DYNAMIC
SYS.KUPC$S_1_20201126011354_0       26-NOV-20    DYNAMIC
SYS.KUPC$C_1_20201126011840_0       26-NOV-20    DYNAMIC
SYS.KUPC$C_1_20201126034030_0       26-NOV-20    DYNAMIC
SYS.KUPC$S_1_20201126005244_0       26-NOV-20    DYNAMIC
SYS.KUPC$C_1_20201126011348_0       26-NOV-20    DYNAMIC
SYS.KUPC$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.KUPC$S_1_20201126011348_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$S_1_20201126034030_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$S_1_20201126011840_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$S_1_20201126011354_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$C_1_20201126011840_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$C_1_20201126034030_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$S_1_20201126005244_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$C_1_20201126011348_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$C_1_20201126011354_0');
EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name =>'SYS.KUPC$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$BACKGROUND
SYS$USERS
sztechdbXDB
sztechdb
SYS$BACKGROUND
SYS$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 (c) 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监听服务全部删除。