Feed aggregator

Going To New Orleans - ODTUG Kalaidoscope 2008

Susan Duncan - Fri, 2008-06-06 07:36
I've traveled and spoken on Oracle all around the world at many events but this year will be my first at ODTUG (June 15th-19th). I'm looking forward to both the technical and the non-technical aspects of the conference. New Orleans is somewhere that I haven't visited in 15 years and I'm happy to be one of the nearly-75 strong ODTUG Brigade volunteering for a day of community service work to give back to the city that has given so much to music lovers and so many others like me.

The conference is packed with keynotes and sessions, I'm going to be presenting two:
Who Moved My Code? - Team Development in Oracle JDeveloper on Wednesday 8.00-9.00am
Seven Secrets (and more) of Successful JDeveloper Database Designers on Wednesday 2.45-3.45pm

Both of these will be predominantly demo driven sessions. In the first Lynn Munsinger will be joining me so we can demo multi-developer tips and tricks using Subversion. The Seven Secrets will focus on existing and new features for database development and visualization for application developers. I'm hoping also to squeeze in a sneak preview of a project I'm working on around Application Lifecycle Management. Please join me if you are at ODTUG as I would welcome your feedback.

Our Usability Research Team is running some feedback sessions that Lynn, Grant Ronald and myself will be attending, be sure and sign up for one of those. Plus, if you want to talk to us about any aspect of JDeveloper we will be in the exhibit halls ready and willing to demo and discuss.

Finally, I hear there is a ODTUG Jam Session and I'm pretty sure I wont be able to resist!

New Best Practices Paper for Subversion and JDeveloper

Susan Duncan - Fri, 2008-06-06 07:32
I've finally got around to pulling together some of the information and best practices that I've blogged about into a document. You can view it here on Oracle Technology Network. Let me know what you think

The insidious effects of Productivity claims

Project Directions - Thu, 2008-06-05 12:14

Interesting little tidbit in CIO Insight this week about productivity.  Parallax View – IT Spending – IT: A Bright Spot in an Economic Slowdown.

There’s nothing wrong really with trying to see the silver lining in an economic slowdown (ie. that workers are increasing their productivity while the economy slows), but I think we need to see beyond the short term effects of this productivity surge as they call it.

The writer’s feel that businesses haven’t been investing as heavily as they did in in the 1990’s and I agree with that.  I cut my teeth in the business world during the go-go days when everyone was installing ERP systems to head of the doomsday scenario that Y2K was sure to bring.  I felt fortunate to be in consulting those years because it was lucrative but I really should have been in sales.  I think you just had to sit by your phone for a few years in the late 90’s and fortunes rained down upon you.

Back to today’s reality however, I think the game has changed significantly.  The low hanging fruit is gone.  What major or even medium sized company doesn’t have an ERP system already?  There’s even plentiful solutions for the SMB market.  Where to invest the IT dollars isn’t as much of a no-brainier anymore but I think the returns to be had can be significant if you’re actually aligning your IT projects to your business needs.

Anyway, I started out talking about productivity and what’s left unsaid behind the numbers.  I tend to think that productivity is going to go up during recessions because companies force less people to do the same work that was done by more.  Whether companies actively reduce their headcount or let it happen by attrition it’s natural that the ones left behind will be obliged to pick up the slack.  You know they want to do it because the alternative is looking for a new job when there are plenty of others doing the same and businesses aren’t hiring.  I think this is, at best, a very short term solution.  I say we should look for productivity to increase going into the downturn but start to lag just like everything else.  Despite fearing for their jobs, people still get burnt out.  They will start to spend more time complaining about the way things are versus how they used to be.  They might still stay at work until 9pm and come in at 7am but the output during those hours just is not going to be sustainable over time.

One of my favorite sites, iTulip, posted this great article on the insidious unseen effects of Inflation.  Although the topic of the article is inflation, I think you can draw a great parallel between that and what happens during an economic slowdown.  In the iTulip article they used a restaurant as the business to illustrate how inflation changes the way they operate.  You can read between the lines though and see that all companies could react the same way – all businesses have people and they have inputs to their end product.   A restaurant or company cutting back can cut their fixed expenses by reducing the number of people, or they can try to lower unit costs by substituting cheaper products or providing less quantity for the price.  While having less shrimp in your pad Thai is a bad thing, I’m more focused here on what the effect is on the employees left to hold it together.

The quote from the article hits it right on the head:

Management tries to lower fixed expenses (versus per plate of food unit costs) by reducing staff. Customers experience this as slowness and crankiness among the remaining overburdened wait staff.  If your wait person is cranky and unresponsive, count how many tables they are covering before passing judgment. These days it’s probably too many.

