MSCPDPW: Planning Data Pull Worker errors Intermittently ORA-01555 ORA-02063 ORA-06512 insert into MSC_ST_SALES_ORDERS (Doc ID 2518474.1)

Last updated on AUGUST 29, 2023

Applies to:

Symptoms

On : 12.2.5 version, Data Collections in PROD

Users have 2 different operating units so they run data collections twice Mon thru Fri
1st run 1 pm CST complete refresh - Overseas
2nd run 7:30 pm CST complete refresh - North America
Sales order parameter = No
Sat - no collections no planning
Sun - All orgs Complete refresh with Sales order parameter = Yes

Data collections will run fine for 8 to 10 days, but then the MSCPDPW: Planning Data Pull Worker errors with the following intermittently

ERROR
-----------------------
28-JAN 13:46:01 : insert into MSC_ST_SALES_ORDERS ( DEMAND_ID, ROW_TYPE, PARENT_DEMAND_ID, DELETED_FLAG, RESERVATION_TYPE, CTO_FLAG, REFRESH_ID, SR_INSTANCE_ID) select x.DEMAND_ID, x.ROW_TYPE, x.PARENT_DEMAND_ID, 1, 2, 2, :v_refresh_id, :v_instance_id from MRP_AD_HARD_RESERVATIONS_V@WOAPRD x where x.RN> :v_so_sr_lrn AND x.ORGANIZATION_ID IN (502,503,504,505,5907,6485,6705,7347,7367,8208,8228,8229,8270)
28-JAN 13:46:01 : insert into MSC_ST_SALES_ORDERS ( DEMAND_ID, ROW_TYPE, PARENT_DEMAND_ID, DELETED_FLAG, RESERVATION_TYPE, CTO_FLAG, REFRESH_ID, SR_INSTANCE_ID) select x.DEMAND_ID, x.ROW_TYPE, x.PARENT_DEMAND_ID, 1, 1, x.CTO_FLAG, :v_refresh_id, :v_instance_id from MRP_AD_SALES_ORDERS_V@WOAPRD x WHERE x.RN> :v_so_lrn AND x.ORGANIZATION_ID IN (502,503,504,505,5907,6485,6705,7347,7367,8208,8228,8229,8270)
28-JAN 14:30:19 : Error pulling data from MRP_AN3_SALES_ORDERS_V to MSC_ST_SALES_ORDERS.
28-JAN 14:30:19 : ORA-01555: snapshot too old: rollback segment number 182 with name "_SYSSMU182_1285772741$" too small
ORA-02063: preceding line from WOAPRD
28-JAN 14:30:19 : ORA-01555: snapshot too old: rollback segment number 182 with name "_SYSSMU182_1285772741$" too small
ORA-02063: preceding line from WOAPRD
28-JAN 14:30:19 : Error_Stack...
28-JAN 14:30:19 : ORA-01555: snapshot too old: rollback segment number 182 with name "_SYSSMU182_1285772741$" too small
ORA-02063: preceding line from WOAPRD

28-JAN 14:30:19 : Error_Backtrace...
28-JAN 14:30:19 : ORA-06512: at "APPS.MSC_CL_PULL", line 7028
ORA-06512: at "APPS.MSC_CL_PULL", line 1815

If it fails with the Sales order parameter = Yes then they set it to No and that will fix the problem and vice versa - no rhyme or reason. Sometimes on AN2 or AN3 view. This is not duplicable in Test only PROD. Sometimes they rebuild all snapshots - no recent RDBMS upgrades - seeing this error 5-6 times per month. Started about 6 months ago


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run data collections
2. Intermittently PDP concurrent request fails with multiple errors
3. Set PDP sales order parameter to opposite setting and rerun collections to get successful collections run

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.