oracle呼叫外部程式時報：ora-28575以及ora-28546錯誤
今天發現一個有關oracle呼叫外部程式時報：ora-28575以及ora-28546錯誤，現將問題的現象，原因和處理辦法總結如下：
SQL> create or replace library GENMAILNUM
as '/home/oracle/product/10.2.0/lib/libmailnum.so'; 2
/
Library created.
SQL> CREATE OR REPLACE FUNCTION genmailnum_func (
prefix VARCHAR2,
suffix VARCHAR2,
seq VARCHAR2,
mailnum out varchar2)
RETURN PLS_INTEGER
AS LANGUAGE C
LIBRARY genmailnum
NAME "generate_mail_num";
/
2 3 4 5 6 7 8 9 10 11
Function created.
SQL> set serveroutput on
SQL> declare
result PLS_INTEGER;
prefix varchar2(20) :='LN';
suffix varchar2(20) :='CN';
seq varchar2(20) :='12345678';
mailnum varchar2(20);
begin
-- Call the function
result := genmailnum_func(prefix,
suffix ,
seq ,
mailnum );
end; 2 3 4 5 6 7 8 9 10 11 12 13 14
15 /
ORA-28575: unable to open RPC connection to external procedure agent
而另外一臺測試環境上相同的程式碼，相同的表結構，呼叫外部程式是可以的。根據此錯誤，檢視監聽檔案的配置情況：
[oracle@xxxx-test ~]$cat /home/oracle/product/10.2.0/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
看來是沒有配置靜態監聽，再來檢視監聽器狀態情況：
[oracle@xxxx-test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-DEC-2011 13:25:21
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.129)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 29-DEC-2011 13:23:09
Uptime 0 days 0 hr. 2 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.129)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "xxxx" has 1 instance(s).
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxxXDB" has 1 instance(s).
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxx_XPT" has 1 instance(s).
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "cognos" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
Service "cognosXDB" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
Service "cognos_XPT" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
The command completed successfully
通過這個可以看出本資料庫伺服器的監聽都是通過動態監聽的。也想嘗試新增一個靜態的監聽，來看看是不是究竟就是靜態監聽的問題，配置靜態監聽需要重啟監聽器。但是這是生產伺服器，系統非常的忙，不能重啟監聽器，否則會給生產帶來一定的影響，因此想看看究竟能不能採用動態監聽來呼叫外部程式。
修改tnsnames .ora檔案：
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID =PLSExtProc)
(PRESENTATION = RO)
)
)
將SID =PLSExtProc改為SID =xxxx
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID =xxxx)
(PRESENTATION = RO)
)
)
再來呼叫外部程式：
SQL> declare
result PLS_INTEGER;
prefix varchar2(20) :='LN';
suffix varchar2(20) :='CN';
seq varchar2(20) :='12345678';
mailnum varchar2(20);
begin
-- Call the function
result := genmailnum_func(prefix,
suffix ,
seq ,
mailnum );
end;
2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 /
declare
*
ERROR at line 1:
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-06512: at "TMSDEV.GENMAILNUM_FUNC", line 1
ORA-06512: at line 10
此錯誤一般都是異構資料庫透明閘道器相關的錯誤，或者是dblink方面的問題，和這個沒什麼關係，覺得很奇怪。
仔細查過oracle有關網路配置的官方文件：
Static Service Registration (SID_LIST) Section
You can use the SID_LIST section of the listener.ora to statically configure service information for the listener.
The SID_LIST section is required for Oracle8 release 8.0 or Oracle7 database services, as well as external procedure calls and Heterogeneous Services, and some management tools, including Oracle Enterprise Manager.
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=global_database_name)
(SID_NAME=sid)
(ORACLE_HOME=oracle_home))
(SID_DESC=...))
For later database releases, the listener uses the dynamic service information about the database and instance it has received through service registration before using statically configured information in the listener.ora file. Therefore, the SID_LIST is not required, unless Oracle Enterprise Manager is used to monitor an Oracle9i or Oracle8 database.
發現oracle外部程式在10g中必須通過靜態監聽才能呼叫。在11g中卻沒有此要求。
我在另一臺10g測試環境上，重新配置了一個靜態監聽，問題解決：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = xxxx)
(ORACLE_HOME = /home/oracle/product/10.2.0)
(SID_NAME = xxxx)
)
)
重啟監聽器：
[oracle@xxxx-test admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-DEC-2011 17:25:54
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.129)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 29-DEC-2011 17:24:21
Uptime 0 days 0 hr. 1 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/product/10.2.0/network/admin/listener.ora
Listener Log File /home/oracle/product/10.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.129)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xxxx" has 2 instance(s).
Instance "xxxx", status UNKNOWN, has 1 handler(s) for this service...
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxxXDB" has 1 instance(s).
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "xxxx_XPT" has 1 instance(s).
Instance "xxxx", status READY, has 1 handler(s) for this service...
Service "cognos" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
Service "cognosXDB" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
Service "cognos_XPT" has 1 instance(s).
Instance "cognos", status READY, has 1 handler(s) for this service...
The command completed successfully
靜態監聽已經有了。
再次呼叫外部程式：
SQL> declare
result PLS_INTEGER;
prefix varchar2(20) :='LN';
suffix varchar2(20) :='CN';
seq varchar2(20) :='12345678';
mailnum varchar2(20);
begin
-- Call the function
result := genmailnum_func(prefix,
suffix ,
seq ,
mailnum );
end;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 /
PL/SQL procedure successfully completed.
問題解決。
另外在11g上也搭建了同樣的程式，監聽器沒有強制配置靜態監聽，呼叫外部程式也是可以的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-714101/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Java魔法堂：呼叫外部程式Java
- 解決IE上登陸oracle OEM時報：“證書錯誤，導航已阻止”的錯誤Oracle
- oracle外部表詳解以及使用Oracle
- PD連線oracle時報錯Oracle
- Oracle RAC 錯誤記錄以及處理方法Oracle
- 匯入sql時報日期型別錯誤SQL型別
- 靜默安裝oracle時報錯Oracle
- AngularJS中監視Scope變數以及外部呼叫Scope方法AngularJS變數
- [Oracle]startup mount時報ORA-12514錯誤的解決方法Oracle
- 安裝FreeIPA以及應用時報錯彙總
- EF：開啟Oracle連線時報錯Oracle
- [Golang]呼叫外部shell程式處理檔案Golang
- oracle外部表建立以及收集統計資訊以及臨時表Oracle
- Python 指令碼中呼叫 Java 程式時 Classpath 錯誤Python指令碼Java
- ORACLE 錯誤Oracle
- 一個JAVA程式呼叫外部程式的問題！（急）Java
- 查詢外部表出現KUP-4040錯誤
- 【OCR】Oracle CRS 10.2.0.1版本Bug導致映象OCR時報PROT-22錯誤Oracle
- oracle 雙監聽器啟動時報tns-01106錯誤Oracle
- 使用 deploy 部署專案時報 Serialization of 'Closure' is not allowed 錯誤
- 資料泵匯出時報錯ORA-1422錯誤
- latex 錯誤以及解決方案
- 轉oracle資料泵匯出時報錯Oracle
- 【ERROR】Oracle列印錯誤程式碼解釋ErrorOracle
- QTP呼叫外部應用程式的4種方法QT
- ORACLE 異常錯誤 錯誤號大全Oracle
- phpcms生成兩級選單的程式碼以及後臺刪除內容時報錯PHP
- Oracle 錯誤收集Oracle
- ORACLE錯誤大全Oracle
- 外部函式的呼叫函式
- C#呼叫外部DLLC#
- docker中使用systemctl命令時報Too many open files錯誤Docker
- C語言程式碼區錯誤以及編譯過程C語言編譯
- 呼叫https介面時報錯：PKIX path building failed 的問題HTTPUIAI
- 編譯oracle時報錯找不到loraolap10編譯Oracle
- python 呼叫外部程式訪止一直阻塞Python
- 變數命名以及常見錯誤變數
- mysql 主從錯誤以及監控MySql