Monday

SOA Upgrade 11.1.1.3 -> 11.1.1.6

We upgraded our SOA install from 11.1.1.3 to 11.1.1.6 and hit a few issues.   One of the main issues was that after upgrading DEV our inflight processes disappeared.    We talked back and forth with Oracle and it was supposed to be supported but for some reason it wasn't working for us.    I know that from SOA 10g to SOA 11g in-flight processes are not supported as part of the upgrade.

One of the ways we use BPEL is workflow for a custom application.  It could take months for a workflow to complete and there are always in-flight processes.    So there is never a quiet time, or a point at which all workflows are complete to do the upgrade.

We put a plan in place to manually re-place all items back in the proper stage.    It seems like it was going to be a fair amount of effort so for DEV we only re-placed a subset of items to make sure we had the process correct.

However, once we upgrade TEST the in-flight processes somehow magically survived.    We aren't sure why, there could be some small inconsistencies between DEV and TEST which caused it.  Another reason could be that in DEV we upgraded to 11.1.1.6 first, then at a later point upgraded to 11.1.1.6.6.   In TEST we went directly to 11.1.1.6.6.

The SOA upgrade itself isn't a very resilient process.   If the PSA (Patch Set Assistant) gets interrupted for some reason its not smart enough to recover.  In DEV it wasn't a big issue, I had an export of the MDS and SOAINFRA schemas and I just dropped and recreated.    For PROD tho, the amount of data, thus downtime, would prevent us from doing this.    We only hit an issue once in DEV and TEST was smooth,  so for PROD we all had our fingers cross.

The PROD install was going smoothly until I had to run the PSA.     Shortly after I started the schema upgrade it failed and a dreaded error appeared.  In the $FMW_HOME/oracle_common/upgrade/logs directory I found:


