Usage and Storage Management of SYSAUX
tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER [ID 329984.1]
Question:
Can Tablespace Point-In-Time recovery (TSPITR)
be performed on the SYSAUX tablespace?
Answare :
No, the point in time recovery cannot be
performed on the SYSAUX tablespace.
Pre steps:
SQL> @$ORACLE_HOME/rdbms/admin/AWRINFO.sql
Step 2:
SQL>
SELECT occupant_name, space_usage_kbytes FROM V$SYSAUX_OCCUPANTS;
SQL> select * from DBA_HIST_WR_CONTROL;
SQL> select occupant_name,
space_usage_kbytes from v$sysaux_occupants;
SQL >
set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT
999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item",
space_usage_kbytes/1048576
"Space Used (GB)",
schema_name
"Schema",
move_procedure
"Move Procedure"
FROM v$sysaux_occupants
ORDER BY 1
/
1. Investigate how old stats history we are keeping:
SQL>
select dbms_stats.get_stats_history_retention from dual;
SQL>
select dbms_stats.get_stats_history_availability from dual;
SQL>
select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
2. If the data is over 31 days we need to drill down to
object levels:
a. set
retention on history table to 31 days.
SQL>
exec dbms_stats.alter_stats_history_retention(31);
b.
SQL> col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
select sum(bytes/1024/1024) Mb,
segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order
by 1 asc;
c. exec
DBMS_STATS.PURGE_STATS(SYSDATE-number of days you want to keep the data);
exec DBMS_STATS.PURGE_STATS(SYSDATE-31);
--> most likely it will fail.
3. Apply RDMBS patch 14373728
Note: Before downloading the Ptach for compatible version Please refer (ID 224346.1)
Note: Before downloading the Ptach for compatible version Please refer (ID 224346.1)
1.
download and apply patch 14373728 for appropiate db version
2. Set
"autoextend on" on temp datafiles.
3.
spool the output from the post-patch install tasks,
4.
review the log to make sure that the script ran okay
5. Wait
for the retention period(31 days) to lapse, then check if p_old has been
dropped
6. If
not, then perform a manual drop in case it was taking longer for the p_old to
drop because of its size
This
patch creates a table, WRI$_OPTSTAT_HISTHEAD_HISTORY, with partition. So, next time through it is
easier for oracle to look at the partition and drop it
according
to retention period. Currently the table is huge and takes time to recreate
table with partition. So, it is necessary
to set
autoextend on on temp datafiles. For the successful execution on post-patch
steps should create table with partion. If try to
get ddl
for the table we should see below:
SQL> col subobject_name format a15;
SQL> alter session set nls_date_format
='MON-DD-YY HH24:MI';
SQL>
select object_name, subobject_name, object_type, status, last_ddl_time
from dba_objects where object_name like
'%WRI$_OPTSTAT%'
order by 1;
In the result we will see few tables are
created with partition:
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_TYPE STATUS LAST_DDL_TIME
----------------------------------------
------------------------------ ------------------- ------- ---------------
I_WRI$_OPTSTAT_AUX_ST
INDEX VALID 17-JUL-10
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
INDEX VALID 17-JUL-10
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
INDEX VALID 17-JUL-10
WRI$_OPTSTAT_SYNOPSIS$ SYS_SUBP168 TABLE SUBPARTITION VALID
12-MAY-12
WRI$_OPTSTAT_SYNOPSIS$ SYS_SUBP169 TABLE SUBPARTITION VALID
12-MAY-12
WRI$_OPTSTAT_SYNOPSIS_HEAD$
TABLE VALID 17-JUL-10
WRI$_OPTSTAT_SYNOPSIS_PARTGRP
TABLE VALID 17-JUL-10
WRI$_OPTSTAT_TAB_HISTORY
TABLE VALID 17-JUL-10
SQL> select
dbms_metadata.get_ddl('TABLE','WRI$_OPTSTAT_HISTHEAD_HISTORY','SYS') from dual;
DBMS_METADATA.GET_DDL('TABLE','WRI$_OPTSTAT_HISTHEAD_HISTORY','SYS')
--------------------------------------------------------------------------------
CREATE TABLE
"SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY"
( "OBJ#" NUMBER NOT NULL ENABLE,
"INTCOL#" NUMBER NOT NULL ENABLE,
"SAVTIME" TIMESTAMP (6) WITH TIME
ZONE,
"FLAGS" NUMBER,
"NULL_CNT" NUMBER,
"MINIMUM" NUMBER,
"MAXIMUM" NUMBER,
"DISTCNT" NUMBER,
"DENSITY" NUMBER,
"LOWVAL" RAW(32),
"HIVAL" RAW(32),
"AVGCLN" NUMBER,
"SAMPLE_DISTCNT" NUMBER,
"SAMPLE_SIZE" NUMBER,
"TIMESTAMP#" DATE,
"SPARE1" NUMBER,
"SPARE2" NUMBER,
"SPARE3" NUMBER,
"SPARE4" VARCHAR2(1000),
"SPARE5" VARCHAR2(1000),
"SPARE6" TIMESTAMP (6) WITH TIME ZONE,
"EXPRESSION" CLOB,
"COLNAME" VARCHAR2(30),
"SAVTIME_DATE" DATE GENERATED ALWAYS AS
(TRUNC("SAVTIME")
) VIRTUAL VISIBLE
) PCTFREE 1 PCTUSED
40 INITRANS 1 MAXTRANS 255 NOCOMPRES
S LOGGING
STORAGE(INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483
645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE
"SYSAUX"
LOB
("EXPRESSION") STORE AS BASICFILE (
TABLESPACE
"SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 21474
83645
PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ENA
BLE ROW MOVEMENT
Note: To verify the index for rebuild :
SQL> select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from
dba_segments where tablespace_name = 'SYSAUX'
and segment_name like '%OPT%' and segment_type='INDEX'
4. Old partition should be dropped itself. If not wait for
retention period and check if the size of the index. If it is not dropped,
dropped them manually:
-- drop
indexes: if they get timeout they need to repeat these statements until they
can be dropped.
drop
index i_wri$_optstat_hh_obj_icol_st;
drop
index i_wri$_optstat_hh_st;
-- drop
partition p_old
alter
table WRI$_OPTSTAT_HISTHEAD_HISTORY drop partition P_OLD;
--
recreate the indexes
create
unique index i_wri$_optstat_hh_obj_icol_st on wri$_optstat_histhead_history
(obj#, intcol#, savtime, colname) tablespace sysaux;
create
index i_wri$_optstat_hh_st on wri$_optstat_histhead_history (savtime)
tablespace sysaux;
>>
repeating similar procedure for histogram history table:
-- drop
indexes: if they get timeout they need to repeat these statements until they
can be dropped.
drop
index i_wri$_optstat_h_obj#_icol#_st;
drop
index i_wri$_optstat_h_st;
-- drop
partition p_old
alter
table wri$_optstat_histgrm_history drop partition P_OLD;
--
recreate indexes
create
index i_wri$_optstat_h_obj#_icol#_st on wri$_optstat_histgrm_history (obj#,
intcol#, savtime, colname) tablespace sysaux;
create
index i_wri$_optstat_h_st on wri$_optstat_histgrm_history (savtime) tablespace
sysaux;
NOTE
======
SM/OPTSTAT
select dbms_stats.get_stats_history_retention from dual;
exec dbms_stats.alter_stats_history_retention(10);
If you like to specifically purge data from before a certain
timestamp you can use the PURGE_STATS procedure for this. For example:
exec DBMS_STATS.PURGE_STATS(to_timestamp_tz('01-09-2006
00:00:00 Europe/London','DD-MM-YYYY HH24:MI:SS TZR'));
Note:
run it at a time where there is no stats gathering activity
on the system, and system activity is minimal
+++++++++++++++++++++
243246.1, 782974.1, 950128.1
++++++++++++++++++++++
No comments:
Post a Comment