在Oracle的10.2版本中，Oracle不僅會將資料庫的例項註冊到監聽器，與此同時還會註冊生成一個名為_XPT（以資料庫的SID開頭，以XPT結尾，中間是下劃線）的服務，這個服務是用於Data Guard環境，對正常資料庫連線沒有影響。鑑於_XPT服務的作用，我們完全有理由取消這個註冊。這裡給出禁止_XPT服務註冊到監聽器的方法。
我們可以透過將隱含引數“__dg_broker_service_names”內容置空的方法達到這個目的。

1.“__dg_broker_service_names”隱含引數的含義
可以透過x$ksppi和x$ksppcv獲取隱含引數的當前值和說明資訊。查詢結果如下。
sys@ora10g> select a.ksppinm param_name,b.ksppstvl param_value,a.ksppdesc param_description from x$ksppi a,x$ksppcv b where a.indx = b.indx and a.ksppinm = '__dg_broker_service_names';

PARAM_NAME                 PARAM_VALUE   PARAM_DESCRIPTION
-------------------------- ------------- ----------------------------
__dg_broker_service_names ora10g_XPT    service names for broker use

2.監聽器狀態資訊
ora10g@secdb /home/oracle$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-AUG-2011 21:19:58

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=secdb)(port=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-AUG-2011 21:19:12
Uptime                    0 days 0 hr. 0 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/ora10gR2/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File         /oracle/ora10gR2/product/10.2.0/db_2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb.localdomain)(PORT=1521)))
Services Summary...
Service "ora10g" has 1 instance(s).
Instance "ora10g", status READY, has 1 handler(s) for this service...
Service "ora10g_XPT" has 1 instance(s).
Instance "ora10g", status READY, has 1 handler(s) for this service...
The command completed successfully

其中“ora10g_XPT”便是我們所提到的_XPT服務。我們的目標就是去除這個註冊資訊。

3.將“__dg_broker_service_names”隱含引數置空
sys@ora10g> alter system set "__dg_broker_service_names" = '';

System altered.

注意，此隱含引數為動態引數，可以直接進行調整。

4.重啟監聽器後檢視監聽狀態
1）停止監聽器
LSNRCTL> stop
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=secdb)(port=1521)))
The command completed successfully

2）啟動監聽器
LSNRCTL> start
Starting /oracle/ora10gR2/product/10.2.0/db_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/ora10gR2/product/10.2.0/db_2/network/admin/listener.ora
Log messages written to /oracle/ora10gR2/product/10.2.0/db_2/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=secdb)(port=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-AUG-2011 21:22:40
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/ora10gR2/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File         /oracle/ora10gR2/product/10.2.0/db_2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

3）等待資料庫例項註冊到監聽後檢視監聽器狀態
ora10g@secdb /home/oracle$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-AUG-2011 21:23:00

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(address=(protocol=tcp)(host=secdb)(port=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                22-AUG-2011 21:22:40
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/ora10gR2/product/10.2.0/db_2/network/admin/listener.ora
Listener Log File         /oracle/ora10gR2/product/10.2.0/db_2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb.localdomain)(PORT=1521)))
Services Summary...
Service "ora10g" has 1 instance(s).
Instance "ora10g", status READY, has 1 handler(s) for this service...
The command completed successfully

此時ora10g_XPT服務已經不在監聽中註冊。任務完成。

5.小結
在非Data Guard環境的Oracle 10.2版本及以上環境，我們都可以使用此方法實現禁止_XPT服務註冊到監聽器。消除一切疑惑的內容，將資料庫的執行狀態掌握在自己的手中。

Good luck.

secooler
11.08.22

-- The End --

【LISTENER】使用Oracle隱含引數禁止“_XPT”服務註冊到監聽器

相關文章