Monday, 28 September 2015

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
Posted by at
Labels: , , , ,

3 comments:

  1. Eric van der Spoel4 September 2018 at 05:38

    Great sql! Keep in mind that if you use Data Guard the standby database info is not always in CM$MGMT_DB_DBNINSTANCEINFO_ECM yet (only when it has been primary for a while) so, you will be missing some entries. No problem, just good to know.

    Eric

    ReplyDelete
  2. Eric van der Spoel5 September 2018 at 05:36

    To get also the missing entries, try this sql:
    SELECT DISTINCT tbl_sid.sid "DB Name", tbl_sid.targetnaam, tbl_tar.host_name "Host Name", tbl_comp.compatible "Compatible", tbl_ver.version "DB Version"
    FROM (SELECT s.target_guid, UPPER (s.property_value) AS sid, s.target_name as targetnaam
    FROM sysman.mgmt$target_properties s
    WHERE s.property_name = 'SID') tbl_sid,
    (SELECT s.target_guid, s.value AS compatible
    FROM sysman.mgmt$db_init_params s
    WHERE s.name = 'compatible') tbl_comp,
    (SELECT s.target_guid, s.property_value AS version
    FROM sysman.mgmt$target_properties s
    WHERE s.property_name IN ('Version')) tbl_ver,
    sysman.mgmt$target_properties tbl_main,
    sysman.mgmt$target tbl_tar
    WHERE tbl_main.target_guid = tbl_sid.target_guid(+)
    AND tbl_main.target_guid = tbl_tar.target_guid(+)
    AND tbl_main.target_guid = tbl_ver.target_guid(+)
    AND tbl_main.target_guid = tbl_comp.target_guid(+)
    AND tbl_tar.target_type = 'oracle_database'
    ORDER BY 1;

    Eric

    ReplyDelete
  3. Kuba (Jakub Szepietowski)4 October 2018 at 00:54

    Thanks Eric for the good tip. Having different compatible parameter between primary and standby(for example standby 12.1.0.0.0 and primary 11.2.0.0.0) can lead to ... hmmm being not able to perform switchback after switchover. So another surprise:)

    ReplyDelete

Subscribe to: Post Comments (Atom)