Über unsMediaKontaktImpressum
Andrea Held 04. November 2014

Der Oracle Sysaux-Tablespace: Reorganisation und Verkleinerung

Oracle-Datenbanken speichern Daten in Tablespaces. Ein spezieller darunter ist der Tablespace Sysaux: Er speichert Daten verschiedener Datenbank-Tools. Bei manchen Einstellungen kann es passieren, dass er enorm wächst. Seine Größe und die seiner Datafiles wieder zu verringern, ist nicht immer einfach. Der Artikel beschreibt, wie Sie verfahren können, um den Tablespace effektiv zu verkleinern. Das beschriebene Verfahren lässt sich zu großen Teilen auch auf andere Tablespaces anwenden.

Der Sysaux-Tablespace speichert Daten einiger Optionen und Werkzeuge einer Oracle-Datenbank. Dazu zählt beispielsweise das Automatic Workload Repository (AWR), das zahlreiche Datenbankstatistiken bereithält. Sie sind bei Performance-Engpässen und Optimierungen der Datenbank hilfreich. Sind die Einstellungen des AWR – beispielsweise der Detaillierungsgrad der Statistiken, die Aufbewahrungszeit und die Häufigkeit der Erstellung – ungünstig gewählt, wächst das Datenvolumen und damit der Tablespace enorm. Diese Einstellungen können Sie leicht prüfen und ändern. Wie Sie dies bewerkstelligen können, wird weiter unten beschrieben. 

Möchte man den Sysaux-Tablespace jedoch wieder verkleinern, so gestaltet sich das manchmal gar nicht so einfach. Der Artikel zeigt, wie Sie den Sysaux-Tablespace prüfen, reorganisieren und verkleinern können. Vorab: Sie operieren hier "am offenen Herzen"! Sichern Sie sich daher ab. Bevor Sie irgendein Script in einer für den Geschäftsbetrieb erforderlichen Umgebung durchführen, testen Sie umfangreich in einer separaten Test-Datenbank. Sichern Sie Ihre Datenbank und prüfen Sie die Validität der Sicherung, bevor Sie irgendeine Änderung vornehmen. Nutzen Sie zudem für die Reorganisation ein Wartungsfenster.

Welche Komponenten speichern Daten im Sysaux-Tablespace?

Einige Komponenten der Oracle-Datenbank speichern Daten im Sysaux-Tablespace. Die Menge der allokierten Datenblöcke resultiert zum einen daraus, welche Komponenten und Funktionen konfiguriert und verwendet werden, zum anderen hängen sie vom Workload der Datenbank und einigen Einstellungen wie beispielsweise der Aufbewahrungszeit von Datenbank-Statistiken im Automatic Workload Repository (AWR) ab.

Das Oracle AWR verwendet oft den größten Teil des Speicherplatzes im Tablespace Sysaux. Wie viel das genau ist, wird durch einige Faktoren bestimmt, darunter:

  • Anzahl aktiver Sessions im System,
  • Detaillierungsgrad der Statistiken,
  • Intervall, in dem Statistik-Schnappschüsse des Systems erstellt werden und
  • Aufbewahrungsdauer der Statistiken.

Damit Ihr AWR nicht zu groß wird, sollten Sie von vornherein adäquate Werte einstellen für den Detaillierungsgrad der Statistiken, für die Häufigkeit von Schnappschüssen und den Zeitraum, für den historische Daten aufbewahrt werden.

Neben dem AWR gibt es weitere Werkzeuge, die Daten im Sysaux-Tablespace speichern. Dazu zählt das Repository des Enterprise Managers (EM). Es speichert die Daten des graphischen Verwaltungswerkzeugs Oracle Enterprise Manager Database Control. 

Weitere Datenbank-Komponenten, die den Sysaux-Tablespace nutzen, sind unter anderem Oracle UltraSearch, Oracle Text und Oracle Streams. Werden diese Komponenten nicht genutzt, so speichern sie auch keine signifikante Menge an Daten und benötigen kaum Platz.

Verbrauch des Speicherplatzes im Sysaux-Tablespace ermitteln

Wir verschaffen uns zunächst einen Überblick. Welchen Speicherplatz belegt der Tablespace? Welche Komponenten sind darin gespeichert? Welche Datafiles liegen darunter?

Welche Komponenten Daten im Sysaux-Tablespace speichern, zeigt die folgende Abfrage:

 SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES
FROM   V$SYSAUX_OCCUPANTS;

Script 1: Welche Komponenten belegen den Sysaux-Tablespace?

Dann bietet es sich an, die Größe des Sysaux-Tablespace zu ermitteln. Die folgende Abfrage zeigt, wie groß der Tablespace aktuell ist und wie viel Platz darin belegt wird:

SELECT NVL(b.tablespace_name, NVL(a.tablespace_name,'UNKNOWN')) "Tablespace", 
  ROUND( kbytes_alloc, 2) "Allocated MB",
  ROUND( kbytes_alloc-NVL(kbytes_free,0), 2) "Used MB",
  ROUND( NVL(kbytes_free,0), 2) "Free MB",
  ROUND( ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc), 2) "Used",
  data_files "Data Files"
FROM
  (SELECT SUM(bytes)/1024/1024 Kbytes_free,
    MAX(bytes)      /1024/1024 largest,
    tablespace_name
  FROM sys.dba_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT SUM(bytes)/1024/1024 Kbytes_alloc,
    tablespace_name,
    COUNT(*) data_files
  FROM sys.dba_data_files
  GROUP BY tablespace_name
  )b
WHERE a.tablespace_name (+) = b.tablespace_name
AND b.tablespace_name       = 'SYSAUX';

Script 2: Belegter Speicherplatz im Sysaux-Tablespace

Welche Datafiles zum Sysaux-Tablespace gehören, ermitteln Sie über die View dba_data_files:

select file_name, round(bytes/1024/1024/1024, 2) GB 
from   dba_data_files where tablespace_name = 'SYSAUX';

Script 3: Ermitteln des Datafiles

Möchte man ein Datafile des Sysaux-Tablespace verkleinern,  so kann man dies mit einem resize auf das Datafile durchführen:

alter database datafile '<dateiname>' resize  <neue_groesse>;

Script 4: Größe des Datafiles ändern

Beispiel:

alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 5000M

Script 5: Größe des Datafiles ändern

Falls nun aber irgendein Objekt in der Datenbank noch einen Block in diesem Datafile hinter der Grenze allokiert, so erhält man einen Fehler:

SQL> alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 5000M
  2  ;
alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 5000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


Script 6: Fehlermeldung beim Ändern der Größe

Was nun? Ganz einfach: Man prüft, welche Objekte die letzten Blöcke belegen. Die folgende Abfrage gibt alle Segmente des Sysaux Tablespace aus und zwar sortiert nach ihrer maximalen Block-ID. Die Block-ID gibt die Position eines Datenbank-Blocks innerhalb des Datafiles des Tablespaces an. Die Sortierung nach der maximalen Block-ID zeigt uns also, welche Segmente die hinteren Bereiche des Datafiles belegen und so verhindern, dieses zu verkleinern. Hier die Abfrage:

set pagesize 3000
set linesize 3000

select e.file_id, max(e.block_id),
       e.owner, e.segment_name, segment_type, partition_name
from   dba_extents e
where  e.tablespace_name = 'SYSAUX'
group  by e.file_id, e.owner, e.segment_name, segment_type, partition_name
order by 1, 2;

Script 7: Segment-Belegung im Datafile

Wie bekommen wir diesen Platz nun wieder frei? Viele der eingangs genannten Komponenten bieten Optionen, diese von ihrem angestammten Platz zu bewegen. Die View v$_sysaux_occupants gibt hierüber Auskunft:

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;


