OracleDBのマテリアライズド・ビューによるデータ移行を試す

概要

DBLINKとマテリアライズド・ビューを組み合わせたデータ同期について検証するために、DockerでOracleDBを2台立て、OracleDB間でのデータ移行とリフレッシュを試してみます。

実行環境

Dockerイメージの利用には自身でのDockerビルドが必要になりますが割愛します。

docker-composeを利用してDock上でOracleDBを2台立ち上げます。

ディレクトリoradata1、oradata2の所有者のユーザIDは54321にchownで変更しています。

oracle1が移行元、oracle2が移行先になります。

version: '3'
services:

  oracle1:
    image: oracle/database:19.3.0-ee
    environment:
      - ORACLE_SID=ORCLCDB1
      - ORACLE_PWD=p@ssw0rd
      - ORACLE_CHARACTERSET=AL32UTF8
    ports:
      - 11521:1521
      - 15500:5500
    volumes:
      - ./oradata1:/opt/oracle/oradata

  oracle2:
    image: oracle/database:19.3.0-ee
    environment:
      - ORACLE_SID=ORCLCDB2
      - ORACLE_PWD=p@ssw0rd
      - ORACLE_CHARACTERSET=AL32UTF8
    ports:
      - 21521:1521
      - 25500:5500
    volumes:
      - ./oradata2:/opt/oracle/oradata

作業概要

以下のシナリオで作業を進めます。

1. （共通）ユーザ、スキーマ、表領域を用意
2. （Oracle1）テーブルの作成とデータを挿入
3. （Oracle2）データベースリンクの設定
4. （Oracle1）マテリアライズド・ビューログを作成
5. （Oracle2）マテリアライズド・ビューの作成
6. （Oracle1）データの追加挿入確認

作業詳細

（共通）ユーザ、スキーマ、表領域を用意

（Oracle1)まずは、テーブルを作成する下準備をします。CDBに接続します。

sqlplus / as sysdba;

PDBに接続して表領域作成します。

ALTER SESSION SET CONTAINER = ORCLPDB1;

CREATE TABLESPACE TABLE_SPACE_MONSTER_GAME /*表領域名*/
DATAFILE '/opt/oracle/oradata/ORCLCDB1/ORCLPDB1/monster_game_1.dbf'
SIZE 100M /*サイズ*/
AUTOEXTEND OFF /*自動拡張*/
;

CREATE TEMPORARY TABLESPACE TEMP_SPACE_MONSTER_GAME　/*表領域名*/
TEMPFILE '/opt/oracle/oradata/ORCLCDB1/ORCLPDB1/temp_monster_game_2.dbf'
SIZE 100M /*サイズ*/
;

ユーザ作成して権限付与します。

CREATE USER MONSTER_GAME
IDENTIFIED BY password123
DEFAULT TABLESPACE TABLE_SPACE_MONSTER_GAME
TEMPORARY TABLESPACE TEMP_SPACE_MONSTER_GAME
QUOTA 100M ON TABLE_SPACE_MONSTER_GAME
PROFILE DEFAULT
ACCOUNT UNLOCK
;

GRANT CREATE SESSION, 
 CREATE TABLE,
 CREATE VIEW,
 CREATE SEQUENCE,
 CREATE TRIGGER,
 CREATE SYNONYM,
 UNLIMITED TABLESPACE TO MONSTER_GAME;

作成したユーザでPDBにログインします。

sqlplus MONSTER_GAME@//localhost:1521/ORCLPDB1

同様のことをOracle2でも実施します。

sqlplus / as sysdba;

ALTER SESSION SET CONTAINER = ORCLPDB1;

CREATE TABLESPACE TABLE_SPACE_MONSTER_GAME /*表領域名*/
DATAFILE '/opt/oracle/oradata/ORCLCDB2/ORCLPDB1/monster_game_1.dbf'
SIZE 100M /*サイズ*/
AUTOEXTEND OFF /*自動拡張*/
;

CREATE TEMPORARY TABLESPACE TEMP_SPACE_MONSTER_GAME　/*表領域名*/
TEMPFILE '/opt/oracle/oradata/ORCLCDB2/ORCLPDB1/temp_monster_game_2.dbf'
SIZE 100M /*サイズ*/
;

CREATE USER MONSTER_GAME
IDENTIFIED BY password123
DEFAULT TABLESPACE TABLE_SPACE_MONSTER_GAME
TEMPORARY TABLESPACE TEMP_SPACE_MONSTER_GAME
QUOTA 100M ON TABLE_SPACE_MONSTER_GAME
PROFILE DEFAULT
ACCOUNT UNLOCK
;

GRANT CREATE SESSION, 
 CREATE TABLE,
 CREATE VIEW,
 CREATE SEQUENCE,
 CREATE TRIGGER,
 CREATE SYNONYM,
 UNLIMITED TABLESPACE TO MONSTER_GAME;

（Oracle1）テーブルの作成とデータを挿入

同期したいテーブルを作成し適当にデータを入れておきます。

CREATE TABLE PLAYER(
  NAME VARCHAR2(100) NOT NULL,
  HP NUMBER(4, 0) NOT NULL,
  STR NUMBER(3,0) NOT NULL,
  DEF NUMBER(3,0) NOT NULL
);

INSERT INTO PLAYER VALUES ('MAGICIAN', 10, 10, 30);
INSERT INTO PLAYER VALUES ('THIEF', 20, 40, 30);
INSERT INTO PLAYER VALUES ('ASSASSIN', 20, 60, 30);
INSERT INTO PLAYER VALUES ('FIGHTER', 40, 30, 30);

（Oracle2）データベースリンクの設定

Oracle2にSYSでログインしてOracle2のMONSTER_GAMEに権限を追加します。

GRANT CREATE DATABASE LINK TO MONSTER_GAME;

Oracle2のMONSTER_GAMEにログインしてOracle1のMONSTER_GAMEに対してデータベースリンクを設定します。

CREATE DATABASE LINK DBLINK_PLAYER
CONNECT TO MONSTER_GAME IDENTIFIED BY password123 
USING '//oracle1:1521/ORCLPDB1';

SELECTしてみるとOracle1から無事データを取得できていることを確認できました。

SQL> COL NAME FORMAT A10
SQL> SELECT * FROM PLAYER@DBLINK_PLAYER;

NAME               HP        STR        DEF
---------- ---------- ---------- ----------
MAGICIAN           10         10         30
THIEF              20         40         30
ASSASSIN           20         60         30
FIGHTER            40         30         30

（Oracle1）マテリアライズド・ビュー・ログを作成

リフレッシュに必要なマテリアライズド・ビュー・ログを作成します。

CREATE MATERIALIZED VIEW LOG ON PLAYER WITH ROWID;

（Oracle2）マテリアライズド・ビューの作成

Oracle2にSYSでログインしてOracle2のMONSTER_GAMEに権限を追加します。

GRANT CREATE MATERIALIZED VIEW TO MONSTER_GAME;

Oracle2のMONSTER_GAMEでマテリアライズド・ビューを作成します。

CREATE MATERIALIZED VIEW PLAYER
AS SELECT * FROM PLAYER@DBLINK_PLAYER;

SELECT文でOracle1のテーブルと同期できていることを確認できました。

SQL> COL NAME FORMAT A10
SQL> SELECT * FROM PLAYER;

NAME               HP        STR        DEF
---------- ---------- ---------- ----------
MAGICIAN           10         10         30
THIEF              20         40         30
ASSASSIN           20         60         30
FIGHTER            40         30         30

（Oracle1）データの追加挿入確認

Oracle1のMONSTER_GAMEでデータ挿入します。

INSERT INTO PLAYER VALUES ('KNIGHT', 80, 30, 20);
INSERT INTO PLAYER VALUES ('BISHOP', 30, 30, 50);

Oracle2のMONSTER_GAMEでリフレッシュします。

EXECUTE DBMS_MVIEW.REFRESH('PLAYER', 'c');

Oracle2のMONSTER_GAMEでSELECT文を実施し、同期できていることを確認できました。

SQL>  SELECT * FROM PLAYER;

NAME               HP        STR        DEF
---------- ---------- ---------- ----------
MAGICIAN           10         10         30
THIEF              20         40         30
ASSASSIN           20         60         30
FIGHTER            40         30         30
KNIGHT             80         30         20
BISHOP             30         30         50

6 rows selected.

後片付け

（Oracle2）DBLINK削除

DROP DATABASE LINK DBLINK_PLAYER;

（共通）スキーマと表領域削除

DROP USER MONSTER_GAME CASCADE;
DROP TABLESPACE TABLE_SPACE_MONSTER_GAME INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE TEMP_SPACE_MONSTER_GAME INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

感想

実際に試してみることでどちらのサーバで何をする必要があるかがわかりました。