Closed shree4343 closed 2 years ago
darold
commented
5 years ago Hi,
DDL direct import is voluntary not possible with Ora2Pg, you need to pass through flat files. Only data can be migrated on the fly. This is historical but with current Ora2Pg code and PostgreSQL features this limitation could be removed. However you can simply export to file and use psql to import the DDL.
Regards,
shree4343
commented
5 years ago Hi Darold, I just want to migrate oracle DB to AWS RDS postgres instance. Is it possible to migrate oracle data with ora2pg tool?
darold
commented
5 years ago Yes you can migrate everything DDL, Stored Procedures and DML but only data can be load directly. For all object creation Ora2Pg generate a SQL file that you have to import into the PostgreSQL target database before importing data.
sritama-stack
commented
3 years ago Hi
I am trying to migrate oracle DB to AWS RDS Postgres instance. Is it possible to migrate oracle data with the ora2pg tool?
Anyone can help, please? any documentation also helps me.
thanks in Advance
Hi There,
I want to import my data from oracle 12c to postgres database in different server directly using Ora2Pg tool. When I start migration I got some errors. I want to write what I do from starting to getting error
*Below credentials are just for example. **
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 [========================>] 9/9 tables (100.0%) end of scanning. DBD::Pg::db do failed: ERROR: relation "customers" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 12746. FATAL: ERROR: relation "customers" does not exist Aborting export...
############################################################### My config in below ############################ ORACLE_HOME /usr/lib/oracle/12.2/client64 ORACLE_DSN dbi:Oracle:host=192.168.1.12;sid=test7;port=1521----- ORACLE_USER system ORACLE_PWD *** USER_GRANTS 0 DEBUG 1 SCHEMA SHREE CREATE_SCHEMA 1 COMPILE_SCHEMA 1 PG_SCHEMA SHREE TYPE COPY PACKAGE VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TYPE PARTITION DISABLE_COMMENT 0 EXTERNAL_TO_FDW 1 TRUNCATE_TABLE 0 USE_TABLESPACE 0 REORDERING_COLUMNS 0 CONTEXT_AS_TRGM 0 FTS_INDEX_ONLY 1 USE_UNACCENT 0 USE_LOWER_UNACCENT 0 DATADIFF 0 DATADIFF_UPDATE_BY_PKEY 0 DATADIFF_DEL_SUFFIX _del DATADIFF_UPD_SUFFIX _upd DATADIFF_INS_SUFFIX _ins DATADIFF_WORK_MEM 256 MB DATADIFF_TEMP_BUFFERS 512 MB KEEP_PKEY_NAMES 0 PKEY_IN_CREATE 0 FKEY_ADD_UPDATE never FKEY_DEFERRABLE 0 DEFER_FKEY 0 DROP_FKEY 0 DISABLE_SEQUENCE 1 DISABLE_TRIGGERS 0 PRESERVE_CASE 0 INDEXES_RENAMING 0 INDEXES_RENAMING 0 USE_INDEX_OPCLASS 0 PREFIX_PARTITION 0 DISABLE_PARTITION 1 WITH_OID 0 ORA_RESERVED_WORDS audit,comment USE_RESERVED_WORDS 0 PG_DSN dbi:Pg:dbname=shree;host=192.168.1.13;port=5432 PG_USER postgres PG_PWD **** FILE_PER_CONSTRAINT 1 FILE_PER_INDEX 1 FILE_PER_FKEYS 1 FILE_PER_TABLE 1 FILE_PER_FUNCTION 1 STOP_ON_ERROR 1 COPY_FREEZE 0 CREATE_OR_REPLACE 1 PG_NUMERIC_TYPE 1 PG_INTEGER_TYPE 1 DEFAULT_NUMERIC bigint ENABLE_MICROSECOND 1 TO_NUMBER_CONVERSION numeric GEN_USER_PWD 0 FORCE_OWNER 0 DATA_LIMIT 10000 NOESCAPE 0 TRANSACTION serializable STANDARD_CONFORMING_STRINGS 1 LONGREADLEN 1047552 NO_LOB_LOCATOR 0 XML_PRETTY 0 LOG_ON_ERROR 0 TRIM_TYPE BOTH INTERNAL_DATE_MAX 49 FUNCTION_CHECK 1 JOBS 1 ORACLE_COPIES 1 PARALLEL_TABLES 1 DROP_INDEXES 0 SYNCHRONOUS_COMMIT 0 EXPORT_INVALID 0 PLSQL_PGSQL 0 NULL_EQUAL_EMPTY 0 EMPTY_LOB_NULL 0 PACKAGE_AS_SCHEMA 1 REWRITE_OUTER_JOIN 1 FUNCTION_STABLE 1 COMMENT_COMMIT_ROLLBACK 0 USE_ORAFCE 0 ESTIMATE_COST 0 COST_UNIT_VALUE 5 DUMP_AS_HTML 0 TOP_MAX 10 HUMAN_DAYS_LIMIT 5 PG_SUPPORTS_WHEN 1 PG_SUPPORTS_INSTEADOF 1 PG_SUPPORTS_MVIEW 1 PG_SUPPORTS_CHECKOPTION 1 PG_SUPPORTS_IFEXISTS 1 PG_SUPPORTS_LATERAL 1 PG_SUPPORTS_PARTITION 1 BITMAP_AS_GIN 1 PG_BACKGROUND 0 PG_SUPPORTS_SUBSTR 1 PG_SUPPORTS_NAMED_OPERATOR 1 AUTODETECT_SPATIAL_TYPE 1 CONVERT_SRID 1 DEFAULT_SRID 4326 GEOMETRY_EXTRACT_TYPE INTERNAL FDW_SERVER orcl MYSQL_PIPES_AS_CONCAT 0 MYSQL_INTERNAL_EXTRACT_FORMAT 0
I just want to migrate oracle database schema to postgresql database without manual copying DDL to different postgre server. This is just for simulation to migrate oracle DB to AWS RDS postgres instance. As just informed AWS RDS does not provide server access. It only provide database connection strings for database connection only. I try to find out something but unfortunately I'm not able to find any solution.