Утилиты экспорта и импорта данных в базе данных Oracle - Data Pump
В состав технологии Data Pump входят утилиты: Data Pump Export (expdp) и Data Pump Import (impdp).
Data Pump Export – выгружает данные в файлы операционной системы, называемые файлами дампа (dumps files), в специальном формате, который может понимать только утилита Data Pump Import.
Получить справку по утилитам можно выполнив команды:
expdp help=y
impdp help=y
Если необходимо выполнить экспорт схемы или ее объектов, воспользуйтесь правами данной схемы. Использовать полномочия учетных записей sys и system не рекомендуется (по той причине, что для импорта могут потребоваться права sys и system соответственно).
Файл параметров экспорта схемы.
$ vi exoprt_schema_name.config
JOB_NAME=impdp_schema_name
DUMPFILE=dpdumps:schema_name.dmp
LOGFILE=dplogs:expdp_schema_name_YYYYMMDD.log
JOB_NAME - имя задания, чтобы при необходимости задание можно было бы идентифицировать по имени.
DUMPFILE - каталог для дампа LOGFILE - каталог для логов
dplogs - ссылка в базе данных на каталог в котором должны будут сохраниться логи результата выполнения экспорта схемы базы данных.
dpdumps - ссылка в базе данных на каталог в котором должны будут сохраниться файл дампа базы данных.
dplogs и dpdumps должны ссылаться на реальные каталоги операционной системы с достаточным набором прав на запись.
# mkdir -p /u03/oradata/datapump/dumps
# mkdir -p /u03/oradata/datapump/logs
# chown -R oracle11:dba /u03/oradata/datapump/dumps
# chown -R oracle11:dba /u03/oradata/datapump/logs
Создание ссылки в базе данных на каталоги операционной системы
$ sqlplus / as sysdba
Посмотреть уже имеющиеся каталоги для datapump:
SQL> set linesize 200;
SQL> set pagesize 0;
SQL> col directory_name format a30;
SQL> col directory_path format a60;
SQL> select directory_name, directory_path from dba_directories;
Мне не нравится каталог по умолчанию. Предпочитаю его удалить
DROP DIRECTORY DATA_PUMP_DIR;
Создаю директории
CREATE DIRECTORY dpdumps as '/u03/oradata/datapump/dumps';
CREATE DIRECTORY dplogs as '/u03/oradata/datapump/logs';
Делегирую права на запись в данную директорию пользователю scott
GRANT READ, WRITE ON DIRECTORY dpdumps TO scott;
GRANT READ, WRITE ON DIRECTORY dplogs TO scott;
Если необходимо предоставить возможность экспорта данных в указанные каталоги для любых схем:
GRANT READ, WRITE ON DIRECTORY dpdumps TO PUBLIC;
GRANT READ, WRITE ON DIRECTORY dplogs TO PUBLIC;
Экспорт схемы с использованием файла параметров:
$ nohup expdp scott/tiger parfile=exoprt_schema_name.config &
В некоторых случаях необходимо явно указать SID базы данных.
$ nohup expdp scott/tiger@SID parfile=exoprt_schema_name.config &
Экспорт можно выполнить одной командой без использования файла параметров:
$ nohup expdp scott/tiger job_name=scott_export_job_01 dumpfile=dpdumps:scott_YYYYMMDD.dmp logfile=dplogs:scott_YYYYMMDD &
Технология Data Pump состоит из трех главных компонентов:
- Пакет DBMS_DATAPUMP – это главный механизм для осуществления загрузки и выгрузки метаданных словаря данных. В пакете DBMS_DATAPUMP содержится основополагающие элементы технологии Data Pump в виде процедур, которые в действительности приводят в действие задания по загрузке и выгрузке данных. Содержимое этого пакета отвечает за работу как утилиты Data Pump export, так и утилиты Data Pump Import.
- Пакет DBMS_METADATA – для извлечения и изменения метаданных Oracle.
- Клиенты с интерфейсом командной строки – impdbp и expdp
Режимы утилиты Data Pump Export
Data Pump Export поддерживает несколько режимов для выполнения заданий.
- Режим экспорта всей базы данных. Позволяет выполнять экспорт всей базы данных за один сеанс экспорта с помощью параметра FULL. Для использования этого режима, необходимы привилегии EXPORT_FULL_DATABASE.
- Режим схем. Позволяет выполнять экспорт данных и/или объектов только конкретного пользователя с помощью параметра SCHEMAS.
- Режим табличных пространств. Позволяет выполнять экспорт всех таблиц, которые содержатся в одном или нескольких табличных пространствах, с помощью параметра TABLESPACES или только метаданных тех объектов, которые содержатся в одном или нескольких табличных пространствах, с помощью параметра TRANSPORT_TABLESPACES. Выполнять экспорт табличных пространств между базами данных можно, сначала выполнив экспорт метаданных, затем скопировав файлы табличного пространства на целевой сервер, а потом импортировав метаданные в целевую базу данных.
- Режим таблиц. Позволяет выполнять экспорт только одной или нескольких конкретных таблиц с помощью параметра TABLES.
По умолчанию для выполнения заданий Data Pump Export и Data Pump Import используется режим схем.
Параметры фильтрации экспортируемых данных.
Параметр CONTENT - позволяет выполнять фильтрацию тех данных, которые должны помещаться в файл дампа при экспорте. Он может принимать следующие значения:
- ALL – указывает, что требуется экспортировать как данные таблиц, так и определения этих таблиц и других объектов (метаданных);
- DATA_ONLY – указывает, что требуется экспортировать только строки таблиц.
- METADATA_ONLY – указывает, что требуется экспортировать только метаданные.
Пример:
$ nohup expdp scott/tiger dumpfile=dpdumps:mydump01.dmp logfile=dplogs:mydump01.log CONTENT=DATA_ONLY &
Параметры EXCLUDE и INCLUDE
Параметры EXCLUDE и INCLUDE – это два взаимоисключающих параметра, которые можно применять для выполнения так называемой фильтрации метаданных (metadata filtering). Фильтрация метаданных позволяет выборочно исключать или наоборот включать определенные типы объектов во время выполнения задания Data Pump Export или Data Pump Import. В прежней утилите экспорта для указания того, требуется ли экспортировать такие объекты, применялись параметры CONSTRAINTS, GRANTS и INDEXES. За счет использования параметров EXCLUDE и INCLUDE теперь стало можно включать и исключать объекты и многих других видов помимо тех четырех, фильтрацию которых можно было осуществлять ранее. Например, если необходимо сделать так, чтобы во время экспорта не экспортировались никакие пакеты, такое поведение задается с помощью параметра EXCLUDE.
Проще говоря, параметр EXCLUDE помогает пропускать определенные типы объектов базы данных во время операции экспорта или импорта, а параметр INCLUDE наоборот – включать в эти операции только определенный набор объектов. Ниже показано, как в общем случае выглядит синтаксис этих параметров:
EXCLUDE=тип_объекта[:конструкция_имени]
INCLUDE=тип_объекта[:конструкция_имени]
Параметры EXCLUDE и INCLUDE являются взаимоисключающими. Поэтому во время выполнения одного и того же задания применять можно только какой-то один из них; использовать тот и другой одновременно нельзя.
Как для параметра EXCLUDE, так и для параметра INCLUDE, элемент конструкцияимени является необязательным. Как известно, некоторые объекты в базе данных, например, таблицы, индексы, пакеты и процедуры, обладают именами, а некоторые, например, объекты GRANTS – нет. Элемент конструкцияимени в параметре EXCLUDE или INCLUDE позволяет применять SQL-функцию для фильтрации именованных объектов.
Ниже приведен простой пример исключения всех таблиц, имя которые начинается с ECMP.
EXCLUDE=TABLE:”LIKE ‘EMP%’”
В этом примере ”LIKE ‘EMP%’” представляет конструкцию имени.
Элемент конструкция_имени является необязательным в параметрах EXCLUDE и INCLUDE. Он представляет собой просто средство фильтрации, позволяющее более точно определять тип подлежащих исключению или включению объектов (индексов, таблиц и т.д.). В случае его пропуска включаться или исключаться будут все объекты указанного типа.
В следующем примере Oracle исключит из операции экспорта все индексы, потому в элементе конструкция_имени не было указано никакого значения, требующего, чтобы исключались только определенные индексы:
EXCLUDE=INDEX
Вдобавок параметр EXCLUDE может применяться для исключения целой схемы, как показано в следующем примере:
EXCLUDE=SCHEMA:”=’HR’”
Параметр INCLUDE является противоположностью параметру EXLCUDE и позволяет принудительно включать в операцию экспорта только определенный набор объектов. Как и в случае параметра EXLCUDE, для указания того, какие точно объекты требуется экспортировать, вместе с INCLUDE тоже можно использовать элемент конструкция_имени.
Ниже приведены три примера, демонстрирующие применение элемента конструкция_имени для ограничения выбираемых объектов:
INCLUDE=TABLE:”IN (‘EMPLOYEES’,’DEPARTMENTS’)”;
INCLUDE=PROCEDURE
INCLUDE=INDEX:”LIKE ‘EMP%’”
В первом примере параметр INCLUDE указывает, что в процессе экспорта должны принять участие только две таблицы: ECMPLOYEES и DEPARTMENTS, во втором – только процедуры, а в третьем – только индексы, причем лишь те, имя у которых начинается с EMP.
В следующем примере показано, как использовать символ косой черты для отмены двойных кавычек:
$ expdp scott/tiger DUMPFIEL=dum.file%U.dmp
schemas=SCOT EXCLUDE=TABLE:\”=’EMP’\”, EXLUDE=FUNCTION:\”=’MY_FUNCTION’\”
При выполнении фильтрации метаданных за счет применения параметра EXCLUDE и INCLUDE нужно помнить о том, что все объекты, которые зависят от какого-то из фильтруемых объектов, будут обрабатываться тем же образом, что и сам этот фильтруемый объект. Например, в случае использования параметра EXCLUDE для исключения некоторой таблицы также автоматически будут исключаться индексы, ограничения, триггеры и прочие зависящие от этой таблицы объекты.
Существует еще множество всевозможных параметров в т.ч. и шифрование, компрессия и д.р.
Data Pump Import
$ nohup impdp scott/tiger dumpfile=datapumps:mydump01.dmp logfile=datapumps:mydump01.log &
Иногда, (в моем случае при неудачном импорте) можно вытащить из файла дампа весь код DDL.
Для этого можно воспользоваться параметром SQLFILE.
$ nohup impdp scott/tiger dumpfile=datapumps:mydump01.dmp logfile=datapumps:mydump01.log sqlfile=datapumps:scott.sql job_name=scott_import_job_01 &
Создается файл scott.sql с DDL.
Параметры фильтрации
Параметр CONTENT применяться в Data Pump Import, как и в Data Pump Export, для указания того, должны ли загружаться только строки (CONTENT=DATA_ONLY), строки и метаданные (CONTENT=ALL), либо только метаданные (CONTENT=METADATA_ONLY). Параметры EXLCUDE и INCLUDE имеют в Data Pump Import точно такое же предназначение, как и в Data Pump Export, и являются взаимоисключающими, а в частности:
- Параметр INCLUDE используется для перечисления объектов, которые необходимо импортировать;
- Параметр EXCLUDE применяться для перечисления объектов, которые импортировать не требуется.
Ниже приведет простой пример использования параметра INCLUDE. В этом примере импорт ограничивается только объектами таблиц. В результате импортирована будет только таблица PERSONS.
INCLUDE=TABLE:”= ‘persons’ “
Для импорта только тех таблиц, имя у которых начинается с букв PER, можно использовать конструкцию INCLUDE=TABLE:”LIKE ‘PER%’”. Вдобавок параметр INCLUDE можно применять и отрицательным образом, указывая то, что все объекты с определенным синтаксисом должны игнорироваться: INCLUDE=TABLE:”NOT LIKE ‘PER%’”
Обратите внимание на то, что в случае установки для параметра CONTENT значения DATA_ONLY, использовать во время импорта ни параметр EXCLUDE ни параметр INCLUDE нельзя.
Параметр TABLE_EXISTS_ACTION позволяет указывать Data Pump Import, что следует делать в случае, если таблица уже существует. Для этого параметра можно устанавливать четыре разных значения:
- SKIP – (значение по умолчанию) – пропустить таблицу, если таковая уже существует;
- APPEND – присоединять строки к таблице;
- TRUNCATE – усекать таблицу и загружать данные из экспортного файла дампа.
- REPLACE – удалять таблицу, если таковая существует, создавать ее заново и снова загружать в нее данные.
Параметры переопределения
Параметр REMAP_TABLE
Параметр REMAP_TABLE позволяет переименовывать таблицу при выполнении операции импорта с с использованием метода переноса табличных пространств.
TABLES=hr. employees REMAP_TABLE=hr. employees:emp
В этом примере параметр REMAP_TABLE указывает, что при выполнении операции импорта имя таблицы hr.employees должно быть изменено на hr.emp
Параметр REMAP_SCHEMA
Параметр REMAP_SCHEMA позволяет перемещать объекты из одной схемы в другую. Задается этот параметр примерно так:
REMAP_SCHEMA=hr:oe
В этом примере параметр REMAP_SCHEMA указывает, что при выполнении операции импорта требуется переместить все объекты из исходной схемы HR в целевую схему OE. Утилита Data Pump Import может даже создать схему OE, если таковой в целевой базе данных не существует.
Параметр REMAP_TABLESPACE
Иногда бывает нужно, чтобы табличное пространство, в которое выполняется импорт данных, отличалось от используемого в исходной базе данных. Параметр REMAP_TABLESPACE позволяет осуществлять во время импорта перемещение объектов из одного табличного пространства в другое.
REMAP_TABLESPACE=’example_tbs’: ‘new_tbs’
Параметр REMAP_DATAFILE
При перемещении баз данных между двумя различными платформами, на каждой из которых используется свое согласие по именованию фалов, параметр REMAP_DATAFIE приходится очень кстати, поскольку позволяет изменять формат именования файлов. Ниже приведен пример, показывающий, как с помощью этого параметра указать утилите Data Pump Import, что вместо формата файловой системы Windows, требуется использовать формат файловой системы UNIX. После этого при обнаружении в экспортном файле дампа любой ссылки на файл с именем в формате файловой системы Windows, утилита Data Pump Import будет автоматически изменять имя файла в соответствии с форматом файловой системы UNIX.
REMAP_DATAFIELE=’DB1$:[HRDATA.PAYROLL]tbs6.f’:’/db1/drdata/payroll/tbs6.f’
Параметры TRANSFORM
Предположим, что требуется импортировать таблицу из другой схемы или даже другой азы данных и не импортировать при этом другие атрибуты хранения объектов, т.е. необходимо просто перенести содержащиеся в таблице данные. Параметр TRASNSFORM позволяет указать утилите Data Pump Import не импортировать определенные атрибуты хранения и атрибуты других видов. За счет применения параметра TRANSFORM можно исключать из таблицы или индекса конструкции STORAGE и TABLESPACE или только конструкции STORAGE. При выполнении импорта с помощью Data Pump Oracle создает объекты с использованием DDL-операторов, которые находит в экспортных файлах дампа. Параметр TRANSFORM, по сути, указывает утилите Data Pump Import изменять приводящие к созданию объектов операторы DDL определенным образом.
В целом синтаксис параметра TRANSFORM выглядит так:
Ниже приведено краткое описание того, что собой представляет каждый элемент.
1) Название_трансовармации. Существуют всего четыре опции, которые могут указываться на месте этого элемента. Эти опции позволяют, соответственно, изменять четыре основных вида характеристик объекта.
- SEGMENT ATTRIBUTES. Эта опция позволяет влиять на атрибуты сегмента, в число которых входят физические атрибуты, атрибуты хранения, табличные пространства и журналы. Принуждать Data Pump Import включать все эти атрибуты можно, указав на месте название_трансформации этой опции со значением Y (SEGMENT_ATTRIBUTES=Y), которое является для этого параметра значением по умолчанию. В таком случае Data Pump Import будет включать все четыре атрибута сегмента вместе с их операторами DDL.
- STORAGE. За счет указания на месте название_трансформации опции STORAGE со значением Y (STORAGE=Y), представляющее собой значение по умолчанию, можно получать лишь атрибуты хранения тех объектов, которые являются частью задания Data Pump Import.
- OID. В случае указания на месте название_трансформации опции OID со значением Y (OID=Y), которое является для нее значением по умолчанию, объектам таблицам во время импорта будет присваиваться новый OID.
- PCTSPACE. За счет указания на месте название_трансформации опции PCTSPACE с положительным числом в качестве значения можно увеличивать выделяемый под объекты и файлы данных объем пространства на соответствующее количество процентов.
2) Значение. На месте элемента значение в параметре TRANSFORM может указываться либо значение Y (да), либо значение N (нет). Как упоминалось выше, для первых трех опций, которые могут указываться на месте название_трансформации, по умолчанию устанавливается значение Y. Это означает, что по умолчанию Data Pump предусматривает выполнение импорта как атрибутов сегмента, так и атрибутов хранения объекта. В качестве альтернативного варианта, для этих опций можно устанавливать значение N и тем самым указывать Data Pump не импортировать исходные атрибуты сегмента и/или хранения. Что касается опции PCTSPACE, то для нее на месте элемента значение может задаваться только какое-то число.
3) Типобъекта.</strong> На месте элемента типобъекта можно указывать утилите Data Pump Import, объекты какого типа необходимо трансформировать. Это могут быть таблицы, индексы, табличные пространства, типы, кластеры, ограничения и прочие объекты, в зависимости от опций, указываемых на месте название_трансформации. В случае не указания типа подлежащих трансформации объектов при использовании опции SEGMENT_ATTRIBUTES и STORAGE, эти опции будут применяться ко всем таблицам и индексам, которые являются частью операции импорта.
Ниже приведен пример применения параметра TRANSFORM:
TRANSFORM=SEGMENT_ATTIBUTES:N:table
В этом примере для SEGMENT_ATTRIBUTES установлено значение N, а в качестве типа объекта указана таблица. В такой спецификации параметр TRANSFROM указывает утилите Data Pump Import не импортировать существующие атрибуты хранения ни для каких таблиц.
Мониторинг выполнения заданий Data Pump
Наиболее важными для мониторинга за выполнением заданий Data Pump являются представления DBA_DATAPUMP_JOBS и DBA_DATAPUMP_SISSIONS.
Представление DBA_DATAPUMP_JOBS позволяет получать сводную информацию обо всех выполняющихся в текущий момент заданиях Data Pump.
SQL> set pagesize 0;
SQL> set linesize 220;
SQL> col owner_name format a20;
SQL> col job_name format a20;
SQL> col operation format a20;
SQL> col state format a20;
SQL> SELECT owner_name, job_name, operation, state
FROM dba_datapump_jobs
WHERE state = 'EXECUTING';
SYS IMP_M2M_SVT IMPORT EXECUTING
Представление DBA_DATAPUMP_SESSIONS позволяет выяснять, какие пользовательские сеансы в текущий момент подключены к заданию Data Pump Export или Data Pump Import
SQL> SELECT sid, serial#
FROM v$session s, dba_datapump_sessions d
WHERE s.saddr = d.saddr;
Просмотр информации о ходе выполнения заданий Data Pump
Ниже приведен типичный сценарий, который можно использовать для получения информации о том, сколько времени осталось до завершения выполнения задания Data Pump:
SQL> SELECT opname, target_desc, sofar, totalwork, start_time, time_remaining
FROM v$session_longops;
- OPNAME - имя задания Data Pump
- TOTALWORK - показывает, сколько всего мегабайт было насчитано для выполнения данного задания;
- SOFA - показывает, сколько пока было передано мегабайт во время выполнения данного задания;
Дополнительно
Oracle Data Pump 21c and Cloud Object Stores
https://www.youtube.com/watch?v=6S2N1-V5qc8