OGG 12c 설치 구성

1. ogg 설치 ( silent 설치 )

  

  ./runInstaller -silent -waitForCompletion -responseFile /tmp/oggcore.rsp

  

   [oggcore]

 

   INSTALL_OPTION=ORA12c

   SOFTWARE_LOCATION=/ogg

   DATABASE_LOCATION=/u01/db

   INVENTORY_LOCATION=/u01/oraInventory

   UNIX_GROUP_NAME=oinstall

 

 

2. 설정

  

1) 테이블 스페이스 생성

   -- create tablespace tbsdat01 datafile "/tmp/test1.dat" size 1g;

 

2) ogg 계정 생성

   create user ogg identified by 'ogg'

   default tablespace tbs_ogg_dat temporary tablespace temp;

 

3) ogg 권한 부여 

   grant connect, resource to ogg;

   grant select any dictionary, select any table to ogg;  

   grant create table to ogg;

   grant flashback any table to ogg;

   grant execute on dbms_flashback to ogg;

   grant execute on utl_file to ogg;

   grant create any table to ogg;

   grant insert any table to ogg;

   grant update any table to ogg;

   grant delete any table to ogg;

   grant drop any table to ogg;

 

   GRANT CREATE SESSION, CONNECT, RESOURCE, ALTER SYSTEM TO ogg;

   EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ogg');

 

   dbms_goldengate_auth.grant_admin_privilege

   grant_admin_privilege('ggadm')  

   grant_admin_privilege('ggadm',grant_select_privileges=>true)  => 11.2.0.3 이전

 

   # check point 정보 기록 테이블 생성

   dblogin userid ogg 

   add checkpointtable

    

 

  4) DB 설정 변경

   SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, supplemental_log_data_min, supplemental_log_data_fk,          supplemental_log_data_all, FORCE_LOGGING FROM v$database;

 

   위에 쿼리 조회시 no로 나타나면 아래 명령어 수행

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 

  5) ogg wallet 구성 (필요하면...)

   add credentialstore

   alter credentialstore add user ogg password xxxxxx alias ogg 

 

  6) DB 파라미터 변경

    enable_goldengate_replication  (parameter)

 

 

  7) DB PK 및 로깅 확인

 

    -- pk, uk check

    select t.table_name from dba_tables t where t.owner='HR' and

    t.table_name in ('COUNTRIES','DEPARTMENTS','EMPLOYEES','JOB_HISTORY','JOBS','LOCATIONS','REGIONS')

    minus

    select c.table_name from dba_constraints c where c.owner='HR' and c.constraint_type in ('P','U')

 

    -- logging check

    select table_name from dba_tables where owner='HR' and logging='NO' and table_name in        ('COUNTRIES','DEPARTMENTS','EMPLOYEES','JOB_HISTORY','JOBS','LOCATIONS','REGIONS')

 

 

     begin

     for r in ( select table_name from dba_tables where owner='HR' and logging='NO' and table_name in      ('COUNTRIES','DEPARTMENTS','EMPLOYEES','JOB_HISTORY','JOBS','LOCATIONS','REGIONS')) loop

         execute immediate 'alter table hr.'|| r.table_name ||' LOGGING';

     end loop;

     end;

 

 

   8) ogg 설정

      테이블 또는 스키마에 대한 로깅 설정

 

      -- 테이블

      add schematrandata hr

 

      -- 스키마

      info schematrandata hr

      info trandata hr.EMPLOYEES

 

      select owner, log_group_name, table_name

      from dba_log_groups where owner = 'HR';

 

       select * from table(logmnr$always_suplog_columns('SCHEMAUSER','HR'));

 

       -- replicat(타켓)쪽 check point 테이블 생성

       ADD CHECKPOINTTABLE

 

 

3. ogg extract/pump/replicat 설정

 

--- exttrail

EXTRACT finance

USERIDALIAS tiger1 

LOGALLSUPCOLS

ENCRYPTTRAIL AES192

EXTTRAIL /ggs/dirdat/lt

SEQUENCE hr.employees_seq;

TABLE hr.*;

 

 

DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH AES256 ENCRYPTKEY mykey1

 

 

 

--- datapump

EXTRACT extpump

USERIDALIAS tiger1

RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2

RMTTRAIL /ggs/dirdat/rt

SEQUENCE hr.employees_seq;      (SOURCECATALOG pdb1)

TABLE hr.*;

 

 

--- replicat

REPLICAT financer

USERIDALIAS tiger2

ASSUMETARGETDEFS

MAP hr.*, TARGET hr2.*;

 

 

4. TDE 적용시 적용

sqlplus sys/as sysdba

"@?/app/oracle/product/orcl111/rdbms/admin/prvtclkm.plb"

GRANT EXECUTE ON DBMS_INTERNAL_CLKM TO psmith;

 

orapki wallet create -wallet . -auto_login[_local] 

mkstore -wrl . -createEntry ORACLE.TDE.HSM.AUTOLOGIN non-empty-string

mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

 

mkstore -wrl . -list

출처: https://semode.tistory.com/261?category=663959 [세모데]