How to list all databases with old COMPATIBLE or OPTIMIZER_FEATURES_ENABLE according to OH software release version

With end of 2015 will Oracle Database 11g out of normal support so lot of customers make an upgrade campaign in data centers. Every DBA go(should go at least) through a list of manual step to upgrade database (Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)).

This note state that after upgrade we have to set new value for COMPATIBLE parameter. I will recommend as well to look on another instance parameter related with database release software version - OPTIMIZER_FEATURES_ENABLE. This parameter is responsible for enabling a series of optimizer features based on an Oracle release number.

Sometimes after few months or years:) after upgrade you realize that some of your 1000 databases have wrong parameters which you had simply forgot to change...

Bearing that in my mind I use Configuration Search feature of Enterprise Manager to report all database configuration issue due differences between oracle home release version and instance compatible and optimizer_features_enable parameters. (More about Configuration Search you can find in nice post from Dave Wolf https://blogs.oracle.com/oem/entry/harness_the_power_of_configuration).

So here is the script:

set lines 300
set pages 100
col hostname format a30
col instance format a20
col parameter format a20
col value format a20
col dbversion format a20
  SELECT s5.target_name hostname,
         s2.target_name instance,
         s4.NAME parameter,
         s4.VALUE,
         s3.DBVERSION
    FROM CM$MGMT_DB_INIT_PARAMS_ECM s4,
         CM$MGMT_DB_DBNINSTANCEINFO_ECM s3,
         MGMT$TARGET s5,
         GC$ASSOC_INST_WITH_INVERSE s5a1,
         MGMT$ECM_CURRENT_SNAPSHOTS s2gen1,
         MGMT$TARGET s2
   WHERE     s2gen1.TARGET_GUID = s2.TARGET_GUID
         AND s2gen1.ECM_SNAPSHOT_ID = s3.ECM_SNAPSHOT_ID(+)
         AND s3.ECM_SNAPSHOT_ID = s4.ECM_SNAPSHOT_ID
         AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('compatible'))
         AND s5a1.SOURCE_ME_GUID = s2.TARGET_GUID
         AND s5a1.DEST_ME_GUID = s5.TARGET_GUID
         AND s5.TARGET_TYPE = 'host'
         AND s5a1.ASSOC_TYPE = 'hosted_by'
         AND s2.TARGET_TYPE = 'oracle_database'
         AND s2gen1.SNAPSHOT_TYPE = 'oracle_dbconfig'
         AND SUBSTR (s3.DBVERSION, 1, 4) != SUBSTR (s4.VALUE, 1, 4)
ORDER BY 2, 3

and output:

