Patch Analysis(adpatch)

As a DBA we often need to apply Patches. This is a brief process to analysis the patch impact in our environments, before applying the patch.

Patch analysis:-
------------------------------------------------------------------------------------------------------------
Login to Unix/linux/solaris server that contain the Database.

$sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 18 12:17:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
SAMPLEDB

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
11.5.10.2

SQL> select patch_level from apps.fnd_product_installations where patch_level like '%AD%';

PATCH_LEVEL
------------------------------
11i.AD.I.7
(Similarly check for other modules as well.)

SQL> select LANGUAGE_CODE, NLS_LANGUAGE, INSTALLED_FLAG from apps.fnd_languages_vl where INSTALLED_FLAG = 'I';

LANG NLS_LANGUAGE                   I
---- ------------------------------ -
ZHS  SIMPLIFIED CHINESE             I
ZHT  TRADITIONAL CHINESE            I
(This is for my sample environment, there may be other languages also)

SQL> select count(1) from dba_objects where status='INVALID';

  COUNT(1)
----------
        53

SQL> select object_type, count(1) from dba_objects where status='INVALID' group by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
PROCEDURE                    9
VIEW                        10
SYNONYM                      4
PACKAGE BODY                26
PACKAGE                      4

SQL> select owner, count(1) from dba_objects where status='INVALID' group by owner;

OWNER                            COUNT(1)
------------------------------ ----------
SYSTEM                                  2
EAR                                     1
EAPTIB                                  4
SUPPORT                                 2
CHUK                                    2
APPS                                   33
EDMSRUN                                 2
MFM                                     1
PERFSTAT                                2
TIBCO                                   4

SQL> select host||'.'||domain from apps.fnd_nodes where support_admin='Y';

HOST||'.'||DOMAIN
--------------------------------------------------------------------------------
eaad380.ams.sample.com
------------------------------------------------------------------------------------------------------------
Oracle also provides us the Patch Wizard in the Oracle Application Manager(OAM) for performing the patch analysis. Please wait for my next post on using the OAM.
I believe the above information shall help new DBAs in their activities. Please feel free to comment for any further information.