Dies ist der allgemeine Beitrag zu diesem Thema. Am Ende des Beitrags finden Sie einen Link zu dem detaillierten Beitrag (mit Source der Scripte)
Oracle 11g SYSAUX Growth, Bug 14373728 „Old Statistics not Purged from SYSAUX Tablespace“
Beim Betrieb von etlichen Datenbanken, gemischt Oracle 11.2.0.4.3 sowie 12.1.0.2.1, fielen im Vergleich die 11g Datenbanken durch exzessives Größenwachstum in SYSAUX auf – bei gleicher Datenlage wie eine entsprechend auf 12c aufgesetzte DB waren gigabyteweise mehr Daten im Tablespace SYSAUX vorzufinden.
Detailanalyse zeigte dann auf, dass die anwachsenden Daten unter „Automatic Workload Repository“ – AWR geführt sind.
Der folgende Link weist auf einen Artikel von Frau Held hin, eine hervorragende Fundstelle, die die dem Problem zugrunde liegenden Ursachen beschreibt.
der-oracle-sysaux-tablespace-reorganisation-und-verkleinerung
Dieser Beitrag hier geht noch etwas weiter und beschreibt die Möglichkeiten, eine Maschine zu bereinigen, die schon soviel Daten angehäuft hat, dass die Standardprozeduren in Resourcenprobleme laufen.
Ursache ist ein Bug, der scheinbar in der SOLARIS Release nicht gepatcht war:
Auswirkung:
Diese Übersicht kommt aus dem EM. Man kann sie sich auch manuell holen:
Select * from v$sysaux_occupants order by SPACE_USAGE_KBYTES DESC
Wir sehen hier eine Vielzahl von Tabellen und Indizes (partitioned), die definitiv zu groß sind.
Über awrinfo kann man sich die zu erwartenden Größen anzeigen lassen.
Das awrinfo.sql script findet man im $ORACLE_HOME/rdbms/admin directory
Dort findet man auch eine Schätzung der zu erwartenden Größen des AWR-Bereichs aufgrund der eingestellten Retentionparameter. Üblich ist z.B. ein stündlicher Snapshot für 7 Tage oder 30 Tage.
Die Informationen kann man sich natürlich auch dediziert holen:
SELECT DBID, MIN(SNAP_ID), MIN(BEGIN_INTERVAL_TIME), MAX(SNAP_ID), MAX(BEGIN_INTERVAL_TIME)
from dba_hist_snapshot group by dbid;
ergibt dann beispielsweise:
DBID MIN(SNAP_ID) MIN(BEGIN_INTERVAL_TIME) MAX(SNAP_ID) MAX(BEGIN_INTERVAL_TIME)
———- ———— ————————— ———— —————————
2996453776 15599 26.10.17 00:00:17,112000000 15806 03.11.17 14:00:23,434000000
Verändert werden können die Parameter, die die Frequenz der Berechnung und Aufbewahrungszeit steuern, zum Beispiel so:
— change retention time
— this is in minutes chg to 4d
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>5760);
Aufgrund eines Bugs in der Release 11g kommt es jedoch vor, dass der physische Speicherbedarf unlimitiert anwächst, da alte Snapshots nicht entfernt werden.
Diese müssen dann manuell entfernt werden. Dafür gibt es eine Standardprozedur:
DBMS_WORKLOAD_REPOSITORY. DROP_SNAPSHOT_RANGE
(LOW_SNAP_ID => MIN_ID, HIGH_SNAP_ID => MAX_ID, DBID => DBID);
Zu füttern ist der Aufruf mit der DBID sowie dem Range der verwaisten Snapids. Später liefern wir ein Script, mit dem man diese ermitteln kann.
Ausgabe Simplescript:
Partition_name WRH$_ACTIVE_413047826_8381
i 1 dbid 413047826 : WRH$_ACTIVE_413047826_8381 MinInPartition 8381 MaxInPartition 8404
—
Anzahl verwaister Records ist 6253 MinOrphaned is 8381 MaxOrphaned is 8403
—
Executing: DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE ( low_snap_id ->8381; high_snap_id ->8403; dbid ->413047826);
simple script kann DROP_SNAPSHOT_RANGE direkt aufrufen, das ist die Komfortlösung.
Ausgabe Detailscript:
DBID in DBA_HIST_WR_CONTROL 413047826
–
===0> Hier stehen die inkonsistenten Daten für das manuelle Delete ==>
DBID in WRH$_EVENT_HISTOGRAM 413047826 MIN SNAPID 8381 MAX SNAPID 8403
–
===0> Und hier stehen die aktuellen Daten für den zeitnahen Bereich ==>
DBID in DBA_HIST_SNAPSHOT 413047826 MIN SNAPID 8404 MAX SNAPID 8611 MINT 26.10.17 00:00:43,119
Die Werte für den Aufruf von DROP_SNAPSHOT_RANGE kann man hier direkt ablesen und einsetzen.
Im Normalfall wäre man dann schon fertig.
Leider passiert es, wenn die Datenbanken längere Zeit, viele Monate ohne diese Pflege laufen, dass sich soviele Daten in SYSAUX AWR ansammeln, dass die DROP_SNAPSHOT_RANGE-Procedur in solch große Resourcenprobleme kommt, dass das Ganze nicht mehr handelbar ist.
Dafür haben wir dann ein Script gebaut, das resourcenschonend Objekt für Objekt in handlichen Chunks löscht (jeweils commited), und dies in von außen begrenzbaren Iterationen. So konnten wir letztlich das Größenwachstum in den Griff bekommen.
Der nächste nötige Schritt war jedoch zunächst der Reorg der einfachen und partitionierten Indizes: es reicht nicht aus, einfach nur die verwaisten snapids aus der Tabelle zu löschen, es gab dann immer noch gigabyteweise Indizes. Dafür haben wir weitere Scripte gebaut. Erforderlich war das, um die partitionierten Indizes automatisch zu reorganisieren sowie um Reihenfolgen zu beachten. Letztlich gab es einen weiteren Bug, der kurioserweise dafür sorgte, dass nach Reorganisieren eines Index plötzlich ein anderer Index wieder korrupt war. Nachlesen kann man das im Oracle Metalink unter Rebuilding Many Indexes on Sysaux Causes ORA-1502 Error on ‚SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST‘ (Doc.ID 2294203.1)
Im Wesentlichen bedeutet es, dass man im Script anfangen und/oder enden muss mit diesen beiden expliziten Zeilen:
— geschuldet (Doc ID 2294203.1)
ALTER INDEX SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST REBUILD TABLESPACE SYSAUX
ALTER INDEX SYS.I_WRI$_OPTSTAT_IND_ST REBUILD TABLESPACE SYSAUX
Erst jetzt, nachdem die Tabellen bereinigt und die Indizes reorganisiert sind, kann man anfangen darüber nachzudenken, wieder physischen Speicherplatz zu gewinnen.
Auch dafür haben wir ein Script für diesen Spezialfall, welches gleich den ALTER Befehl zum Verkleinern des Tablespaces mitliefert:
FileSize SYSAUX 13713
BEFORE : Allocierter Platz (GESAMT) in SYSAUX is 4326,25 MB
DB : Freespace in SYSAUX is 9386,75 MB
BEFORE : Groesse AWR Objekte in SYSAUX is 288,6875 MB
DB : Freespace in SYSAUX is 9384,75 MBFile /u01/oradata/DB/datafile/o1_mf_sysaux_8xtqyfp2_.dbf smallest 6857 currsize 6966 max. Ersparnis 109
File /u01/oradata/DB/datafile/o1_mf_sysaux_8xtrp6s7_.dbf smallest 6728 currsize 6747 max. Ersparnis 19Optimale empfohlene Mindestgrösse von 5624 MB
ALTER CMDS TO GAIN DISK SPACE:
alter database datafile ‚/u01/oradata/DB/datafile/o1_mf_sysaux_8xtqyfp2_.dbf‘ resize 6857m;
alter database datafile ‚/u01/oradata/DB/datafile/o1_mf_sysaux_8xtrp6s7_.dbf‘ resize 6728m;
Nächstes Kapitel:
Im Folgeartikel wird mehr auf die Zusammenhänge und detaillierten Arbeitsschritte eingegangen.