I am working on upgrading all of our production databases to the Oracle 11.2.0.4 version. My company’s most important database serves a custom, in-house developed application so we have the luxury (and sometime the curse) of having complete control over the application source code. If I discover a version-specific issue with a their party application, I file a trouble ticket with the vendor to get the issue fixed. But for our own application, I often have to diagnose the problem and determine how to get the issue fixed.
Since I have been at this company, I have upgraded from 11.1.0.7 to 11.2.0.2 and then to 11.2.0.3 and now to 11.2.0.4. The two previous upgrades went just fine. No problems. So I have been very surprised that the upgrade from 11.2.0.3 to 11.2.0.4 has been problematic for our application.
EVEN WHEN YOU THINK THE UPGRADE IS “minor“, PERFORM ADEQUATE TESTING!!!!
I never expected to find issues with this simple patchset upgrade. I’m not skipping versions and 11.2.0.4 shouldn’t introduce too many problems. My first issue I blogged about here:
http://www.peasland.net/2014/08/19/sticky-upgrade-problem/
The next problem is a query similar to the following in our application code:
SELECT DISTINCT columnA
FROM our_table
Lawax Capsules and Vital M-40 Capsules is a wonderful solution to treat erectile dysfunction. cialis generika You are not complete without a partner, this is the reason these capsules have been proved as the best herbal remedies for solving your impotence, premature ejaculation, nightfall, wet dreams, spermatorrhea and cialis samples nocturnal emissions. All in all, avoid cheap pills and always look for reasonabhttp://foea.org/projects/ cialis tadalafil generic price and be aware of the manufacturer’s reputation as well as taking the appropriate dosage for your body. Remember, this benefit of Kamagra for ED issue can hurt mentally to viagra tablets 100mg some of the person as penile erection has been a major cause of couple’s broken relationship.
ORDER BY columnB;
The above query will now return an ORA-01791 error in Oracle 11.2.0.4 but it ran just fine in previous versions. When a DISTINCT is used, and the ORDER BY clause has a column not seen in the SELECT clause, the ORA-1791 error will be raised. Oracle says that the fact that this used to work is a bug. The bug is fixed in 11.2.0.4 so the above now raises an exception.
When I first was made aware of this issue, my initial thought was why are we ordering by a column not in the SELECT caluse? The end user won’t know the data is ordered because they can’t see the order. Then I found out that this routine is only used for internal processing. Well machine’s work just fine without ordering the data. So the simple fix on our end was to remove the GROUP by clause. As soon as the code changes gets into production, I can proceed with my database upgrade.
It is so important that I’ll say it again:
EVEN WHEN YOU THINK THE UPGRADE IS “minor“, PERFORM ADEQUATE TESTING!!!!
At this company, we follow a strict process for changes. The change is made in development first. And then after a period of time, the change is made in Test environment. And then after a period of time, if there are no issues, the change can proceed to production. We also have a custom test application that exercises key components of our application so even if our testers are not hitting that portion of the app, our automated test suite will.
Without adequate changes, the two issues we encountered would most likely not have been noticed until the change was in production. Then the DBA would have been blamed, even though both of these issues were application code problems. Test, test, and test again.
