ANBOB™

下午一个朋友的问题，简单记录，版本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 —

打赏

微信扫一扫，打赏作者吧～