Tuesday Jan 20, 2015

Demantra Large Table Partitions and Using the Flashback Recycle bin, recyclebin, dba_recyclebin and sys.RECYCLEBIN$ Purge Best Practice

This is covered using MOS note:

Demantra Large Table Partitions and Using the Flashback Recycle bin, recyclebin, dba_recyclebin and sys.RECYCLEBIN$ Purge Best Practice (Doc ID 1962730.1)

When you are using the  feature and Oracle partitions are involved you will need to perform additional due diligence.  After the automatic
purge that occurrs when the quota is reached or after you issue a purge command to the recyclebin, you will notice that there are orphaned
BIN$ objects that consume the same space as the original partition that was dropped and the purged.

 

This was the solution based off of a customer SR that puzzled us at first.  The flashback documentation does not discuss the flashback purge
and partitions.  We recommend following the current best practice when managing the flashback feature and partitions.

If you have already performed or if the auto purge has occured, you will need to perform the following.  Of course customized to you BIN$ object name:

ALTER SESSION SET RECYCLEBIN=OFF;
drop table lpudemantra."BIN$A3+yI1NBASrgUwoVBkIBKg==$0";
drop table lpudemantra."BIN$A4gGznhiAVbgUwoVBkIBVg==$0";
.
.
.
.
drop table lpudemantra."BIN$9WCVYRl3BGbgQwoVBkIEZg==$0";

If you have not experienced the purge of the recycle bin, attempt the following:

  1) select count(*) from sys.recyclebin$;

  2) Instead of simply trying to purge the table we can use the following alternative:

     a) flashback table <table_name> to before drop;

          or

          if  <table_name> is currently used by another object:

        flashback table <table_name> to before drop rename to <new_table_name>;


     b) create a script that will drop all the partitions one by one :

        spool drop_<table_name>_partitions.sql

        select 'alter table '|| table_owner|| '.'|| table_name ||' drop partition '|| partition_name||';'
        from dba_tab_partitions
        where table_name='<table_name>'
        /

        spool off


     c) run drop_<table_name>_partitions.sql


     d) drop table <table_name>;


     e) purge recyclebin;


To turn off the recyclebin
  - alter system set recyclebin=off scope=spfile;

Wednesday Dec 17, 2014

Demantra 12.2.4.1 Has Been Released. See details below

Hello Demantra Customers!  Are you at version 12.2.4?  A new cumulative patch has been released.  This 12.2.4.1 cumulative patch is different to the 12.2.x patch strategies in the respect that it will only contain fixes that are new to 12.2.4.  

Uptake of VCP 12.2.4.1 is mandatory for all VCP and Demantra 12.2.4 installations.

    Demantra Patch Number is 19973580.
    For E1 customers Long Item Code  Patch Number is 19608405.
    Demantra 12.2.4.1 will only work with VCP 12.2.4.1.
    Demantra 12.2.4.1 and VCP 12.2.4.1 will work with EBS 12.1.3 or EBS 12.2.4.

see MOS Note:

Demantra 12.2.4.1 Cumulative Patch. This CU is Specifically for r12.2.4.1. There are 13 Issues Patched and or Improved. (Doc ID 1952805.1)

and

Oracle Demantra Demand Planning Announcing New Release 12.2.4.1 is Now Available (Doc ID 1948684.1)

and

Demantra Release 12.2.4.1 Known Upgrade and Known Product Issues with Workarounds (Doc ID 1948769.1)

and

Oracle Value Chain Planning (VCP) Readme, Release 12.2.4.1 (Doc ID 1931390.1)

Thursday Sep 25, 2014

Demantra Integration Webcast 24-Sep-2014

Hello!   Were you able to attend the the Demantra webcast 24-Sep-2014?   The topic was integration.  You can watch a replay found at:
Demantra Solutions Advisor Webcast Calendar And Archive (Doc ID 800030.1) and scroll to Integrating Demantra With Oracle Applications.

 

Coming up:

1) 8th OCT, V2 Analyzer review and Data Mining Live Demo

2) I am thinking about breaking the 24-Sep-2014 into two presentations.  This would allow us to dig deeper into debugging the integration and workflow.  These would concentrate on data once it arrives into the denorm table AND data mapping. 

Has anyone used the e_plan_tree database object?

