oracle application r11i,Oracle ERP-ERP R11i-MLOG -MLOG$_BOM_COMPONENTS_B

TIP:

Click help for a detailed explanation of this page.

Subject:

APS/ASCP/GOP MLOG Unused

Note:580205.1

Type:

HOWTO

Last Revision Date:

31-MAY-2008

Status:

PUBLISHED

Applies to:

Oracle Advanced Supply Chain Planning - Version: 11.5.7 to

11.5.10

Information in this document applies to any platform.

ORA-01653 ORA-01555

Goal

Symptoms

- APS/GOP Is Not Used And MLOG Tables Grow Uncontrolled

- MLOG grow until all space is used up

- ATP Data Collection or Planning Data Collection is not

used.

- Load/Copy/Merge MDS MRCSAL1 fails with error message -

ORACLE error 12096 in mrlins_insert_mrp_sched_dates

Cause: mrlins_insert_mrp_sched_dates failed due to ORA-12096: error

in

materialized view log on "MRP"."MRP_SCHEDULE_DATES"

ORA-01653: unable to extend table MRP.MLOG$_MRP_SCHEDULE_DATES

Solution

Cause

The MLOG$ tables that store changes to various master tables to be

used to

update the database snapshots that are used by the APS modules are

not being

maintained so they continue to grow in size as the master tables

are updated.

Eventually space problems can occur and cause the programs that use

the

master tables to fail. Normally these are maintained by the 'Data

Collections'

processes for the APS and GOP/GATP programs. The Refresh

Collections Snapshot

program is not run so the snapshot logs are not maintained and

purged.

Fix

If Advanced Planning and Scheduling (APS) AND Global Order

Promising (GOP)

AND Demand Planning (ODP) are not used then the APS/GOP/ODP

SNAPSHOT LOGS

can be dropped and snapshot TRUNCATED. This will prevent the

uncontrolled

growth of the snapshot logs and free up the space used by the

snapshots.

NOTE: These objects can be recreated anytime by setting the

profile

option 'MSC: Source Setup Required' to 'Y' and running the

Refresh

Collections Snapshots program using the 'Complete' parameter.

It is recommended to apply the latest ASCP Data Collection

Rollup patch before recreating objects.

Reference Note 223026.1 for the latest collection patch.

NOTE: These are the commands to drop ONLY the objects that belong

to

APS/GOP. DO NOT drop any other snapshot logs as they would

belong

to other application products.

Note that not all objects exist in all versions.

It is best to keep the empty snapshots so that views that reference

them

do not go invalid. The views are not needed either but cause

confusion if

they are invalid. Only the Snapshot LOGS need to be dropped to

make

objects inert.

- objects introduced in APS 11.5.10

WSH.WSH_TRIP_SN

WSH.WSH_TRIP_STOP_SN

AHL.AHL_SCH_MTLS_SN

BOM.BOM_STRUCTURES_B

BOM.BOM_COMPONENTS_B

- object name difference in 11.5.10

BOM_BILL_OF_MATERIALS is now BOM_STRUCTURES_B

BOM_INVENTORY_COMPONENTS is now BOM_COMPONENTS_B

- Note that dropping the MLOG for BOM.BOM_COMPONENTS_B

will cause two ENI Materialized Views to go invalid,

these can be ignored as the objects are never used.

The MLOG is shared with these MVs which causes the records in

the

MLOG MLOG$_BOM_COMPONENTS_B to

always be preserved

even when the Snapshot/MV BOM_INV_COMPS_SN is refreshed.

ENI_DBI_BOM_COMPONENTS_MV1

ENI_DBI_BOM_COMPONENTS_MV2

These belong to the Daily Biz Intellegence (DBI) product

named

'Product Development Intelligence'

They are created during the BIS install and are never be used

again.

DROP SNAPSHOT LOG ON INV.MTL_SUPPLY;

DROP SNAPSHOT LOG ON INV.MTL_US

R_SUPPLY;

DROP SNAPSHOT LOG ON INV.MTL_USER_DEMAND;

DROP SNAPSHOT LOG ON INV.MTL_S

STEM_ITEMS_B;

DROP SNAPSHOT LOG ON INV.MTL_ONHAND_QUANTITIES_DETAIL;

DROP SNAPSHOT LOG N INV.MTL_MATERIAL_TRANSACTIONS_TEMP;

DROP SNAPSHOT LOG ON INV.MTL_DEMAND;

DROP SNAPSHOT&nbs

;LOG ON BOM.BOM_BILL_OF_MATERIALS; -- 11.5.9

DROP SNAPSHOT LOG ON BOM.BOM_STRUCTURES_

; -- 11.5.10

DROP SNAPSHOT LOG ON BOM.BOM_INVENTORY_COMPONENTS; -- 11.5.9

DROP&nbs

MetaLink V2 - Note