LightDB/Postgres 使用ora2pg遷移Oracle到LightDB/Postgres
一、ora2pg部署
1.1 ora2pg簡介
ora2pg是一款免費遷移工具，能將Oracle遷移到LightDB，簡單使用並記錄過程如下
1.2 安裝perl依賴
[root@localhost ~]$ yum install -y perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker Loaded plugins: langpacks, ulninfo Resolving Dependencies --> Running transaction check ---> Package perl.x86_64 4:5.16.3-294.el7_6 will be updated ---> Package perl.x86_64 4:5.16.3-297.el7 will be an update (省略中間...) Dependency Installed: gdbm-devel.x86_64 0:1.10-8.el7 perl-ExtUtils-Install.noarch 0:1.58-297.el7 perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7 perl-Locale-Maketext.noarch 0:1.23-3.el7 perl-Module-CoreList.noarch 1:2.76.02-297.el7 perl-Module-Load-Conditional.noarch 0:0.54-3.el7 perl-Params-Check.noarch 1:0.38-2.el7 perl-Test-Harness.noarch 0:3.28-3.el7 perl-version.x86_64 3:0.99.07-6.el7 systemtap-sdt-devel.x86_64 0:4.0-13.0.1.el7 Updated: perl.x86_64 4:5.16.3-297.el7 Dependency Updated: perl-libs.x86_64 4:5.16.3-297.el7 Complete!
1.3 安裝DBI模組
DBI，Database Independent Interface，是Perl語言連線資料庫的介面，下載地址
下載DBI-1.643.tar.gz
然後解壓安裝
[root@node1 ora2pg]# tar -xzvf DBI-1.643.tar.gz [root@node1 ora2pg]# cd DBI-1.643/ [root@node1 DBI-1.643]# perl Makefile.PL [root@node1 DBI-1.643]# make && make install
1.4 安裝DBD::Oracle模組
新增環境變數，需要在本機安裝Oracle，在root下執行export即可
[root@node1 DBD-Oracle-1.74]# export ORACLE_BASE=/oracle/app [root@node1 DBD-Oracle-1.74]# export ORACLE_HOME=/oracle/app/product/19.3.0/db_1 [root@node1 DBD-Oracle-1.74]# export ORACLE_SID=orcl1 [root@node1 DBD-Oracle-1.74]# export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
安裝DBD-Oracle驅動，下載地址：點開後面連線找到download下載
下載 DBD-Oracle-1.74.tar.gz
[root@node1 ora2pg]# tar -xzvf DBD-Oracle-1.74.tar.gz [root@node1 ora2pg]# cd DBD-Oracle-1.74/ cd DBD-Oracle-1.74/ [root@node1 DBD-Oracle-1.74]# perl Makefile.PL [root@localhost /usr/local/DBD-Oracle-1.74]$ make && make install ...略... Installing /usr/local/share/man/man3/DBD::Oracle::Troubleshooting::Win64.3pm Installing /usr/local/share/man/man3/DBD::Oracle::Troubleshooting::Cygwin.3pm Installing /usr/local/share/man/man3/DBD::Oracle::Troubleshooting::Hpux.3pm Installing /usr/local/share/man/man3/DBD::Oracle::GetInfo.3pm Appending installation info to /usr/lib64/perl5/perllocal.pod
最後安裝成功資訊如上所示
1.5 安裝DBD::Pg模組
安裝DBD-Pg驅動，
，下載出DBD -Pg-3.14.2.tar.gz
登入到lightdb使用者，配置pg_config
cd $PGHOME/bin ln -s lt_config pg_config
root使用者配置環境變數並安裝
export LIGHTDB_PORT=5432
export PGUSER=lightdb
export LIGHTDB_HOST=10.0.4.4
export POSTGRES_HOME=/home/lightdb/base/lightdb-x/13.3-22.1
export PGDATA=/home/lightdb/data
export PATH=${POSTGRES_HOME}/bin:${POSTGRES_HOME}/tools/iftop/bin:${POSTGRES_HOME}/tools/iotop/bin:${POSTGRES_HOME}/tools/linux-ftools/bin:${POSTGRES_HOME}/tools/vmtouch/bin:${PATH}
export LD_LIBRARY_PATH=${POSTGRES_HOME}/lib:${POSTGRES_HOME}/lib/ltext:${LD_LIBRARY_PATH}
[root@node1 ora2pg]# tar -zxvf DBD-Pg-3.15.1.tar.gz
[root@node1 ora2pg]# cd DBD-Pg-3.15.1/
[root@localhost /usr/local]$ perl Makefile.PL
[root@localhost /usr/local]$ make
[root@localhost /usr/local]$ make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib64/perl5/auto/DBD/Pg/Pg.so
Installing /usr/local/lib64/perl5/auto/DBD/Pg/Pg.bs
Installing /usr/local/lib64/perl5/DBD/Pg.pm
Installing /usr/local/lib64/perl5/Bundle/DBD/Pg.pm
Installing /usr/local/share/man/man3/Bundle::DBD::Pg.3pm
Installing /usr/local/share/man/man3/DBD::Pg.3pm
Appending installation info to /usr/lib64/perl5/perllocal.pod
最後安裝成功資訊如上所示
1.6 安裝ora2pg
[root@node1 ora2pg]# cd ora2pg [root@node1 ora2pg]# ls changelog doc INSTALL lib LICENSE Makefile.PL MANIFEST packaging README scripts [root@node1 ora2pg]# perl Makefile.PLChecking if your kit is complete... [root@node1 ora2pg]# make cp lib/Ora2Pg.pm blib/lib/Ora2Pg.pm cp lib/Ora2Pg/GEOM.pm blib/lib/Ora2Pg/GEOM.pm cp lib/Ora2Pg/PLSQL.pm blib/lib/Ora2Pg/PLSQL.pm cp lib/Ora2Pg/Oracle.pm blib/lib/Ora2Pg/Oracle.pm cp lib/Ora2Pg/MySQL.pm blib/lib/Ora2Pg/MySQL.pm cp scripts/ora2pg blib/script/ora2pg /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg cp scripts/ora2pg_scanner blib/script/ora2pg_scanner /usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/ora2pg_scanner Manifying blib/man3/ora2pg.3 [root@node1 ora2pg]# make install Installing /usr/local/share/perl5/Ora2Pg.pm Installing /usr/local/share/perl5/Ora2Pg/GEOM.pm Installing /usr/local/share/perl5/Ora2Pg/PLSQL.pm Installing /usr/local/share/perl5/Ora2Pg/Oracle.pm Installing /usr/local/share/perl5/Ora2Pg/MySQL.pm Installing /usr/local/share/man/man3/ora2pg.3 Installing /usr/local/bin/ora2pg_scanner Installing /usr/local/bin/ora2pg Installing default configuration file (ora2pg.conf.dist) to /etc/ora2pg Appending installation info to /usr/lib64/perl5/perllocal.pod
1.7 檢視軟體是否安裝成功
[root@node1 ora2pg]# ora2pg --help Usage: ora2pg [-dhpqv --estimate_cost --dump_as_html] [--option value] -a | --allow str : Comma separated list of objects to allow from export. Can be used with SHOW_COLUMN too.
如上，ora2pg命令正確顯示即安裝成功
二、遷移資料庫物件
2.1 編輯配置檔案ora2pg.conf
用root賬戶，編輯配置檔案ora2pg.conf
### Oracle 客戶端的 ORACLE_HOME ORACLE_HOME /oracle/app/product/19.3.0/db_1 ### 源端連線串 MySQL如: dbi:mysql:host=192.168.1.10;database=tpch;port=3306 ORACLE_DSN dbi:Oracle:host=10.0.4.4;sid=orcl1;port=1521 PG_VERSION 13 TRUNCATE_TABLE 1 #If set 1, a TRUNCATE TABLE instruction will be add before loading data. This is usable only during INSERT or COPY export type. PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 0 #指定0轉換成bigint或者bigint #PG_INTEGER_TYPE 1 #指定1 轉換成numeric EXPORT_SCHEMA 1 #ALTER SCHEMA fund60trans1 OWNER TO fund60trans1; SET search_path = fund60trans1,public; DROP_IF_EXISTS 1 #CREATE SCHEMA IF NOT EXISTS fund60trans1; PREFIX_PARTITION 1 #匯出的分割槽表加上主表檔名字首 PREFIX_SUB_PARTITION 1 #同上，針對的物件是子分割槽 FILE_PER_CONSTRAINT 1 #將匯出的約束單獨放在一個檔案中 FILE_PER_INDEX 1 #將匯出的索引單獨放在一個檔案中 FILE_PER_FKEYS 1 #將匯出的外來鍵放在單獨的檔案中 USE_RESERVED_WORDS 1 #如果oracle中匯出的表名或列名有關鍵字，則匯出時自動為其加上雙引號，儘量詢問應用看能否更改PG中的表名或欄位名 TRANSACTION readonly #設定為只讀事務，避免誤操作Oracle端資料 DISABLE_UNLOGGED 1 #禁止轉換unlogged表，避免出現unlogged表 #DEFAULT_NUMERIC float JOBS 6 NLS_LANG AMERICAN_AMERICA.UTF8
2.2 編輯匯出指令碼
vim exp 新增如下內容
filedate=`date +"%Y%m%d_%H%M%S"`
if [ "$1" = "ora2pg" ]; then
if [ "$2" = "" ]; then
echo "Usage: ora ora2pg <exp/imp>"
exit 0
fi
if [ "$2" = "exp" ]; then
echo ""
echo "First edit ora2pg config file ora2pg.conf to make sure datasource is correct"
echo "vim ora set data_type_list parameter to set export object type"
echo ""
while true
do
read -p "please input schema name[exit or EXIT]:" schema
read -p "please input schema password[exit or EXIT]:" password
if [ -z "${schema}" ];then
echo "The username or password is empty, program exit"
exit 0
fi
if [ -z "${password}" ];then
echo "The username or password is empty, program exit"
exit 0
fi
if [ "$schema" = "exit" -o "$schema" = "EXIT" -o "$schema" = "exit" -o "$schema" = "EXIT" ]; then
echo "input exit, program exit"
exit 0 ###執行退出命令
fi
connect=`ora2pg -t SHOW_VERSION -c ora2pg.conf -u $schema -w $password`
echo $connect
ora28000_flag="ORA-28000"
ora01017_flag="ORA-01017"
oracle_logon_flag="Oracle"
if [[ "$connect" =~ ^"${ora01017_flag}".* ]]; then
echo $connect
echo "ORA-01017: invalid username/password; logon denied"
elif [[ "$connect" =~ ^"${ora28000_flag}".* ]]; then
echo $connect
echo "ORA-28000: The account is locked"
elif [[ "$connect" =~ ^"${oracle_logon_flag}".* ]]; then
mkdir -p $schema
# default export object
data_type_list='TABLE
PARTITION
COPY
SEQUENCE
SYNONYM'
#PROCEDURE
#FUNCTION
#PACKAGE
#GRANT
#VIEW
for data_type in $data_type_list
do
echo 'exporting' ${data_type}' please wait...'
# 新增 -P 10 指定並行
ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost -u $schema -w $password -n $schema -t$data_type -b $schema -o ${data_type}_${schema}_${filedate}.sql > ${data_type}_${schema}_${filedate}.log 2>&1 &
done
echo 'background exporting...'
fi
done
elif [ "$2" = "imp" ]; then
echo ""
echo "Make sure target Lightdb has been created database and schemas for the importing database"
echo ""
while true
do
read -p "please input superuser [exit or EXIT]:" username
read -p "please input superuser password[exit or EXIT]:" password
read -p "please input database name[exit or EXIT]:" db_name
read -p "please input target ip address[exit or EXIT]:" ip
read -p "please input target lightdb port[exit or EXIT]:" port
read -p "please input data folder[exit or EXIT]:" data_folder
read -p "import option? [table_only/view_only/data_only/index_only/foreign_key_only/all/ | exit or EXIT]:" import_option
if [ -z "${username}" ];then
echo "The superuser name is empty, program exit"
exit 0
fi
if [ -z "${password}" ];then
echo "The superuser password is empty, program exit"
exit 0
fi
if [ -z "${db_name}" ];then
echo "The target database name empty, program exit"
exit 0
fi
if [ -z "${ip}" ];then
echo "The target database ip information is empty, program exit"
exit 0
fi
if [ -z "${port}" ];then
echo "The target database port information is empty, program exit"
exit 0
fi
if [ -z "${data_folder}" ];then
echo "The data folder information is empty, program exit"
exit 0
fi
if [ -z "${import_option}" ];then
echo "The import option parameter is empty, program exit"
exit 0
fi
if [ "$import_option" = "exit" -o "$import_option" = "EXIT" -o "$username" = "exit" -o "$username" = "EXIT" -o "$password" = "exit" -o "$password" = "EXIT" -o "$db_name" = "exit" -o "$db_name" = "EXIT" -o "$ip" = "exit" -o "$ip" = "EXIT" -o "$port" = "exit" -o "$port" = "EXIT" -o "$data_folder" = "exit" -o "$data_folder" = "EXIT" ]; then
echo "input exit, program exit"
exit 0 ###執行退出命令
fi
#-v ON_ERROR_STOP=ON
# 如果要單獨匯入資料，需要先刪除掉外來鍵
# alter table act_ge_bytearray drop constraint if EXISTS act_fk_bytearr_depl;
# 然後再根據FKEYS_中的內容去建立
# ALTER TABLE act_ge_bytearray ADD CONSTRAINT act_fk_bytearr_depl FOREIGN KEY (deployment_id_) REFERENCES act_re_deployment(id_) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
if [ "$import_option" = "data_only" ]; then
echo 'Importing data begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^COPY_'` >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &
elif [ "$import_option" = "view_only" ]; then
echo 'Importing views begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^VIEW_'` >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &
elif [ "$import_option" = "index_only" ]; then
echo 'Importing indexes begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^INDEXES_'` >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &
elif [ "$import_option" = "foreign_key_only" ]; then
echo 'Importing foreign key begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^FKEYS_'` >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &
elif [ "$import_option" = "table_only" ]; then
echo 'Importing only tables begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^TABLE_'` >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}_${import_option}.log 2>&1 &
elif [ "$import_option" = "all" ]; then
echo 'Importing all folder please wait...'
## 如果要不匯入表註釋掉下兩行...
echo 'Importing tables begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' > imp_${db_name}_${data_folder}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^TABLE_'` >> imp_${db_name}_${data_folder}.log 2>&1 &&
for sql_file in `ls ${data_folder} | grep -v '^TABLE_' | grep -v '^FKEYS_'`
do
echo 'Importing '${sql_file}' begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}.log 2>&1
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/${sql_file} >> imp_${db_name}_${data_folder}.log 2>&1
done &&
echo 'analyze verbose begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -c "analyze verbose" >> imp_${db_name}_${data_folder}.log 2>&1 &&
echo 'Importing foreign key begin at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}.log 2>&1 &&
PGPASSWORD=$password ltsql -U $username -h $ip -p $port -d $db_name -f ${data_folder}/`ls ${data_folder} | grep '^FKEYS_'` >> imp_${db_name}_${data_folder}.log 2>&1 &&
echo 'Importing complete at '`date +"%Y-%m-%d %H:%M:%S"`'...' >> imp_${db_name}_${data_folder}.log 2>&1 &
fi
done
echo 'importing complete'
fi
fi
2.3 自定義匯出物件
多數情況下Oracle資料庫中是有分割槽表的，則需要在ora2pg.conf中的data_type_list同時指定TABLE和PARTITION
[root@node1 ora2pg]# ./exp ora2pg exp First edit ora2pg config file ora2pg.conf to make sure datasource is correct vim ora set data_type_list parameter to set export object type please input schema name[exit or EXIT]:scott please input schema password[exit or EXIT]:tiger Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 exporting TABLE please wait... exporting COPY please wait... exporting SEQUENCE please wait... exporting SYNONYM please wait... background exporting... please input schema name[exit or EXIT]: please input schema password[exit or EXIT]: The username or password is empty, program exit [root@node1 ora2pg]# cd scott [root@node1 scott]# ls -ltr total 32 -rw-r--r-- 1 root root 356 Jun 25 12:02 SEQUENCE_scott_20220625_120153.sql -rw-r--r-- 1 root root 354 Jun 25 12:02 SYNONYM_scott_20220625_120153.sql -rw-r--r-- 1 root root 784 Jun 25 12:02 PARTITION_scott_20220625_120153.sql -rw-r--r-- 1 root root 1333 Jun 25 12:02 TABLE_scott_20220625_120153.sql -rw-r--r-- 1 root root 354 Jun 25 12:02 INDEXES_TABLE_scott_20220625_120153.sql -rw-r--r-- 1 root root 463 Jun 25 12:02 FKEYS_TABLE_scott_20220625_120153.sql -rw-r--r-- 1 root root 511 Jun 25 12:02 CONSTRAINTS_TABLE_scott_20220625_120153.sql -rw-r--r-- 1 root root 3827 Jun 25 12:02 COPY_scott_20220625_120153.sql
2.4 自定義匯入表結構
[root@node1 ora2pg]# ./exp ora2pg imp Make sure target Lightdb has been created database and schemas for the importing database please input superuser [exit or EXIT]:scott please input superuser password[exit or EXIT]:scott please input database name[exit or EXIT]:scott please input target ip address[exit or EXIT]:10.20.30.199 please input target lightdb port[exit or EXIT]:5435 please input data folder[exit or EXIT]:scott import option? [table_only/view_only/data_only/index_only/foreign_key_only/all/ | exit or EXIT]:table_only please input superuser [exit or EXIT]: please input superuser password[exit or EXIT]: please input database name[exit or EXIT]: please input target ip address[exit or EXIT]: please input target lightdb port[exit or EXIT]: please input data folder[exit or EXIT]: import option? [table_only/view_only/data_only/index_only/foreign_key_only/all/ | exit or EXIT]: The superuser name is empty, program exit
檢視匯入日誌
[root@node1 ora2pg]# more imp_scott_scott_table_only.log Importing only tables begin at 2022-06-25 12:19:55... SET SET ltsql:scott/TABLE_scott_20220625_120153.sql:11: NOTICE: schema "scott" already exists, skipping CREATE SCHEMA ALTER SCHEMA SET CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE SET SET SET
2.5 自定義匯入表資料
[root@node1 ora2pg]# ./exp ora2pg imp Make sure target Lightdb has been created database and schemas for the importing database please input superuser [exit or EXIT]:scott please input superuser password[exit or EXIT]:scott please input database name[exit or EXIT]:scott please input target ip address[exit or EXIT]:10.20.30.199 please input target lightdb port[exit or EXIT]:5435 please input data folder[exit or EXIT]:scott import option? [table_only/view_only/data_only/index_only/foreign_key_only/all/ | exit or EXIT]:data_only please input superuser [exit or EXIT]: please input superuser password[exit or EXIT]: please input database name[exit or EXIT]: please input target ip address[exit or EXIT]: please input target lightdb port[exit or EXIT]: please input data folder[exit or EXIT]: import option? [table_only/view_only/data_only/index_only/foreign_key_only/all/ | exit or EXIT]: The superuser name is empty, program exit
檢視匯入日誌
[root@node1 ora2pg]# more imp_scott_scott_data_only.log Importing data begin at 2022-06-25 12:23:24... SET BEGIN SET SET SET TRUNCATE TABLE COPY 0 SET SET SET
2.6 目標端資料確認
scott@scott=# select * from orders; o_orderkey | o_orderdate | o_name ------------+---------------------+---------- 1.00 | 2021-11-11 00:00:00 | xiaoming 2.00 | 2022-01-11 00:00:00 | xiaogang 3.00 | 2022-02-11 00:00:00 | xiaoju (3 rows) scott@scott=# \d orders Partitioned table "scott.orders" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+--------- o_orderkey | numeric(20,2) | | not null | o_orderdate | timestamp without time zone | | not null | o_name | character varying(79) | | not null | Partition key: RANGE (o_orderdate) Number of partitions: 3 (Use \d+ to list them.)
總結
遷移過程中要考量資料型別相容性，基於PostgreSQL的資料庫都可以考慮使用ora2pg的方式進行遷移
不足：需要一定的停機時間
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2903107/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用GoldenGate 遷移Oracle到PostgreSQL/LightDBGoOracleSQL
- LightDB/Postgres邏輯複製的搭建
- lightdb -- Oracle相容 -- rownumOracle
- Oracle轉換PostgresOracle
- lightdb -- merge into insert 相容 OracleOracle
- LightDB-Oracle和LightDB邏輯備份測試對比（十二）Oracle
- MySQL資料庫遷移到PostgresMySql資料庫
- LightDB/PostgreSQL 相容Oracle儲存過程SQLOracle儲存過程
- lightdb的merge into使用介紹
- LightDB部署模式模式
- LightDB 23.1相容Oracle新特性支援Oracle
- Oracle/MySQL透過odbc訪問PostgreSQL for LightDBOracleMySql
- LightDB-像Oracle一樣使用最佳化器提示（十）Oracle
- docker 部署 postgresDocker
- Postgresql外部表使用 postgres_fdwSQL
- LightDB23.1新特性支援Oracle hint增強DB2Oracle
- LightDB 22.4 新特性之相容Oracle sqluldr2OracleSQL
- LightDB canopy 表管理
- LightDB/PostgreSQL 設定LightDB訪問白名單pg_hba.confSQL
- Postgres索引詳解索引
- LightDB 22.4 新特性之相容Oracle樹形查詢Oracle
- LightDB-指定lightdb_syntax_compatible_type切換不同資料引擎
- LightDB canopy叢集管理
- LightDB分散式實現分散式
- How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1OracleGo
- LightDB-指定lightdb_syntax_compatible_type切換不同資料引擎（十一）
- [保姆教程] [Postgres] 1分鐘深入瞭解Postgres主鍵自增
- postgres 讀書筆記筆記
- Postgres 流複製配置
- Postgres併發處理
- postgres yum源安裝
- Zalando Postgres Operator 快速上手
- LightDB/postgresql內建特性之訪問oracle之oracle_fdw介紹SQLOracle
- LightDB訂閱和釋出
- LightDB/PostgreSQL等待事件 Lock transactionidSQL事件
- LightDB/PostgreSQL 客戶端部署SQL客戶端
- LightDB 22.4 新特性之支援Oracle cardinality和ordered_predicates hintOracle
- 英國《衛報》是如何不停機從MongoDB遷移到Postgres？MongoDB