SQL> desc msdem.e_plan_tree
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 E_PLAN_NODE_ID                            NOT NULL NUMBER(10)
 E_PLAN_CHILD_NODE                                  NUMBER(10)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 FIELD_NAME                                NOT NULL VARCHAR2(30)
 E_PLAN_NAME                                        VARCHAR2(30)
 E_PLAN_TITLE                                       VARCHAR2(100 CHAR)
 E_PLAN_BASE_DIM                                    VARCHAR2(30)
 E_PLAN_TYPE                                        VARCHAR2(10)
 E_PLAN_DATA_TYPE                                   VARCHAR2(10)
 E_PLAN_RELATION                                    NUMBER(10)
 SOURCE_NAME                                        VARCHAR2(30)
 E_PLAN_AGGRI                                       VARCHAR2(400)
 MODEL_VERSION                             NOT NULL NUMBER(5)
 VERSION_NAME                                       VARCHAR2(30)
 FICTIVE_COLUMN_NAME                                VARCHAR2(30)
 E_PLAN_TABLE_NAME                                  VARCHAR2(30)
 HYRARCHY_NO                                        NUMBER(5)
 ENABLE_LEVEL                                       VARCHAR2(20)
 USE_EP_GEN_ID_FOR_FILTER                           NUMBER(1)
 USE_ITEM_LOC_ID                                    VARCHAR2(20)
 AGGRI_FUNC                                         VARCHAR2(400)
 WAVG_DEPENDANT                                     VARCHAR2(30)
 PARTS_VERSION                             NOT NULL NUMBER(5)
 IS_HIERARCHY_LEVEL                                 NUMBER(1)
 PARENT_FIELD_NAME                                  VARCHAR2(30)
 APPLICATION_ID                                     VARCHAR2(63)

 

Monday Aug 11, 2014

Announcing the release of Demantra 12.2.4! Fantastic new functionality!

Hello Demantra Customers!  We are excited to announce that Oracle Value Chain Planning 12.2.4 is now available for new and existing customers. This release includes Demantra 12.2.4.  Providing a wide array of new features driven by customer requirements and needs.  This release includes over 20 key features including; In-Memory Performance Driven Planning, In-Memory Consumption Driven Planning, APCC – Custom Hierarchies and Measures, APCC – End Item Pegging-based Analysis and more!

Please see My Oracle Support note, Oracle Demantra Documentation Library (Doc ID 443969.1), for complete details.

Tuesday Jul 08, 2014

Latest for 12.2.3: New DocumentDemantra 12.2.3 - Create Simular Product - Error ORA-30683 ODPM-70420, ORA-06512: in "SYS.DBMS_DEBUG_JDWP" (Doc ID 1905097.1)

Hello!  Here is the latest for Demantra 12.2.3

Demantra 12.2.3 - Create Simular Product - Error ORA-30683 ODPM-70420, ORA-06512: in "SYS.DBMS_DEBUG_JDWP" (Doc ID 1905097.1)

On :  12.2.3 version, When using the Create Similar Product Option of Demantra, it fails and produces the errors

ERROR
-----------------------
ODPM-70420: Create Similar Item process failed.
--
This error is on all items loaded in the system and reproducible by following the standard way of working with this functionality.
-- Collaboration.log files shows ORA-30683.
ORA-30683:..
ORA-12541: TNS: no listener.
ORA-06512: in "SYS.DBMS_DEBUG_JDWP", regel 68
ORA-06512: in regel 1
[Additional info: null]
java.sql.SQLException: O

Users cannot use NPI process

See MOS Note 1905097.1 for additional details.   Regards!   Jeff

Monday May 05, 2014

New Demantra Webcast 7-May-21014. This is Part II and very comprehensive!

Do you want to gain a solid understanding of Demantra Logs?  Perhaps you would like to enhance your knowledge of Demantra logs. 

The May webcast:

  • Demantra Logs by Procedure.
  • Where are They?
  • When are They Updated? 

Topics include

  • Install
  • Workflow
  • Member Management and Chaining Logs
  • Procedures and Logs, plus more! 

This webcast promises to deliver at least one Demantra log fact that you did not previously know.
Please see My Oracle Support (MOS) note 800030.1, Demantra Solutions Advisor Webcast Calendar And Archive.

Wednesday Mar 12, 2014

Troubleshooting Strategy and Processes that Execute Enormous data sets in Demantra

Hello Demantra Customers!   There is a new white paper that discusses large data set loading.

See MOS Note 1634925.1