The morale of their article is that well run, properly capitalized businesses that have a lot of cash can use these recessions to steal the customers from their weaker competitor’s.  The key is to not do the things that are forcing your competitor’s to go out of business.  Don’t raise your prices as much, don’t begin to skimp on the quality of the products you offer and most importantly, don’t let the service you provide slip because you’ve decided to rest all the work of many on the shoulders of less.

Bad things can happen when you push people to the point of breaking…


Vote for APEX!

Carl Backstrom - Thu, 2008-06-05 11:45
If you didn't know this year at Oracle World there have been a number of slots saved for community suggested and voted on topics. In the last couple days two APEX ones have been posted that look very interesting.

Rapid Development and Validation of ApEx Applications at an FDA Regulated Medical Device Company
and
(Re)Developing a logistic application in APEX in the real world.


You probably have to register to vote but lets get those APEX number up!

Vrroom! Go (Con)Figure : Speeding up the model preloading in configurator...

Gaurav Verma - Thu, 2008-06-05 07:39

What got triggered as a human mistake in a cloned instance at a customer environment, caused a new discovery by the onsite senior DBA, Brian Fane

Someone from the customer released a sysadmin custom job that caused the configurator servlet JVMs in Production to get bounced! This was an unexpected situation and while the users sat twiddling their thumbs while the configurator models were pre-loading, Brian started digging into the current executing sql of the DB sessions doing the preloading.

To give an idea of the patch levels, the CZ patchset installed was  11i.CZ.I(2690432) and the FND & ATG patchsets were 11i.FND.H(3262159) and 11i.ATG_PF.H.5(5473858) respectively.

He happened to note that the maximum time seemed to be taken by the following sql:

SELECT  cz_lce_load_specs.lce_header_id,
        cz_lce_texts.lce_text,
        cz_lce_headers.component_id
    FROM apps.cz_lce_load_specs,
         apps.cz_lce_headers,
         apps.cz_lce_texts
   WHERE cz_lce_load_specs.attachment_comp_id = :1
     AND cz_lce_load_specs.net_type = :2
     AND cz_lce_texts.lce_header_id = cz_lce_load_specs.lce_header_id
     AND cz_lce_texts.lce_header_id = cz_lce_headers.lce_header_id
     AND cz_lce_headers.deleted_flag = :3
     AND cz_lce_load_specs.deleted_flag = :4
ORDER BY cz_lce_texts.seq_nbr;

When he ran it a BCV copy of the PROD (which was a replica of PROD as of that morning - daily process), it did ~37,400 gets/execution, and almost all of them were physical reads. Most of this comes from a full table scan on CZ_LCE_LOAD_SPECS, which right around 37,150 blocks. Query time was between 1 and 2 seconds (TOAD doesn't get any more precise when dealing with values > 1 second).
His Solution... So well, he thought, why not add an index to cz_lce_load_specs that may speed up the query:

CREATE INDEX cz.blf_test ON cz.cz_lce_load_specs
       (attachment_comp_id, net_type, deleted_flag)
       COMPUTE STATISTICS;

This reduced the gets from 37,400 to 20. Execution dropped to ~10 ms.

Some more data points... Let's try some testing in DEV and see how this index performs in the wild :)

DECLARE
   CURSOR c_driver IS
      SELECT attachment_comp_id, net_type
        FROM apps.cz_lce_load_specs
       WHERE deleted_flag = '0'
         AND ROWNUM < 100;

   counter NUMBER := 0;
   v4 varchar2(10) := '0';
   v1 number;
   v2 varchar2(2000);
   v3 number;
BEGIN
   FOR r_driver IN c_driver LOOP
      BEGIN
          SELECT   cz_lce_load_specs.lce_header_id,
                   cz_lce_texts.lce_text,
                   cz_lce_headers.component_id
            INTO v1, v2, v3
            FROM apps.cz_lce_load_specs,
                 apps.cz_lce_headers,
                 apps.cz_lce_texts
           WHERE cz_lce_load_specs.attachment_comp_id = r_driver.attachment_comp_id
             AND cz_lce_load_specs.net_type = r_driver.net_type
             AND cz_lce_texts.lce_header_id = cz_lce_load_specs.lce_header_id
             AND cz_lce_texts.lce_header_id = cz_lce_headers.lce_header_id
             AND cz_lce_headers.deleted_flag = v4
             AND cz_lce_load_specs.deleted_flag = v4
           ORDER BY cz_lce_texts.seq_nbr;
           EXCEPTION
             WHEN others THEN NULL;
      END;
   END LOOP;
END;
/
Observation data for without the index creation on cz_lce_load_specs: 1) First passive configurator model loading (preloading):

Web response
time

Buffer  gets

Disk reads

