下午一个朋友的问题，简单记录，版本11.2.0.4 2-nodes RAC, 现象是service_name 参数出现了一些SYS.KUPC$ 的service, 监听上同样有，且停节点1 ，service会漂到节点2， 重启双实例后同样存在， 手动修改service_name可以临时解决，但是重启实例还是会存在，虽然是新增service, 监听上看着乱，其它没什么影响， 这类服务常见于datapump 自动增加的，用完后会自己删除， db alert LOG中同样会出现ALTER SYSTEM SET service_names=’SYS$SYS.*…’，前几日做datapump导数时有中止过，所以service 就留了下来， 下面记录如何清理这些service.
DATAPUMP doesn’t change existing service name and no impact to existing services.
DATAPUMP just adds new services for its own queue operation.
SQL> select name from gv$services; NAME ---------------------------------------------------------------- SYS.KUPC$S_1_20181220143414 SYS.KUPC$C_1_20181220143414 telemtXDB telemt SYS$BACKGROUND SYS$USERS telemtXDB telemt SYS$BACKGROUND SYS$USERS 10 rows selected.
Note:
The view (G)V$SERVICES shows all services ever created for this instance, even those that have been stopped or deleted, while the view V$ACTIVE_SERVICES shows only the active services.
SQL> SELECT service_id, inst_id,name,network_name, creation_date
2 FROM gv$active_services
3 WHERE name like '%KUPC%';
SERVICE_ID INST_ID NAME NETWORK_NAME CREATION_DAT
---------- ---------- --------------------------- ------------------------------- -----------------
5 1 SYS.KUPC$C_1_20181220143414 SYS$SYS.KUPC$C_1_20181220143414.TELEMT 20-DEC-18
SQL> exec dbms_service.stop_service('SYS.KUPC$C_1_20181220143414');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.delete_service('SYS.KUPC$C_1_20181220143414');
BEGIN dbms_service.delete_service('SYS.KUPC$C_1_20181220143414'); END;
*
ERROR at line 1:
ORA-44319: Cannot delete AQ service
ORA-06512: at "SYS.DBMS_SERVICE", line 482
ORA-06512: at "SYS.DBMS_SERVICE", line 350
ORA-06512: at line 1
SQL> ho oerr ora 44319
44319, 0000, "Cannot delete AQ service"
// *Document : Yes
// *Cause : Specified AQ service belonged to a queue.
// *Action : Delete the queue.
SQL> EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'SYS.KUPC$C_1_20181220143414');
BEGIN DBMS_AQADM.DROP_QUEUE( queue_name => 'SYS.KUPC$C_1_20181220143414'); END;
*
ERROR at line 1:
ORA-24011: cannot drop QUEUE, SYS.KUPC$C_1_20181220143414 should be stopped first
ORA-06512: at "SYS.DBMS_AQADM", line 201
ORA-06512: at line 1
SQL> EXECUTE DBMS_AQADM.stop_QUEUE( queue_name => 'SYS.KUPC$C_1_20181220143414');
PL/SQL procedure successfully completed.
SQL> SQL> EXECUTE DBMS_AQADM.DROP_QUEUE( queue_name => 'SYS.KUPC$C_1_20181220143414');
PL/SQL procedure successfully completed.
SQL> select name from gv$services;
NAME
----------------------------------------------------------------
telemtXDB
telemt
SYS$BACKGROUND
SYS$USERS
telemtXDB
telemt
SYS$BACKGROUND
SYS$USERS
— enjoy —