01_テーブル数の比較
No

概要

対象

ユーザ

1

前提条件

--

2

Oracle Databaseのテーブル数確 DBサーバ
認(Oracle Database)3

PostgreSQLのテーブル数確認

コマンド
下記の条件を満たしていること
・ Oracle DatabaseがPostgreSQLへのデータ移行が完了していること

確認 （確認コマンド等）-# sqlplus [スキーマ名]/[パスワード]@[ホスト名]:[ポート番号]/[SID]-

備考

SQL> SELECT COUNT(*) FROM user_tables;
root

COUNT(*)---------44$ psql -d infoscoop

DBサーバ(PostgreSQL)

postgres-=# SELECT COUNT(*) FROM pg_stat_user_tables;count------44(1 row)

ページ 1

02_データ件数の比較
No1

2

3

4

概要

対象

ユーザ

前提条件

--

Oracle Databaseの各テーブルの DBサーバ
データ件数(Oracle Database)

PostgreSQLの各テーブルのデー
タ件数(1/2)

PostgreSQLの各テーブルのデー
タ件数(2/2)

DBサーバ(PostgreSQL)

DBサーバ(PostgreSQL)

root

postgrespostgresコマンド
下記の条件を満たしていること
・ Oracle DatabaseがPostgreSQLへのデータ移行が完了していること

確認 （確認コマンド等）-# sqlplus [スキーマ名]/[パスワード]@[ホスト名]:[ポート番号]/[SID]

左記SQLの実行結果

SQL> SET HEADING OFF
SQL> SET COLSEP ','SQL>selecttable_name,to_number(
extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) countfrom user_tables
WHERE TABLE_NAME NOT LIKE 'BIN$%'and (iot_type != 'IOT_OVERFLOW' or iot_type is null)order by table_name;

DATABASECHANGELOGDATABASECHANGELOGLOCK
IS_ACCESSLOGSIS_ACCOUNTS
IS_ADMINROLES
（中略）

,,,,,

21
1
1
113

IS_WIDGETCONFS
IS_WIDGETS

,,

12
15

$ psql -d infoscoop

左記SQLの実行結果

備考

44行が選択されました。

