12c: Bypassing EM to turn off metric thresholds

File this one under the heading "not quite sure why this exists" as the article "How Disable Database Time Spent Waiting (%) At Database Level" on MOS with note 1629021.1 clearly explains the commands to turn the thresholds off for several different types of wait classes, but I do not understand WHY this is an option, required, or even suggested.  Is there a bug in the 12c EM which requires this to be completed "by hand"?  If there was a problem, I could understand turning off some threshold alerting but this seems completely backwards to anybody wanting to be proactive.

SOA: Failure to start when missing data source

While MOS note 1630343.1 is straightforward for why a SOA server can't start up when the data source is missing or unavailable, this note has a link to Oracle documentation which explains WHY that is and offers ways to tune data source connections.  This has multiple applications as it applies to WebLogic and JDBC connections, so this may add some value for you either today or down the road!

R12 Payables: Invoice Workbench error APP-SQLAP-10108 when committing

Some days, the articles I share have a very specific focus but today this feels like a much larger lesson on several fronts.  The MOS article "Invoice Workbench/Distributions - APP-SQLAP-10108: An Error has occurred while trying to commit" in 1570047.1 is very straightforward for how to replicate the issue and that the cause is non-printing characters but it goes to make me wonder why this was not included in a testing suite by Oracle.  Even more curious, is the reaction that "this just should not be done, so stop doing it" instead of Oracle development working to make sure the form will not accept these types of characters.  I wonder what other forms have this ticking time bomb lurking in them, and how many times it has to be reported until it gets resolved in an eventual release.

Weekend Learning: OBIEE 11g and DBFS

MOS note 1621469.1 captured my attention, as we have just implemented OBIEE again but on the 11g platform this time but I haven't had a lot of exposure to that or DBFS.  What is DBFS?  The note has a good explanation, I'll need to find more documentation about the subject later, but it boils down to a File System for Oracle Databases that can be used instead of NFS or ZFS based on SecureFile LOBs with access controlled with PL/SQL procedure calls.

The point of the article was if DBFS can be used for OBIEE 11g, and I'll steal a bit of thunder for once and tell you no it can't be used.  What I won't tell you, is WHY that is the case, and if you are curious you can visit the MOS note!

R12.1.3: Can a refund from Receivables have a Payables invoice in a different OU?

I am not sure why this feature is requested, but I do not spend time designing business cases so I assume this could be valid in certain scenarios depending on different business processes that some companies may have.  Regardless, it appears that this could have existed prior to R12.1.3 but is not possible after that code release per MOS note 1626484.1, yet if you need it to work this way an Enhancement Request has been opened in bug 18274880.  Oracle suggests that you open an SR, and mention this bug, if you want to be associated with the development of the ER.

R12.2.3: Error when creating Receivables Customer

Keeping an eye on the future, the bug documented in MOS note 1635413.1 is an interesting one.  When upgrading to R12.2.3 you will need to be aware of an ORA-06508 error in the procedure sync_contact_point when trying to create a Customer in Receivables.  This is as a result of an INVALID HZ_STAGE_MAP_TRANSFORM package as this and other key packages are dynamically generated so they need to be re-generated after upgrades when there are changes made to them or their underlying packages.

Selecting from too many tables hits Oracle DB bug on 11.2.0.3

Doing some research, I found a few hundred tables which were being gathered as if they contained no data but I wanted to verify it myself by looking at the data (or absence of data).  No problem I thought, I would just take the list of tables, put a comma at the end of each one, and then use the list in something like this:

SELECT * FROM table1, table2, etc

They are empty, so it sounds like a great idea right?  Not so much.  I believed that there was such a thing as too many tables (or maybe columns) that can be joined together (in a Cartesian join in this example) as the following ORA-00600 error was spit out at me:

ORA-00600: internal error code, arguments: [mal0-size-too-large], [kksoff : opitca], [], [], [], [], [], [], [], [], [], []

At first, I thought it was simply because of the Cartesian join but when I reduced the query to only have 20 or 30 tables I no longer received my ORA-00600 message and I received the single null row with all of the columns from each table returned.  It turns out, that since we are on 11.2.0.3 this is a bug I'm encountering detailed in MOS note/bug 553051.1/675576 which is resolved in 11.2.0.4 and 12.1.0.1 versions of the database.

12c: Datapatch failure with ORA-01017 error

As we are going to be upgrading a database to 12c, I'm keeping my eyes open for issues that might crop up when we install it and MOS note 1635007.1 is one worth reading up on!  Titled "Datapatch fails with ORA-01017, as it only connects to the database with OS Authentication" it appears that datapatch only is using OS authentication and it reports back the rejection error of ORA-01017 if you are trying to install it under an account which fails the OS authentication.  Even though this note indicates that the issue may be resolved with bug 18151716, if you read that bug information it doesn't really match the description of this note!

