6.5.1 NVA Generation Slow in a 12.2.5 Oracle Applications Planning Instance (ASCP), Needing Fixed Object and Data Dictionary Stats Gathered

We have our 12.2.5 Oracle Applications ERP instances and then we have our 12.2.5 Oracle Applications ASCP, the planning module, instances. 

With the 6.4.1 NVA regeneration process associated with our planning module, my NVA regeneration process (stage 2 through 4) would usually take about 15 to 20 minutes.

With 6.5.1 NVA, my regenerations were taking about 1 hour.

While watching my output from SQL*Plus (used for my NVA regeneration process), I see this text, "Default role prefixes to Oracle user names", which is from xgenall.sql script. It stays at this output line for about 1/2 hour or so.

When I look at the most recent records in v$sql, I see that w_process_user_flag.sql is being ran.


Thinking that there might be a locking issue (seen this before), I queried for the existing locked objects:

SELECT
    a.os_user_name,
    a.oracle_username,
    a.object_id,
    c.object_name,
    c.object_type
FROM
    v$locked_object a
    JOIN
        dba_objects c
    ON
        a.object_id = c.object_id;

After reviewing the lack of locks associated with the NVA as a possible source of impediments, I raised a ticket with Magnitude Software.


Their proposal was to execute running statistics for a special group of objects. Specifically, they suggested to have statistics gathered for fixed objects and data dictionary objects:



exec dbms_stats.gather_fixed_objects_stats();
exec dbms_stats.gather_dictionary_stats;



After this was executed by our dba, the performance reverted back to 15 to 20 minutes to refresh.


From a dba perspective, these objects just are not gathered very often (comes down to need). With us migrating to 12.2.5 Oracle Applications in July of 2016, many things change in the database so you would anticipate that these object could benefit from a gathering of statistics.

Monitoring Edition Based Redefinition in a 12.2.5 Oracle Applications Environment

We migrated from a NoetixViews 6.4.1 to a NoetixViews 6.5.1 environment concurrently with our Oracle Applications 12.1.3 to 12.2.5 migration.  There were many, many changes that had to be made with customizations (referencing of objects in custom NoetixViews) 

Also, the 12.2.5 environment relies on a edition based redefinition model for code moves/patching which is a dramatic departure from previous approaches.  The edition based redefinition approach allows for online patching which is in contrast to the outage dependent NVA regeneration process which Magnitude advocates be ran in the runtime edition of the database.  

One (of many approaches) to be aware of the patching process is to monitor editions associated with the database instance.

This query will provide a hierarchical perspective of the editions associated with a database instance with the last level being the current edition (aka runtime edition):

SELECT level,

de.edition_name,

de.parent_edition_name,

de.usable

FROM dba_editions de

START WITH de.edition_name = ‘ORA$BASE’

CONNECT BY PRIOR de.edition_name = de.parent_edition_name

ORDER BY de.edition_name

Here is an example of this query being ran using apps in a development instance.

apps@dev>SELECT level,

2 de.edition_name,

3 de.parent_edition_name,

4 de.usable

5 FROM dba_editions de

6 START WITH de.edition_name = ‘ORA$BASE’

7 CONNECT BY PRIOR de.edition_name = de.parent_edition_name

8 ORDER BY de.edition_name

9 /

LEVEL     EDITION_NAME     PARENT_EDITION_NAME     USABLE

—————————————————————————————————

1               ORA$BASE                                                             YES

2               V_20160703_2120   ORA$BASE                             YES

3               V_20160725_2132   V_20160703_2120                   YES

Level 3 displays to the current runtime edition.

Applies To:   Patching and code move process

Lessons Learned from Migrating to the 6.5.1 NoetixViews in an Oracle Applications 12.2 Environment

I received a copy of the 6.5.1 NoetixViews back in late March of this year. We needed to be on a version of the NoetixViews which will be certified with 12.2.5.

Right now we have completed our 2nd round of UAT and various things are still popping up.

