Need to Kill j000 process to shutdown the database after upgrade from 8.1.7.4 to 9.2.0.8.

This is a common problem after the upgrade. One important point to remember is to disable all the schedules for dbms jobs before kicking off the upgrade and enable the schedule after the uprade is over. Alternatively job_queue_processes parameter can be set to '0'.

You see following symptoms in the alert log.

-- Clip --

Active call for process 21244 user 'oracle' program 'oracle@hostname (J000)'
SHUTDOWN: waiting for active calls to complete.

-- End Clip --

Trace File of J000 process shows

-- Clip --

Unix process pid: 13302, image: oracle@hostname (J000)

*** SESSION ID:(95.9333) 2007-12-14 22:50:52.282
OPIRIP: Uncaught error 1089. Error stack:
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-00448: normal completion of background process

-- End Clip --


Reference - Metalik Note:342805.1

Autopatch Timing Report. Useful information that helps you to plan your Production Downtime.

I was all set to write a shell script that will parse the patch log file and arrive at top time consuming sqls that will help me know the time taken by the sqls for the upgrades. After constantly watching the Maintenance Pack ( 3480000 ) session for 30+ hours. The following lines of the autopatch session caught my attention.

-- Clip --

sqlplus -s APPS/***** @/xxxx/xxxx/xxxxappl/ad/11.5.0/admin/sql/adtimrpt.sql 5578 adt05578

A job timing report has been generated for the current session.
You should check the file
/xxxx/xxxx/xxxxappl/admin/XXXXXX/out/adt05578.lst

for details.

-- End Clip --

Out of curiosity I opened the file "adt05578.lst", I saw the information that I was exactly looking for.

-- Clip --

Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37

Summary page 1

Jobs that ran successfully on the first try : 96048
Jobs Failed, deferred, then run successfully : 35
Jobs Failed, restarted, then run successfully : 3
Jobs Failed and Skipped : 0

Total number of jobs : 96086


^L
Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37

Top 100 Time Consuming Jobs (out of 96086 total jobs) page 1

Elapsed
Task Time Wrk Start Time
Num Prod Job (HH:MM:SS Phase Id (Mon DD YYYY HH:MI:SS)
----- ------ ------------------ ---------- ------------ ---- -----------------------
0 inv inviusi1.sql 3:25:18 dat+10 16 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 10 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 9 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:13 dat+10 18 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:08 dat+10 3 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:08 dat+10 13 Dec 13 2007 21:29:06
0 inv inviusi1.sql 3:25:07 dat+10 8 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:24:58 dat+10 19 Dec 13 2007 21:29:17
0 inv inviusi1.sql 3:24:57 dat+10 27 Dec 13 2007 21:29:01
0 inv inviusi1.sql 3:24:57 dat+10 6 Dec 13 2007 21:29:18
0 inv inviusi1.sql 3:24:56 dat+10 12 Dec 13 2007 21:28:58

-- End Clip --


Summary section that this log shows is important and can be a source of truth for auditing purposes.

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 !!