Actual ora2pg is migrated from Oracle11g to PG12 four

ora2pg Version selection

https://github.com/darold/ora2pg/releases
After testing ：21.1-23.1 Version uniform import gis The type table reports an error .

ora2pg -p -c xxx.conf -t TABLE -o table.sqlFATAL: _column_info() ORA-00911: invalid character (DBD ERROR: error possibly near <> indicator at char 17 in 'SELECT ST_SRID(c.<>%s) FROM %s c')DBI::db=HASH(0x2ec0bb8)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/local/share/perl5/Ora2Pg.pm line 14893.Aborting export...

So this time we use 21.0 edition .

The steps for version change are very simple ：

1. download zip Source code https://github.com/darold/ora2pg/releases2. Upload to oracle The server ,unzip Unzip the corresponding directory v.xx3. Just compile and install cd v.xxperl Makefile.PLmake && make installora2pg -v

install ora2pg May refer to ：https://blog.csdn.net/jycjyc/article/details/108466740

1. Prepare the configuration file

db.conf Major changes ORACLE_DSN,ORACLE_USER,ORACLE_PWD,SCHEMA It's OK to wait for parameters .

PG_VERSION 12ORACLE_HOME /oracle/app/oracle/product/11.2.0/dbhome_1ORACLE_DSN  dbi:Oracle:host=192.168.226.177;sid=oradb;port=1521ORACLE_USER orauserORACLE_PWD  passwordSCHEMA orauser PG_NUMERIC_TYPE    0PG_INTEGER_TYPE    1DEFAULT_NUMERIC floatNLS_LANG    AMERICAN_AMERICA.UTF8#ALLOW TPR_* TSM_* #ALLOW tpr_*,tsm_*,twf_*#exclude *_20_* *_TSM_FUNCTION_*#SKIP    fkeys pkeys ukeys indexes checks#OUTPUT     table.sql

2. Guide table structure and index, etc

ora2pg -p -c db.conf -t TABLE -o table.sql Exclude specific names plus parameters  -e 'TABLE[XXX.*,XXX.*]' Specify the name plus parameters  -a 'TABLE[XXX.*,XXX.*]' Notice the dot symbol of fuzzy matching  .*

3. Import table data

For small libraries , You can output a guide 1 File data.sql

[root@db]# vi data.sh ora2pg -p -c db.conf -t COPY -o data.sql -j 4 [root@db]# chmod +x data.sh [root@db]# nohup ./data.sh > ./data.out &

For large libraries , It is recommended that the data of each table be output separately , More flexible .
oracle The script that splices the exported data : The script conditions can be modified according to the actual needs

select 'ora2pg -p -c db.conf -t COPY -a ''TABLE['|| table_name ||']'' -o '||table_name||'.sql -j 4' from user_tables where table_name not like '%LOG%' and table_name not like '%20%' and table_name not like '%19%' and table_name not like '%21%' and table_name not like '%0%' and table_name not like '%22%' and table_name not like '%_BAK%'  and table_name not like '%ColumnMetadata%' and table_name not like '%AQ$%'  order by 1 asc;

Output to the above file data.sh Give Execution Authority chmod +x data.sh
And then execute nohup ./data.sh > ./data.out & that will do

4. Guide diagram 、 Stored procedures and other objects

Store the following contents in other.sh in , Then execute it .

# cat other.sh  ora2pg -p -c db.conf -t VIEW -o VIEW.sql ora2pg -p -c db.conf -t SEQUENCE -o SEQUENCE.sql ora2pg -p -c db.conf -t TRIGGER -o TRIGGER.sql ora2pg -p -c db.conf -t FUNCTION -o FUNCTION.sql ora2pg -p -c db.conf -t PROCEDURE -o PROCEDURE.sql ora2pg -p -c db.conf -t PACKAGE -o PACKAGE.sql ora2pg -p -c db.conf -t MVIEW -o MVIEW.sql ora2pg -p -c db.conf -t DBLINK -o DBLINK.sql ora2pg -p -c db.conf -t SYNONYM -o SYNONYM.sql ora2pg -p -c db.conf -t TYPE -o TYPE.sql # chmod +x other.sh  # nohup ./other.sh > ./other.out# tail -f other.out

5. Import operation

export PGPASSWORD=xxx Save the trouble of inputting the password every time

5.1 guide type type , First import ：

psql --host=192.168.208.40 --port=5432 --username=pguser --echo-errors pgdb -f TYPE.sql -1

pg Add... At the end of import -1 Parameters , In this way, as long as an error is reported, it will immediately fail, roll back and throw an error .

5.2 Guide table structure

psql --host=192.168.208.40 --port=5432 --username=pguser --echo-errors pgdb -f table.sql -1 

5.3 Lead sequence

psql --host=192.168.208.40 --port=5432 --username=pguser --echo-errors pgdb -f SEQUENCE.sql 

5.4 Leading data （ Similar to the 4 Step , Consider writing sh In file , One batch processing , Re examination out journal , Just confirm whether there is any problem to be solved ）

psql --host=192.168.208.40 --port=5432 --username=pguser--echo-errors pgdb -f data.sql -1 

5.4 Import other objects , ditto