Is there a big lesson learned from this migration?

We have lots of customizations (close to 200) with a lot of custom objects referenced. What I noticed is that my custom scripts (xu2 and xu5) that referenced custom objects needed to be properly referenced in the template tables (where applicable).

My convention up until the 6.5.1 NoetixViews with 12.2.5 Oracle Apps was to create a synonym in APPS and reference the object in n_view_table_templates through this synonym.  With 6.5.1, I just confirmed that the custom schema source was properly registered as an Oracle Application and then referenced the table directly from its schema.

This was my greatest pain point.

The different objects that are now associated with an Oracle Apps table (even a custom table owned by a registered schema) have ancillary objects as part of the edition based redefinition construct which is implemented with Oracle Applications 12.2.

Specifically, each table will have an edition based redefintion view owned in its schema and an APPS owned synonym.

Once the views are instantiated in the 12.2 EBS environment, they will then point at the edition based redefinition view associated with the table. This is true of both standard Oracle Apps tables and custom tables in registered Oracle Applications schemas.

With these new objects, the NVA will expect the Noetix admin database account to have grant select with grant option for all of the custom objects referenced in n_view_templates that are associated with the custom tables.


Last year I performed a 6.0.1 to 6.4.1 NoetixViews migration and this year I performed a 6.4.1 to a 6.5.1 NoetixView migration.  Because we are also upgrading Oracle Apps from 12.1.3 to 12.2.5 in this migration, personally, this was more work (and more risk).

Digression: Oracle Apps Descriptive Flexfield Query with Assigned Flexfield Value Sets

Much has happened since my last entry.
-completed a 6.0.1 to 6.4.1 Noetix View migration
-now have global views for everything in our Oracle Apps stack
-in UAT for a 6.4.1 to 6.5.1 Noetix View migration (migrating from 12.1.3 to 12.2.5 Oracle Apps concurrently)
-purchased Noetix Workbench

Many lessons have been learned.  Many tickets submitted to Magnitude. With the tickets to Magnitude, I had an opportunity to work with various levels of support (front line staff who were very helpful to senior engineers).

I will have to write about these details at some point.

Today, I have a reporting requirement pertaining to descriptive flexfields where the compiled usage is a date with a certain mask (no value set assigned though). 

Usually, I have created xu5 scripts to cast these dates as dates (though it is important to know the mask used).

Anyways, I did not have a DFF query with flex value sets associated, nor could I find one so here is my ANSI special query (I practice ANSI standard for practice and fun):

SELECT fcu.*,
  fcv.*,
 ffvs.*
FROM applsys.fnd_descr_flex_contexts fcv
JOIN applsys.fnd_application_tl A
ON fcv.application_id = A.application_id
AND A.language        = 'US'
JOIN applsys.fnd_descr_flex_column_usages fcu
ON fcv.application_id                 = fcu.application_id
AND fcv.descriptive_flexfield_name    = fcu.descriptive_flexfield_name
AND fcv.descriptive_flex_context_code = fcu.descriptive_flex_context_code
JOIN applsys.fnd_descr_flex_col_usage_tl fcut
ON fcu.application_id                 = fcut.application_id
AND fcu.descriptive_flexfield_name    = fcut.descriptive_flexfield_name
AND fcu.descriptive_flex_context_code = fcut.descriptive_flex_context_code
AND fcu.application_column_name       = fcut.application_column_name
AND fcut.language                     = 'US'
LEFT OUTER JOIN applsys.fnd_flex_value_sets ffvs
ON ffvs.flex_value_set_id = fcu.flex_value_set_id
WHERE A.application_name LIKE :application_name
  ||'%'
AND fcu.descriptive_flexfield_name LIKE '%'
  || :dff_name_eg_OE_LINE_ATTRIBUTES
  || '%'
AND fcut.form_left_prompt LIKE '%'
  || :dff_form_left_prompt
  ||'%'