Recently we have encountered a few scenarios where customers tried to process huge amounts of data within a Demantra process.
Examples:

  • Customer wanted to load over 50M records via EP_LOAD process on weekly bases.
  • Customer wanted to process millions of rows via BLE process on daily bases.
  • Customer wanted to load via integration interface 40M rows on weekly bases.


In all of the above the customer complained about the system inability to process the amount of data, either being too slow or it simply could not be completed at all meaning the process erred out.
In all of the above examples an escalated defect was logged, and development worked troubleshooting the problem.

An additional common theme between all the above examples and many more is that there was no real need to have a product fix although performance improvement opportunities may have been identified, they were not the final solution that addressed the problem.
What helped was understanding that there is no real need to process all the data and recognizing the data that really needed to be loaded.  The data could have been loaded in a more efficient way using some best practices and creative thinking.

The scope of this document is to provide some guidance how to troubleshoot such customer situations.
The document will focus on three main areas:

  • Learning and understanding the business needs
  • Understanding the actual data that needs to be loaded, understand the gap between this number and the number of rows that the customer actually tries to load.
  • Provide some best practices that can help the customer work with the data efficiently.


This document will not deal with the initial Data load processes although some of the best practices can be adopted for such situations as well.
We will illustrate the concepts in this document using three real life examples of service requests and/or defects that were logged on behalf of the customer.

 

Wednesday Oct 23, 2013

Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.

Hello!  Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.
This is an updated re-release of the generic Partial Schema Exporter Tool.  The generic patch is for 7.3.1.x and 12.2.x.
TABLE_REORG was introduced in 7.3.1.3 12.2.0.  Therefore for 7.3.1.x the schema must be at 7.3.1.3 or above.

This is build 3 of the patch.

It contains fixes for the following bugs
- BUG 17495971 - DEMANTRA 12.2 - CUMULATIVE HISTORY NOT CORRECT
  It now only uses DATA_PUMP COMPRESSION only on Enterprise Edition for 11g and and up.

- Bug 17452153 - 1OFF:16086475:TRYING TO FILTER DROP DOWN IN A METHOD CALL USING MORE THAN 1 ATTR
  It now builds GL level filters with and without the GL id column where applicable.

These bugs are also fixed in 7.3.1.6 and 12.2.3.

Friday Jun 21, 2013

Announcing Oracle-Demantra 12.2.1 Release

We are excited to announce Oracle Demantra 12.2.1 is now available for new and existing customers. All customers who are not incorporating Demantra with other VCP products are welcome to upgrade without any restrictions. Customers who are using Demantra in conjunction with VCP products will need to upgrade VCP to 12.2.1 which requires application and participation in Oracle E-Business Suite early adopter program.

Demantra 12.2.1 includes a wide array of new features driven by customer requirements and needs. Key features include:

·       Streamlined import and export from Microsoft Excel

·       Support Gregorian Month data aggregation in weekly system

·       Multilanguage support for eleven languages

·       Promotion Calendar Optimization

·       Enhanced integration with Advanced Planning Command Center (VCP 12.2.1 Required)

Demantra 12.2.1 will work with JD Edwards EnterpriseOne 9.1 using the AIA 11.4 for the Value Chain Planning Base Integration Pack.

Demantra 12.2.1 will only work with VCP 12.2.1.

Demantra 12.2.1 and VCP 12.2.1 will work with EBS 12.1.3 or EBS 12.2.1.

Wednesday Feb 13, 2013

Demantra Adding Partitions to SALES_DATA? Check out this 'sample' plan

Sample Action Plan to create partitions on Demantra Sales_data table.  This has been used recently and was successful.  USE at your own RISK after careful study and adjustment to meet your needs.  This was produced to use in an 11gr2 RDBMS environment.   

I will be providing a table_reorg action plan, available 7.3.1.3 and later soon for partitioned and non-partitioned tables.  These can be used to replace MyOracleSupport (MOS) note 1085012.1.

Ping me with any questions/comments.   Regards!   Jeff

conn / as sysdba
Run utlrp.sql / dbms_recomp  Recompile all the objects.
set pages 1000
set lines 300
spool demantra_object_status.lst
spool object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by 2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
spool off

1) EXP SALES_DATA TABLE

expdp Demantra/<PASSWD> table_name=SALES_DATA DIRECTORY=exportdir DUMPFILE=exptrans%U.dmp PARALLEL=3

2)Adding size to existing Tablespace to create the Partitions for NEW SALES_DATA
==================================================================================
Add 50GB of datafile space to  TABLESPACE "TS_SALES_DATA"

