PDI ETL Metadata Injection: real example

Приведу пример реального использования Metadata Injection.
Задача:
Есть набор XML-файлов разной структуры, представляющих собой выгрузку наполнения БД из некой внешней системы. Каждый XML-файл соответствует одной сущности БД. Каждой XML соответствует своя XSD.
Необходимо загрузить эти XML в БД.

1 Шаг.
На основании XSD нужно сгенерить структуру в БД для загрузки в нее данных из XML.
С помощью простой трансформации парсим информацию из XSD:


Информация сохраняется в таблицу БД:

Затем генерится DDL для создания таблиц. Пример скриптов генерации здесь

2 Шаг.
Загрузка данных.
Ниже представлена основная джоба запуска загрузки.
Сначала, файлы разархивируются, удаляется список некорректных символов, включается логирование и т.п.. Нас интересует запуск трансформации Run inj







Эта трансформация выглядит следующим образом:

Get List of B$-tables из таблицы заполненной из XSD получаем следующую информацию:
путь к файлам, имя файлов, имя таблиц для загрузки, loop_xpath для xml Get data from XML

Get list of XML-file получает реальные путь и наименование XMLфайлов, на выходе поле filename
В трансформацию Get list of columns B$-table передаем список параметров:

Вызываемая трансформация имеет вид:

List of columns B$-table получает следующую информацию:
наименование поля из XML, соответствующие ему поля БД, тип данных, маска форматирования.

Get Variables from prev step переопределяет переменные с предыдущего шага.

Полученные значения полей и переменные передаем в шаг ETL Metadata Injection: start parse XML на вкладку Inject Metadata





Трансформация в ETL Metadata Injection: start parse XML имеет следующую структуру:

Парсится XML и информация вставляется в БД.
В этой самой финальной трансформации необходимо отметить две тонкости:
а) Metadata Injection поддерживает не все опции и значения шага Get data from XML.
Поэтому filename, loop xpath и prune path to handle large files необходимо определять через переменные с предыдущего шага.



Плюс, для файлов размером более 300 Мб обязательно нужно заполнять prune path to handle large files. Это позволит читать большие файлы в параллель. Подробности здесь.

б) В XML встречаются длинные текстовые значения, и для одного поля БД VARCHAR2(200 CHAR) текст полученный из XML превышал размерность. И обрезать это поле не получилось, ввиду того что Metadata Injection поддерживает не все виды шагов. Поэтому пришлось сменить тип поля на CLOB.



Ссылка на код здесь.

Опыты с Oracle VirtualBox: PDI, git и т.п.

Запуская виртуалку, получаем ошибку: Callee RC: REGDB_E_CLASSNOTREG” (0x80040154).
Причина в следах предыдущей установки VM.
Нужно зайти в C:\Users\yourname\.VirtualBox\ и удалить VirtualBox.xml (0 Kb). Затем переименовать VirtualBox.xml-prev (примерно 3 Kb) в VirtualBox.xml

Если была миграция на новую версию VB, то возможен вариант когда все UUID у уже используемых VM могут поплыть. Вариант, менять все ссылки на UUID.
Важно понимать, что файлы с расширением vboх, это xml и их можно редактировать любым редактором, конечно, сохранив оригинал.

$ VBoxManage.exe internalcommands sethduuid "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk1_2.vmdk"
UUID changed to: 5bf8212c-0d44-446c-9ab1-3ec50c71219e

Устанавливаем vmdk и vdi новые UUID. Пример ниже:

Затем эти UUID заменяются в разделах (пример):

<HardDisks>
        <HardDisk uuid="{5bf8212c-0d44-446c-9ab1-3ec50c71219e}" location="ODI 12c Getting Started VM-disk1_2.vmdk" format="VMDK" type="Normal">
          <HardDisk uuid="{1828a2c4-c767-465e-9b15-e5e3516d053e}" location="Snapshots/{07afd67c-70a1-4641-955b-c9a6a3b6cd20}.vmdk" format="VMDK">
          </HardDisk>
        </HardDisk>
        <HardDisk uuid="{892ad03a-2d9b-4ca6-b436-9137d43f57f7}" location="ODI 12c Getting Started VM-disk2_2.vmdk" format="VMDK" type="Normal"/>
      </HardDisks>