Item                      GB Schema           Move Procedure
---------------------- -------- -------------- ------------------------------------
AO                        0.00 SYS            DBMS_AW.MOVE_AWMETA
AUDIT_TABLES       0.00 SYS            DBMS_AUDIT_MGMT.move_dbaudit_tables
AUTO_TASK                 0.00 SYS
EM                        0.00 SYSMAN         emd_maintenance.move_em_tblspc
EM_MONITORING_USER        0.00 DBSNMP
EXPRESSION_FILTER         0.00 EXFSYS
JOB_SCHEDULER             0.01 SYS
PL/SCOPE                  0.00 SYS
LOGMNR                    0.01 SYSTEM         SYS.DBMS_LOGMNR_D.SET_TABLESPACE
LOGSTDBY                  0.00 SYSTEM         SYS.DBMS_LOGSTDBY.SET_TABLESPACE
ORDIM                     0.00 ORDSYS         ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDDATA             0.00 ORDDATA        ordsys.ord_admin.move_ordim_tblspc
ORDIM/ORDPLUGINS          0.00 ORDPLUGINS     ordsys.ord_admin.move_ordim_tblspc
SDO                       0.00 MDSYS          MDSYS.MOVE_SDO
SMON_SCN_TIME             0.00 SYS
SM/AWR                  498.03 SYS
SM/ADVISOR                0.02 SYS
SM/OPTSTAT                0.01 SYS
SM/OTHER                  0.01 SYS
SQL_MANAGEMENT_BASE       0.00 SYS
STATSPACK                 0.00 PERFSTAT
STREAMS                   0.00 SYS
TEXT                      0.00 CTXSYS         DRI_MOVE_CTXSYS
ULTRASEARCH               0.00 WKSYS          MOVE_WK
ULTRASEARCH_DEMO_USER     0.00 WK_TEST        MOVE_WK
XDB                       0.05 XDB            XDB.DBMS_XDB.MOVEXDB_TABLESPACE
XSOQHIST                  0.00 SYS            DBMS_XSOQ.OlapiMoveProc
XSAMD                     0.00 OLAPSYS        DBMS_AMD.Move_OLAP_Catalog
WM                        0.00 WMSYS          DBMS_WM.move_proc
TSM                       0.00 TSMSYS

Script 8: Move-Prozeduren im Sysaux-Tablespace ermitteln

Der Logminer beispielsweise bietet die Prozedur sysmdbms_logmnr_d.set_tablespace, um seine Komponenten zu verschieben. XDB kann mit xdb.dbms_xdb_movexdbtablespace bewegt werden. Diese Prozeduren können Sie nun dazu verwenden, die Objekte der jeweilgen Komponenten vorübergehend in einen anderen Tablespace zu verschieben, das Datafile des Sysaux-Tablespaces zu verkleinern und anschließend die Daten der Komponente wieder zurückzulegen.

Es gibt aber auch Komponenten, die keine solche Prozedur bereitstellen. Das AWR ist eine davon. Was also tun? Eine Möglichkeit ist, die hinteren Segmente im Datafile über eine Reorganisation freizuräumen.

Wenn Statistiken zu viel Platz benötigen

Historien von Datenbankstatistiken können eine Menge Platz benötigen – gerade wenn der Statistiklevel auf "ALL"und dann noch die Aufbewahrungszeit auf einen recht langen Zeitraum gesetzt wurde.

Statistiken werden nach Ablauf der Aufbewahrungszeit automatisch gelöscht. Der Standardwert ist 31 Tage. Wie dies in Ihrem System eingestellt ist, zeigt Ihnen die folgende Abfrage:

select dbms_stats.get_stats_history_retention from dual;

Script 9: Ermitteln der Aufbewahrungszeit von Statistiken

Den Wert können Sie leicht ändern. Die Prozedur dbms_stats.alter_stats_history_retention nimmt als Parameter die Anzahl an Tagen entgegen, nach denen Datenbank-Statistiken zu löschen sind:

exec dbms_stats.alter_stats_history_retention(10);

Script 10: Ändern der Aufbewahrungszeit von Statistiken

Um nun aber veraltete Statistiken sofort zu entsorgen, können Sie auch purge_stats verwenden. Hier wird ein Zeitpunkt übergeben, im folgenden Beispiel genau jetzt vor 10 Tagen:

exec DBMS_STATS.PURGE_STATS(sysdate -10);

Script 11: Löschen veralteter Statistiken

Ob die Statistiken auch tatsächlich gelöscht wurden, sehen Sie mit folgendem Statement:

select dbms_stats.get_stats_history_availability from dual;

Script 12: Prüfen, wie alt die ältesten Statistiken sind

