2 Replies Latest reply on Sep 14, 2018 8:06 AM by Jonathan Rademacher

    Question regarding Oracle.OracleRDBMS_Options.OracleOptionsandPacks

    Jonathan Rademacher

      Hi guys, it's time for another question:

       

      In our Company we're tracking the usage of Oracle Options via Discovery. For an upcoming license audit we analysed how e.g. the Tuning Pack "Used" attribute is gathered. I came to the conclusion that it's the Oracle.OracleRDBMS_Options.OracleOptionsandPacks pattern which is responsible for creating the Details and the usage status. The Information is collected using the  "OptionPackFunctions.run_option_usage_sql_query(db_si, options_and_packs);" methode.

      This Methode is defined in "Oracle.OracleRDBMS_Options" pattern module and contains the following lines of code:

       

       

       

      1017         if options_and_packs_usage_results then

      1018             for row in options_and_packs_usage_results do

      1019                 is_enabled := false;

      1020

      1021                 if row.feature_being_used and not row.feature_being_used in ['None', '0'] then // means that option/pack is installed/enabled

      1022                     is_enabled := true;

      1023                 end if;

      1024

      1025                 version := '';

      1026                 if row.version then

      1027                     version := row.version;

      1028                 end if;

      1029

      1030                 is_used := false;

      1031                 if row.usage and row.usage = 'CURRENT_USAGE' then

      1032                     is_used := true;

      1033                 end if;

      1034

      1035                 if row.used then

      1036                      is_used := row.usage;

      1037                 end if;

      1038

      1039                 features := [];

      1040                 if is_enabled then

      1041                     list.append(features, row.feature_being_used);

      1042                 end if;

      1043                 log.debug("Option/Pack name: '%row.optionpackname%', enabled: '%is_enabled%', version: '%version%', used: '%row.used%'");

      1044

      1045                 if not row.optionpackname in options_and_packs then

      1046                     options_and_packs[row.optionpackname] := [is_enabled, version, is_used, features];

      1047                 // Update the pack information

      1048                 else

      1049                     if is_enabled then

      1050                         if not row.feature_being_used in options_and_packs[row.optionpackname][3] then

      1051                             list.append(options_and_packs[row.optionpackname][3], row.feature_being_used);

      1052                         end if;

      1053                     end if;

      1054                 end if;

      1055             end for;

      1056         end if;

       

      We enabled the Feature "SQL Advisor" for a Oracle 12 database which is modeled as third Feature in the Tuning packs Detail list. Although the SQL Integration result returns the Feature "SQL Profile" as "Used", the Tuning pack is still marked Used = "False". This is caused by lines 1045 and following. It's the first Feature_being_used that sets the is_used Attribute for the whole Pack/Option.

      According to our license Management an Oracle Pack/Option must be licensed even if only one Feature contained in the pack is in use. So with this code we cannot guarantee that we only use the Features we're actually allowed to use. A slight modification keeps this up-to-date:

       

       

      1045                 if not row.optionpackname in options_and_packs then

      1046                     options_and_packs[row.optionpackname] := [is_enabled, version, is_used, features];

      1047                     log.debug("Custom Log ORACLE RDBMS: Case A: feature added to options_and_packs named %row.optionpackname% with feature %row.feature_being_used%");

      1048                 // Update the pack information

      1049                 else

      1050                     if is_enabled then

      1051                         if not row.feature_being_used in options_and_packs[row.optionpackname][3] then

      1052                             list.append(options_and_packs[row.optionpackname][3], row.feature_being_used);

      1053                             if options_and_packs[row.optionpackname][2] = false and is_used = true then

      1054                                options_and_packs[row.optionpackname][2] := is_used;

      1055                                log.debug("Custom Log ORACLE RDBMS: Updated Usage Attibute of Pack %row.optionpackname%");

      1056                             end if;

      1057                             log.debug("Custom Log ORACLE RDBMS: Case B: Feature already added to options_and_packs. Added feature_being_used '%row.feature_being_used%.'");

      1058                         end if;

      1059                     end if;

      1060                 end if;

       

      Would you please update the "else" condition so the pattern changes the usage state to it's real value depending on the Features and their usages that belong to the Pack? Is that a known defect or did i just not understand how it works?

      I scanned the host again and now - with my modifications - the Tuning Pack attribute "Used" is marked as "True", because the SQL Profile is used while SQL Access Advisor, SQL Monitoring and Tuning Pages and SQL Tuning Advisor are not.

       

      Best Regards - J

        • 1. Re: Question regarding Oracle.OracleRDBMS_Options.OracleOptionsandPacks
          Andrew Waters

          I have created a defect for the TKU team (DRDC1-11983)

          • 2. Re: Question regarding Oracle.OracleRDBMS_Options.OracleOptionsandPacks
            Jonathan Rademacher

            Hi Andrew,

             

            there's a second thing that should be fixed in the next TKU update regarding the pattern mentioned above:

             

            Now all PDB's will be discovered successfully including their Options. But: DB's without CDB$ROOT do not get Options and packs anymore. This is caused by the same lines of code. There is no Attribute "row.feature_being_used" in the Integration results for the usual "OracleQueries.options_and_packs_usage"-Function. They only exist in "OracleQueries.options_and_packs_usage_for_pluggable_database".

             

            So there needs to be the second case, i used the following code to get back all the Options for all Oracle DBs where we have credentials:

             

            if options_and_packs_usage_results then
                    for row in options_and_packs_usage_results do
               //Switch Case: C1 it's a PDB with optionpackname and feature_being_used attribute or C2 it's a simple Oracle DB
               if row.optionpackname then
                is_enabled := false;

                if row.feature_being_used and not row.feature_being_used in ['None', '0'] then // means that option/pack is installed/enabled
                 is_enabled := true;
                end if;

                version := '';
                if row.version then
                 version := row.version;
                end if;

                is_used := false;
                if row.usage and row.usage = 'CURRENT_USAGE' then
                 is_used := true;
                end if;

                if row.used then
                  is_used := row.usage;
                end if;

               
                features := [];
                if is_enabled then
                 list.append(features, row.feature_being_used);
                end if;
                log.debug("Option/Pack name C1: '%row.optionpackname%', Detailed Name: '%row.feature_being_used%' enabled: '%is_enabled%', version: '%version%', used: '%row.used%' currently used: '%row.currently_used%'");

                if not row.optionpackname in options_and_packs then
                 options_and_packs[row.optionpackname] := [is_enabled, version, is_used, features];
                 log.debug("Custom Log ORACLE RDBMS: Case A: feature added to options_and_packs named %row.optionpackname% with feature %row.feature_being_used%");
                // Update the pack information
                else
                 if is_enabled then
                  if not row.feature_being_used in options_and_packs[row.optionpackname][3] then
                   list.append(options_and_packs[row.optionpackname][3], row.feature_being_used);
                   if options_and_packs[row.optionpackname][2] = false and is_used = true then
                      options_and_packs[row.optionpackname][2] := is_used;
                      log.debug("Custom Log ORACLE RDBMS: Updated Usage Attibute of Pack %row.optionpackname%");
                   end if;
                   log.debug("Custom Log ORACLE RDBMS: Case B: Feature already added to options_and_packs. Added feature_being_used '%row.feature_being_used%.'");
                  end if;
                 end if;
                end if;
               elif row.product then
              
                is_enabled := false;
               
                if not row.product in ['None','0'] then
                 is_enabled := true;
                end if;
               
                version :='';
                if row.version then
                 version := row.version;
                end if;
               
                is_used := false;
                if row.usage and row.usage = 'CURRENT_USAGE' then
                 is_used := true;
                end if;
               
                features := [];
                if is_enabled then
                 list.append(features, row.product);
                end if;
                log.debug("Option/Pack name C2: '%row.product%', enabled: '%is_enabled%', used: '%row.usage%' ");
               
                if not row.product in options_and_packs then
                 options_and_packs[row.product] := [is_enabled, version, is_used, features];
                else
                 if is_enabled then
                  if not row.product in options_and_packs[row.product][3] then
                   list.append(options_and_packs[row.product][3], row.product);
                   if options_and_packs[row.product][2] = false and is_used = true then
                    options_and_packs[row.optionpackname][2] := is_used;
                   end if;
                  end if;
                 end if;
                end if;
              
               end if;
                
              
               
                    end for;
                end if;

                return options_and_packs;

             

            Feel free to find a better way to distinguish the 2 different integration results attributes correctly.

             

            Best Regards & have a nice weekend - J