Oracle OpenWorld 2010 planning

2 September 2010 – 0:00

I'm pretty excited to be heading to Oracle OpenWorld this year. My only previous OpenWorld was in 2006, which feels like eons ago. This time around, I'll be going on a blogger pass, which I feel is very generous of Oracle, since my most popular post has been a piece that I wrote for someone else's blog, and the most popular page on this blog is basically a 5-line shell script. *ahem* At the very least, I hope to become worthy of the blogger title by blowing the doors off my 2-posts-per-month lifetime average during the month of September.

With the conference less than 3 weeks away, I'm mostly all set. Plane tickets and hotel were booked long ago. I've learned that getting from SFO to my hotel via BART will be ridiculously easy. I've even managed to arrange a roommate who's certain to take all blame in the vanishingly unlikely event of shenanigans. ;-) There's just one problem: there's way too much to do once I get there! Okay, maybe that's not a problem; call it an abundance of riches.

The challenge: Fitting it all in

When I left OOW 4 years ago, I told myself that I would "do better next time." It felt like I hadn't managed to get as much out of the conference as I could have. This time around, I'm hoping to strike a better balance between sessions, exhibits, keynotes, and, um...

oow10-networking.png

...I'm not sure what Chet means by those first two, but I do hope to work in some networking. I'm looking forward to meeting some people with whom I've mostly interacted 140 characters at a time, or via my longer-winded blog comments. I'm already signed up to attend the Blogger meetup, and am looking for other opportunities to meet people; drop me a comment or an email if you'd like to grab a coffee, beer, or just exchange a quick handshake. :)

But back to that "abundance of riches" problem. I'm having a heck of a time reconciling my general interests (Oracle Apps, "Core" database, virtualization), specific areas of curiosity (this year, GoldenGate and MySQL), and my desire to see presentations by people way smarter than me. Thankfully, there's a fair amount of overlap on that last point, but I'm still looking at almost 40 interesting sessions over 5 days, not counting keynotes and hopefully an Unconference visit or two. It's unclear when I'll be eating lunch, most days. I'm even double- and triple-booking Sunday, for heaven's sake, and that's "just" SIG/User Group day!

The List, so far

Here are the sessions that I'm thinking about. If you have any thoughts about how to make this more sane, or (I can't believe I'm typing this) if you think I might enjoy a session that's not listed here, I'd love your feedback. Thanks to Floyd Teter (@fteter), Steven Chan, and the people who put together the Oracle ACE presentations page on the Oracle Wiki for making my job slightly easier by providing some initial filters for the 2000+ sessions. Now if only I could find some mad scientists to finish the work on these cloning vats...

Apps, EBS, Fusion, Whatchamacallit

I'm probably going a bit overboard on some of the R12 DBA and R12 upgrade stuff, but it's always a good idea to hear what others have done/are doing, and the surest path to stagnation is to assume you already know enough.

Oracle RDBMS

There are some folks in here that I'm really interested in seeing, since they're known to be great presenters. It was hard enough to winnow the database track down to just this list.

General Hodgepodge

And here's the grab-bag, which is not to suggest I have a lower level of interest. I'm just too lazy to subcategorize further. :-)

By jpiwowar | Posted in Oracle misc | Tagged | Comments (0)

Resolving a pesky ORA-12545 during EBS patching

23 July 2010 – 23:55

I'm working with a client for the next few weeks to help them meet baseline patching requirements for EBS 11i Extended Support. You know what that means:

  1. A bazillion browser tabs
  2. An SR or two
  3. Piles of patch READMEs
  4. Resolving prerequisites until your eyes cross
  5. Memorizing individual patch numbers, whether you like it or not
  6. The reward for all the hard research work: "15 jobs running, 235 ready to run, and 50682 waiting."
  7. Sporadic ORA-12545 errors in the adworker logs

Yeah, okay, #7 caught me a bit by surprise, too. The really tricky bit was that the worker wouldn't usually be marked as "Failed" when it received an ORA-12545. Instead, it would stay in a "Running" state, and eventually the entire patch session would go idle, until I manually restarted the errored worker. There is no shortage of troubleshooting notes for ORA-12545, but the essential condition is that the client cannot resolve the hostname of the database server. Considering how sporadic the errors were, this was more than a bit strange. So we tested client connectivity using the various permutations of the EBS database hostnames, verified that there wasn't any weirdness in the RAC listener setup, and still the problems persisted.

At a high level, it's always disturbing to have sporadic network wonkiness. More practically, it wasn't very much fun to contemplate writing production patching instructions that read, "Babysit the adpatch session very closely. Manually restart any workers that fail with ORA-12545." (I could also note that it's not very much fun to have a multi-hour patch test run grind to a halt 10 minutes after leaving the office for the day, but that would be whining, so I won't do that). I was just starting to consider running SQLNet traces to try to capture more information about this 1-in-1000 chance error condition, when one of the client's DBAs mentioned that the test systems and the DNS server were in separate data centers, connected over a WAN.

Rather than suggesting that we march over to the network admins and claim that DNS requests were being dropped on the floor (what a great way to make new friends!), and lacking the time to put together an easily-reproducible test to back up such a claim, I recommended adding the hostnames and IPs of the database servers to the apps tier's /etc/hosts file. All subsequent patch runs were free of ORA-12545 errors, which left me free to concentrate on other, more interesting errors. I'm not a big fan of solution-by-assumption, but in this case I was able to carry the work forward, and the investigation into potential DNS problems can wait for a quieter time.

By jpiwowar | Posted in EBS | Tagged , , , | Comments (0)

Weekend mumblings: Oracle and VMware

27 June 2010 – 0:13

I’ve had VMware and Oracle on my mind recently, probably for three reasons:

  1. In the last week or so, I’ve been involved in a two discussions about the viability/supportability of Oracle E-Business Suite on VMware, one on OracleCommunity.net, and the other on LinkedIn.
  2. The IOUG recently sent out a VMware-sponsored email entitled, “Why Oracle DBAs should care about virtualization.”
  3. I am an unreformed, unrepentant nerd.

So, if you're up for some lazy Saturday evening musings about Oracle and VMware, keep reading. I’ll try not to ramble too much.

FUD or misinterpretation? The difference between “supported” and “certified”

The crux of the recent online discussions in which I participated was the age-old question: Will Oracle support customers running on VMware, or not? The confusion arises from a My Oracle Support note stating Oracle’s position on the topic: Support Position for Oracle Products Running on VMWare Virtualized Environments (Doc ID 249212.1). If read too quickly, or too conservatively, it’s possible to conclude that Oracle won’t provide full support for its products in a VMware environment. Coupled with the knowledge that Oracle offers its own virtualization product, Oracle VM, that it does fully certify and support, it’s easy to see where Oracle customers might think twice about considering VMware as a virtualization platform.

It’s important to remember, however, that when it comes to Oracle products, there’s a big difference between certification and support. Certification of Oracle software on specific hardware platforms, for example, is pretty much out of scope for Oracle Support, and VMware is, in effect, providing a virtual hardware platform upon which to run your systems. The My Oracle Support note referenced above actually spells out pretty clearly how Oracle will support you if you’re running on VMware. What the note effectively states is, “If the reported issue looks like a problem with our software, we will support you. If your issue looks like it’s related to VMware, we’re going to send you to VMware for resolution.” While it might seem like this is a less-than-usual level of support, it’s actually entirely fair. If your problem could be traced to the OS, after all, you could expect to be referred to the OS vendor. Similarly, if the conclusion were that you had hardware problems, you could expect to be sent to your hardware vendor.

Incidentally, this illustrates a core benefit of the Oracle-OEL/Solaris-Oracle-VM technology stack: if you have issues that require intervention from Oracle Support, whether they’re with software, hardware, or virtualization layer, there’s no concern that you’ll be left playing “vendor volleyball.” Your issues will be handled entirely by Oracle, and there won’t ever be any finger-pointing between the various product support teams, because everyone is living in peace and harmony. *ahem* :-)

Bottom line: Currently, yes, you’ll be supported if you run your Oracle environment in VMware. Unless your problem turns out to be VMware-related, in which case you probably want VMware’s help anyway.

That’s great and all, but does it work, particularly in production?

DIsclosure time: I’ve had my doubts in the past about the viability of VMware for production E-Business Suite environments, as noted here in a comment on Steven Chan’s blog. I’m just one guy, though. VMware’s products have matured since then, and some of the problems I alluded to were not strictly VMware issues. Even back then (2005-2006), though, I was comfortable running Grid Control and some production Oracle Collaboration Suite application tiers in VMware. From what I’ve read, things have only gotten better with VMware vSphere.