-Eg: Alter tablespace TS_SALES_DATA add datafile '<Location>/file_name' size n;

3) Creating  partition table for SALES_DATA and loading data into new table
===========================================================================
CREATE TABLE DEMANTRA.SALES_DATA_PART
  ((    "ITEM_ID" NUMBER(10,0) NOT NULL ENABLE,
    "LOCATION_ID" NUMBER(10,0) NOT NULL ENABLE,
    "SALES_DATE" DATE NOT NULL ENABLE,
    "ITEM_PRICE" NUMBER(20,10),
    "SYNCRO_SIG" NUMBER(1,0) DEFAULT -1,
    "LAST_UPDATE_DATE" DATE DEFAULT SYSTIMESTAMP,
.
.
.
    "TOTALDISTRIBUCION" NUMBER(20,10),
    "BUDGET_VALOR" NUMBER(20,10),
    "PRON_PLDEM" NUMBER(20,10),
    "PRECIO_FUTURO" NUMBER(20,10),
    CONSTRAINT "SALES_DATA_PK" PRIMARY KEY ("ITEM_ID", "LOCATION_ID","SALES_DATE")
              USING INDEX PCTFREE 30 INITRANS 10 MAXTRANS 255
              TABLESPACE "TS_SALES_DATA_X" ENABLE)
         SEGMENT CREATION IMMEDIATE
   PARTITION BY RANGE (SALES_DATE)
PARTITION SALES_DATA_042010 VALUES LESS THAN (TO_DATE('01-May-2010','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA1
PARTITION SALES_DATA_052010 VALUES LESS THAN (TO_DATE('01-JUN-2010','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA2
.
.
.
PARTITION SALES_DATA_112014 VALUES LESS THAN (TO_DATE('01-DEC-2014','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA15
PARTITION SALES_DATA_122014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
  TABLESPACE TS_SALES_DATA16
as select * from demantra.sales_data
   PCTFREE 30 PCTUSED 60 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING PARALLEL 8
   STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
   FREELISTS 5 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
   );
 
 
3) Drop  SALES_DATA cascade / Please check the Export first step is completed and moved the dump to safe place.
=============================================================================================================================
Drop table DEMANTRA.SALES_DATA cascade;

5) rename new  Table name to SALES_DATA
===================================
      alter table SALES_DATA_PART
       rename to
       SALES_DATA;

5.1) GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON  SALES_DATA TO DEMANTRA;

Run utlrp.sql / dbms_recomp
spool Invalid_objects_after_Partition.lst
select object_name,object_type,owner,status from dba_objects where status='INVALID'
spool off
   
       
6) create dependencies for SALES_DATA After Renaming table back to Sales_DATA
==============================================================================
IMP: Try to Recompile all the objects so see all the dependent objects are valid (If not use the below scripts to create or replace the objects)

- Recreate the indexes on Sales_Data Table (Local)
i) Script to create the indexes on Sales_data table is sales_data_index_ddl.txt

spool index_sales_data.lst
select index_name,table_name,status from dba_indexes where table_name='SALES_DATA';
Spool off

- Create the Synonym
i) Script to create the synonym sales_synonym_ddl.txt

- Create Triggers on Sales_Data
i) Script to create triggers is sales_trigger_ddl.txt

IMP: This step have to do only if Step 5:- Recompile output shows Any demantra packages are invalid
- Try to recompile the Procedures/Packages of SALES_DATA If this does not work then recreate the procedures and packages
i) Script to use to recreate package is Package_body_Sales_Data.zip
ii) Scripts to use to recreate the Package body is Package_body_Sales_Data.zip

- Create Materialized View on Sales_data
i) BIEO_EXPORT_1_JUNTA_DEMAND.MV
ii) BIEO_EXPORT_2_JUNTA_DEMAND.MV

7)execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname => 'SALES_DATA');

7.1)  collect stats on SALES_DATA

use this:

BEGIN    DBMS_STATS.GATHER_TABLE_STATS (      ownname          =>  '"DEMANTRA"',  ESTIMATE=>30 ,    tabname          =>  '"SALES_DATA"',      granularity      =>  'GLOBAL AND PARTITION',      cascade          =>  TRUE,      no_invalidate    =>  FALSE    ); END; /

Proof: 30% gathered.
Select table_name, last_analyzed,sample_size from user_tables;


