How to rebuild or install AWR for Oracle 10g/11g

If you encounter a large amount of AWR data in your daily work, you can clear it as follows:
SQL> select distinct dbid, db_name, instance_name from wrm $ _ database_instance;
DBID DB_NAME INSTANCE_NAME
-----------------------------------
2691876695 MYDB mydb

Run the following script to clear AWR data:
SQL> @? \ Rdbms \ admin \ catnoawr. SQL
(Omitted)

If you do not restart, clear the sharing pool.
Alter system flush shared_pool;

Run the reconstruction AWR script:
SQL> @? \ Rdbms \ admin \ catawrtb. SQL
(Omitted)

Session changed

SQL> @? \ Rdbms \ admin \ utlrp. SQL

(Omitted)

The following script must be run for Oracle 11g:
SQL> @? \ Rdbms \ admin \ execsvrm. SQL

SQL> exec dbms_workload_repository.create_snapshot;

The PL/SQL process is successfully completed.

If you do not re-compile the stored procedure, the following issues will be prompted when you execute the snapshot process:

SQL> exec dbms_workload_repository.create_snapshot;
BEGIN dbms_workload_repository.create_snapshot; END;

*
Row 3 has an error:
ORA-13516: AWR operation failed: AWR Schema not initialized
ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: In "SYS. DBMS_WORKLOAD_REPOSITORY", line 122
ORA-06512: In line 1

After the stored procedure is re-compiled, the stored procedure runs normally.

Run again every five minutes:
SQL> exec dbms_workload_repository.create_snapshot;

The PL/SQL process is successfully completed.
SQL> @? /Rdbms/admin/awrrpt. SQL

Note that in the RAC environment, you need to cancel the Cluster Parameters and modify them again after the execution is complete:
Alter system set cluster_database = false scope = spfile;

Of course, after reconstruction, we need to further check whether the related objects have exceptions. For objects with exceptions, re-compile:

Pool objects. lst
Set pagesize500
Set linesize 100

Select substr (comp_name,) comp_name, status, substr (version,) version
From dba_registry
Order by comp_name;

Select substr (object_name,) object_name, substr (owner,) owner, object_type
From dba_objects
Where status = 'invalid' order by owner, object_type;

Select owner, object_type, count (*)
From dba_objects
Where status = 'invalid'
Group by owner, object_type order by owner, object_type;

Spool off

Alter package <schema name>. <package_name> compile;
Alter package <schema name>. <package_name> compile body;
Alter view <schema name>. <view_name> compile;
Alter trigger <schema). <trigger_name> compile;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12