Oracle 데이터 복제

샘플 스크립트 createuser-ora-nodba.sql

이 스크립트를 복사하고 꺾쇠괄호 안의 값을 대체하십시오. Oracle에서 데이터를 복제하기 위한 Oracle 데이터베이스 사용자를 작성하려면 이 스크립트를 실행하십시오.

/* _______________________________________________________ {COPYRIGHT-TOP} _____
** Licensed Materials - Property of IBM
** IBM Data Replication Cartridge for IBM Cloud Pak for Data
** 5900ASF
**
** (c) Copyright IBM Corp. 2001, 2022 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
* ________________________________________________________ {COPYRIGHT-END} _____*/

--This script does not grant dba to the data replication user.

-- create user
CREATE user <user> identified by <password>
default tablespace <tablespace_name> temporary tablespace <temporary_tablespace_name>;

-- Grant basic roles
grant connect to <user>;
grant resource to <user>;
grant select_catalog_role to <user>;
grant unlimited tablespace to <user>;

-- Table DDL permissions
grant create any table to <user>;
grant alter any table to <user>;
grant drop any table to <user>;
grant lock any table to <user>;

-- Table DML permissions
grant select any table to <user>;
grant flashback any table to <user>;
grant insert any table to <user>;
grant update any table to <user>;
grant delete any table to <user>;

-- Index and view DDL permissions
grant create any index to <user>;
grant alter any index to <user>;
grant drop any index to <user>;
grant create any view to <user>;
grant drop any view to <user>;

-- Sequence and trigger DDL permissions
grant create any sequence to <user>;
grant create any trigger to <user>;

-- Procedure permissions
grant create any procedure to <user>;
grant execute any procedure to <user>;

-- Permission to perform select on the v_$ tables
grant select any dictionary to <user>;

-- General system views
grant select on sys.v_$database to <user>;
grant select on sys.v_$controlfile to <user>;
grant select on sys.v_$version to <user>;
grant select on sys.nls_database_parameters to <user>;

-- Archive and redo logs
grant select on sys.v_$log to <user>;
grant select on sys.v_$logfile to <user>;
grant select on sys.v_$archived_log to <user>;
grant select on sys.v_$log_history to <user>;

-- Sessions and transactions
grant alter session to <user>;
grant select on sys.v_$session to <user>;
grant select on sys.gv_$session to <user>;
grant select on sys.v_$transaction to <user>;
grant select on sys.v_$mystat to <user>;

-- Tables, indexes, columns and related views
grant select on sys.all_coll_types to <user>;
grant select on sys.all_type_attrs to <user>;
grant select on sys.dba_tables to <user>;
grant select on sys.dba_tab_comments to <user>;
grant select on sys.dba_tab_columns to <user>;
grant select on sys.dba_col_comments to <user>;
grant select on sys.dba_indexes to <user>;
grant select on sys.dba_ind_columns to <user>;
grant select on sys.all_constraints to <user>;
grant select on sys.dba_constraints to <user>;
grant select on sys.all_cons_columns to <user>;
grant select on sys.dba_cons_columns to <user>;
grant select on sys.tab$ to <user>;
grant select on sys.ind$ to <user>;
grant select on sys.lob$ to <user>;
grant select on sys.col$ to <user>;
grant select on sys.icol$ to <user>;
grant select on sys.coltype$ to <user>;
grant select on sys.attrcol$ to <user>;
grant select on sys.ccol$ to <user>;
grant select on sys.cdef$ to <user>;

-- Miscellaneous other objects
grant select on sys.obj$ to <user>;
grant select on sys.dba_mviews to <user>;
grant select on sys.dba_objects to <user>;
grant select on sys.dba_sequences to <user>;
grant select on sys.hist_head$ to <user>;
grant select on sys.resource_cost to <user>;

-- Storage
grant select on sys.dba_tablespaces to <user>;
grant select on sys.dba_rollback_segs to <user>;

-- Permissions
grant select on sys.dba_users to <user>;
grant select on sys.dba_sys_privs to <user>;
grant select on sys.dba_tab_privs to <user>;
grant select on sys.dba_profiles to <user>;
grant select on sys.dba_roles to <user>;
grant select on sys.user$ to <user>;
grant select on user_role_privs to <user>;

exit;

샘플 스크립트 createuser-ora-xstream.sql

이 스크립트를 복사하고 꺾쇠괄호 안의 값을 대체하십시오. createuser-ora-xstream.sql를 실행하기 전에 샘플 스크립트 createuser-ora-nodba.sql 를 실행하십시오. 복제하는 스키마와 동일한 데이터베이스에서 로그가 처리되는 로컬 캡처를 사용하도록 Data Replication 를 구성하는 경우, 동일한 데이터베이스에 대해 createuser-ora-nodba.sql 및 createuser-ora-xstream.sql 를 실행하고 <user>에 대해 동일한 값을 사용하십시오. Oracle XStream 프로세스 로그를 복제하는 스키마와 별도의 데이터베이스에 두려면 선택적으로 다른 사용자를 지정하고 데이터베이스에 대해 스크립트를 실행하십시오.

