Neben der physikalischen Sicherung einer Oracle Datenbank mit dem Recovery Manager, werden häufig auch logische Backups der gesamten Datenbank oder Teilmengen davon mit Data Pump Export (expdp) durchgeführt. Überraschenderweise protokolliert Oracle in der Datenbank leider keine Informationen über vergangene Exports, die mit Data Pump durchgeführt worden sind. In diesem Artikel möchte ich den Zweck und Vorteil einer selbsterstellten Data Pump Export Job History erläutern und an einem Beispiel aufzeigen, wie man eine solche erzeugen kann.
Das Problem
Exportiert man regelmäßig und jobgesteuert Datenbankinhalte mittels Data Pump, so möchte man sicher sein, dass die Exporte gelaufen und fehlerfrei waren. Außerdem wäre es praktisch, wenn historische Kennzahlen über vergangene Exporte zur Verfügung stehen würden. So kann es zum Beispiel von Interesse sein, wie lange ein vergangener Export gelaufen ist, wie viele Zeilen exportiert worden sind oder wie viel Platz ein Dumpfile in Anspruch genommen hat. Will man nicht das Data Pump Logfile, welches als Textdatei sich in einem Verzeichnis im Filesystem befindet, als Informationsquelle nutzen, so hat man ein Problem. Anders als beim Oracle Recovery Manager (RMAN), gibt es von Haus aus in der Datenbank keine View oder Tabelle, welche einem diese Daten liefert. Beim Oracle Recovery Manager (RMAN) lassen sich die Views V$RMAN_STATUS oder V$RMAN_BACKUP_JOB_DETAILS abfragen, um Informationen über vergangene physikalische Backups zu erhalten. Für jedes Monitoring-Tool, dass in der Lage ist eine SQL-Abfrage gegen die Datenbank laufen zu lassen und das Ergebnis anschließend auszuwerten, ist es ein daher ein Leichtes, den Status eines RMAN Backups zu ermitteln. Bei Data Pump hat man diese Möglichkeit aufgrund der fehlenden persistenten Daten innerhalb der Datenbank nicht. Die beiden Oracle Views DBA_DATAPUMP_JOBS and DBA_DATAPUMP_SESSIONS, die auf den ersten Blick als Datenquelle in Frage kommen könnten, enthalten nur Informationen während der Laufzeit eine Data Pump Jobs. Ist ein Export erfolgreich gelaufen und beendet, so sind die Daten verschwunden.
Die Lösung
Ich möchte hier einen einfachen Ansatz vorstellen, der das geschilderte Problem behebt. Bei dem Verfahren muss man zunächst dafür sorgen, dass die flüchtigen Informationen, die während eines Data Pump Exports vorhanden sind, nicht gelöscht werden. Im zweiten Schritt müssen die gewünschten Daten persistent in einer eigens dafür erstellte History Tabelle gespeichert werden. Wie dies im Detail durchgeführt werden kann, möchte ich in den folgenden Abschnitten zeigen.
Data Pump Master-Tabelle
Oracle erstellt während eines Export- oder Import-Job eine sogenannte Master-Tabelle, welche Daten zu dem gerade laufenden Data Pump Job enthält. Diese Tabelle beinhaltet nicht nur Steuerdaten, um gestoppte oder abgebrochene Jobs wieder starten zu können, sondern auch Statusinformationen, Kennzahlen und weitere Metadaten. Mit Hilfe dieser Daten generiert Oracle auch das Data Pump Logfile. Die Master-Tabelle wird immer im Schema des Data Pump Benutzers erstellt und heißt so, wie der Name des Data Pump Jobs. Gibt man beim Aufruf von Data Pump keinen Jobnamen über den Parameter JOB_NAME mit, so wird vom System ein Standardname generiert. Bei einem Full-Export ist dies SYS_EXPORT_FULL_01 und bei einem Schema-Export heißt die Master-Tabelle SYS_EXPORT_SCHEMA_01. Das Nummerpostfix am Ende des Standardnamens, wird um eine Nummer erhöht, sollte parallel ein aktiver Job, besser gesagt dieselbe Master Tabelle, bereits existieren. Aus Gründen der eindeutigen Referenzierung und Unterscheidung der Data Pump Jobs, empfehle ich in einen eigenen sprechenden Jobnamen zu wählen und nicht die Standardnamen zu nutzen.
Bei folgender Ausführung
expdp USER=BACKUPADMIN FULL=Y JOB_NAME=MYFULLEXPORT
wird z.B. eine Master-Tabelle namens BACKUPADMIN.MYFULLEXPORT generiert.
Wie bereits oben erwähnt, wird standardmäßig die Master-Tabelle mit Beendigung des Data Pump Jobs gelöscht. Dies kann man allerdings verhindern, indem man beim Aufruf den Parameter KEEP_MASTER=Y mit gibt.
expdp USER=BACKUPADMIN FULL=Y JOB_NAME=MYFULLEXPORT KEEP_MASTER=y
Wenn man sich den Inhalt und die Struktur einer Master Tabelle eines Exports einmal anschaut, wird man feststellen, dass diese auf den ersten Blick sehr unstrukturiert und unübersichtlich wirkt. Die Master Tabelle enthält in der aktuellen Version 95 Spalten und kann, Abhängig von Art und Umfang des durchgeführten Export, mehrere zehntausend Zeilen enthalten. Neben vielen Daten, die für uns nicht von Interesse sind, enthält die Master-Tabelle alle Informationen, die zum Aufbau einer Data Pump Job History Tabelle hilfreich sind. Da man durch den Parameter KEEP_MASTER dafür sorgen kann, dass die Master-Tabelle nach Beendigung eines Export Jobs nicht gelöscht wird, bietet diese sich daher sehr gut als Datenquelle an.
Erstellung einer Datapump Job History Tabelle
Zur Aufnahme der Daten aus der Master-Tabelle eines Data Pump Export Jobs, bedarf es eine zentrale Tabelle, die zunächst erstellt werden muss. In meinem Beispiel heißt die Tabelle DATAPUMP_JOB_HISTORY und liegt im Schema SYSTEM. Die Tabelle soll, unabhängig unter welchem Benutzer Data Pump Export Jobs ausgeführt werden, die jeweilige Data Pump Daten speichern. Dabei soll später pro Data Pump Export Job ein Datensatz erzeugt werden.
CREATE TABLE SYSTEM.datapump_job_history
(
timestamp DATE,
job_name VARCHAR2(30),
user_name VARCHAR2(30),
job_mode VARCHAR2(21),
operation VARCHAR2(8),
degree NUMBER,
state VARCHAR2(12),
error_count NUMBER,
dp_version VARCHAR2(60),
DATABASE VARCHAR2(4000),
INSTANCE VARCHAR2(60),
db_version VARCHAR2(60),
job_status VARCHAR2(200),
start_time DATE,
end_time DATE,
elapsed_time interval day TO second,
completed_rows NUMBER,
estimated_mb NUMBER,
completed_mb NUMBER,
dump_directory VARCHAR2(4000),
dumpfile VARCHAR2(4000),
dumpfile_count NUMBER,
file_max_size_mb NUMBER,
client_command VARCHAR2(4000),
compression VARCHAR2(4000),
compression_algorithm VARCHAR2(4000),
data_access_method VARCHAR2(4000),
logtime VARCHAR2(4000),
estimate VARCHAR2(4000),
server VARCHAR2(4000),
log_file_directory VARCHAR2(4000),
log_file_name VARCHAR2(4000),
consistent_time VARCHAR2(4000),
schema_expr VARCHAR2(4000),
status_queue VARCHAR2(30),
CONSTRAINT job_id PRIMARY KEY (status_queue)
);
Werden Data Pump Export Jobs mit einem anderen Datenbankbenutzer als SYSTEM durchgeführt, so muss der Benutzer SYSTEM noch die entsprechenden Objektberechtigungen erteilen.
In meinem Beispiel werden die Export Jobs mit dem Benutzer BACKUPADMIN ausgeführt.
GRANT INSERT, UPDATE, DELETE, SELECT ON SYSTEM.datapump_job_history TO backupadmin;
Erstellung einer Prozedur zu Speicherung der Data Pump Daten
Damit die Daten aus der Master-Tabelle in die Data Pump Job History Tabelle kopiert werden, wird eine entsprechende Prozedur benötigt. In meinem Beispiel hat diese den Namen SAVE_DATAPUMP_EXPORT_DATA und wird im Schema des Benutzers erstellt, welcher den Export Job ausführt. Die unten aufgeführte Prozedur erwartet als Argument den Namen der Master-Tabelle, welcher aus dem Data Pump Benutzer und dem Data Pump Jobnamen besteht (Beispiel: BACKUPADMIN.MYFULLEXPORT).
Ebenfalls integriert in die Prozedur, ist das anschließende Löschen der Master-Tabelle. Außerdem beinhaltet die Prozedur eine Löschroutine, um Daten älter als 180 Tage zu entfernen und somit das Wachstum der History-Tabelle zu begrenzen.
CREATE OR REPLACE PROCEDURE BACKUP.SAVE_DATAPUMP_EXPORT_DATA ( P_IN_MASTER_TABLE IN VARCHAR2 DEFAULT 'SYSTEM.DATAPUMP_EXPORT' ) AS v_master_table varchar(30) := P_IN_MASTER_TABLE; v_exdp_hist_table varchar(30) := 'SYSTEM.DATAPUMP_JOB_HISTORY'; no_master_table exception; pragma exception_init( no_master_table, -942 ); /* Kopieren der Daten von der Master Table in die History Table */ BEGIN execute immediate 'insert into '||v_exdp_hist_table||' ( timestamp,job_name,user_name,job_mode,operation,degree,state,error_count, dp_version,database,instance,db_version,job_status,start_time,status_queue ) select sysdate,name job_name,user_name,job_mode,operation,degree,state, error_count,job_version dp_version,value_t database,instance,db_version, object_type_path job_status,start_time,status_queue from '||v_master_table||' where abort_step = 0'; execute immediate 'update '||v_exdp_hist_table||' set end_time = (select max(start_time) from '||v_master_table||') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set completed_rows = (select sum(completed_rows) from '||v_master_table||') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set estimated_mb = (select round(sum(total_bytes/1024/1024),1) from '||v_master_table||') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set completed_mb = (select round(sum(completed_bytes/1024/1024),1) from '||v_master_table||') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set dump_directory = (select user_directory from '||v_master_table||' where user_directory is not null and rownum = 1) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set dumpfile = (select file_name from '||v_master_table||' where file_name is not null and rownum = 1) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set dumpfile_count = (select count(user_file_name) from '||v_master_table||' where seed is null) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set file_max_size_mb = (select file_max_size/1024/1024 from '||v_master_table||' where file_max_size is not null and rownum = 1) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set client_command = (select value_t from '||v_master_table||' where name = ''CLIENT_COMMAND'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set compression = (select value_t from '||v_master_table||' where name = ''COMPRESSION'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set compression_algorithm = (select value_t from '||v_master_table||' where name = ''COMPRESSION_ALGORITHM'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set data_access_method = (select value_t from '||v_master_table||' where name = ''DATA_ACCESS_METHOD'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set logtime = (select value_t from '||v_master_table||' where name = ''LOGTIME'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set estimate = (select value_t from '||v_master_table||' where name = ''ESTIMATE'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set server = (select value_t from '||v_master_table||' where process_name = ''DW00'' and rownum = 1;) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set log_file_name = (select value_t from '||v_master_table||' where name = ''LOG_FILE_NAME'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set log_file_directory= (select value_t from '||v_master_table||' where name = ''LOG_FILE_DIRECTORY'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set consistent_time = (select value_t from '||v_master_table||' where name = ''FLASHBACK_TIME'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set schema_expr = (select value_t from '||v_master_table||' where name = ''SCHEMA_EXPR'') where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; execute immediate 'update '||v_exdp_hist_table||' set elapsed_time = (select (to_timestamp((select to_char(max(start_time),''DD.MM.YYYY HH24:MI:SS'') from '||v_master_table||'),''dd.mm.yyyy hh24:mi:ss'') - to_timestamp((select to_char(start_time,''DD.MM.YYYY HH24:MI:SS'') from '||v_master_table||' where abort_step = 0),''dd.mm.yyyy hh24:mi:ss'')) from dual) where status_queue = (select status_queue from '||v_master_table||' where abort_step = 0)'; /* Drop Master Table */ execute immediate 'drop table '||v_master_table||''; /* Housekeeping: Loescher Jobs aelter als 180 Tage */ execute immediate 'delete from '||v_exdp_hist_table||' where timestamp <sysdate-180'; commit; /* Error handling */ EXCEPTION WHEN no_master_table THEN raise_application_error (-20001,'Data Pump Master Table '||v_master_table||' does not exist. Possible Cause: 1) Export has not been executed with KEEP_MASTER=Y 2) No export with specific job name has been executed 3) Master table has already been dropped 3) Wrong argument (USER.JOB_NAME)'); END SAVE_DATAPUMP_EXPORT_DATA; /
Ausführung der Prozedur zu Speicherung der Data Pump Daten
Die Prozedur sollte unmittelbar nach einem Data Pump Export Job ausgeführt werden, um zeitnah die Daten in die Job History Tabelle zu kopieren und die Master-Tabelle zu löschen.
Solange die Master-Tabelle vorhanden ist, können folgende gleichnamige Export Jobs nicht durchgeführt werden.
Wenn man seine regelmäßigen Export-Jobs mit Hilfe eines Scripts durchführt, so ist dieses um den Aufruf der Prozedur SAVE_DATAPUMP_EXPORT_DATA zu ergänzen.
Beispiel Linux Shell-Script:
expdp user=backupadmin/mypassword full=y job_name=myfullexport keep_master=y
echo "exec save_datapump_export_data('BACKUPADMIN.MYFULLEXPORT');" | sqlplus backupadmin/mypassword
Jeder Data Pump Export Job, dessen Informationen in der Data Pump Job History Tabelle landen sollen, muss mit dem KEEP_MASTER Parameter gestartet werden, gefolgt von einem anschließenden Aufruf der Prozedur zur Speicherung der Daten.
Statt dem expdp kann natürlich auch für den Export der Daten die in der Datenbank vorhandenen Export API DBMS_EXPORT verwendet werden.
Abfrage der Data Pump Jobs History Daten
Die mit der Prozedur gesammelten Daten, lassen sich individuelle per SQL abfragen.
Beispiel:
set linesize 130
set pagesize 300
col job_name FOR a20
col job_mode FOR a15
col state FOR a20
col error_count FOR 999999 head "ERROR|COUNT"
col start_time FOR a17
col end_time FOR a17
col elapsed_time FOR a12
col completed_rows FOR 99999999999999
SELECT job_name,
job_mode,
state,
error_count,
To_char(start_time, 'DD.MM.YY HH24:MI:SS') start_time,
To_char(end_time, 'DD.MM.YY HH24:MI:SS') end_time,
To_char(Cast(elapsed_time AS interval day(2) TO second(0))) elapsed_time,
completed_rows
FROM SYSTEM.datapump_job_history
ORDER BY start_time;
Beispiel Output:
JOB_NAME JOB_MODE STATE COUNT START_TIME END_TIME ELAPSED_TIME ROWS -------- -------- --------- ----- ----------------- ----------------- ------------ ---------- EXPFUL FULL COMPLETED 0 11.12.16 01:14:18 11.12.16 01:23:14 +00 00:08:56 581530365 EXPFUL FULL COMPLETED 0 12.12.16 01:14:03 12.12.16 01:22:56 +00 00:08:53 581572101 EXPFUL FULL COMPLETED 0 13.12.16 01:13:42 13.12.16 01:22:37 +00 00:08:55 581638893 EXPFUL FULL COMPLETED 0 14.12.16 01:15:55 14.12.16 01:24:53 +00 00:08:58 581810679 EXPFUL FULL COMPLETED 0 15.12.16 01:15:59 15.12.16 01:24:58 +00 00:08:59 581883215 EXPFUL FULL COMPLETED 0 16.12.16 01:16:02 16.12.16 01:25:01 +00 00:08:59 581998995 EXPFUL FULL COMPLETED 0 17.12.16 01:15:37 17.12.16 01:22:03 +00 00:06:26 582092937 EXPFUL FULL COMPLETED 0 18.12.16 01:15:56 18.12.16 01:24:51 +00 00:08:55 582122557 EXPFUL FULL COMPLETED 0 19.12.16 01:16:20 19.12.16 01:25:16 +00 00:08:56 582142669 EXPFUL FULL COMPLETED 0 20.12.16 01:16:30 20.12.16 01:25:31 +00 00:09:01 582219335 EXPFUL FULL COMPLETED 0 21.12.16 01:15:42 21.12.16 01:24:40 +00 00:08:58 582310817 EXPFUL FULL COMPLETED 0 22.12.16 01:16:28 22.12.16 02:07:49 +00 00:51:21 582386619