Manually Cleanup Submitted task in a cross database scenario - For Oracle

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

Following the instructions from online documentation available at Cleanup Submitted Task, manually executing the archive_crossdb_oracle.sql results in below error

Procedure ARCHIVEBYID compiled

Procedure ARCHIVEBYEVENTID compiled

Procedure ARCHIVEBYROWID compiled

Procedure ARCHIVEBYTASKID compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
70/17     PL/SQL: SQL Statement ignored
70/35     PL/SQL: ORA-00942: table or view does not exist
Errors: check compiler log

Procedure ARCHIVETASKS compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
52/17     PL/SQL: Statement ignored
52/17     PLS-00905: object <SCHEMA_NAME>.ARCHIVEBYTASKID is invalid
Errors: check compiler log

Procedure ARCHIVETASKPERSISTENCE compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
13/9      PL/SQL: Statement ignored
13/9      PLS-00905: object <SCHEMA_NAME>.ARCHIVETASKS is invalid
Errors: check compiler log

Environment

Identity Manager 14.4 & 14.5

Cause

In a cross database scenario where Task Persistence and Archive databases do not share the same database or tablespace, the script archive_crossdb_oracle.sql must be executed manually before performing cleanup of submitted tasks. The script must be run as archive database owner and this requires that specific Task persistence tables to be granted with SELECT & DELETE privileges on archive db owner. 

The above indicated error is resulted from lack of privileges (SELECT & DELETE) for archive db owner to execute commands on some of the Task persistence tables. 

Resolution

The problem can be resolved by implemeting one of the below steps:

1. Grant SELECT and DELETE privileges to the Archive database owner for the following tables in the Task persistence in addition to other tables listed in the documentation. 

task_to_resubmit_endpointdata
task_to_resubmit

grant SELECT on "TP_SCHEMA_NAME"."TASK_TO_RESUBMIT_ENDPOINTDATA" to "ARCHIVE_DB_OWNER"; 
grant DELETE on "TP_SCHEMA_NAME"."TASK_TO_RESUBMIT" to "ARCHIVE_DB_OWNER";

OR

2. Please grant SELECT and DELETE privileges to Archive database owner for all the tables in the Task Persistence database

Additional Information

Online documentation on Cleanup Submitted Task is available at 

Cleanup Submitted Tasks Tab