Troubleshooting Oracle Health Check Collections Manager

Use the following information to troubleshoot common installation, configuration, email notification, and collection-processing issues in Oracle Health Check Collections Manager.

If the Installation Summary reports an error similar to the following:
```
error at line 13: PLS-00201: identifier 'UTL_SMTP' must be declared
```
Grant the EXECUTE privilege on UTL_SMTP to the Oracle Application Express parsing schema or workspace owner.
If users need to download files from Oracle Health Check Collections Manager, additional configuration is required.

Note:
This configuration is required only for downloading files. It is not required for uploading files to Oracle Health Check Collections Manager.
Before installing Oracle Health Check Collections Manager, re-create the Oracle Application Express built-in function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL in the appropriate APEX_XXXXX or FLOW_XXXXXX schema for your environment.

Run the following DDL:
```
CREATE OR REPLACE FUNCTION Wwv_flow_epg_include_mod_local(
  procedure_name IN VARCHAR2
)
RETURN BOOLEAN
IS
BEGIN
  RETURN TRUE; -- It should always return TRUE.

  IF UPPER(procedure_name) IN ('') THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END Wwv_flow_epg_include_mod_local;
```
After re-creating the function, verify that it is in the VALID state.

After installing Oracle Health Check Collections Manager, grant access to RCA13_GET_DOC to enable file downloads:
```
GRANT EXECUTE ON RCA13_GET_DOC TO PUBLIC;
```
Verify that Oracle Application Express is installed and configured successfully.
If any default system privileges were revoked from the default Oracle Application Express users, restore the required privileges.

Verify that the Oracle Application Express-related user accounts are not locked or expired.

If required, unlock the accounts:

ALTER USER ANONYMOUS ACCOUNT UNLOCK;
ALTER USER XDB ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER FLOWS_FILES ACCOUNT UNLOCK;

If email notifications are not working, verify the Access Control List (ACL) configuration and ensure that the application schema has the required privileges to connect to the SMTP server.

For example, create an ACL and grant the required privileges to the Oracle Health Check Collections Manager application schema:

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;

Replace ORACHK CM USERNAME, mailservername.com, and the port range with values appropriate for your environment.

If an uploaded collection remains in the NEW state for an extended period or collection processing does not start, verify the status of the RCA13_PROCESS_DATA database scheduler job.
Check the configured scheduler jobs:
```
SELECT *
FROM user_scheduler_jobs
WHERE job_name LIKE 'RCA13_%';
```
Check currently running scheduler jobs:
```
SELECT *
FROM user_scheduler_running_jobs
WHERE job_name LIKE 'RCA13_%';
```
Review scheduler job execution details:
```
SELECT *
FROM user_scheduler_job_run_details
WHERE job_name LIKE 'RCA13_%'
ORDER BY log_date DESC;
```
Verify that the required scheduler jobs are enabled and running correctly.

A separate RCA13_COL_% job is used to process each collection.

To investigate collection-processing problems further, review the Oracle Health Check Collections Manager log:
```
SELECT *
FROM rca13_log
ORDER BY ins_date DESC;
```
You can also review processing logs from the application by clicking Upload Collections > Log.
If collection processing fails because the Oracle Application Express tablespace or application schema tablespace does not have sufficient free space, increase the applicable tablespace size as required.