5 Replies Latest reply on Feb 9, 2012 4:50 PM by dayneo

    How to run AWR report in 11g Standard Edition

    430060
      Guys

      I need to know how to run AWR report in 11g SE. When I run awrrpt.sql I get some warning messages on top of report and no data in report.

      I know that It is part of Performance Pack and not available in Standard Edition.

      In 10g SE, I am able to run AWR manually. but not in 11g SE

      Here is what I get when I run report.

      WARNING (-20023) ORA-20023: Missing start and end values for time model stat: parse time elapsed WARNING (-20023) ORA-20023: Missing start and end values for time model stat: DB CPU WARNING (-20016) ORA-20016: Missing value for SGASTAT: free memory WARNING (-20016) ORA-20016: Missing value for SGASTAT: free memory WARNING (-20009) ORA-20009: Missing System Statistic logons current WARNING (-20009) ORA-20009: Missing System Statistic logons current WARNING (-20009) ORA-20009: Missing System Statistic opened cursors current WARNING (-20009) ORA-20009: Missing System Statistic opened cursors current WARNING (-20023) ORA-20023: Missing start and end values for time model stat: sql execute elapsed time WARNING (-20008) ORA-20008: Missing Init.ora parameter undo_management WARNING (-20008) ORA-20008: Missing Init.ora parameter db_block_size WARNING (-20016) ORA-20016: Missing value for SGASTAT: log_buffer WARNING (-20023) ORA-20023: Missing start and end values for time model stat: DB time WARNING (-20008) ORA-20008: Missing Init.ora parameter timed_statistics WARNING (-20008) ORA-20008: Missing Init.ora parameter timed_statistics WARNING (-20008) ORA-20008: Missing Init.ora parameter statistics_level WARNING (-20008) ORA-20008: Missing Init.ora parameter statistics_level WARNING (-20008) ORA-20008: Missing Init.ora parameter sga_target WARNING (-20008) ORA-20008: Missing Init.ora parameter pga_aggregate_target WARNING (-20023) ORA-20023: Missing start and end values for time model stat: background cpu time WARNING (-20023) ORA-20023: Missing start and end values for time model stat: background elapsed time WARNING (-20023) ORA-20023: Missing start and end values for time model stat: connection management call elapsed time WARNING (-20016) ORA-20016: Missing value for SGASTAT: buffer_cache WARNING (-20016) ORA-20016: Missing value for SGASTAT: buffer_cache WARNING: Since the DB Time is less than one second, there was minimal foreground activity in the snapshot period. Some of the percentage values will be invalid.


      Any suggesstion/workaround?
        • 1. Re: How to run AWR report in 11g Standard Edition
          Naveed Yameen Khakoo
          DBA-08859,

          Have you verified if the user - you're trying to execute the job with - has ADVISOR system privilege? One you're done with that, refer to the following post to see if it helps:

          http://arjudba.blogspot.com/2008/08/how-to-invoke-collect-and-run-awr-and.html

          Regards,
          Naveed.
          • 2. Re: How to run AWR report in 11g Standard Edition
            430060
            Thanks Naveed,

            Yes , I am running this as SYS. Also, I read that link you sent, but no help.

            I am still getting same error
            • 3. Re: How to run AWR report in 11g Standard Edition
              dayneo
              Hi there,

              Edited by: dayneo on Feb 9, 2012 8:31 AM:
              To anyone still reading down the thread, pay attention to Hans Forbrich's comments below.
              The solution of setting control_management_pack_access to anything other than NONE for Oracle Standard edition is effectively invalid. The Diagnostic and tuning packs can only be purchased as an optional extra with Enterprise edition.
              End edit

              The problem you are experiencing is caused by the fact that AWR is part of the DIAGNOSTIC and TUNING packs of Oracle 11g, which as of 11g is no longer a standard feature of the database but rather an optional extra which must be licensed in addition to your default Oracle Standard Edition package. <-- incorrect, that is: it can only be added in to 11g Enterprise Edition.

              Once you have cleared the licensing issue, you will be free to enable diagnostics by setting the new database parameter, CONTROL_MANAGEMENT_PACK_ACCESS. You can do this by running the following as a dba:
              <pre>alter system set control_management_pack_access="DIAGNOSTIC+TUNING" scope=both;</pre>

              After running this command, your database will start accumulating diagnostic information. Note that you will need to wait until new snapshots are created in which the new diagnostic information will be available. I found that although most of the information became available after setting CONTROL_MANAGEMENT_PACK_ACCESS, it was only once the database was restarted that all of the information became available. I have seen many people reporting similar behavior.

              Old snapshots will obviously continue to give the errors because they did not have the diagnostic information available at the time.

              Check my blog entry for more and in particular, links to the licensing information and other resources.
              • 4. Re: How to run AWR report in 11g Standard Edition
                Hans Forbrich
                dayneo wrote:
                The problem you are experiencing is caused by the fact that AWR is part of the DIAGNOSTIC and TUNING packs of Oracle 11g, which as of 11g is no longer a standard feature of the database but rather an optional extra which must be licensed in addition to your default Oracle Standard Edition package.
                Hmmmm. I don't recall the Diag Pack EVER being part of the base system license. Even when it was the PERFORMANCE Pack, back in 8i, it was an additional cost option.

                The fact that it was turned on by default has little to do with whether you could legally use it.

                >
                Once you have cleared the licensing issue, you will be free to enable diagnostics by setting the new database parameter, CONTROL_MANAGEMENT_PACK_ACCESS. You can do this by running the following as a dba:
                <pre>alter system set control_management_pack_access="DIAGNOSTIC+TUNING" scope=both;</pre>
                Clearing the license issue might be a challenge. From Oracle's license manual at http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CIHGFIAF we read "The management packs can be purchased only with Enterprise Edition. "

                It is one of the reasons many of us still fall back to the predecessor of the AWR - the StatsPack we "all" know and love.
                • 5. Re: How to run AWR report in 11g Standard Edition
                  dayneo
                  Hans Forbrich wrote:
                  Clearing the license issue might be a challenge. From Oracle's license manual at http://docs.oracle.com/cd/E11882_01/license.112/e10594/options.htm#CIHGFIAF we read "The management packs can be purchased only with Enterprise Edition. "
                  ah... yes... quite right!
                  And that is why you should always read licensing information from beginning to end without skipping a single line or fine print and without using the hyperlinks provided.

                  Interesting thing is that this one line makes all of the "set database parameter solutions" effectively invalid... I am definitely going to go back to the 10g docs to verify. One question though.... the AWR functionality was available by default when installing Oracle 10g Standard edition. What happened there?

                  I think I get it now:
                  The Automatic Workload Repository is installed by default, is running by default, is available by default. But you are actually not allowed to use it in any way shape or form. Not even allowed to select from it's views. Not the most intelligent implementation of feature "licensing" that I have ever seen. Or perhaps that was Oracles intention...?
                  That is a really easy one to mess up.

                  Burleson's a genius: http://www.dba-oracle.com/oracle_news/2004_2_17.htm