Optimizing Oracle archiving and purging

NOTE: This article applies only to xMatters version 4.1; the process has not been updated to support xMatters on demand or xMatters version 5.0.

Archiving and purging database information in xMatters is database generic, resulting in slower performance on Oracle databases. This article explains how to improve the performance of an xMatters deployment using an Oracle database by optimizing the archiving and purging processes.

Issue Details

To help resolve this issue, two SQL scripts are attached to this article: CreateArchivingScheduledJob.sql and CreateSPArchiving.sql.

Resolution

To implement this solution, you will need to create Oracle Stored Procedures to perform an optimized archive/purge, and configure the Schedule Job settings in xMatters to avoid collisions between the Stored Procedures and the xMatters archiving and purging schedule.

Before creating the Stored Procedures, ensure that your database administrator has granted the permissions required for the xMatters schema owner to be able to create scheduled jobs. To grant the appropriate permissions, run the following query (replace "<XMATTERS>" with the schema owner):

GRANT CREATE ANY JOB TO <XMATTERS>;   

To create Oracle Stored Procedures:

  1. Stop all xMatters Nodes and Webservers.
  2. Review and run the CreateSPArchiving.sql script while logged in to sqlplus as the xMatters schema owner to create the following logging tables and stored procedures:
    • Table: AP_ARCHIVING_LOG
    • Table: ARCHIVE_TABLES
    • Stored Procedure: LOG_ARCHIVE_ENTRY
    • Stored Procedure: ARCHIVE_TABLE
    • Stored Procedure: ARCHIVE_ALL_TABLES
  3. Edit the CreateArchivingScheduledJob.sql script to configure the Retention period and Archiving schedule:
BEGIN  
2 DBMS_SCHEDULER.CREATE_JOB (  
3 job_name => 'AP_Archiving_Job',  
4 job_type => 'PLSQL_BLOCK',  
5 job_action => 'DECLARE  
6 v_runtime_retention INTEGER := 7; -- How many days to keep  
data in Runtime, before moving to archive  
7 v_archive_retention INTEGER := 21; -- How many days to keep data in Archive area, before deleting from system  
-- Set the below two variables to override retention period  
9 -- ie Setting v_first_date_to_archive := to_timestamp(''2008/11/29 11:45'', ''yyyy/mm/dd hh24:mi'')  
10 -- will Archive all runtime data older than 2008/11/29 11:45 regardless of retention period  
11 v_first_date_to_archive TIMESTAMP := NULL;  
12 v_first_date_to_delete TIMESTAMP := NULL;  
13 BEGIN  
14 archive_all_tables(1000,v_runtime_retention,v_archive_retention, v_first_date_to_archive, v_first_date_to_delete);  
15 END;',  
16 start_date => to_timestamp('2005/01/01 23:15:00','yyyy/mm/dd hh24:mi:ss'),  
17 repeat_interval => 'FREQ=DAILY',  
18 enabled => TRUE);  
19 END;  
20 /

Notes:

  • In line 6, the "v_runtime_retention" parameter indicates the number of days you want xMatters to retain runtime data.
  • In line 7, the "v_archive_retention" parameter indicates the number of days you want xMatters to retain archived data.
  • Lines 11 and 12 are for manual archiving / purging of xMatters data and should stay NULL for regular use.
  • Lines 16 and 17 control how often and when the archiving / purging stored procedures should run. Change the time portion of start_date to reflect the time you want the archiving / purging process to run.

For more details, see Oracle's guide on using the Scheduler

  1. As the xMatters schema owner, run the CreateArchivingScheduledJob.sql script.
  2. Restart the Node and web servers.

To configure xMatters:

  1. Log in to xMatters as a Super Administrator
  2. On the Admin tab, in the Configuration menu, click Schedule Jobs.
  3. On the Schedule Job Selection page, ensure that Process Expired Data is displayed in the drop-down list, and then click Continue.
  4. In the Runtime data retention time field, specify a number of days that is at least one greater than the value you set for the v_runtime_retention parameter in the CreateArchivingScheduledJob.sql file.
  5. In the Archive data retention time field, specify a number of days that is at least one greater than the value you set for the v_archive_retention parameter in the CreateArchivingScheduledJob.sql file
  6. Click Save.
  7. Click the Delete Expired Processes Trigger link.
  8. On the Schedule for Delete Expired Processes Trigger page, in the Start Time field, specify a start time that is at least four hours later than the time specified the start_date parameter in the CreateArchivingScheduledJob.sql.
    • The objective is to allow the first four hours for the Stored Procedures to process the expired data.
  9. Click Save.

Further Information

To view the logs for an archive/purge run, issue the following query as the schema owner while logged in to an Oracle client:

SELECT * FROM ap_archiving_log ORDER BY db_ts;  

To see the status of the Oracle scheduler job 'AP_Archiving_Job', issue the following query:

SELECT JOB_NAME, ENABLED, STATE, NEXT_RUN_DATE, FAILURE_COUNT, RUN_COUNT FROM USER_SCHEDULER_JOBS;

xMatters Reference

JDN-1282 Originally created by Don Clark

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk