Script:诊断SYSAUX表空间使用情况

Script:以下脚本可以用于诊断SYSAUX表空间使用情况

 

./opatch lsinventory -detail

@?/rdbms/admin/awrinfo

select dbms_stats.get_stats_history_retention from dual;

select dbms_stats.get_stats_history_availability from dual;

select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;

select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_tab_history;

select count(*) from sys.wri$_optstat_ind_history;

select count(*) from sys.wri$_optstat_histhead_history;

select count(*) from sys.wri$_optstat_histgrm_history;

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

 

 

示例输出:

 

~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00

select count(*) from sys.wri$_optstat_aux_history;

select count(*) from sys.wri$_optstat_opr;

Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days

       DB_ID DB_NAME   HOST_PLATFORM                             INST STARTUP_TIME      LAST_ASH_SID PAR
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 195600696  PROD      maclean1.oracle.com - Linux x86 64-bit       1 07:42:19 (06/01)         65130 YES
  195600696  PROD      maclean2.oracle.com - Linux x86 64-bit       2 07:41:20 (06/01)         65226 YES

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size                        193.6 MB ( 1% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema  SYS          occupies            101.1 MB (  52.2% )
| Schema  SYSMAN       occupies             74.5 MB (  38.5% )
| Schema  SYSTEM       occupies             13.7 MB (   7.1% )
| Schema  WMSYS        occupies              3.5 MB (   1.8% )
| Schema  DBSNMP       occupies              0.8 MB (   0.4% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| EM                   SYSMAN                        74.5 MB
| SM/AWR               SYS                           56.2 MB
| LOGMNR               SYSTEM                        12.3 MB
| SM/ADVISOR           SYS                            8.9 MB
| SM/OPTSTAT           SYS                            7.7 MB
| SM/OTHER             SYS                            6.9 MB
| WM                   WMSYS                          3.5 MB
| SQL_MANAGEMENT_BASE  SYS                            1.7 MB
| PL/SCOPE             SYS                            1.6 MB
| AO                   SYS                            1.5 MB
| XSOQHIST             SYS                            1.5 MB
| LOGSTDBY             SYSTEM                         1.4 MB
| STREAMS              SYS                            1.0 MB
| EM_MONITORING_USER   DBSNMP                         0.8 MB
| JOB_SCHEDULER        SYS                            0.5 MB
| SMON_SCN_TIME        SYS                            0.5 MB
| AUTO_TASK            SYS                            0.3 MB
| AUDIT_TABLES         SYS                            0.0 MB
| EXPRESSION_FILTER    EXFSYS                         0.0 MB
| ORDIM                ORDSYS                         0.0 MB
| ORDIM/ORDDATA        ORDDATA                        0.0 MB
| ORDIM/ORDPLUGINS     ORDPLUGINS                     0.0 MB
| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA             0.0 MB
| SDO                  MDSYS                          0.0 MB
| STATSPACK            PERFSTAT                       0.0 MB
| TEXT                 CTXSYS                         0.0 MB
| TSM                  TSMSYS                         0.0 MB
| ULTRASEARCH          WKSYS                          0.0 MB
| ULTRASEARCH_DEMO_USE WK_TEST                        0.0 MB
| XDB                  XDB                            0.0 MB
| XSAMD                OLAPSYS                        0.0 MB
|
| Others (Unaccounted space)                         12.9 MB
|

******************************************
(1c) SYSAUX usage - Unregistered Schemas
******************************************

| This section displays schemas that are not registered
| in V$SYSAUX_OCCUPANTS
|
|
| Total space                                0.0 MB
|

*************************************************************
(1d) SYSAUX usage - Unaccounted space in registered schemas
*************************************************************
|
| This section displays unaccounted space in the registered
| schemas of V$SYSAUX_OCCUPANTS.
|
| Unaccounted space in SYS/SYSTEM           12.9 MB
|
| Total space                               12.9 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
|    AWR size/day                          674.3 MB (28,768 K/snap * 24 snaps/day)
|    AWR size/wk                         4,719.8 MB (size_per_day * 7) per instance
|    AWR size/wk                         9,439.5 MB (size_per_day * 7) per database
|
| Estimates based on 1 snaps in past 24 hours:
|    AWR size/day                          533.8 MB (28,768 K/snap and 1 snaps in past 1.3 hours)
|    AWR size/wk                         3,736.5 MB (size_per_day * 7) per instance
|    AWR size/wk                         7,472.9 MB (size_per_day * 7) per database
|

**********************************
(3a) Space usage by AWR components (per database)
**********************************

COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED          35.8   63.7       18,336      340.2     2,381.5    56% : 44%
EVENTS          5.8   10.3        2,976       55.2       386.5    43% : 57%
SQL             4.0    7.1        2,048       38.0       266.0    72% : 28%
SPACE           3.9    7.0        2,016       37.4       261.8    65% : 35%
SQLPLAN         2.3    4.1        1,184       22.0       153.8    86% : 14%
RAC             1.3    2.2          640       11.9        83.1    65% : 35%
SQLTEXT         0.6    1.0          288        5.3        37.4    89% : 11%
ASH             0.6    1.0          288        5.3        37.4    67% : 33%
SQLBIND         0.3    0.6          160        3.0        20.8    60% : 40%

**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME - % SPACE_USED                                           SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED           3.0 WRH$_SYSMETRIC_HISTORY_INDEX                                  -  33%  INDEX
FIXED           3.0 WRH$_SYSMETRIC_HISTORY                                        -   6%  TABLE
FIXED           2.0 WRH$_LATCH.WRH$_LATCH_195600696_0                             -  56%  TABLE PARTITION
FIXED           2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0                       -  53%  INDEX PARTITION
FIXED           0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0                          -  78%  INDEX PARTITION
FIXED           0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0                          -  85%  TABLE PARTITION
FIXED           0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0                     -  80%  INDEX PARTITION
FIXED           0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0                        -  85%  TABLE PARTITION
FIXED           0.6 WRH$_SYSMETRIC_SUMMARY                                        -   8%  TABLE
FIXED           0.6 WRH$_WAITCLASSMETRIC_HIST_IND                                 -  29%  INDEX
FIXED           0.6 WRH$_WAITCLASSMETRIC_HISTORY                                  -  13%  TABLE
EVENTS          2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0               -  59%  INDEX PARTITION
EVENTS          0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0                  -  85%  TABLE PARTITION
EVENTS          0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0                     -  63%  TABLE PARTITION
SQL             2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0                          -  47%  TABLE PARTITION
SPACE           0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0                         -  80%  TABLE PARTITION
SQLPLAN         2.0 WRH$_SQL_PLAN                                                 -  65%  TABLE
SQLTEXT         0.5 WRH$_SQLTEXT                                                  -  74%  TABLE

**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************

COMPONENT        MB SEGMENT_NAME                                                          SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
NON_AWR         4.0 SYSMAN.MGMT_MESSAGES                                                  TABLE
NON_AWR         4.0 SYSTEM.SYS_LOB0000001147C00009$$                                      LOBSEGMENT
NON_AWR         2.0 SYSMAN.MGMT_METRICS                                                   TABLE
NON_AWR         2.0 SYSMAN.MGMT_MESSAGES_PK                                               INDEX
NON_AWR         2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST                                     INDEX
NON_AWR         1.0 SYS.SYS$SERVICE_METRICS_TAB                                           TABLE
NON_AWR         1.0 SYS.SYS_LOB0000006339C00038$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_PK                                                INDEX
NON_AWR         0.9 SYS.SYS_LOB0000005097C00005$$                                         LOBSEGMENT
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_03                                            INDEX
NON_AWR         0.9 SYSMAN.MGMT_METRICS_IDX_01                                            INDEX
NON_AWR         0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY                                     TABLE
NON_AWR         0.8 SYSMAN.SYS_LOB0000015321C00004$$                                      LOBSEGMENT
NON_AWR         0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                                     TABLE
NON_AWR         0.6 SYSMAN.MGMT_METRICS_RAW_PK                                            INDEX
NON_AWR         0.6 SYSMAN.MGMT_POLICIES                                                  TABLE
NON_AWR         0.6 SYS.I_WRI$_OPTSTAT_HH_ST                                              INDEX
NON_AWR         0.5 SYSMAN.MGMT_JOB_STEP_PARAMS                                           TABLE
NON_AWR         0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG                                          TABLE
NON_AWR         0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS                                        INDEX
NON_AWR         0.5 SYS.SYS_LOB0000006331C00004$$                                         LOBSEGMENT

**********************************
(5a) AWR snapshots - last 50
**********************************

Total snapshots in DB 195600696 Instance 2 = 1
Total snapshots in DB 195600696 Instance 1 = 1

      DBID    SNAP_ID  INST FLUSH_ELAPSED        ENDTM             STARTUP_TIME      STATUS ERRCNT
---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------
 195600696         20     1 +00000 00:00:13.4    07:52:14 (06/01)  07:42:19 (06/01)       0      0
 195600696         20     2 +00000 00:00:11.8    07:52:15 (06/01)  07:41:20 (06/01)       0      0

**********************************
(5b) AWR snapshots with errors or invalid
**********************************

no rows selected

**********************************
(5c) AWR snapshots -- OLDEST Non-Baselined snapshots
**********************************

      DBID  INST    SNAP_ID ENDTM             STATUS ERROR_COUNT
---------- ----- ---------- ----------------- ------ -----------
 195600696     1         20 07:52:14 (06/01)       0           0

**********************************
(6) AWR Control Settings - interval, retention
**********************************

       DBID  LSNAPID LSPLITID LSNAPTIME      LPURGETIME      FLAG INTERVAL          RETENTION         VRSN
----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----
  195600696       20        0 06/01 07:52:28 06/01 08:11:13     2 +00000 01:00:00.0 +00008 00:00:00.0    5

**********************************
(7a) AWR Contents - row counts for each snapshots
**********************************

   SNAP_ID  INST        ASH        SQL      SQBND      FILES      SEGST     SYSEVT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
        20     1          3         99        257          5         55        155
        20     2         12        105        300          5         58        156

**********************************
(7b) AWR Contents - average row counts per snapshot
**********************************

SNAP_COUNT  INST        ASH    SQLSTAT    SQLBIND      FILES    SEGSTAT   SYSEVENT
---------- ----- ---------- ---------- ---------- ---------- ---------- ----------
         1     2         12        105        300          5         58        156
         1     1          3         99        257          5         55        155

**********************************
(7c) AWR total item counts - names, text, plans
**********************************

   SQLTEXT    SQLPLAN   SQLBMETA     SEGOBJ   DATAFILE   TEMPFILE
---------- ---------- ---------- ---------- ---------- ----------
       407       4506       1267        297          5          1

########################################################
(II) Advisor Framework Info
########################################################

**********************************
(1) Advisor Tasks - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       13/ADDM:195600696_1_4            12:00:23 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       25/ADDM:195600696_2_4            12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       26/ADDM:195600696_4              12:00:24 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       14/ADDM:195600696_1_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       28/ADDM:195600696_5              13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       27/ADDM:195600696_2_5            13:00:29 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       29/ADDM:195600696_2_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       15/ADDM:195600696_1_6            14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       16/ADDM:195600696_6              14:00:17 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       17/ADDM:195600696_1_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       30/ADDM:195600696_2_7            15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       18/ADDM:195600696_7              15:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       20/ADDM:195600696_8              16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       19/ADDM:195600696_1_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       31/ADDM:195600696_2_8            16:00:34 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       32/ADDM:195600696_2_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       42/ADDM:195600696_9              17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       41/ADDM:195600696_1_9            17:00:40 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       33/ADDM:195600696_2_10           18:00:51 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       44/ADDM:195600696_10             18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       43/ADDM:195600696_1_10           18:01:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       45/ADDM:195600696_1_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       34/ADDM:195600696_2_11           19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       35/ADDM:195600696_11             19:00:01 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       36/ADDM:195600696_2_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       46/ADDM:195600696_1_12           20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       47/ADDM:195600696_12             20:00:02 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       37/ADDM:195600696_2_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       48/ADDM:195600696_1_13           21:00:09 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       49/ADDM:195600696_13             21:00:09 (05/20)          9          9 AUTO  COMPLETED
SYS/ADDM       50/ADDM:195600696_1_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       38/ADDM:195600696_2_14           22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       51/ADDM:195600696_14             22:00:15 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       39/ADDM:195600696_2_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       52/ADDM:195600696_1_15           23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       53/ADDM:195600696_15             23:00:21 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       54/ADDM:195600696_1_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       40/ADDM:195600696_2_16           00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       55/ADDM:195600696_16             00:00:25 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       57/ADDM:195600696_17             01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       56/ADDM:195600696_1_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       61/ADDM:195600696_2_17           01:00:28 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       62/ADDM:195600696_2_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       59/ADDM:195600696_18             02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       58/ADDM:195600696_1_18           02:00:32 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       71/ADDM:195600696_19             03:00:41 (05/21)          1          1 AUTO  COMPLETED
SYS/ADDM       60/ADDM:195600696_1_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED
SYS/ADDM       63/ADDM:195600696_2_19           03:00:41 (05/21)          0          0 AUTO  COMPLETED

**********************************
(2) Advisor Task - Oldest 5
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
SYS/ADDM       11/ADDM:195600696_1_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       21/ADDM:195600696_2_2            10:00:13 (05/20)          1          1 AUTO  COMPLETED
SYS/ADDM       22/ADDM:195600696_2              10:00:14 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       23/ADDM:195600696_2_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       12/ADDM:195600696_1_3            11:00:19 (05/20)          0          0 AUTO  COMPLETED
SYS/ADDM       24/ADDM:195600696_3              11:00:19 (05/20)          0          0 AUTO  COMPLETED

**********************************
(3) Advisor Tasks With Errors - Last 50
**********************************

OWNER/ADVISOR  TASK_ID/NAME                     CREATED          EXE_DURATN EXE_CREATN HOW_C STATUS
-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------
TASK_DESC
--------------------------------------------------------------------------------------------------------------
ERROR_MSG
--------------------------------------------------------------------------------------------------------------
SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK       07:52:03 (05/20)                       AUTO  INITIAL
Description: Automatic SQL Tuning Task
Error Msg  :

########################################################
(III) ASH Usage Info
########################################################

**********************************
(1a) ASH histogram (past 3 days)
**********************************

**********************************
(1b) ASH histogram (past 1 day)
**********************************

**********************************
(2a) ASH details (past 3 days)
**********************************

**********************************
(2b) ASH details (past 1 day)
**********************************

**********************************
(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)
**********************************

Foreground %
Background %
MMNL %

End of Report
Report written to awrinfo.txt
SQL>
GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> SQL>
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
20-MAY-12 07.52.20.633129000 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.091525 AM -04:00
20-MAY-12 06.01.15.033444 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.28.548542 AM -04:00
20-MAY-12 06.01.15.071814 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.52.27.103629 AM -04:00
20-MAY-12 06.01.15.048213 PM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.53.50.386756 AM -04:00
20-MAY-12 07.57.17.388624 AM -04:00

SQL> SQL>
MIN(SAVTIME)
---------------------------------------------------------------------------
MAX(SAVTIME)
---------------------------------------------------------------------------
20-MAY-12 07.57.47.200604 AM -04:00
20-MAY-12 07.57.47.205012 AM -04:00

SQL> SQL>
  COUNT(*)
----------
      2191

SQL> SQL>
  COUNT(*)
----------
      2631

SQL> SQL>
  COUNT(*)
----------
     21962

SQL> SQL>
  COUNT(*)
----------
      5206

SQL> SQL>
  COUNT(*)
----------
        18

SQL> SQL>
  COUNT(*)
----------
       126

 

 

若发现statistics统计信息占用了SYSAUX上的大量空间,则可以考虑 使用dbms_stats.purge_stats过程实施清理

 

Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION
Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege.

1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10.

2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from “09-JUN-11 01.52.06.895132 PM -05:00 ” to “22-AUG-11 02.53.34.754747 PM -05:00”

3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31.

ACTION PLAN:
=========

A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs)

SQL> SPOOL CHECK1.OUT

1) Try to force the execution of the purge operations :