It’s worth noting that VMware itself runs Oracle products, including E-Business Suite, in a VMware virtualization environment. They also have a list of customer references on their web site; if you elect to use VMware in your production Oracle environment, you won’t be in uncharted waters. If you’d like to read about the experiences of an Oracle customer running on VMware without the marketing filter of the vendor’s website, I recommend reading Jay Weinshenker’s blog. That’s twice I’ve linked to him in two blog posts, but I’m not digitally stalking him, I swear. He just writes interesting stuff. ;-) I’ll probably link to him one more time before I’m done here.

Why not just use Oracle VM?

I have far more experience with VMware than I do with Oracle VM, so it shouldn’t be surprising that I present as a VMware fanboy. Oracle has made a clear commitment to the virtualization space, however, and has put together an attractive package to support it. Oracle VM might be a good fit for you, particularly if you:

  • are interested primarily in virtualizing Oracle products (note: Oracle VM can be used for non-Oracle virtualization needs as well)
  • haven’t already made an investment in VMware in other parts of your IT infrastructure.
  • prefer having just one vendor to flog when things go wrong. :)

References/Additional reading

As if I haven’t packed enough links into this post, here are a few more:

By jpiwowar | Posted in EBS, Virtualization | Tagged , | Comments (2)

Recently relevant (to me) links

21 June 2010 – 15:16

I've found a few bits of interesting reading over the past week or two. Most of this content is not brand-new, but since they've added some technical enrichment to my life, I figured that a "shout-out rollup" was appropriate.

  • Jonathan Lewis' review of a Collaborate '09 presentation on tuning by cardinality feedback, and the slides from that presentation, helped me refresh my approach to a report tuning exercise.
  • Kerry Osborne saved my sanity with an 18-month old post, as I tried to figure out why a 'shared server idle wait event' was skewing my 11g Statspack reports.  
  • Ever wondered what the translations are for the command_type column in V$SQL? Martin Widlake offers a few pointers to get that info, in not one, but two posts.
  • Finally, if you're interested in some real-world information about using Oracle E-Business Suite on VMware (and who isn't, really?), J Weinshenker (Twitter: @aus_effendi) has recently spun up a blog about those topics and others of interest to ORACLENERDs of the DBA/Apps DBA variety. Quality content there; go read!
By jpiwowar | Posted in Oracle misc | Tagged , , , , | Comments (0)

Small change to GL_SECURITY_PKG in EBS 12.1.2

14 June 2010 – 9:09

After a recent upgrade to Oracle Applications 12.1.2, a client started experiencing problems with Discoverer reports. These reports were based on views in a custom schema that use APPS.GL_SECURITY_PKG.VALIDATE_ACCESS to restrict the returned data set. Despite no known changes to the reports or associated permissions, reports were terminating with the following error:

ORA-00942: table or view does not exist
ORA-06512: at "APPS.GL_SECURITY_PKG", line 1427

Line 1427 of GL_SECURITY_PKG starts a query against table owned by APPS to which the custom schema did not have access. While this section of the code had not changed between the original system and the upgraded one, one thing that had changed was the package header.

Here's the header from the original package:

SQL> select line
  2       , text
  3    from all_source
  4   where owner = 'APPS'
  5     and name = 'GL_SECURITY_PKG'
  6     and type = 'PACKAGE'
  7     and line <= 2
  8   order by line asc
  9  /

      LINE TEXT
---------- --------------------------------------------------------------------------------
	 1 PACKAGE gl_security_pkg AS
	 2 /* $Header: gluoases.pls 120.10.12000000.1 2007/01/16 22:31:44 appldev ship $ */

And on the upgraded system:

      LINE TEXT
---------- --------------------------------------------------------------------------------
	 1 PACKAGE gl_security_pkg AUTHID CURRENT_USER AS
	 2 /* $Header: gluoases.pls 120.10.12000000.1 2007/01/16 22:31:44 appldev ship $ */

Somehow, the GL_SECURITY_PKG was recompiled with invoker's rights (CURRENT_USER) instead of the default DEFINER rights. This explains why the custom schema was suddenly unable to access the APPS-owned tables without explicit permission to read the tables.

