export import ACL & ORA-24244 error during import

Hello everyone,

it’s been a while since my last post. So, I wanted to write about export and import ACL privileges. They always become a pain (at least for me). after 12c, Oracle introduce us a new and easy way of ACL copying from db to db. export&import.

so here is the basic command on your source database:

expdp system directory=DATA_PUMP_DIR logfile=ACL_EXPORT.log dumpfile=ACL_EXPORT.DMP full=y include=NETWORK_ACL

1	expdp system directory=DATA_PUMP_DIR logfile=ACL_EXPORT.log dumpfile=ACL_EXPORT.DMP full=y include=NETWORK_ACL

this will export all stored ACL privileges on your database and to import those ACLs to a new db:

impdp system directory=DATA_PUMP_DIR logfile=ACL_IMPORT.log dumpfile=ACL_EXPORT.DMP

1	impdp system directory=DATA_PUMP_DIR logfile=ACL_IMPORT.log dumpfile=ACL_EXPORT.DMP

so easy but during the import you can get ORA-24244: invalid host or port for access control list (ACL) assignment error.

I encountered this error while creating a new db for a client. In my case this has happened because RESOLVE privileges shouldn’t be used with port definition!

so, somehow previous db has port definitions for RESOLVE privileges and those ones wasn’t imported. to add them into new db, I simply used this sql:

select 
  'BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '''|| t1.host||''',
ace => xs$ace_type(privilege_list => xs$name_list(''RESOLVE''),
principal_name => '''||t2.principal||''',
principal_type => xs_acl.ptype_db));
end;
/' run_Script
from dba_network_acls t1
join dba_network_acl_privileges t2 on t1.aclid = t2.aclid
where privilege='resolve' and lower_port is not null;

select

'BEGIN

DBMS_NETWORK_ACL_ADMIN.append_host_ace (

host => '''|| t1.host||''',

ace => xs$ace_type(privilege_list => xs$name_list(''RESOLVE''),

principal_name => '''||t2.principal||''',

principal_type => xs_acl.ptype_db));

end;

/' run_Script

from dba_network_acls t1

join dba_network_acl_privileges t2 on t1.aclid = t2.aclid

where privilege='resolve' and lower_port is not null;

run this select on the source db and execute the output on the target db.

wish you all happy and healthy days.

Leave a Reply Cancel reply