SQL> alter session set “_swrf_test_action” = 72;

2) Purging snapshots :

SQL> exec dbms_stats.purge_stats(sysdate-&days);

using &days = n, n-1, n-2, …, n-x

3) Then again execute the below set of SQL’s and upload the spool output file

SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;

SQL> SPOOL OFF

B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.)

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400);

NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes

C) Check the value of STATISTICS_LEVEL

If the above value is Set to ALL, then consider changing to TYPICAL.

The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space.

Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

Once the above actions performed, please keep us posted on the status of the same

【Oracle数据恢复】如何重建SYSAUX表空间上的对象

SQL> select file_id,file_name from dba_data_files where tablespace_name=’SYSAUX’;

FILE_ID
———-
FILE_NAME
——————————————————————————————————————————————–
2
/s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf
SQL> alter database datafile 2 offline;

Database altered.

 
假设这里datafile 2即SYSAUX所有的数据文件都丢失了 且没有任何备份,此时我们无法利用任何备份还原该表空间
但是SYSAUX表空间却是数据库必要的系统表空间之一,存放了 AWR等重要数据和其他一些组件的辅助数据

 

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

*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-00376: file ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/s01/oradata/FIXIT/datafile/o1_mf_sysaux_85wkhmrk_.dbf’
cannot be read at this time
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 99
ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 122
ORA-06512: at line 1

 

