欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

基于ora2pg遷移Oracle19C到postgreSQL14的全過(guò)程

 更新時(shí)間：2023年11月30日 10:20:49   作者：IT邦德  
ora2pg是一個(gè)開(kāi)源工具,可將Oracle數(shù)據(jù)庫(kù)模式轉(zhuǎn)換為PostgreSQL格式,支持導(dǎo)出數(shù)據(jù)庫(kù)絕大多數(shù)對(duì)象類(lèi)型,本文就給大家介紹了基于ora2pg遷移Oracle19C到postgreSQL14的全過(guò)程,文中有詳細(xì)的代碼示例,需要的朋友可以參考下

1.Ora2Pg介紹

Ora2Pg是我的第一個(gè)盟友。
它是一個(gè)開(kāi)源工具，可將Oracle數(shù)據(jù)庫(kù)模式轉(zhuǎn)換為PostgreSQL格式。
可以處理大量的甲骨文對(duì)象
可通過(guò)配置文件進(jìn)行配置
https://ora2pg.darold.net/

特點(diǎn)：
支持導(dǎo)出數(shù)據(jù)庫(kù)絕大多數(shù)對(duì)象類(lèi)型，包括表、視圖、序列、索引、外鍵、約束、函數(shù)、存儲(chǔ)過(guò)程等。
提供PL/SQL到PL/PGSQL語(yǔ)法的自動(dòng)轉(zhuǎn)換，一定程度避免了人工修正。
可生成遷移報(bào)告，包括遷移難度評(píng)估、人天估算。
可選對(duì)導(dǎo)出數(shù)據(jù)進(jìn)行壓縮，節(jié)約磁盤(pán)開(kāi)銷(xiāo)。
配置選項(xiàng)豐富，可自定義遷移行為。

2.ora2pg安裝

2.1 安裝依賴(lài)包

yum install -y gcc perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN bzip2
perl-ExtUtils-eBuilder perl-ExtUtils-MakeMaker perl-Time-HiRes perl-tests perf cpan

2.2 正式安裝

perl -MCPAN -e ‘install DBI'
perl -MCPAN -e ‘install DBD::Oracle'
perl -MCPAN -e ‘install DBD::Pg'

3.相關(guān)配置

3.1 表結(jié)構(gòu)配置

cat > /etc/ora2pg/ora2pg_table_ddl.conf <<“EOF”
ORACLE_HOME /usr/lib/oracle/21/client64
ORACLE_DSN dbi:Oracle:host=172.18.12.90;sid=oradb;port=1521
#ORACLE_DSN dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
#ORACLE_DSN dbi:Oracle:tns_ora19c
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA STEST
EXPORT_SCHEMA 1
CREATE_SCHEMA 1
TYPE TABLE
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys checks
#SKIP keys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT_DIR /tmp
OUTPUT ora2pg_table_ddl.sql
PG_VERSION 14
EOF

3.2 表數(shù)據(jù)的配置文件

cat > /etc/ora2pg/ora2pg_table_data.conf <<“EOF”
ORACLE_HOME /usr/lib/oracle/21/client64
ORACLE_DSN dbi:Oracle:host=172.18.12.90;sid=oradb;port=1521
#ORACLE_DSN dbi:Oracle:tns_ora19c
ORACLE_USER system
ORACLE_PWD oracle
SCHEMA STEST
TYPE COPY
PG_NUMERIC_TYPE 0
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC float
SKIP fkeys checks
#SKIP fkeys pkeys ukeys indexes checks
NLS_LANG AMERICAN_AMERICA.UTF8
OUTPUT_DIR /tmp
OUTPUT ora2pg_table_data.sql
PG_DSN dbi:Pg:dbname=jemdb;host=172.18.12.50;port=5432
PG_USER postgres
PG_PWD jeames
PG_SCHEMA stest
PG_VERSION 14
EOF

4.ora2pg遷移數(shù)據(jù)

4.1 遷移全部表結(jié)構(gòu)

mkdir -p /ora2pg
ora2pg -c /etc/ora2pg/ora2pg_table_ddl.conf -t table -b /ora2pg

4.2 PG中創(chuàng)建數(shù)據(jù)

su - postgres
psql
CREATE USER STEST WITH password ‘post' CREATEDB SUPERUSER replication createrole login;
create database jemdb;
alter database jemdb owner to STEST;
\c jemdb
– 跑腳本
\i /ora2pg/ora2pg_table_ddl.sql
jemdb=# \d

4.3 遷移數(shù)據(jù)

ora2pg -d -t copy -c /etc/ora2pg/ora2pg_table_data.conf -P 12 -L 100000 -j 12
此過(guò)程執(zhí)行完成后，數(shù)據(jù)就已經(jīng)插入到PG數(shù)據(jù)庫(kù)中了：

5.數(shù)據(jù)驗(yàn)證

su - postgres
psql
\c jemdb

emdb=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

jemdb=# set search_path=stest,public;
SET
jemdb=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 stest  | addresses            | table | postgres
 stest  | card_details         | table | postgres
 stest  | customers            | table | postgres
 stest  | inventories          | table | postgres
 stest  | logon                | table | postgres
 stest  | order_items          | table | postgres
 stest  | orderentry_metadata  | table | postgres
 stest  | orders               | table | postgres
 stest  | product_descriptions | table | postgres
 stest  | product_information  | table | postgres
 stest  | warehouses           | table | postgres
(11 rows)

jemdb=# select  nspname AS schemaname,
jemdb-# relname,
jemdb-# reltuples::numeric as rowcount,
jemdb-# pg_size_pretty (
jemdb(# pg_total_relation_size ( '"' || nspname || '"."' || relname || '"' )) AS SIZE
jemdb-# from    pg_class C LEFT JOIN pg_namespace N ON ( N.oid = C.relnamespace ) 
jemdb-# where   nspname NOT IN ( 'pg_catalog', 'information_schema' ) 
jemdb-# AND relkind = 'r' 
jemdb-# ORDER by reltuples DESC 
jemdb-# LIMIT 20;
 schemaname |       relname        | rowcount |    size    
------------+----------------------+----------+------------
 stest      | inventories          |   899441 | 433 MB
 stest      | order_items          |     7341 | 1072 kB
 stest      | logon                |     2383 | 160 kB
 stest      | card_details         |     1500 | 264 kB
 stest      | addresses            |     1500 | 264 kB
 stest      | orders               |     1430 | 376 kB
 stest      | warehouses           |     1000 | 192 kB
 stest      | customers            |     1000 | 440 kB
 stest      | product_descriptions |     1000 | 288 kB
 stest      | product_information  |     1000 | 400 kB
 stest      | orderentry_metadata  |       -1 | 8192 bytes
(11 rows)

jemdb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 jemdb     | stest    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

jemdb=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
 stest  | stest
(2 rows)

jemdb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 stest     | Superuser, Create role, Create DB, Replication             | {}

以上就是基于ora2pg遷移Oracle19C到postgreSQL14的全過(guò)程的詳細(xì)內(nèi)容，更多關(guān)于遷移Oracle19C到postgreSQL14的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

相關(guān)文章

最新評(píng)論