RDS for Oracle マテリアライズドビューを使用してデータを移行をしてみた

LimChaejeong

2024.05.16

本ブログではOracle DBをマテリアライズドビューを利用してデータを移行する方法をまとめました。
移行方法は下記のページを参考しています。

アジェンダ

検証環境の作成とサンプルデータ作成
Oracleのユーザーアカウントの作成と権限付与
データベースリンクを作成
マテリアライズドビューのログを作成
移行先のDBにマテリアライズドビューを作成
移行されたデータの確認
(2024.5.21 追加) 移行後、移行元にデータの更新が起こった場合に追加移行
不要になったリソースの片付け

1. 検証環境の作成とサンプルデータ作成

検証環境

今回検証する環境は下記になります。

移行元：既存のOracle DB
移行先：新しく作成したDBで、移行元のデータをこのDBに移したい

AWSでの設定

移行元と移行先が両方AWS環境に位置している場合（別のVPCに存在する場合）

VPC1、VPC2が通信できるようにピアリング接続の設定
移行元と移行先のセキュリティグループの設定

移行元と移行先が両方AWS環境に位置している場合（同じのVPCに存在する場合）

移行元と移行先のセキュリティグループの設定

移行元がオンプレミスの場合

移行元と移行先のセキュリティグループの設定

サンプルデータの作成

既存のDBにサンプルのデータを入れます。

コマンドに必要なパラメータは下記になります。

[table_name] : 検証で使用するテーブル、テーブル名は任意

実行対象：移行元

#サンプルデーブル作成
SQL> 
CREATE TABLE [table_name](COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_COLB ON [table_name](COLB); 

#サンプルデータ作成
SQL> 
DECLARE
TYPE tbl_ins IS TABLE OF [table_name]%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..14000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..14000 INSERT INTO [table_name] VALUES w_ins(i);
   COMMIT;
END;
/

#データ数の確認
SQL> 
select count(*) from [table_name];
 
#  COUNT(*)
#----------
#     14000

移行元にサンプルデータが作成されたので、このデータを移行先に移行してみましょう。

2. Oracleのユーザーアカウントの作成と権限付与

実行対象：移行元、移行先

まずは、移行元・移行先に同じパスワードで認証できるユーザーアカウントを作成します。
コマンドに必要なパラメータは下記になります。

[user_name] : 任意でなんでも大丈夫です
[password] : Oracleユーザーのパスワードにはルールがありますので、設定の際に注意しましょう。
- パスワードの長さは6文字以上
- 5種類のうち3種類以上の文字が含まれていること
  - 英大文字(A - Z)
  - 英小文字(a - z)
  - 10進数の数字(0 - 9)
  - 英数字以外の文字(!、$、#、^など)
  - Unicode文字
- 詳しい内容は下記を参考

ユーザーを作成して権限を付与するコマンドです。

# 移行用のユーザー作成
CREATE USER [user_name] IDENTIFIED BY "[password]"
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;
# > User [user_name]は作成されました。

# データベースに接続してSESSIONを作成できる権限付与
GRANT CREATE SESSION TO [user_name];
# > Grantが正常に実行されました。

# すべてのテーブルをSELECTできる権限付与
GRANT SELECT ANY TABLE TO [user_name];
# > Grantが正常に実行されました。

# データ・ディクショナリをすべてSELECTできる権限付与
GRANT SELECT ANY DICTIONARY TO [user_name]; 
# > Grantが正常に実行されました。

移行元と移行先の両方に同じ名とパスワードのユーザーを作成する必要があります。
作成して権限まで設定したら、次に行きます。

3. データベースリンクを作成

実行対象：移行先

次には、移行先の RDS for Oracle インスタンスから移行元のインスタンスへのデータベースリンクを作成します。
コマンドに必要なパラメータは下記になります。

[user_name] : 2番で作成したユーザー名
[password] : 2番で作成したユーザーのパスワード
[移行元のhost] : データベースのホスト名またはIPアドレス
[移行元のlistener-port] : データベースのリスナーポート番号 (ex.1521)
[移行元のsource-db-sid] : データベースのSID

# データベースリンクを作成
CREATE DATABASE LINK remote_site
  CONNECT TO [user_name] IDENTIFIED BY "[password]"
  USING '(description=(address=(protocol=tcp) (host=[移行元のhost]) 
    (port=[移行元のlistener-port])) (connect_data=(sid=[移行元のsource-db-sid])))';
# > Database link REMOTE_SITEは作成されました。

リンクが作成されると、確認するためにデータベースリンクのテストを行います。

# 作成したデータベースリンクのテスト
SELECT * FROM V$INSTANCE@remote_site;

下記のように何かが表示されたら、移行元のデータベースとリンク完了になります。

4. マテリアライズドビューのログを作成