HOSTNAME                            INSTANCE             PARAMETER            VALUE                DBVERSION          
----------------------------------  -------------------- -------------------- -------------------- --------------------
adux0444.us01.ny02.example.com      ADB03XXI             compatible           11.1.0.7.0           11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXAB1E             compatible           10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXAB1K             compatible           10.2.0.4             11.2.0.4.0         
adux0302.us01.ny02.example.com      ADUXATDE             compatible           11.1.0.7             11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXAX2K             compatible           11.1.0.0.0           11.2.0.4.0         
adux0318.us01.ny02.example.com      ADUXAX2P             compatible           11.1.0.0.0           11.2.0.3.0         
adux0037.us01.ny02.example.com      ADUXBO2P             compatible           11.1.0.7             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXCL1E             compatible           10.2.0.3             11.2.0.4.0         
adux0444.us01.ny02.example.com      ADUXCL1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXCL1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXCL1P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXCL1Q             compatible           10.2.0.4.0           11.2.0.3.0         
adux0179.us01.ny02.example.com      ADUXCL2Q             compatible           10.2.0.4             11.2.0.3.0         
aduc0219.us01.ny02.example.com      ADUXCR6K_CR6K1       compatible           10.2.0.4             11.2.0.3.0         
aduc0220.us01.ny02.example.com      ADUXCR6K_CR6K2       compatible           10.2.0.4             11.2.0.3.0         
aduc0546.us01.ny02.example.com      ADUXCR6K_CR6K3       compatible           10.2.0.4             11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXCT1E             compatible           10.2.0.4.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXCT1K             compatible           10.2.0.2.0           11.2.0.4.0         
adux0044.us01.ny02.example.com      ADUXCT1P             compatible           10.2.0.2.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXCT1T             compatible           10.2.0.2.0           11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXCZ2E             compatible           10.2.0.3             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXCZ2K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXCZ2P             compatible           10.2.0.4             11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXCZ2Q             compatible           10.2.0.4             11.2.0.4.0         
aduc0233.us01.ny02.example.com      ADUXEH3E             compatible           10.2.0.4.0           11.2.0.2.0         
adux0044.us01.ny02.example.com      ADUXEH3P             compatible           11.1.0.7.0           11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXEV3K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXEV3P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0157.us01.ny02.example.com      ADUXEV4P             compatible           10.2.0.4.0           11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXFI1I             compatible           10.2.0.3             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXFI1K             compatible           10.2.0.3             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXFI1P             compatible           10.2.0.3             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXFI2E             compatible           10.2.0.3             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXFK3E             compatible           11.1.0.0             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXFK3K             compatible           11.1.0.0             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXFK3P             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXFR4E             compatible           10.2.0.3             11.2.0.1.0         
adux0280.us01.ny02.example.com      ADUXGV8K             compatible           10.2.0.4             11.2.0.2.0         
adux0350.us01.ny02.example.com      ADUXIB3I             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB3K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXIB3P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB3S             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB4I             compatible           11.1.0.7.0           11.2.0.4.0         
adux0350.us01.ny02.example.com      ADUXIB7K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC1E             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2E             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2I             compatible           10.2.0.4             11.2.0.4.0         
adux0248.us01.ny02.example.com      ADUXIC2K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXIC2P             compatible           10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXKM1K             compatible           10.2.0.2.0           11.2.0.2.0         
adux0037.us01.ny02.example.com      ADUXKM1P             compatible           10.2.0.2.0           11.2.0.2.0         
adux0249.us01.ny02.example.com      ADUXMM1K             compatible           10.2.0.4             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXMM1P             compatible           10.2.0.4             11.2.0.4.0         
adux0319.us01.ny02.example.com      ADUXOA1K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0318.us01.ny02.example.com      ADUXOA1P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1E             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXOC1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXOC1P             compatible           10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXPC1K             compatible           10.2.0.4             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXPC1P             compatible           10.2.0.4             11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXSB5K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0044.us01.ny02.example.com      ADUXSB5P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0043.us01.ny02.example.com      ADUXSB5Q             compatible           11.1.0.7.0           11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXSM5P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0066.us01.ny02.example.com      ADUXSM5Q             compatible           11.1.0.7.0           11.2.0.2.0         
adux0065.us01.ny02.example.com      ADUXSM6P             compatible           11.1.0.7.0           11.2.0.3.0         
adux0445.us01.ny02.example.com      ADUXSO1I             compatible           10.2.0.4.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXSO1K             compatible           10.2.0.4.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXSP1K             compatible           10.2.0.2.0           11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXTC1I             compatible           10.2.0.2             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXTM1K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXTM2K             compatible           11.1.0.7.0           11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXUA6E             compatible           10.1.0.0.0           10.2.0.5.0         
adux0165.us01.ny02.example.com      ADUXUK8E             compatible           10.2.0.3.0           11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK8K             compatible           10.2.0.3.0           11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK8P             compatible           11.1.0.7.0           11.2.0.4.0         
adux0249.us01.ny02.example.com      ADUXVA1K             compatible           10.2.0.3.0           11.2.0.2.0         
adux0094.us01.ny02.example.com      ADUXVT2K             compatible           11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXVT2P             compatible           11.1.0.7.2           11.2.0.3.0         
adux0096.us01.ny02.example.com      ADUXWQ1E             compatible           11.1.0.0             11.2.0.4.0         
adux0445.us01.ny02.example.com      ADUXWQ1I             compatible           10.2.0.4             11.2.0.4.0         
adux0096.us01.ny02.example.com      ADUXWQ1K             compatible           10.2.0.4             11.2.0.4.0         
adux0065.us01.ny02.example.com      ADUXWQ1P             compatible           10.2.0.4             11.2.0.4.0         

84 rows selected.

Change this line to get list of optimizer parameters across your enviroment 


from
AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('compatible'))
to 
AND (NLS_UPPER (s4.NAME) LIKE NLS_UPPER ('optimizer_features_enable'))


Output:

HOSTNAME                       INSTANCE             PARAMETER                    VALUE                DBVERSION          
------------------------------ -------------------- ---------------------------- -------------------- --------------------
adux0065.us01.ny02.example.com      ADUXBB1P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2E             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2K             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXBO2P             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0094.us01.ny02.example.com      ADUXBO2T             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
aduc0219.us01.ny02.example.com      ADUXCR6K_CR6K1       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
aduc0220.us01.ny02.example.com      ADUXCR6K_CR6K2       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
aduc0546.us01.ny02.example.com      ADUXCR6K_CR6K3       optimizer_features_enable    10.2.0.4             11.2.0.3.0         
adux0044.us01.ny02.example.com      ADUXEH3P             optimizer_features_enable    10.2.0.4             11.2.0.3.0         
adux0249.us01.ny02.example.com      ADUXIR1K             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0157.us01.ny02.example.com      ADUXIR1P             optimizer_features_enable    11.1.0.7             11.2.0.4.0         
adux0037.us01.ny02.example.com      ADUXKM1P             optimizer_features_enable    10.2.0.5             11.2.0.2.0         
adux0113.us01.ny02.example.com      ADUXUK3P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0113.us01.ny02.example.com      ADUXUK4P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK5K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK5P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0112.us01.ny02.example.com      ADUXUK6P             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK7K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         
adux0165.us01.ny02.example.com      ADUXUK9K             optimizer_features_enable    10.2.0.4             11.2.0.4.0         

19 rows selected.



PS. Using emcli you can easily export your reports from dev environment to production

emcli export_report -title="Report_Diff_Ora" -owner="SYSMAN" -output_file=/l/ora/reports/Report_Diff_Ora.xml

CloudControl: Report for sizing memory/sga across enterprise

