Hi Friends,
After long time writing post.
Today I was facing some issue with 11.2.0.3.0 standard edition as this is not having default feature for AWR report.
Data was missing in AWR report as this standard edition not having license for AWR report.
So the sultion for this as below.
The problem 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 pur default Oracle Standard Edition package. In terms of the licensing, we just need to license it as an optional extra so that we will actually be using it legally, and then we can go ahead and enable it.
While it may be an additional licensed item, it is actually installed on our DB by default, but is just not enabled. So, once we have cleared the licensing issue (With Oracle Support), we will be free to enable diagnostics by setting the new database parameter, CONTROL_MANAGEMENT_PACK_ACCESS. We can do this by running the following as a dba:
alter system set control_management_pack_access=”DIAGNOSTIC+TUNING” scope=both;
After running this command, our database will start accumulating diagnostic information.
Note that we 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.
Old snapshots will obviously continue to give the errors because they did not have the diagnostic information available at the time.
Meantime we can get statspack report , which is also giving some same data in text format and this is available in all Edition without any license issue.
So do we perform this stats pack creation is as below.
1. Create PERFSTAT as default tablespacce & PERFSTAT_TMP as temporary tablespace for PERFSTAT user.
2. Create user PERFSTAT and password will be PERFSTAT.
3. Run the /rdbms/admin/spcreate to create statspack report.
4. Run the /rdbms/admin/spauto to gather stats with auto job.
5. Now will restart DB to make sure we will have latest snaps here onwards.
Thanks!!!!
samadhandba 