0 Replies Last post: Feb 10, 2012 12:08 AM by Community Admin  
Novice 44 posts since
Mar 30, 2009
Currently Being Moderated

Feb 10, 2012 12:19 AM

Oracle Recreate Automatic Workload Repository

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

 

The statistics collected and processed by AWR include:

  • Object statistics that determine both access and usage statistics of database segments

  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL andV$SESS_TIME_MODEL views

  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views

  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time

  • ASH statistics, representing the history of recent sessions activity

 

Started noticing ORA-600 errors in the alert log.  Flushing system buffer cache did not resolve the issue.  On close examination, there were missing objects in WRH$_ACTIUVE_SESSION_HISTORY, and indication of AWR corruption.  Time to rebuild AWR.  Steps to re-build it below:

 

SQL> conn /as sysdba

Connected.

SQL> show parameter cluster_database;

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

cluster_database                    boolean    FALSE

cluster_database_instances          integer    1

 

SQL> show parameter statistics_level

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

statistics_level                    string      TYPICAL

SQL> show parameter sga_target

 

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                          big integer 0

-- Downtime starts from this point

SQL> shutdown immediate;

Datbase closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup restrict;

ORACLE instance started.

SQL>@$ORACLE_HOME/rdbms/admin/catnoawr.sql

SQL>alter system flush shared_pool;

SQL>@$ORACLE_HOME/rdbms/admin/catawrtb.sql

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

-- On 11g it is required to also run:

SQL>@$ORACLE_HOME/rdbms/admin/execsvrm.sql

SQL>shutdown immediate;

SQL>startup;

-- End of Downtime

-- Check for invalid objects, re-compile

 

SQL> spool objects.list

SQL> set pagesize 500

SQL> set linesize 100

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) versi

on from dba_registry order by comp_name;

 

COMP_NAME                                STATUS      VERSION

---------------------------------------- ----------- ----------

JServer JAVA Virtual Machine              VALID      11.1.0.7.0

OLAP Analytic Workspace                  VALID      11.1.0.7.0

OLAP Catalog                                    VALID      11.1.0.7.0

Oracle Data Mining                             VALID      11.1.0.7.0

Oracle Database Catalog Views           VALID      11.1.0.7.0

Oracle Database Java Packages          VALID      11.1.0.7.0

Oracle Database Packages and Types VALID      11.1.0.7.0

Oracle Expression Filter                      VALID      11.1.0.7.0

Oracle Multimedia                               VALID      11.1.0.7.0

Oracle OLAP API                               VALID      11.1.0.7.0

Oracle Rule Manager                          VALID      11.1.0.7.0

Oracle Text                                        VALID      11.1.0.7.0

Oracle Workspace Manager                VALID      11.1.0.7.0

Oracle XDK                                        VALID      11.1.0.7.0

Oracle XML Database                         VALID      11.1.0.7.0

Spatial                                               VALID      11.1.0.7.0

 

 

SQL> select substr(object_name,1,40) object_name, substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner, object_type;

 

no rows returned.

 

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;

 

no rows returned.

 

-- Take AWR snapshots

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

-- Wait 5- 10 minutes

 

SQL> exec dbms_workload_repository.create_snapshot;

More Like This

  • Retrieving data ...