Der Platz im Tablespace ist jetzt jedoch noch nicht zwingend freigegeben. Das liegt daran, dass die Statistik-Tabellen zwar geleert wurden, den Platz aber dennoch nicht deallokieren. Eventuell wird nun eine Reorganisation nötig. Sie können zunächst versuchen, wie oben beschrieben das Datafile zu verkleinern. Sollte dies nicht möglich sein, müssen Sie weiter analysieren.

Das Automatic Workload Repository bereinigen

Ob das AWR oder eine andere Datenbank-Komponente der wichtigste Platzverbraucher ist, zeigt wieder die View v$sysaux_occupants:

SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES
FROM   V$SYSAUX_OCCUPANTS
ORDER BY 1;

Script 13: Speicherplatz im Sysaux-Tablespace prüfen

Im folgenden Beispiel belegt das Automatic Workload Repository (AWR) rund 500 GB:

OCCUPANT_NAME             SPACE_USAGE_KBYTES
------------------------- ------------------
AO                                      1536
AUDIT_TABLES                               0
AUTO_TASK                                320
EM                                         0
EM_MONITORING_USER                       896
EXPRESSION_FILTER                       1664
JOB_SCHEDULER                           6784
LOGMNR                                 13824
LOGSTDBY                                1408
ORDIM                                      0
ORDIM/ORDDATA                              0
ORDIM/ORDPLUGINS                           0
ORDIM/SI_INFORMTN_SCHEMA                   0
PL/SCOPE                                1408
SDO                                        0
SM/ADVISOR                             22976
SM/AWR                             498527904
SM/OPTSTAT                           1476032
SM/OTHER                                6976
SMON_SCN_TIME                           3328
SQL_MANAGEMENT_BASE                     1728
STATSPACK                                  0
STREAMS                                 1024
TEXT                                       0
TSM                                        0
ULTRASEARCH                                0
ULTRASEARCH_DEMO_USER                      0
WM                                         0
XDB                                    57664
XSAMD                                      0
XSOQHIST                                1536

Skript 14: Automatic Workload Repository

Wie holen wir uns nun den Platz zurück? Ganz einfach: Die Prozedur drop_snapshot_range im Datenbank-Package dbms_workload_repository löscht veraltete Schnappschüsse. Doch dazu muss man wissen, welche Schnapsschüsse gelöscht werden sollen. Diese Information holen wir uns aus der View dba_hist_snapshot:

select min(snap_id),  min (begin_interval_time) as startzeit, 
      max(snap_id),  max (end_interval_time)   as endezeit
      from dba_hist_snapshot;

MIN(SNAP_ID) START                      MAX(SNAP_ID) ENDE
------------ -------------------------- ------------ ---------------------------
         605 28-SEP-13 01.00.41.889 AM         15809  06-OCT-14 02.00.24.538 PM

Script 15: Ermitteln der Schnappschüsse

Möchte man alle Schnappschüsse sehen, fragt man wie folgt ab:

select snap_id, begin_interval_time,end_interval_time
from   dba_hist_snapshot
order by 1

Script 16: Historie der Schnappschüsse

Beim Aufruf der Prozedur

dbms_workload_repository.drop_snapshot_range 

übergibt man als ersten Parameter die minimale Schnapschuss-ID und als zweiten die maximale Schnappschuss-ID. Ein Beispiel:

exec dbms_workload_repository.drop_snapshot_range(605, 15700);

Script 17: Alte Schnappschüsse löschen

Oft bleiben zudem verwaiste Daten in einigen AWR-Tables bestehen. Gerade bei Tabellen wie wrh$latch_children kommt dies vor, wenn der MMON-Prozess aus irgendeinem Grund die Bereinigung nicht ordnungsgemäß beenden konnte. Folgendes Script erstellt dynamisch die passenden Statements, um solche Waisen zu entfernen:

set pages 0 
set heading off
set lines 200
spool loesche_verwaiste.sql

select 'delete from '||table_name||' where snap_id not in (select snap_id
from WRM$_SNAPSHOT) and dbid=(select DBID from v$database);' from dba_tables
where table_name like 'WRH$%';

spool off

Script 18: Verwaiste Sätze des AWR löschen

Das Spoolfile loesche_verwaiste.sql enthält nun die passenden Statements zum Bereinigen. Prüfen Sie die erzeugten Statements sorgfältig und führen Sie sie nicht aus, ohne zuvor eine Vollsicherung der Datenbank gemacht zu haben.

