Issue - Handles cannot be shared between threads driver may need a clone method

[shyamnambiar]

Hi,
I am a beginner.I am trying to migrate from my oracle to pg.But when I keep TYPE INSERT or TYPE COPY in the conf file , I am getting an error Handles cannot be shared between threads driver may need a clone method ,but if I keep TYPE TABLE , it is working fine.Please help me

[darold]

Hi,

Well I've not tested latest code over Windows operating system and was pretty sure that the new fork system won't works on it. Now I'm really sure, it won't works :-) Sorry for that, if you want to use Ora2Pg please use an Unix like system. Saying that, please consider that data export with Ora2Pg is not something that should be run on a desktop computer unless you have small gigabytes of data. Other types of export should still work because they don't use call to fork().

Regards,

[shyamnambiar]

Hi,

Thanks a lot for your quick reply !
I am a beginner and I think this is because of using same connection,in sub multiprocess_progressbar,
I could not see the actual use of these two line in it
$self->{dbh}->{InactiveDestroy} = 1;
$self->{dbh} = undef;

$self->{dbh} -> is not using in sub multiprocess_progressbar.
If I remove these 2 line, the error disappears but standing without giving any progress updates
Could you please give some quick idea like this to fix this and to make this working.

Sorry for troubling you...Don't take it serious if I type non-sense here I am a beginner , not a perl expert.

[darold]

This 2 lines are use to not disconnect from the Oracle database when the child is destroyed, as this child will be terminated at end of the export I think you can remove them safely, Ora2Pg could warn later but things were done.

Don't know about your configuration and the command line but I think you don't see the progress bar because you are in debug mode, please set DEBUG to 0 in your configuration file.

[shyamnambiar]

Hi,

Will Any other old version works on windows 7 ?

[darold]

Yes, versions before 11.0 should works.

[shyamnambiar]

Thanks a lot...10.1 worked.Nice....

[shyamnambiar]

Hi,
After getting the output.sql ,I tried to import it to my psql database but after a few lines I am getting this error related to encoding - psql:copy.sql:70163: ERROR: character with byte sequence 0x9d in encoding "WIN1
252" has no equivalent in encoding "UTF8" could you please help me ? Again sorry for troubling you

[darold]

You have to set the right values to NLS_LANG and CLIENT_ENCODING into ora2pg.conf. You can get these values by running the following command:

ora2pg -c /path/to/ora2pg.conf -t SHOW_ENCODING

Please try it and send me the result of the above command if you still have the error.

Note that the encoding should be set automatically by ora2pg but there was a bug that is only fixed in 11.0.

[shyamnambiar]

Hi,

This is the result :
NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252
CLIENT ENCODING WIN1252
Thanks for your quick reply

[darold]

Ok,

You have to set your ora2pg.conf as follow:

NLS_LANG     AMERICAN_AMERICA.WE8MSWIN1252

CLIENT_ENCODING    WIN1252

FILE_PER_TABLE       1

TRUNCATE_TABLE     1

then execute the following:

mkdir data/
ora2pg -t COPY -c /path/to/ora2pg.conf -b data/ -o table.sql

You will have one file per table under the data/ directory.

You can either try to load your data using table.sql global file and detect wich file is causing an encoding error.

In each file with error replace the client_encoding value to UTF8 instead of WIN1252, let me know.

Regards,

[shyamnambiar]

Hi,
Getting this error :

psql:data/KM_MAST_FF_table.sql:3: ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "km_rel_ff_usr_data" references "km_mast_ff".
HINT:  Truncate table "km_rel_ff_usr_data" at the same time, or use TRUNCATE ...CASCADE.

thanks

[darold]

Yes this is normal, a good practice is to load constraints, indexes, sequences and triggers at the end when all data are imported. If you can I really recommend you proceed like that, data import will be speediest. To do so activate FILE_PER_INDEX and FILE_PER_CONSTRAINT to separate them from the schema export.

Here to solve the issue with out restarting from the begining you can try to enable DROP_FKEY in configuration file and run again "ora2pg -t COPY -c /path/to/ora2pg.conf -b data/ -o table.sql" after removing everything in the data/ directory.

[shyamnambiar]

Hi,

Almost data imported well, but at last, getting this error
psql:data/table.sql:106: ERROR: relation "gid" does not exist

[shyamnambiar]

Hi,
Again I changed to UTF-8 encoding for one table and not worked , getting following error:

psql:data/KM_MAST_FF_table.sql:3941: ERROR: invalid byte sequence for encoding
"UTF8": 0xbf

waiting for your reply,
thank you so much again...

[darold]

During schema export the table named "gid" was not imported, may be an error was encountered, please look at the shema output file to see how the relation is created.

[shyamnambiar]

Hi,
Again I changed to UTF-8 encoding for one table and not worked , getting following error:

psql:data/KM_MAST_FF_table.sql:3941: ERROR: invalid byte sequence for encoding
"UTF8": 0xbf

waiting for your reply,
thank you so much again...

[darold]

In this case you may try other encoding, like LATIN1 or if you just have one or two errors replace the bad encoding characters in your data.

[shyamnambiar]

Hi ,

Ok.I will try that.
After the migration , column names turned to lowercase ,but I need it in uppercase,
could you please suggest me something needful ?

thanks a lot

[darold]

Why do you need them uppercase ? PostgreSQL is case insensitive so you may not have problem with that unless all object's name are between quotes in your queries, is that the reason ?

[shyamnambiar]

Hi,
After getting resultset , existing application will convert it into hashtable with column name as key.
I used keynames in uppercase through out the application , Please tell me is der any way to do this in ora2pg
otherwise kindly leave this...

LATIN1 Worked for that table thanks...

ALTER SEQUENCE GID RESTART WITH 12801; - this is not working , I think SEQUENCE GID is not created...
while I exported table schema.Could you please tell me ,what can I do to get this work ?

Thanks

[darold]

You can force Ora2Pg to use the same case than in Oracle, but I'm not sure it will solve your problem as all objects names will need to be enclosed between double quote. You just have to enable the directive CASE_SENSITIVE in your ora2pg.conf.

To export sequence, proceed as follow:

ora2pg -t SEQUENCE -c /path/to/ora2Pg.conf -o sequences.sql

this will export all sequences from Oracle into file sequences.sql.

The usual way is to only load sequences after all data being loaded, when exporting data you must set DISABLE_SEQUENCE to 1 to not add sequences restart order at end of data import file.

[shyamnambiar]

Hi,

    Yes.This worked......Thanks a lot