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:
Post a Comment