Executions

Sql time
for execution

Buffer gets/
execution

2:14.6

2,403,145

35,551

68

110.45

35,340

2:02.0

2,367,735

0

67

94.66

35,340

2:03.3

2,367,735

0

68

94.7

35,340


2) The models are already preloaded now and they are being launched:

Web response
time

Buffer  gets

Disk reads

Executions

0:05.6

0

0

0

0:06.4

0

0

0

0:05.5

0

0

0


3) Launch a configurator model, change an attribute of a selection for an item or sub-item and save the configuration (do not submit an order yet):

Web response
time

Buffer  gets

Disk reads

Executions

Sql time
for execution

Buffer gets/
execution

0:30.1

600,259

3

17

23.76

35,309

0:05.9

0

0

0



0:04.3

0

0

0




Observation data for WITH the index creation on cz_lce_load_specs:
1) First passive load of the configurator model (preloading):

Web response
time

Buffer  gets

Disk reads

Executions

Sql time
for execution

Buffer gets/
execution

0:22.7

3,240

3

67

0.03

48

0:23.1

3,141

0

67

0.04

47

0:26.1

3,141

0

67

0.02

47


2) First WebUI launch, after the preloading with the index:

Web response
time

Buffer  gets

Disk reads

Executions

0.04.3

0

0

0

0.04.4

0

0

0

0.04.8

0

0

0


3) Launch a configurator model, change an attribute of a selection for an item or sub-item and save the configuration (do not submit an order yet):

Web response
time

Buffer  gets

Disk reads

Executions

Sql time
for execution

Buffer gets/
execution

0.05.1

209

0

17

0.01

12

0.04.8

0

0

0



0.04.0

0

0

0




Real time benefits.. To illustrate this point better after putting it in production, here is a quick comparision of the preload timings of a some configurator models that were grouped in 2 configurator servlets JVMs:

Before the index:

GROUP29
Max preloading time was 857 secs taken by M91R
 Min preloading time was 852 secs taken by M91
 Avg preloading time was 0:14:13 ( 853 seconds ) secs/model over a total of 6 models
GROUP30
Max preloading time was 1684 secs taken by TDXSR-CG
Min preloading time was 1302 secs taken by TDXSCSEAT
Avg preloading time was 0:25:56 ( 1556 seconds ) secs/model over a total of 13 models

After the index:

GROUP29
Max preloading time was 54 secs taken by M91R
Min preloading time was 53 secs taken by M91-C
Avg preloading time was 0:0:53 ( 53 seconds ) secs/model over a total of 6 models
GROUP30
Max preloading time was 96 secs taken by TDXSC2-CG
Min preloading time was 82 secs taken by TDXSR-HD
Avg preloading time was 0:1:30 ( 90 seconds ) secs/model over a total of 14 models

Conclusion Although this index could have been provided by Oracle, it was a lucky discovery to speeden up the preloading time for configurator models. With the new index, it is now possible for the client to bounce anytime and have 10-20 most popular models preloaded in within 2-3 mins, as compared to 10-15 mins before, which is really something.

Interestingly, the same columns got indexes in the 11.5.10 release as seen from http://etrm.oracle.com:



Note: This article is being produced with the explicit permission of Brian Fane,  and is aimed at sharing tips in the Oracle world for other people who might be in the same situation.



Join Oracle at Microsoft Tech·Ed in Orlando

Christian Shay - Thu, 2008-06-05 06:15
Oracle is once again sponsoring Microsoft Tech·Ed (happening now through June 13 in Orlando, FL). We hope to see you there!

Microsoft has split the convention into two parts this year - a Developer week (happening right now), and an IT week (next week). Oracle will be present during both weeks, with a "Birds of a Feather" (BOF) session being hosted by Alex Keh (Oracle) tomorrow, and we'll have a large presence in the Expo hall next week during the IT part of the show. If you are in Orlando right now for Developer Week, I highly recommend you drop by the .NET BOF session as they have been very lively and informative and fun in past years! For those of you attending the IT show, please drop by our demo booth to get your questions answered and see your own personal demo of Oracle technologies. We'll also have a BOF session during IT week entitled "Managing a Mix of Oracle and Microsoft SQL Server Databases"

Here are the details:

Using Oracle with .NET - Birds of a Feather Session:
Hosted by Alex Keh, Oracle
When: This Friday, June 6 10:15 am
Where: Room S330E
Check online to verify room and time
What: Discuss tips and tricks on developing .NET applications with Oracle databases on topics, such as ASP.NET providers, best practices, and comparing available providers. Learn about the latest Oracle features for Microsoft Visual Studio 2008 and the Microsoft .NET Framework 3.5. Ask Oracle and .NET experts your most pressing questions.