ORDER BY fcut.descriptive_flexfield_name,
  fcut.application_column_name ;

Noetix 6.0.1 to 6.4.1 Noetix Views Migration ~ Running a Seeded Regeneration

I have been busy working on my Noetix 6.0.1 to 6.4.1 Noetix Views migration and I am supposed to crank through this project pretty quickly. I have been setting up my test environment.  My approach to setting-up my 6.4.1 Noetix View schema is as follows:

• Run a 6.4.1 Noetix Views regeneration “out of the box”on an existing Noetix schema (6.0.1 to be exact).
• Run a 6.4.1 Noetix Views regeneration with all my xu2 hook scripts.
• Run a 6.4.1 Noetix Views regeneration with all my xu5 hook scripts.

With the “out of the box” regeneration, I noticed that popview.sql and ycrcal.sql appear to have bugs (Magnitude supplied me with a corrected popview.sql script).

Here is the error for the ycrcal.sql:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 ( l_effective_date,
20 TRIM(to_char(l_effective_date,'MONTH')),
21 TRIM(to_char(l_effective_date,'DAY')),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'WW')),
24 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'Q')),
25 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'J')),
26 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'MM')),
27 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'W')),
28 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'DD')),
29 to_number(to_char(to_date(l_effective_date,'DD-MON-YY'),'D')));
30 l_effective_date := l_effective_date + 1;
31
32 exit when l_effective_date = to_date('01-JAN-2100','DD-MON-YYYY') ;
33 end loop;
34 commit;
35 end;
36 /
declare
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 6

This install script is taking l_effective_date, which is a date and casting it as date (I have not seen that done successfully nor is this a great idea).

Here is a reproduction of that error in a more trivial example:

NOETIX_SYS@erp>select to_date(sysdate,'dd-mon-yyyy')
2 from
3 dual;
select to_date(sysdate,'dd-mon-yyyy')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

When I correct this casting of a date as a date issue, the script looks like this:

6 insert into noetix_calendar
7 ( effective_date,
8 month,
9 day,
10 year,
11 week_of_year,
12 quarter,
13 julian_day,
14 month_of_year,
15 week_of_month,
16 day_of_month,
17 day_of_week )
18 values
19 (l_effective_date,
20 to_char(l_effective_date,'MONTH'),
21 to_char(l_effective_date,'DAY'),
22 to_number(to_char(l_effective_date,'YYYY')),
23 to_number(to_char(l_effective_date,'WW')),
24 to_number(to_char(l_effective_date,'Q')),
25 to_number(to_char(l_effective_date,'J')),
26 to_number(to_char(l_effective_date,'MM')),
27 to_number(to_char(l_effective_date,'W')),
28 to_number(to_char(l_effective_date,'DD')),
29 to_number(to_char(l_effective_date,'D')));

Presently, I am waiting for Magnitude to acknowledge the problem and fix it.

Capturing Errors in XU2 Scripts Early in Stage 4 of a View Regeneration Using MS PowerShell

--This is a re-post

I posted on this previously and documented a MS DOS command to find errors in my XU2 scripts (here).The xu2 scripts are invoked during stage 4 of the Noetix View Administrator's regeneration process.  This is right after SQL Loader is finished loading seeded template table records. The errors that are thrown during this part of the regeneration are typically associated with constraint errors.  If you examine the constraints that are associated with a given Noetix template table, one can see that they are quite robustly written to help make sure that you do not add a record in an erroneous way.

One can typically examine the root cause of these errors if one looks at this query:

SELECT *
  FROM DBA_CONS_COLUMNS
 WHERE 1          = 1
   AND OWNER      = 'NOETIX_SYS'
   AND TABLE_NAME = 'N_VIEW_COLUMN_TEMPLATES';