It may be that someone manually compiled this package with AUTHID CURRENT_USER, but it seems more likely that this was done during patching. The short-term "band-aid" fix here is to grant read permission to the custom schema on the tables referenced in APPS.GL_SECURITY_PKG. The longer-term solution, currently underway, is to engage with Oracle Support to determine how or why this package was recompiled with the AUTHID CURRENT_USER property, and learn if there is new guidance regarding the use of this package.

By jpiwowar | Posted in EBS | Tagged | Comments (0)

EBS R12 nugget: The difference between adopmnctl.sh and opmnctl

23 May 2010 – 11:15

This post takes its inspiration from a recent OTN forum thread about problems with E-Business Suite R12 web services, but it's not an attempt to resolve the root cause of that thread -- lots of other people had already chimed in to help by the time I got there. One thing that was going on in the thread as part of the troubleshooting, however, was an attempt to start web services using the generic 10gAS opmnctl script, instead of the R12-specific adopmnctl.sh script. This might not seem like such a bad idea on the surface, since adopmnctl.sh is "just" a wrapper script that calls opmnctl, and the latter script is very familiar to admins who have worked with Oracle Application Server components. But take a look at the difference in the behavior of the two scripts, even when R12 web services are down:

[applvis@londo ~]$ adopmnctl.sh status

You are running adopmnctl.sh version 120.6

Checking status of OPMN managed processes...
Unable to connect to opmn.
Opmn may not be up.

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/appl/admin/log/adopmnctl.txt for more information ...  

[applvis@londo ~]$ opmnctl status
Error reading opmn.xml
Message:{/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.2/opmn/conf/opmn.xml (No such file or directory)}
Filename:{/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.2/opmn/conf/opmn.xml}
FileContent:{}