=# WITH temp AS
table_count_query(-----------------------------------------------------------------------------------------------------SELECT----------------'SELECT '''||relname||''' AS table_name,count(*) AS count FROM '||relname as query,
SELECT 'databasechangelog' AS table_name,count(*) AS count FROM databasechangelog UNION all
CASE WHEN lead(relname) over (ORDER BY relname) IS NOT NULL then ' UNION all' ELSE '' END AS connect SELECT 'databasechangeloglock' AS table_name,count(*) AS count FROM databasechangeloglock UNION all_querySELECT 'is_accesslogs' AS table_name,count(*) AS count FROM is_accesslogs UNION all
FROM
SELECT 'is_accounts' AS table_name,count(*) AS count FROM is_accounts UNION all
pg_stat_user_tables
SELECT 'is_adminroles' AS table_name,count(*) AS count FROM is_adminroles UNION all)[省略] SELECT 'is_systemmessages' AS table_name,count(*) AS count FROM is_systemmessages UNION all
SELECT
SELECT 'is_tab_admins' AS table_name,count(*) AS count FROM is_tab_admins UNION all
query || connect_query AS table_count_query
SELECT 'is_tablayouts' AS table_name,count(*) AS count FROM is_tablayouts UNION all
FROM
SELECT 'is_tabs' AS table_name,count(*) AS count FROM is_tabs UNION all
Temp;
SELECT 'is_userprefs' AS table_name,count(*) AS count FROM is_userprefs UNION all
SELECT 'is_widgetconfs' AS table_name,count(*) AS count FROM is_widgetconfs UNION all
SELECT 'is_widgets' AS table_name,count(*) AS count FROM is_widgets(44 rows)$ psql -d infoscoop
左記SQLの実行結果(項番3で得たSQLを実行します。)
table_name| count=# SELECT 'databasechangelog' AS table_name,count(*) AS count FROM databasechangelog UNION all--------------------------------+-------# SELECT 'databasechangeloglock' AS table_name,count(*) AS count FROM databasechangeloglock UNION al databasechangelog|21l
databasechangeloglock|1-# SELECT 'is_accesslogs' AS table_name,count(*) AS count FROM is_accesslogs UNION all
is_accesslogs|
3-# SELECT 'is_accounts' AS table_name,count(*) AS count FROM is_accounts UNION all
is_accounts|
11-# SELECT 'is_adminroles' AS table_name,count(*) AS count FROM is_adminroles UNION all
is_adminroles|
1-# SELECT 'is_authcredentials' AS table_name,count(*) AS count FROM is_authcredentials UNION all[省略]-# SELECT 'is_caches' AS table_name,count(*) AS count FROM is_caches UNION all
is_widgetconfs|
12-# SELECT 'is_forbiddenurls' AS table_name,count(*) AS count FROM is_forbiddenurls UNION all
is_widgets|
4-# SELECT 'is_gadget_icons' AS table_name,count(*) AS count FROM is_gadget_icons UNION all(44 rows)

本手順で得たSQLの結果を、表計算ソフトに貼り付け件数比較を行います。

ページ 2

03_データ内容の比較
No
1

概要

対象ユーザ

前提条件

--

2

テーブル一覧取得スクリプト作
成

DBサーバ(Oracle Database)

root

3

テーブル一覧取得

DBサーバ(Oracle Database)

root

4

テーブル一覧の転送

DBサーバ(Oracle Database)

root

コマンド
下記の条件を満たしていること
・ Oracle DatabaseからPostgreSQLへのデータ移行が完了していること

確認 （確認コマンド等）-# cd /tmp
# vi table_list.sql-備考[下記を追記]SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET TERMOUT OFF
SET TRIM ON
SET TRIMSPOOL ON
SPOOL /tmp/table_list.txt
SELECT TABLE_NAME FROM USER_TABLES;SPOOL OFFEXIT

# scp table_list.txt [移行先DBサーバIPアドレス]:/tmp/# ls -l /tmp/infoscoop_tables_list.txt-rw-r--r-- 1 root root 682 3月 2 21:05 2015 /tmp/infoscoop_tables_list.txt-# vi /tmp/ora2csv.sh-# sqlplus [スキーマ名]/[パスワード]@[ホスト名]:[ポート番号]/[SID] @table_list.sql

実行権限が足りない場合は以下コマンドを実行する。
SQL> grant execute on DBMS_CRYPTO to [スキーマ名];[下記を追記]#!/bin/bash
mkdir -p /tmp/ora_table_define/mkdir -p /tmp/ora_csv/
READFILE=/tmp/table_list.txt#カラム名、定義確認
while read line; do
sqlplus -s system/[パスワード]@[ホスト名]:[ポート番号]/[SID] <<EOF
SPOOL "/tmp/ora_table_define/$line.define.csv"
SET LINESIZE 32767
SET TRIMSPOOL ON
SET PAGESIZE 0
SET HEADING OFF
SET NEWPAGE NONESET FEEDBACK OFF
SELECT COLUMN_NAME||','|| DATA_TYPE FROM DBA_TAB_COLUMNS where TABLE_NAME = '$line';
exit;
EOF
done < $READFILE

5

テーブル毎にCSVファイルを取得 DBサーバするスクリプトの作成(Oracle Database)root#CSV出力クエリ生成
rm -f /tmp/createcsv.sql
echo "SET LINESIZE 32767" > /tmp/createcsv.sql
echo "SET TRIMSPOOL ON" >> /tmp/createcsv.sql
echo "SET PAGESIZE 0" >> /tmp/createcsv.sql
echo "SET HEADING OFF" >> /tmp/createcsv.sql
echo "SET NEWPAGE NONE" >> /tmp/createcsv.sql
echo "SET FEEDBACK OFF" >> /tmp/createcsv.sql
echo "SET LONG 1000000" >> /tmp/createcsv.sql
echo "SET LONGC 1000000" >> /tmp/createcsv.sql
while read line; do
flag=0sql="SELECT "for def in `cat /tmp/ora_table_define/$line.define.csv`
do
if test $flag -ne 0 ; thensql="${sql} ||"\'","\'"|| "fiarr=( `echo $def | tr -s ',' ' '`)if test "${arr[1]}" = "BLOB" || test "${arr[1]}" = "CLOB" ; thensql="${sql} rawtohex(DBMS_CRYPTO.Hash(\"${arr[0]}\",2))"
else
sql="${sql} \"${arr[0]}\""
fiflag=1
done
sql="${sql} FROM $line;"echo "$line" >> /tmp/output_table_list.txt
echo "spool /tmp/ora_csv/"$line".csv" >> /tmp/createcsv.sql
echo $sql >> /tmp/createcsv.sql
done < $READFILE#CSV出力クエリ実行
sqlplus -s [infoScoop用のスキーマ]/[パスワード]@[ホスト名]:[ポート番号]/[SID] <<EOF@/tmp/createcsv.sql
exit;
EOF
Exit 06

スクリプトの実行

DBサーバ(Oracle Database)root# sh ora2csv.sh
上記スクリプトにてエラーが発生した場合は以下対応※ infoScoopではIS_TABLAYOUTS, IS_USERPREFSでエラーとなる※ 作成後にCSV格納ディレクトリに移動
1. DBMS_CRYPTO.Hash関数で処理するカラムにNULL値が含まれる場合（IS_TABLAYTOUS, IS_USERPREFS）
DBMS_CRYPTO.Hashは値がNULLの場合はエラーとなる。
NULL文字を一旦別の文字（以下例では”NULL”）に置き換える。
2. CLOB以外のテキスト型（VARCHAR2等）にも改行文字タブ文字が含まれる場合（IS_USERPREFS）
改行文字・タブ文字を含むVARCHAR2型もハッシュ値に変換する。

7

エラー発生箇所の個別出力

DBサーバ(Oracle Database)root# ls /tmp/ora_csv/（出力されたCSVファイル）# ./test.sh
0,0,C21F969B5F03D33D43E04F8F136E7682,defaultRole,OrganizationPrincipal,default,55FD0A9B2509049650C41
72C3360E3B2,E4F31D5048605EDAF51BF34E66338576,-,0,,commandbar,0,C21F969B5F03D33D43E04F8F136E7682,defaultRole,OrganizationPrincipal,default,7486CC07B96D
827C4F2C3FB7A38DA269,947D39FF109605E47DA9B89080C41B43,-,0,,header,0,C21F969B5F03D33D43E04F8F136E7682,defaultRole,OrganizationPrincipal,default,18C86DEEAC4F1D4A
CC95C055C3A7E6CA,6C3E226B4D4795D518AB341B0824EC29,0,0,,1,url,BDF23D948F7C4829F1CD3CB86A056ACD,6C3E226B4D4795D518AB341B0824EC29（以下略）

上記修正点を踏まえたスクリプト例を以下に記載[スクリプト例]#!/bin/bash
sqlplus -s scott/Hoge_1234@localhost:1521/XE <<EOF
SET HEADING OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET FEEDBACK OFF
SET ECHO OFF
SET LINESIZE 32767
SPOOL "/tmp/IS_TABLAYOUTS.csv"
SELECT "TABID" ||','|| "ROLEORDER" ||','|| rawtohex(DBMS_CRYPTO.Hash("ROLE",2)) ||','|| "ROLENAME" ||','|| "PR
INCIPALTYPE" ||','|| "DEFAULTUID" ||','|| rawtohex(DBMS_CRYPTO.Hash("WIDGETS",2)) ||','|| rawtohex(DBMS_CRYPT
O.Hash(NVL("LAYOUT", 'NULL'),2)) ||','|| "WIDGETSLASTMODIFIED" ||','|| "TEMP" ||','|| "WORKINGUID" ||','|| "TEMPLASTMODIFIED" FROM IS_TABLAYOUTS;SPOOL OFFSPOOL "/tmp/IS_USERPREFS.csv"
SELECT "FK_WIDGET_ID" ||','|| "NAME" ||','|| rawtohex(DBMS_CRYPTO.Hash(UTL_I18N.STRING_TO_RAW("VALUE", 'AL32UTF8'),2)) ||','|| rawtohex(DBMS_CRYPTO.Hash(NVL("LONG_VALUE",'NULL'),2)) FROM IS_USERPREFS;SPOOL OFFEXIT
EOFExit 08

CSVファイルの転送

DBサーバ(Oracle Database)root# zip -r ora.zip ora_csv# scp ora.zip [移行先DBサーバIPアドレス]:/tmp/-ページ 3

03_データ内容の比較# cd /tmp# vi pg2csv.sh-[下記を追記]#!/bin/bash
user=$1
dbname=$2
rm -rf /tmp/pg_table_define/rm -rf /tmp/pg_csv/
mkdir -p /tmp/pg_table_define/mkdir -p /tmp/pg_csv/
READFILE=/tmp/table_list.txt#カラム名、定義情報出力
while read line; do
psql -U $user $dbname -c "COPY (SELECT a.attname, format_type(a.atttypid, a.atttypmod) FROM
pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHEREa.attrelid = '$line'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum) TO'/tmp/pg_table_define/$line.define.csv' WITH DELIMITER ','"
done < $READFILE

9

テーブル毎にCSVファイルを取得 DBサーバするスクリプトの作成(PostgreSQL)

root#CSV出力クエリ生成
rm -f /tmp/createcsv.sql
while read line; doflag=0
sql="COPY (SELECT "for def in `cat /tmp/pg_table_define/$line.define.csv | sed -e 's/ //g'`doif test $flag -ne 0 ; thensql="${sql} ,"fiarr=( `echo $def | tr -s ',' ' '`)#IFS=','#set -- $def
if test "${arr[1]}" = "bytea" || test "${arr[1]}" = "text" ; then
#if test "$2" = "bytea" ; thensql="${sql} upper(md5(\"${arr[0]}\"))"
elif test "${arr[1]}" = "timestampwithouttimezone" ; thensql="${sql} to_char(\"${arr[0]}\", 'YY-MM-DD HH24:MI:SS.US')"else
sql="${sql} \"${arr[0]}\""#sql="${sql} \"$1\""
fiflag=1
done
sql="${sql} FROM $line) TO '/tmp/pg_csv/$line.csv' WITH DELIMITER ',';"echo $sql >> /tmp/createcsv.sql
done < $READFILE#CSV出力クエリ実行
psql -U $user $dbname -c "\i /tmp/createcsv.sql"
Exit 010 スクリプトの実行

DBサーバ(PostgreSQL)

11 エラーテーブルの個別出力

DBサーバ(PostgreSQL)

postgrespostgres# chmod 777 pg2csv.sh# su – postgres$ cd /tmp$ ./pg2csv.sh [ユーザ名] [データベース名]$ exit# ls /tmp/pg_csv/（出力されたCSVファイル）

Oracleで出力方法を変え個別に出力したテーブルについては形式を揃えるためにPostgreSQLでも個別に出力※ PostgreSQLのmd5関数はNULL値であってもエラーにならないためIS_TABLAYOUTSは出力しなくともよい。
※ 出力後にCSV格納ディレクトリに移動$ psql iscoop -c "COPY (SELECT "fk_widget_id" , "name" , upper(md5("value")) ,upper(md5("long_value")) FROM IS_USERPREFS) TO '/tmp/IS_USERPREFS.csv' WITH DELIMITER ',';"COPY 68$ psql iscoop -c "COPY (SELECT "fk_widget_id" , "name" , upper(md5("value")) ,upper(md5("long_value")) FROM IS_USERPREFS) TO '/tmp/IS_USERPREFS.csv' WITH DELIMITER ',';"# vi diff_table.sh-

CSV整形&比較用スクリプトの作
12 成

DBサーバ
(PostgreSQL)

root[下記を追記]#!/bin/bash#usage: ./diff_table.sh [oracle_csv_list_path] [postgres_csv_list_path]#< oracle#> postgres
oracle_path=$1
postgres_path=$2
READFILE=/tmp/check_table_list.txt
mkdir -p /tmp/diff_result
mkdir -p /tmp/$oracle_path.sort
mkdir -p /tmp/$postgres_path.sort
while read line; do#ソート処理sort $oracle_path/$line.csv > /tmp/$oracle_path.sort/$line.csv
sort $postgres_path/$line.csv > /tmp/$postgres_path.sort/$line.csv#'NULL'のハッシュ値を''に置換
sed -i -e 's/6C3E226B4D4795D518AB341B0824EC29//g' /tmp/$oracle_path.sort/$line.csv#メタ記号置換処理
sed -i -e 's/^ *//g' /tmp/$postgres_path.sort/$line.csv
sed -i -e 's/ *$//g' /tmp/$postgres_path.sort/$line.csv
sed -i -e 's/\\\\/\\/g' /tmp/$postgres_path.sort/$line.csv
sed -i -e 's/\\,/,/g' /tmp/$postgres_path.sort/$line.csv
sed -i -e 's/\"/"/g' /tmp/$postgres_path.sort/$line.csv
sed -i -e 's/\\N//g' /tmp/$postgres_path.sort/$line.csv#差分記録
diff /tmp/$oracle_path.sort/$line.csv /tmp/$postgres_path.sort/$line.csv > /tmp/diff_result/$line.txt
done < $READFILErm -rf /tmp/$oracle_path.sort
rm -rf /tmp/$postgres_path.sort
Exit 013 CSV整形&比較用スクリプトの実
行

DBサーバ(PostgreSQL)

root# chmod 744 diff_table.sh# unzip ora.zip# ./diff_table ora_csv pg_csv# ls /tmp/diff_result/（出力された差分ファイル）# ls -s /tmp/diff_result/（差分ファイルのサイズが全て0になることを確認）# ls -s /tmp/diff_result/
合計 0
0 DATABASECHANGELOG.txt(以下略）

14 差分ファイルの確認

DBサーバ(PostgreSQL)

root

0 IS_OAUTHPROVIDER_CLIENT.txt

ページ 4