DMS (Database Migration Service)는 데이터베이스 관리자가 하나 이상의 소스 데이터베이스에서 오라클 클라우드 데이터베이스로 실시간으로 대규모로 이관하는 것을 돕는 완전 관리형 서비스로 데이터베이스 마이그레이션을 단일 인터페이스에서 구성, 실행 및 모니터링할 수 있음.
데이터 이관 대상 데이터베이스 (DBCS to ADB)
소스 : 오라클 기본 데이터베이스 19c Enterprise Edition
타겟 : 오라클 자율주행 데이터베이스 19c
추가적으로 예상될 수 있는 고객사 환경 조건 포함하여 테스트
1) 소스 데이터베이스는 Private 서브넷 환경에 위치 (연결을 위해 Bastion 서비스나 Bastion 호스트 필요)
2) 구획 Compartment가 세분화되어 있어 Network 환경이 셋업 되는 Compartment와 DB가 구동되는 Compartment가 다름
중요!! DMS 환경 구성을 위해 OCI 어드민 권한과 정책이 필요
소스와 타켓 데이터베이스 지원 범위
가격은요, 처음 6개월 동안은 무료로 사용 가능하기에 일회성 작업으로 데이터 이관하는데 부담이 없음.
구획 생성 Compartment
dwh-network
dwh-resource
네트워크 생성 Virtual Cloud Network
VCN name : VCN-DWH-JK
Compartment : dwh-network
VCN IPv4 CIDR block : 10.10.0.0/16
Public subnet IPv4 CIDR block : 10.10.0.0/24
Private subnet IPv4 CIDR block : 10.10.1.0/24
Security List 포트 오픈
볼트 생성 Vault
Compartment : dwh-resource
Name : DMS_Vault-JK
키생성
Compartment : dwh-resource
Protection Mode : HSM
Name : DMS_Key-JK
Key Shape: Algorithm AES (Symmetric key used for Encrypt and Decrypt)
Key Shape: Length 256 bits
오브젝트 스토리지 버킷 생성
소스 데이터베이스 생성 DBCS
DBCS 정보 확인
Private IP : 10.10.1.7
CDB Service Name : DB0216_qpv_yny.sub02161007471.vcndwhjk.oraclevcn.com
PDB Service Name : DB0216_pdb1.sub02161007471.vcndwhjk.oraclevcn.com
Private Subnet DB 연결을 위한 포트 포워딩 (Bastion 서비스)
Window PowerShell 창을 통해 해당 커맨드 실행 후 접속
(<privateKey> <localPort> 부분은 치환 필요)
ssh -i <privateKey> -N -L <localPort>:10.10.1.7:22 -p 22 ocid1.bastionsession.oc1.ap-chuncheon-1.amaaaaaaxc7u3kialcrbdvb5sbqs2gdqjcvtcldn6yqk6jbabgtxx4bkvpjq@host.bastion.ap-chuncheon-1.oci.oraclecloud.com
또는 ssh 툴에서 제공되는 터널링 기능에 해당 bastion 세션 정보 입력 후 접속
접속은 아래와 같이 localhost로
DBCS SSL 인증과 ACL 권한 부여
(만약 소스나 타겟이 ADB가 아니고 데이터베이스 연결 중 SSH 세부 정보를 제공하지 않고 HTTPS 연결을 달성하려면 소스 및 타겟 데이터베이스 호스트에서 다음 단계를 수행 필요)
Wallet / SSL / ACL 설정 필요에 대한 설명
sudo su - oracle
mkdir -p /u01/app/oracle/dumpdir/wallet
cd /u01/app/oracle/dumpdir/
curl -o walletSSL.zip https://objectstorage.us-phoenix-1.oraclecloud.com/p/FSBC_LRRpLxcSuSM6yRjO9u1TDuDy8wuiawEIl8Q_xPYFmvap_tPFdtm_c6TskV_/n/axsdric7bk0y/b/SSL-Wallet-For-No-SSH-Migrations-Setup/o/walletSSL.zip
unzip walletSSL.zip
col host format a20
col privilege format a20
set pages 20
SELECT host, lower_port, upper_port, privilege, status FROM user_network_acl_privileges;
define clouduser='system'; /*user performing export at source or import at target*/
define sslwalletdir='/u01/app/oracle/dumpdir/wallet'; /* OCI wallet path*/
BEGIN
dbms_network_acl_admin.append_host_ace(host => '*', lower_port => 443, upper_port => 443, ace => xs$ace_type(privilege_list => xs$name_list('http', 'http_proxy'), principal_name => upper('&clouduser'), principal_type => xs_acl.ptype_db));
dbms_network_acl_admin.append_wallet_ace(wallet_path => 'file:&sslwalletdir', ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates','use_passwords'), principal_name => upper('&clouduser'), principal_type => xs_acl.ptype_db));
END;
/
SELECT host, lower_port, upper_port, privilege, status FROM user_network_acl_privileges;
데이터 이관 전 데이터베이스 설정 확인 및 변경
Preparing Your Databases for Migration
ls -ltr
sudo mv dms-db-prep-v2.sh /home/oracle/
sudo chown oracle:oinstall /home/oracle/dms-db-prep-v2.sh
sudo su - oracle
ls -ltr
chmod +x dms-db-prep-v2.sh
ls -ltr
./dms-db-prep-v2.sh[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 ~]$
>> Offline 이관일 경우
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_4z9_sin
--Database is Container (CDB): YES
--Database CDB Service Name: DB0216_4Z9_SIN.SUB02160159371.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: TRUE
--
--########################################################################################
-- Actions to be performed for preparing your SOURCE Database for OFFLINE Migration
--########################################################################################
--
-- Database is in Archived Log Mode, No Restart required.
--
-- Database DB0216 GLOBAL_NAMES is enabled, alter database is required.
--
-- Property Description
-- Parameter type Boolean
-- Default value false
-- Modifiable ALTER SESSION, ALTER SYSTEM
-- Modifiable in a PDB Yes
-- Range of values true | false
-- Basic No
--
-- GLOBAL_NAMES specifies whether a database link is required to have the same
-- name as the database to which it connects.
-- If the value of GLOBAL_NAMES is false, then no check is performed. If you use
-- use or plan to use distributed processing, then Oracle recommends that you set
-- set this parameter to true to ensure the use of consistent naming conventions
-- for databases and links in a networked environment.
-- Oracle DMS recommends global_names to be set at false
--
ALTER SYSTEM SET GLOBAL_NAMES=FALSE;
--
-- Database DB0216 STREAMS_POOL_SIZE current size is 0Mb and it will be modified to 2048Mb
-- The STREAMS_POOL_SIZE value helps determine the size of the Streams pool.
--
-- Property Description
-- Parameter type Big integer
-- Syntax STREAMS_POOL_SIZE = integer [K | M | G]
-- Default value 0
-- Modifiable ALTER SYSTEM
-- Modifiable in a PDB No
-- Range of values Minimum: 0
-- Maximum: operating system-dependent
-- Basic No
--
-- Oracle's Automatic Shared Memory Management feature manages the size of
-- the Streams pool when the SGA_TARGET initialization parameter is set to
-- a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also
-- is set to a nonzero value, then Automatic Shared Memory Management uses
-- this value as a minimum for the Streams pool.
-- Oracle Data Migration Service recommends streams_pool_size to be set at 2G at least.
--
ALTER SYSTEM SET STREAMS_POOL_SIZE=2048M;
--
-- 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 does not have SUPPLEMENTAL LOGGING enabled and an alter database is required.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--
-- User SYSTEM from database DB0216 is unlocked, NO action is required.
--
--
-- 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>
>> Online 이관일 경우
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_4z9_sin
--Database is Container (CDB): YES
--Database CDB Service Name: DB0216_4Z9_SIN.SUB02160159371.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: TRUE
--
--########################################################################################
-- 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: NO (Required value for GoldenGate: YES)
--Database Stream Pool Size Mb: 0 (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.
--
-- Database DB0216 GLOBAL_NAMES is enabled, alter database is required.
--
-- Property Description
-- Parameter type Boolean
-- Default value false
-- Modifiable ALTER SESSION, ALTER SYSTEM
-- Modifiable in a PDB Yes
-- Range of values true | false
-- Basic No
--
-- GLOBAL_NAMES specifies whether a database link is required to have the same
-- name as the database to which it connects.
-- If the value of GLOBAL_NAMES is false, then no check is performed. If you use
-- use or plan to use distributed processing, then Oracle recommends that you set
-- set this parameter to true to ensure the use of consistent naming conventions
-- for databases and links in a networked environment.
-- Oracle DMS recommends global_names to be set at false
--
ALTER SYSTEM SET GLOBAL_NAMES=FALSE;
--
-- Database DB0216 STREAMS_POOL_SIZE current size is 0Mb and it will be modified to 2048Mb
-- The STREAMS_POOL_SIZE value helps determine the size of the Streams pool.
--
-- Property Description
-- Parameter type Big integer
-- Syntax STREAMS_POOL_SIZE = integer [K | M | G]
-- Default value 0
-- Modifiable ALTER SYSTEM
-- Modifiable in a PDB No
-- Range of values Minimum: 0
-- Maximum: operating system-dependent
-- Basic No
--
-- Oracle's Automatic Shared Memory Management feature manages the size of
-- the Streams pool when the SGA_TARGET initialization parameter is set to
-- a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also
-- is set to a nonzero value, then Automatic Shared Memory Management uses
-- this value as a minimum for the Streams pool.
-- Oracle Data Migration Service recommends streams_pool_size to be set at 2G at least.
--
ALTER SYSTEM SET STREAMS_POOL_SIZE=2048M;
--
-- 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 does not have SUPPLEMENTAL LOGGING enabled and an alter database is required.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--
-- 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>
※ 적용하고 난 후에 다시 실행하여 더 이상 적용할 것이 없는지 재확인 필요
ALTER SYSTEM SET GLOBAL_NAMES=FALSE;
ALTER SYSTEM SET STREAMS_POOL_SIZE=2048M;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
DBCS에 임의 테이블 생성 및 데이터 입력
sqlplus "/as sysdba"
show pdbs;
alter session set container=DB0216_PDB1;
--DROP USER DMSMIG1 CASCADE;
CREATE USER DMSMIG1 IDENTIFIED BY "PassW0rd##11";
GRANT CONNECT, RESOURCE TO DMSMIG1;
ALTER USER DMSMIG1 QUOTA UNLIMITED ON USERS;
exit;
sqlplus DMSMIG1@10.10.1.7:1521/DB0216_pdb1.sub02161007471.vcndwhjk.oraclevcn.com
CREATE TABLE dept (
deptno NUMBER(2) PRIMARY KEY,
dname VARCHAR2(14),
loc VARCHAR2(13)
);
INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');
commit;
CREATE TABLE emp (
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)
);
INSERT INTO emp (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 (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 (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 (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 (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 (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 (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 (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 (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 (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;
select count(*) from dept;
select count(*) from emp;
타겟 데이터베이스 생성 ADW
데이터베이스 연결 생성 - 소스 DBCS CDB와 PDB 모두
중요! connection string은 반드시 호스트는 IP로 변경해서 입력
Name : SourceCDBJK
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)
-----------------------------
Name : SourcePDBJK
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)
데이터베이스 연결 생성 - 타겟 ADB
Name : TargetADBJK
Compartment : dwh-resource
Vault in dwh-resource : DMS_Vault-JK
Encryption key in dwh-resource : DMS_Key-JK
Database type : Autonomous Database
Database in dwh-resource : DMS-Target-ADB
마이그레이션 생성 Migration 및 검증 Validation
Name : MigrationJK
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 : SourcePDBJK
Database is pluggable database (PDB) : Checked
Container database connection in dwh-resource : SourceCDBJK
Target database
Database connection in dwh-resource : TargetADBJK
----------------------------
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
마이그레이션 실행 및 결과 확인
기본으로 제외되는 스키마와 오브젝트들 :
Objects and Schemas Excluded by Default
The following object types are always excluded:
GoldenGate administrators: identified in DBA_GOLDENGATE_PRIVILEGES, including ggadmin and c##ggadmin users
If target is Autonomous Data Warehouse Shared Infrastructure: CLUSTER, DB_LINK, INDEXTYPE, STATISTICS
If target is Autonomous Data Warehouse Dedicated Infrastructure, Autonomous Transaction Processing Shared or Dedicated Infrastructure: CLUSTER, DB_LINK, STATISTICS
All other targets: STATISTICS
The following schemas are excluded by default:
Schema is marked as ORACLE_MAINTAINED in SYS.DBA_USERS on the source or target database
Schema is marked as excluded from export in SYS.KU_NOEXP_VIEW on the source database
Schema GGADMIN and C##GGADMIN
참고
https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/run-workshop?p210_wid=856
https://www.oracle.com/my/cloud/database-migration/
https://docs.oracle.com/en-us/iaas/database-migration/index.html
'3. 데이터관리' 카테고리의 다른 글
| OCI DMS 사용하여 데이터 이관하기 (Online) (0) | 2024.02.22 |
|---|---|
| Select AI 쿼리 생성 기능 소개(ADB) (0) | 2024.02.20 |
| MySQL HeatWave 생성, 클러스터 추가 후 Report Query 성능 비교 (0) | 2024.02.04 |
| 데이터 펌프로 자율운영 데이터베이스에 데이터 임포트 (0) | 2023.10.12 |
| DBCS License Upgrade하기 (0) | 2023.02.26 |
댓글