[OAUGNetDBA]-Re: SQL statement to get the applied patches list ?

[dave <david.best@gmail.com>]

Before you rely on that query, I would take some of the patches you
have applied in the past and see if it finds them.   We had some
issues with it and when I talked to Oracle support they said use
ad_bugs.    The last time I used it was 11.5.9, not sure if it works
better now in 11.5.10 or R12.

I think the problem may be that ad_applied_patches contains the list
of patches applied but not the list of bugs resolved in each patch. If
you search for a bug number contained in a consolidated patch it
sometimes can't find it.   So what would happen is that we would apply
a consolidated patch and a few months later have to apply a one-off.
The one-off patch would have a pre-req and the patch wizard would say
it hasn't been applied.   However, if you check ad_bugs it would be
there and upon investigation it was part of the consolidated patch.
So worst case I guess is that you just waste time re-applying a patch.

Once I hit the issue a few times I just stopped using the patch
wizard, especially after support told me to use ad_bugs.

On Tue, Aug 19, 2008 at 8:17 AM, GUERIN Jean-Luc <GUERINJL@essilor.fr> wrote:
> Thank you Dave
>
> Finally I have used
> alter system set events '10046 TRACE NAME CONTEXT
> FOREVER, LEVEL 4';
> to catch the following OAM SQL:
>
> SELECT *
>  FROM (SELECT   patch_name, merged_patches, max (appl_top_name), LANGUAGE,
>                 applied_patch_id, appl_top_id, count (*) drivers_applied,
>                 to_char (max (end_date),
>                          'dd-MM-yyyy hh24:mi:ss'
>                         ) completion_date,
>                 max (end_date), patch_drv_id
>            FROM (SELECT ap.patch_name patch_name,
>                         DECODE
>                            (merged_driver_flag,
>                             'Y', ad_pa_validate_criteriaset.get_concat_mergepatches
>                                                           (pd.patch_driver_id),
>                             ''
>                            ) merged_patches,
>                         AT.NAME appl_top_name, l.LANGUAGE LANGUAGE,
>                         ap.applied_patch_id, pr.appl_top_id, pr.end_date,
>                         pd.patch_driver_id patch_drv_id
>                    FROM ad_appl_tops AT,
>                         ad_patch_driver_langs l,
>                         ad_patch_runs pr,
>                         ad_patch_drivers pd,
>                         ad_applied_patches ap
>                   WHERE pr.appl_top_id = AT.appl_top_id
>                     AND AT.applications_system_name = :1
>                     AND pr.patch_driver_id = pd.patch_driver_id
>                     AND pd.applied_patch_id = ap.applied_patch_id
>                     AND pd.patch_driver_id = l.patch_driver_id
>                     AND ap.patch_name = :2
>                     AND (pr.start_date >= :3
>                     AND pr.end_date <= :4)
>                  UNION
>                  SELECT ap.patch_name patch_name,
>                         DECODE
>                            (merged_driver_flag,
>                             'Y', ad_pa_validate_criteriaset.get_concat_mergepatches
>                                                           (pd.patch_driver_id),
>                             ''
>                            ) merged_patches,
>                         AT.NAME appl_top_name, l.LANGUAGE LANGUAGE,
>                         ap.applied_patch_id, pr.appl_top_id, pr.end_date,
>                         pd.patch_driver_id patch_drv_id
>                    FROM ad_appl_tops AT,
>                         ad_patch_driver_langs l,
>                         ad_applied_patches ap,
>                         ad_patch_drivers pd,
>                         ad_patch_runs pr
>                   WHERE pr.patch_run_id IN (
>                              SELECT prb.patch_run_id
>                                FROM ad_patch_run_bugs prb, ad_bugs b
>                               WHERE b.bug_id = prb.bug_id
>                                     AND b.bug_number = :5)
>                     AND pr.appl_top_id = AT.appl_top_id
>                     AND AT.applications_system_name = :6
>                     AND pr.patch_driver_id = pd.patch_driver_id
>                     AND pd.applied_patch_id = ap.applied_patch_id
>                     AND pd.patch_driver_id = l.patch_driver_id
>                     AND (pr.start_date >= :7 AND pr.end_date <= :8))
>        GROUP BY applied_patch_id,
>                 patch_name,
>                 appl_top_id,
>                 merged_patches,
>                 LANGUAGE,
>                 patch_drv_id
>        ORDER BY 9 DESC, 1 DESC, 2 DESC, 3 DESC, 4 DESC)
>  WHERE ROWNUM < 201
>
> Regards
> Jean-Luc
>
> -----Message d'origine-----
> De : OAUG Net DBA listserver [mailto:OAUGNetDBA@oaug.com] De la part de dave
> Envoyé : lundi 18 août 2008 20:03
> À : OAUG Net DBA listserver
> Objet : [OAUGNetDBA]-Re: SQL statement to get the applied patches list ?
>
> I've experienced issues with OAM detecting whether or not a patch has been applied.  Personally I use ad_bugs.
>
> So one way would be to create a database link to the databases  and query against that table.
>
> Quick and dirty way I guess..
>
> On Mon, Aug 18, 2008 at 4:27 AM, GUERIN Jean-Luc <GUERINJL@essilor.fr> wrote:
>> Hello
>>
>> We would like to compare the fine patch level of different EBS 11i
>> environments.
>> We need the sql statement to perform it (including the ones applied as
>> merged patches).
>> One way would be to trap the SQL behind the report generated by OAM.
>> Does someone know how to get it?
>> Thank you in advance.
>> Regards.
>>
>> Jean-Luc GUERIN
>> ESSILOR INTERNATIONAL
>>
>> #############################################################
>> This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
>> To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com> To switch to the
>> FEED mode, send any message to <OAUGNetDBA-feed@oaug.com> To switch to
>> the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com> To switch to
>> the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com> Send
>> administrative queries to  <OAUGNetDBA-request@oaug.com>
>>
>>
>
> #############################################################
> This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
> To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com> To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com> To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com> To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com> Send administrative queries to  <OAUGNetDBA-request@oaug.com>
>
>
> #############################################################
> This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
> To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com>
> To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com>
> To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com>
> To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com>
> Send administrative queries to  <OAUGNetDBA-request@oaug.com>
>
>

#############################################################
This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com>
To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com>
To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com>
To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com>
Send administrative queries to  <OAUGNetDBA-request@oaug.com>