実行対象：移行元

データベースリンクを作成したので、次には移行元にマテリアライズドビューのログを作成します。

コマンドに必要なパラメータは下記になります。

[table_name] : 移行するテーブル名
[pk_name] : PRIMARY KEY (主キー)、任意でOK (ex.pk_temp_0)

# テーブルにPRIMARY KEY(主キー)制約を追加
ALTER TABLE [table_name] ADD CONSTRAINT [pk_name] PRIMARY KEY (COLA) USING INDEX;
# > Table [table_name]が変更されました。

# マテリアライズドビューのログを作成
CREATE MATERIALIZED VIEW LOG ON [table_name];
# > Materialized view log [table_name]は作成されました。

ログが作成されたら、下記のようにテーブルが追加されます。

ここまで問題なければ次の手順で移行先のデータベースにデータを移行します。

5. 移行先のDBにマテリアライズドビューを作成

実行対象：移行先

準備も終わったので、実際にデータを移行してみます。

コマンドに必要なパラメータは下記になります。

[管理者ユーザー] : 管理者ユーザー（SYSやSYSTEMユーザー、AWS）
[table_name] : 移行するテーブル名

# マテリアライズドビュー作成
CREATE MATERIALIZED VIEW [table_name]
  BUILD IMMEDIATE REFRESH FAST 
  AS (SELECT * 
      FROM   [管理者ユーザー].[table_name]@remote_site);
# > Materialized view [table_name]は作成されました。

# マテリアライズドビューのリフレッシュ（fはマテリアライズドビューを最新状態に更新するためのパラメータ）
EXEC DBMS_MVIEW.REFRESH('[table_name]', 'f');
# > PL/SQLプロシージャが正常に完了しました。

これでデータの移行は完了になります。

6. 移行されたデータの確認

実行対象：移行先

最後に移行したデータに問題ないか確認をしましょう。　
確認は問題が発生しないように確実にしたほうがいいですが、ブログではサンプルデータを使用しているので簡単にデータの数だけ確認します。

#データ数の確認
SQL> 
select count(*) from [table_name];
 
#  COUNT(*)
#----------
#     14000

移行元のデータが問題なく移行されました。

7. (2024.5.21 追加) 移行後、移行元にデータの更新が起こった場合に追加移行

上の手順で移行元から移行先にデータの移行は完了しています。
ただし、移行が一回で終わらせることではなく、移行元のデータが更新されて追加移行が必要の場合にはどうやってすればいいでしょうか。

マテリアライズドビューはリアルタイムでデータが同期化する機能はないので、追加移行をするためには手動で移行する必要があります。
実際に確認してみましょう。

まずは移行元にサンプルデータを追加します。
実行対象：移行元

#追加サンプルデータの作成
DECLARE
TYPE tbl_ins IS TABLE OF [table_name]%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100 LOOP 
   w_ins(i).COLA :=14000+i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..100 INSERT INTO [table_name] VALUES w_ins(i);
   COMMIT;
END;

移行元のデータを確認してみると、データの数が100個追加されたことが見えます。
実行対象：移行元

#データ数の確認（移行元）
SQL> 
select count(*) from [table_name];
 
#  COUNT(*)
#----------
#     14100

移行先も確認してみます。
移行先のデータの数は最初に移行した数のそのままで100個が追加されてないです。
実行対象：移行先

#データ数の確認（移行先）
SQL> 
select count(*) from [table_name];
 
#  COUNT(*)
#----------
#     14000

次には、移行元のデータを移行先に追加で移行してみます。
上の[5.マテリアライズドビューを作成]手順で使用したマテリアライズドビューを最新状態に更新するコマンドを実行します。
実行対象：移行先

# マテリアライズドビューのリフレッシュ（fはマテリアライズドビューを最新状態に更新するためのパラメータ）
EXEC DBMS_MVIEW.REFRESH('[table_name]', 'f');
# > PL/SQLプロシージャが正常に完了しました。

最新状態に更新したので、もう一回データを確認してみましょう。
移行先のデータが100個追加されました！
実行対象：移行先

#データ数の確認（移行元）
SQL> 
select count(*) from [table_name];
 
#  COUNT(*)
#----------
#     14100

8. 不要になったリソースの片付け

移行を完了して追加の移行もすべて完了すると、必要なくなったリソースは削除します。　
マテリアライズドビューやOracleユーザーを含めてデータベースリンクも必要ないと削除しましょう。

#　マテリアライズドビューの削除
DROP MATERIALIZED VIEW [table_name] PRESERVE TABLE;

# Oracleユーザーの削除
drop user [user_name];

# データベースリンクの削除
# データベースリンクがつながっている状態で削除ができない場合には「exit;」をしてリングを切ってください
DROP DATABASE LINK remote_site;