Oracle GoldenGate, Oracle Database 설정

Source Database 설정

supplemental log data 생성하기

-- SUPPLEMENTAL LOG
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA ( ALL ) COLUMNS;

반대로 삭제하고 싶을때

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

설정 확인하기

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN,
  2         SUPPLEMENTAL_LOG_DATA_ALL AL,
  3         SUPPLEMENTAL_LOG_DATA_PK  PK,
  4         SUPPLEMENTAL_LOG_DATA_UI  UNIQ,
  5         SUPPLEMENTAL_LOG_DATA_FK  FK
  6         FROM V$DATABASE;

MIN      AL  PK  UNI FK
-------- --- --- --- ---
YES      YES YES YES NO

SQL> 

supplemental log 설정이 끝났다면 archive log 활성화 하기

[oracle@ogg ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 5 12:01:15 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2466250752 bytes
Fixed Size                  8795760 bytes
Variable Size             671091088 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19
SQL> show parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
SQL>