oracle呼叫外部程式時報：ora-28575以及ora-28546錯誤

dcswinner發表於2011-12-28

今天發現一個有關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

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

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魔法堂：呼叫外部程式
2021-02-10
Java
解決IE上登陸oracle OEM時報：“證書錯誤，導航已阻止”的錯誤
2014-02-25
Oracle
oracle外部表詳解以及使用
2013-12-20
Oracle
PD連線oracle時報錯
2015-11-26
Oracle
Oracle RAC 錯誤記錄以及處理方法
2012-06-20
Oracle
匯入sql時報日期型別錯誤
2020-04-05
SQL型別
AngularJS中監視Scope變數以及外部呼叫Scope方法
2016-01-23
AngularJS變數
靜默安裝oracle時報錯
2019-05-14
Oracle
[Oracle]startup mount時報ORA-12514錯誤的解決方法
2015-12-23
Oracle
安裝FreeIPA以及應用時報錯彙總
2018-12-12
EF：開啟Oracle連線時報錯
2015-12-27
Oracle
[Golang]呼叫外部shell程式處理檔案
2021-09-09
Golang
Python 指令碼中呼叫 Java 程式時 Classpath 錯誤
2024-04-26
Python指令碼Java
oracle外部表建立以及收集統計資訊以及臨時表
2011-11-07
Oracle
一個JAVA程式呼叫外部程式的問題！（急）
2004-04-11
Java
ORACLE 錯誤
2019-11-28
Oracle
查詢外部表出現KUP-4040錯誤
2012-11-24
【OCR】Oracle CRS 10.2.0.1版本Bug導致映象OCR時報PROT-22錯誤
2010-11-02
Oracle
oracle 雙監聽器啟動時報tns-01106錯誤
2009-10-26
Oracle
使用 deploy 部署專案時報 Serialization of 'Closure' is not allowed 錯誤
2020-05-15
資料泵匯出時報錯ORA-1422錯誤
2008-08-12
latex 錯誤以及解決方案
2020-11-24
QTP呼叫外部應用程式的4種方法
2010-03-18
QT
轉oracle資料泵匯出時報錯
2016-05-19
Oracle
【ERROR】Oracle列印錯誤程式碼解釋
2021-07-29
ErrorOracle
phpcms生成兩級選單的程式碼以及後臺刪除內容時報錯
2016-10-21
PHP
ORACLE 異常錯誤錯誤號大全
2017-02-28
Oracle
外部函式的呼叫
2018-11-06
函式
C#呼叫外部DLL
2009-04-15
C#
docker中使用systemctl命令時報Too many open files錯誤
2021-03-03
Docker
Oracle 錯誤收集
2014-11-22
Oracle
ORACLE錯誤大全
2015-05-29
Oracle
C語言程式碼區錯誤以及編譯過程
2021-06-09
C語言編譯
呼叫https介面時報錯：PKIX path building failed 的問題
2018-04-09
HTTPUIAI
python 呼叫外部程式訪止一直阻塞
2013-11-27
Python
變數命名以及常見錯誤
2019-01-12
變數
mysql 主從錯誤以及監控
2013-08-12
MySql
編譯oracle時報錯找不到loraolap10
2012-08-20
編譯Oracle

oracle呼叫外部程式時報：ora-28575以及ora-28546錯誤

相關文章