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:

• Oracle Health Check Collections Manager for Oracle Application Express 5.x

• Oracle Health Check Collections Manager for Oracle Application Express 4.2

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 Upload Collections - Log"

   Figure 3-55 Upload Collections - Viewing Logs

   
   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.