3.9 Troubleshooting Oracle Health Check Collections Manager

This topic describes how to troubleshoot Oracle Health Check Collections Manager.

Note:

There are two separate versions of Oracle Health Check Collections Manager respectively for Oracle Application Express 4.2 and Oracle Application Express 5.x.

The troubleshooting procedures are applicable to:

  1. If you see any error like, error at line 13: PLS-00201: identifier 'UTL_SMTP' must be declared in the Installation Summary, then grant execute on UTL_SMTP privilege to the parsing schema or workspace owner.
  2. If there is a requirement to download files from within the Oracle Health Check Collections Manager, then two more steps are required. These steps are NOT required to upload files into Oracle Health Check Collections Manager.
  3. Before installing the Oracle Health Check Collections Manager, run the DDL mentioned below to re-create the Application Express built-in function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL in the APEX_XXXXX or FLOW_XXXXXX schema whichever is appropriate to your environment. After re-creating the function, ensure that it is in VALID state.
    CREATE OR replace FUNCTION Wwv_flow_epg_include_mod_local( 
    procedure_name IN VARCHAR2) 
    RETURN BOOLEAN 
    IS 
    BEGIN 
    	RETURN TRUE; ----- It should be always “RETURN TRUE” 
    	IF Upper(procedure_name) IN ( '' ) THEN 
    		RETURN TRUE; 
    	ELSE 
    		RETURN FALSE; 
    	END IF; 
    END Wwv_flow_epg_include_mod_local;

    Once the Oracle Health Check Collections Manager is installed, run RCA13_GET_DOC to enable file downloads:

    SQL> grant execute on RCA13_GET_DOC to public;
  4. Ensure that Oracle Application Express is installed successfully. If you have revoked any default system privileges from default Application Express users, then grant them again.
  5. Ensure that all the Oracle Application Express related users are not locked and expired.
    alter user ANONYMOUS account unlock;
    alter user XDB account unlock;
    alter user APEX_PUBLIC_USER account unlock;
    alter user FLOWS_FILES account unlock;
  6. If you see any issues in setting up email notifications, then verify your ACL permissions and privileges to the application schema on the SMTP mail server.

    For example, to create ACL system and grant privileges to Application schema, do as follows:

    BEGIN
    	DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'apex1.xml',
    		description => 'APEX ACL',
    		principal => 'ORACHK CM USERNAME',
    		is_grant => true,
    		privilege => 'connect');
    	DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apex1.xml',
    		principal => 'ORACHK CM USERNAME',
    		is_grant => true,
    		privilege => 'resolve');
    	DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'apex1.xml',
    		host => 'mailservername.com',lower_port=>10,upper_port=>1000);
    END;
    /
    COMMIT;
  7. If you see that any uploaded collection processing is not started or collection status is NEW for long time, then verify the database scheduler job RCA13_PROCESS_DATA status and ensure that the job is enabled and running fine.
    select * from user_scheduler_jobs where job_name like 'RCA13_%';
    select * from user_scheduler_running_jobs where job_name like 'RCA13_%'
    
    select * from user_scheduler_job_run_details where job_name like 'RCA13_%'
    order by log_date desc;

    RCA13_COL_% job is used for processing each collection by having unique job.

    This logger helps you in debugging the non-processing collections with reference like
    select * from rca13_log order by ins_date desc;

    You can view the logs by clicking Upload Collections > Log.

    Figure 3-54 Upload Collections - Log

    Description of Figure 3-54 follows
    Description of "Figure 3-54 Upload Collections - Log"

    Figure 3-55 Upload Collections - Viewing Logs

    Description of Figure 3-55 follows
    Description of "Figure 3-55 Upload Collections - Viewing Logs"
  8. If you see that collection process is failed due to lack of space in Oracle Application Express tablespace and application schema tablespace, then increase the tablespace sizes as much as needed.