Expo Hall

When: IT Week (June 10-13)
Where: Oracle booth (#1408)
What: Get custom demonstrations of the latest Oracle .NET, database clustering and tools, server management, and application server technology for Windows developers and administrators. Learn about Oracle Database 11g and its integration with new Microsoft technologies, such as Visual Studio 2008 and WIndows Server 2008. Enter to win an iPhone!

BOF: Managing a Mix of Oracle and Microsoft SQL Server Databases
When: Wednesday June 11? (check schedule for date, time, and location)

Hope to see you there!!!

Product Manager at Microsoft

Chris Grillone - Tue, 2008-06-03 12:38
Hi,

I am grateful for all of your support while I was at Oracle. I am now a Product Manager at Microsoft for Windows Essential Business Server (EBS), formerly code named "Centro". EBS is scheduled to be released later this year. EBS is a server solution for mid-size businesses, 25-300 clients. Please check out my blog for EBS: http://www.microsoft.com/windowsserver/essential/default.mspx.

Cheers,

Chris

DBMS_STATS becomes INVALID after Refresh/Database Upgrade.

Madan Mohan - Mon, 2008-06-02 21:04
Issue:
******

One of the DBMS JOB failed and recorded error in the alertlog as

From ORCL database alert log file /ORCL/ORCLDB/10.2.0/admin/ORCL_ctloraerp06/bdump/alert_ORCL.log

9:ORA-12012: error on auto execute of job 282253
10:ORA-04063: ORA-04063: package body "SYS.DBMS_STATS" has errors


Observation:
************


SYS.DBMS_STATS Package Body is Invalid.

select object_name,object_type ,owner, status from dba_objects where object_name='DBMS_STATS';

OBJECT_NAME OBJECT_TYPE OWNER STATUS
-------------------- ------------------- ------------------------------ -------
DBMS_STATS PACKAGE SYS VALID
DBMS_STATS PACKAGE BODY SYS INVALID
DBMS_STATS SYNONYM PUBLIC VALID

Solution
*********


Rebuild/ Re-compile the DBMS_STATS Objects as

connect / as sysdba

@?/rdbms/admin/dbmsstat.sql
@?/rdbms/admin/prvtstas.plb
@?/rdbms/admin/prvtstat.plb

The right combination of options to trace configurator servlet..

Gaurav Verma - Sun, 2008-06-01 22:57

A long while back, we had faced a weird situation with configurator and desperately needed to trace it to find out what was happening behind the scenes.

After consulting many a metalink notes, however hard I tried, the tracing would simply not happen. The right sequence of character and options is quite tricky and not very well documented. (Not surprising, as Configurator is an acquired product)

After many attempts, the tracing did work and this article is more as a reminder to myself and any other people out there who want to trace the confiurator servlet. Be reminded though, that this tracing is more like FRD log tracing (for Oracle Forms) and is NOT sql tracing.

Having said so, here are the magical options that you need to add to $IAS_ORACLE_HOME/Apache/Jserv/etc/zone.properties file:

zone.properties
==========
....
....
servlets.default.initArgs=cz.activemodel=|/tl=@$TRACEDIR/ciotracesupport.lce| /gt=$TRACEDIR/ciotracesupport.java|/nolp|/nodp|/noatp|
....

....

where, $TRACEDIR is the directory where the trace (.lce) file will be produced. Also, please note that the /gt= option was just wrapped around to the below line for reading purposes. In reality, it needs to be in the same line as the /tl= option.

If anyone of you reading this article has a better idea than this, please let me know through the comment mechanism.


OTL Time Keeper

RameshKumar Shanmugam - Sun, 2008-06-01 18:13
There are multiple ways you can enter time into OTL

  • Self Service time - employee entering his own time
  • Line Manager time Entry - Manager Entering time for his direct reports
  • Timestore Deposit API - Time import using the interface from the third party system
  • Timekeeper - one person entering time for the group of employees based on the group assigned to them
  • Authorized Delegate - One person entering time for the group of employees based on the security profile attached in the user/responsibility
In this section I am going to explain how to setup Timekeeper module for entering the time for the Group of employees

Before starting the Timekeeper configuration , if you directly go the Responsibility OTL Super timekeeper and click the function timekeeper Entry you will get the following error msg,

The above error message will clearly tell you what are the setup steps you need to do to enable the Timekeeper

The first two steps are to be done in the OTL Application developer Responsibility
1. Timekeeper Misc Setup Items
2. Timekeeper Layout attribute

The third step should be done in the OTL super Timekeeper Responsibility
3. Timekeeper Group is created

The Forth step should be done in the System administrator, this step should be done at the user level and it is only for the super timekeeper
4 Profile OTL: Allow Change Group Timekeeper

I'll be explaining each of the above steps in detail in my next post
Categories: APPS Blogs

So you thought you upgraded your OLAP database to 10g..

Gaurav Verma - Sat, 2008-05-31 02:01


W
ell, so did we. In fact, the dba_registry view showed the version of OLAP option as 10.2.0.3. Here, see it for yourself:

  1  select comp_name , version, status from dba_registry
  2* where comp_name like  '%Analytic%'
SQL> /

COMP_NAME                  VERSION      STATUS
-------------------------- ------------ -------
OLAP Analytic Workspace    10.2.0.3.0   VALID


Seems pretty convincing, does it not? One would think so.
An unexpected error..But, a month later, when the customer tried to do an archive and restore of a demand plan, they received the following error:

Demand Planning: Version : 11.5.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights
reserved.

MSDRESTORE module: Restore demand plan

+---------------------------------------------------------------------------+

Current system time is 30-MAY-2008 08:24:19

+---------------------------------------------------------------------------+

**Starts**30-MAY-2008 08:24:19

**Ends**30-MAY-2008 08:24:21

ORA-35071: EIF file ARCH4027.eif cannot be imported because
analytic workspace ODPCODE has not been upgraded to version 10.0.0.0.

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

Error in restore process: see batch log for details

ORA-35071: EIF file ARCH4027.eif cannot be imported because
analytic workspace ODPCODE has not been upgraded to version 10.0.0.0.

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

Executing request completion options...

Finished executing request completion options.

+---------------------------------------------------------------------------+

Exceptions posted by this request:

Concurrent Request for "Restore demand plan" has
completed with error.

+---------------------------------------------------------------------------+
Metalink, our friend in need..So we turned to our friend, Metalink and came across Note 390004.1, which says that Imported the Analytic Workspace in an different database release, but the AWs are still of the old version.

Eh! So we tried to confirm the same and found that there were indeed 4 analytical workspaces that were still of 9.1 version, including the ODPCODE AW:

SQL> select aw_name , aw_version
     from all_aws
     where aw_version like '9%';

AW_NAME                        AW_V
------------------------------ ----
ZPBANNOT                       9.1
ZPBDATA                        9.1
MSD4029                        9.1
ODPCODE                        9.1


Strangely, the other AWs were all 10.2:

SQL> select aw_name , aw_version
     from all_aws
     where aw_version like '10%';

AW_NAME                        AW_V
------------------------------ ----
EXPRESS                        10.2
AWMD                           10.2
AWCREATE                       10.2
AWCREATE10G                    10.2
AWXML                          10.2
AWREPORT                       10.2
XWDEVKIT                       10.2
MMSD4027                       10.2
MSD4027                        10.2
MMMSD4027                      10.2
PMMSD4027A0                    10.2
PMMSD4027A21                   10.2
PMMSD4027A22                   10.2
PMMSD4027A26                   10.2
PMMSD4027A32                   10.2
PMMSD4027A33                   10.2
PMMSD4027A55                   10.2
PMMSD4027A57                   10.2
PMMSD4027A58                   10.2
MSD4027A58                     10.2
PMMSD4027A78                   10.2
PMMSD4027A79                   10.2
PMMSD4027A80                   10.2
PMMSD4027A86                   10.2
PMMSD4027A87                   10.2
PMMSD4027A88                   10.2
PMMSD4027A93                   10.2
PMMSD4027A95                   10.2
PMMSD4029A                     10.2
PMMSD4029A5                    10.2
PMMSD4029A3                    10.2
PMMSD4029A1                    10.2
MMMSD4029                      10.2
MMSD4029                       10.2
ZPBCODE                        10.2

35 rows selected.

SQL>

Where did our OLAP upgrade procedure go wrong..So the question before us was that how did our production upgrade procedure, the one which we thought was so meticulously planned and executed, go wrong?

For upgrading the ODP workspaces, we had followed Note 418200.1 - ODP: Steps When Upgrading the RDBMS Version Used With ODP.
We had even
got a
clarification from the author of that note about what steps were needed
for ODP
workspaces upgrade.


Unfortunately, The following note does not have any direct reference to upgrading OLAP workspaces:


Note 362203.1

Interopratbility notes Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
We had found the following Notes on OLAP upgrade/exporting/clone:

472215.1 How to Export AWs or a Plan in Demand Planning
418200.1 ODP Steps When Upgrading the RDBMS Version Used With ODP
412308.1 How to Check the Version for ODP 11.5.10?
339110.1 How To Clone the OLAP Portion of ODP 11.5.10

Note 418200.1 is the most relevant for the upgrade activity, but unfortunately, even that does not have any reference to dba_aws view to check the aw_version column of each workspace and use the dbms_aw.convert API for the workspaces that could not be upgraded due to some reason.

So that is definitely one thing that should be part of that note. We would be sending a note to the author to include these steps into note
418200.1.

Another gotcha while implementing the solution (exec dbms_aw.convert API) is that it does not accept the OWNER name of the workspace in the first argument. Note 390004.1 actually sets a false expectation on this front.

Also, you need to be logged in as the owner of the workspace while issuing the dbms_aw API or you would get this error:

SQL> show user
USER is "SYS"
SQL> exec dbms_aw.convert('ODPCODE');
BEGIN dbms_aw.convert('
ODPCODE'); END;

*
ERROR at line 1:
ORA-33262: Analytic workspace
ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 58
ORA-06512: at "SYS.DBMS_AW", line 134
ORA-06512: at "SYS.DBMS_AW", line 1245
ORA-06512: at line 1


Oh, Oh.. A Gotcha while implementing the solution..
To make matters worse, by a copy-paste, I specified the owner name along with the workspace like this:

SQL> exec dbms_aw.convert('apps.odpcode');

It came back with an error like you cant specify the owner name of the table.   

After that, the name of the aw got changed to ODPCODE_ (with the _ in the end), but it upgraded to 10.2 as per dba_aws or all_aws.

SQL> select aw_name, aw_version
     from all_aws
     where aw_name like 'ODP%';


AW_NAME                        AW_V
------------------------------ ----
ODPCODE_                       10.2


Essentially, the dbms_aw.convert had left the ODPCODE workspace in a transient state and not done a good job of cleaning up after the exception.
A workaround for cleaning up...Well, there is an API called dbms_aw.aw_rename that would do the trick of putting the name back to ODPCODE like this:

SQL> show user
USER is APPS

SQL> exec dbms_aw.aw_rename('ODPCODE_','ODPCODE');

PL/SQL procedure successfully completed.

SQL> select aw_name, aw_version from all_aws where aw_name='ODPCODE';

AW_NAME                         AW_V
------------------------------ ----
ODPCODE                        10.2

Now just to make sure that it was really 10.2, we ran the dbms_aw.convert API again and got this message, which implied that it was already in 10.2 format:

SQL> exec dbms_aw.convert('ODPCODE');
BEGIN dbms_aw.convert('ODPCODE'); END;

*
ERROR at line 1:
ORA-33298: Analytic Workspace ODPCODE is already in the newest format allowed
by the current compatibility setting
ORA-06512: at "SYS.DBMS_AW", line 1267
ORA-06512: at line 1


After this, the MSDRESTORE module: Restore demand plan concurrent job completed successfully.

Conclusion...There were multiple learnings from this Sev 1 experience in production instance:
  • Ask your customer to test exhaustively. In this case, the customer is a very small shop with an IT department of 5-6 people and yet to graduate to the enterprise level thinking for managing their IT systems.
Any testing was good testing for them and this error was discovered when they were going to go live with another project phase implementation.

Unfortunately, in this case, we had very little say in their testing plan.
  • In this case, we were caught a little off guard because we did not have exhaustive expertise in OLAP upgrades and had to rely on published metalink notes, which did not cross link all the known upgrade issues. 
In this case, it would have paid to check the output of all_aws view too. Similarly, other Oracle Server options may have their own views.
  • While executing the dbms_aw APIs, it is best to be logged in as the owner of the analytical workspace being worked on, or you might land up in a worse soup than you signed up for.
As per our observation, even the alter session set current_schema=<OWNER> sql does not work on dbms_aw APIs.


Never move house….

Lisa Dobson - Fri, 2008-05-30 15:30
….unless you are fully prepared for it to take over your life for a couple of months and push your stress levels through the roof, only for it to come crashing down round your feet on the day it’s all supposed to complete.The worst part is knowing that now that it’s back on the market, I’m going to have to go through the whole thing again in the not too distant future.Anyway, that sort of Lisahttp://www.blogger.com/profile/16434297444320005874noreply@blogger.com1

FND_GLOBAL affected by New Global Performance Changes

Aviad Elbaz - Thu, 2008-05-29 04:59

After applying ATG Rollup 5 patch (and above) we discovered an issue with some of our custom developments.
For some processes we got the following errors:

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250

and this:

ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been
detected in
fnd_global.set_nls.set_paramenters('NLS_LANGUAGE','AMERICAN').

After some debug work we found that this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.

According to Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" this issue cause by a new global performance changes.

Oracle Development said: "Very sorry if the new global performance changes have exposed you to this error, but there is no way we can back out these changes. They are not only complex and wide spread but required to maintain functional performance levels. Using fnd_global to change user/resp context from a trigger is not only not supported it is ill advised."

OK, So we had to find a workaround to this issues and we found two...

I'll start with a sample of the new behavior of fnd_global to demonstrate the issue and the solutions/workarounds will come right after.

SQL> create table test1 (a number, b number);
Table created

SQL> insert into test1 (a) values (1001);
1 row inserted

SQL> insert into test1 (a) values (1002);
1 row inserted

SQL> commit;
Commit complete

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       fnd_global.APPS_INITIALIZE(:new.a,1,1);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- ....
  9  end;
10  /
Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;

update test1 set b=1102 where a=1002

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -4092: ORA-04092: cannot SET NLS in a trigger
has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN').
has been detected in fnd_global.set_nls.
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_GLOBAL", line 240
ORA-06512: at "APPS.FND_GLOBAL", line 1410
ORA-06512: at "APPS.FND_GLOBAL", line 1655
ORA-06512: at "APPS.FND_GLOBAL", line 2170
ORA-06512: at "APPS.FND_GLOBAL", line 2312
ORA-06512: at "APPS.FND_GLOBAL", line 2250
ORA-06512: at "APPS.TEST1_TRG_BI", line 2
ORA-04088: error during execution of trigger 'APPS.TEST1_TRG_BI'

As you can see, the second update failed because apps_initialize was executed for the second time in the same transaction.

Now I'll show two ways to workaround this issue:

1) As suggested in Note: 556391.1 - "ORA-02074: Cannot SET NLS in a Distributed Transaction" a wrapper Concurrent Request which contain a call to the context set (apps_initialize) and afterwards submits the original request, is one possible solution.

instead:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     fnd_global.APPS_INITIALIZE(:new.a,1,1);
     ret_code := fnd_request.submit_request ('OWNER', 'ORIGINAL_CONC', . . .);
     . . .
     . . .
end;

create the following trigger:

create or replace trigger test1_trg_bi
after update on test1
for each row
declare
     . . .
begin
     ret_code := fnd_request.submit_request ('OWNER', 'WRAPPER_CONC', . . . , :new.a, . . . );
     . . .
     . . .
end;

additionally - create a new plsql concurrent (WRAPPER_CONC) that contains the fnd_global.apps_initialize and submits the ORIGINAL_CONC concurrent request.

This way, the apps_initialize statement executed in a separate transaction with no error.

This is the preferred and recommended solution by Oracle.

2) The second solution is easier to implement, works fine but according to Note: 556391.1 is not supported since it contains calls to fnd_global within a database trigger.

