【オラクルクライアントインストール】
# rpm -ivh oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm
# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
# vi /etc/profile.d/oracle.sh <<__EOF__
export ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export C_INCLUDE_PATH=/usr/include/oracle/11.2/client64
sudo dnf -y install postgresql15-private-devel # シンボリックリンクのみで実体なし。
cpanm --quiet DBD::Pg is up to date (3.18.0).
【DBD::Oracle ドライバインストール】
$ sudo yum -y install perl-CPAN
$ sudo yum -y install libyaml-devel
$ sudo yum -y install gcc
$ git clone https://github.com/darold/ora2pg.git
__EOF__
# source /etc/profile
【参考】20241025 追記 amazon linux2023
sudo dnf -y install libnsl
cd /tmp
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-devel-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/1920000/oracle-instantclient19.20-jdbc-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-basic-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-devel-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-sqlplus-19.20.0.0.0-1.x86_64.rpm
sudo rpm -Uvh oracle-instantclient19.20-jdbc-19.20.0.0.0-1.x86_64.rpm
sudo sh -c 'cat > /etc/profile.d/oracle.sh <<__EOF__
export ORACLE_HOME=/usr/lib/oracle/19.20/client64/lib
export LD_LIBRARY_PATH=/usr/lib/oracle/19.20/client64/lib
export C_INCLUDE_PATH=/usr/include/oracle/19.20/client64
__EOF__'
source /etc/profile
sudo sh -c 'cat > /etc/ld.so.conf.d/oracle.conf <<__EOF__
/usr/lib/oracle/19.20/client64/lib
__EOF__'
sudo ldconfig
sqlplus user/password@host:1521/service_name(sid) # SIDではつながらなかった。
sudo dnf provides */pg_config
# /usr/bin/pg_config -> pg_server_config
sudo dnf -y install postgresql15-server-devel # これで実体がインストールされた。
sudo dnf -y install postgresql15-server-devel # これで実体がインストールされた。
sudo dnf -y install perl-CPAN
sudo dnf -y install libyaml-devel
sudo dnf -y install gcc # allready installed
sudo dnf -y install perl-ExtUtils-MakeMaker # allready installed
sudo dnf -y install perl-Test-Simple # allready installed
sudo dnf -y install perl-Env # allready installed
sudo dnf -y install postgresql15
sudo dnf -y install perl-DBI perl-DBD-Pg
$ cd /home/ec2-user
$ cd /home/ec2-user
※先にポストグレスとオラクルドライバをインストール
sudo cpan App::cpanminus
sudo cpan
sudo cpan
cpan 1> o conf init
Would you like to configure as much as possible automatically? [yes] yes
upgrade
What approach do you want? (Choose 'local::lib', 'sudo' or 'manual')
Would you like me to append that to /home/ec2-user/.bashrc now? [yes]
network traffice external site?( Y/n) return
Path to pg_config? return
Check for a new version of the Public Suffix List? [N ]
Import PAUSE and author keys to GnuPG? [y]
Do you want to run external tests?
These tests *will* *fail* if you do not have network connectivity. [n]
network traffic to external sites. [Y/n]
sudo cpanm --quiet YAML
sudo cpanm --quiet DBI
sudo cpanm --quiet Test
sudo cpanm --quiet Test::NoWarnings
sudo cpanm --quiet DBD::Oracle
sudo cpanm --quiet DBD::Pg
【ora2pg インストール】
$ sudo dnf install git
$ mkdir Make
$ cd Make
$ git clone https://github.com/darold/ora2pg.git
$ git clone https://github.com/darold/ora2pg.git
$ cd ./ora2pg
$ perl Makefile.PL
$ gmake
$ sudo gmake install
#---------------------------------------------------------------------------
【参考】 20240123 追記 Rocky linux9.3
# dnf install perl-CPAN
# dnf install libyaml-devel
# dnf install gcc
# dnf install perl-ExtUtils-MakeMaker
# dnf install perl-Test-Simple
# dnf install perl-Env
# dnf install postgresql15-devel
# dnf install perl-DBI perl-DBD-Pg
POSTGRES_HOME=/usr/pgsql-15 # for DBD::Pg
$ cpan
o conf init
upgrade
takahab
noxxxxx
install YAML
install Test
install Test::NoWarnings # 下記エラー発生の為追加。
install DBI
install DBD::Oracle
install DBD::Pg
DBD::Pg is up to date (3.18.0).
$ sudo yum -y install perl-CPAN
$ sudo yum -y install libyaml-devel
$ sudo yum -y install gcc
$ sudo yum install perl-ExtUtils-MakeMaker
$ sudo yum install perl-Test-Simple
$ sudo yum install perl-Test-Simple
$ sudo yum install perl-Env
$ sudo yum install postgresql-devel # for DBD::Pg
$ cpan
o conf init
$ export NLS_LANG=Japanese_Japan.JA16SJIS
【ora2pg インストール】
upgrade
install YAML
install Test
install Test::NoWarnings # 下記エラー発生の為追加。
install DBI
install DBD::Oracle
install DBD::Pg # for type=COPY
$ export ORACLE_SID=ARCSDBMS2
$ export ORACLE_USERID="arcsdbcb/password"
$ export ORACLE_DSN="dbi:Oracle:SID=arcsdbms2;host=192.168.10.201;port=1521"$ export NLS_LANG=Japanese_Japan.JA16SJIS
or
export NLS_LANG=Japanese_Japan.AL32UTF8
$ sqlplus64 arcsdbcb/passwd@10.0.19.2:1521/arcsdbms.world
# vi /etc/ld.so.conf.d/oracle.conf <<__EOF__
/usr/lib/oracle/11.2/client64/lib
__EOF__
$ cpan
install DBI
install DBD::Oracle
install DBD::Pg # for type=COPY
ERROR Test::NoWarnings is not installed (any version required)
install Test::NoWarnings
# vi /etc/profile.d/proxy.sh <<__EOF__
PROXY="http://10.0.19.2:3128"
export http_proxy=$PROXY
export HTTP_PROXY=$PROXY
export https_proxy=$PROXY
export HTTPS_PROXY=$PROXY
__EOF__
# cpan DBI
# cpan DBD::Oracle
又は、
$ perl -MCPAN -e shell #全てEnter
$ perl -MCPAN -e 'install DBI'
$ perl -MCPAN -e 'install DBD::Oracle'
$ git clone https://github.com/darold/ora2pg.git
$ cd ./ora2pg
$ perl Makefile.PL
$ gmake
$ sudo gmake install
【初期設定】
$ mkdir migration$ ora2pg --project_base ./migration --init_project test_project
$ vi ./migration/test_project/config/ora2pg.conf __EOF__
ORACLE_DSN dbi:Oracle:host=192.168.10.201;sid=xxxxxxxx;port=1521
ORACLE_USER xxxxxxxxx
ORACLE_PWD xxxxxx
# Oracle schema/owner to use
SCHEMA xxxxxxx
__EOF__
$ cd ./migration/test_project
ora2pg -t SHOW_VERSION -c config/ora2pg.conf
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
※ 設定ファイルのひな型 : /etc/ora2pg/ora2pg.conf.dist
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg.conf
【Oracle から Postgresへ変換】
$ ssh ora2pg
$ mkdir Database
$ cd Database
$ mkdir arcsdbcb
$ cd arcsdbcb
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg.conf
$ vi ora2pg.conf << __EOF__
ORACLE_DSN dbi:Oracle:host=10.0.19.2;sid=xxxxxxxx;port=1521
ORACLE_USER xxxxxxxxx
ORACLE_PWD xxxxxx
__EOF__
【on the fly 】
$ vi ora2pg.conf << __EOF__
PG_DSN dbi:Pg:dbname=ARCSDBMS;host=10.0.18.101;port=5432
PG_USER xxxxxxcb
PG_PWD xxxxx00
__EOF__
【表領域出力＆パーティション】
$ vi ora2pg.conf << __EOF__
DATA_LIMIT 1000 # for out of memory
USE_TABLESPACE 1
KEEP_PKEY_NAMES 0 # 表領域出力 USE_TABLESPACE=1にしているので、内部で強制的に１になる。
PREFIX_PARTITION 0 # RENAME_PARTITION と同じ動き？ → 0指定であとで手動で修正
PG_NUMERIC_TYPE 0 # numbe(n,m) → real → decimal(n,m)
PG_INTEGER_TYPE 0 # number(n) → bigint → numeric( n)
STOP_ON_ERROR 0 # for debug
__EOF__
【修正】
ora2pg_TABLE.sql
1) 1,$s/TABLESPACE ARCSDB/TABLESPACE TABLESPACE_ARCSDB/g
2) 1,$s/_primary/_pkey/
3) rb_xxxx →table_pkey に修正
$ cat *TAB*sql |grep rb_
ALTER TABLE cca002 ADD CONSTRAINT rb_4f3840d101 PRIMARY KEY (acode,gcode,ccode,scode);
ALTER TABLE cca003 ADD CONSTRAINT rb_48edd00701 PRIMARY KEY (acode,gcode,ccode,rcode);
ALTER TABLE cca004 ADD CONSTRAINT rb_48edd02701 PRIMARY KEY (acode,gcode,ccode,tcode);
ALTER TABLE ccc002 ADD CONSTRAINT rb_4f3840ec03 PRIMARY KEY (gcode,ccode,scode);
ALTER TABLE ccc003 ADD CONSTRAINT rb_48edd04105 PRIMARY KEY (gcode,ccode,rcode);
ALTER TABLE ccc004 ADD CONSTRAINT rb_48edd05a04 PRIMARY KEY (gcode,ccode,tcode);
4) サブパーティション名変更
mv ora2pg_PARTITION.sql ora2pg_PARTITION.sql.org
cat ora2pg_PARTITION.sql.org |./convert.py >ora2pg_PARTITION.sql
5) csb008 Primary KEY名不正 (ora2pg_TABLE.sql )
csb008 -> csb008_pkey
ysb008 -> ysb008_pkey
sss016_pkey2 -> sss016_pkey
TABLESPACE_ARCSDBPA_TBL -> TABLESPACE_ARCSDBSS_TBL (sst999 )
5) ERROR: テーブル空間 tablespace_arcsdbss_tbl へのアクセスが拒否されました。
arcsdbss -> arcsdbxx (一部 検索要）
arcsdbss -> arcsdbtm
$ mkdir -P ~/Database/arcsdbcb
$ cd Database/arcsdbcb
$ cp /etc/ora2pg/ora2pg.conf.dist ora2pg_cb.conf
$ time ora2pg -c ora2pg_cb.conf -t TABLE -b ~/Database/arcsdbcb/ -o ora2pg_TABLE.sql
[2024-01-10 19:52:19] [========================>] 82/82 tables (100.0%) end of scanning.
[2024-01-10 19:53:05] [========================>] 82/82 tables (100.0%) end of table export.
real 6m21.216s
user 0m0.367s
sys 0m0.093s
$ time ora2pg -c ora2pg_cb.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbcb/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 15:26:27] [========> ] 3564/9900 partitions (36.0%) end of output.
real 7m16.630s
user 0m0.623s
sys 0m0.231s
$ time ora2pg -c ora2pg_cb.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 15:47:53] [========================>] 82/82 tables (100.0%) end of scanning.
[2024-01-22 15:48:10] [========================>] 10/10 objects types (100.0%) end of objects auditing.
real 5m8.331s
user 0m0.155s
sys 0m0.067s
$ time ora2pg -c ora2pg_cb.conf -t GRANT -N arcsdcb -b ~/Database/arcsdbcb/ -o ora2pg_GRANT.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
real 1m52.493s
user 0m0.106s
sys 0m0.010s
$ time ora2pg -c ora2pg_ss.conf -t TABLE -b ~/Database/arcsdbss/ -o ora2pg_TABLE.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 19:05:12] [========================>] 375/375 tables (100.0%) end of scanning.
[2024-01-22 19:10:02] [========================>] 375/375 tables (100.0%) end of table export.
real 19m48.498s
user 0m1.752s
sys 0m0.362s
$ time ora2pg -c ora2pg_ss.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 19:34:16] [========> ] 28727/79783 partitions (36.0%) end of output.
real 22m30.711s
user 0m4.047s
sys 0m1.588s
$ time ora2pg -c ora2pg_ss.conf -t PARTITION -N arcsdbss -b ~/Database/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 because USE_TABLESPACE is enabled.
[2024-01-22 22:27:11] [========> ] 28727/79783 partitions (36.0%) end of output.
real 23m33.264s
user 0m4.306s
sys 0m1.546s
USAGE ./CreDB.sh create_tablespace_directory # 表領域の作成
./CreDB.sh dropdatabases_arcsdbms # ARCSDBMSデータベースの削除(databae,schema,role,table)
./CreDB.sh initdatabases_arcsdbms # ARCSDBMS全スキーマ・ROLE作成
./CreDB.sh arcsdbms_gettable # オラクルからテーブル情報取得
./CreDB.sh arcsdbms_createtable # ARCSDVMS内全テーブルの作成
./CreDB.sh arcsdbms_copy # オラクル環境からの全データのコピー
ERROR: 共有メモリが足りません
HINT: max_locks_per_transactionを増やす必要があるかもしれません
# vi /var/lib/pgsql/15/data/postgresql.conf
shared_buffers =128MB -> 256MB
max_locks_per_transaction = "" ->512
max_connections = 100 -> 200
# systemctl restart postgresql-15
→解消されず。
# vi /pg_data/data/postgresql.conf
max_locks_per_transaction = "" ->1024
→ 解消された。
TABLESPACEを指定しないCREATEテーブル文がある。
→
SET default_tablespace = TABLESPACE TABLESPACE_ARCSDBSS_TBL; 追加。
FATAL: ERROR: リレーション"ccm201"の列"norcode"は存在しません.
→ psql -h 10.0.18.101 -U arcsdbcb -d arcsdbms
ALTER TABLE arcsdbcb.ccm201 ADD norcode varchar(1) NULL DEFAULT '0';
psql -h 10.0.18.101 -U vrcsdbcb -d arcsdbms
ALTER TABLE vrcsdbcb.ccm201 ADD norcode varchar(1) NULL DEFAULT '0';
削除するときは
ALTER TABLE arcsdbcb.ccm201 DROP COLUMN norcode;
※ CXD擬似のxrcsdbcbは、norcodeはなかった。
FATAL: ERROR: リレーション"ssn003"の列"authoritymasterprod"は存在しません
→
psql -h 10.0.18.101 -U arcsdbss -d arcsdbms
alter table arcsdbss.ssn003 add authoritymasterprod varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasterdept varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasterprodgroup varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymastercust varchar(1) NULL DEFAULT '2';
alter table arcsdbss.ssn003 add authoritymasteremployee varchar(1) NULL DEFAULT '2';
FATAL: ERROR: リレーション"ssz103"の列"month"は存在しません
→
psql -h 10.0.18.101 -U arcsdbss -d arcsdbms
alter table arcsdbss.ssz103 add month numeric(14,0);
alter table arcsdbss.ssz103 add whcode numeric(14,0);
alter table arcsdbss.ssz103 add actqty numeric(14,0);
alter table arcsdbss.ssz103 add alcqty numeric(14,0);
alter table arcsdbss.ssz103 add resqty numeric(14,0);
alter table arcsdbss.ssz103 add movqty numeric(14,0);
alter table arcsdbss.ssz103 add ordqty numeric(14,0);
alter table arcsdbss.ssz103 add rcvqty numeric(14,0);
alter table arcsdbss.ssz103 add pmonthqty numeric(14,0);
alter table arcsdbss.ssz103 add whinqty numeric(14,0);
alter table arcsdbss.ssz103 add whoutqty numeric(14,0);
alter table arcsdbss.ssz103 add whadjqty numeric(14,0);
alter table arcsdbss.ssz103 add qtypurchaseret numeric(14,0);
alter table arcsdbss.ssz103 add qtysalesret numeric(14,0);
alter table arcsdbss.ssz103 add qtymoveout numeric(14,0);
alter table arcsdbss.ssz103 add qtymovein numeric(14,0);
alter table arcsdbss.ssz103 add qtyinventoryout numeric(14,0);
alter table arcsdbss.ssz103 add qtyinventoryin numeric(14,0);
alter table arcsdbss.ssz103 add qtyrepairout numeric(14,0);
alter table arcsdbss.ssz103 add qtyrepairin numeric(14,0);
alter table arcsdbss.ssz103 add qtyproductout numeric(14,0);
alter table arcsdbss.ssz103 add qtyproductin numeric(14,0);
alter table arcsdbss.ssz103 add qtydismantleout numeric(14,0);
alter table arcsdbss.ssz103 add qtydismantlein numeric(14,0);
alter table arcsdbss.ssz103 add qtyhousebonout numeric(14,0);
※ データもNULLなので、本来なら擬似環境から削除する方が望ましい。
擬似環境ORACLE DLL
"MONTH" NUMBER(*,0),
"WHCODE" NUMBER(*,0),
"ACTQTY" NUMBER(*,0),
"ALCQTY" NUMBER(*,0),
"RESQTY" NUMBER(*,0),
"MOVQTY" NUMBER(*,0),
"ORDQTY" NUMBER(*,0),
"RCVQTY" NUMBER(*,0),
"PMONTHQTY" NUMBER(*,0),
"WHINQTY" NUMBER(*,0),
"WHOUTQTY" NUMBER(*,0),
"WHADJQTY" NUMBER(*,0),
"QTYPURCHASERET" NUMBER(*,0),
"QTYSALESRET" NUMBER(*,0),
"QTYMOVEOUT" NUMBER(*,0),
"QTYMOVEIN" NUMBER(*,0),
"QTYINVENTORYOUT" NUMBER(*,0),
"QTYINVENTORYIN" NUMBER(*,0),
"QTYREPAIROUT" NUMBER(*,0),
"QTYREPAIRIN" NUMBER(*,0),
"QTYPRODUCTOUT" NUMBER(*,0),
"QTYPRODUCTIN" NUMBER(*,0),
"QTYDISMANTLEOUT" NUMBER(*,0),
"QTYDISMANTLEIN" NUMBER(*,0),
"QTYHOUSEBONOUT" NUMBER(*,0),
FATAL: ERROR: 行に対応するパーティションがリレーション"ssz103"に見つかりません SSZ103 (1000 recs/sec)
DETAIL: 失敗した行のパーティションキーは(zdate) = (20520120)を含みます。
CONTEXT: ssz103のCOPY、行 79: "301 1093 0001 7716650000361 20520120 1000000000000004 20220120165711 905 0 -1 0 0 1 905 0 0 00 0 0 ..."
FATAL: ERROR: 行に対応するパーティションがリレーション"ssz104"に見つかりません
DETAIL: 失敗した行のパーティションキーは(month) = (205201)を含みます。
CONTEXT: ssz104のCOPY、行 64: "301 1093 0001 7716650000361 205201 1000000000000004 20220120165711 905 0 -1 0 0 1 905 0 0 0 00 0 0 ..."
→デフォルトパーティション作成
CREATE TABLE ssz103_default PARTITION OF ssz103 DEFAULT;
CREATE TABLE ssz104_default PARTITION OF ssz104 DEFAULT;
CREATE TABLE ssz111_default PARTITION OF ssz111 DEFAULT;
CREATE TABLE ssz112_default PARTITION OF ssz112 DEFAULT;
CREATE TABLE ssz113_default PARTITION OF ssz113 DEFAULT;
CREATE TABLE ssz118_default PARTITION OF ssz118 DEFAULT;
NOTICE: リレーション"batchstatuslog_seq"は存在しません、スキップします
→ vi run.sh
time ora2pg -c ora2pg_pa.conf -t SEQUENCE -N arcsdbpa -b ~/Database/arcsdbpa/ -o ora2pg_SEQUENCE.sql
psql:ora2pg_VIEW.sql:149: ERROR: 列"BUSINESSTYPE"は存在しません
行 3: select "BUSINESSTYPE","PROCESSTYPE","REVERSALTYPE","DEALINGM...
※ ""カット（大文字->小文字）
→129,149s/"//g
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)
※ CXD擬似には、arcsdbtp, darcsdbcb ,trcsdbcb は存在しない。
【参考】
psql コマンド 例
psql -h host -d postgres -U postgres
\l データベース一覧表示
\c arcsdbms データベースを切り替える
\c arcsdbms arcsdbcb データベースとユーザを切り替える
\c - arcsdbss ユーザのみを切り替える
\dn スキーマ一覧
\d テーブル一覧表示(viewやsequence含む)
\dt テーブル一覧 （ユーザ切り替え後に実行）
\d
\d arcsdbcb.cca001 列定義一覧
\z arcsdbcb.cca001 テーブルのアクセス権限表示
\du ユーザー(Role)の一覧を表示
\dp アクセス権限の表示
\z
select current_database();select current_user; 現在のユーザ
select * from pg_user; ユーザ一覧
#----------------------------------------
# 定義
#----------------------------------------
STAGE=test_
POSTGRES_HOST=192.168.210.201
SUPERUSER_NAME=${STAGE}admin
DATABASE_NAME=${STAGE}arcsdbms
#SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs arcsdbco arcsdbtm arcsdbtp vrcsdbcb xrcsdbcb"
#SCHEMA_LISTS="xrcsdbcb vrcsdbcb"
#SCHEMA_LISTS="arcsdbcb"
SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs vrcsdbcb xrcsdbcb"
TABLE_LISTS="ssa000 ssa001"
#----------------------------------------
# Main ルーチン
#----------------------------------------
main(){
case $1 in
new )
new
;;
drop )
drop
;;
all )
clean
init
getdll
report
;;
init )
init
;;
getdll )
getdll
;;
putdll )
putdll
;;
report )
report
;;
copy )
copy
;;
copytable )
if [ -p /dev/stdin ]; then
cat -
else
echo $@
fi | copy_by_table
;;
save )
save
;;
clean )
clean
;;
diff )
diff
;;
show )
show
;;
showtable )
showtable
;;
psql )
psql
;;
*)
echo "usage: ./run clean # スキーマワークディレクトリの削除"
echo "usage: ./run drop # 表領域＆データベース＆スキーマ＆ユーザの削除"
echo "usage: ./run new # 表領域＆データベース＆スキーマ＆ユーザの作成"
echo "usage: ./run init # ora2pg.conf ファイルの初期化とスキーマワークディレクトリの作成"
echo "usage: ./run getdll # OracleDBからDLL取得"
echo "usage: ./run putdll # PostgresDBにDLLi登録"
echo "usage: ./run report # 変換レポートの作成"
echo "usage: ./run copy # oracleDBらデータをexportしてposgreDBにimport"
echo "usage: ./run all (clean/init/getdll/report) # /clean/init/getdll/reportを一括実行"
;;
esac
}
new(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
/* --------------------------------------------------+
| create ROLE |
+-------------------------------------------------- */
create user ${SUPERUSER_NAME} with password 'casio00' superuser;
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data
--\! sudo chown postgres:postgres /data
--\! sudo chmod 700 /data
--\! sudo mkdir -p /data/${DATABASE_NAME}_dat
--\! sudo chown postgres:postgres /data/${DATABASE_NAME}_dat
--\! sudo chmod 700 /data/${DATABASE_NAME}_dat
create tablespace ${DATABASE_NAME}_dat owner ${SUPERUSER_NAME} location '/data/${DATABASE_NAME}_dat';
/* --------------------------------------------------+
| create DATABASE |
+-------------------------------------------------- */
create database ${DATABASE_NAME} owner ${SUPERUSER_NAME} tablespace ${DATABASE_NAME}_dat ;
__EOF__
echo DONE CREATE DATABASE database=${DATABASE_NAME} owner=${SUPERUSER_NAME} tablespace=${DATABASE_NAME}_dat ;
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U ${SUPERUSER_NAME} -d ${DATABASE_NAME} << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data/${STAGE}${sc}_tbl
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_tbl
--\! sudo chmod 700 /data/${STAGE}${sc}_tbl
--\! sudo mkdir -p /data/${STAGE}${sc}_idx
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_idx
--\! sudo chmod 700 /data/${STAGE}${sc}_idx
create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_tbl';
create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_idx';
/* --------------------------------------------------+
| create ROLE & SCHEMA |
+-------------------------------------------------- */
create user ${STAGE}${sc} with password 'casio00' ;
create schema ${sc} authorization ${STAGE}${sc};
__EOF__
echo "DONE USER=${STAGE}${sc}"
echo create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_tbl";
echo create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_idx";
done
show_postgres
}
drop(){
echo "データベース（${DATABASE_NAME})を全て削除します。"
echo "他（dbeaver等)で接続している場合は、切断してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
drop schema ${sc};
drop user ${STAGE}${sc};
drop tablespace ${STAGE}${sc}_tbl;
drop tablespace ${STAGE}${sc}_idx;
__EOF__
done
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c postgres postgres
drop database ${DATABASE_NAME};
drop tablespace ${DATABASE_NAME}_dat;
drop user ${SUPERUSER_NAME};
__EOF__
}
show_postgres(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\echo データベース一覧
select * from pg_database;
\echo 表領域一覧
select * from pg_tablespace;
\echo ユーザ一覧
select * from pg_user;
__EOF__
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
showtable(){
sc=arcsdbss
echo "psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c '\dt'"
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c "\dt"
}
psql(){
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
show(){
sc=arcsdbcb
cd $sc
cdir=`pwd`
echo POSTGRES_HOST=192.168.210.201
echo SUPERUSER_NAME=${STAGE}admin
echo DATABASE_NAME=${STAGE}arcsdbms
echo time ora2pg -c ora2pg.conf -t TABLE -N ${STAGE}${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
}
ALL(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
echo time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
echo time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
init(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${STAGE}${sc}/" | sed -e "s/ARCS_PG_DBNAME/${DATABASE_NAME}/"> ora2pg.conf
/usr/bin/diff ../ora2pg.conf.org ora2pg.conf
#time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
#time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
getdll(){
for sc in ${SCHEMA_LISTS}; do
echo start collect table DLL $sc
cd $sc
grep ORACLE_DSN ora2pg.conf | grep -v '#ORACLE_DSN'
cdir=`pwd`
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql -e 'TABLE[smp_*]'
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cp -p ora2pg_TABLE.sql ora2pg_TABLE_org.sql
cat ora2pg_TABLE_org.sql | convert > ora2pg_TABLE.sql
cd ..
done
}
convert(){
sed -e "s/) ;/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_TBL\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_IDX\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_TBL\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_IDX\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\));/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\));/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\)) TABLESPACE \([^;]*\);/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/ALTER TABLE csb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE csb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/ALTER TABLE ysb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE ysb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/verbose numeric/x_verbose numeric/"
}
report(){
for sc in ${SCHEMA_LISTS}; do
echo start create report $sc
cd $sc
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}
putdll(){
echo "インポートしたDLLでテーブルを作成します。"
echo "一時的にスーパユーザになる必要があります。dbeaver等で${STAGE}userをスーパユーザに設定してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
echo start create $sc
cd $sc
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
copy(){
for sc in ${SCHEMA_LISTS}; do
echo start copy data to $sc
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
cd ..
done
}
copy_by_table(){
sc=arcsdbss
for ta in $(cat -); do
echo start copy data to $ta
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
cd ..
done
}
clean(){
for sc in ${SCHEMA_LISTS}; do
rm -rf ${sc}
done
echo "ディクトリを削除しました（${SCHEMA_LISTS})"
}
diff(){
/usr/bin/diff -l /etc/ora2pg/ora2pg.conf.dist ora2pg.conf.org
}
save(){
tar cvzf arcsdbms.tar ${SCHEMA_LISTS}
}
#----------------------------------------
# 汎用関数
#----------------------------------------
yes_no(){
echo -n "よろしいですか？ [y/N]:"
if tty -s; then
read ANS
else
echo TTY端末からの起動で無いため、強制的に「y」を入力します。
ANS=Yes
fi
case $ANS in
[Yy]* )
#echo "Yes"
return 0
;;
* )
#echo "No"
return 1
;;
esac
}
main $@
select current_database();select current_user; 現在のユーザ
select * from pg_user; ユーザ一覧
psql -h 10.0.18.101 -d arcsdbms -U arcsdbcb -f ora2pg_TABLE.sql
psql -h 10.0.18.101 -d arcsdbms -U arcsdbcb -f ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t TABLE -b ~/arcsdbcb/ -o ora2pg_TABLE.sql
$ time ora2pg -c ora2pg.conf -t PARTITION -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t TABLESPACE -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_TABLESPACE.sql
$ time ora2pg -c ora2pg.conf -t PROCEDURE -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_PROCEDURE.sql
$ time ora2pg -c ora2pg.conf -t INSERT -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_INSERT.sql
$ time ora2pg -c ora2pg.conf -t GRANT -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_GRANT.sql
$ time ora2pg -c ora2pg.conf -t VIEW -N arcsdcb -b ~/arcsdbcb/ -o ora2pg_VIEW.sql
$ time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -o ora2pg_COPY.sql
※ 表領域設定の為、一時的にスーパユーザにする。
付与する ALTER ROLE ARCSDBCB WITH SUPERUSER;
剥奪する ALTER ROLE ARCSDBCB WITH NOSUPERUSER;
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS -f ora2pg_PARTITION.sql
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccd002]' -e 'PARTITION[sys_*]'
※ 正規表現＊が動作しない。
※ for パスワード自動入力
$ vi ~/.pgpass <<__EOF__
192.168.210.201:5432:postgres:postgres:Noxxxxx77%
192.168.210.201:5432:ARCSDBMS:admin:caxxx00
__EOF__
$ chmod 0600 ~/.pgpass
SCHEMA_LISTS="ARCSDBCB ARCSDBCO ARCSDBCS ARCSDBPA ARCSDBSS ARCSDBTM ARCSDBTP DRCSDBCB TRCSDBCB VRCSDBCB XRCSDBCB SGNGDBMC SGNGDBMS"
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_TABLE.sql
psql -h 192.168.210.201 -p 5432 -U ${sc} -d ARCSDBMS -f ora2pg_PARTITION.sql
cd ..
done
【例】
$ time ora2pg -c ora2pg.conf -t TABLE -N arcsdbss -b ~/arcsdbss/ -o ora2pg_TABLE.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========================>] 373/373 tables (100.0%) end of scanning.
[========================>] 373/373 tables (100.0%) end of table export.
real 13m3.017s
user 0m3.534s
sys 0m0.556s
$ time ora2pg -c ora2pg.conf -t PARTITION -N arscdbss -b ~/arcsdbss/ -o ora2pg_PARTITION.sql
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========> ] 25667/71283 partitions (36.0%) end of output.
real 14m20.962s
user 0m8.570s
sys 0m1.826s
※ ora2pg.conf 内の
$ time psql -h 192.168.210.201 -p 5432 -U admin -d ARCSDBMS -f ora2pg_TABLE.sql
$ time psql -h 192.168.210.201 -p 5432 -U admin -d ARCSDBMS -f ora2pg_PARTITION.sql
FATAL: ERROR: new row for relation "ccd002_part100_subpart1" violates partition constraint
DETAIL: Failing row contains (201512, 101, 1360, 0002, 0003, 10001000003000000100, 売上集計管理サービス, 1, 5400, 5400, 400, 201512, 20160106125511, 0, null, null, null, 7716610000552, 002001).
CONTEXT: COPY ccd002_part100_subpart1, line 1: "201512 101 1360 0002 0003 10001000003000000100 売上集計管理サービス 1 5400 5400 400 201512..."
DBI::db=HASH(0x3b50558)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /home/takahab/perl5/lib/perl5/Ora2Pg.pm line 14637.
Aborting export...
→メインテーブルコピー後に、重複してサブパーティションをコピーした為、
（サブパーティションは外す）
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccp001]' -e 'table[sys_subp*]'
→正規表現が効かない！！
psql -h 192.168.210.201 -p 5432 -U arcsdbcb -d ARCSDBMS
\COPY test_table (id, food_name) FROM '/xxxxx/test_table.csv' WITH CSV HEADER;
--------------------------------------------------------------------------------------------------
【実行結果例】
$ time ora2pg -c ora2pg.conf -t COPY -b ~/arcsdbcb/ -a 'table[ccp020]'
WARNING: Enforcing KEEP_PKEY_NAMES to 1 as USE_TABLESPACE is enabled.
[========================>] 1/1 tables (100.0%) end of scanning.
SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;
[========================>] 7584704/7584704 rows (100.0%) Table CCP020 (2785 recs/sec)
[========================>] 7584704/7584704 total rows (100.0%) - (2723 sec., avg: 2785 recs/sec).
[========================>] 7584704/7584704 rows (100.0%) on total estimated data (2723 sec., avg: 2785 recs/sec)
real 46m11.963s
user 16m58.208s
sys 0m10.782s
---------------------------------------------------------------------------------------------------
スキーマ定義のexport
$ cd migration/test_project
$ ./export_schema
$ psql -h 192.168.210.201 -U postgres
----------------------------------------------------------------------------------------------------
【移行オブジェクト】
| 移行対象オブジェクト | 指定するキーワード | 説明 | ||||||
|---|---|---|---|---|---|---|---|---|
| テーブル | TABLE | Export tables, constraints, indexes, ... | ||||||
| パッケージ | PACKAGE | Export packages | ||||||
| データ | INSERT | Export data from table as INSERT statement | $ time ora2pg -c ora2pg_cb.conf -t PARTITION -N arcsdbcb -b ~/Database/arcsdbcb/ -o ora2pg_PARTITION.sql | [2024-01-10 20:12:17] [========> ] 3546/9850 partitions (36.0%) end of output. | real 6m49.824s | user 0m0.601s | sys 0m0.246s | |
| データ | COPY | Export data from table as COPY statement | ||||||
| ビュー | VIEW | Export views | ||||||
| 権限 | GRANT | Export grants | ||||||
| シーケンス | SEQUENCE | Export sequences | ||||||
| トリガ | TRIGGER | Export triggers | ||||||
| ユーザ関数 | FUNCTION | Export functions | ||||||
| プロシージャ | PROCEDURE | Export procedures | ||||||
| 表領域 | TABLESPACE | Export tablespace (PostgreSQL >= 8 only) | ||||||
| データ型 | TYPE | Export user defined Oracle types | ||||||
| パーティション | PARTITION | Export range or list partition (PostgreSQL >= v8.4) | ||||||
| 外部表 | FDW | Export table as foreign data wrapper tables | ||||||
| マテリアライズドビュー | MVIEW | Export materialized view as snapshot refresh view | ||||||
| SQL文 | QUERY | Convert Oracle SQL queries from a file. | ||||||
| XMLテンプレート | KETTLE | Generate XML ktr template files to be used by Kettle. | ||||||
| シノニム | SYNONYM | (説明なし) | ||||||
| データベースリンク | DBLINK | (説明なし) |
【データの変換】
| 属性 | Oracleのデータ型 | ora2pgでの変換後のデータ型 | AWS-SCTでの変換後のデータ型 | 備考 |
|---|---|---|---|---|
| 文字列 | CHAR(n) | char(n) | character(n) | PostgreSQLのnは文字数 |
| NCHAR(n) | char(n) | character(n) | ||
| VARCHAR2(n) | varchar(n) | character varying(n) | PostgreSQLのnは文字数 | |
| NVARCHAR2(n) | varchar(n) | character varying(n) | ||
| CLOB | text | text | ||
| LONG | text | text | ||
| 数値 | NUMBER | bigint | double | |
| NUMBER(n) | smallint | numeric(n,0) | n=1～4 | |
| NUMBER(n) | integer | numeric(n,0) | n=5～9 | |
| NUMBER(n) | bigint | numeric(n,0) | n=10～19 | |
| NUMBER(n) | decimal | numeric(n,0) | n=20～38 | |
| NUMBER(n,m) | real | numeric(n,m) | n=2～6 | |
| NUMBER(n,m) | double precision | numeric(n,m) | n=7～15 | |
| NUMBER(n,m) | decimal | numeric(n,m) | n=16～38 | |
| 日付 | DATE | timestamp | timestamp | |
| TIMESTAMP | timestamp | timestamp | ||
| バイナリ | BLOB | bytea | bytea | |
| RAW | bytea | bytea | ||
| その他 | ROWID | oid | character(255) |
# 定義
#----------------------------------------
STAGE=test_
POSTGRES_HOST=192.168.210.201
SUPERUSER_NAME=${STAGE}admin
DATABASE_NAME=${STAGE}arcsdbms
#SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs arcsdbco arcsdbtm arcsdbtp vrcsdbcb xrcsdbcb"
#SCHEMA_LISTS="xrcsdbcb vrcsdbcb"
#SCHEMA_LISTS="arcsdbcb"
SCHEMA_LISTS="arcsdbcb arcsdbss arcsdbpa arcsdbcs vrcsdbcb xrcsdbcb"
TABLE_LISTS="ssa000 ssa001"
#----------------------------------------
# Main ルーチン
#----------------------------------------
main(){
case $1 in
new )
new
;;
drop )
drop
;;
all )
clean
init
getdll
report
;;
init )
init
;;
getdll )
getdll
;;
putdll )
putdll
;;
report )
report
;;
copy )
copy
;;
copytable )
if [ -p /dev/stdin ]; then
cat -
else
echo $@
fi | copy_by_table
;;
save )
save
;;
clean )
clean
;;
diff )
diff
;;
show )
show
;;
showtable )
showtable
;;
psql )
psql
;;
*)
echo "usage: ./run clean # スキーマワークディレクトリの削除"
echo "usage: ./run drop # 表領域＆データベース＆スキーマ＆ユーザの削除"
echo "usage: ./run new # 表領域＆データベース＆スキーマ＆ユーザの作成"
echo "usage: ./run init # ora2pg.conf ファイルの初期化とスキーマワークディレクトリの作成"
echo "usage: ./run getdll # OracleDBからDLL取得"
echo "usage: ./run putdll # PostgresDBにDLLi登録"
echo "usage: ./run report # 変換レポートの作成"
echo "usage: ./run copy # oracleDBらデータをexportしてposgreDBにimport"
echo "usage: ./run all (clean/init/getdll/report) # /clean/init/getdll/reportを一括実行"
;;
esac
}
new(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
/* --------------------------------------------------+
| create ROLE |
+-------------------------------------------------- */
create user ${SUPERUSER_NAME} with password 'casio00' superuser;
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data
--\! sudo chown postgres:postgres /data
--\! sudo chmod 700 /data
--\! sudo mkdir -p /data/${DATABASE_NAME}_dat
--\! sudo chown postgres:postgres /data/${DATABASE_NAME}_dat
--\! sudo chmod 700 /data/${DATABASE_NAME}_dat
create tablespace ${DATABASE_NAME}_dat owner ${SUPERUSER_NAME} location '/data/${DATABASE_NAME}_dat';
/* --------------------------------------------------+
| create DATABASE |
+-------------------------------------------------- */
create database ${DATABASE_NAME} owner ${SUPERUSER_NAME} tablespace ${DATABASE_NAME}_dat ;
__EOF__
echo DONE CREATE DATABASE database=${DATABASE_NAME} owner=${SUPERUSER_NAME} tablespace=${DATABASE_NAME}_dat ;
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U ${SUPERUSER_NAME} -d ${DATABASE_NAME} << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
/* --------------------------------------------------+
| create TABLE_SPACE ディレクトはサーバ側に作成 |
+-------------------------------------------------- */
--\! sudo mkdir -p /data/${STAGE}${sc}_tbl
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_tbl
--\! sudo chmod 700 /data/${STAGE}${sc}_tbl
--\! sudo mkdir -p /data/${STAGE}${sc}_idx
--\! sudo chown postgres:postgres /data/${STAGE}${sc}_idx
--\! sudo chmod 700 /data/${STAGE}${sc}_idx
create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_tbl';
create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location '/data/${STAGE}${sc}_idx';
/* --------------------------------------------------+
| create ROLE & SCHEMA |
+-------------------------------------------------- */
create user ${STAGE}${sc} with password 'casio00' ;
create schema ${sc} authorization ${STAGE}${sc};
__EOF__
echo "DONE USER=${STAGE}${sc}"
echo create tablespace ${STAGE}${sc}_tbl owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_tbl";
echo create tablespace ${STAGE}${sc}_idx owner ${SUPERUSER_NAME} location "/data/${STAGE}${sc}_idx";
done
show_postgres
}
drop(){
echo "データベース（${DATABASE_NAME})を全て削除します。"
echo "他（dbeaver等)で接続している場合は、切断してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c ${DATABASE_NAME} ${SUPERUSER_NAME}
drop schema ${sc};
drop user ${STAGE}${sc};
drop tablespace ${STAGE}${sc}_tbl;
drop tablespace ${STAGE}${sc}_idx;
__EOF__
done
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\c postgres postgres
drop database ${DATABASE_NAME};
drop tablespace ${DATABASE_NAME}_dat;
drop user ${SUPERUSER_NAME};
__EOF__
}
show_postgres(){
psql -h ${POSTGRES_HOST} -p 5432 -U postgres << __EOF__
\echo データベース一覧
select * from pg_database;
\echo 表領域一覧
select * from pg_tablespace;
\echo ユーザ一覧
select * from pg_user;
__EOF__
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
showtable(){
sc=arcsdbss
echo "psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c '\dt'"
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -c "\dt"
}
psql(){
echo psql -h ${POSTGRES_HOST} -p 5432 -U postgres
}
show(){
sc=arcsdbcb
cd $sc
cdir=`pwd`
echo POSTGRES_HOST=192.168.210.201
echo SUPERUSER_NAME=${STAGE}admin
echo DATABASE_NAME=${STAGE}arcsdbms
echo time ora2pg -c ora2pg.conf -t TABLE -N ${STAGE}${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
}
ALL(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${sc}/" > ora2pg.conf
echo time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
echo time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
echo time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
init(){
for sc in ${SCHEMA_LISTS}; do
if [ ! -d $sc ]; then
mkdir $sc
fi
echo start init $sc
cd $sc
cdir=`pwd`
cat ../ora2pg.conf.org | sed -e "s/ARCS_ORACLE_USER/${sc}/" | sed -e "s/ARCS_SCHEMA_NAME/${sc}/" | sed -e "s/ARCS_PG_USER/${STAGE}${sc}/" | sed -e "s/ARCS_PG_DBNAME/${DATABASE_NAME}/"> ora2pg.conf
/usr/bin/diff ../ora2pg.conf.org ora2pg.conf
#time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql
#time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
#psql -h ${POSTGRES_HOST} -p 5432 -U ${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
getdll(){
for sc in ${SCHEMA_LISTS}; do
echo start collect table DLL $sc
cd $sc
grep ORACLE_DSN ora2pg.conf | grep -v '#ORACLE_DSN'
cdir=`pwd`
time ora2pg -c ora2pg.conf -t TABLE -N ${sc} -b ${cdir} -o ora2pg_TABLE.sql -e 'TABLE[smp_*]'
time ora2pg -c ora2pg.conf -t PARTITION -N ${sc} -b ${cdir} -o ora2pg_PARTITION.sql
#time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cp -p ora2pg_TABLE.sql ora2pg_TABLE_org.sql
cat ora2pg_TABLE_org.sql | convert > ora2pg_TABLE.sql
cd ..
done
}
convert(){
sed -e "s/) ;/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_TBL\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/) TABLESPACE \([^_]*_IDX\);/) TABLESPACE ${STAGE}${sc}_tbl;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_TBL\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/USING INDEX TABLESPACE \([^_]*_IDX\);/USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\));/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/PRIMARY KEY (\1) USING INDEX TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\));/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/CREATE INDEX \([^ ]*\) ON \([^ ]*\) (\([^)]*\)) TABLESPACE \([^;]*\);/CREATE INDEX \1 ON \2 (\3) TABLESPACE ${STAGE}${sc}_idx;/" \
| sed -e "s/ALTER TABLE csb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE csb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/ALTER TABLE ysb008 ADD CONSTRAINT \([^ ]*\) PRIMARY KEY (\([^)]*\)) USING INDEX TABLESPACE \([^;]*\);/ALTER TABLE ysb008 ADD CONSTRAINT \1_primary PRIMARY KEY (\2) USING INDEX TABLESPACE \3;/" \
| sed -e "s/verbose numeric/x_verbose numeric/"
}
report(){
for sc in ${SCHEMA_LISTS}; do
echo start create report $sc
cd $sc
time ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html > ora2pg_REPORT.html
cd ..
done
}
putdll(){
echo "インポートしたDLLでテーブルを作成します。"
echo "一時的にスーパユーザになる必要があります。dbeaver等で${STAGE}userをスーパユーザに設定してください。"
yes_no
if [ $? -eq 0 ]; then
echo "削除を開始します。"
else
echo "中止しました。"
return 1
fi
for sc in ${SCHEMA_LISTS}; do
echo start create $sc
cd $sc
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_TABLE.sql
psql -h ${POSTGRES_HOST} -p 5432 -U ${STAGE}${sc} -d ${DATABASE_NAME} -f ora2pg_PARTITION.sql
cd ..
done
}
copy(){
for sc in ${SCHEMA_LISTS}; do
echo start copy data to $sc
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -e 'table[sys_subp*]'
cd ..
done
}
copy_by_table(){
sc=arcsdbss
for ta in $(cat -); do
echo start copy data to $ta
cd $sc
cdir=`pwd`
echo ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
time ora2pg -c ora2pg.conf -t COPY -N ${sc} -b ${cdir} -a "table[${ta}]" -e "table[sys_subp*]"
cd ..
done
}
clean(){
for sc in ${SCHEMA_LISTS}; do
rm -rf ${sc}
done
echo "ディクトリを削除しました（${SCHEMA_LISTS})"
}
diff(){
/usr/bin/diff -l /etc/ora2pg/ora2pg.conf.dist ora2pg.conf.org
}
save(){
tar cvzf arcsdbms.tar ${SCHEMA_LISTS}
}
#----------------------------------------
# 汎用関数
#----------------------------------------
yes_no(){
echo -n "よろしいですか？ [y/N]:"
if tty -s; then
read ANS
else
echo TTY端末からの起動で無いため、強制的に「y」を入力します。
ANS=Yes
fi
case $ANS in
[Yy]* )
#echo "Yes"
return 0
;;
* )
#echo "No"
return 1
;;
esac
}
main $@
