Oracle DBA by Example. Code samples, Scripts, Reference.: Manage SYSAUX size by Example.

========================
General
========================
There is a general alert for Tablespace low on free space.

To check which Tablespace is low on disk space:

SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_MORE_SPACE
--------------- ----------------- ------------- ------------ --------- --------------
DWH_INDEX 13200 29568 42768 31 N
SYSAUX 78 922 1000 8 Y
IGT_TABLE 6351 7649 14000 45 N
SYSTEM 637 363 1000 64 N
IGT_INDEX 6604 1396 8000 83 N
DWH_TABLE 19792 12976 32768 60 N

It is SYSAUX Tablespace.

To Check what Segment is occupying SYSAUX Tablespace:

SELECT owner, tablespace_name, segment_name, USED_MB

FROM

(SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB

FROM DBA_SEGMENTS

WHERE tablespace_name LIKE '%SYSAUX%'

GROUP BY owner, tablespace_name,segment_name

ORDER BY ROUND(SUM(bytes)/1024/1024) DESC

)

WHERE ROWNUM < 11;

OWNER TABLESPACE_NAME SEGMENT_NAME USED_MB

---------- --------------- ------------------------------ ----------

SYS SYSAUX WRH$_ACTIVE_SESSION_HISTORY 341

SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 72

SYS SYSAUX WRH$_ACTIVE_SESSION_HISTORY_PK 51

SYS SYSAUX WRI$_OPTSTAT_HISTGRM_HISTORY 41

SYS SYSAUX I_WRI$_OPTSTAT_H_ST 29

SYS SYSAUX WRH$_SYSMETRIC_HISTORY 27

SYS SYSAUX WRH$_SYSMETRIC_HISTORY_INDEX 20

SYS SYSAUX WRI$_ADV_MESSAGE_GROUPS_PK 13

SYS SYSAUX SCHEDULER$_EVENT_LOG 12

SYS SYSAUX SYS_LOB0000005981C00038$$ 12

Looks like AWR data is occupying most data.

Another tools to check for SYSAUX contents:

A. V$SYSAUX_OCCUPANTS View

SELECT OCCUPANT_NAME,OCCUPANT_DESC, SCHEMA_NAME, MOVE_PROCEDURE, SPACE_USAGE_KBYTES

FROM V$SYSAUX_OCCUPANTS

ORDER BY SPACE_USAGE_KBYTES DESC;

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME MOVE_PROCEDURE SPACE_USAGE_KBYTES

-------------------- ------------------------------ --------------- -------------------- ------------------

SM/AWR Server Manageability - Automat SYS 592448

ic Workload Repository

SM/OPTSTAT Server Manageability - Optimiz SYS 175104

er Statistics History

SM/ADVISOR Server Manageability - Advisor SYS 58048

Framework

XDB XDB XDB XDB.DBMS_XDB.MOVEXDB 56256

JOB_SCHEDULER Unified Job Scheduler SYS 15104

LOGMNR LogMiner SYSTEM SYS.DBMS_LOGMNR_D.SE 7808

WM Workspace Manager WMSYS DBMS_WM.move_proc 7296

SM/OTHER Server Manageability - Other C SYS 6336

omponents

SMON_SCN_TIME Transaction Layer - SCN to TIM SYS 3328

E mapping

EM_MONITORING_USER Enterprise Manager Monitoring DBSNMP 1920

User

SQL_MANAGEMENT_BASE SQL Management Base Schema SYS 1728

PL/SCOPE PL/SQL Identifier Collection SYS 1472

AO Analytical Workspace Object Ta SYS DBMS_AW.MOVE_AWMETA 1408

XSOQHIST OLAP API History Tables SYS DBMS_XSOQ.OlapiMoveProc 1408

LOGSTDBY Logical Standby SYSTEM SYS.DBMS_LOGSTDBY. 1024

STREAMS Oracle Streams SYS 1024

B. utlsyxsz.sql script.

sqlplus / as sysdba @/software/oracle/111/rdbms/admin/utlsyxsz.sql

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 13 07:33:41 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

This script estimates the space required for the SYSAUX tablespace.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is utlsyxsz.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: SYSAUX_USAGE.txt

Using the report name SYSAUX_USAGE.txt

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SYSAUX Size Estimation Report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at

07:34:17 on Apr 13, 2016 ( Wednesday ) in Timezone -05:00

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR

----------- ---------------------------------------- ----- ----------------- ---

* IGT rnd-mas-1-ver-5 - Linux x86 64-bit 1 07:28:45 (04/06) NO

~~~~~~~~~~~~~~~~~~~~

Current SYSAUX usage

~~~~~~~~~~~~~~~~~~~~

| Total SYSAUX size: 922.3 MB

| Total size of SM/AWR 578.6 MB ( 62.7% of SYSAUX )

| Total size of SM/OPTSTAT 171.0 MB ( 18.5% of SYSAUX )