Verkleinern des Sysaux-Tablespace

Auch wenn veraltete Statistiken gelöscht sind, wird der Speicherplatz nicht sofort wieder freigegeben. Oft sind Indizes übergroß und Tabellen belegen einen Datenblock irgendwo ganz hinten im Datafile. In diesem Fall lassen sich Datafiles des Tablespaces nicht verkleinern. Aber auch hierfür gibt es Abhilfe: Nach einer Reorganisation ist wieder Platz, die Daten-Dateien des Sysaux-Tablespaces können dann wieder verkleinert werden.

Zunächst einmal sollten Sie den Bestand prüfen. Die aktuelle Größe der Speichersegmente können Sie mit der folgenden Abfrage ermitteln:

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a20
set lines 120

select sum(bytes/1024/1024) Mb, owner, segment_name,segment_type from dba_segments
where tablespace_name = 'SYSAUX'
group by owner, segment_name,segment_type
order by 1;

Script 19: Segmentgrößen im Sysaux-Tablespace ermitteln

Hier sehen Sie auch den Typ der Segmente: Tabellen, Tabellen-Partitionen, Indizes und Index-Partitionen sind es meist. Gelegentlich sind auch Lob-Segmente darunter. In den nächsten Abschnitten betrachten wir genauer, wie sich die einzelnen Segment-Typen richtig reorganisieren und verkleinern lassen.

Reorganisation von Tabellen und Tabellen-Partitionen im Sysaux-Tablespace

Eines vorweg: Lassen Sie die Finger von Tabellen der XMLDB. Ein Reorg derselben führt gelegentlich zu Fehlern, die sich nur durch Neuinstallation von XMLDB beheben lassen. Andere Tabellen dagegen können durch ein Move verkleinert werden. Testen Sie die Effekte dennoch vorab in einer Testumgebung und sichern Sie vor einer Reorganisation sicherheitshalber Ihre Datenbank. 

Tabellen lassen sich mit dem Befehl alter table <tabellen_name> move reorganisieren. Ein Beispiel:

alter table sys.WRI$_OPTSTAT_IND_HISTORY move;

Script 20: Tabelle reorganisieren und verkleinern

Eine Tabelle nach der anderen manuell zu reorganisieren, ist jedoch ein mühseliges Handwerk. Einige Tools wie zum Beispiel Quest Toad bieten hier Unterstützung. Doch man kann auch einfach mit SQL-Scripting einiges automatisieren und vereinfachen. Untenstehendes Script reorganisiert alle Tabellen des Sysaux-Tablespaces. Aber auch hier gilt: Bitte nicht so einfach mal im Produktivsystem testen – schon gar nicht im laufenden Betrieb und auch nicht ohne Datensicherung vorab. Testen Sie in einem separaten System!

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for tab in ( select owner, segment_name from dba_segments
                where tablespace_name = 'SYSAUX'
                and segment_type='TABLE'
                and partition_name is null
                order by 1, 2) loop
    begin
      l_sql := 'alter table ' ||
                tab.owner     || '."' || tab.segment_name || '" move';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 21: Alle Tabellen reorganisieren und verkleinern

Bei partitionierten Tabellen müssen die Partitionen einzeln reorganisiert werden. Das folgende Script extrahiert die Segmente der einzelnen Table Partitions und verkleinert sie:

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for tab in ( select owner, segment_name, partition_name from dba_segments
                where tablespace_name = 'SYSAUX'
                and segment_type='TABLE PARTITION'
                and partition_name is not null
                order by 1, 2, 3 asc) loop
    begin
      l_sql := 'alter table ' ||
                tab.owner     || '."' || tab.segment_name ||
                '" move partition "'   || tab.partition_name || '"';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 22: Tabellenpartitionen reorganisieren und verkleinern

Sollten auch Subpartitionen bestehen, ist eine kleine Modifikation nötig:

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for tab in ( select owner, segment_name, partition_name from dba_segments
                where tablespace_name = 'SYSAUX'
                and segment_type='TABLE SUBPARTITION'
                and partition_name is not null
                order by 1, 2, 3 asc) loop
    begin
      l_sql := 'alter table ' ||
                tab.owner     || '."' || tab.segment_name ||
                '" move subpartition "'   || tab.partition_name || '"';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 23: Subpartitionen reorganisieren