Regardless, I learned a bit as I usually track against ad_bugs to see if patches were applied but there is another table DBA_REGISTRY_SQLPATCH which Oracle suggests to modify if the datapatch needs to be applied manually in one of the two workarounds suggested.

Pictures, pictures: see all about it!

As some of you may have seen, I got to present at Hotsos and one of the requirements I completely spaced was getting them an updated headshot!  Luckily the team at IMAGINE Graphic Design were able to fit me into their schedule, took quite a few photos which were hard to choose from, and allowed me to get my picture published on the web and in the materials for the conference too!  I'm so happy with the results, that I'll be updating my social media profiles in several places with the official picture and maybe a few of the other versions as well.

If you're in Arizona, and are looking for a team to take pictures for most any event, contact the folks at IMAGINE Graphic Design.  Oh, and tell them I sent you too!  :}

Weekend Learning: Where are Audit Trails for the PLM product?

Did you know that there are audit trails for the Product Lifecycle Management (PLM) module?  Well I didn't either!  MOS note 1612322.1 has a script to pull from the commonLifecycleEventLog table, linking it to several other tables, but I'm more interested to see what this log table has in our environment as we just installed a new module called PLM which I think is the same thing.  If this tracks the audit trails for every change, we'll need to get a handle on how it gets purged, and add it to our documentation!

Weekend Learning: Oracle Real World Performance team

Are you interested in getting the most performance that you can out of the software and hardware you purchase from Oracle?  Of course you are!  That reason is why you should start following Oracle's Real World Performance team, including visiting their site that has several YouTube videos and which I'm sure will contain a link to the webcast Andrew Holdsworth (@ajholdsworth) held today about this very topic.  There will be other webcasts from Andrew, so look for them in the very near future!

Oracle Applications Patchset Comparison Utility

You may already be aware of this tool, but it has come up recently at work "how to know if we have the most recent patches applied?"  That's a great question!  Fortunately, My Oracle Support has the answer in note 139684.1 titled "R11i / R12.0 / R12.1 / R12.2: Oracle Applications Current Patchset Comparison Utility - patchsets.sh".  The best part?  You can download an updated file containing the newest patches EVERY day from Oracle and re-run this utility to find out if there are new patches EVERY day!  That's awesome!  I'm even thinking of putting this into a cron job to go out and get the file, then run the utility automatically so I'll have a fresh output each morning when I get in.  Talk about being proactive!

Oracle OpenWorld 2014 Call for Proposals!

The floodgates have opened for submitting abstracts to this year's Oracle OpenWorld!  I've been able to present for OAUG and Hotsos, but I'm looking to join others at OpenWorld as a presenter this year to continue the journey I've started.  Time to brush up on abstracts that might have been rejected previously, those selected for other conferences, and brand new abstracts that are percolating in my brain for OOW this year!

R12: Accounting Program/Create Accounting reports stall

At 7 AM our Submit Accounting and Unposted Items Report that should have run hours earlier were just sitting in the Concurrent Manager in a Pending status.  I found that this was due to an incompatibility between these reports, the Accounting Program and Create Accounting reports but they started at 8 PM so I had to solve the puzzle for why they were still running.  What challenges did I face while trying to verify this?

• The Accounting Program and Create Accounting reports did not actually create any entries in the log files so I couldn't tell that they were alive at all.
• Even further, I tried finding these alive in gv$session but I couldn't find anything by ACTION or MODULE.
• Our monitoring that constantly runs was reporting the Create Accounting report had a particular SID/SERIAL# combination but looking at that SID in gv$session actually saw different sessions coming in and out which were not our programs.

When a co-worker came in, I was at my wit's end, and I asked him to take a look at this.  Imagine my surprise when he tells me the Accounting Program and Create Accounting reports had been completed!  What did I learn from this?  The reports don't actually log their activity while they are alive and running, until they complete so it is easy to think they are dead!  I'll be opening up an SR to see if bug 17662654 relates to this problem, and if so I'll have to get the bug reopened as it has been closed.  If this isn't related, then I'll need to research this more locally when I get a chance.

A few other items I found on MOS that may be related are as follows: 1378432.1, 1463805.1, patch 14013094, 1465689.1, 791049.1, patch 16060007.

What prevents you from dropping a tablespace in a 12c PDB?

As we're looking to go to 12c, I've found myself paying more attention to this content so MOS note 1634495.1 grabbed my attention when it talked about receiving the following when trying to drop a tablespace in a 12c PDB:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-65040: operation not allowed from within a pluggable database

This is especially interesting for me as the application on top of the database that we are migrating to 12c uses tablespaces as versioning, so we started out with VERSION as the initial tablespace name and the next release was in a tablespace named VERSION2, etc.  Well, what happens now when we migrate the database to 12c and our initial tablespace can no longer be removed?  This note tells you why it is an issue, how you resolve it, and will be a key reference document I'm sure by our team down the road.