In my previous post I showed how to create tnsnames file from grid/cloud repository. I think OMS repository is very useful tool but at the same time too oft underestimated.
Here is my story: My client is running numbers of databases on AIX. I was asked to show distribution of memory across LPARs and Oracle Instances. Yes, sure, you can take calculator and go through your environment. But I am too lazy... Motivation for this task came after watching a seminar with Christo Kutrovsky from Pythian(https://www.youtube.com/watch?v=5HyjTxD4soA) about benefits of RAM memory.
Based on SYSMAN.MGMT_ECM_HW you can build simple query to get hardware configuration:

COL HOSTNAME FORMAT A20
select HOSTNAME,CPU_COUNT,MEMORY_SIZE_IN_MB/1024 MEM_GB from SYSMAN.MGMT_ECM_HW order by hostname;
HOSTNAME              CPU_COUNT     MEM_GB
-------------------- ---------- ----------
utest32                       3         64
integ22                       2         16
integ32                       3         16
unprod32                      3         48
...
unprod62                      8         64
unprod72                      2         24
ucont32                       3         32
ucont32                       3         24
ucont42                       2         16
ucont52                       2         16

But challenge is to get for example SGA size for each instance on every host. You can choose between SYSMAN.MGMT_DB_INIT_PARAMS_ECM and SYSMAN.MGMT_DB_SGA_ECM. Here is my script:
SET pages 100
SET lines 300
col hostname format a20
col db_name format a10
col "Total SGA" format a20
break ON hostname SKIP page ON hostname
COMPUTE sum OF "MB" ON hostname
 SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
 sga.sganame AS "Total SGA",
 sga.sgasize AS "MB"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  ORDER BY
    c.host_name,c.entity_name;
Output:
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest32                        BBAIS      Total SGA (MB)              864
                               BBAIS12    Total SGA (MB)              864
                               BUSYI      Total SGA (MB)             3472
                               BUSYT      Total SGA (MB)             3136
                               DWRT       Total SGA (MB)             3610
                               DWRT12     Total SGA (MB)             3504
                               FEINP8I    Total SGA (MB)              696
                               FEINP8T    Total SGA (MB)              955
                               HRDWRT     Total SGA (MB)             2819
                               HRDWRT12   Total SGA (MB)             2880
                               OPSWT      Total SGA (MB)              696
                               PAIST      Total SGA (MB)             3098
                               OPTRALT    Total SGA (MB)              788
                               PRFT6T     Total SGA (MB)              496
                               RMAN1T     Total SGA (MB)             1559
                               RMANBK22   Total SGA (MB)             1376
******************************                                 ----------
sum                                                                 30813
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest35                        FEINT      Total SGA (MB)              562
******************************                                 ----------
sum                                                                   562
...
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod62                       DWRP       Total SGA (MB)            33137
                               RMAN2P     Total SGA (MB)             1543
******************************                                 ----------
sum                                                                 34680
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod72                       HRDWRP     Total SGA (MB)             4970
                               RMAN3P     Total SGA (MB)             1911
******************************                                 ----------
sum                                                                  6881
I made as well a percentage distribution within SGA:
col hostname format a20
col db_name format a10
SET pages 100
SET lines 300
break ON hostname SKIP page ON hostname
COMPUTE sum OF "Total SGA(MB)" ON hostname
SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
    round(sga.sgasize) AS "Total SGA(MB)",
    round(sp.sgasize/sga.sgasize*100) AS "SharedPool(%)",
    round(bc.sgasize/sga.sgasize*100) AS "BufferedCache(%)",
    round(rb.sgasize/sga.sgasize*100/1024) AS "RedoBuffers(%)",
    round(lp.sgasize/sga.sgasize*100/1024) AS "LargePool(%)",
    round(jp.sgasize/sga.sgasize*100) AS "JavaPool(%)"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_db_sga_ecm sp,
    sysman.mgmt_db_sga_ecm bc,
    sysman.mgmt_db_sga_ecm rb,
    sysman.mgmt_db_sga_ecm lp,
    sysman.mgmt_db_sga_ecm jp,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.snapshot_guid = sp.ecm_snapshot_id
  AND s.snapshot_guid = bc.ecm_snapshot_id
  AND s.snapshot_guid = rb.ecm_snapshot_id
  AND s.snapshot_guid = lp.ecm_snapshot_id
  AND s.snapshot_guid = jp.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  AND sp.sganame='Shared Pool (MB)'
  AND bc.sganame='Buffered Cache (MB)'
  AND rb.sganame='Redo Buffers (KB)'
  AND lp.sganame='Large Pool (KB)'
  AND jp.sganame='Java Pool (MB)'
  ORDER BY
    c.host_name,c.entity_name;
Output:
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest32              BBAIS                864            69                8              1            2          13
                     BBAIS12              864            69               19              1            2           1
                     BUSYI               3472            60               30              6            2           0
                     BUSYT               3136            62               28              7            2           1
                     DWRT                3610            24               43             29            2           0
                     DWRT12              3504            44               47              0            1           1
                     FEINP8I              696            35               30             30            3           1
                     FEINP8T              955            42               27             23            5           2
                     HRDWRT              2819            39               19             37            2           1
                     HRDWRT12            2880            18               74              0            1           1
                     OPSWT                696            35               32             30            2           1
                     PAIST               3098            60               36              0            1           1
                     OPTRALT              788            58               27              3            2           1
                     PRFT6T               496            79               11              0            2           2
                     RMAN1T              1559            45               36             14            3           1
                     RMANBK22            1376            33               55              1            6           1
********************            -------------
sum                                     30813
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest35              FEINT                562            89                9              1            0           1
********************            -------------
sum                                       562
...
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
unprod82n0           OPTRALP             1449            49               30             15            0           1
                     PRFT6P              1509            47               34              2            0           1
                     RMAN9P              1575            58               40              0            1           1
********************            -------------
sum                                      4533
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
ucont25              FEINQ                562            89                9              1            0           1
********************            -------------