DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, July 22, 2014

ORA-24247: network access denied by access control list (ACL)

Getting the above error after apex upgrade from 4.0.2 to 4.2.5 version. The PDF printing from EBS concurrent requests was failing. So followed the below steps to rectify the issue.

On executing the below verification script got the following output:

SQL> DECLARE
  ACL_PATH VARCHAR2(4000);
  ACL_ID RAW(16);
  BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040200
  -- the "connect" privilege if APEX_040200 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
    -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040200'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
  FROM XDB.XDB$ACL A, PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
  EQUALS_PATH(P.RES, ACL_PATH) = 1;
    DBMS_XDBZ.ValidateACL(ACL_ID);
    IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE (ACL_PATH, 'APEX_040200', 'connect')IS NULL THEN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
  'APEX_040200', TRUE, 'connect');
  END IF;
    EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
  'ACL that lets power users to connect to everywhere',
  'APEX_040200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
  END;
/

Output
=====



DECLARE
*
ERROR at line 1:
ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
ORA-06512: at "XDB.DBMS_XDBZ", line 130
ORA-06512: at line 22


Script to check the ACL:

SQL> SELECT ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
NACL.ACLID = ACE.ACLID AND
NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);



Output:
=====

ACL
--------------------------------------------------------------------------------
PRINCIPAL
--------------------------------------------------------------------------------
/sys/acls/OracleEBS.xml
APEX_040000

Solution:
======


SQL> create user APEX_040000 identified by apex;

User created.

SQL> grant connect,resource to APEX_040000;

Grant succeeded.

SQL> begin
2 dbms_network_acl_admin.add_privilege('/sys/acls/OracleEBS.xml',
'APEX_040200', TRUE, 'connect');
3 4 end;
5 /

PL/SQL procedure successfully completed.



Now the issue was fixed and was able to print PDF from EBS concurrent request.

KEY COLUM Explanation process




KEYCOLS is used when there is no primary key used in the table . Normally Oracle GoldenGate requires a unique row identifier on the source and target tables to locate the correct target rows for replicating updates and deletes statements. We can define a substitute columns which contain unique values. We can define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter
Example:
Extract Program without keycols

Replicat Program Without Keycols


Trying To Update The Column



When we try to update the column automatically replicate program got a bended. Golden Gate throws
WarningOGG-01004 in ggserr.log file. Golden Gate error log clearly shows that due to update statement only the replicat program got abended.

Replicate Got  a bended





Method Of Using Keycols
After Adding Keycols In Extract Program


After Adding Keycols In Replicat Program


Error Resolved


Sunday, July 20, 2014

Dead Connection Detection and Clear the same


Dead Connection Detection (DCD)

These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally.
Examples of a dead connection:
- A user reboots/turns-off their machine without logging off or disconnecting from the database.
- A network problem prevents communication between the client and the server.

In these cases, the shadow process running on the server and the session in the database may not terminate.

Implemented by
      * adding SQLNET.EXPIRE_TIME = <MINUTES> to the sqlnet.ora file

With DCD is enabled, the Server-side process sends a small 10-byte packet to the client process after the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter.

If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer..and another packet will be sent when next interval expires (assuming no other activity on the connection).

If the client fails to respond to the DCD probe packet
     * the Server side process is marked as a dead connection and
     * PMON performs the clean up of the database processes / resources
     * The client OS processes are terminated

NOTE: SQLNET.RECV_TIMEOUT can be set on the SERVER side sqlnet.ora file. This will set a timeout for the server process
                 to wait for data from the client process.


Database Resource Limits (using user profiles)

Implemented by
     * Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
     * Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit
     * Assigning a profile to a user whose resources are wished to be limited

It could happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being set to 'sniped' and then getting 'cleaned up' via the shell script. The removal of the dispatcher will result in other sessions 'dying' .In that case, If you are to implement resource limits, may be advisable to create new profiles
that be assigned to users and not to change the characteristics of DEFAULT.
Alternatively, if you do change DEFAULT, ensure that all the properties that you
have affected have been fully tested in a development environment.

When a resource limit is exceeded (for example IDLE_TIME) ... PMON does the following
     * Mark the V$SESSION as SNIPED
     * Clean up the database resources for the session
     * Remove the V$SESSION entry

When a resource limit is exceeded (for example IDLE_TIME) ... PMON marks the session as SNIPED in V$SESSION.  Then, AFTER the SNIPED session tries to execute any SQL statement, its database resources are cleaned up and its V$SESSION entry is removed.

It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to clean up resources at both the database and OS level

This combination will not clean up IDLE / ABANDONED / INACTIVE connections (OS processes) as these sessions still have active clients

For this case we will see that :
     * PMON has cleaned up the V$SESSION entries .. but both the OS processes and the V$PROCESS entries will still exist
     * SQLNET will continue to be able to send the 10 byte packet successfully until the session is logged off

This condition can be a major problem as
     * The database exhausts PROCESSES and gives ORA-20 maximum number of processes <num> exceeded
     * The OS can become exhausted due to the unneeded resources consumed by the abandoned processes

The SYMPTOMS of this condition are
     * The database view V$PROCESS will have no corresponding V$SESSION entry
     * An OS process / thread still exists for the SNIPED session

The solutions to this scenario can are to cleanup the OS processes ... after which the V$PROCESS entries should be removed automatically

Methods to cleanup OS processes:

     * UNIX : kill -x ... the OS process at the OS level (typically kill -9)
     * UNIX:  if using a dedicated server, use the following shell script to kill the shadow process
#!/bin/sh
tmpfile=/tmp/tmp.$$
sqlplus system/manager <<EOF
spool $tmpfile
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || ''''  || ';' from v$session s where s.status  = 'SNIPED';
spool off
EOF
sqlplus system/manager <<EOF
@tmpfile
EOF
rm $tmpfile