Today I wanted to document another way to find these errors.  More than a year ago, I noticed that Microsoft had an updated shell, called PowerShell, and so I placed it on my laptop and did not do anything with it. I noticed that it was quite different from MS Dos and I looked at it for a little bit and talked to some peers.  My peers heard of it, but thought that it really had gained little traction in terms of use by administrators and other IT staff. For one reason or another, I stumbled on the blog, http://blogs.technet.com/b/heyscriptingguy/, and it really made me reconsider using this shell environment in lieu of MS DOS where practical.

Anyway, the two main commands one needs to know in this shell are as follows:

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Get-Alias  MS_DOS_Command

Notes on this:  This cmdlet just helps you translate your MS DOS command to the PowerShell cmdlet.

Here is an example:

PS C:\WINDOWS\system32\windowspowershell\v1.0> Get-Alias cd

This returns:

CommandType     Name                                                                                           Definition                                                                                    

-----------     ----                                                                                           ----------                                                                                    

Alias           cd                                                                                             Set-Location         

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The other command that is necessary is the following:

Get-Help cmdlet

Today, I will go through how one can invoke a command (well, actually a cmdlet) to find Oracle thrown errors that occur while the Noetix View Administrator is in stage 4 of a regeneration.


First, a little background information on PowerShell.

It is rather nice because it is object oriented and its piping is similar to Unix/Linux.  Specifically, the piping can return objects and then the next cmdlet (that is what they call their commands in this new shell) manipulates it, one object at a time through the pipe kind of like an embedded function.  This is very dissimilar to MS DOS where it completes the first command and then pushes the result set to the next command.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Anyways, I use this shell as my main shell that I use for various tasks.  Here is my cmdlet to watch for errors being thrown right before GUI prompt for the APPS password (which is right after the wnoetxu2.sql script has run):

Get-ChildItem -Path "\\cmntx02\NoetixViews 6.0.1 - ERP\Installs\NOETIX_SYS_erpdev" -Filter "*xu2.lst" | Select-String -pattern "ORA-"

Explanation: 

  

  The Get-ChildItem cmdlet gets the items in one or more specified locations. In this context it obtains all the xu2.lst spooled files and pipes the results to the Select-String cmdlet which finds matches to the pattern, "ORA-".

I invoke this script on my laptop and it peruses the files in the path I have identified.  I suppose you might take a look at this script and think; I will never type a path that long!  Well, if you are not aware, you can drag a file from MS explorer to your shell environment and Windows will “type it in” for you.

                                                                       

Administration Digression: My Favorite Kind of New Oracle Function, LISTAGG

With the administration of my Discoverer / Noetix environment, I find that sometimes I need to compose comma separated concatenated list of some text column (e.g. for the purpose of creating documentation).

In Oracle Database 11g release 2, the LISTAGG function was introduced which is well suited for this type of task (and no custom function needs to be created).


This function has two flavors, analytic (non-aggregate) and aggregate.Here is a description of the function (see link above for full description in 11g R2):


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

-expression is the expression one wishes to concatenate
-delimeter is self-explanatory (e.g. ',')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The analytic flavor is invoked as follows:

LISTAGG ( expression, [delimeter] ) WITHIN GROUP (ORDER BY  order_by_expression_list )

 [OVER (PARTITION BY partition_columns)]


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


With some documentation I am creating, I need to create a concatenated list of Discoverer responsibiities.


Here is my invocation of this function using the aggregate version of this function:


 EUL5_US@erpdev> SELECT LISTAGG(responsibility_name, ', ') WITHIN GROUP (
  2  ORDER BY responsibility_name) Responsibility_list
  3  FROM applsys.fnd_responsibility_tl
  4  WHERE 1      =1
  5  AND language = SYS_CONTEXT ('USERENV', 'LANG')
  6  AND regexp_like( responsibility_name, 'Discoverer.+ - \w{2}')
  7  /

Responsibility_list
------------------------------------------------------------------------
Discoverer Commissions - CS, Discoverer Viewer - CA, Discoverer Viewer - CM, Discoverer Viewer - CS, Discoverer Viewer - CW