Migration Problem

[keakarsu]

Hi There,

I want to copy my data from oracle to postgres. When I start migration I got some errors. I want to write what I do from starting to getting error

1- Install ora2pg 2- Install DBD:Oracle and DBD:Pg 3- Check connection for Oracle and Postgres 4- Edit config file 5- Create project ora2pg --init_project MyProject 6- Run export_schema.sh 7- Run ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf -J 4 -j 3 Than Get This Error

[========================>] 1995/1995 tables (100.0%) end of scanning. DBD::Pg::db do failed: ERROR: relation "act_evt_log" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 12746. FATAL: ERROR: relation "act_evt_log" does not exist DBD::Pg::db do failed: ERROR: relation "act_evt_log" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 12746. FATAL: ERROR: relation "act_evt_log" does not exist Aborting export... DBD::Oracle::db disconnect failed: ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 2941 Serial number: 37035 (DBD ERROR: OCISessionEnd) at /usr/local/share/perl5/Ora2Pg.pm line 12001. Aborting export... DBD::Pg::db do failed: ERROR: relation "act_evt_log" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 12746. FATAL: ERROR: relation "act_evt_log" does not exist DBD::Pg::db do failed: ERROR: relation "act_evt_log" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 12746. FATAL: ERROR: relation "act_evt_log" does not exist DBD::Oracle::db disconnect failed: ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 2941 Serial number: 37035 (DBD ERROR: OCISessionEnd) at /usr/local/share/perl5/Ora2Pg.pm line 12001. Aborting export... DBD::Oracle::db disconnect failed: ORA-03135: connection lost contact Process ID: 0 Session ID: 2941 Serial number: 37035 (DBD ERROR: OCISessionEnd) at /usr/local/share/perl5/Ora2Pg.pm line 12001. Aborting export... [========================>] 0/0 rows (100.0%) Table ACT_EVT_LOG-part-0 (1 sec., 0 recs/sec) [========================>] 0/0 rows (100.0%) Table ACT_EVT_LOG-part-2 (1 sec., 0 recs/sec) [========================>] 0/0 rows (100.0%) Table ACT_EVT_LOG-part-1 (2 sec., 0 recs/sec) [========================>] 0/0 rows (100.0%) Table ACT_EVT_LOG-part-3 (2 sec., 0 recs/sec) [========================>] 0/0 rows (100.0%) Table ACT_EVT_LOG (4 sec., 0 recs/sec) DBD::Oracle::db prepare failed: ORA-03135: connection lost contact Process ID: 32033 Session ID: 2941 Serial number: 37035 (DBD ERROR: OCIStmtExecute/Describe) [for Statement "SELECT A.COLUMN_NAME, A.NULLABLE, A.DATA_DEFAULT, A.TABLE_NAME, A.OWNER, A.COLUMN_ID FROM DBA_TAB_COLUMNS A, ALL_OBJECTS O WHERE A.OWNER=O.OWNER and A.TABLE_NAME=O.OBJECT_NAME and O.OBJECT_TYPE='TABLE' AND A.TABLE_NAME='ACT_GE_BYTEARRAY' AND A.OWNER='LOGO' ORDER BY A.COLUMN_ID "] at /usr/local/share/perl5/Ora2Pg.pm line 8185. FATAL: _column_attributes() ORA-03135: connection lost contact Process ID: 32033 Session ID: 2941 Serial number: 37035 (DBD ERROR: OCIStmtExecute/Describe) Aborting export...

I try to find out something but unfortunately I'm not able to find any solution.

[SebAlbert]

It looks like you have configured ora2pg for a direct connection to Postgres (configuration parameter PG_DSN), which is used for the -t COPY instruction. As this is done before you have imported the schema (e.g. by using import_all.sh), this fails for the schema has not been created in Postgres yet. I suggest that you either disable the direct connection (PG_DSN) in your configuration, so data would be exported to data.sql and then imported by import_all.sh, or you runimport_all.sh before the ora2pg -t COPY.

[keakarsu]

