🍪 🎤 🚼 Migrating from Oracle to Postgres Ⓜ️ 👳🏿 👲

Why transfer data

() Oracle Postgres . Postgres , , . Oracle — , . . . , () , . , — .

, , , , 15 Oracle 11.2.0.4 Linux Redhat 6.8 Postgres 9.4 Windows. , . DBA /CUT.

, . , , ORA2PG . Ora2PG Oracle Perl- , , SQL-, . DDL-. : INSERT . , .. Postgres. PERL:PG.

, c Oracle Postgres . , , .

CentOS 7 development tools . Postgres 10.70.85.11, Oracle — 10.70.85.14, — 10.70.85.15.
ORA2PG .

, :

— -: CNTLM. ( ‘postgres’ ).
Postgres Postgres 9.4.
Postgres 9.4 , Perl-
instant- Oracle. , , .
.
.
Perl.
ORA2 PG.

Oracle

, :

useradd oracle
grpoupadd oinstall
/usr/sbin/usermod –g oinstall –G oracle

Oracle ( ):

oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm
oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm
oracle-instantclient18.3-jdbc-18.3.0.0.0-1.x86_64.rpm
oracle-instantclient18.3-devel-18.3.0.0.0-1.x86_64.rpm
rpm –Uvh oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm

zip-, /u01/app/oracle/instant_client_18_3/. :

hown –R oracle:oinstall /u01/app/oracle/instant_client18_3/

./bash_profile Oracle. LD_LIBRARY_PATH:

export SQLPATH=/u01/app/oracle/instantclient_18_3
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=${SQLPATH}
export LD_LIBRARY_PATH=${SQLPATH}
export PATH=${SQLPATH}:${PATH}

: “./sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory”, libaio1.

Yum install libaio1 libaio-devel

readline:

yum install readline-devel.x86_64

Oracle : TNSNAMES.ORA /network/admin .

, Listener Oracle .

, tnsnames.ora, :

iwtm =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = 10.70.85.14)(Port = 1521 ))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = iwtm )
 )
)

tnsping:

tnsping iwtm
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.70.8
.14)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = iwtm)))
OK (40 msec)

, sqlplus , :

Error 46 initializing SQL*Plus
HTTP proxy setting has incorrect value
SP2-1502: The HTTP proxy server specified by http_proxy is not accessible

, ./bash_profile Oracle:

unset https_proxy
unset http_proxy

PostgreSQL

, :

yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm

yum install postgresql94

yum install postgresql94-server

postgres

Postgres pg_hba.conf, :

host all all 10.70.85.15./32 md5

Perl

Perl 5.10. CPAN:

yum install gcc cpan

CPAN . .

 http://mirror.truenetwork.ru/CPAN/
http://mirror.ps.kz/CPAN/

Perl

DBD::Oracle Oracle. - Oracle- (, ora-instant-client, SDK, SQL plus libaio1), . :

source  ~/.bash_profile

        perl -MCPAN -e 'install DBD::Oracle'

, Ora2PG.

perl -MCPAN -e shell
        cpan> get DBD::Oracle
        cpan> quit
        cd ~/.cpan/build/DBD-Oracle*
        export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
        export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
        perl Makefile.PL
        make
        make install

DBI::PG postgres. 1.614, :

Can't set DBI::db=HASH(0x9036450)->{AutoInactiveDestroy}: 
    unrecognised attribute name or invalid value 
    at /rad/perl/lib/cpan/DBI.pm line 708.
Can't get DBI::db=HASH(0x9036450)->{AutoInactiveDestroy}: 
    unrecognised attribute name

CPAN :

upgrade /(.*)/

yum install libdbd-pg-perl postgresql-plperl
yum install postgresql94-devel

yum install perl-DBD-Pg

        perl -MCPAN -e 'install DBD::PG'

, , postgres :

cd ~/.cpan/build/DBD-PG*
        perl Makefile.PL
        make && make install

Postgres pgadmin, , — postgres.

, .

tar -xf ora2pg-x.x.tar.bz2
        cd ora2pg-x.x/
        perl Makefile.PL
        make && make install

oracle. :

ora2pg --project_base /home/oracle/export/ --init_project test_project

. . . DBI::oracle — /:

# Set Oracle database connection (datasource, user, password)
ORACLE_DSN  dbi:Oracle:host=10.70.85.14;sid=iwtm;port=1521
ORACLE_USER system
ORACLE_PWD  password

DBI::PG — /:

# Define the following directive to send export directly to a PostgreSQL
# database. This will disable file output.
PG_DSN      dbi:Pg:dbname=IWDM;host=10.70.85.11;port=5432
PG_USER postgres
PG_PWD      password

Oracle :

# Export Oracle schema to PostgreSQL schema
EXPORT_SCHEMA   1
# Oracle schema/owner to use
SCHEMA  IWDM

, ( ):

# Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
# It is enable by default and concern on TABLE export type.
CREATE_SCHEMA   1

Postrges, :

PG_SCHEMA   IWDM

output- :

OUTPUT      output.sql

# EXPORT SECTION (Export type and filters)
# Type of export. Values can be the following keyword:
TYPE        TABLE

, , , ( constraints):

# Trace all to stderr
DEBUG       1

Oracle. , :

cd /home/oracle/export/test_project/
nohup  ./export_schema.sh -d  >/dev/null 2>&1

ora2pg -t COPY -o output.sql -b ./data -c ./config/ora2pg.conf

./import_all.sh -h 10.70.85.11 -d IWDM -o IWDM -U postgres./import_all.sh -h 10.70.85.11 -d IWDM -o postgres -U postgres –y -I

–y — , , .. .

-y , , .

, , - . , .

-I — , , . .

, . . User , , ‘user’. STOP_ON_ERROR.

– user, offset, from, to. , «».

https://postgrespro.ru/docs/postgresql/9.4/sql-keywords-appendix

! sed:

sed -i 's/user/”user”/' /schema/tables/g’ data/tables.sql

, - .

, /schema/output.sql .

c :

psql -h 10.70.85.11 -U postgres -p 5432 –d IWDM < data/output.sql

psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ INDEXES_table.sql

psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ CONSTRAINTS_table.sql
psql -h 10.70.85.11 -U postgres -d IWDM < schema/tables/ FKEYS_table.sql

psql -h 10.70.85.11 -U postgres -d IWDM < schema/triggers/trigger.sql

,

: Oracle SQL Developer Oracle PGadmin postgres:

130 , .

Oracle-Postgres, Ora2Pg, , Oracle: varchar2 varchar, Number Numeric .. , . , , , , .

Oracle:

CREATE TABLE "IWDM"."Statistics1" 
   (    "Id" NUMBER(20,0) NOT NULL ENABLE, 
    "CreateDate" DATE NOT NULL ENABLE, 
    "User" NVARCHAR2(128), 
    "Workstation" NVARCHAR2(255), 
    "Operation" NUMBER(*,0) NOT NULL ENABLE, 
    "UnicParam" NUMBER(20,0) NOT NULL ENABLE, 
    "UnicString" NVARCHAR2(255) NOT NULL ENABLE, 
    "Description" NVARCHAR2(2000), 
          ) 
  TABLESPACE "IWDM" ;

Postgres:

CREATE TABLE statistics1 (
    id numeric(20) NOT NULL,
    createdate timestamp NOT NULL,
    "user" varchar(128),
    workstation varchar(255),
    operation numeric(38) NOT NULL,
    unicparam numeric(20) NOT NULL,
    unicstring varchar(255) NOT NULL,
    description varchar(2000)
) ;

Oracle:

CREATE SEQUENCE  "IWDM"."SQ_Statistics1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE ;

Postgres:

REATE SEQUENCE iwdm.sq_statistics1
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE iwdm.sq_statistics1
  OWNER TO postgres;

, .

ORA2PG PL/SQL PL/PGSQL, Oracle . :

CREATE INDEX idx_userage ON user ( to_number(to_char('YYYY', user_age)) ); 
CREATE INDEX idx_userage ON «user» ( date_part('year', user_age) );

Postgres. . , BLOB . BLOB bytea . EXCLUDE , THREAD_COUNT.

, . . postgres . , , ( ).

: GTRch

Migrating from Oracle to Postgres

Why transfer data

Oracle

PostgreSQL

postgres

Perl

Perl

,

More articles: