DMS 온라인 Online 데이터 이관 : 이 마이그레이션은 시점 point-in-time 복사본을 만들고 소스(원본)에서 타겟(대상) 데이터베이스로 이후 변경 사항을 전부 복제. 이를 통해 마이그레이션 중에도 응용 프로그램이 온라인 상태를 유지하고 원본 데이터베이스에서 대상 데이터베이스로 전환될 수 있음.
사전 준비
2024.02.19 - [3. 데이터관리] - OCI DMS (Database Migration Service) 사용하여 데이터 이관하기
- 상단 구성도 상에 표현된 마켓 플레이스를 통해 셋업되는 VM GoldenGate 이외에 OCI GoldenGate 서비스도 가능
- 지난 DMS Offline 마이그레이션 설정에서 추가되는 내용들만 작성됨
Security List 포트 오픈 (443)
데이터 이관 전 데이터베이스 설정 확인 및 변경 - Migration Type online
[opc@dbcs ~]$ sudo su - oracle
Last login: Wed Feb 21 03:35:22 UTC 2024 on pts/0
[oracle@dbcs ~]$
[oracle@dbcs ~]$ ./dms-db-prep-v2.sh
-- Oracle Cloud Infrastructure Database Migration Service --
This script will help you prepare your source and target databases for migration.
Please answer the following questions to proceed:
Database type [(s)ource/(t)arget]?: s
Is your source database hosted in AWS RDS (Amazon Relational Database Service)? [y/n]: n
Is your database multi-tenant or single-tenant? [(m)ulti/(s)ingle]: m
Please provide your PDB service name (e.g. amer.subnet1.alimavcn.oraclevcn.com): DB0216_pdb1.sub02160159371.vcndwhjk.oraclevcn.com
Password for system user:
Migration type [(on)line/(off)line]: on
Password for ggadmin/c##ggadmin user:
Sql script /home/oracle/dms_prep_db.sql generated.
Please connect to your database as sysdba (role) and run the above generated sql script.
This script will analyze your database and will generate a subsequent sql script that you must review, modify (if needed) and run in order to get your database set up for the migration.
[ info] When setting up your migration through the OCI Console:
[ info] Use system user as the Database Administrator User when Registering Source Database through the Database Migration Service
[ info] Make one Database Registration for Container Database (CDB) and another one for Plugable Database (PDB)
[ info] Use ggadmin user as the GoldenGate Database User for Source Database when creating the Migration definition through the Database Migration Service
[ info] Use c##ggadmin user as the GoldenGate Container Database user for Source Database when creating the migration definition through the Database Migration Service
[oracle@dbcs ~]$
SQL> @/home/oracle/dms_prep_db.sql
--
-- The output shown below is informational. This is to provide you detail on the actions
-- that will be performed when the generated script is ran on your database.
--
--
--########################################################################################
-- Database Information
--########################################################################################
--Database Name: DB0216
--Database Host Name: dbcs
--Database Instance Name: DB0216
--Database Unique Name: DB0216_qpv_yny
--Database is Container (CDB): YES
--Database CDB Service Name: DB0216_QPV_YNY.SUB02161007471.VCNDWHJK.ORACLEVCN.COM
--Database PDB Service Name: DB0216_PDB1.SUB02160159371.VCNDWHJK.ORACLEVCN.COM
--Database CDB User Exist: NO (User Name: C##GGADMIN)
--Database CDB User Locked: YES (Required: NO)
--Database PDB User Exist: NO (User Name: GGADMIN)
--Database PDB User Locked: YES (Required: NO)
--Database Global Names: FALSE
--
--########################################################################################
-- Database GoldenGate Status
--########################################################################################
--Database Restart Required: NO
--Database Archived Log Mode: YES (Required value for GoldenGate: YES)
--Database Force Logging Mode: YES (Required value for GoldenGate: YES)
--Database Supplemental Mode: YES (Required value for GoldenGate: YES)
--Database Stream Pool Size Mb: 2048 (Recommended value for GoldenGate: 2048Mb)
--GoldenGate Enable Parameter: FALSE (Required value for GoldenGate: TRUE)
--
--########################################################################################
-- Actions to be performed for preparing your SOURCE Database for ONLINE Migration
--########################################################################################
--
-- Database is in Archived Log Mode, No Restart required.
--
-- Global_names is set to FALSE and no action is necessary.
--
-- Stream pool size is already configured to 2048 and no action is necessary.
--
-- Database DB0216 is in the required FORCE LOGGING mode, NO action is required.
--
-- Database DB0216 GoldenGate Parameter is not ENABLED, alter database is required
--
-- Property Description
-- Parameter type Boolean
-- Default value false
-- Modifiable ALTER SYSTEM
-- Modifiable in a PDB No
-- Range of values true | false
-- Basic No
-- Oracle RAC All instances must have the same setting
--
-- This parameter primarily controls supplemental logging required to support logical
-- replication of new data types and operations. The redo log file is designed to be
-- applied physically to a database, therefore the default contents of the redo log file
-- often do not contain sufficient information to allow logged changes to be converted
-- into SQL statements. Supplemental logging adds extra information into the redo log
-- files so that replication can convert logged changes into SQL statements without
-- having to access the database for each change. Previously these extra changes
-- were controlled by the supplemental logging DDL. Now the ENABLE_GOLDENGATE_REPLICATION
-- parameter must also be set to enable the required supplemental logging for any
-- new data types or operations.
--
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
--
-- Database DB0216 has SUPPLEMENTAL LOGGING enabled, NO action is required.
--
-- User SYSTEM from database DB0216 is unlocked, NO action is required.
--
--######################################################
--#### Create and Grant Privileges to the CDB user. ####
--######################################################
--
-- GoldenGate CDB User does not exist, create CDB user is required to extract transactions from the database.
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE USER C##GGADMIN IDENTIFIED BY "PassW0rd##11" CONTAINER=ALL DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;
--
GRANT CONNECT TO C##GGADMIN CONTAINER=ALL;
GRANT RESOURCE TO C##GGADMIN CONTAINER=ALL;
GRANT CREATE TABLE TO C##GGADMIN CONTAINER=ALL;
GRANT CREATE VIEW TO C##GGADMIN CONTAINER=ALL;
GRANT CREATE SESSION TO C##GGADMIN CONTAINER=ALL;
GRANT SELECT_CATALOG_ROLE TO C##GGADMIN CONTAINER=ALL;
GRANT DV_GOLDENGATE_ADMIN TO C##GGADMIN CONTAINER=ALL;
GRANT DV_GOLDENGATE_REDO_ACCESS TO C##GGADMIN CONTAINER=ALL;
GRANT ALTER SYSTEM TO C##GGADMIN CONTAINER=ALL;
GRANT ALTER USER TO C##GGADMIN CONTAINER=ALL;
GRANT SELECT ANY DICTIONARY TO C##GGADMIN CONTAINER=ALL;
GRANT SELECT ANY TRANSACTION TO C##GGADMIN CONTAINER=ALL;
GRANT EXECUTE ON dbms_lock TO C##GGADMIN CONTAINER=ALL;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##GGADMIN',CONTAINER=>'ALL');
--
--######################################################
--#### Create and Grant Privileges to the PDB user. ####
--######################################################
--
-- GoldenGate PDB User does not exist, create PDB user is required to extract transactions from the database.
--
ALTER SESSION SET CONTAINER = DB0216_PDB1;
CREATE TABLESPACE GG_ADMIN DATAFILE '+DATA/' SIZE 100m AUTOEXTEND ON NEXT 100m;
CREATE USER GGADMIN IDENTIFIED BY "PassW0rd##11" CONTAINER=CURRENT DEFAULT TABLESPACE GG_ADMIN TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON GG_ADMIN;
--
GRANT CONNECT TO GGADMIN CONTAINER=CURRENT;
GRANT RESOURCE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE SESSION TO GGADMIN CONTAINER=CURRENT;
GRANT SELECT_CATALOG_ROLE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER SYSTEM TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER USER TO GGADMIN CONTAINER=CURRENT;
GRANT DATAPUMP_EXP_FULL_DATABASE TO GGADMIN CONTAINER=CURRENT;
GRANT DATAPUMP_IMP_FULL_DATABASE TO GGADMIN CONTAINER=CURRENT;
GRANT SELECT ANY DICTIONARY TO GGADMIN CONTAINER=CURRENT;
GRANT SELECT ANY TRANSACTION TO GGADMIN CONTAINER=CURRENT;
GRANT INSERT ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT UPDATE ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT DELETE ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT LOCK ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY INDEX TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY CLUSTER TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY INDEXTYPE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY OPERATOR TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY PROCEDURE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY TRIGGER TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY TYPE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE ANY VIEW TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY TABLE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY INDEX TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY CLUSTER TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY INDEXTYPE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY OPERATOR TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY PROCEDURE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY TRIGGER TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY TYPE TO GGADMIN CONTAINER=CURRENT;
GRANT ALTER ANY SEQUENCE TO GGADMIN CONTAINER=CURRENT;
GRANT CREATE DATABASE LINK TO GGADMIN CONTAINER=CURRENT;
GRANT EXECUTE ON dbms_lock TO GGADMIN CONTAINER=CURRENT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('GGADMIN',CONTAINER=>'CURRENT');
--
--
-- Script DMS_Configuration.sql generated. Please review this script, modify as appropriate and run it in your database.
-- Your source database will be ready for migration after execution of these operations.
--
SQL>
[oracle@dbcs ~]$ ./dms-db-prep-v2.sh
-- Oracle Cloud Infrastructure Database Migration Service --
This script will help you prepare your source and target databases for migration.
Please answer the following questions to proceed:
Database type [(s)ource/(t)arget]?: s
Is your source database hosted in AWS RDS (Amazon Relational Database Service)? [y/n]: n
Is your database multi-tenant or single-tenant? [(m)ulti/(s)ingle]: m
Please provide your PDB service name (e.g. amer.subnet1.alimavcn.oraclevcn.com): DB0216_pdb1.sub02160159371.vcndwhjk.oraclevcn.com
Password for system user:
Migration type [(on)line/(off)line]: on
Password for ggadmin/c##ggadmin user:
Sql script /home/oracle/dms_prep_db.sql generated.
Please connect to your database as sysdba (role) and run the above generated sql script.
This script will analyze your database and will generate a subsequent sql script that you must review, modify (if needed) and run in order to get your database set up for the migration.
[ info] When setting up your migration through the OCI Console:
[ info] Use system user as the Database Administrator User when Registering Source Database through the Database Migration Service
[ info] Make one Database Registration for Container Database (CDB) and another one for Plugable Database (PDB)
[ info] Use ggadmin user as the GoldenGate Database User for Source Database when creating the Migration definition through the Database Migration Service
[ info] Use c##ggadmin user as the GoldenGate Container Database user for Source Database when creating the migration definition through the Database Migration Service
[oracle@dbcs ~]$
[oracle@dbcs ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 03:45:33 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> @/home/oracle/dms_prep_db.sql
--
-- The output shown below is informational. This is to provide you detail on the actions
-- that will be performed when the generated script is ran on your database.
--
--
--########################################################################################
-- Database Information
--########################################################################################
--Database Name: DB0216
--Database Host Name: dbcs
--Database Instance Name: DB0216
--Database Unique Name: DB0216_qpv_yny
--Database is Container (CDB): YES
--Database CDB Service Name: DB0216_QPV_YNY.SUB02161007471.VCNDWHJK.ORACLEVCN.COM
--Database PDB Service Name: DB0216_PDB1.SUB02160159371.VCNDWHJK.ORACLEVCN.COM
--Database CDB User Exist: YES (User Name: C##GGADMIN)
--Database CDB User Locked: NO (Required: NO)
--Database PDB User Exist: YES (User Name: GGADMIN)
--Database PDB User Locked: NO (Required: NO)
--Database Global Names: FALSE
--
--########################################################################################
-- Database GoldenGate Status
--########################################################################################
--Database Restart Required: NO
--Database Archived Log Mode: YES (Required value for GoldenGate: YES)
--Database Force Logging Mode: YES (Required value for GoldenGate: YES)
--Database Supplemental Mode: YES (Required value for GoldenGate: YES)
--Database Stream Pool Size Mb: 2048 (Recommended value for GoldenGate: 2048Mb)
--GoldenGate Enable Parameter: TRUE (Required value for GoldenGate: TRUE)
--
--########################################################################################
-- Actions to be performed for preparing your SOURCE Database for ONLINE Migration
--########################################################################################
--
-- Database is in Archived Log Mode, No Restart required.
--
-- Global_names is set to FALSE and no action is necessary.
--
-- Stream pool size is already configured to 2048 and no action is necessary.
--
-- Database DB0216 is in the required FORCE LOGGING mode, NO action is required.
--
-- Database DB0216 GoldenGate Parameter is ENABLED, NO action is required.
--
-- Database DB0216 has SUPPLEMENTAL LOGGING enabled, NO action is required.
--
-- User SYSTEM from database DB0216 is unlocked, NO action is required.
--
-- CDB User already exists, no action required.
--
--######################################################
--#### Privileges to be granted to the CDB user. ####
--######################################################
--
ALTER SESSION SET CONTAINER = CDB$ROOT;
--
-- Privilege CONNECT already granted TO C##GGADMIN
-- Privilege RESOURCE already granted TO C##GGADMIN
-- Privilege CREATE TABLE already granted TO C##GGADMIN
-- Privilege CREATE VIEW already granted TO C##GGADMIN
-- Privilege CREATE SESSION already granted TO C##GGADMIN
-- Privilege SELECT_CATALOG_ROLE already granted TO C##GGADMIN
-- Privilege DV_GOLDENGATE_ADMIN already granted TO C##GGADMIN
-- Privilege DV_GOLDENGATE_REDO_ACCESS already granted TO C##GGADMIN
-- Privilege ALTER SYSTEM already granted TO C##GGADMIN
-- Privilege ALTER USER already granted TO C##GGADMIN
-- Privilege SELECT ANY DICTIONARY already granted TO C##GGADMIN
-- Privilege SELECT ANY TRANSACTION already granted TO C##GGADMIN
GRANT EXECUTE ON dbms_lock TO C##GGADMIN CONTAINER=ALL;
--
-- PDB User already exists, no action required.
--
--######################################################
--#### Privileges to be granted to the PDB user. ####
--######################################################
--
ALTER SESSION SET CONTAINER = DB0216_PDB1;
--
-- Privilege CONNECT already granted TO GGADMIN
-- Privilege RESOURCE already granted TO GGADMIN
-- Privilege CREATE SESSION already granted TO GGADMIN
-- Privilege SELECT_CATALOG_ROLE already granted TO GGADMIN
-- Privilege ALTER SYSTEM already granted TO GGADMIN
-- Privilege ALTER USER already granted TO GGADMIN
-- Privilege DATAPUMP_EXP_FULL_DATABASE already granted TO GGADMIN
-- Privilege DATAPUMP_IMP_FULL_DATABASE already granted TO GGADMIN
-- Privilege SELECT ANY DICTIONARY already granted TO GGADMIN
-- Privilege SELECT ANY TRANSACTION already granted TO GGADMIN
-- Privilege INSERT ANY TABLE already granted TO GGADMIN
-- Privilege UPDATE ANY TABLE already granted TO GGADMIN
-- Privilege DELETE ANY TABLE already granted TO GGADMIN
-- Privilege LOCK ANY TABLE already granted TO GGADMIN
-- Privilege CREATE ANY TABLE already granted TO GGADMIN
-- Privilege CREATE ANY INDEX already granted TO GGADMIN
-- Privilege CREATE ANY CLUSTER already granted TO GGADMIN
-- Privilege CREATE ANY INDEXTYPE already granted TO GGADMIN
-- Privilege CREATE ANY OPERATOR already granted TO GGADMIN
-- Privilege CREATE ANY PROCEDURE already granted TO GGADMIN
-- Privilege CREATE ANY SEQUENCE already granted TO GGADMIN
-- Privilege CREATE ANY TRIGGER already granted TO GGADMIN
-- Privilege CREATE ANY TYPE already granted TO GGADMIN
-- Privilege CREATE ANY SEQUENCE already granted TO GGADMIN
-- Privilege CREATE ANY VIEW already granted TO GGADMIN
-- Privilege ALTER ANY TABLE already granted TO GGADMIN
-- Privilege ALTER ANY INDEX already granted TO GGADMIN
-- Privilege ALTER ANY CLUSTER already granted TO GGADMIN
-- Privilege ALTER ANY INDEXTYPE already granted TO GGADMIN
-- Privilege ALTER ANY OPERATOR already granted TO GGADMIN
-- Privilege ALTER ANY PROCEDURE already granted TO GGADMIN
-- Privilege ALTER ANY SEQUENCE already granted TO GGADMIN
-- Privilege ALTER ANY TRIGGER already granted TO GGADMIN
-- Privilege ALTER ANY TYPE already granted TO GGADMIN
-- Privilege ALTER ANY SEQUENCE already granted TO GGADMIN
-- Privilege CREATE DATABASE LINK already granted TO GGADMIN
GRANT EXECUTE ON dbms_lock TO GGADMIN CONTAINER=CURRENT;
--
--
-- Script DMS_Configuration.sql generated. Please review this script, modify as appropriate and run it in your database.
-- Your source database will be ready for migration after execution of these operations.
--
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
SQL>
SQL> GRANT EXECUTE ON dbms_lock TO C##GGADMIN CONTAINER=ALL;
SQL>
SQL> ALTER SESSION SET CONTAINER = DB0216_PDB1;
SQL>
SQL> GRANT EXECUTE ON dbms_lock TO GGADMIN CONTAINER=CURRENT;
SQL>
DBCS에 임의 테이블 생성 및 데이터 입력
[oracle@dbcs ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 03:52:35 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DB0216_PDB1 READ WRITE NO
SQL>
SQL> alter session set container=DB0216_PDB1;
Session altered.
SQL> CREATE USER DMSMIGONLINE IDENTIFIED BY "PassW0rd##11";
User created.
SQL> GRANT CONNECT, RESOURCE TO DMSMIGONLINE;
Grant succeeded.
SQL> ALTER USER DMSMIGONLINE QUOTA UNLIMITED ON USERS;
User altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@dbcs ~]$
[oracle@dbcs ~]$ sqlplus DMSMIGONLINE@10.10.1.7:1521/DB0216_pdb1.sub02161007471.vcndwhjk.oraclevcn.com
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 03:53:27 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> CREATE TABLE dept_online (
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
2 3 4 5
Table created.
SQL> INSERT INTO dept_online (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept_online (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept_online (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept_online (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');
commit;
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL> CREATE TABLE emp_online (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
2 3 4 5 6 7 8 9 10
Table created.
SQL> INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1990-06-09', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1995-10-31', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1992-06-11', 'YYYY-MM-DD'), 2850, NULL, 30);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1993-05-14', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1996-03-05', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1997-12-05', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1995-06-04', 'YYYY-MM-DD'), 1500, 0, 30);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('2000-06-23', 'YYYY-MM-DD'), 950, NULL, 30);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1998-12-05', 'YYYY-MM-DD'), 1250, 1400, 30);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1998-08-15', 'YYYY-MM-DD'), 1600, 300, 30);
commit;
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
Commit complete.
SQL> select count(*) from dept_online;
COUNT(*)
----------
4
SQL> select count(*) from emp_online;
COUNT(*)
----------
10
SQL>
데이터베이스 연결 생성 - 소스 DBCS (replication option과 함께)
CDB
Name : SourceCDBOnlineJK
Compartment : dwh-resource
Vault in dwh-resource : DMS_Vault-JK
Encryption key in dwh-resource : DMS_Key-JK
Database type : Oracle Base Database
Database system in dwh-resource : DMS-Source-DBCS
Database home : dbhome20240216101319
Database : DB0216
Connect string : 10.10.1.7:1521/DB0216_qpv_yny.sub02161007471.vcndwhjk.oraclevcn.com
The host in the connect string must be an IP address; if needed change the FQDN default to an IP address.
Subnet in dwh-network : private subnet-VCN-DWH-JK (in VCN-DWH-JK VCN)
-------
Initial load database username : system
Use different credentials for replication : Checked
Replication database username : c##ggadmin
PDB
Name : SourcePDBOnlineJK
Compartment : dwh-resource
Vault in dwh-resource : DMS_Vault-JK
Encryption key in dwh-resource : DMS_Key-JK
Database type : Oracle Base Database
Database system in dwh-resource : DMS-Source-DBCS
Database home : dbhome20240216101319
Database : DB0216
Connect string : 10.10.1.7:1521/DB0216_pdb1.sub02161007471.vcndwhjk.oraclevcn.com
The host in the connect string must be an IP address; if needed change the FQDN default to an IP address.
Subnet in dwh-network : private subnet-VCN-DWH-JK (in VCN-DWH-JK VCN)
-------
Initial load database username : system
Use different credentials for replication : Checked
Replication database username : ggadmin
데이터베이스 사용자 ggadmin 활성화 및 연결 생성 - 타겟 ADB (replication option과 함께)
사용자 활성
마이그레이션 생성 Migration 및 검증 Validation
Name : MigrationOnlineJK
Compartment : dwh-resource
Vault in dwh-resource : DMS_Vault-JK
Encryption key in dwh-resource : DMS_Key-JK
----------------------------
Source database
Database connection in dwh-resource : SourcePDBOnlineJK
Database is pluggable database (PDB) : Checked
Container database connection in dwh-resource : SourceCDBOnlineJK
Target database
Database connection in dwh-resource : TargetADBOnlineJK
----------------------------
Transfer medium for initial load : Data Pump via object storage
Source database
Export directory object name : dumpdir
Export directory object path : /u01/app/oracle/dumpdir
Source Database file system SSL wallet path : /u01/app/oracle/dumpdir/wallet
Object storage bucket in dwh-resource : bucket-DMS-JK
Use online replication : checked
마이그레이션 실행 및 결과 확인
소스 데이터베이스 데이터 변경 (데이터 삭제 > 확인, 데이터 입력 > 확인)
[oracle@dbcs ~]$ sqlplus DMSMIGONLINE@10.10.1.7:1521/DB0216_pdb1.sub02161007471.vcndwhjk.oraclevcn.com
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 21 06:59:53 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Feb 21 2024 03:53:32 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> delete from emp_online;
delete from dept_online;
commit;
10 rows deleted.
SQL>
4 rows deleted.
SQL>
Commit complete.
SQL> select count(*) from dept_online;
select count(*) from emp_online;
COUNT(*)
----------
0
SQL>
COUNT(*)
----------
0
SQL> INSERT INTO dept_online (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept_online (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1990-06-09', 'YYYY-MM-DD'), 5000, NULL, 10);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1995-10-31', 'YYYY-MM-DD'), 2975, NULL, 20);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1993-05-14', 'YYYY-MM-DD'), 2450, NULL, 10);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1996-03-05', 'YYYY-MM-DD'), 3000, NULL, 20);
INSERT INTO emp_online (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1997-12-05', 'YYYY-MM-DD'), 3000, NULL, 20);
commit;
select count(*) from dept_online;
select count(*) from emp_online;
1 row created.
SQL>
1 row created.
SQL> SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> SQL>
Commit complete.
SQL> SQL>
COUNT(*)
----------
2
SQL>
COUNT(*)
----------
5
SQL>
최종 이관 작업 마무리 Switchover
참고
OCI Database Migration Service End-To-End Online Migration Tutorial
'3. 데이터관리' 카테고리의 다른 글
| Oracle 데이터베이스 보안 솔루션 #1 - Overview (Database Security Solution Overview) (0) | 2024.02.27 |
|---|---|
| 오라클 데이터베이스 JSON 지원 (0) | 2024.02.27 |
| Select AI 쿼리 생성 기능 소개(ADB) (0) | 2024.02.20 |
| OCI DMS (Database Migration Service) 사용하여 데이터 이관하기 (0) | 2024.02.19 |
| MySQL HeatWave 생성, 클러스터 추가 후 Report Query 성능 비교 (0) | 2024.02.04 |
댓글