我们需要在 丢失SYSAUX所有数据文件及其备份的情况下 重建该表空间上的对象

 

除了以上彻底丢失 数据文件的情况, SYSAUX 数据文件存在某些逻辑或物理坏块也可能是重建该表空间上对象的理由

例如以下错误:

ORA-00600: [kcbz_check_objd_typ] from MMON slave or its process
ORA-00600: [kdsgrp1] while querying WR% tables from SYSAUX

对应不同的AWR负载基表, $ORACLE_HOME/rdbms/admin/目录下存有相关这些系统表的创建脚本,例如:

WRI$_OPTSTAT catost.sql – Optimizer Statistics Tables
WRI$_ALERT catalrt.sql – Catalog script for server ALeRT
WRH$_* catawrtb.sql – Catalog script for AWR Tables
catawrvw.sql – Catalog script for AWR Views

接下来我们开始着手重建SYSAUX上的对象,请注意recreate sysaux上的对象是在正常恢复之外的非常规手段,也是恢复SYSAUX的最后一步,不要在产品环境中滥用以下手段:

鉴于该操作过于危险,只适合于深入了解Oracle的人士,所以具体操作步骤移除。

如果不能自行解决该问题,那么也可以联系MACLEAN专业数据库修复团队。

 

 

沪ICP备14014813号-2

沪公网安备 31010802001379号