Главное, имя snapshot-а не поменять. И в

        <AttachedDevice type="HardDisk" hotpluggable="true" port="0" device="0">
          <Image uuid="{ebedbda3-8599-445f-aa80-37f222dda611}"/>
        </AttachedDevice>
        <AttachedDevice type="HardDisk" hotpluggable="false" port="1" device="0">
          <Image uuid="{3c6461b1-f3ff-4223-8566-4b20b92cc037}"/>
        </AttachedDevice>

Основное, VB сам подскажет, какие UUID у него не стыкуются. Читаешь и исправляешь.

Со снапшотами сложней, у них ссылка внутри файлов зашиты, поэтому для них выполняем команду установить и проверить как установился PARENTUUID

$ VBoxManage.exe internalcommands sethdparentuuid "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\Snapshots\{07afd67c-70a1-4641-955b-c9a6a3b6cd20}.vmdk" 5bf8212c-0d44-446c-9ab1-3ec50c71219e
UUID changed to: 5bf8212c-0d44-446c-9ab1-3ec50c71219e
$ VBoxManage.exe internalcommands dumphdinfo "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\Snapshots\{07afd67c-70a1-4641-955b-c9a6a3b6cd20}.vmdk"
--- Dumping VD Disk, Images=1
Dumping VD image "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\Snapshots\{07afd67c-70a1-4641-955b-c9a6a3b6cd20}.vmdk" (Backend=VMDK)
Header: Geometry PCHS=16383/16/63 LCHS=1024/255/63 cbSector=85962176
Header: uuidCreation={1828a2c4-c767-465e-9b15-e5e3516d053e}
Header: uuidModification={283b44da-c12b-4721-af9f-d849466714b0}
Header: uuidParent={5bf8212c-0d44-446c-9ab1-3ec50c71219e}
Header: uuidParentModification={873141d2-cabf-4581-88cd-9a2c34b0c18d}

Возможно виртуальная машина  не будет доступна с осносной машины по 22-му порту. Для устранения проблемы в разделе Сеть -> Адаптер 1 (NAT) -> Проброс портов завести запись с указанием 22 порта для хоста и гостя.

Как подключится к БД запущенной на VM с локальной машины.
- Заглушаем машину.
- В настройках машины выбрать Сеть-Адаптер 2 (или более)-Включить сетевой адаптер.
- Выбрать тип подключения - Сетевой мост.
- Запустить виртуальную машину.
- На виртуальной машине выполнить команду

[oracle@ODIGettingStarted ~]$ /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 01:01:11:11:1A:1A  
          inet addr:11.1.1.11  Bcast:.......................

eth1      Link encap:Ethernet  HWaddr 01:01:11:12:11:1D  
          inet addr:111.17.11.11  Bcast:....................

и полученный ip (в примере, это eth1 и 111.17.11.11) прописать на локальной машине в tnsnames.ora

Настройка git на VB. Необходимо установить git, я использовал ссылку для Oracle Linux 7 Git 2.x Installation on Linux. Что бы git запускать из любой директории, нужно выполнить следующие команды под root:

В конец файла /etc/bashrc добавить:
export MY_DIR=/opt/my_dir
export PATH=${PATH}:$MY_DIR

и перелогиниться. Для доступа в удаленный репозиторий используем уже имеющейся на основной машине ключ id_rsa.
Необходимо сгенерить ключ:

[oracle@ODIGettingStarted git]$ ssh-keygen -t rsa -b 4096 -C "frog@test.tst"
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa): 
/home/oracle/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
99:99:99:99:9c:9c:aa:ff:ff:99:99:99:99:cc:77:dd frog@test.tst
The key's randomart image is:
+--[ RSA 4096]----+
|                 |
......
+-----------------+
[oracle@ODIGettingStarted git]$ eval "$(ssh-agent -s)"
Agent pid 8397

Заменяем сгенеренные id_rsa и id_rsa.pub уже имеющимися. После этого попытка добавить ключ или скачать репозиторий заканчивается ошибками:

