Ora2Pg

Allikas: Imre kasutab arvutit

Sissejuhatus

Ora2Pg http://ora2pg.darold.net/ tarkvara on programmeeritud Perl keeles. Käesolevas tekstis käsitletakse Ora2Pg tarkvara kasutamist Debian v. 8 Jessie sarnases operatsioonisüsteemi keskkonnas.

Tööpõhimõte

Ora2Pg ühendub Oracle andmebaasiga ja tekitab failisüsteemi dump faili, õieti sql skripti, mida saab kasutada PostgreSQL andmebaasi andmete sisselaadimiseks.

      Oracle                 Ora2Pg                 PostgreSQL
       ____                   ____                     ____
      |    |                 |    |                   |    |
      |    |-----------------|    |-------------------|    |
      |____|                 |____|                   |____|

kus

  • Oracle arvutis töötab Oracle andmebaas, port 1521/tcp; sellest arvutis eksporditakse andmed
  • OraPg arvutis töötab Perlis kirjutatud klientprogamm ora2pg ja pöördub andmete kopeerimiseks Oracle serveri poole kasutades nt /opt/instantclient kataloogi paigaldatud Oracle tarkvara teeke
  • PostgreSQL arvutis töötab PostgreSQL andmebaas, port 5432/tcp; sellesse arvutisse imporditakse andmed
  • Ora2Pg on võimeline nö otse saatma andmeid PostgreSQL andmebaasi, aga parema kontrolli, korratavuse huvides ja võimalike probleemidega tegelemise seisukohast on asjakohane salvestada andmed vahepeal Ora2Pg arvuti failisüsteem sql skriptide kujul

Kui andmeid on palju, siis võib olla kasu eksportida mitte kogu andmebaas (Oracle lingo järgi skeem) vaid eraldi tabelid. Või isegi teha üks suur tabel tükkideks WHERE tingimuse abil.

Keskkonna ettevalmistamine

Ora2Pg tarvkara eeldab, et süsteemis on olemas

Ora2Pg Tarkvara paigaldamine

Kopeerida tarvkara ja paigaldada nagu tavaliselt Perl teeke paigaldatakse

# perl Makefile.PL
# make
# make install

Paigaldamise tulemusena tekib failisüsteemi

  • /usr/local/bin/ora2pg - Perl programm
  • /etc/ora2pg/ora2pg.conf.dist - seadistusfaili näidis

Seadistamine

Ora2Pg tööd juhib seadistusfail /etc/ora2pg/ora2pg.conf, nt sellise sisuga

$ cat /etc/ora2pg/ora2pg.conf
..
ORACLE_HOME     /opt/instantclient
ORACLE_DSN      dbi:Oracle:andmebaasitnsnimi
ORACLE_USER     kasutajanimi
ORACLE_PWD      parool
USER_GRANTS     1
SCHEMA          skeeminimi
TYPE            INSERT
OUTPUT          /var/tmp/baasinimi-20141223-11345-output.sql
LONGREADLEN     25526886
DATA_LIMIT      10000

kus

  • ORACLE_DSN - vastav baas peab olema kirjeldatud failis /opt/instantclient/tnsnames.ora
$ cat /opt/instantclient/tnsnames.ora
andmebaasitnsnimi =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.66)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = service.nimi)
  )
)
  • TYPE - INSERT või COPY (alustuseks proovimiseks võib olla sobiv kasutada väärtust TABLE, millega kopeeritakse ainult nö skeem ja ei kopeerita andmeid)
  • OUTPUT - millisesse faili tulemuseks olev dump st sql skript moodustatakse (-o käsurea parameeter on tähtsam)
  • LONGREADLEN - peaks olema suurem kui read on suuremad
  • DATA_LIMIT - kui suurte tükkidena Oracle baasi tabelist andmeid võetakse; mida suurem väärtus, seda rohkem mälu peab olema kasutada Ora2Pg arvutis; kui tabeli read on suured, maksab see väärtus teha väiksemaks (nt 100); mida suurem on see väärtus, seda kiiremini protseduur põhimõtteliselt toimub (sarnaselt dd programmi parameetrile 'bs'); samas, kui see on liiga suur, siis hakkab arvuti väga swappima vms ja kiirus läheb uuesti alla
  • ALLOW - tabeli nimi millega tegelekse (vaikimisi tegeldakse kõigi tabelitega)
  • EXCLUDE - tabeli nimi, millega ei tegelda, kõik muud kopeeritakse
  • NLS_LANG - võib olla maksab kasutada väärtust ESTONIAN_ESTONIA.AL32UTF8 (kuigi väidetavalt uuemal ajal jätta selle tuvastamine automaatseks)
  • BINMODE -
  • DEBUG -
  • DISABLE_SEQUENCE -
  • SCHEMA - millise skeemiga töötatakse

Kasutamine

ora2pg programmi töötamise tulemusena asuvad eksporditud andmed failisüsteemi moodustatud sql skriptis.