(Disclosure: I added opmnctl to my PATH for ease of reading/typing; it's not there by default in the applmgr environment)

What's causing the difference? The opmnctl script uses the value of the ORACLE_CONFIG_HOME environment variable to set the ONS (Oracle Notification Service) home, and if that environment variable isn't set correctly, then opmnctl will look in the wrong place for the opmn.xml file, resulting in the error displayed above. The default value of ORACLE_CONFIG_HOME in the apps tier environment is the 10.1.2 configuration directory, but in order to function properly, opmnctl needs to see the 10.1.3 ORACLE_CONFIG_HOME. Here's an illustration:

First, the default value of ORACLE_CONFIG_HOME:

[applvis@londo ~]$ echo $ORACLE_CONFIG_HOME
/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.2

Then, in the opmnctl script, see that the value of oracle.ons.oraclehome is set based on the value of ORACLE_CONFIG_HOME. If you call it directly from the apps environment, that will make it point to the 10.1.2 config directory, as shown by these lines in the opmnctl script (I've highlighted the relevant parts):

if [ -n "$ORACLE_CONFIG_HOME" ]
then
MODIFIED_ORA_CONFIG_HOME="`echo $ORACLE_CONFIG_HOME | tr -d "[:space:]"`"
else
MODIFIED_ORA_CONFIG_HOME=""
fi
if [ -z "$MODIFIED_ORA_CONFIG_HOME" ]
then
$JAVA -classpath $ARGUS_JAR:$OPTIC_JAR \
-Doracle.ons.oraclehome=$ORACLE_HOME \
oracle.ias.opmn.argus.Argus -help
else
$JAVA -classpath $ARGUS_JAR:$OPTIC_JAR \
-Doracle.ons.oraclehome=$MODIFIED_ORA_CONFIG_HOME \
oracle.ias.opmn.argus.Argus -help
fi

If you run adopmnctl.sh instead, ORACLE_CONFIG_HOME gets set to the 10.1.3 config directory, as it should be, and that is the value that gets passed to the opmnctl script:

[applvis@londo ~]$ grep ORAENV_FILE `which adopmnctl.sh`
# Set ORAENV_FILE to 10.1.3 Oracle Home Environment File
ORAENV_FILE="/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.3/R12VIS_londo.env"
# Check the existence of ORAENV_FILE
if [ ! -f "$ORAENV_FILE" ];
. $ORAENV_FILE
[applvis@londo ~]$ grep ORACLE_CONFIG_HOME= /u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.3/R12VIS_londo.env
ORACLE_CONFIG_HOME="/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.3"

Please note that the takeaway from all of this is NOT "manually change the value of ORACLE_CONFIG_HOME to run opmnctl." The takeaway is that running adopmnctl.sh is not the equivalent to running opmnctl, and that you can expect strange behavior if you try start R12 web services without using adopmnctl.sh. Whenever possible, use the tools provided as they're intended to be used.

By jpiwowar | Posted in EBS | Tagged | Comments (0)

Old PLAN_TABLE in EBS Vision

21 May 2010 – 22:50

While attempting to generate an explain plan in an R12 Vision database, I instead generated the following error:

APPS@R12VIS(11.1.0.7)>set autotrace traceonly explain
APPS@R12VIS(11.1.0.7)>select count(*) from DUAL;

Execution Plan
----------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle support
       Please provide also a DMP file of the used plan table PLAN_TABLE
       ORA-00904: "OTHER_TAG": invalid identifier

Well, okay, I wasn't really querying the DUAL table, but it makes the example shorter. Since this was only happening when I was connected as the APPS user, I suspected a private PLAN_TABLE had been created for APPS, and sure enough:

APPS@r12vis(11.1.0.7)>select owner
  2  , object_name
  3  , object_type
  4  from dba_objects
  5  where object_name like 'PLAN_TABLE%';

OWNER		OBJECT_NAME		       OBJECT_TYPE
--------------- ------------------------------ --------------------
SYS		PLAN_TABLE$		       TABLE
PUBLIC		PLAN_TABLE		       SYNONYM
APPS		PLAN_TABLE		       TABLE

It's not too unusual to have a private PLAN_TABLE in a schema, even in a post-9i database. Oracle has definitely kept utlxplan.sql up to date in 11g. What was surprising to me was the structure of the table:

APPS@r12vis(11.1.0.7)>desc plan_table;
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 STATEMENT_ID								    VARCHAR2(30)
 TIMESTAMP								    DATE
 REMARKS								    VARCHAR2(80)
 OPERATION								    VARCHAR2(30)
 OPTIONS								    VARCHAR2(30)
 OBJECT_NODE								    VARCHAR2(128)
 OBJECT_OWNER								    VARCHAR2(30)
 OBJECT_NAME								    VARCHAR2(30)
 OBJECT_INSTANCE							    NUMBER(38)
 OBJECT_TYPE								    VARCHAR2(30)
 SEARCH_COLUMNS 							    NUMBER(38)
 ID									    NUMBER(38)
 PARENT_ID								    NUMBER(38)
 POSITION								    NUMBER(38)
 OTHER									    LONG

There are a lot of fields missing from that table. A quick reference check reveals that this is not a 9i version of the PLAN_TABLE, not even an 8i version, but a vintage Oracle 7.3 PLAN_TABLE. This is actually pretty cool, since it implies that Oracle's been eating its own dog food with respect to upgrading its demo instance. The Vision database clearly has a long and storied history. ;-)

The solution to my original problem, of course, is to just drop the old plan table. If you'd rather keep a static PLAN_TABLE around for the APPS schema, it's still a good idea to drop it and recreate it with the 11g utlxplan.sql.

By jpiwowar | Posted in EBS | Tagged , , | Comments (0)

Logging for non-existent listeners

16 May 2010 – 20:32

While working on an 11gR1 database server today, I fat-fingered the name of a secondary listener (you know, practicing my stock-trading skills), and in the process I noticed something that I hadn't considered before. Here's the setup:

[oracle@11gr1srv ~]$ cd $DIAG_DIR/tnslsnr/11gr1srv
[oracle@11gr1srv 11gr1srv]$ ls
listener  listener_old
[oracle@11gr1srv 11gr1srv]$ lsnrctl start LISTENER_ODL

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 16-MAY-2010 13:09:10

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Starting /opt/oracle/app/oracle/product/11.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.7.0 - Production
System parameter file is /opt/oracle/app/oracle/product/11.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/app/oracle/diag/tnslsnr/11gr1srv/listener_odl/alert/log.xml
TNS-01151: Missing listener name, LISTENER_ODL, in LISTENER.ORA

Listener failed to start. See the error message(s) above...

It looks like there are more consequences than brief embarrassment for that typo, though. My failed attempt to start the listener created a full Automatic Diagnostic Repository (ADR) directory structure for the non-existent listener: 

[oracle@11gr1srv 11gr1srv]$ ls
listener  listener_odl  listener_old
[oracle@11gr1srv 11gr1srv]$ ls -R listener_odl
listener_odl:
alert  cdump  incident  incpkg  lck  metadata  stage  sweep  trace

listener_odl/alert:
log.xml

listener_odl/cdump:

listener_odl/incident:

listener_odl/incpkg:

listener_odl/lck:
AM_1096102193_3488045378.lck  AM_1744845641_3861997533.lck
AM_1096102262_3454819329.lck  AM_3216668543_3129272988.lck

listener_odl/metadata:
ADR_CONTROL.ams       INC_METER_IMPT_DEF.ams
ADR_INVALIDATION.ams  INC_METER_PK_IMPTS.ams

listener_odl/stage:

listener_odl/sweep:

listener_odl/trace:
listener_odl.log

I looked through the Net Services docs, but wasn't able to find a way to disable this behavior, and my Google-fu failed me as well. This only seems to be happening for the 'start' command; other lsnrctl commands (reload, status, stop, etc) just return the expected "TNS-01101: Could not find service name" response.

This sort of thing happens in 10g, too (and probably in older versions, I'm just too lazy to fire up my 9i test system), but the impact is much smaller: a single log file, not all of the ADR stuff that comes with 11g. Even in 11g, it's not a big deal; this just generates some light housecleaning work. At the extreme, I suppose it could be possible to launch the lamest DOS attack ever by chewing up lots of inodes, but there have to be more entertaining (and faster) ways to do that.

By jpiwowar | Posted in Oracle RDBMS | Tagged , | Comments (0)

Resolving ORA-4023 during a 10gR2->11gR1 upgrade

13 May 2010 – 1:08

Here's one for a hypothetical frustrated Googler. I just had something goofy happen to me while testing preparations for a database upgrade from 10gR2 to 11gR1. I'd already been through the process a few times on this server, so I wasn't expecting any problems. Since production upgrades usually happen during time windows that guarantee reduced mental capacity, however, I'd decided to perform one last dry run to verify my documentation and to test my "upgrade day" checklist. I cleaned out the database files and related configurations from the previous successful upgrade, and started over from step 0.

Everything was going as expected until I actually ran DBUA. The first step after selecting the database to upgrade, "Gathering database information," was failing with the message:

ORA-04023: Object SYS.STANDARD could not be validated or authorized

At first, I thought I might have fallen prey to the conditions described in My Oracle Support Note 729909.1: Upgrading to 11.1.0 and DBUA reports ORA-4023 On SYS.STANDARD, but the diagnostic steps in the note did not match what I was seeing. Next stop: the DBUA logs in $ORACLE_BASE/cfgtoollogs/dbua/logs. The answer became obvious when I saw the following lines in the trace.log from the failed dbua session (some lines omitted for brevity):

[main] [2:35:45:170] [DatabasesPage.initializePage:351]  Loading databases instance found in OraTab/Registry
[main] [2:35:45:170] [OsUtilsUnix.enumerateSIDs:350]  checking sid: xxxx
[main] [2:35:45:179] [Database.getStepSQLInterface:690]  OH in database: /opt/oracle/app/oracle/product/11.1.0/dbhome_1
[main] [2:35:45:186] [Database.getStepSQLInterface:718]  sqlplus created with home:=/opt/oracle/app/oracle/product/11.1.0/dbhome_1 and sid:=xxxx
[Thread-4] [2:35:49:44] [CompManager.setSelectedDatabase:1339]  setSelectedDatabase::oracleHome=/opt/oracle/app/oracle/product/11.1.0/dbhome_1 sid=xxxx
[Thread-4] [2:35:49:45] [CompManager.setSelectedDatabase:1424]  Old home=/opt/oracle/app/oracle/product/11.1.0/dbhome_1
[Thread-4] [2:35:49:47] [CompManager.setSelectedDatabase:1425]  New home=/opt/oracle/app/oracle/product/11.1.0/dbhome_1

Sure enough, when I checked the same log file from a previous successful upgrade, the corresponding lines in the trace.log file all referred to the old (10.2.0) ORACLE_HOME. Despite all my careful cleanup, I had somehow forgotten to reset the value of the ORACLE_HOME for the database in /etc/oratab:

[oracle@testsrv logs]$ grep product /etc/oratab
xxxx:/opt/oracle/app/oracle/product/11.1.0/dbhome_1:N

To make matters worse, I had also failed to notice that the ORACLE_HOME value was wrong in the DBUA interface. This, I suppose, is why we test. :-P

After setting the ORACLE_HOME in /etc/oratab back to the 10gR2 value, I was able to complete the upgrade successfully.

By jpiwowar | Posted in Oracle RDBMS | Tagged , , | Comments (0)

An overview of merging patches in E-Business Suite

29 April 2010 – 21:36

A few days ago, a poster on the OTN forums asked a question about merged patches in EBS, wondering if it was possible to identify which individual patches in a merged patch had failed. The short answer, as explained in the thread, is, "no, merged patches don't work that way."

In nutshell, here is what the Oracle Applications patch merge utility, admrgpch, does:

  1. Reads through the drivers of each patch to be merged
  2. Checks the version of each file within the patch to determine if it should be included in the merged patch
  3. Copies the appropriate files to the new merged patch destination directory
  4. Generates a new patch driver that encompasses all of the actions for the individual patches. This driver will be u_merge_name.drv, where merge_name is supplied as an argument to admrgpch. If you elect not to specify a value for merge_name, the default is "merged," which is both boring and potentially confusing to you if you perform multiple merges over the lifetime of your EBS instance.

You can watch these steps unfold for yourself by using the verbose flag when running admrgpch. The default level, 1 (QUIET), is kind of dull, but level 3 (LOUD) can be instructive. In particular, you'll see the decisions made in the course of comparing file versions:

  • "File does not exist in the merged patch, adding...." - new file found
  • "Versions Same" - no need to add this file to the merge, as it was found in an earlier patch
  • "Replacing old entry with new one." -- Current file has a newer revision
  • Presumably, there's also a message for "this version is older than the one in the merge", but my test run didn't include that condition.

Merging patches saves time in two ways. First, since the result of the process is a single new patch, there is no need to run multiple patching sessions. Second, the merged patch will often do less work than applying patches in series, since multiples versions of files are culled, and only the highest version of a given file is applied. Consider the following example, from a merged set of arbitrarily selected patches. This file exists in multiple individual patches, with different versions.

zathras: jpiwowar$ find unmerged -name zxifsrvctypspkgb.pls | xargs grep '$Header' | cut -f4 -d\

120.246.12010000.19
120.246.12010000.9
120.246.12010000.27
120.246.12010000.19
120.246.12010000.45
120.246.12010000.28

If I were to apply each patch individually, adpatch could have to process that PL/SQL file up to 6 times. Perhaps that doesn't seem like a big deal, but there are sure to be similar situations for other files, and that can add up to a significant amount of extra work for large groups of patches. Of course, I could also have the good fortune to apply the patch with the highest version of the file first, and therefore only process that .pls file once, but how often does that ever happen? ;-) In contrast, by creating a merged patch, earlier versions of the file are discarded in favor of the highest version:

zathras:ZX jpiwowar$ find merged_patches -name zxifsrvctypspkgb.pls | xargs grep '$Header' | cut -f4 -d\

120.246.12010000.45

Incidentally, while it may look like the transcription of a stifled sneeze, or the name of an Icelandic volcano, that's actually a real filename.

A final, slightly repetitive point: The result of the patch merging process is a new, single patch. The driver file for the merged patch will indicate that the patch is merged, but the only time this information matters is when the AD_BUGS and AD_APPLIED_PATCHES tables are populated at the end of the adpatch session. When adpatch is running, there are no indications of the source of the individual files being processed. There aren't any intermediate steps wherein adpatch records the completion of individual patches, because there aren't individual patches anymore.

Even though the individual patches have lost their identities during the merge, it is still possible to retrieve information about the bug fixes themselves by joining ad_applied_patches, ad_comprising_patches, ad_patch_drivers, and ad_bugs. Here's a query that associates a merged patch with its individual bug fixes. If you're not in the habit of giving your merged patches unique names, then you'll need to modify the query to use ad_applied_patches.applied_patch_id rather than patch_name. When reviewing the results of the query, remember that patch numbers often, but not always, correspond to bug numbers.

select ap.patch_name
     , ad.orig_patch_name
     , ad.driver_file_name
     , ad.merged_driver_flag
     , ad.merge_date
     , bug.bug_number
     , cp.patch_abstract
  from ad_applied_patches ap
  join ad_patch_drivers ad using (applied_patch_id)
  join ad_comprising_patches cp using (patch_driver_id)
  join ad_bugs bug using (bug_id)
 where ap.patch_name = '&merge_name'
By jpiwowar | Posted in EBS | Tagged | Comments (2)