Yes you are right I have configured ora2pg for a direct connection. I read some posts about direct connection faster than generating output file. Is that true , If I create schema before run COPY I will not get this error?

[SebAlbert]

Yes, try running import_all.sh right before ora2pg -t COPY.

[keakarsu]

One more question if i try to run import_all.sh I got bad interpreter error. Which parameters should I call with script for only create schema?

[darold]

You can import the schema using:

psql -1 mydb -f schema/tables/table.sql

Import your data and load other objects in schema/ subdirectory using psql. Especially the constraints and indexes.

[keakarsu]

I have another question, I put my cost report and configuration file

Desktop.zip

to attachment. I have a table which has 60 gb data with large objects. While I try to export data it takes long time. Is it possible to improve performans.

In other topic While importing schema as you describe in above it returns me "function uuid_generate_v4() does not exist" I think this is not about migration but I can't find any solution.

[keakarsu]

Still same place.. Is there any suggestion to improve export speed..

[darold]

Please post the ora2pg command used to export data.

For the second issue, you need to install the uuid-ossp extension in your database:

gilles=# create extension "uuid-ossp";
CREATE EXTENSION
gilles=# select uuid_generate_v4() ;
           uuid_generate_v4           
--------------------------------------
 c1e2a6ac-845f-498d-9619-24b2ffc82605
(1 row)

[keakarsu]

Here is the command ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf -J 4

[darold]

OK, you database have lot of BLOB column, this will takes time to export data whatever the solution you choose. Following the statistics from the report.html document I would proceed as follow supposing you have 8 cores and you change DATA_LIMIT to 25000:

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf -P 4 -j 2 -e "ls_logitems has h_002_pntlines ts_taskenvvar h_002_pworkflgs h_002_pntcards h_002_pntbalance"

and then with the tables previously excluded:

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf -J 4 -j 2 -a "ls_logitems has h_002_pntlines ts_taskenvvar h_002_pworkflgs h_002_pntcards h_002_pntbalance"

How much cores and memory do you have on the Ora2Pg machine?

[keakarsu]

I have 16 gb ram and cpu is Intel Xeon e5-2650 v4 2.20Ghz with 12 core

[keakarsu]

Should I change LONGREADLEN and BLOB_LIMIT parameters in config ?

[keakarsu]

While creating schema I'm facing with a problem, If I run psql -1 mydb -f schema/tables/table.sql it returns me "function uuid_generate_v4() does not exist" but it has already installed and select uuid_generate_v4() returns data. When I try to import table from another program it returns me " character with byte sequence 0xcc 0x87 in encoding "UTF8" has no equivalent in encoding "LATIN5"

How can I avoid this issue?

[darold]

Do not change BLOB_LIMIT, Ora2Pg will automatically adjust to a reasonable value. For LONGREADLEN, defaut is 1MB so I guess you need to increase the value depending on the max size of your BLOB. If you don't know, set it to 50000000 and increase the value if you have an error message complaining about longreadlen. Do not increase the value too much because memory will be allocated following LONGREADLEN*DATA_LIMIT.

About the uuid_generate_v4() I think you have created the extension in the wrong database.

[keakarsu]

Database checked it is the correct one. When I try to import *.sql file it returns error which function uuid_generate_v4() does not exist , but if I try to create table which throws error seperately, it has been created succesfully. So I'm not able to create schema with one command.

I also tested uuid_generate_v4 with select command it returns correctly too..

[SebAlbert]

Maybe an issue with the search_path, so the function (which is probably in schema public) is not visible?

[keakarsu]

But If I want to create only this table than it's okey. Problem occurs creating all the schema

[darold]

As @SebAlbert said you may want to add public schema to the search_path defined in your sql files.