[oracle@ODIGettingStarted git]$ ssh-add ~/.ssh/id_rsa
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@         WARNING: UNPROTECTED PRIVATE KEY FILE!          @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Permissions 0666 for '/home/oracle/.ssh/id_rsa' are too open.
It is recommended that your private key files are NOT accessible by others.
This private key will be ignored.
[oracle@ODIGettingStarted git]$ git clone ssh://git@199.99.999.99:7999/frog/frogdb.git
Initialized empty Git repository in /home/oracle/git/frogdb/.git/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@         WARNING: UNPROTECTED PRIVATE KEY FILE!          @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Permissions 0666 for '/home/oracle/.ssh/id_rsa' are too open.
It is recommended that your private key files are NOT accessible by others.
This private key will be ignored.
bad permissions: ignore key: /home/oracle/.ssh/id_rsa
Permission denied (publickey).
fatal: The remote end hung up unexpectedly
[oracle@ODIGettingStarted git]$ chmod 400 ~/.ssh/id_rsa
[oracle@ODIGettingStarted git]$ git clone ssh://git@199.99.999.99:7999/frog/frogdb.git
Initialized empty Git repository in /home/oracle/git/frogdb/.git/
remote: Counting objects: 5218, done.

Нужно выдать права на id_rsa и проблема исчезнет.

[oracle@ODIGettingStarted git]$ chmod 400 ~/.ssh/id_rsa
[oracle@ODIGettingStarted git]$ git clone ssh://git@199.99.999.99:7999/frog/frogdb.git
Initialized empty Git repository in /home/oracle/git/frogdb/.git/
remote: Counting objects: 5218, done.
remote: Compressing objects: 100% (2744/2744), done.
remote: Total 5218 (delta 2600), reused 3198 (delta 1600)
Receiving objects: 100% (5218/5218), 59.74 MiB | 1.03 MiB/s, done.
Resolving deltas: 100% (2600/2600), done.

У готовой VM ODIGettingstarted раздел под /u01 выделен на 15G и даже в новой VM каталог $ORACLE_HOME был фактически заполнен на 100%. Поэтому возникла проблема с установкой патчей с помощью OPatch.