Verkleinern von LOB-Segmenten

LOB steht für Large Objects. Oracle speichert in LOB-Spalten große binäre Objekte wie Bilder und Dateien sowie große Zeichenketten beispielsweise in Form von XML-Daten. Welche LOB-Segmente Im Sysaux-Tablespace gespeichert sind, zeigt die folgende Abfrage:

set pagesize 3000
set linesize 3000
col owner format a20

select e.file_id, max(e.block_id ), l.owner, l.table_name, l.column_name,
       e.segment_name, segment_type, partition_name
       from   dba_extents e, dba_lobs l
where  e.tablespace_name = 'SYSAUX'
and    e.owner = l.owner
and    e.segment_name = l.segment_name
group  by e.file_id, l.owner, l.table_name, l.column_name,
       e.segment_name, segment_type, partition_name
order by 1, 2;

Script 24: Lob-Segmente ermitteln

Die Ausgabe ist nach der maximalen Block-ID des Segments sortiert. So können Sie sehen, welche Lob-Segmente weit hinten im Datefile liegen.

Um ein Lob-Segment zu verkleinern, kann die folgende Syntax verwendet werden:

Alter table  <table_name> modify lob (<lob_column>) shrink space;

Script 25: Lob-Segmente verkleinern

Oder auch ab Oracle Database 11g:

alter table <table_name> enable row movement;
alter table <table_name> shrink space cascade;

Script 26: Lob-Segmente verkleinern mit Oracle Database 11g

Reorganisation von Index-Segmenten im Sysaux-Tablespace

Segmente vom Typ Index reorganisieren Sie mit dem Befehl:

alter Index <index_name> rebuild; 

Script 27: Index reorganisieren und verkleinern

Ein Beispiel:

alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;

Script 28: Index reorganisieren und verkleinern (Beispiel)

Wenn man sich den Reorg der Indizes ganz einfach machen möchte, kann man auch hier wieder mit einem Script alle Indizes im Sysaux-Tablespace  operieren. Das sollten Sie selbstverständlich zuvor in einem Testsystem prüfen und nicht einfach im laufenden Betrieb ausführen. Bevor Sie eine solche Prozedur in einer Datenbank durchführen, machen Sie bitte eine Datensicherung.

Hier das Script für die Reorganisation von Indizes:

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for ind in ( select owner, segment_name from dba_segments
                where tablespace_name = 'SYSAUX'
                and segment_type='INDEX'
                group by segment_name,owner, segment_type
                order by 1, 2 asc) loop
    begin
      l_sql := 'alter index ' ||
                ind.owner     || '. "' || ind.segment_name || '" rebuild';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 29: Alle Indizes reorganisieren

Index-Partitionen benötigen für den Rebuild eine erweiterte Syntax:

alter index <index_name> rebuild partition <partition_name>;

Script 30: Erweiterte Syntax für den Rebuild

Ein Beispiel:

alter index sys.WRH$_PARAMETER_PK  rebuild partition WRH$_PARAMETER_MXDB_MXSN;

Script 31: Index-Partitionen reorganisieren

Auch dies lässt sich wieder in ein  Script für alle Indizes des Sysaux-Tablespaces packen:

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for ind in ( select owner, segment_name, partition_name from dba_segments
                where tablespace_name = 'SYSAUX'
                and segment_type='INDEX PARTITION'
                group by segment_name,owner, segment_type, partition_name
                order by 1, 2, 3 asc) loop
    begin
      l_sql := 'alter index ' ||
                ind.owner     || '. "' || ind.segment_name ||
                '" rebuild partition "' || Ind.partition_name || '"';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 32: Alle Index-Partitionen reorganisieren

Prüfen, ob alle Objekte valide sind