Eksport

Loogiline oleks eksportimise ajaks Oracle andmebaasi muu kasutus st rakenduste poolt kasutamine peatada, aga iseensest ei ole see oluline. Käivitamiseks sobib öelda

$ export TNS_ADMIN=/opt/instantclient
$ ora2pg
[========================>] 13/13 tables (100.0%) end of scanning.                   
[======>                  ] 10000/39029 rows (25.6%) Table TABELINIMI (55.6 recs/sec)
...

kus

  • progressi väljendav arv-n/arv-m esimene osa suureneb DATA_LIMIT sammuga
  • tundub, et normaalne on, et protsent läheb vahel üle 100
  • väljundiks kasutatavasse faili kirjutatakse andmeid DATA_LIMIT sammude haaval

Tulemusena moodustatakse seadistusfailis näidatud dump fail. Käsureal saab seadistusfaili parameetreid üle kirjutada, nt väljundi faili

$ ora2pg -o baasinimi.tabelinimi.sql -l baasinimi.tabelinimi.sql.log 1> ora2pg.log 2>&1

Vastavalt vajadusele, aga kasu võib olla selliste seadistusparameetrite muutmisest

  • -o - millisesse faili väljund kirjutatakse
  • -l - millisesse faili logi kirjutatakse
  • 1> ora2pg.log 2>&1 - kui ora2pg väljund suunata faili, siis see sisu on üllatavalt ilus, st kuigi võiks arvata, et teksti lööb väga segi terminalile progressi kuvamise terminali kontroll sümbolite kasutamine vms

Eksport tulemusena tekib nt selline skript, begin ja end vahel, st transaktsioonina

-- Generated by Ora2Pg, the Oracle database Schema converter, version 14.0
-- Copyright 2000-2014 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:aditbaas

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


BEGIN;
INSERT INTO usertype (short_name,description,disk_quota) VALUES (E'person',E'Eraisik',52428400);
INSERT INTO usertype (short_name,description,disk_quota) VALUES (E'institution',E'Institutsioon / asutus',52428400);
INSERT INTO usertype (short_name,description,disk_quota) VALUES (E'company',E'Ettevõte',52428400);

COMMIT;

Import

Andmete importimiseks peab olema ilmselt kasutada PostgreSQL andmebaasis kasutaja ja sobiva search_path'iga

CREATE ROLE adit_admin LOGIN password 'xxx';
ALTER ROLE adit_admin SET search_path = hadit, public;

andmebaas

CREATE DATABASE hadit
WITH OWNER = hadit_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'et_EE.UTF-8'
LC_CTYPE = 'et_EE.UTF-8'
CONNECTION LIMIT = -1;

Andmete sisselaadmisel maksab kaaluda triggerite väljalülitamist

ALTER TABLE customers DISABLE TRIGGER archive_customer;

(sisselülitamiseks

ALTER TABLE customers ENABLE TRIGGER archive_customer;

)

contraintide väljalülitamist

ALTER TABLE ONLY hadit.document DROP CONSTRAINT parent_document_id;

(sisselülitamiseks

ALTER TABLE ONLY hadit.document ADD CONSTRAINT parent_document_id FOREIGN KEY (parent_id) REFERENCES document(id);

)

ja indeksite väljalülitamist

DROP ...

Andmete sisselugemiseks sobib öelda nt

$ psql -h 127.0.0.1 -U hadit_admin hadit < dump.sql 1>dump.sql.1.log 2>dump.sql.2.log

Peale andmte sisselaadimist tuleb jälgida, et loendurid jätkaksid õigeks kohast

ALTER SEQUENCE IF EXISTS user_contact_id_seq RESTART WITH 20244;
ALTER SEQUENCE IF EXISTS document_file_id_seq RESTART WITH 597920;
ALTER SEQUENCE IF EXISTS document_id_seq RESTART WITH 498650;
ALTER SEQUENCE IF EXISTS notification_id_seq RESTART WITH 845;
...

WHERE klausli kasutamine

Olgu andmebaasis tabel, mille mingi välja maht varieerub vahemikus 0 kui 200 MBait. Sel juhul võib olla otstarbekas eksportida väiksema mahuga read suurema DATA_LIMIT komplektiga (nt 10000) ja suurema mahuga read väiksema DATA_LIMIT komplektiga (nt 1).

Oracle baasist saab küsida kui palju on suuruste vahemikku jäävaid ridu nt nii

SQL> select count(id) from document_file where dbms_lob.getlength(file_data) > 100000 and dbms_lob.getlength(file_data) < 200000;

vastav seadisuse rida ora2pg.conf failis on

WHERE dbms_lob.getlength(file_data) > 100000 and dbms_lob.getlength(file_data) < 200000

Loenduri sättimine

Andmebaasi migreerimisel on oluline, et lisaks tabelite sisule jätkaksid loendurid (ingl. k. sequence) õigetelt positsioonidelt (loendurit kasutatakse mingi tabeli välja automaatseks väärtustamiseks, nt trigger funktsioonist või siis teatud andmetüüpide puhul tehakse seda automaatselt, nt 'serial').

