RDS for Oracle のハンズオン (Immersion Day) をやってみて気づきを整理してみた

# はじめに

RDS for Oracle の細かい部分がよくわかってなかったこともあり、英語のハンズオンをやってみました。基礎から応用編まで詳細な使い方が紹介されているので、興味がある方はやってみても良いと思います。

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US



上記のハンズオンをやってみて、個人的に気になった部分を備忘録的に紹介します。



# Creating an RDS Oracle Instance

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab1

RDS で Oracle Database を構成する章

　

Create Database

![image-20230103142047952.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f2338b89-4786-c94f-922d-ee51b6086e3a.png)

　

個人の学習目的で利用するため、Enterprise Edition の BYOL を選択

![image-20230103143047164.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/da430175-28c2-80e7-e98a-22cdf3b77c55.png)

　

instanceの名前、管理者の名前、パスワードを指定

![image-20230103143722678.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/ea5e2736-b0f3-aad9-377e-ddeb3a1acc13.png)

　

Instance class の選択

![image-20230103144115519.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2f7029d4-b203-2623-bb0e-51af5eefefbd.png)

　

メモリが多いインスタンスが推奨されている

![image-20230103143902237.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3f38c678-0886-f416-c095-2c21a89e3c50.png)

　

CPU やスレッドを調整する機能が備わっている。今回は無効で設定そする

![image-20230103144256173.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d0b96630-1038-fd0a-5300-0bfcc6746796.png)

　

Storage を指定可能

- gp3, gp2, io1, magnetic から指定可能

![image-20230103144541572.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/c7aa068f-6dd1-03bb-7107-1a6d027e3171.png)

　

Multi-AZ は、作成時は無効化

![image-20230103144647716.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e8232b4a-f428-dcc9-5a63-54e37b6e6ff5.png)

　

適当にネットワークを指定

![image-20230103144854515.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/0df50c35-e959-c401-1b26-825bb00be115.png)

　

パスワード認証のみ。Kerberos 認証もできるらしい。

![image-20230103144950216.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f5df8d19-0c90-dfe2-6e71-87c5d318b1f9.png)

　

Initial database name を指定して、それ以外はデフォルト

![image-20230103145849155.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e1e543c9-9443-d0d0-3615-a7e9c8554b4b.png)

　

Create database を押す

![image-20230103145915728.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e2a80317-b419-0412-befe-9491f8c11acb.png)

　

Creating になった

![image-20230103150109130.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2025c6e5-eb9b-73de-63f2-4f12f756a8f7.png)



# Client Connections

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab2

作成した DB Instance に接続する章

　

ハンズオンでは、Cloud9 から接続する方法になっているが、Cloud9 インスタンスではなく EC2 インスタンスでやってみる。

Amazon Linux 2 のインスタンスを作成

![image-20230103152144137.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/85b0a37e-8342-085d-ba59-11c85fe6da74.png)

　

SSH で接続して、Oracle Client とそれに付随する SQL*Plus をインストール

```
sudo su -

cd /etc/yum.repos.d
wget http://yum.oracle.com/public-yum-ol7.repo
wget http://yum.oracle.com/RPM-GPG-KEY-oracle-ol7
rpm --import RPM-GPG-KEY-oracle-ol7
yum install -y yum-utils
yum-config-manager --enable ol7_oracle_instantclient
yum install -y oracle-instantclient18.3-sqlplus

exit

echo "export ORACLE_HOME=/usr/lib/oracle/18.3/client64/" >> ~/.bashrc
echo "export LD_LIBRARY_PATH=/usr/lib/oracle/18.3/client64/lib/" >> ~/.bashrc
echo "export PATH=$PATH:/usr/lib/oracle/18.3/client64/bin" >> ~/.bashrc
. ~/.bashrc
```

　

RDS for Oracle の Endpoint を確認します

```
mydatabase.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com
```

![image-20230103153654158.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d3db6344-7c2f-0f58-28ce-e2e37b8cf1e8.png)

　

日本語を扱えるように NLS 環境変数を指定

```
export NLS_LANG=Japanese_Japan.AL32UTF8
```

　

SQL * Plus で接続します

```
sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
```

　

実行例 : パスワードを入力して接続

```
[ec2-user@ip-192-168-0-168 ~]$ sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 3 06:39:56 2023
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue Jan 03 2023 06:39:45 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL>
```



# Read Replicas (Oracle Data guard)

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab3

Oracle Database を選択して、Create replica を押す

![image-20230103155220334.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/fbba5491-a77b-12cc-88e2-ed3fe7476a4e.png)

　

Read Replica 側でも、Multi-AZ の構成が出来そう

![image-20230103155457003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/baeb35da-df8d-7e81-ebb9-8810da790c5d.png)

　

以下はデフォルト

![image-20230103155508191.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a5bfb7b2-38b8-2420-e08c-d780b951bfa6.png)

　

DB instance identifier を指定

![image-20230103155532541.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/62e46e2c-972a-c490-b5e0-21d9ef0c0b6b.png)

　

このまま Create

![image-20230103155613749.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/9c0a5b54-2693-eee5-3fdf-9cc95877ef21.png)

　

Replica が Creating になった

![image-20230103155735091.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/5ef611e8-455e-6c86-34b9-ab7814bd8b20.png)

　

Replica 側の Endpoint も確認できる

```
mydatabase-replica.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com
```

![image-20230103161353594.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/6c4b2f8c-6c0d-87e6-c112-01862093a6eb.png)



## Source 側

日本語を扱えるように NLS 環境変数を指定

```
export NLS_LANG=Japanese_Japan.AL32UTF8
```

　

SQL * Plus で接続します

```
sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
```

　

OPEN_MODE が READ WRITE になっている

```
SQL> SELECT name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCL      READ WRITE           PRIMARY
```

　

適当にテーブルを作成

```
CREATE TABLE emp 
(
  empno VARCHAR2(10) NOT NULL,
  empname VARCHAR2(50),
  gender_f NUMBER(1,0)
);
```

　

データを入れる

```
INSERT INTO emp VALUES (1, '鈴木', 1);
INSERT INTO emp VALUES (2, '佐藤', 2);
INSERT INTO emp VALUES (3, '田中', 2);
COMMIT;
```

　

確認

```
SQL> SELECT * FROM emp;

EMPNO      EMPNAME                                              GENDER_F
---------- -------------------------------------------------- ----------
1          鈴木                                                        1
2          佐藤                                                        2
3          田中                                                        2
```



## Replica 側

SQL * Plus で接続します

```
sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase-replica.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
```

　

実行例

```
[ec2-user@ip-192-168-0-168 ~]$ sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase-replica.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))

SQL*Plus: Release 18.0.0.0.0 - Production on 火 1月 3 07:14:25 2023
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

パスワードを入力してください:
最終正常ログイン時間: 火 1月  03 2023 07:07:44 +00:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
に接続されました。
SQL>
```

　

OPEN_MODE が、READ ONLY になっている。Read Replica 側なので、想定通り。

```
SQL> SELECT name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCL      READ ONLY WITH APPLY PHYSICAL STANDBY
```

　

Source 側で格納したデータもちゃんと見える

```
SQL> SELECT * FROM emp;

EMPNO      EMPNAME                                              GENDER_F
---------- -------------------------------------------------- ----------
1          鈴木                                                        1
2          佐藤                                                        2
3          田中                                                        2
```

　

Read Replica 側での Insert は失敗する

```
SQL> INSERT INTO emp VALUES (4, '佐々木', 1);
INSERT INTO emp VALUES (4, '佐々木', 1)
            *
行1でエラーが発生しました。:
ORA-16000:
データベースまたはプラガブル・データベースは読取り専用アクセスでオープンされてい
ます
```

　

Replica Lag を確認可能

![image-20230103162032220.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b4fae5d3-3a8a-f0a7-b235-bd7e183767e9.png)

　　

Read Replica の Switch Over も可能

![image-20230103162309876.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/7da09db0-0355-42a5-3cce-d22ac43c3c0b.png)



# High Availability (Multi-AZ)

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab4

Modify を押す

![image-20230103163019954.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/c6240f06-ed08-46d7-23d0-824be58a98a0.png)

　　

Multi-AZ を有効化する

![image-20230103163231341.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/89d60b33-1893-eded-1546-0a96f9136477.png)

　

Continue

![image-20230103163244410.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/52639163-d14a-3882-46c6-f907cdf4f4ca.png)

　

即変更

![image-20230103163307563.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3add3f21-68d7-0d3d-727a-b97955a73535.png)

　

Modifying に変わる

![image-20230103163346039.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/178bce22-9d50-c928-0423-396c760baf91.png)

　

Available になりました

![image-20230103165552548.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d4dbabd3-e18a-1d1f-7370-80fe4462a63f.png)

　

Multi-AZ が有効になっている

![image-20230103165811324.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/65235ced-0f21-47dc-b463-3e9ba1160f2b.png)

　

Reboot するときに、Multi-AZ の対抗側にフェールオーバーする機能がある

![image-20230103191244375.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/94f4ce2a-838f-7c21-ce35-387df84a7f42.png)



![image-20230103191300668.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a07d7a37-f23d-9229-ea88-24d5126fb428.png)



# Backup & Restore

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab5

- Point-In-Time-Restore 機能を使うことで、任意の時間のデータを使って、新しい DB Instance を復元することが可能。通常の自動バックアップでは、1日1回バックアップといった方法となる。Point-In-Time-Restore では、より細かい粒度でデータを復元が可能。通常は、最短で 5 分前のデータの復元が出来る。
- Snapshot のクロスリージョンバックアップ機能がある
![image-20230103192347650.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/118091ae-cfc2-3dfe-0e3b-f381e0298829.png)



# Performance Monitoring

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab6

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Oracle.Options.OEMAgent.html

RDS for Oracle でも、Enterprise Manager の配下で管理が可能

![image-20230103193001515.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f34c9d98-6465-27df-2628-a3a331244b1d.png)

　

Add option を押す

![image-20230103193146239.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/bf9be32b-a0ad-503d-fdba-75ff7f4eb1cc.png)

　

OEM_AGENT の選択肢があり、連携が可能

![image-20230103193320816.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/6025dcf7-e38d-3343-38fc-60dfad90f8f5.png)



# Data Pump with S3 Integration

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab9

Data Pump などで取得したダンプファイルを RDS for Oracle に Import 可能



## S3 bucket にダンプファイルのアップロード

ハンズオンで公開されているダンプファイルをダウンロード
https://solution-references-us-east-1.s3.amazonaws.com/rdspg/HR.dmp

　

S3 Bucket にアップロードした

![image-20230103200427666.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/5ec2f55b-94e1-d152-27c8-3d09949c34a6.png)



## RDS に IAM Role の付与

詳細画面を開く

![image-20230103200647971.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/1fb782d2-1331-3443-0725-5b984518f3f4.png)

　

IAM Role と、S3_INTEGRATION を選択して、Add role を押す

![image-20230103203107100.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f4c1e136-349b-3711-c259-6783c2ed3051.png)



## Option Group の変更

![image-20230103203203547.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d30e193a-e278-d435-c244-25ea0805caaa.png)

　

Add option

![image-20230103203233218.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/538a9e10-8735-f9b1-691f-c3ba9095d4e5.png)



## DB Instance に紐づける Option Group の変更

DB Instance の Modify から、Option Group を変更

![image-20230103203414533.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/355be958-980b-3d82-3271-5d5db2159bad.png)



## RDS のインスタンスに、S3 上のダンプファイルをダウンロード

SQL * Plus で接続

```
[ec2-user@ip-192-168-0-168 ~]$ sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydatabase.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 3 11:38:55 2023
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Tue Jan 03 2023 07:07:44 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0

SQL>
```

　

MY_DUMP_DIR の作成

```
exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'MY_DUMP_DIR');
```

　

確認

```
SQL> select * from table(rdsadmin.rds_file_util.listdir(p_directory => 'MY_DUMP_DIR'));

FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
01/
directory        4096 03-NOV-22


SQL>
```

　

S3 にアップロードした Data Pump のダンプファイルを、RDS のインスタンスにdownloadする

```
select rdsadmin.rdsadmin_s3_tasks.download_from_s3(
	p_bucket_name=> 'rds-bucket-test01',
	p_directory_name => 'MY_DUMP_DIR')
as TASK_ID from dual; 
```

　

実行例

```
SQL> select rdsadmin.rdsadmin_s3_tasks.download_from_s3(
        p_bucket_nam  2  e=> 'rds-bucket-test01',
        p_directory_name => 'MY_DUMP_DIR')
as TASK_ID from dual;  3    4

TASK_ID
--------------------------------------------------------------------------------
1672746304588-643
```

　

Task の実行結果を確認

```
select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1672746304588-643.log'));
```

　

実行例 : HP.dmp をダウンロードしたログが表示されている

 ```
SQL> select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1672746304588-643.log'));

TEXT
--------------------------------------------------------------------------------
2023-01-03 11:45:04.679 UTC [INFO ] This task is about to list the Amazon S3 obj
ects for AWS Region ap-northeast-1, bucket name rds-bucket-test01, and prefix .

2023-01-03 11:45:04.742 UTC [INFO ] The task successfully listed the Amazon S3 o
bjects for AWS Region ap-northeast-1, bucket name rds-bucket-test01, and prefix
.

2023-01-03 11:45:04.757 UTC [INFO ] This task is about to download the Amazon S3
 object or objects in /rdsdbdata/userdirs/01 from bucket name rds-bucket-test01
and key HR.dmp.


TEXT
--------------------------------------------------------------------------------
2023-01-03 11:45:04.922 UTC [INFO ] The task successfully downloaded the Amazon
S3 object or objects from bucket name rds-bucket-test01 with key HR.dmp to the l
ocation /rdsdbdata/userdirs/01.

2023-01-03 11:45:04.923 UTC [INFO ] The task finished successfully.
 ```

　

ファイル一覧を見ると、正常に HR.dmp がダウンロードされている

```
SQL> select * from table(rdsadmin.rds_file_util.listdir(p_directory=>'MY_DUMP_DIR'));

FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
01/
directory        4096 03-JAN-23

HR.dmp
file           720896 03-JAN-23
```



## ダンプファイルを Import

User アカウントを作成

```
create user hr identified by hr;
grant create session, resource to hr;
alter user hr quota 100M on users;
```

　

以下コマンドを実行

```
DECLARE 
      hdnl NUMBER; 
      BEGIN 
      hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null); 
      DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'HR.dmp', directory => 'MY_DUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); 
      DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'HR_imp.log', directory => 'MY_DUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); 
      DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''HR'')'); DBMS_DATAPUMP.START_JOB(hdnl); END;
      / 
```

　

実行例

```
PL/SQL procedure successfully completed.
```

　

正常に Import が終了

```
SQL> select table_name from dba_tables where owner = 'HR';

TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
JOB_HISTORY
DEPARTMENTS
JOBS
EMPLOYEES

7 rows selected.
```

　

Import したテーブルの中身を確認

```
SQL> select first_name, last_name, job_id, salary from hr.employees where rownum <10;

FIRST_NAME           LAST_NAME                 JOB_ID         SALARY
-------------------- ------------------------- ---------- ----------
Donald               OConnell                  SH_CLERK         2600
Douglas              Grant                     SH_CLERK         2600
Jennifer             Whalen                    AD_ASST          4400
Michael              Hartstein                 MK_MAN          13000
Pat                  Fay                       MK_REP           6000
Susan                Mavris                    HR_REP           6500
Hermann              Baer                      PR_REP          10000
Shelley              Higgins                   AC_MGR          12008
William              Gietz                     AC_ACCOUNT       8300

9 rows selected.
```



# DBS タスク

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab10



## Log の閲覧

![image-20230103205409973.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/349a8e16-cfee-94ec-56f1-026502c5284b.png)



ログの閲覧やダウンロードが可能

![image-20230103205430534.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/4521da7a-1356-bea0-14ff-94bc0c1e9acc.png)



## RMAN を使ったバックアップ

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/2-foundation/lab10#performing-a-rman-full-database-backup-on-rds-oracle-and-upload-backup-files-to-s3

RDS に備わっているバックアップ機能に加えて、RMAN を使ったバックアップも可能。



# Migrate Oracle to Amazon RDS

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/3-intermediate/lab13

オンプレミスの Oracle Database から、Data Pump と DMS (Database Migration Services) を使って、ダウンタイムの最小限にする移行方法が紹介されている。

- データ移行元 : RDS for Oracle の 19c
- データ移行先 : RDS for Oracle の 21c



## 移行先 : RDS インスタンスの作成

RDS for Oracle を適当に作成。Multitenant Architecture を有効にすると、21c が選択可能となる

![image-20230104151841259.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/99bb3d5e-65d5-4487-08d0-e200ad442468.png)

　

RDS IAM Role の設定

![image-20230104155527688.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b9bf0b1d-e36b-ecad-ffa1-5c269d6c4b12.png)



## 移行元 : テストデータの作成と Archivelog の保持期間を設定

テストデータの作成

```
create user  demo identified by Welcome123;
grant dba to demo;

create table demo.event_history(
event_id number generated always as identity(start with 1 increment by 1),
event_name varchar2(10),
event_date date not null);

alter table demo.event_history add ( constraint eventid_pk primary key (event_id));

insert into demo.event_history (event_name,event_date) select dbms_random.string('x',10), to_date(trunc(dbms_random.value(to_char(DATE '2020-01-01','J'), to_char(DATE '2021-01-01','J'))),'J')
event_date from dual connect by level <=50;
commit;

alter table demo.event_history modify( event_id drop identity);
select * from demo.event_history;
```

　

データの確認結果

```
SQL> select * from demo.event_history;

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
         1 9QSHO2K9J0 08-MAR-20
         2 FL5B0R1LP9 11-OCT-20
         3 W67AJ8L1XH 18-DEC-20
         4 FQEOKRMM8U 16-SEP-20
         5 0FFMQXFV6N 24-MAR-20
         6 G1BQA3IRIG 02-DEC-20
         7 T9Q3NE9GF9 17-MAR-20
         8 CTX8Z3RO6Y 31-DEC-20
         9 RGOS3J435O 23-DEC-20
        10 QJ31ZDV4EZ 14-JAN-20
        11 E9AU2SOQI7 22-MAY-20
```

　

アーカイブログの保持期間が 0 になっている

```
SQL> set serveroutput on
SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
NAME:archivelog retention hours
VALUE:0
DESCRIPTION:ArchiveLog expiration specifies the duration in hours before
archive/redo log files are automatically deleted.

PL/SQL procedure successfully completed.
```

　

24 時間の指定と supplemental logging の有効化

```
exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours', 24);
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD');
exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY');
```

　

24 時間の指定

```
SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
NAME:archivelog retention hours
VALUE:24
DESCRIPTION:ArchiveLog expiration specifies the duration in hours before
archive/redo log files are automatically deleted.
```

　

移行元データベースの SCN (system change number) を表示する。システム内部で利用されるタイムスタンプ的な数字となっており、DMS でレプリケーションする際に利用される。

```
SQL>
select current_scn from v$database;SQL>

CURRENT_SCN
-----------
     601167
```



## 移行元 : Data Pump を使って、S3 にダンプファイルを出力

Data Pump を使って、ローカルにダンプファイルを吐き出す。`demo` スキーマを Data Pump の対象にしている (はず)

```
set scan off
set serveroutput on
set escape off
whenever sqlerror exit 
DECLARE
    h1 number;
    s varchar2(1000):=NULL;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
    success_with_info EXCEPTION;
    PRAGMA EXCEPTION_INIT(success_with_info, -31627);
    flashbackscn number;
begin
    select current_scn into flashbackscn from v$database;
    h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'EXP_DEMO', version => 'COMPATIBLE'); 
    tryGetStatus := 1;
    dbms_datapump.set_parameter(handle => h1, name => 'COMPRESSION', value => 'ALL'); 
    dbms_datapump.set_parallel(handle => h1, degree => 2); 
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype => 3); 
    dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1); 
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''DEMO'')'); 
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT%U-'||to_char(sysdate,'hh24_mi_ss')||'.DMP', directory => 'DATA_PUMP_DIR', filesize => '500M',  filetype => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); 
    dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_SCN', value => flashbackscn);
    dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC'); 
    dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS'); 
    dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); 
    dbms_datapump.detach(handle => h1); 
    errorvarchar := 'NO_ERROR'; 
EXCEPTION
    WHEN OTHERS THEN
    BEGIN 
        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN 
            DBMS_DATAPUMP.DETACH(h1);
        END IF;
    EXCEPTION 
    WHEN OTHERS THEN 
        NULL;
    END;
    RAISE;
END;
/
```

　

Dump ファイルが DB Instance 内に出力されている

```
SQL> select * from table(rdsadmin.rds_file_util.listdir(p_directory=>'DATA_PUMP_DIR'));

FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
datapump/
directory        4096 04-JAN-23

EXPDAT-07_25_57.LOG
file             1233 04-JAN-23

EXPDAT02-07_25_57.DMP
file            77824 04-JAN-23


FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
EXPDAT01-07_25_57.DMP
file            24576 04-JAN-23


```

　

S3 に Dump ファイルをアップロードします

```
select rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name=>'rds-bucket-test01', p_directory_name=>'DATA_PUMP_DIR') as TASK_ID from dual;
```

　

実行例 : TASK_ID が返ってきます

```
SQL> select rdsadmin.rdsadmin_s3_tasks.upload_to_s3(p_bucket_name=>'rds-bucket-test01', p_directory_name=>'DATA_PUMP_DIR') as TASK_ID from dual;

TASK_ID
--------------------------------------------------------------------------------
1672817389382-646

```

　

Bucket にアップロードされました

![image-20230104163046953.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/8e5c4875-0dd5-4b5b-2545-33ae31668379.png)



## 移行先 : S3 からダンプファイルをローカルにdownload

移行先のインスタンスに接続します

```
sqlplus oraadmin@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-target-21c.cwvq7vck29x3.ap-northeast-1.rds.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SID=ORCL)))
```

　

移行先の DB Instance に、S3 からダンプファイルをダウンロードします

```
select rdsadmin.rdsadmin_s3_tasks.download_from_s3(p_bucket_name=> 'rds-bucket-test01', p_directory_name => 'DATA_PUMP_DIR') as TASK_ID from dual; 
```

　

TASK_ID が返ってきます

```
SQL> select rdsadmin.rdsadmin_s3_tasks.download_from_s3(p_bucket_name=> 'rds-bucket-test01', p_directory_name => 'DATA_PUMP_DIR') as TASK_ID from dual;

TASK_ID
--------------------------------------------------------------------------------
1672817790036-29
```

　

S3 からダウンロードするタスクのログを表示

```
select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1672817790036-29.log'));
```

　

実行例 : 末尾の方に successfully と表示されている

```
SQL> select text from table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1672817790036-29.log'));

TEXT
--------------------------------------------------------------------------------
2023-01-04 07:36:30.330 UTC [INFO ] This task is about to list the Amazon S3 obj
ects for AWS Region ap-northeast-1, bucket name rds-bucket-test01, and prefix .

2023-01-04 07:36:30.399 UTC [INFO ] The task successfully listed the Amazon S3 o
bjects for AWS Region ap-northeast-1, bucket name rds-bucket-test01, and prefix
.

2023-01-04 07:36:30.416 UTC [INFO ] This task is about to download the Amazon S3
 object or objects in /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F from
bucket name rds-bucket-test01 and key EXPDAT-07_25_57.LOG.


TEXT
--------------------------------------------------------------------------------
2023-01-04 07:36:30.533 UTC [INFO ] The task successfully downloaded the Amazon
S3 object or objects from bucket name rds-bucket-test01 with key EXPDAT-07_25_57
.LOG to the location /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F.

2023-01-04 07:36:30.533 UTC [INFO ] This task is about to download the Amazon S3
 object or objects in /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F from
bucket name rds-bucket-test01 and key EXPDAT01-07_25_57.DMP.

2023-01-04 07:36:30.646 UTC [INFO ] The task successfully downloaded the Amazon
S3 object or objects from bucket name rds-bucket-test01 with key EXPDAT01-07_25_
57.DMP to the location /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F.

TEXT
--------------------------------------------------------------------------------

2023-01-04 07:36:30.646 UTC [INFO ] This task is about to download the Amazon S3
 object or objects in /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F from
bucket name rds-bucket-test01 and key EXPDAT02-07_25_57.DMP.

2023-01-04 07:36:30.759 UTC [INFO ] The task successfully downloaded the Amazon
S3 object or objects from bucket name rds-bucket-test01 with key EXPDAT02-07_25_
57.DMP to the location /rdsdbdata/datapump/F16B3D273B93159FE0530100007FEA4F.

2023-01-04 07:36:30.760 UTC [INFO ] The task finished successfully.

9 rows selected.
```

　

実際にdownloadされていることがわかります

```
SQL> select * from table(rdsadmin.rds_file_util.listdir(p_directory=>'DATA_PUMP_DIR'));

FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
F16B3D273B93159FE0530100007FEA4F/
directory        4096 04-JAN-23

EXPDAT02-07_25_57.DMP
file            77824 04-JAN-23

EXPDAT-07_25_57.LOG
file             1233 04-JAN-23


FILENAME
--------------------------------------------------------------------------------
TYPE         FILESIZE MTIME
---------- ---------- ---------
EXPDAT01-07_25_57.DMP
file            24576 04-JAN-23


```



## 移行先 : ダンプファイルを import

ファイル名を書き換えて実行

```
create user demo identified by Welcome123;
grant dba to demo;
set scan off
set serveroutput on
set escape off
whenever sqlerror exit
DECLARE
    h1 number;
    s varchar2(1000):=NULL;
    errorvarchar varchar2(100):= 'ERROR';
    tryGetStatus number := 0;
    success_with_info EXCEPTION;
    PRAGMA EXCEPTION_INIT(success_with_info, -31627);
begin
    h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'IMP_DEMO', version => 'COMPATIBLE');
    tryGetStatus := 1;
    dbms_datapump.set_parallel(handle => h1, degree => 2);
    dbms_datapump.add_file(handle => h1, filename => 'IMPDAT-'||to_char(sysdate,'hh24_mi_ss')||'.LOG', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file );
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''DEMO'')');
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT01-07_25_57.DMP', directory => 'DATA_PUMP_DIR',filetype => dbms_datapump.ku$_file_type_dump_file);
    dbms_datapump.add_file(handle => h1, filename => 'EXPDAT02-07_25_57.DMP', directory => 'DATA_PUMP_DIR',filetype => dbms_datapump.ku$_file_type_dump_file);
    DBMS_DATAPUMP.START_JOB(h1);
    errorvarchar := 'NO_ERROR';
EXCEPTION
    WHEN OTHERS THEN
    BEGIN
        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN
            DBMS_DATAPUMP.DETACH(h1);
        END IF;
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
END;
/
```

　

demo スキーマで、データが復元されました

```
select * from demo.event_history;
```

　

実行例

```
SQL> select * from demo.event_history;

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
         1 9QSHO2K9J0 08-MAR-20
         2 FL5B0R1LP9 11-OCT-20
         3 W67AJ8L1XH 18-DEC-20
         4 FQEOKRMM8U 16-SEP-20
         5 0FFMQXFV6N 24-MAR-20
         6 G1BQA3IRIG 02-DEC-20
         7 T9Q3NE9GF9 17-MAR-20
         8 CTX8Z3RO6Y 31-DEC-20
         9 RGOS3J435O 23-DEC-20
        10 QJ31ZDV4EZ 14-JAN-20
        11 E9AU2SOQI7 22-MAY-20

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        12 H1TNSHF4E0 29-SEP-20
        13 FUXLU86O7D 03-APR-20
        14 1BQLRAMBJ5 11-JAN-20
        15 K6VJFIA55E 17-NOV-20
        16 CU528HV3R9 12-SEP-20
        17 8NLXFTZAFK 07-DEC-20
        18 ADD34EB32P 12-FEB-20
        19 7EY2NDEK9D 03-SEP-20
        20 54ACFSPQ2P 01-APR-20
        21 PYAHJKYTVG 02-FEB-20
        22 6P8BTD8O3P 21-JAN-20

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        23 KPSWU4ESCF 30-JAN-20
        24 HVINM691CK 24-MAY-20
        25 DF3QOGTF4A 16-DEC-20
        26 IU4LYDA8YY 15-DEC-20
        27 PDVI2FK8I3 29-JUN-20
        28 OFHCOJJ3AY 07-DEC-20
        29 MGG1XF8ZA9 09-OCT-20
        30 8X659PBJMY 22-MAY-20
        31 Z7T4HGL6VK 27-MAY-20
        32 5FSXAFVI9C 07-JUN-20
        33 VLQ20352OY 20-FEB-20

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        34 FLXIIM4KPF 10-MAR-20
        35 1VHL48LW2U 23-DEC-20
        36 P9T8MI940Z 28-DEC-20
        37 3XBJFZKXGE 20-APR-20
        38 C6UAMZ7WNO 29-MAY-20
        39 9TK1SQ5KOI 12-FEB-20
        40 DEWLP62AC7 27-FEB-20
        41 KKF7UFXSF8 24-OCT-20
        42 NY4AKJ9GKR 16-SEP-20
        43 9RKGO9K3T8 21-DEC-20
        44 O1EGQJKSI5 28-JAN-20

  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        45 HPG5YMN12G 04-DEC-20
        46 414HFIR1EX 08-NOV-20
        47 2EVU4CRLN8 28-MAY-20
        48 QWJ3CBNJQD 31-DEC-20
        49 TJTYHLZQC7 25-JUN-20
        50 CJLHQ8AAH6 30-OCT-20

50 rows selected.
```



## DMS : インスタンスの作成

Create replication instance

![image-20230104184354637.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/47ca45c9-afca-6dff-c593-5ee2aeb2cebb.png)

　

instance名の指定

![image-20230104184530270.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/8d45f9b7-b5f0-5b6b-d73a-b5c58db83db7.png)

　

DMS の Instance Type の選び方の Document がある。基本的にはパフォーマンステストの実施を事前にするのが良いと思う。

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Types.html

　

以下のパラメータを指定

- Instance Class の指定
- DMS の Engine Version の指定
- Multi-AZ の有無を指定

![image-20230104185220365.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/685bb441-c538-92bd-4c16-f7f14e25760a.png)

　

ストレージサイズの指定

![image-20230104185322154.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/fcc465df-9939-de9d-477c-f6a484edd641.png)

　

ネットワーク系の指定

![image-20230104185431988.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/bc29a1a4-b1aa-ef31-982b-930b93ec9663.png)

　

メンテナンス枠の指定をして、Create

![image-20230104185456978.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/8b9d7757-2ef6-67a7-0efc-7d7e424590b6.png)

　

Creating になりました

![image-20230104185546742.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3267b88e-eba3-b264-12c7-4edecfcc7258.png)



## DMS : Endpoint の作成

移行元と移行先の 2 つの Endpoint を作成

![image-20230104191458293.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3ed62a8a-9b83-acdb-cd07-1b29dd631e08.png)

　

移行元の Endpoint を指定

![image-20230104191737815.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/c837d387-6478-cf61-98f5-0619d0a00ded.png)

　

接続の Endpoint や、Username, Password 等を指定

![image-20230104191821614.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d04eb8db-137f-896d-cef1-92d42d6714fa.png)

　

接続テストのための機能がある。このまま Create Endpoint を押す

![image-20230104191922453.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/6f969280-e64d-096e-d703-29262e3dfc8c.png)



　

移行先の Endpoint を指定

- 21c の RDS Instance は、一覧に出てこないので、手動でエンドポイントを指定していく

![image-20230104192117564.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/75b8b1f3-0b31-8007-7312-fa95d01aa16a.png)

　

手動で指定

![image-20230104192213361.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b5de8211-c133-ddb5-77e6-1b35c65c1297.png)

　

Endpoint が 2 つ作成されました

![image-20230104192335253.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e890717d-3de3-e27a-2bac-db5fc9bd0588.png)



## DMS : DMS CDC Task

Create

![image-20230104192416336.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/57c59b6c-1604-2ddf-934e-1d0a5657a36b.png)

　

Data Pump を使って初期データは移行済みなので、**Replicate data changes only** を選ぶ

![image-20230104192533581.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/35466414-3091-e759-0bdf-0786b4acb977.png)

　

SCN (System change number) を控えていたので、その時の番号を入力します。

![image-20230104193020444.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b3aaa253-97e6-16ac-b081-c18580587c6a.png)

　

このあたりはデフォルトのまま

![image-20230104193109252.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/197c9b7f-9139-ac65-3846-f56934321eb7.png)

　

このあたりもデフォルトのまま

![image-20230104193213088.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/96abb2a5-96d9-89b4-2d9e-14915ee62891.png)

　

DEMO Schema をレプリケーションの対象にします

![image-20230104193314467.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a13897a5-df9d-045e-b145-c91f50e2cc49.png)

　

Transformation Rule を指定

![image-20230104193425958.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2106797d-9a27-2664-b00f-92433b0bfc6c.png)

　

Create Task

![image-20230104193447762.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/1d3a4f16-2526-8451-7481-ae0c3e58768b.png)

　

Creating になりました

![image-20230104193512660.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/598716aa-5477-fb68-fb43-f155e899e6a0.png)

　

自動的に Starting に変わります

![image-20230104193639197.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/cf2806d6-4967-a1fa-54b0-ca4107e30a51.png)

　

Status が、Replication ongoing となり、Progress が 100 % です

![image-20230104193830817.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/12b89d71-e971-e672-19f8-3de72a63c49e.png)

　

View Logs を押します

![image-20230104194003522.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e81b9b59-acc6-b430-94ed-56f2c77c0d10.png)

　

SCN が見えた

![image-20230104194055053.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/72b85451-2fab-0f4f-a5f8-191c3111b9fe.png)



## データ同期テスト

データ移行元で Insert を行う

```
insert into demo.event_history(event_id,event_name,event_date) values(51,'demoevent1','04-SEP-21');
insert into demo.event_history(event_id,event_name,event_date) values(52,'demoevent2','03-SEP-21');
commit;
```

　

データの確認

```
select * from demo.event_history;
```

　

Insert がされている

```
  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        45 HPG5YMN12G 04-DEC-20
        46 414HFIR1EX 08-NOV-20
        47 2EVU4CRLN8 28-MAY-20
        48 QWJ3CBNJQD 31-DEC-20
        49 TJTYHLZQC7 25-JUN-20
        50 CJLHQ8AAH6 30-OCT-20
        51 demoevent1 04-SEP-21
        52 demoevent2 03-SEP-21

52 rows selected.

```

　

DMS の Database Migration Task で、実際に数値がカウントされていることがわかる

![image-20230104194354034.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/08136623-f169-2250-6387-48c40494ad54.png)

　

データ移行先で、レプリケーションされていることを確認

```
select * from demo.event_history;
```

　

Insert したデータが同期されている

```
  EVENT_ID EVENT_NAME EVENT_DAT
---------- ---------- ---------
        45 HPG5YMN12G 04-DEC-20
        46 414HFIR1EX 08-NOV-20
        47 2EVU4CRLN8 28-MAY-20
        48 QWJ3CBNJQD 31-DEC-20
        49 TJTYHLZQC7 25-JUN-20
        50 CJLHQ8AAH6 30-OCT-20
        51 demoevent1 04-SEP-21
        52 demoevent2 03-SEP-21

52 rows selected.
```



# Database Activity Streams

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US/4-advanced/lab14

Database Activity Streams の機能を有効にすると、ほぼリアルタイムにアクティビティを Kinesis Data Streams に流すことができる。このデータは、監査ログとして利用ができる。例えば、SELECT や INSERT といったコマンド履歴をリアルタイムに取得が可能。

Kinesis Data Streams から Lambda を起動して、例えば OpenSearch Service にデータを格納することもできる。

![image-20230104195639928.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/41870788-cb17-77df-c3b0-310cda4d0ac5.png)

![image-20230104195655603.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/17beb728-7399-6d7c-a6f3-5b36b10eef5f.png)

# その他気が付いたこと

## NLS_LANG

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.OracleCharacterSets.html#Appendix.OracleCharacterSets.db-character-set

https://dev.classmethod.jp/articles/rds-for-oracle-nls-param/

RDS for Oracle では、初期化パラメータで、`NLS_LANGUAGE` 等を変更することが出来ないので、接続クライアント側で NLS_LANG 環境変数を利用するのが一番シンプルな方法



# 参考 URL

https://catalog.us-east-1.prod.workshops.aws/workshops/61aa971f-90c8-4e97-b108-60683e3f9c36/en-US

https://qiita.com/sugimount/items/4d4f8ccee095353f51ec