Mittwoch, 9. November 2005 15h00, Musensaal Database „Siedler-DBA Edition“ – oder das effiziente Ausnutzen von Ressourcen in der DB Henrik Rünger ckc ag, Hannover Schlüsselworte: Reorganisation, Partitionierung, Komprimierung, Monitoring, SQL*Plus Features, move, resize, shrink Einleitung Dieser Vortrag soll aufzeigen, dass es immer wieder wichtig sein kann, vorhandene Möglichkeiten, die die DB bietet, zu nutzen. Anhand eines Beispielprojektes in dem die DB von 200GB (durch eine lückenhafte Planung zu groß geraten) verkleinert werden soll und auch nur eingeschränkte Personalressourcen zur Verfügung stehen sollen Ideen aufgezeigt werden mit denen dies schnell und effizient erledigt werden kann. Dabei kommen Techniken wie „transportable Tablespaces“, „Table-Shrink“, „alter table move tablespace“, „Resize“, „Sicherungen“, „Import / Export“ zur Sprache. Praktisch einzusetzen sein soll dieser Vortrag als Kochbuch für DBA Tätigkeiten. Des Weiteren soll gezeigt werden, dass die Datenbank eine Vielzahl von Möglichkeiten bietet, die es zu entdecken gilt. Auf dem Weg zum besseren Verständnis der Datenbank werden diese und andere Features kurz angerissen, so dass man bei Interesse die für sich wichtigen "Einstiegs-Infos" in den folgenden Wochen weiterverfolgen kann. Das Problem Nehmen wir einmal den Fall an, dass eine Datenbank geplant wird die für eine große Menge an Daten ausgelegt ist (schätzungsweise 200GB) und aus diesem Grund mit den entsprechenden Statements angelegt wird. Aufgrund von Schätzungen wird angenommen, dass diese Datenbank innerhalb von kürzester Zeit diese Menge von Daten enthalten wird und aufgrund des Haltens von Daten für nur einen bestimmten Zeitraum nicht weiter wachsen wird (siehe Abb. 1). Die Probleme an dieser Stelle sind zahlreich, sollte sich die Schätzung doch nicht als ganz richtig erweisen: 1. Was passiert, wenn mehr Speicher gebraucht wird? 2. Was passiert, wenn weitaus weniger Speicher benötigt wird? 3. Wie wird mit der Sicherung in den ersten Wochen umgegangen, wenn die Datenbank noch nicht mit Daten gefüllt ist? 4. wurde die Datafilegröße sehr klein gewählt (2 GB, ehemals Unix Grenze), so ist auch das Dateihandling bei einem Umzug sehr umständlich Abb. 1: ursprüngliche Erstellung der Tablespaces Das Ziel Nachdem ein Export der produktiven Daten und Import in das Testsystem vorgenommen wurde, kam heraus, dass die exportierten Daten nicht mehr als 11GB betrugen. Obwohl das System schon mehrere Monate im Einsatz war, stieg der Datenbestand doch nicht auf 200GB an. In der Testdatenbank betrug die Menge an Daten dann 56GB. Dies ist auf mehrere Gründe zurückzuführen: Database DB-Entwicklung 18. Deutsche ORACLE-Anwenderkonferenz 1. es sollten statt 100 Tage doch nur 60 Tage in der Historie gehalten werden 2. Indexe wurden entfernt, da Materialized für regelmäßige Auswertungen performanter waren 3. die Schätzung war inklusive Puffer und spiegelte somit nur eine ungefähre Maximalmenge des benötigten Speicherplatzes wieder Daraus lassen sich mehrere Ziele ableiten, durchzuführen in der angegebenen Reihenfolge. Für die vorhandene Hardwareumgebung wurden bei der anschließenden Umsetzung nebenstehende Zeiten benötigt: 1. Sicherung der produktiven Datenbank (um im Notfall zurückgehen zu können) • 3 Stunden 2. Export des entsprechenden Users, über den alle Daten angelegt wurden • 1,5 Stunden 3. Vergrößern des Undo-Tablespaces, um bei den umfangreichen fachlichen Datenlöschaktionen keinen Überlauf zu bekommen • 30 Minuten 4. Löschen des Users inklusive Inhalten • 1 Stunde 5. betroffene Tablespaces und Datafiles löschen • 10 Minuten 6. Tablespaces und Datafiles mit neuen Einstellungen wieder anlegen • 1,5 Stunden 7. Import der Userdaten inkl. Inhalten • 3 Stunden 8. Löschen von fachlichen Daten, um nur 60 Tage zu historisieren • 5 Stunden 9. Statistiken aktualisieren • 7 Stunden Externe Tools Auch wenn es in Zeiten von Window – getriebenen Applikationen schwer fällt ein Argument für die Kommandozeile auszusprechen, so hat sie sich für diese Aufgabe doch am besten bewährt. Natürlich gibt es zahlreiche Tools, inklusive des Oracle Enterprise Managers, die einen bei der Syntax unterstützen ohne dass man immer wieder in der Dokumentation nachschauen muss. Insofern fällt mein Urteil über externe Tools im Rahmen dieser Aufgabe auch so aus, dass es keinen Favoriten gibt, es aber von Vorteil ist, Tools zur Seite zu haben, mit denen man sich noch einmal die grundlegende Syntax einzelner Befehle vor Augen halten kann. Aufgrund der langen Laufzeit einzelner Statements ist es sicherer, diese direkt auf dem Datenbankserver auszuführen, um sich nicht dem Risiko auszusetzen, dass die Client-Verbindung abgebrochen wird. Wie im weiteren Verlauf deutlich wird, ist die Zahl der einzugebenden Kommandos so gering, dass der Aufwand mit Tools eher höher ist, als wenn man es von Hand vornimmt. Boardmittel Da die Anzahl der Möglichkeiten in der Oracle Datenbank mittlerweile sehr zahlreich sind, bietet es sich an, erst einmal zu schauen, was man davon nutzen kann um diese Aufgabenstellung zu lösen. Dabei lässt sich die Aufgabe grob in drei Bereiche Teilen: 1. Backup und Recovery 2. DB-Änderungen 3. fachliche Daten performant löschen Zu Punkt 1 gibt es dabei folgende Möglichkeiten: Abb. 2: Backup und Recovery Varianten Zu Punkt 2 bieten sich folgende Möglichkeiten an: Abb. 3: technische Hilfsmittel, um Grössenänderungen vorzunehmen Um fachliche Daten möglichst performant zu löschen, muss man natürlich ganz genau analysieren, wie diese aufgebaut sind. In diesem Fall bezieht es sich auf eine geringe Anzahl von Tabellen (acht Stück), ohne Constraints, mit einem bis zu 3 Indexes pro Tabelle. Die Inhalte variieren von 20 Millionen bis zu 130 Millionen. Gelöscht werden die Daten, indem eine Datumsspalte als Vergleichskriterium älter als 60 Tage benutzt wird: Abb. 4: Möglichkeiten zum Löschen von fachlichen Daten Umsetzungsmöglichkeiten An dieser Stelle soll eine Auflistung der möglichen Befehle stehen, um sie für zukünftige Projekte benutzen zu können. Das Anlegen eines Tablespaces mit neuen Datafiles. Um möglichst flexibel für zukünftige Datenmengen zu sein, empfiehlt es sich fast immer Datafiles automatisch wachsen zu lassen. Andererseits sollte es allerdings einen gewissen Spielraum geben, indem sich dieses Wachstum nur bewegen kann. Da Datenbankadministratoren eigentlich nicht den gesamten Rechner zur Verfügung haben, sondern nur einen bestimmten Speicherbereich, sollte eben auch der Speicherplatzbedarf schon innerhalb der Datenbank begrenzt werden. Database DB-Entwicklung 18. Deutsche ORACLE-Anwenderkonferenz Abb. 5: Tablespace anlegen mit Autoexend Möglichkeit Ein Offline Backup, um im Fehlerfall reibungslos zum Ausgangspunkt zurück zu kommen. Wenn man genug Speicherplatz zur Verfügung hat und die Datenbank auch nicht im Online Status bleiben muss, so ist dies eine sehr sichere Variante. Dafür müssen folgende Schritte durchgeführt werden: 1. shutdown 2. Sichern der relevanten Dateien – alle Datafiles – Init.ora und spfile.ora (opt) – alle Online Redo Logs – alle Control Files Beim Resize eines Datafiles kann sowohl das Vergrößern als auch das Verkleinern Ziel des Befehls sein. Beim Verkleinern wird die Datei am Ende gekürzt. Das bedeutet, es dürfen keine Segmente am Ende der Datei sein. Es gibt diverse Scripte und Möglichkeiten durch Dritthersteller die Datafilebelegung herauszufinden. Somit ist schnell zu erkennen, inwieweit ein Datafile verkleinert werden kann. Abb. 6: Verkleinern oder Vergrössern eines Datafiles Mit einem Table Shrink kann gearbeitet werden, wenn bekannt ist, dass es einzelne Tabellen gibt, die eine Menge Speicherplatz für sich beanspruchen. Mit dem ersten Befehl wird geschaut, wie viel Speicherplatz die Tabelle wirklich braucht und mit dem zweiten Befehl kann sie dann auf die entsprechende Größe verkleinert werden. Abb. 7: Erkennen von freiem Platz und anschließender Freigabe Klippen Nicht unterschätzt werden sollte der Zeitbedarf, der für das Löschen durch Delete erforderlich ist. Es ist von allen DML-Statements das Aufwendigste. Dafür sollte ein ausreichend großes Undo-Tablespace vorhanden sein. Des Weiteren ist es zeitaufwendig die Statistiken neu zu erstellen. Aus dem Grund kann es durchaus sinnvoll sein, vorhandene Statistiken zu exportieren und im System später wieder einzuspielen. Abb. 8: vorhandene Statistiken nutzen Ebenfalls ein langwieriger Prozess ist das Neuerstellen von Indexes. Aus dem Grund sollte, sofern der Platz vorhanden ist, die Möglichkeit des Online Rebuild gewählt werden Abb. 9: Index online neu erstellen Zusammenfassung Abb. 10: Orientierung an diversen Möglichkeiten Kontaktadresse: Henrik Rünger Wedekindstr. 1 D-30161 Hannover Telefon: +49(0)163-605 7678 E-Mail [email protected] Internet: www.ckc.de Database DB-Entwicklung 18. Deutsche ORACLE-Anwenderkonferenz