/* _______________________________________________________ {COPYRIGHT-TOP} _____
** Licensed Materials - Property of IBM
** IBM Data Replication Cartridge for IBM Cloud Pak for Data
** 5900ASF
**
** (c) Copyright IBM Corp. 2001, 2022 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
* ________________________________________________________ {COPYRIGHT-END} _____*/

-- This script is a continuation of createuser-ora-nodba.sql script with
-- additional changes needed for data replication user as well as XStream Log
-- Processing Database user. Process all needed privileges from the script
-- createuser-ora-nodba.sql prior to continuing with this script.

-- XStream privileges on data replication User

-- Expected to be used for DBMS_CAPTURE_ADM.BUILD
-- to define XStream Capture initialization points

grant execute on DBMS_CAPTURE_ADM to <user>;

-- Optional privilege that is expected to be used on
-- the target engine required for Recursion Prevention
-- in Bidirectional replication.

grant execute on DBMS_XSTREAM_ADM to <user>;

-- Enable enable_goldengate_replication

-- You need to set the enable_goldengate_replication system parameter on your source
-- database to enable XStream replication capability in Oracle.

ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;

-- to enable the enable_goldengate_replication system parameter.
-- All instances in Oracle RAC must have the same setting. Refer to Oracle documentation
-- for further understanding on the impact of enabling this parameter on your database.

-- Setting up Oracle XStream user account.

-- Most work in data replication is done using the Oracle user account.
-- When data replication needs to interact with Oracle XStream it will use the Oracle XStream account.
-- An Oracle XStream account needs sufficient privileges to comply with Oracle XStream user requirements.
-- In a Pluggable database environment, the Oracle user account has all the granted access to the
-- Pluggable database when replication is configured for a pluggable database, whereas the XStream
-- user account has to be defined in the root container database as per Oracle XStream requirements.
-- All Oracle XStream related objects are defined and managed in the container database in this case.

CALL DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE(
  grantee                 => <Log Processing Database User>,
  privilege_type          => 'CAPTURE');

exit;

1. 로그 처리 데이터베이스를 구성하십시오. Data Replication 자산을 작성할 때 Data Replication 에서 사용할 Oracle 로그 처리 데이터베이스의 이름을 묻는 프롬프트가 표시됩니다. 이 데이터베이스는 Data Replication 이 Oracle XStream API에서 제공하는 논리 변경 레코드를 처리하기 위해 연결하고 첨부하는 XStream 아웃바운드 데이터베이스를 제공합니다. 로컬 Oracle XStream 캡처 및 아웃바운드 서버의 경우 이는 이전과 동일한 데이터베이스입니다. 플러그 가능한 데이터베이스의 경우 로그 처리 데이터베이스는 루트 컨테이너 데이터베이스입니다. 다운스트림 캡처의 경우 로그 처리 데이터베이스는 다운스트림 데이터베이스입니다. 로그 처리 데이터베이스에는 enable_goldengate_replication도 필요합니다.

2. 선택사항: 다운스트림 로그 처리 데이터베이스를 구성하십시오. 실시간 다운스트림 캡처 또는 아카이브 로그 다운스트림 캡처를 구성하려면 여러 단계가 필요합니다. Oracle XStream 문서를 검토하여 데이터베이스 링크 및 로그 파일 전송을 구성하십시오.

3. Oracle XStream 사용자 계정을 설정하십시오. Data Replication 의 대부분의 작업은 Oracle 사용자 계정을 사용하여 수행됩니다. Data Replication 가 Oracle XStream과 상호작용해야 하는 경우, Oracle XStream 계정을 사용합니다. Oracle XStream 계정에는 Oracle XStream 사용자 요구사항을 준수하기 위한 충분한 권한이 필요합니다. 플러그 가능한 데이터베이스 환경에서 Oracle 사용자 계정은 플러그 가능한 데이터베이스에 대해 복제가 구성될 때 플러그 가능한 데이터베이스에 대해 부여된 모든 액세스 권한을 갖습니다. 그러나 XStream 사용자 계정은 Oracle XStream 요구사항에 따라 루트 컨테이너 데이터베이스에 정의되어야 합니다. 이 경우 모든 Oracle XStream 관련 컴포넌트는 루트 컨테이너 데이터베이스에서 정의되고 관리됩니다. DBMS_XSTREAM_AUTH.GRANT_ADMIN_PRIVILEGE XStream 사용자 계정 권한을 부여합니다. Oracle XStream 아웃바운드 데이터베이스 신임 정보는 대소문자를 구분합니다. 자세한 내용은 Oracle 설명서를 참조하세요.

4. 스키마를 작성하거나 사용자 데이터베이스 메타데이터 테이블에 대한 기존 스키마를 선택하십시오. Data Replication를 구성할 때 이 스키마를 지정해야 합니다.