Anyway...

The idea is to call the apps_initialize in an Autonomous Transaction procedure.

Follow this sample:

SQL> create or replace procedure test1_apps_init (p_user_id number) is
  2  pragma autonomous_transaction;
  3  begin
  4       fnd_global.APPS_INITIALIZE(p_user_id,1,1);
  5       commit;
  6  end;
  7  /

Procedure created

SQL> create or replace trigger test1_trg_bi
  2  after update on test1
  3  for each row
  4  begin
  5       test1_apps_init (:new.a);
  6       -- fnd_request.submit_request...
  7       -- ....
  8       -- .....
  9  end;
10  /

Trigger created

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
        -1

SQL> update test1 set b=1101 where a=1001;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1001

SQL> update test1 set b=1102 where a=1002;
1 row updated

SQL> select fnd_global.user_id from dual; 
   USER_ID
----------
      1002

As you can see, the update statements were executed successfully this time and the session was updated with the appropriate user context in each update statement.

Those two solutions are working fine, but keep in mind that the second is not supported.

You are welcome to leave a comment.

Aviad

Categories: APPS Blogs

Site Specific Browser for EPM Applications

Oracle EPM Smart Space - Wed, 2008-05-28 11:00

So for the past few posts I have been talking about blurring the lines between desktop and web applications and the technologies that will get us there. And in my first post on this topic I mentioned a technology called 'site specific browsers'. Site specific browsers are just that, it is a browser window dedicated to one site or in the case of BI and EPM a web based application. The installed application gets a shortcut on the user's desktop or the start menu and is a simple click away from running. You can read more about site specific browsers and how Mozilla Labs describes their product 'Prism'.


