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 5000MScript 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 1Script 16: Historie der Schnappschüsse
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 offScript 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
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
Alter table <table_name> modify lob (<lob_column>) shrink space;Script 25: Lob-Segmente verkleinern
alter table <table_name> enable row movement;Script 26: Lob-Segmente verkleinern mit Oracle Database 11g
alter table <table_name> shrink space cascade;
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
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
alter index <index_name> rebuild partition <partition_name>;Script 30: Erweiterte Syntax für den Rebuild
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.
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
select distinct status from dba_ind_partitions;Script 35: Auf invalide Index-Partitionen und Subpartitionen prüfen
select distinct status from dba_ind_subpartitions;
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
@?/rdbms/admin/utlrp.sqlScript 37: Invalide Objekte rekompilieren
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_levelScript 38: Statistik-Level in SQLPLus ermitteln
select value from v$parameter where name = 'statistics_level';Script 39: Statistik-Level mit SQL ermitteln
- 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.
alter system set statistics_level='TYPICAL';Script 40: Statistik-Level ändern
select snap_interval, retention from dba_hist_wr_control;Script 41: Schnapschuss-Interval für Statistiken ermitteln
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>7200)Script 42: Aufbewahrungszeit von Datenbank-Statistiken ändern
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL=>120) ;Script 43: Schnappschuss-Interval ändern
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>10080);Script 44: Moving Baseline Window
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
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
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?
SQL> alter database datafile '/u02/oradata/numarcr/sysaux01.dbf' resize 10 G;Script 48: Verkleinerung des Datafiles
Database altered.
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!
Publikationen
- Oracle 12c New Features: Inklusive Release 2
- Der Oracle DBA: Handbuch für die Administration der Oracle Database 12c
- Oracle 11g. Neue Features für Administratoren und Entwickler
- Oracle 10g Hochverfügbarkeit. Die ausfallsichere Datenbank mit RAC, Data Guard und Flashback Edition Oracle
Tor Bye
am 23.11.2021Ich habe Ihre Anweisungen zum Reduzieren des Sysaux-Tablespace befolgt, aber ich habe ein Problem mit meiner alten Oracle 11g-Version 11.2.0.4 (Redhat). Wenn ich versuche, den LOBSEGMENT-Teil zu reduzieren oder wenn ich versuche, die Daten des XDB-Benutzers in einen anderen Tablespace zu verschieben, erhalte ich Fehler.
Ich habe z.B. a LOBSEGMENT wie folgt als höchster Blockwert im sysaux-Tablespace.
2 2025728 XDB XDB$RESSOURCE"XMLDATA"."XMLLOB"SYS_LOB0000069260C00025$$ LOBSEGMENT. Wenn ich versuche, das DBMS_ADMIN-Paket zu verwenden:
exec DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE ('XDBDS');
BEGIN DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE ('XDBDS'); ENDE;
*
FEHLER in Zeile 1:
ORA-01403: keine Daten gefunden
ORA-06512: bei "XDB.DBMS_XDB_ADMIN", Zeile 969
ORA-06512: an der Linie 1
Offensichtlich mache ich etwas falsch. Ich weiß, dass dies ein alter Artikel ist, daher weiß ich, dass diese Frage nicht ganz oben auf Ihrer Liste steht. Aber ich wäre Ihnen sehr dankbar, wenn Sie mir einen Hinweis geben könnten, woran es liegt. (Dieses Problem stammt von einem unserer deutschen Kunden)
Mit freundlichen Gruessen
Tor Egil Bye
Andrea Held
am 23.11.2021Herzliche Grüße
Andrea Held