I recently upgraded a critical production system from Oracle 11.2.0.4 to 12.1.0.2. This is also a 3-node Oracle RAC database. I ran into a few issues which needed resolution after the upgrade. Hopefully this helps out others in their upgrades.
The first issue was related to a Global Temp Table that is central to our custom app. The GTT was involved in too many Cartesian Products. The solution was to leverage Oracle 12c’s new Session Stats for GTT’s. This AskTom article discusses the new session stats. Tim Hall has another great article on the subject as well. So to solve the problem, I just added an appropriate call to DBMS_STATS in our package code where the GTT was populated with data. Unfortunately, I used GATHER_TABLE_STATS similar to the examples in the articles I linked above. In an environment with a high degree of concurrency, this led to too many ‘library cache pin’ issues. I modifed the package code to use SET_TABLE_STATS instead…much faster. All I needed to do was compute the number of rows, and with the knowledge of what a typical average row length would be, the rest of the stats were easy to compute. This alleviated the pin contention and that issue was resolved.
The next issue I had to fix was due an ORA-22990 error: “LOB Locators cannot span transactions”. A dev in our shop fixed the issue so I never determined the exact root cause. But something in 12c changed because the package with this code has been unchanged for at least the last 7 years. I have two guesses as to the root cause…either a supplied package is now issuing a COMMIT that wasn’t there before or a supplied package was spawning a new transaction. The dev just changed where we initialize the LOB locator to be a spot later in the code.
This is first visible on the face and body, obesity, cialis price try description and even acne. For the best consequences of Kamagra it is suggested to take alpha look here sildenafil from canada blockers. However, if http://robertrobb.com/disappointing-ballot-measure-debates/ low price cialis you are planning to start a business then you must have a web site. They help to carve out emotional cheapest cialis stress on bed that can cause embarrassment and depression. I’ve had Oracle 12c in our test pipleline since August, or about 2 months before we hit production. Its a fair question to ask why testing didn’t capture these issues prior to the prod upgrade. For the GTT table issue, we simply did not have enough load on the system with people hitting the same data points as our end users do. For the LOB Locator issue, the part of the application receiving this error is only executed during mid-to-late October, as I later found out. So it was never executed during our testing period. This is why we missed it during testing.
As I was fixing a few other minor performance issues (all resolved by updating table/index stats) and addressing the issues above, I learned that Oracle changed their stance on the free Extended Support for Oracle 11.2.0.4. I upgraded last Wednesday evening and on Friday, Oracle announced that free ES for 11.2.0.4 would be extended until May of 2017! One of the main drivers for me upgrading to 12c was to ensure that our mission-critical database would be supported and the initial free ES for 11.2.0.4 was to expire in January, just 4 months away. As I’m working through upgrade issues and I learned of this ES extension, I was starting to wonder if the announcement had a message (reading between the lines) that 12.1.0.2 wasn’t ready for prime time. I actually started the investigation on what it would take for me to downgrade this database. But I was able to work through all of the issues and today, life seems to be just great on 12.1.0.2.