So in doing some searches I found that there are 2 major options with site specific browsers, Fluid and Prism. Fluid is Mac only so for my testing I used Prism. I decided to throw a couple of BI applications at it and see what I thought of the user experience. (I ignored minor bugs or issues as this stuff is still not official product). So I put Workspace (9.3.1) and OBI (10.1.3.3.2) in Prism and played around a bit. Here are some screen shots:


Workspace with a planning form loaded.



OBI with the default Paint Dashboard.

Both applications worked pretty well out of the box and it was nice not to have all the clutter and overhead that a browser carries with it. It was also nice to have the applications launch from a shortcut on the desktop. Some of the things I didn't like were as follows:


  1. There were times where it would have been nice to have navigation (ability to go back). This is not an issue with Prism itself, it was the fact that the application was designed to live in a browser.
  2. This does not change the application experience that much. (I did not expect it to) Basically if you are happy with the application experience this will improve on it. If you hate the way the application works then this will do little.

Overall this is a very cool concept but you are simply making web applications run as if they were desktop applications. I will use Prism for things like Google Mail or Yahoo Mail but at the end of the day, from a user experience perspective, I still prefer Thunderbird or Outlook for email. It would be cool to see if an RIA (Rich Internet Application) developed in Flex or Silverlight can run in a site specific browser…

