ACL and XDB Problems

Problem statement:

After upgrading a database from Oracle 11.1.0.6 to 11.1.0.7 an XDB installation was performed to be able to send Emails from the database.
As of Oracle 11g the sending of Emails needs the creation of an Access Control List, to control which database users can access a remote host for relaying ( or sending ) the Email to.

On the Internet there are scripts available to create such an ACL.

We used the script below to first create a procedure that takes care of all the steps involved in creating an ACL:

create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;
/


Now we used the following call to this procedure to create an ACL for an user:

begin
mailserver_acl(
'mailserver_acl.xml', -- Name of the ACL list
'ACL used for Email', -- Just some comment about what this list is/does
'SCOTT', -- The user/role to grant this privilige to
TRUE, -- Is the privilege granted (TRUE) or denied (FALSE)
'connect', -- What right granted
'our.mail.server', -- to what server
25); -- on which Port
end;


The first time the result was:
ORA-46105: Unable to load security class DAV::dav

Looking at the database objects, an Invalid package was detected and recompiled.
After recompiling the package and retrying the procedure, the below problem appeared:
ORA-46105: Unable to load security class http://xmlns.oracle.com/plsql:network


Googling a bit around, gave us the result that the problem was in a failed XDB installation. This was true, because there still were some Invalid XDB objects, which couldn't be compiled.


The solution:

The below steps brought us to a solution. It took an S/R with Oracle support to get the steps right, because the Notes aren't that clear:

First make a note of the tablepace used by the XDB user in the database.
Also write down the name of the TEMP tablespace of that database.
These values will be needed in the reinstallation steps.

First deinstall the current XDB installation. Refer to Note 742014.1 if you're using applications like APEX or Oracle Multimedia.

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> spool xdb_reins.log
SQL> shutdown Immediate;
SQL> startup;
SQL> @?/rdbms/admin/catnoqm.sql

This will completely deinstall the XDB installation and also drop the XDB user.
Now shutdown the database again, leave and restart SQLplus.

SQL> shutdown immediate
SQL> quit;

It is very important in this stage to exit and restart SQLplus
Use the statement below to reinstall the XDB packages.

Note that you need to enter the above noted values at the prompt of the SQL script.

In our case we entered 'xdb' for the password of the XDB user, 'sysaux' for the tablespace and 'temp' for the temporary tablespace;

SQL> conn / as sysdba
SQL> spool xdb_reins.log append --append to the existing logfile
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <xdb> <target tablespace> <temp tablespace>
SQL> spool off
SQL> quit

When these steps finish, the Oracle Note about reinstalling XDB also finishes.

It is thanks to good assistance of Oracle Support ( and no, I don't get money for this statement ) that our reinstallation succeeded.

There is another step to take !!
That is why.

You have to re-register XML components of the EXFSYS schema's.
Note 558834.1 discusses these steps. But I'll also list them here.
I strongly recommend to first read the Note, before executing these steps!!

-- Register XML Schemas for the Rules Manager component (part of CATPROC)

SQL> connect / as sysdba
SQL> alter session set current_schema = EXFSYS;
SQL> @?/rdbms/admin/rulpbs.sql

SQL> commit;

Note 558834.1 also show steps for other schema's but they only need to be executed for Oracle Intermedia.

After all these steps and another restart of the database, our ACL could be created and we were able to start using UTL_MAIL packages.