今天发现一个有关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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12129601/viewspace-714101/