Categories: Development

ORA-12547 while creating ASM instance using DBCA in 10gR2

Madhu Thatamsetty - Wed, 2008-05-28 04:39
ORA-12547 - TNS Lost Contact while creating ASM instance using DBCA.Cause: Seems like Oracle Binaries were not relinked properly.[oracle@testsrv01 ~]$ ldd `which oracle`lddlibc4: cannot read header from `/oracle01/oracle/product/10.2.0/db_1/bin/oracle'[oracle@testsrv01 ~]$Fix: shutdown the listener and kill any stale processes referring to the executable of your ORACLE_HOME andcd $ORACLE_HOME/Madhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com0

Recommended by Joe

Mark A. Williams - Tue, 2008-05-27 18:21

A friend of mine at Oracle (that is to say Greg is still at Oracle whilst I am not) pointed out to me that Microsoft's Joe Stagner has Pro .NET Oracle Programming as a recommended book on the Oracle - ASP.NET forum. Currently the recommended books list looks like this (subject to change naturally):

joe_stagner_recommends_me

That got me to thinking a bit. It has been just over 4 years since I started writing that book. (I started the first chapter in March 2004). Certainly a lot has changed since then with several releases of the Oracle Data Provider for .NET, Oracle Database, Visual Studio, the Oracle Developer Tools for Visual Studio .NET, etc. I was just at one of the major booksellers here locally over the weekend and the "computer section" has dwindled immensely. I wonder if it would be worthwhile to update the book? There are several things I would want to change to be sure, but would it be worth it? Do people get their technical information from OTN and MSDN mostly now?

More on JavaFX

Oracle EPM Smart Space - Tue, 2008-05-27 14:18

OK I will be totally honest I don't have a whole lot on this one, simply because it is the newest entry in the market. It does look promising from the videos I have seen and what is being said about platform support sounds great. I just hope we can avoid all the issues I have seen over the years with JRE and version compatibility… I have signed up to preview the SDK and when I get a hold of it I will be sure to share more. I do want to share one cool feature I have seen on video. It is the ability to start with the application in the browser and then drag it to the desktop. Here the video that shows this:



The feature I am talking about is about 2:10 into the video and I think this will be a key differentiator that the other RIA (Rich Internet Application) players will quickly try to copy.

Categories: Development

Are your projects failing? How to avoid the Pitfalls

Project Directions - Tue, 2008-05-27 10:20

In a recently published article entitled Why Projects Fail (And How to Avoid the Pitfalls) published by Enterprise Systems, Senior Director of Strategy for Oracle Projects Colleen Baumbach outlines many of the common mistakes that lead to project failure.

I think one of the best points Ms. Baumbach makes is at the end where she says the accumulated years of project failures almost creates a mindset from the start that a project is doomed.  As she notes, there are countless studies that have been prepared showing how dismal project success rates are.

How are companies addressing this?  According to a Forrester study published in early 2007, twenty-six percent of IT leaders planned to hire project managers and 59 percent planned to train their current staff in project management in 2007.  They noted that those numbers changed very little from 2002.

Further reasoning behind the rush to acquire or train more qualified project managers: 

“The reason for the continued emphasis on project management skills is because IT’s value to business remains contingent on it’s ability to deliver projects which meet business requirements both on time and on budget. IT staff accustomed to more technical roles struggle to transition to project management, CIO’s argue, and complain that educational institutions are not putting adequate focus on these skills through coursework.”

It should be a good time to be a project manager as long as you know how to avoid the pitfalls.


"Demo It To Oracle" (DITO) - CamStudio Help

Pankaj Chandiramani - Sun, 2008-05-25 22:03

Now you can record & share the issues you are facing to Oracle Support .

https://metalink.oracle.com/metalink/plsql/f?p=130:14:7679480494464650902::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,11.1,1,1,0,helvetica

Its a nice way to share the error & show the support guys on how that error occurred & faster reproducibility.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator