Friday, December 14, 2007

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.

2 comments:

Sundeep said...

Madhu, how are you doing man, good to see you in Apps DBA blogs :-) Take care, Sundeep Dubey

Madhu Sudhan said...

Hi Sundeep, Nice to hear from you. You can reach me on madhusudhan.thatamsetty@gmail.com.


regards,
Madhu