Im Leben eines DBAs kann es mal vorkommen, dass man einen Tablespace reorganisieren muss. Häufig tut man dies, um wieder Freiplatz auf dem Datenbank-Storage zu bekommen, nachdem sich ein Tablepace aufgrund eines Ereignisses „aufgebläht“ hat oder sehr viele Daten gelöscht wurden. Ich selbst hatte kürzlich das Problem, dass eine Datenbank in Folge einer Unicode-Migration von 200 auf 500 GB angewachsen ist. Bei genauerer Betrachtung stellte sich heraus, dass für das Wachstum Spalten vom Datentyp CLOB verantwortlich waren. Obwohl die CLOBs insgesamt eine Segment-Größe von 400 GB hatten, waren nur 200 GB davon tatsächlich belegt. Sprich, ich hatte über 200 GB Luft ist den CLOB-Segmenten! Da ein „Shrinking“ der dem Tablespace zugehörigen Datenbankdateien in diesem Fall nicht möglich war, musste der Tablespace also in geringer Größe neu angelegt und die Datenbankobjekte dort hineinbewegt werden. Dafür gibt es verschiedene Methoden wie z.B. das Umbewegen aller Objekte mittels „Alter … move“-Befehle. Dieses Verfahren hat allerdings den Nachteil hat, dass man die Objekte zunächst in einen „Zwischen“-Tablespace umziehen lassen muss, um sie dann noch einmal in den neu erstellten ursprünglichen Tablespace umzubewegen. Außerdem kann dies sehr schnell sehr komplex und aufwendig werden, wenn es sich dabei um sehr viele Tabelle mit vielen Constraints und Indexe handelt, welche ebenfalls berücksichtigt werden müssen.
Ich möchte in diesem Blog-Beitrag eine Methode aufzeigen, die recht robust und relativ einfach ist und Oracle Data Pump nutzt. Die Tablespace-Inhalte werden dabei exportiert und, nachdem der Tablespace wieder verkleinert angelegt wurde, wieder importiert. Bei diesem Verfahren muss man allerdings sicherstellen, dass der Tablespace, den man reorganisieren möchte, absolut in sich geschlossen ist (self-contained). Das bedeutet, dass es keine Abhängigkeiten von und zu Objekten gibt, die sich in anderen Tablespaces befinden (z.B. Constraints, Indexe usw.). Sollte dies der Fall sein, was nicht ungewöhnlich ist, muss man die anderen Tablespaces ebenfalls mit in die Reorganisation einschließen. Die Prüfung der „In-Sich-Geschlossenheit“ kann man komfortabel mit der Oracle Prozedur DBMS_TTS.TRANSPORT_SET_CHECK durchführen, welche in der Regel im Zusammenhang mit der Transaportable Tablespaces Technologie genutzt wird. Da die Daten aus der Datenbank exportiert werden, muss man gewährleisten, dass man ein Laufwerk zur Verfügung hat, in dem genug Freiplatz zur Aufnahme des Exports-Dumps vorhanden ist. Kurz erwähnen möchte ich noch, dass diese Art von Tablespace Reorganisation nicht für die Tablespaces SYSTEM und SYSAUX angewendet werden kann, da diese beiden Tablespaces ständig verfügbar sein müssen.
1. Aktuelle Größe der Tablespace anzeigen
set linesize 130 pagesize 100 col tablespace_name for a30 select tablespace_name, round(sum(bytes)/1024/1024,0) allocated_mb from dba_data_files group by tablespace_name order by tablespace_name;
Beispiel
TABLESPACE_NAME ALLOCATED_MB ------------------------------ ------------ APPLTS 2170 DWHTS 210 ERPTS 418004 SYSAUX 26070 SYSTEM 700 UNDOTBS1 3170 UNICODE_REPO 900 USERS 5
In dem o.a. Beispiel belegt der Tablespace ERPTS belegt insgesamt ca. 408 GB und soll nun reorganisiert werden, um wieder mehr Freiplatz zu bekommen.
2. Self Containing Check durchführen
Der zu reorganisierende Tablespace muss in sich geschlossen sein (self-contained). Um dies sicherzustellen, sollte man folgende Prüfung mit dem Package DBMS_TTS.TRANSPORT_SET_CHECK durchführen.
Beispiel Check Tablespace ERPTS
SQL> execute dbms_tts.transport_set_check(full_check=> TRUE, ts_list => 'ERPTS', incl_constraints => true);
Abrage Ergebnis:
SQL> select * from transport_set_violations;
VIOLATIONS ---------------------------------------------------------------------------------------------------------------------------------- ORA-39907: Index ERP.PRODUCT_INDEX in tablespace ERPTS points to table ERP.CUSTOMER_LOGO in tablespace APPLTS. ORA-39908: Index ERP.PRODUCT_PRIMARY$503 in tablespace ERPTS enforces primary constraints of table ERP.CUSTOMER_LOGO in tablespace APPLTS. ORA-39907: Index ERP.PRODUCT_OBJPARENTINDEX in tablespace ERPTS points to table ERP.CUSTOMEROBJEKT in tablespace APPLTS. ORA-39908: Index ERP.PRODUCT_OBJECT_PRIMARY$220 in tablespace ERPTS enforces primary constraints of table ERP.GRAFIKOBJEKT in tablespace APPLTS. ORA-39907: Index ERP.PRODUCT_POLYPARENTINDEX in tablespace ERPTS points to table ERP.CUSTOMER in tablespace APPLTS. ORA-39908: Index ERP.PRODUCT_PLG_PRIMARY$502 in tablespace ERPTS enforces primary constraints of table ERP.CUSTOMER in tablespace APPLTS. ORA-39906: Constraint ERP_FOREIGN$3081 between table ERP.ZEITSCHEIBEN in tablespace APPLTS and table ERP.AUFTRAG in tablespace ERPTS.
In dem o.a. Beispiel zeigt die Abfrage der Violations, dass es einige Abhängigkeiten zu und von anderen Objekten in anderen Tablespaces gibt! Eine Reorganisation des Tablespace ERPTS allein würde zu Referenzierungsfehlern führen, die sich anschließend nur mit viel Aufwand beheben lassen. Dies gilt es zu vermeiden.
Wie ist hierbei nun vorzugehen? In dem Fall bleibt einem nichts anderes übrig, als den Abhängigen Tablespace (hier APPTS) ebenfalls mit in die Reorganisation einzubeziehen. Sollte es nach erneuter Prüfung weitere, kaskadierende Abhängigen zu anderen Tablespaces geben, müssten diese ebenfalls einbezogen werden.
Wiederholung des Self Containing Check inklusive dem abhängigen Tablespace APPLTS
SQL> execute dbms_tts.transport_set_check(full_check=> TRUE,ts_list => 'ERPTS,APPLTS',incl_constraints => true);
SQL> select * from transport_set_violations;
no rows selected
Das Erbebnis der Prüfung zeigt nun, dass es keine weiteren Violations gibt. Daraus folgt, dass der Tablespace ERPTS und der Tablespace APPTS zusammen reorganisiert werden müssen und es keine weiteren Objekte/Tablespaces mit Abhängigkeiten gibt
3. Anwendung und Jobs stoppen
Während der Reorganisation stehen die Anwendungsdaten in den Tablespaces nicht zur Verfügung. Um den lesenden und schreibenden Zugriff der Datenbankanwendungen zu unterbinden, sollten diese folglich gestoppt werden.
4. Archive-Logging deaktivieren
Während des geplanten Imports werden u.U. sehr viele Archivelogs erzeugt, sofern die Datenbank sich im Archivelog Modus befindet. Ein massives Schreiben von Archivelogs könnte das System über Gebühr belasten und das System zum Stillstand bringen, falls der Freiplatz der Archivelog Destination nicht ausreicht (Archiver Stuck).
Ich empfehle daher, sofern möglich, bei großen Datenimports die Datenbank in den Noarchivelog Mode zu versetzen und zur Unterbindung von Anwendungs-Sessions den Restriced Mode zu aktivieren. Der Restricted Mode verhindert, dass sich Nicht-Administationsnutzer an die Datenbank anmelden können. Des Weiteren werden Remote-Anmeldungen, die über den Listener kommen, geblockt.
sqlplus / as sysdba
SQL> shutdown immediate; SQL> startup mount restrict; SQL> alter database noarchivelog; SQL> alter database open;
Falls das Setzen der Datenbank in den Noarchivelog-Mode nicht möglich ist, weil man die Datenbank nicht herunterfahren kann oder möchte, dann kann man auch beim Import den Data Pump Parameter transform=disable_archive_logging:y setzen, welcher das Archivelogging auf ein Minimum reduziert. Leider funktioniert diese Methode in der Praxis nicht allzu gut, da der Parameter beim Import eigentlich nur auf Objektebene das Archivelogging deaktiviert und es beim Import bei anschließender Wiederaktivierung zu Fehlern kommen kann. Außerdem hat der Parameter keine Wirkung, wenn die Datenbank im „Force Logging“ Mode läuft.
impdp .... transform=disable_archive_logging:y
5. Datenbanksicherung prüfen bzw. anfertigen
Da eine Tablespace-Reorganisation auch einmal schief gehen kann, sollte man zwingend sicherstellen, dass man eine gültige physikalische Datenbanksicherung hat, um die Datenbank in den Zustand unmittelbar vor der Reorganisation wiederherstellen zu können. Falls keine Sicherung vorhanden ist, sollte man diese z.B. mit RMAN anfertigen.
6. Status vorhandener Invalid Objects feststellen
Um die Fehlerfreiheit nach dem Reorg feststellen zu können, ist es hilfreich den Zustand aller Datenbankobjekte vor und nach dem Reorg vergleichen zu können. Ich tue dies für gewöhnlich, indem ich die derzeitigen Objekte mit dem Status INVALID in eine zu erstellende Tabelle schreibe, um hinterher prüfen zu können, ob neue Objekte mit dem Status INVALID durch den Reorg hinzugekommen sind.
sqlplus / as sysdba
@?/rdbms/admin/utlrp create table system.snapshot_inv_objs as select owner, object_name, object_type, status from dba_objects where status !='VALID' order by owner; commit;
7. Tablespaces exportieren
Die beiden Talespaces können mit Data Pump z.B. wie folgt exportiert werden.
expdp USERID=system DIRECTORY=DATA_PUMP_DIR TABLESPACES=ERPTS,APPTS DUMPFILE=ts_%u.dmp LOGFILE=expdp_ts.log FLASHBACK_TIME=SYSTIMESTAMP
8. Tablespaces droppen und neu angelegen
Nach erfolgreichem Export können die Tablespace nun gelöscht werden.
SQL> drop tablespace ERPTS including contents and datafiles cascade constraints; SQL> drop tablespace APPTS including contents and datafiles cascade constraints;
Um sie dann anschließend mit geringerer Größe neu anzulegen.
Beispiel: Tablespace ERPTS mit einer initialen Größe von 50 GB und maximal automatisch erweiterbar auf 160 GB (5×32 GB)
SQL> create tablespace "ERPTS" datafile size 10G autoextend on next 10M maxsize unlimited; SQL> alter tablespace "ERPT" add datafile size 10G autoextend on next 10M maxsize unlimited; SQL> alter tablespace "ERPT" add datafile size 10G autoextend on next 10M maxsize unlimited; SQL> alter tablespace "ERPT" add datafile size 10G autoextend on next 10M maxsize unlimited; SQL> alter tablespace "ERPT" add datafile size 10G autoextend on next 10M maxsize unlimited;
Beispiel: Tablespace APPTS mit einer initialen Größe von 1 GB und maximal automatisch erweiterbar auf 32 GB (Unlimited)
SQL> create tablespace "APPTS" datafile size 10G autoextend on next 10M maxsize unlimited;
9. Tablespace-Inhalte wieder importieren
Die Inhalte der beiden Tablespaces können mit Data Pump z.B. wie folgt wieder importiert werden.
impdp USERID=system DIRECTORY=DATA_PUMP_DIR DUMPFILE=ts_%u.dmp LOGFILE=impdp_ts.log
Der Import sollte fehlerfrei sein.
10. Prüfung auf neue Invalid Objects
Sind neue Objekte mit Status „INVALID“ hinzugekommen? Um diese verneinen zu können, sollte die folgenden Abfrage keine Datenbankobjekte ausgeben.
sqlplus / as sysdba
set linesize 130 pagesize 600 col owner for a25 col object_name for a35 col object_type for a20 col status for a10 heading select owner, object_name, object_type, status from dba_objects where status !='VALID' minus select owner, object_name, object_type, status from system.snapshot_inv_objs order by owner, object_name;
no rows selected
Wir die Tabelle nicht mehr benötigt, kann sie gelöscht werden.
SQL> drop table system.snapshot_inv_objs;
11. Aktuelle Größe der Tablespace anzeigen
Nach erfolgter Reorganisation kann man sich abschließend das Ergebnis, sprich den Platzgewinn anzeigen lassen.
set linesize 130 pagesize 100 col tablespace_name for a30 select tablespace_name, round(sum(bytes)/1024/1024,0) allocated_mb from dba_data_files group by tablespace_name order by tablespace_name;
Beispiel
TABLESPACE_NAME ALLOCATED_MB ------------------------------ ------------ APPLTS 1956 DWHTS 210 ERPTS 205103 SYSAUX 26070 SYSTEM 700 UNDOTBS1 3170 USERS 5
Der Tablespace ERPTS belegt nun statt 418004 MB nur noch 205103 MB!
12. Archive-Logging wieder aktivieren
Wenn im Punkt 4 das Archivelogging der Datenbank deaktviert worden, ist dann sollte dieses wieder aktiviert werden und die Datenbank im Non-Restricted Mode gestartet werden.
sqlplus / as sysdba
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;
Um die Wiederherstellbarkeit der Datenbank zu gewährleisten. sollte nun noch direkt im Anschluß eine Online Sicherung durchgeführt werden.
13. Datenbanksicherung durchführen
Um die Wiederherstellbarkeit der Datenbank zu gewährleisten. sollte nun noch direkt im Anschluß eine Online Sicherung durchgeführt werden.
14. Objektstatistiken sammeln
Nach dem Reimport der Objekte der Tablespaces sind mutmaßlich einige Objekt-Statistiken nicht mehr zutreffend (Stale). Will man nicht warten bis diese im nächsten Maintenance-Fenster automatisch aktualisiert werden, kann man diese manuell aktualisieren.
Welche User haben Objekte in den importierten Tablespaces?
select distinct owner from dba_segments where tablespace_name in ('ERPTS','APPTS');
OWNER --------------- ERP SCOTT
Beispiel Aktualisierung der Statistiken von Tabellen und Indexe inklusive Histogramm-Statistiken der beiden Schemata ERP und SCOTT:
exec dbms_stats.gather_schema_stats('ERP', method_opt=>'for all columns size auto', estimate_percent => dbms_stats.auto_sample_size, cascade => true);
exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'for all columns size auto', estimate_percent => dbms_stats.auto_sample_size, cascade => true);
15. Anwendung und Jobs starten
Die Anwendungen und -Jobs können letztendlich nun wieder aktiviert werden.