Вы здесь

Cleaning Oracle SYSAUX Tablespace Usage

Как почистить SYSAUX

 


Июл042011

 

Если закончилось место в табличном пространстве SYSAUX, то решить эту проблему можно двумя путями: добавив файл данных или удалив отчёты AWR, которые, как известно, хранятся именно там. Если база второстепенная и нам не нужна информация о её производительности в прошлом (читай: лень возиться), то можно быстро дропнуть и пересоздать таблицы, содержащие данные для отчётов:

-- Этот скрипт поудаляет всё, связанное с AWR
@?/rdbms/admin/catnoawr.SQL 
-- А этот - вернёт AWR на место, если мы вдруг передумаем.
@?/rdbms/admin/catawrtb.SQL

 

Restrictions on SYSAUX tablespace

1. Using SYSAUX DATAFILE clause in the CREATE DATABASE statement you can specify only datafile attributes in SYSAUX tablespace.
2. You can not alter attributes like (PERMANENT, READ WRITE,EXTENT MANAGMENT LOCAL,SEGMENT SPACE MANAGMENT AUTO) with an ALTER TABLESPACE statement
3. SYSAUX tablespace cannot be dropped or renamed.

Now coming to our issue of oracle sysaux tablespace full, we need to do following to free up the space. To give a prospect of what was the size of SYSAUX before cleanup we have put a screenshot of SYSAUX before clean up below: ( 94% full)

SYSAUX Before Cleanup:

sysaux tablespace usage

1. Check whats occupying SYSAUX tablespace:

Run below query to know what all occupants are there in sysaux occupying all the space.

select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants

Sample Output:

sysaux tablespace occupants

Once we are sure about whats all occupying the space then we can work on it. Here in this article we have tackled with huge number of AWR reports occupying 5GB+ space(shown in below screenshot).

SYSAUX AWR Usage

2. Check AWR Retention Period:

First is to check what is the AWR retention period in DB. For us it was set for 90 days which we did not require. You can query using below SQL:

select retention from dba_hist_wr_control;

AWR retention Period

We reduced it to 7 day which is 7*24*60 = 10080 minutes.  In this example the retention period is modified to 7 days (10080 minutes) and the interval between each snapshot is 60 minutes.

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

In case while reducing the retention period you encounter below error then check the MOVING_WINDOW_SIZE value and update it to correct value and then execute the above AWR retention query again.

Example Error:

ORA-13541

 

 

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

SELECT moving_window_size
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';

 

3. Cleanup old AWR reports to free up space:

Once the retention period is set you can follow below steps to cleanup the old AWR reports to free up space. Run below query to find the oldest and newest AWR snapshots.

SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)
UNION
SELECT
snap_id, begin_interval_time, end_interval_time
FROM
SYS.WRM$_SNAPSHOT
WHERE
snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)
/

AWR retention snapshots

Execute below command to cleanup all AWR reports between snap_id 9723 to 9920.

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => 9723, high_snap_id=>9920);
END;
/

If above removal process taking too much of time then you can run below two sqls as sysdba to drop the old AWR’s and rebuild the repositories. This process is very fast.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

After clearing up all the AWR reports we were able to cleanup approx of 5GB space from SYSAUX tablespace.
SYSAUX Tablespace Post AWR Cleanup:
SYSAUX Tablespace post cleanup

Post new comment

Filtered HTML

  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Разрешённые HTML-теги: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Строки и параграфы переносятся автоматически.

Plain text

  • HTML-теги не обрабатываются и показываются как обычный текст
  • Адреса страниц и электронной почты автоматически преобразуются в ссылки.
  • Строки и параграфы переносятся автоматически.
CAPTCHA
Этот вопрос задается для того, чтобы выяснить, являетесь ли Вы человеком или представляете из себя автоматическую спам-рассылку.
1 + 7 =
Решите эту простую математическую задачу и введите результат. Например, для 1+3, введите 4.