What is the result of the following command?: grep search_path schema/tables/*.sql

[keakarsu]

45 minutes ago I passed first error. It's all about search path as @SebAlbert said before. Now I'm facing with first error function nlssort(character varying, unknown) does not exist

[darold]

There is no automatic translation of NLSSORT() by Ora2Pg you need to fix this by hand. You may look at COLLATE. For example: ORDER BY colname COLLATE "da_DK"

[keakarsu]

Oh no, I have 1900 nlssort stament in my file. Isn't there any way to do this automatically? CREATE INDEX wfi_s_userloginprefs ON s_userloginprefs (nlssort(te_wfiid,'nls_sort=''TURKISH_CI''')); CREATE INDEX i_001_cadistdefinitions_03 ON u_001_cadistdefinitions (nlssort(description,'nls_sort=''TURKISH_CI'''), logicalref);

here is the sample data set

[darold]

I will see what can be done, the hard work seems to be the mapping between Oracle to PostgreSQL collation.

[keakarsu]

For now, I just skipped creating indexes. Now I'm trying to create procedures. In attachment I put some code for creating that procedure but it brings me a syntax error. I have compared with oracle side but I can't find any difference between them. Problem occurs at line 196 LOGO.U_CHECKDIFFWH_002_01 statement PostgreSQL.txt

[darold]

This is because Ora2Pg doesn't know about functions/procedures defined in the LOGO schema, you have to set LOOK_FORWARD_FUNCTION to LOGO in ora2pg.conf before exporting your procedures.

[keakarsu]

Even I change the config as you said above,it still throws same error..

[darold]

My bad I've read the file too quickly, well you will have to fix these call manually. Please post the source of the Oracle function logo.u_repairitemtots_002_01() I will see what's going wrong.

[keakarsu]

Here is this.. logo.u_repairitemtots_002_01 .txt

[darold]

This is the ora2pg translation, I need the original Oracle definition. You can find it in the sources/procedures/ sub-directory of your migration project tree.

[keakarsu]

Opss excuse me here is the correct one logo.u_repairitemtots_002_01 .txt

[keakarsu]

Even I have problems while creating schema, in other side I try to import data. While creating data it throws this error Wide character in subroutine entry at /usr/local/share/perl5/Ora2Pg.pm line 12750.ec., avg: 762 recs/sec) Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(HOST=10.0.1.67)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ora))) at /usr/local/share/perl5/Ora2Pg.pm line 12750.

[keakarsu]

Hİ there again, Now I have one more question about encoding. I set encoding parameters

# Enforce default language setting following the Oracle database encoding. This
# may be used with multibyte characters like UTF8. Here are the default values
# used by Ora2Pg, you may not change them unless you have problem with this
# encoding. This will set $ENV{NLS_LANG} to the given value.
NLS_LANG    TURKISH_TURKEY.WE8ISO8859P9
# This will set $ENV{NLS_NCHAR} to the given value.
NLS_NCHAR   WE8ISO8859P9

# By default PostgreSQL client encoding is automatically set to UTF8 to avoid
# encoding issue. If you have changed the value of NLS_LANG you might have to
# change  the encoding of the PostgreSQL client.
CLIENT_ENCODING LATIN5
---------
BINMODE     LATIN5

like this. But while creating schema has not been generated succesfuly. Some of the traditional characters was not converted succuesfuly. Which parameter should I change to avoid this problem.

[darold]

Please use latest development code, unset all these directive and proceed to direct import (not dump to file) and report the error if there is still one. Usually the "Wide character" error occurs when you are dumping data to files. Also line 12750 in the current Ora2Pg.pm code is related to user defined type, this is not related to data export so I guess you are not using latest code.

[keakarsu]

I downloaded lastest code and install ora2pg again but when I call ora2pg -r SHOW_VERSION -c ora2pg.conf it throws me this error.. syntax error at /usr/local/share/perl5/Ora2Pg.pm line 8181, near "$str ~" syntax error at /usr/local/share/perl5/Ora2Pg.pm line 8187, near "$str ~" BEGIN not safe after errors--compilation aborted at /usr/local/share/perl5/Ora2Pg.pm line 11214. Compilation failed in require at /usr/local/bin/ora2pg line 30. BEGIN failed--compilation aborted at /usr/local/bin/ora2pg line 30.

[keakarsu]

Still same result. Still traditional characthers shown incorrect. Also at last 5 commit I'm not able to export table it throws error. At last I have lots of questions and problems. Biggest one about characthers. Do you have any suggestion?

[darold]

To download and install latest development code you can proceed as follow:

wget https://github.com/darold/ora2pg/archive/master.zip
unzip master.zip
cd ora2pg-master/
perl Makefile.PL
make
sudo make install

I don't know where you get your file /usr/local/share/perl5/Ora2Pg.pm but it has been modified. There is no such error in Ora2Pg code.

[keakarsu]

I did it what as you said before, when I call ora2pg it throws syntax error at /usr/local/share/perl5/Ora2Pg.pm line 8181, near "$str ~" syntax error at /usr/local/share/perl5/Ora2Pg.pm line 8187, near "$str ~" BEGIN not safe after errors--compilation aborted at /usr/local/share/perl5/Ora2Pg.pm line 11214. Compilation failed in require at /usr/local/bin/ora2pg line 30. BEGIN failed--compilation aborted at /usr/local/bin/ora2pg line 30.

[keakarsu]

Here is the last error (19 June Commit) Wide character in subroutine entry at /usr/local/share/perl5/Ora2Pg.pm line 13269.

[darold]

Please post file /usr/local/share/perl5/Ora2Pg.pm here, last commit has been pushed on Fri Jun 22 16:53:06 2018, I'm curious to see what is the file you are working on.

[keakarsu]

Here is this. Ora2Pg.zip

[keakarsu]

Here is the last point. In attachment you can find my working copy of ora2pg.pm, my config and error. I really need help.

[darold]

Ok, this is the latest code but I don't understand the error message:

Wide character in subroutine entry at /usr/local/share/perl5/Ora2Pg.pm line 13269.

at this line the code is $sth->finish(); which can not generate this error.

[keakarsu]

Could you check my last post if it is possible. I think that will give you some idea.

[darold]

Ok the Ora2Pg.pm file in your latest zip is not the same and it can report the kind of error message you've posted. Please post here the result of the locale command executed from your terminal.

[keakarsu]

Here is the result ; LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC="en_US.UTF-8" LC_TIME="en_US.UTF-8" LC_COLLATE="en_US.UTF-8" LC_MONETARY="en_US.UTF-8" LC_MESSAGES="en_US.UTF-8" LC_PAPER="en_US.UTF-8" LC_NAME="en_US.UTF-8" LC_ADDRESS="en_US.UTF-8" LC_TELEPHONE="en_US.UTF-8" LC_MEASUREMENT="en_US.UTF-8" LC_IDENTIFICATION="en_US.UTF-8" LC_ALL=

[darold]

Good, unset CLIENT_ENCODING in your configuration file and give me the result of command:

ora2pg -c ora2pg.conf -t SHOW_ENCODING

[keakarsu]

Here is this Current encoding settings that will be used by Ora2Pg: Oracle NLS_LANG AMERICAN_AMERICA.AL32UTF8 Oracle NLS_NCHAR AL32UTF8 Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6 Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS PostgreSQL CLIENT_ENCODING UTF8 Perl output encoding '' Showing current Oracle encoding and possible PostgreSQL client encoding: Oracle NLS_LANG AMERICAN_AMERICA.WE8ISO8859P9 Oracle NLS_NCHAR WE8ISO8859P9 Oracle NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH24:MI:SS.FF6 Oracle NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS PostgreSQL CLIENT_ENCODING LATIN5

[darold]

Ok, now proceed to data export/import using -d option and redirecting the messages to a file and send the resulting file to my private email < gilles AT darold DOTnet >, example:

ora2pg -d -c ora2pg.conf -t COPY > result.log 2>&1

[keakarsu]

Good news. I changed ora2pg encoding to current oracle encoding than it did not throw error. Now I will check all of the scenarios from beginning. Really really appreciate..

[darold]

Good news.

[keakarsu]

Hi again,

I have a problem while creating procedures. It throws an syntax error when I try to call a function inside of a function. You can find the oracle definition and converted definition in attachment Oracle.txt Converted.txt ora2pg.conf.txt