Datamaker: Unable to access repository-ORA-24247 error
Article ID: 7998
Updated On:
Products
Issue/Introduction
When trying to connect to the repository database in Datamaker, we are receiving the following error message:
Profile: gtrep
Title: Test Data Repository
Database Fatal Error Message: ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1
Transaction Error Code: -1
Database Error Code: 24247
CA Test Data Manager - Datamaker Details:
GUI Version: 4.2.0.141 (6/22/2017 22:04:38)
REP Version: 3.2I
Transaction Details:
PB DBMS driver: ORA Oracle
Client Version: 12.1.0.0.0 Production
Server DBMS: Oracle (oracle)
Server Version: 11.2.0.4.0
Environment
Cause
This error message has various causes. We recommend enabling debugging and analyzing the log files further with your DBA. Directions to do this are listed here: https://knowledge.broadcom.com/external/article?articleId=9609
Resolution
Here are some common resolutions for this error.
A. Grants are missing:
This step requires a DDL and a batch file in order to add the grants properly to Oracle. The contents can be found below. Optionally, both files can be downloaded:
addGrants.zip
1. Save the following as grants.ddl (using Notepad++ or similar tool):
spool grants.log
PROMPT Ignore any errors in this section if you are using Oracle10g or Oracle XE 11g SE or 12c
WHENEVER SQLERROR continue;
prompt Drop the acl if it exists, we then recreate it fresh.
prompt NB this method may fail to delete an existing acl in 12c but you can
prompt ignore the error that arises when it tries to recreate it.
declare n number;
begin
SELECT count(*) into n FROM resource_view WHERE any_path like '/sys/acls/resolve.xml';
if n = 1 then
dbms_network_acl_admin.drop_acl(acl => 'resolve.xml');
end if;
end;
/
prompt Create the acl. NB this call may fail in 12c if the above code failed to delete
prompt an existing acl. You can ignore the error that arises when it tries to recreate it.
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal => '&2', is_grant => true, privilege => 'connect');
prompt If that failed you can ignore the error.
exec dbms_network_acl_admin.add_privilege(acl => 'resolve.xml', principal => '&2', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host => '*');
PROMPT End ignore errors section
exit
2. Save the following in the same location as grants.ddl as addGrants.bat:
echo on
rem -----------------------------------------------------------------------------------------------------------
rem VARIABLE DEFINITIONS TO BE SET BELOW
rem -----------------------------------------------------------------------------------------------------------
rem GT_SYS_USER: System user for Oracle instance
rem GT_SYS_PASSWORD: Password for system user
rem GT_TNS: TNS alias for target database
rem GT_REP_USER: Repository user
rem GT_REP_USER_UC: Repository user (uppercase)
rem GT_REP_PASSWORD: Repository password
rem -----------------------------------------------------------------------------------------------------------
rem SET VARIABLES
rem -----------------------------------------------------------------------------------------------------------
set GT_SYS_USER=system
set GT_SYS_PASSWORD=manager
set GT_TNS=@orcl
set GT_REP_USER=GTREP
rem THE FOLLOWING ENV VARIABLE MUST BE SET TO THE USER NAME IN UPPER CASE
set GT_REP_USER_UC=GTREP
set GT_REP_PASSWORD=Gridt00ls
echo ---------------------------------------------------------------------------------------------------------- >> repository.log
echo
EXECUTE GRANTS.DDL - ACCESS CONTROL LISTS >> repository.log
echo ---------------------------------------------------------------------------------------------------------- >> repository.log
sqlplus -L %GT_SYS_USER%/%GT_SYS_PASSWORD%%GT_TNS% @grants.ddl %GT_REP_USER% %GT_REP_USER_UC% >> repository.log
3. Modify the variables as noted at the top of batch file. Specifically: GT_SYS_USER, GT_SYS_PASSWORD, GT_TNS, GT_REP_USER and GT_REP_USER_UC.
4. Run addGrants.bat
B. Run scripts from the database installation kit:
- Need to run the scripts from the database install kit again.
- Use the grant DDL script and re-do the views.
- After doing this, the issue should be resolved.
exec dbms_network_acl_admin.add_privilege(acl => 'resolve.xml', principal => '&2', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host => '*');
dbms_network_acl_admin.drop_acl(acl => 'resolve.xml');
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal => '&2', is_grant => true, privilege => 'connect');
exec dbms_network_acl_admin.drop_acl(acl => 'resolve.xml');
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal => 'GTREP', is_grant => true, privilege => 'connect');
exec dbms_network_acl_admin.add_privilege(acl => 'resolve.xml', principal => 'GTREP', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host => '*');
C. The user does not have access to the database host or port.
- The error "ORA-24247: network access denied by access control list (ACL)" suggests that the user trying to connect does not have access to the database host or port.
- Try doing a tnsping from your client machine to the database server to test the connection.
- Your DBA will be able to help you further on this in checking the connectivity from your local machine to the database server and granting necessary privileges.
- Please refer to the topic "Using the TNSPING Utility to Test Connectivity from the Client" from https://docs.oracle.com/cd/E11882_01/network.112/e41945/connect.htm#NETAG378 for further information.
4. Further explanations of the error and resolutions:
- http://dba-oracle.com/t_ora_24247_network_access_denied_by_access_control_list_tips.htm
- http://dbtricks.com/?p=159
- https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
- Work with your internal DBA to resolve the issue as this is not a Datamaker product issue.
- Double check the installation process of our repository and see if an error occurred during the execution of grants.dll
- If the grants.dll didn't find a required xml on Oracle Server, the DBA will be able to resolve the issue and re-execute the grants.dll.
Additional Information
If you experience any further issues, please open a support case by going to https://support.broadcom.com
Attachments
Feedback