Loenduri sättimiseks genereerib Ora2Pg sobivad RESTART laused, nt

ALTER SEQUENCE IF EXISTS document_file_id_seq RESTART WITH 597920;

Osutub, et sellised RESTART laused moodustatakse kõigi baasis kirjeldatud loendurite kohta juhul kui

  • ora2pg.conf failis ei kasutata ALLOW direktiivi
  • ora2pg.conf failis kasutatakse EXCLUDE direktiivi

Kui baasist tehakse tabeli kaupa eksporti siis ei moodustata RESTART lauseid. Selleks, et laused tekitada, tundub, et on sobiv teha üks täiendab eksport mingist ühest väikesest tabelist kasutadest kõigi muude tabelite kohta EXCLUDE direktiivi.

Funktsioonide (protseduuride) migreerimine

TODO

Jõudlus

Tundub, et jõudluse tõstmiseks sobib kasutada sama Oracle andmebaasi suhtes mitut ora2pg protsessi paralleelselt. Selleks, et nad kindlasti ühes arvutis töötamisel ei läheks omavahel konflikti saab kasutada mitut Ora2Pg arvutit. Paralleelselt saab kopeerida erinevaid tabeleid või siis sama tabelit kasutades WHERE klauslit. Paistab, et sedasi suureneb kiirus lineaarselt, st 2 arvuti kasutamisel 2x, 3 arvuti kasutamisel 3x, aga samas vaevalt 50 arvuti kaustamisel 50x.

Ressursikasutus

  • Tundub, et töö käigus avab ora2pg palju faile, kustutab neid ja samas ei sulge (iga DATA_LIMIT suuruse andmete komplekti kohta tekib üks tmp kataloogi sissekanne)
# lsof -n | grep ora2pg | grep deleted
ora2pg     7279   root    9u      REG              252,0            0     143711 /tmp/tmp_ora2pgRZoXnz (deleted)
ora2pg     7279   root   10u      REG              252,0            0     143712 /tmp/tmp_ora2pgMvLy9n (deleted)
ora2pg     7279   root   11u      REG              252,0            0     143713 /tmp/tmp_ora2pg2TRd2x (deleted)
ora2pg     7279   root   12u      REG              252,0            0     143714 /tmp/tmp_ora2pgc_qFj5 (deleted)
...

Seetõttu võib olla tarvis vaikimisi kasutada olevate avatud failide limiiti tõsta, ja samuti operatsioonisüsteemi avatud failide limiit üle vaadata

# sysctl fs.file-max
# ulimit -a
# ulimit -n 512000
  • Kui arvutile pole mälu võimalik lisada, aitab ka swap suurendamisest.

Probleemid

  • Vahel tekib selline viga
DBD::Oracle::st execute failed: ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_910045710$" too small
  (DBD ERROR: OCIStmtExecute) [for Statement "SELECT DISTINCT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, 
  ORDER_FLAG, CACHE_SIZE, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER='DVK'"] at /usr/local/share/perl/5.14.2/Ora2Pg.pm 
  line 4900. FATAL: ORA-01555: snapshot too old: rollback segment number 34 with name "_SYSSMU34_910045710$" too small (DBD ERROR:
  OCIStmtExecute)
  Aborting export...

Google andmetel on ORA-01555 tüüpiline viga kui päringud kestavad väga pikka aega ja saab otsa Oracle baasi undo tablespace vms ressurss. Teooria järgi aitab selle ressursi suurendamine + samas Oracle baasis muuude päringute mitte tegemine (st baas töötab vaid selleks, et temast teeb Ora2Pg eskporti). Katse näitab, et see täilikult ei aita, paremaid tulemusi saab tehes export tabeli kaupa.

  • vajadusel kohendada LONGREADLEN väärtust, nt kui saab teateid (mis tähendab, et vajadus oli 3646517 baidi järele, aga ressursi kitsendus oli 1048576 baiti)
DBD::Oracle::st fetchall_arrayref failed: ERROR fetching field 4 of 10. LOB value truncated from 3646517 to 1048576. \
  DBI attribute LongReadLen too small and/or LongTruncOk not set [for Statement "SELECT ...
  • Mõnel juhul jääb ora2pg protsess hanguma, aga osutub, et sellegi poolest võib olla eksport edukas, peab vaatama konkreetset dump faili; vahel piisab kirjutada ise juurde viimaseks reaks
COMMIT;
  • Vahel tekib ora2pg programmi töötamisel selline viga
WARNING: an error occurs during data export. Please check what's happen.

Tõenäoliselt maksab üle kontrollida ega pole jäänud varasemast käimisest (ja võimalik, et selle katkestamisest) /tmp/tmp_ora2pg* nimelisi ajutisi faile, need tuleb enne uut käivitamist eemaldada.

Kasulikud lisamaterjalid