spool index_sales_data.lst
select index_name,table_name,status from dba_indexes where table_name='SALES_DATA';
Spool off

i) Run utlrp.sql / dbms_recomp

ii) Please collect this data information:-
==============================================================
spool Demn_obj_status_after.lst
spool object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by 2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
spool off

8) Test eveything from application side

Wednesday Dec 19, 2012

Un-used Indexes on MDP_MATRIX Consuming Resources

Disable un-used Indexes:

As much as it is recommended to create relevant indexes, it is advised not to have too many indexes on the mdp_matrix table.  Too many indexes will cause long waits on the table as indexes needs to get updated every time the table is updated.  There are many seeded indexes on mdp_matrix, every out of the box data model level has an index on the matrix table.  If a level is unused in the specific data model of the implementation, it is advisable to disable that index.  If the customer is not sure if and how indexes are utilized, the DBA can monitor all indexes.  After a few cycles of operation, the DBA should go over that list and see which indexes have not been used.  Consider disabling them.

There are scripts on the net to monitor indexes or use the monitoring usage clause in the alter index statement.

Thursday Sep 06, 2012

Using DB_PARAMS to Tune the EP_LOAD_SALES Performance

The DB_PARAMS table can be used to tune the EP_LOAD_SALES performance.  The
AWR report supplied shows 16 CPUs so I imaging that you can run with 8 or more
parallel threads.  This can be done by setting the following DB_PARAMS
parameters.  Note that most of parameter changes are just changing a 2 or 4 into an 8:

DBHintEp_Load_SalesUseParallel = TRUE
DBHintEp_Load_SalesUseParallelDML = TRUE
DBHintEp_Load_SalesInsertErr = + parallel(@T_SRC_SALES@ 8) full(@T_SRC_SALES@)
DBHintEp_Load_SalesInsertLd  = + parallel(@T_SRC_SALES@ 8)
DBHintEp_Load_SalesMergeSALES_DATA = + parallel(@T_SRC_SALES_LD@ 8) full(@T_SRC_SALES_LD@)
DBHintMdp_AddUpdateIs_Fictive0SD = + parallel(s 8 )
DBHintMdp_AddUpdateIs_Fictive2SD = + parallel(s 8 )

Tuesday Jul 10, 2012

Data Loading Issues? Try the new Demantra Data Load Guided Resolution

Hello!   Do you have data loading issues?  Perhaps you are trying the new partial schema export tool.  

New to Demantra, the Data Load Guided Resolution, document 1461899.1.  This interactive guide will help you locate known solutions to previously discovered issues quickly.  From performance, ORA and ODPM errors to collections related issues that have no known hard number error.   This guide includes the diagnosis of data being imported into Demantra and data being exported from Demantra. 

Contact me with any questions or suggestions.   Thank You!

Tuesday Jun 26, 2012

Latest EP_LOAD_SALES Performance Improvement for 7.3.1.4 and 12.2

Hello!   Take a look at the latest EP_LOAD_SALES solution.  New for 7.3.1.4 and 12.2, there are 3 additional parameters to control EP_LOAD_SALES.    Click Here

Thursday Jun 14, 2012

Java JRE 7 Automatic Upgrade and Demantra Requirements - Action Required

The following applies to ALL Demantra, EBS and Demantra Oracle Integrations:

All EBS desktop administrators must disable JRE Auto-Update for their end-users immediately.
See this externally-published article:

    URGENT BULLETIN: Disable JRE Auto-Update for All E-Business Suite End-Users
    https://blogs.oracle.com/stevenChan/entry/bulletin_disable_jre_auto_update

Why is this required?

If you have Auto-Update enabled, your JRE 1.6 version will be updated to JRE 7.

    This may happen as early as July 3, 2012.
    This will definitely happen after Sept. 7, 2012, after the release of 1.6.0_35 (6u35). 

Oracle Forms is not compatible with JRE 7 yet.  JRE 7 has not been certified with Oracle E-Business Suite yet.
Oracle E-Business Suite functionality based on Forms -- e.g. Financials -- will stop working if you upgrade to JRE 7.

Related News

Java 1.6.0_33 is certified with Oracle E-Business Suite.  See this externally-published article:

    Java JRE 1.6.0_33 Certified with Oracle E-Business Suite
    https://blogs.oracle.com/stevenChan/entry/jre_1_6_0_33

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« April 2015
SunMonTueWedThuFriSat
   
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today