Setting SP_IMP_QUEUE_PAUSE fails with “For queue pause to take effect the value of SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME” (4297928)

Setting SP_IMP_QUEUE_PAUSE fails with “For queue pause to take effect the value of SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME”

The setting of parameter param sp_imp_queue_resume results in error as shown below:

sp_ctrl (alvsupu07:5438)> set param sp_imp_queue_pause 1

sp_ctrl (alvsupu07:5438)> set param sp_imp_queue_resume 2

   Parameter type checking failed - look in param-defaults file

The event log shows the following error:

Warning  2017-01-16 13:40:54.797522 16056 1 Import: Ignoring SP_IMP_QUEUE_PAUSE  (importing from alvsupu15.PROD.QUEST.CORP queue alvsupu15) [module imp]

Notice   2017-01-16 13:40:54.796435 16056 1 Import: For queue pause to take effect the value of SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME and SP_IMP_QUEUE_RESUME must be greater than 0  (importing from alvsupu15.PROD.QUEST.CORP queue alvsupu15) [module imp]

Notice   2017-01-16 13:40:52.119653 16096 1 Parameter SP_IMP_QUEUE_PAUSE was changed from 0 to 1

Notice   2017-01-16 13:40:52.117171 16096 1 User command: oracle set param sp_imp_queue_pause 1 (from alvsupu07)

The parameter sp_imp_queue_resume should always be set lower than the parameter param sp_imp_queue_pause and not equal to or higher than it.

This is the underlying condition for the parameter SP_IMP_QUEUE_PAUSE to take effect. Moreover it has to be greater than 0. The above two conditions need to be met for the parameter sp_imp_queue_resume to take effect.

Here are the description of the above parameters as given in the Reference Guide: SP_IMP_QUEUE_PAUSE This parameter pauses the writing of data to the post queue when that queue contains the specified number of messages. Post stores queue messages in shared memory until it issues a checkpoint, after which it releases the data from memory. If the post queue runs out of shared memory, the read and write functions will start incurring file IO to free up the memory buffers. By pausing the queue writing, this parameter helps Post maintain its performance by avoiding the need for disk storage and the resultant slowdown in IO. When Import is requested to write to a post queue, and the backlog for that queue is equal to or greater than SP_IMP_QUEUE_PAUSE, Import checkpoints with it associated Export and stops writing to any post queue. Import does, however, remain running. A warning is written to the event log and the status of the process in the show statusdb command output is shown as "paused." The status command shows "Paused" instead of "Running." Import resumes writing to the post queue(s) if Import is stopped and restarted or Import detects that the backlog is less than or equal to the SP_IMP_QUEUE_RESUME parameter. Use the SP_IMP_QUEUE_RESUME parameter to set the number of messages at which Import resumes writing to the post queue. To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be greater than SP_IMP_QUEUE_RESUME. Default: 0 messages (disabled) Range of valid values: n thousand messages, where n is any positive integer Takes effect: immediately SP_IMP_QUEUE_RESUME This parameter works in conjunction with SP_IMP_QUEUE_PAUSE. If the number of messages in the post queue is lower or equal to the value set with this parameter, Import resumes writing to the post queue. To use this feature, both SP_IMP_QUEUE_PAUSE and SP_IMP_QUEUE_RESUME must be greater than zero, and SP_IMP_QUEUE_PAUSE must be gre