oracle9i升級11g後無法傳送郵件

在升級完資料庫之後不久就接到客戶投訴，說是使用者完成工作之後無法收到郵件，經過UNIXteam排除郵件伺服器之後，問題被轉移到DBAteam了，研究了半天終於解決了。
參考文章。
Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.
我們的解決方案參考了前面的文章，具體步驟如下：

Check xdb status

set echo on;
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25

-- Check status of XDB

select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
(select * from v$option)
-- Check for invalid objects

select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB');

spool off;

step1 install xdb

Master Note for Oracle XML Database (XDB) Install / Deinstall (文件 ID 1292089.1)

select tablespace_name from dba_tablespaces;

SQL>

select tablespace_name,SEGMENT_SPACE_MANAGEMENT  from dba_tablespaces;

 

TABLESPACE_NAME                SEGMEN

------------------------------ ------

SYSTEM                         MANUAL

SYSAUX                         AUTO

UNDOTBS1                       MANUAL

TEMP                           MANUAL

USERS                          AUTO

MAXIMO                         AUTO

RBS                            AUTO

RBSBIG                         AUTO

TOOLS                          AUTO

@?/rdbms/admin/catqm.sql  andrew123  USERS  TEMP YES

@?/rdbms/admin/utlrp.sql

step2: install the dbms_network_acl_admin package

@ ?/rdbms/admin/catnacl.sql

@ ?/rdbms/admin/dbmsnacl.sql

@ ?/rdbms/admin/prvtnacl.plb

Step3 create acl

Log db as sysdba

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl=> 'NOR754S_SENDMAIL.xml',

    description=> 'EMAIL_SEND',

    principal=> 'SYS',

    is_grant=> TRUE,

    privilege=> 'connect',

    start_date=> SYSTIMESTAMP,

    end_date=> NULL);

  COMMIT;

END;

/

Step4 :add other user to acl

Run the following SQL AS sysdba:

sqlplus  -s  / as sysdba < set termout off

set heading off
set pagesize 0
set verify off
set echo off

spool /home/ora11g/NOR754S.sql

select

 'BEGIN' ||chr(10)||

 ' DBMS_NETWORK_ACL_ADMIN.add_privilege ( '||chr(10)||

 '    acl=> '||'''NOR754S_SENDMAIL.xml'''||', '||chr(10)||

 '   principal=>'''||upper(username)||''','||chr(10)||

 '   is_grant=> TRUE, '||chr(10)||

 '  privilege=> '||'''connect'','||chr(10)||

 '  position=> NULL, '||chr(10)||

 '  start_date=> SYSTIMESTAMP,'||chr(10)||

 '   end_date=> NULL);'||chr(10)||

 '    END;'||chr(10)||

 '/'||chr(10)

from dba_users where account_status='OPEN'

and username 

not in('SYS');

spool off

!

---then run

sqlplus  /as sysdba

@/home/ora11g/NOR754S.sql

---to add other user

step5: assign act to network

Only one ACL can be assigned to a specific host and port-range combination. Assigning a new ACL to a specific host and port-range results in the deletion of the previous assignment. You must take care when making a new assignment that you are not opening ports that were closed by a previous ACL assignment, or you could be opening yourself to attack. When wildcard usage causes overlapping assignments, the most specific assignment will take precedence, so an ACL assigned to 192.168.2.3:80 takes precedence over once assigned to 192.168.2.* etc. Run the following sql:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl=> 'NOR754S_SENDMAIL.xml',

    host=> '9.xxx.xxx.19', ----郵件伺服器IP

    lower_port=>24433,   -----指定埠號

    upper_port=> NULL);

         COMMIT;

END;

/