Zum Abschluss der Reorganisation einer Oracle-Datenbank sollten Sie stets prüfen, ob alle Objekte der Datenbank valide sind. Indizes beispielsweise können Sie über die View dba_indexes prüfen. Sie enthält die Spalte status, die folgende Werte annehmen kann

  • VALID: Valide, alles ist in Ordnung
  • UNUSABLE: Der Index ist nicht in Ordnung und muss mit einem Rebuild reorganisiert werden.
  • N/A: Not available, dieser Status wird bei partitionierten Indizes angezeigt, da jede einzelne Index-Partition ihrerseits valide oder nicht benutzbar sein kann. Hier müssen Sie in die passende View (dba_ind_partitions und dba_ind_subpartitions) schauen, um den Status der einzelnen Partitionen zu ermitteln.

Indizes, die aktuell nicht nutzbar (unusable) sind, ermitteln Sie mit der folgenden Abfrage:

select owner, index_name, status from dba_indexes where status = 'UNUSABLE';

Script 33: Auf invalide Indizes prüfen

Auch hier können Sie einen unusable index reparieren, indem Sie ein Index-Rebuild ausführen. Folgendes Script führt ein Rebuild aller Indizes aus, die im Status unusable sind.

set serveroutput on
declare
  l_sql varchar2(4000);
begin
   for ind in ( select owner, index_name from dba_indexes
                where status = 'UNUSABLE'
                group by owner, index_name
                order by 1, 2 asc) loop
    begin
      l_sql := 'alter index ' ||
                ind.owner     || '.' || ind.index_name ||
                ' rebuild';
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
      dbms_output.put_line('Erfolgreich reorganisiert');
    exception when others then
      dbms_output.put_line ('Fehler: ' || sqlcode);
    end;
   end loop;
end;
/

Script 34: Invalide Indizes rebuilden

Abschließend sollten Sie nochmals die Abfrage aus Script 33 ausführen und prüfen, ob alle Indizes valide sind.

Partitionierte Indizes prüfen Sie über die Views dba_ind_partitions und dba_ind_subpartitions.

select distinct status from dba_ind_partitions;
select distinct status from dba_ind_subpartitions;

Script 35: Auf invalide Index-Partitionen und Subpartitionen prüfen

Auch hier erfolgt die Reparatur mit einer alter index rebuild, jedoch unter der Angabe des Partitionsnamens.

Packages, Prozeduren und Funktionen rekompilieren

An diesem Punkt ist die Reorganisation abgeschlossen. Prüfen Sie sicherheitshalber jetzt noch, dass auch alle Packages, Prozeduren und Funktionen im Status "VALID" sind. Ist dies nicht der Fall, sollten Sie diese rekompilieren. Hier ist die Prüfung:

select owner, object_name, object_type from dba_objects where status != 'VALID';

Script 36: Auf invalide Objekte prüfen

Die Rekompilierung mit dem Script utlrp.sql ist recht einfach. Es liegt im Oracle-Home-Verzeichnis unter rdbms/admin. Kurz zur Syntax: @ steht für die Script-Ausführung, ? ist ein Platzhalter für das Oracle-Home. Melden Sie sich auf dem Datenbank-Host mit SQLPLUS zunächst als sysdba an und führen es aus:

@?/rdbms/admin/utlrp.sql

Script 37: Invalide Objekte rekompilieren

Prüfen Sie abschließend erneut, ob alle Objekte in der Datenbank im Status "VALID" sind.

Prävention: Was tun, damit der Sysaux-Tablespace gar nicht erst so sehr wächst?

Damit der Sysaux-Tablespace gar nicht erst so groß wird, kann man vorab einiges tun. Zunächst einmal sollten Sie prüfen, welche Komponenten Sie wirklich nutzen. Werden die Daten tatsächlich gebraucht? Auch in diesem Umfang? Das AWR beispielsweise sammelt mehr oder weniger Daten je nach Einstellung. Der Parameter statistics_level gibt darüber Auskunft. Ihn können Sie beispielsweise in SQLPlus mit dem Befehl show parameter anzeigen lassen:

SQL> show parameter statistics_level

Script 38: Statistik-Level in SQLPLus ermitteln

Alternativ kann ein Datenbank-Benutzer mit DBA-Privilegien auch die View v$parameter abfragen:

select value from v$parameter where name = 'statistics_level';

Script 39: Statistik-Level mit SQL ermitteln