[root@ODIGettingStarted ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_odigettingstarted-lv_root
                       37G   11G   24G  32% /
tmpfs                 3.0G  348M  2.6G  12% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/sdb               15G   14G  168M  99% /u01

Необходимо расширить раздел /dev/sdb. В настройках VB данная VM использовала два диска:
D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk1.vmdk
D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk2.vmdk - это /dev/sdb
Формат vmdk невозможно изменить, поэтому конвертируем нужный нам диск в формат vdi и увеличиваем размер до 20G.

VBoxManage.exe clonehd "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk2.vmdk" "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk2.vdi" --format vdi
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone medium created in format 'vdi'. UUID: 899e12d9-5185-411e-bff4-a74827d037de
VBoxManage.exe modifyhd disk "D:\Oracle\VM\Run_vm\ODI 12c Getting Started VM\ODI 12c Getting Started VM-disk2.vdi" --resize 20000
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Затем в настройках VB выбираем вместо диска формата vmdk диск vdi.
Затем необходимо запустить VM и выполнить команды:

[oracle@ODIGettingStarted ~]$ su -
Password: 
[root@ODIGettingStarted ~]# resize2fs /dev/sdb
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/sdb is mounted on /u01; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 2
Performing an on-line resize of /dev/sdb to 5120000 (4k) blocks.
The filesystem on /dev/sdb is now 5120000 blocks long.
[root@ODIGettingStarted ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_odigettingstarted-lv_root
                       37G   14G   22G  39% /
tmpfs                 3.0G   92M  2.9G   4% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot
/dev/sdb               20G   14G  4.6G  75% /u01

Место добавилось.

PDI был скачан по данной ссылке: PDI Download . Достаточно разархивировать.
В каталог установки /u01/pentaho/Pentaho/design-tools/data-integration/lib/ положить ojdbc7-12.1.0.2.jar. При коннекте к pluggable DB в SID сначала поставить \ (find out more...) \

Еще возможны проблемы с конфигурацией сети eth1, которая появляется при создании соединения типа "моста" в свойствах сети виртуальной машины. Чтобы постоянно не прописывать IP, от перезагрузки до перезагрузки, можно сделать его для eth1 статичным.
Первое, что нужно сделать (согласно ссылке) отключить динамическое получение IP. Зайти в настройки  virtualbox и в свойствах сети, на вкладке "DHCP сервер" убрать галочку с надписи "Включить сервер".

Затем согласно ссылке можно настроить командами, но можно и используя графический интерфейс.
The "Network Connections" dialog is available from the menu (System > Preferences > Network Connections) at the console.


Highlighting the device and clicking the "Edit" button allows you to view or alter the configuration of a device.

Затем выполнить команду.
# service network restartТеперь eth1 все время будет статичным.

Возможны проблемы, когда при удаленном подключении к VM через putty нет возможно запустить sqlplus. Что бы все работало нужно в VM указать все переменные связанные с Oracle. В формате export бла-бла. Для этого берем их из файла .bashrc в файл .bash_profile.

Так же при использовании Oracle SQL Developer во многих версиях Linux наблюдается следующая бага. При запуске команд или блоков через F5 или F9, пропадает текстовый курсор и фактически невозможно работать. Но если уменьшить размер окна и затем например сделать опять большим, то курсор появится. Это конечно не вариант, и даже в последней на этот момент версии этот не решено. Есть решение: Tools -> Preferences -> Code Editor - > Caret behavior -> Caret Color и выставить значение (127;127;127). Курсор более тусклый, но работать можно.

GoldenGate: как создать GG VIP и GG Agent

Для отказоустойчивости работы GG, особенно в кластерной среде, рекомендуется использовать GoldenGate Agent. В данной статье я покажу, как его создать и настроить.
Описание параметров команд приведено в GoldenGate: List of parameters.
Под grid необходимо определить список сетей:

$ <GRID_HOME>/bin/crsctl stat res -p |grep -ie .network -ie subnet |grep -ie name -ie subnet
Пример вывода:
NAME=ora.net1.network
USR_ORA_SUBNET=X.X.X.0
NAME=ora.net2.network
USR_ORA_SUBNET=X.X.X.0

Т.к. может быть несколько сетей, то администратор должен выбрать правильную сеть для интерфейсов GG VIP и GG Agent. В документации сказано: There may be multiple networks defined in the cluster and it is at the discretion of the Oracle Clusterware Administrator and the Oracle GoldenGate Administrator to choose the correct network based on the required interface and subnet.
Под root создается GG VIP

|# <GRID_HOME>/bin/appvipcfg create -network=<NETWORK_NUMBER> \
 -ip=<VIP_IP> \
 -vipname=<GGATEVIP> \
 -user=oracle
<NETWORK_NUMBER> - определен на основании предыдущей команды.

Проверить, что GG VIP появился в разделе Cluster Resources

|# <GRID_HOME>/bin/crsctl status resource -t | more
Пример вывода:
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ggatevip      1        OFFLINE OFFLINE

Выдать права для запуска под oracle и настроить автозапуск

|#<GRID_HOME>/bin/crsctl setperm resource <GGATEVIP> -u user:oracle:r-x
|#<GRID_HOME>/bin/crsctl modify resource <GGATEVIP> -attr "AUTO_START=always"

Под пользователем oracle запустить GG VIP

$<GRID_HOME>/bin/crsctl start resource <GGATEVIP>

И проверить, что статус online

$<GRID_HOME>/bin/crsctl status resource <GGATEVIP>
Пример вывода:
NAME=ggatevip
TYPE=app.appvip_net1.type
TARGET=ONLINE
STATE=ONLINE on xen-devgg-src2

Создание GG Agent необходимо производить под пользователем oracle.

$<XAG_HOME>/bin/agctl add goldengate <GGATE_XAG_XO> \
--gg_home <GG_HOME> \
--instance_type source \
--nodes <NODE_LIST> \
--vip_name <GGATEVIP> \
--filesystems ora.asm \
--databases ora.<SRC_DB_UNIQUE_NAME>.db \
--oracle_home <ORACLE_HOME> \
--monitor_extracts <XO>

Более подробно описание параметров создания GG Agent приведено в разделе GoldenGate Agent AGCTL Syntax  документации http://www.oracle.com/technetwork/products/clusterware/overview/ogiba-reference-guide-v1-1844341.html .
Проверку статуса GG Agent производится командой:

$ <XAG_HOME>/bin/agctl status goldengate <GGATE_XAG_XO>

Под grid нужно настроить автозапуск GG Agent

$ <GRID_HOME>/bin/crsctl modify resource xag.<GGATE_XAG_XO>.goldengate -attr "AUTO_START=always"

Агент готов к работе.

GoldenGate: List of parameters

В данной статье, я приведу список параметров-переменных которые вам понадобятся для настройки репликации GoldenGate, между двумя БД под управлением Oracle версии 11.2. БД (SRC) источник работает в RAC-конфигурации, БД (TRG) приемник - noRAC. Переменные заключены в символы <>.
В дальнейших статьях я буду ссылаться на этот список.

Список необходимых переменных:

Name	Description	Value	Example Value	Commentary
<ASM_TRG_DIR_PATH>	Корневой каталог ASM на TRG		+DATA
<CHECKPOINT_RETENTION_TIME>	Параметр, отвечающий за хранение контрольных точек capture-процесса экстрактора <XO>	7.0		Выставляется в днях.
<GG_HOME>	Каталог установки GG на серверах SRC и TRG. На SRC представляет собой общее хранилище доступное со всех нод кластера		/u01/oragrid/acfsmounts/gg/12.1.2.1	Рекомендуется на всех нодах SRC и TRG установить GG по одинаковому пути
<GGATE_SRC>	Пользователь SRC, для поддержки репликации GG.
<GGATE_SRC_PWD>	Пароль пользователя SRC, для поддержки репликации GG
<GGATE_TRG>	Пользователь TRG, для поддержки репликации GG
<GGATE_TRG_PWD>	Пароль пользователя TRG, для поддержки репликации GG.
<GGATE_XAG_XO>	Имя GG Agent	ggate_xag_xo
<GGATEVIP>	Имя GoldenGate VIP	ggatevip		IP должен быть заранее выделен.
<GRID_HOME>	Каталог установки GRID на сервере SRC		/u01/oragrid/product/grid/11.2.0.4	Рекомендуется на всех нодах SRC установить GRID по одинаковому пути
<XEN_....> <XEN_W..> <RPT_USR_SYS> и т.п.	Cхемы сервера SRC
<MINKEEPDAYS>	Параметр GG отвечающий за срок хранения trail-файлов, после того как их обработали все подписанные репликаты	2
<NETWORK_NUMBER>	Номер сети для использования GG VIP и GG Agent		1
<NODE_LIST>	Список нод кластера через запятую		xen-devgg-SRC1,xen-devgg-SRC2
<SRC>	Алиас SRC в файле tnsnames.ora на SRC в <ORACLE_HOME>			В prm-файлах экстрактора и репликата должен быть в регистре, как и файл init<SRC>.ora
<SRC_DB_UNIQUE_NAME>	Уникальное имя SRC	SELECT t.db_unique_name FROM v$database t WHERE 0=0		Запрос выполнять на SRC
<SRC_HOST>	Scan SRC			Доменное имя SCAN-адреса SRC
<SRC_SERVICE_NAME>	service_name SRC
<ORACLE_HOME>	Каталог установки ORACLE на сервере SRC		/u01/oracle/product /db/11.2.0.4	Рекомендуется на всех нодах SRC и TRG установить ORACLE по одинаковому пути
<TRG>	Алиас TRG в файле tnsnames.ora на TRG в <ORACLE_HOME>.			В prm-файлах экстрактора и репликата должен быть в регистре, как и файл init<TRG>.ora
<TRG_DB_UNIQUE_NAME>	Уникальное имя TRG	SELECT t.db_unique_name FROM v$database t WHERE 0=0		Запрос выполнять на TRG
<TRG_HOST>	Scan TRG			Доменное имя SCAN-адреса TRG
<TRG_SERVICE_NAME>	service_name TRG
<RO>	Имя репликата	ro		В нижнем регистре
<SCN_SB>	SCN стендбая в момент стендбай READ ONLY
<STANDBY_DB_UNIQUE_NAME>	Уникальное имя стендбая SRC	SELECT t.db_unique_name FROM v$database t WHERE 0=0		Запрос выполнять на стендбай
<USR_SYS>	Системный пользователь Oracle
<VIP_IP>	IP предназначенный для GoldenGate VIP
<XAG_HOME>	Каталог установки Oracle Grid Infrastructure Bundled Agent на сервере SRC		/u01/oracle/product /xag/	Рекомендуется на всех нодах SRC установить агентов по одинаковому пути
<XO>	имя экстрактора	xo		В нижнем регистре