[2013-08-21T19:28:09.239-04:00] [SOA] [ERROR] [] [upgrade.SOA.SOA1] [tid: 13] [ecid: 0000K2ZD5BJ9lZWpPws1yd1I5Klw000004,0] [[
oracle.sysman.assistants.common.dbutil.SQLFatalErrorException: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The problem was a database session holding a lock in the SOAINFRA schema.   We hit this problem in DEV and the solution was to shutdown applications that access SOAINFRA tables and change the schema password.   However, I didn't notice a hung database session by one of our custom applications.

If you try to run the PSA again it says the SOAINFRA schema isn't valid and won't let you continue with the upgrade.   So I tried manually updating the registry to state the schema is valid.

update schema_version_registry set status='VALID' where mr_name='SOAINFRA';

Great!  The installer started up again but it quickly failed.  Checking the logs:

2013-08-21 19:36:19.378 rcu:Extracted SQL Statement: [ALTER TABLE BPM_CUBE_PROCESS ADD (SubType VARCHAR2(200), DeploymentInfo BLOB)]
2013-08-21 19:36:19.378 rcu:Statement Type: 'DDL Statement'
JDBC SQLException - ErrorCode: 1430SQLState:72000 Message: ORA-01430: column being added already exists in table

Since this was production, I opened a P1 SR with Oracle right away.   In these situations I try to get Oracle involved as quickly as possible.   I'll still continue to do research on my end.  Sometimes I find the solution quicker, sometimes Oracle Support does.   

This time I found the following note:

Patch Set Assistant Failed for SOA 11.1.1.6.0 when Patch 13606871 is Applied on SOA 11.1.1.5.0 (Doc ID 1517404.1)

It provided a set of SQL statements to help rollback the schema upgrade.  Only problem was my upgrade was from 11.1.1.3, not 11.1.1.5.    Knowing that Oracle now supported manually rolling back the upgrade, then I looked at the script that was failing:

/u01/app/oracle/product/fmw11g_SOA/SOAHome_1/rcu/integration/soainfra//sql/upgrade_soainfra_111130_111140_oracle.tsql

After a few trial and errors I managed to write a script to undo all the changes.     Unfortunately if you missed one, which was easy to do as the script is a few thousand lines, you had to start over.   Finally I managed to find all updated objects and continued with the upgrade.

Another upgrade issue I encountered showed itself in the startup logs for soa_server1:

java.sql.SQLException: ORA-25226: dequeue failed, queue UPG_SOAINFRA.EDN_OAOO_QUEUE is not enabled for dequeue
The solution to this problem was to manually restart the queues:

SQL> show user
USER is "UPG_SOAINFRA"
SQL> exec dbms_aqadm.start_queue('IP_OUT_QUEUE',true,true);
PL/SQL procedure successfully completed.
SQL>  exec dbms_aqadm.start_queue('EDN_OAOO_QUEUE',true,true);
PL/SQL procedure successfully completed.
After the startup we waited anxiously to hear from the superusers to let us know the status of in-flight processes.    To much celebration (more so from them than us since they had alot of potential work to do) in-flight processes did not disappear.

27 comments:

aparna john said...

Hi,It is difficult for company formation in Qatar to terminate a registered agency; in addition compensation is payable upon the termination of the agency, including upon the expiry of a fixed term agency.Thanks...

Nidhi Arora said...

Thank you for sharing this nice information about this topic on your blog, it is very informatics info thank for this blog
Web Design Company in Dwarka
Software Development Company in Dwarka
Web Development Company in Dwarka
Web Hosting Company in Dwarka
Internet Marketing in Dwarka
Graphic Design Company in Dwarka
Mobile Apps Development in Dwarka
E-Commerce Development in Dwarka
SEO Company in Dwarka

Path Infotech said...

Thanks for sharing the information

For more info : Oracle 10g Certification

Aditi Jain said...

Nice Blog, Thank you for sharing this nice information about nice topic on your blog, it is very informatics info thank for this blog
Big Height

David Williams said...

Thanks for Information Our Online-Training-Informatica proven expert in all Hyperion Modules like Hyperion Financial Management, Hyperion Financial Data Quality, Hyperion Financial Reporting, Hyperion Essbase, Hyperion Planning, Smart view and Data Relationship management.Hyperion Essbase Online Training

Sekhar Byna said...

Very nice Article
you can also visit
Oracle apps blogs

Nikunj Sanghvi said...

Thanks for the informative article. Please visit our Resources website with more than 300 SQL queries, case studies, whitepapers etc:

https://resource.neocortex.com/

Please have a look at it and see if it would benefit your blog readers. We can also cross-promote your blog and content with us if you’re interested.

Lakshmi said...

Wonderful article, very useful and well explanation. Your post is extremely
incredible. I will refer this to my candidates...
hadoop Training in Chennai

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

Praveen Kumar said...

Valuable information thanks for sharing Oracle APPS DBA Online Training

Samartha Sri said...

wow really nice. It will be helpful for the people those who are ready to crack the interview and please also for remind what they have learned throughout concept.

Recruitment Consultancy in Bangalore

Abiya Carol said...

Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.

Software Testing Training Institute in Chennai

Philips Huges said...


Wonderful blog.. Thanks for sharing informative Post. Its very useful to me.

Installment loans
Payday loans
Title loans

sandhosh said...

Good blog post which provided a interesting information about.keep updating...
SEO Services in India

DataLoader said...

Thanks for the article. It really helped me.

gracy layla said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
Oracle EBS Training

shivani said...

Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
Workday HCM Online Training
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training

liza said...


You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
Thanks you for sharing!

Deer Hunting Tips Camping Trips Guide DEER HUNTING TIPS travel touring tips

liza said...


Thanks Still Hunting Method
Hunting psych tips Survival Tips Travel Touring Tips
for sharing this Post, Keep Updating such topics.

liza said...


I enjoyed over read your blog post. Your blog have nice information, I got good ideas from this amazing blog. I am always searching like this type blog post. I hope I will see again.

travel trekking tips
see the link Tent Camping 101 Exploring Smithriver

shivani said...

A bewildering web journal I visit this blog, it's unfathomably heavenly. Oddly, in this present blog's substance made purpose of actuality and reasonable. The substance of data is informative
Oracle Fusion Financials Online Training
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training

write my paper for me said...

Many thanks. For me it was useful.

https://mypaperedit.com/resume-editing said...

I am usually to blogging and i really admire your content. The article has really peaks my interest. I’m going to bookmark your web site and hold checking for brand spanking new information.

Robert said...

We treat the evaluation process serious and don’t publish fake reviews. In this https://scamfighter.net/review/essaybot.com review you can learn all information about the company which you won’t find elsewhere.

JJ said...

Thank you for this post. This is very interesting information for me.
https://www.writingpapersucks.com/speedypaper-com-review/

reflective essay thesis said...

Thank you for these posts and links, I hope this will come in handy.

Sarah Colins said...

Such an interesting information.

https://speedypaper.com/discounts