My Experience with Oracle Service Contracts (OKS) Rule Migration Metalink Note:265048.1

Does this Note:265048.1 sound familiar ? At least I can't forget this Note#. I can tell this Note# even if someone wakes me up in the middle of the night. This is because I have spent so much of time and iterations doing Service Contracts Rule Migration before 11.5.10.2 upgrade to cut down the time the Maintenace Pack (Patch# 3480000) takes.

Coming to the technical aspect of this Note#, looks like there is an architecture change the way Service Contracts are maintained in 11.5.8 vs 11.5.10.2. So, all the customers having huge volumes of data/Contracts are supposed to follow the Note#265048.1 and migrate the existing data to the new tables. Out of my own experience I have arrived at the following "Best Practices".

#1. First and foremost, read and understnad the Note#265048.1 and know the importance of this module. Sit with CRM users and understand how they use this module in their daily work. Don't be in a rush to apply the patches mentioned in the note.

#2. Before you get your hands on Service Contracts Rule Migration, get hold of a Service Contracts Functional Expert. OKS Functional expert needs to submit few concurrent requests and validate Service Contracts Data (Especially, Coverage Times and Reaction Times of a Contract) during the process.

#3. Define roles & responsibilities for the "Service Contracts Rule Migration" process documented in Note#265048.1 and agree in black & white in a meeting with all the stake holders. I'm over emphasizing it here. A email will do with the agreement. ;)

#4. If the Appliations and Database you are upgrading is 11.5.8 and 8.1.7.4 then make sure to add sufficient space for tablespaces OKSD and OKSX before you kick of the Section:3 Patch mentioned in Note:265048.1. This patch is the one which does the bulk data processing. In other words it inserts the data into the new tables. CAUTION !!! Do not add the datafiles with autoextend ON for OKSD and OKSX tablespaces. You might hit a bug:2229895 while the patch sqls do an insert and you will see the following ORA-00600 error in the alert log.

"ORA-00600: internal error code, arguments: [ktfbtgex-7], [952329], [5], [952328], [], [], [], []"

Refer to My Post "Importance of keeping a eye on alert.log while applying Oracle Applications Patches"

#5. Never ever apply these patches in a telnet or ssh session. Use VNC to apply the patches mentioned in Section:2 and 3 as it takes few hours. (> 10 hours).

#6. Document timings and errors you hit into it.

#7. Not but not the least Never skip any ADPATCH worker. If you do, you pay for it.

This is my experience with OKS in a nut shell. Even though this post looks theoretical, it is lot practical. :)

nJoy !!

Revisting My Previous Post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01"

I thought i'll add in more details to the post "DBUA FAILS WITH unable to extend rollback segment ODMA_RBS01".

Just changing the entry " InNewTablespace name="ODMA_RBS" size="50" unit="MB"" in $ORACLE_HOME/assistants/dbma/mep.cfg is not sufficient. There are couple of more tags that you need to play with in mep.cfg. Refer to "CreateRollbackSegmentsODMA_RBS01" revert="true" " section in the mep.cfg and pay attention to the following tags.

#1. Maxsize value="90" unit="MB"
#2. MaxExtents value="30"

Note: All the values mentioned above are default values.

I ran into an issue when I changed maxsize to "20000" (20 Gb). Where 20Gb of ODMA_RBS was my requirement. DBUA fails with the following message.

--Clip--
CREATE TABLESPACE ODMA_RBS DATAFILE '$ORACLE_HOME/oradata/SID/odma_rbs.dbf' SIZE 2147483648 REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 20971520000 default STORAGE ( INITIAL 3145728 NEXT 3145728 M
INEXTENTS 1 MAXEXTENTS 100 )

*
ERROR at line 1:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause

--End Clip--

Solution: Looks like when DBUA interprets mep.cfg it converts the above values that were changed in bytes. So, when it does create tablespace command doesn't recognize a byte equivalent value of more than 4Gb ( Reference Note:207964.1 ). I haven't got a chance to explore more about it. When I do, you will see a new post :-) in my blog. Till then restrict yourself to have maxsize to 4Gb or lesser value in mep.cfg.

Enjoy !!

adpcpcmp.pls - Takes an hour while applying any patch after upgrading to 11.5.10.2

Any simple problem after the upgrade that is not resolved will have long term maintenance related issues in production.

It is very important to watch the alert log while applying Oracle Applications patch as it gives wealth of information. As a matter of practice I watch the alert log while applying a patch and noticed that Invalid's Compilation stage of adpatch performed by "adpcpcmp.pls" was taking 60 mins. The alert log watch of mine caught the following error.

-- Clip --

Wed June 5 11:55:00 2007
Waited too long for library cache load lock. More info in file /xxxx/xxxx/udump/orcl_ora_7064.trc.

-- End Clip --

The trace file shows the following object

-- Clip --

LIBRARY OBJECT HANDLE: handle=3b3a2f458
name=TESTDB.MY_TEST_TABLE@db_link_name
hash=7e342709

-- End Clip --

Thats all was needed for me to cut down the compilation time from 60 mins to 10 mins. The host referenced in SID used by the above database link was not reachable.

So, watching the alert log while applying Oracle Apps patches pays off !!.

Enjoy !!