Folgende Werte können hier eingestellt werden:

  • BASIC: Es werden nur einige wenige Statistiken gespeichert.
  • TYPICAL: Bei dieser Einstellung werden typische Statistiken für das Monitoring erzeugt. Dies ist der Default-Wert.
  • ALL: Umfangreiche Statistiken werden erzeugt. Diese Einstellung ist für eine eingehende Diagnose geeignet.

Den Parameter können Sie dynamisch zur Laufzeit mit dem Befehl alter system umsetzen:

alter system set statistics_level='TYPICAL';

Script 40: Statistik-Level ändern

Wie häufig Schnappschüsse erstellt werden und wie die Aufbewahrungszeit konfiguriert ist, zeigt das folgende Statement:

select snap_interval, retention from dba_hist_wr_control;

Script 41: Schnapschuss-Interval für Statistiken ermitteln

Beide Parameter kann man recht einfach anpassen. Die Aufbewahrungszeit ist in Minuten anzugeben. Der folgende Befehl setzt sie auf 7.200 Minuten – also 5 Tage:

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>7200) 

Script 42: Aufbewahrungszeit von Datenbank-Statistiken ändern

Und mit dem folgenden Statement werden Schnappschüsse des Systems nur noch alle 2 Stunden erstellt:

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>120) ;

Script 43: Schnappschuss-Interval ändern

Beim ersten Befehl kann es passieren, dass Sie folgende Fehlermeldung erhalten:

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>10080); 
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>10080); END;

*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (86400)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1

Script 44: Moving Baseline Window

In diesem Fall ermitteln Sie das “Moving Window” für Baselines:

SELECT moving_window_size
FROM   dba_hist_baseline
WHERE  baseline_type = 'MOVING_WINDOW';

Script 45: Moving Baseline Window ermitteln

Den Wert ändern Sie wie folgt:

exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7);

Script 46: Moving Baseline Window ändern

Den Sysaux-Tablespace verkleinern

Haben Sie den Tablespace reorganisiert, können Sie anschließend zu Script 4 zurückkehren und die zum Sysaux-Tablespace gehörenden Datafiles verkleinern. Prüfen Sie am besten vorab, auf welche Größe Sie das Datafile auch tatsächlich verkleinern können. Die höchste Block-ID des am weitesten hinten liegenden Speicher-Extents ist hierfür maßgeblich.

select f.file_name, (t.block_size*max(e.block_id)/1024/1024) MB
from dba_tablespaces t, dba_data_files f, dba_extents e
where e.tablespace_name = 'SYSAUX'
and   e.tablespace_name = f.tablespace_name
and e.tablespace_name = t.tablespace_name
group by f.file_name, t.block_size;

Script 47: Bis zu welcher Größe kann ein Datafile verkleinert werden?

Obiges SQL-Statement zeigt Ihnen, bis zu welcher Größe Sie das Datafile verkleinern können. Ok, dann der nächste Versuch:

SQL>  alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 10 G;
Database altered.

Script 48: Verkleinerung des Datafiles

So klappt's dann auch mit der Verkleinerung der Datafiles.

Fazit

Am besten ist es, von vorneherein dafür zu sorgen, dass der Sysaux-Tablespace sich nicht unnötig vergrößert. Ist es dennoch passiert, so lassen sich die Einstellungen zwar schnell ändern. Doch wird der Speicherplatz im Tablespace nicht so eben mal wieder freigegeben. Um den Platz zurückzugewinnen und Datafiles des Sysaux-Tablespaces wieder verkleinern zu können, ist eine Reorganisation notwendig.

Eine Reorganisation benötigt eine sorgfältige Vorbereitung. Sie sollten unbedingt vorab die betreffende Datenbank vollständig sichern, die Sicherung auf Validität prüfen und die Reorganisation in ein Wartungsfenster legen. Und: Testen Sie vorab intensiv in einem separaten Testsystem, bevor Sie eine Produktionsumgebung reorganisieren!

Autorin

Andrea Held

Andrea Held ist technische Architektin und Autorin von Fachartikeln und Büchern, darunter "Der Oracle DBA", "Oracle New Features" und "Oracle Hochverfügbarkeit". Ihr Schwerpunkt: Hochverfügbare Datenbanksysteme.
>> Weiterlesen
Bücher der Autorin:

botMessage_toctoc_comments_9210