| Total size of SM/ADVISOR 56.7 MB ( 6.1% of SYSAUX )

| Total size of XDB 54.9 MB ( 6.0% of SYSAUX )

| Total size of JOB_SCHEDULER 14.8 MB ( 1.6% of SYSAUX )

| Total size of LOGMNR 7.6 MB ( 0.8% of SYSAUX )

| Total size of WM 7.1 MB ( 0.8% of SYSAUX )

| Total size of SM/OTHER 6.2 MB ( 0.7% of SYSAUX )

| Total size of SMON_SCN_TIME 3.3 MB ( 0.4% of SYSAUX )

| Total size of EM_MONITORING_USER 1.9 MB ( 0.2% of SYSAUX )

| Total size of SQL_MANAGEMENT_BASE 1.7 MB ( 0.2% of SYSAUX )

| Total size of PL/SCOPE 1.4 MB ( 0.2% of SYSAUX )

| Total size of XSOQHIST 1.4 MB ( 0.1% of SYSAUX )

| Total size of AO 1.4 MB ( 0.1% of SYSAUX )

| Total size of STREAMS 1.0 MB ( 0.1% of SYSAUX )

| Total size of LOGSTDBY 1.0 MB ( 0.1% of SYSAUX )

| Total size of AUTO_TASK 0.3 MB ( 0.0% of SYSAUX )

| Total size of TSM 0.3 MB ( 0.0% of SYSAUX )

| Total size of Others 11.9 MB ( 1.3% of SYSAUX )

~~~~~~~~~~~~~~~~~~~~

AWR Space Estimation

~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)

| in SYSAUX, we need the following values:

| - Interval Setting (minutes)

| - Retention Setting (days)

| - Number of Instances

| - Average Number of Active Sessions

| - Number of Datafiles

| For 'Interval Setting',

| Press <return> to use the current value: 60.0 minutes

| otherwise enter an alternative

C. Just make sure that there are no "zombie" old snapshots.

Check for oldest snapshot using DBA_HIST_SNAPSHOT or SYS.WRM$_SNAPSHOT, and then purge old snapshots with DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range();

For Example:

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(low_snap_id => 7556, high_snap_id=>1000);

END;

SELECT snap_id,

TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi:ss')min_begin_interval,

TO_CHAR(end_interval_time,'YYYYMMDD hh24:mi:ss') min_end_interval

FROM DBA_HIST_SNAPSHOT

WHERE snap_id = (SELECT MIN(snap_id) FROM DBA_HIST_SNAPSHOT);

SELECT snap_id,

TO_CHAR(begin_interval_time,'YYYYMMDD hh24:mi:ss')min_begin_interval,

TO_CHAR(end_interval_time,'YYYYMMDD hh24:mi:ss') min_end_interval

FROM SYS.WRM$_SNAPSHOT

WHERE snap_id = (SELECT MIN(snap_id) FROM SYS.WRM$_SNAPSHOT );

SNAP_ID MIN_BEGIN_INTERVAL MIN_END_INTERVAL

---------- ------------------ ---------------------

35749 20160408 13:18:39 20160408 15:00:17

In this case all looks OK.

There are no old "zombie" snapshots.

It seems that the default size of 1Gb is just too small for this DB.

The solution would be to increase the tablespace of SYSAUX, to 2Gb.

========================
Solution
========================
Increase the tablespace of SYSAUX.

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_sysaux_01.dbf' RESIZE 2000M;

Database altered

SELECT TABLESPACE_NAME,
(MAX(MAX_SPACE)-MAX(USED_SPACE) )AS DBA_FREE_SPACE_MB,
MAX(USED_SPACE) AS USED_SPACE_MB,
MAX(MAX_SPACE) AS MAX_SPACE_MB,
ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100) as FREE_PCT,
CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE) ) /MAX(MAX_SPACE) ) *100)<15) THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE
FROM (
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE,
0 AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_FREE_SPACE
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
ROUND( SUM(CASE WHEN (bytes>maxbytes) THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE,
0 AS USED_SPACE
FROM DBA_DATA_FILES
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
0 AS FREE_SPACE,
0 AS MAX_SPACE,
ROUND(SUM(bytes/1024/1024)) AS USED_SPACE
FROM DBA_SEGMENTS
WHERE tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM' OR tablespace_name = 'SYSAUX'
GROUP BY tablespace_name
)
GROUP BY tablespace_name;

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB FREE_PCT ADD_MORE_SPACE
--------------- ----------------- ------------- ------------ ---------- --------------
DWH_INDEX 13200 29568 42768 31 N
SYSAUX 1074 926 2000 54 N
IGT_TABLE 6349 7651 14000 45 N
SYSTEM 637 363 1000 64 N
IGT_INDEX 6604 1396 8000 83 N
DWH_TABLE 19792 12976 32768 60 N

6 rows selected

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Wednesday, April 13, 2016

Manage SYSAUX size by Example.

No comments:

Post a Comment