Tuesday 9 December 2014

How to Recreate The AWR( AUTOMATIC WORK LOAD ) Repository


Hi my sysaux table space getting full early. what i have to do, why it is growing too fast.

----Detection
You will get to know how its growing 
utlsyxsz.sql
awrinfo.sql

---- Snapshot Retention Check
col snap_interval format a30
col retention format a30
select snap_interval,
retention
from dba_hist_wr_control
/

----change interval and retention 
BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;

/


Solution Below step Follows.

The best way to deinstall/install AWR is as follows:

1. Disable AWR statistics gathering by setting the statistics level to basic as follows:

    Check settings for parameters as follows:

    sqlplus /nolog
    connect / as sysdba
    show parameter cluster_database
    show parameter statistics_level
    show parameter sga_target



    Or save the spfile before modifying:
    create pfile='/home/oracle/admin/dbs/init@.ora.20140122' from spfile;
     

In 10g and 11g , if  sga_target is not 0, then in pfile or spfile set the following parameters:

    The example below refers to spfile:
    alter system set shared_pool_size = 200m scope = spfile;
    alter system set db_cache_size = 300m scope = spfile;
    alter system set java_pool_size = 100 scope = spfile;
    alter system set large_pool_size = 50 scope = spfile;
    alter system reset sga_target scope= spfile;
    alter system reset memory_target scope= spfile;
    alter system reset memory_max_target scope=spfile;
    alter system set statistics_level=basic scope=spfile;

    -- Setting the parameter cluster_database only applicable
    -- in RAC environment
    alter system set cluster_database = false scope = spfile;

In 11g, you may need to set both SGA_TARGET and MEMORY_TARGET to 0:
    alter system set sga_target=0 scope= spfile;
    alter system set memory_target=0 scope= spfile;
     
otherwise database may not startup and will fail with the errors below:

    SQL> startup restrict
    ORA-01078: failure in processing system parameters
    ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
    ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET



2. Shutdown database and startup in restrict mode so that no transactions will occur while dropping the AWR repository:


    sqlplus /nolog
    connect / as sysdba
    shutdown immediate
    startup restrict

3. Drop and recreate the AWR objects

The following scripts drop AWR tables and then recreates them.
After recreating ,utlrp is run in order to validate all views and objects dependent on the AWR tables.

 -- On both 10g and 11g
    start ?/rdbms/admin/catnoawr.sql   # 10gR1 replace catawrtb.sql
    alter system flush shared_pool;
    start ?/rdbms/admin/catawrtb.sql   # 10gR2
    start ?/rdbms/admin/utlrp.sql
    --On 11g it is necessary to also run:
    start ?/rdbms/admin/execsvrm.sql

Check to see if all the objects are dropped after running catnoawr.sql

    SQL> select table_name,tablespace_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%' and tablespace_name=’SYSAUX’;

If there are still objects after running catnoawr.sql, drop them manually:  

    drop type AWR_OBJECT_INFO_TABLE_TYPE;
    drop type AWR_OBJECT_INFO_TYPE;
    drop table WRH$_PLAN_OPERATION_NAME;
    drop table WRH$_PLAN_OPTION_NAME;
    drop table WRH$_MV_PARAMETER;
    drop table WRH$_MV_PARAMETER_BL;
    drop table WRH$_DYN_REMASTER_STATS;
    drop table WRH$_PERSISTENT_QMN_CACHE;
    drop table WRH$_DISPATCHER;
    drop table WRH$_SHARED_SERVER_SUMMARY;
    drop table WRM$_WR_USAGE;
    drop table WRM$_SNAPSHOT_DETAILS;

If following errors occur, then recompile the objects:

     
    start ?/rdbms/admin/execsvrm.sql

    Fails with the following errors :

    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been
    invalidated
     

Compile again the package dbms_swrf_internal even if the objects appear valid:

    alter package dbms_swrf_internal compile;
    alter package dbms_swrf_internal compile body;

    

    

4)   Reset the parameters shared_pool_size,db_cache_size, java_pool_size ,large_pool_size, sga_target  statistics_level and cluster_database to original values.Also can reset the parameter from copy of the spfile:

create spfile from pfile='/home/oracle/admin/dbs/init@.ora.20140122' ;

 

5) Restart instance in normal mode:


        sqlplus /nolog
        connect / as sysdba
        shutdown immediate
        startup

6) Check invalid objects exists are not , if exists then please compile it manually
As we have run utlrp.sql, any invalid objects should already have been reported there


    spool /u01/objects.lst
    set pagesize500
    set linesize 100

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

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;

    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;

7) To take the AWR snapshots:


    exec dbms_workload_repository.create_snapshot;
    --wait for 5 min
    exec dbms_workload_repository.create_snapshot;

8) To create  AWR report run the script:
  
    start $ORACLE_HOME/rdbms/admin/awrrpt.sql

9) If further assistance is needed or errors arise while performing recreation of AWR, please open an sr.







Error:

SQL> @?/rdbms/admin/awrrpt.sql;

……
….
……
Using 1 for instance number
Declare
*
ERROR at line 1:
ORA-20200: Database/Instance 2028137520/I does not exist in
DBA_HIST_DATABASE_INSTANCE
ORA-06512: at line 23

Solution:

sqlplus ‘/ as sysdba’

select dbid from v$database;
select instance_number from v$instance;

define dbid=<’@dbid from v$database’>
define inst_num=<’instance_number from v$instance’>
SQL> @?/rdbms/admin/awrrpt.sql;

……
….
……
Using 1 for instance number
Declare
*
ERROR at line 1:
ORA-20200: Database/Instance 2028137520/I does not exist in
DBA_HIST_DATABASE_INSTANCE
ORA-06512: at line 23


No comments: