背景・目的
以前、Oracleデータベースの構築やStatspackを試しました。今回はDMS用の環境を構築します
実践
下記を元に試します。
本記事は検証・学習目的です
本番環境では強力なパスワード、アクセス制限、最新Oracleバージョンを使用してください
事前準備
- NWと、EC2、S3を用意します
- S3には、Oracleのインストーラーを用意しておきます
Oracleインストール
Swapの作成
1.rootユーザになります
sh-4.2$ sudo su -
#
2.スワップファイル作成（2GB）を作成します
# dd if=/dev/zero of=/swapfile bs=1M count=2048 # 2GBの空ファイル作成
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 6.08701 s, 353 MB/s
# chmod 600 /swapfile # パーミッション設定（セキュリティ）
# mkswap /swapfile # スワップ領域としてフォーマット
Setting up swapspace version 1, size = 2 GiB (2147479552 bytes)
no label, UUID=XXXX-XXX-XXX-XXX-XXXX
# swapon /swapfile # スワップを有効化（今すぐ使える状態に）
#
#
3.確認します
# echo '/swapfile none swap sw 0 0' >> /etc/fstab
# free -h
total used free shared buff/cache available
Mem: 7.7G 145M 145M 432K 7.4G 7.2G
Swap: 2.0G 0B 2.0G
#
EC2にログイン
- EC2にSSMで接続します
- Oracleユーザにスイッチします
sh-4.2$ whoami
ssm-user
sh-4.2$ id oracle
uid=XXXXX(oracle) gid=XXXX(oinstall) groups=XXXXX(oinstall),XXXXX(dba)
sh-4.2$
sh-4.2$ sudo su - oracle
$
Oracleのダウンロード
- 環境変数をセットします
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ export ORACLE_SID=ORCL
$ export PATH=$ORACLE_HOME/bin:$PATH
2.事前にアップしていたOracleソフトウェアをS3からダウンロードします
$ aws s3 cp s3://XXXXX/linux.x64_11gR2_database_1of2.zip .
download: s3://XXXXX/linux.x64_11gR2_database_1of2.zip to ./linux.x64_11gR2_database_1of2.zip
$ aws s3 cp s3://XXXXX/linux.x64_11gR2_database_2of2.zip .
download: s3://XXXXX/linux.x64_11gR2_database_2of2.zip to ./linux.x64_11gR2_database_2of2.zip
$ ls
linux.x64_11gR2_database_1of2.zip linux.x64_11gR2_database_2of2.zip README.txt
$ ls -l
total 2295596
-rw-r--r-- 1 oracle oinstall 1239269270 Jan 14 08:25 linux.x64_11gR2_database_1of2.zip
-rw-r--r-- 1 oracle oinstall 1111416131 Jan 14 08:26 linux.x64_11gR2_database_2of2.zip
-rw-r--r-- 1 oracle oinstall 654 Feb 16 15:20 README.txt
$
3.解凍します
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip
インストール
Oracleユーザで作業します
1.レスポンスファイルを作成します
$ cat > /home/oracle/db_install.rsp <<'EOF'
> oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
> oracle.install.option=INSTALL_DB_SWONLY
> ORACLE_HOSTNAME=localhost
> UNIX_GROUP_NAME=oinstall
> INVENTORY_LOCATION=/u01/app/oraInventory
> SELECTED_LANGUAGES=en,ja
> ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
> ORACLE_BASE=/u01/app/oracle
> oracle.install.db.InstallEdition=SE
> oracle.install.db.DBA_GROUP=dba
> oracle.install.db.OPER_GROUP=dba
> DECLINE_SECURITY_UPDATES=true
> EOF
$ ls -l /home/oracle/db_install.rsp
-rw-r--r-- 1 oracle oinstall 460 Feb 16 15:31 /home/oracle/db_install.rsp
$
2.インストールします（10分から15分程度かかります）
$ ./runInstaller -silent -responseFile /home/oracle/db_install.rsp -ignorePrereq
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 93788 MB Passed
Checking swap space: must be greater than 150 MB. Actual 2047 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2026-02-16_03-39-18PM. Please wait ...[oracle@ip-XXXXXX database]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2026-02-16_03-39-18PM.log
3.しばらくすると、Successfullyが表示されます
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root scripts to run
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
4. Return to this window and hit "Enter" key to continue
Successfully Setup Software.
スクリプトの実行
別のターミナルを開き、rootユーザで作業します
1.スクリプトを実行します
# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh
Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_ip-XXXXXX.ap-northeast-1.compute.internal_2026-02-16_15-44-35.log for the output of root script
#
データベースの作成
上記の作業が完了したら、Oracleユーザのターミナルに戻り作業します
1.Enterを押します
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
4. Return to this window and hit "Enter" key to continue
Successfully Setup Software.
$
2.環境変数を.bash_profileに追加
cat >> ~/.bash_profile <<'EOF'
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
EOF
source ~/.bash_profile
3.データベース作成用レスポンスファイル作成します
$ cat > /home/oracle/dbca.rsp <<'EOF'
> [GENERAL]
> RESPONSEFILE_VERSION = "11.2.0"
> OPERATION_TYPE = "createDatabase"
>
> [CREATEDATABASE]
> GDBNAME = "ORCL"
> SID = "ORCL"
> TEMPLATENAME = "General_Purpose.dbc"
> SYSPASSWORD = "XXXXX"
> SYSTEMPASSWORD = "XXXXX"
> SYSMANPASSWORD = "XXXXX"
> DBSNMPPASSWORD = "XXXXX"
> DATAFILEDESTINATION = "/u01/app/oracle/oradata"
> RECOVERYAREADESTINATION = "/u01/app/oracle/flash_recovery_area"
> STORAGETYPE = FS
> CHARACTERSET = "AL32UTF8"
> NATIONALCHARACTERSET = "AL16UTF16"
> MEMORYPERCENTAGE = "40"
> TOTALMEMORY = "2048"
> EOF
$
4.データベースを作成します（10-15分かかります）
$ dbca -silent -responseFile /home/oracle/dbca.rsp
5.しばらくすると、進み始めます
$ dbca -silent -responseFile /home/oracle/dbca.rsp
Copying database files
1% complete
3% complete
6.完了しました
$ dbca -silent -responseFile /home/oracle/dbca.rsp
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL.log" for further details.
$
DMS用の設定
現状確認
1.データベースに接続します
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 16 15:53:33 2026
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL>
2.接続確認します
SQL> SELECT status FROM v$instance;
STATUS
------------
OPEN
SQL> SELECT name FROM v$database;
NAME
---------
ORCL
SQL>
3.アーカイブログモードを確認します
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
アーカイブログモードを有効化
1.データベースをシャットダウンします
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
2.マウントモードで起動します
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1224737640 bytes
Database Buffers 905969664 bytes
Redo Buffers 4964352 bytes
Database mounted.
SQL>
3.アーカイブログモードを有効化します
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
SQL>
4.データベースをオープンします
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>
5.確認します。ARCHIVELOGになりました
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
サプリメンタルロギングの設定
データベースレベルの設定
sysユーザで作業します
1.データベースレベルのサプリメンタルロギングを有効化します
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL>
SQL> SELECT supplemental_log_data_min FROM v$database;
SUPPLEME
--------
YES
SQL>
データベース単位の設定
1.最初に確認します
SQL> SELECT supplemental_log_data_min, supplemental_log_data_pk
FROM v$database; 2
SUPPLEME SUP
-------- ---
YES NO
SQL>
2.プライマリキーロギングを有効化します
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
Database altered.
SQL>
3.確認します
SQL> SELECT supplemental_log_data_min, supplemental_log_data_pk
FROM v$database; 2
SUPPLEME SUP
-------- ---
YES YES
SQL>
テーブル単位の設定
1.テーブル単位で設定
DMSユーザの作成と設定
systemユーザで作業します
1.DMSユーザーを作成します
SQL> CREATE USER dmsuser IDENTIFIED BY "XXXX"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp; 2 3
User created.
SQL> exit
2.基本的な権限を付与します
SQL> GRANT CREATE SESSION TO dmsuser;
GRANT SELECT ANY TABLE TO dmsuser;
GRANT SELECT_CATALOG_ROLE TO dmsuser;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
3.LogMiner権限を付与します
V$ビューへの権限付与にはSYSDBA権限が必要なため、sysで実行
sysで作業します
SQL> GRANT EXECUTE ON DBMS_LOGMNR TO dmsuser;
GRANT SELECT ON V_$LOGMNR_LOGS TO dmsuser;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO dmsuser;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>```
4.システムビュー権限を付与します
:::note info
sysで作業します
:::
```sql
SQL> GRANT SELECT ON V_$ARCHIVED_LOG TO dmsuser;
GRANT SELECT ON V_$LOG TO dmsuser;
GRANT SELECT ON V_$LOGFILE TO dmsuser;
GRANT SELECT ON V_$DATABASE TO dmsuser;
GRANT SELECT ON V_$THREAD TO dmsuser;
GRANT SELECT ON V_$PARAMETER TO dmsuser;
GRANT SELECT ON V_$NLS_PARAMETERS TO dmsuser;
GRANT SELECT ON V_$TIMEZONE_NAMES TO dmsuser;
GRANT SELECT ON V_$TRANSACTION TO dmsuser;
GRANT SELECT ON V_$DATABASE_INCARNATION TO dmsuser;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
5.メタデータビュー権限を付与します
sysで作業します
SQL> GRANT SELECT ON ALL_INDEXES TO dmsuser;
GRANT SELECT ON ALL_OBJECTS TO dmsuser;
GRANT SELECT ON ALL_TABLES TO dmsuser;
GRANT SELECT ON ALL_USERS TO dmsuser;
GRANT SELECT ON ALL_CATALOG TO dmsuser;
GRANT SELECT ON ALL_CONSTRAINTS TO dmsuser;
GRANT SELECT ON ALL_CONS_COLUMNS TO dmsuser;
GRANT SELECT ON ALL_TAB_COLS TO dmsuser;
GRANT SELECT ON ALL_IND_COLUMNS TO dmsuser;
GRANT SELECT ON ALL_LOG_GROUPS TO dmsuser;
GRANT SELECT ON ALL_TAB_PARTITIONS TO dmsuser;
GRANT SELECT ON SYS.DBA_REGISTRY TO dmsuser;
GRANT SELECT ON SYS.OBJ$ TO dmsuser;
GRANT SELECT ON DBA_TABLESPACES TO dmsuser;
GRANT SELECT ON DBA_OBJECTS TO dmsuser;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL>
SQL>
SQL>
6.確認します
systemで作業します
SQL> SELECT username, account_status FROM dba_users WHERE username = 'DMSUSER';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
DMSUSER OPEN
SQL>
7.設定数を確認します
- システム権限: 2個
- テーブル/ビュー権限: 39個
SQL> SELECT COUNT(*) as sys_privs FROM dba_sys_privs WHERE grantee = 'DMSUSER';
SYS_PRIVS
----------
2
SQL> SELECT COUNT(*) as tab_privs FROM dba_tab_privs WHERE grantee = 'DMSUSER';
TAB_PRIVS
----------
39
SQL>
8.設定を確認します
- アーカイブログモード: ARCHIVELOG
- サプリメンタルロギング（最小）: YES
- サプリメンタルロギング（PK）: YES
SQL> SELECT
'Archive Mode' as setting,
log_mode as value
FROM v$database
UNION ALL
SELECT
'Supplemental Logging (MIN)',
supplemental_log_data_min
FROM v$database
UNION ALL
SELECT
'Supplemental Logging (PK)',
supplemental_log_data_pk
FROM v$database; 2 3 4 5 6 7 8 9 10 11 12 13 14
SETTING VALUE
-------------------------- ------------
Archive Mode ARCHIVELOG
Supplemental Logging (MIN) YES
Supplemental Logging (PK) YES
SQL>
セキュリティグループ設定
EC2のインバウンドルール:
- ポート1521をDMSのSGからのみ許可
- 0.0.0.0/0は許可しない
リスナー起動
- リスナーを確認します
$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-FEB-2026 16:26:18
Copyright (c) 1991, 2009, Oracle. All rights reserved.
TNS-01106: Listener using listener name LISTENER has already been started
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-FEB-2026 16:26:26
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-FEB-2026 16:13:10
Uptime 0 days 0 hr. 13 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/ip-XXXXX/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-XXXXXXX.ap-northeast-1.compute.internal)(PORT=1521)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully
$
DMSでレプリケーションテスト用のテーブルとデータの準備
- ユーザ作成します
CREATE USER testapp IDENTIFIED BY "XXXXX"
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
User created.
2.権限を付与します
SQL> GRANT CONNECT, RESOURCE TO testapp;
GRANT UNLIMITED TABLESPACE TO testapp;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL> SELECT username FROM dba_users WHERE username = 'TESTAPP';
USERNAME
------------------------------
TESTAPP
SQL>
3.テストテーブルを作成します
SQL> CREATE TABLE testapp.customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL,
email VARCHAR2(100),
created_date DATE DEFAULT SYSDATE
);
CREATE TABLE testapp.orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
order_date DATE DEFAULT SYSDATE,
amount NUMBER(10,2),
status VARCHAR2(20),
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES testapp.customers(customer_id)
); 2 3 4 5 6
Table created.
SQL> SQL> 2 3 4 5 6 7 8
Table created.
SQL>
SQL>
SQL>
4.テストデータを投入します
SQL> INSERT INTO testapp.customers VALUES (1, 'Tanaka Taro', 'tanaka@example.com', SYSDATE);
INSERT INTO testapp.customers VALUES (2, 'Suzuki Hanako', 'suzuki@example.com', SYSDATE);
INSERT INTO testapp.customers VALUES (3, 'Sato Ichiro', 'sato@example.com', SYSDATE);
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO testapp.orders VALUES (101, 1, SYSDATE, 15000, 'COMPLETED');
INSERT INTO testapp.orders VALUES (102, 1, SYSDATE, 8500, 'PENDING');
INSERT INTO testapp.orders VALUES (103, 2, SYSDATE, 25000, 'COMPLETED');
INSERT INTO testapp.orders VALUES (104, 3, SYSDATE, 12000, 'SHIPPED');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
SQL>
SQL> COMMIT;
Commit complete.
SQL>
5.確認します
SQL> SELECT COUNT(*) FROM testapp.customers;
SELECT COUNT(*) FROM testapp.orders;
SELECT * FROM testapp.customers;
SELECT * FROM testapp.orders;
COUNT(*)
----------
3
SQL>
COUNT(*)
----------
4
SQL>
CUSTOMER_ID
-----------
CUSTOMER_NAME
--------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------
CREATED_D
---------
1
Tanaka Taro
tanaka@example.com
16-FEB-26
CUSTOMER_ID
-----------
CUSTOMER_NAME
--------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------
CREATED_D
---------
2
Suzuki Hanako
suzuki@example.com
16-FEB-26
CUSTOMER_ID
-----------
CUSTOMER_NAME
--------------------------------------------------------------------------------
EMAIL
--------------------------------------------------------------------------------
CREATED_D
---------
3
Sato Ichiro
sato@example.com
16-FEB-26
SQL>
ORDER_ID CUSTOMER_ID ORDER_DAT AMOUNT STATUS
---------- ----------- --------- ---------- --------------------
101 1 16-FEB-26 15000 COMPLETED
102 1 16-FEB-26 8500 PENDING
103 2 16-FEB-26 25000 COMPLETED
104 3 16-FEB-26 12000 SHIPPED
SQL>
考察
次回は、DMSと接続を試します。
参考