Monitoring and Maintaining Your AQ Queues

Generating Queue Reports

You can monitor the events in your AQ database queues by generating the following reports:

A summary report that lists the number of events set to the READY and PROCESSED states. To generate a summary report, run the following command:
```
pin_publish_aq_oracle.pl report -r summary -q queue_name [-l/@database_alias]
```
A detailed report that lists each event's ID, state, queuing time, and dequeuing time. To generate a detailed report, run the following command:
```
pin_publish_aq_oracle.pl report -r detail -q queue_name [-l/@database_alias]
```

where:

queue_name specifies the queue name.

database_alias specifies the BRM database alias for the database schema. If you omit the -l parameter, the pin_publish_aq_oracle.pl utility prompts you for the database alias.

For more information about the pin_publish_aq_oracle.pl utility's syntax and parameters, see "pin_publish_aq_oracle.pl".

Dropping the Queue and Queue Tables

To drop an AQ queue and its tables from the database schema, run the following command:

pin_publish_aq_oracle.pl drop -q queueName [-l/@database_alias]

If the database contains no other AQ queues, the utility removes the database package, which contains stored procedures for queuing, dequeuing, and purging events.

Configuring the AQ Queue's Default Location

You specify the default tablespace and queue size for your AQ database queues using the create_aq_queue.conf file. The pin_publish_aq_oracle.pl utility creates AQ database queues in this location when the -t queueTable parameter is not provided at the command line.

To specify the default location of the AQ database queue:

Open the BRM_home/apps/pin_publish_aq/create_aq_queue.conf file in a text editor.
Specify the target tablespace and queue size by editing the storage_clause parameter. For example:
```
$storage_clause = "tablespace PIN00 initrans 5 storage (initial 200k next 200k maxextents unlimited pctincrease 0 )";
```
Tip:

For production systems, create each AQ database queue in its own separate tablespace to improve processing performance.
Save and close the file.

Configuring the Default Retention for Events in the AQ Queue

The Oracle queue monitor process (QMN) removes any event from the AQ database queue that has been in the PROCESSED state for a specified amount of time. You set the default amount of time by using the create_aq_queue.conf file. The pin_publish_aq_oracle.pl utility creates queues with this retention setting when the -r retentionTime option is not provided at the command line.

Note:

Events in the READY state are retained forever.

To configure the default retention time:

Open the BRM_home/apps/pin_publish_aq/create_aq_queue.conf file in a text editor.
Set the retention_time parameter to the amount of time, in seconds, that you want to store PROCESSED events in the AQ database queue:
```
$retention_time = time;
```
Tip:

For production systems, set the retention time to 0 to optimize your processing performance.
Save and close the file.

Troubleshooting Synchronization Issues

If an error occurs during the synchronization process, check the Oracle DM log file for error codes.

Database Queue Creation Error

To create AQ database queues, the pin user must have Oracle AQ privileges. If pin does not have privileges, you receive the following error when creating queues with the pin_publish_aq_oracle.pl utility:

PLS-00201 identifier 'SYS.DBMS_AQ' must be declared

To fix this error:

Using SQL*Plus, log in to your database as the SYS user and grant advanced queuing privileges to user pin:

% sqlplus sys@databaseAlias
Enter password: password

SQL> grant execute on dbms_aq to pin;
  
Grant succeeded.
  
SQL> grant execute on dbms_aqadm to pin;
  
Grant succeeded.
  
SQL> grant execute on dbms_lock to pin;
  
Grant succeeded.

Create your queue by running the pin_publish_aq_oracle.pl utility manually.

See "Creating Additional Queues for Multischema BRM Systems".

Missing Registry Entries

If you enable interleaved event processing in DAT_Listener, and you specify the event and CDR processing time entries (MaxEventProcessTime and MaxCDRProcessTime), you must also specify the entries that set the number of event thresholds (MaxNumEvents and MinNumEvents). If you do not do this, DAT_Listener throws a critical error (ERR_REG_VALUE_INVALID) during startup. However, if you update the registry by using a semaphore and the required entries are not specified, DAT_Listener throws a warning and disregards the semaphore update.

Semaphore Entry Errors

When an interleaving semaphore entry contains an error, the pipelines are deactivated.

The following interleaving semaphore errors cause the pipelines to be deactivated:

The values for MinNumEvents and MaxNumEvents are not numbers.
MinNumEvents is less than MaxNumEvents.
The values for MaxEventProcessTime and MaxCDRProcessTime are not numbers.
CheckInterval is not a number.

To reactivate the pipelines, use the Active semaphore. For example:

ifw.Pipelines.PRE_PROCESS.Active=TRUE
ifw.Pipelines.PRE_RECYCLE.Active=TRUE
ifw.Pipelines.ALL_RATE.Active=TRUE

After the pipelines are reactivated, reissue the interleaving semaphore using the correct values.