[SQL] Oracle Supplemental logging 종류 및 사용 법 (ALL columns, PK columns)

💡 Supplemental logging 옵션

오라클 본연의 Redo Log는 DML이 발생하는 경우 오직 변경된 컬럼의 데이터에 대해서만 Undo(변경 전 데이터)와 Redo(변경 후 데이터) 정보를 남긴다.(=Physiological Logging) 반면, Supplemental Logging은 DML 발생시 리두 로그에 추가적인 데이터를 남긴다. 리두 로그는 기본적으로 Instance Recovery 혹은 Media Recovery를 위해서 사용한다.

● Disable : redo log에 변경된 컬럼 정보만 기록함 (default)

● Enable : 하나의 컬럼이 변경되더라도 전체 row의 정보를 모두 redo log에 저장 

 * enable일 경우 redo log의 양이 커짐

 

Supplemental Logging 종류

 

 

 

Supplemental 특징 및 사용 방법

 

1. supplemental logging 기능 상태 확인

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO


SQL> 
set line 200
col S_PK for a10
col S_UI for a10
col S_FK for a10
select SUPPLEMENTAL_LOG_DATA_MIN S_MIN, 
SUPPLEMENTAL_LOG_DATA_PK S_PK,
SUPPLEMENTAL_LOG_DATA_UI S_UI,
SUPPLEMENTAL_LOG_DATA_FK S_FK,
SUPPLEMENTAL_LOG_DATA_ALL S_ALL from v$database;

S_MIN    S_PK       S_UI       S_FK       S_A
-------- ---------- ---------- ---------- ---
NO       NO         NO         NO         NO


SQL> select * from DBA_SUPPLEMENTAL_LOGGING;

MIN PRI UNI FOR ALL PRO SUB
--- --- --- --- --- --- ---
NO  NO  NO  NO  NO  NO  NO

 

 

2. 최소 단위 supplemental logging 기능 활성화 및 비활성화

 

(1) 최소 단위 supplemental logging 기능 활성화

SQL> alter database add supplemental log data;
 
Database altered.

 

(2) 활성화 확인

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES


SQL> 
set line 200
col S_PK for a10
col S_UI for a10
col S_FK for a10
select SUPPLEMENTAL_LOG_DATA_MIN S_MIN, 
SUPPLEMENTAL_LOG_DATA_PK S_PK,
SUPPLEMENTAL_LOG_DATA_UI S_UI,
SUPPLEMENTAL_LOG_DATA_FK S_FK,
SUPPLEMENTAL_LOG_DATA_ALL S_ALL from v$database;

S_MIN    S_PK       S_UI       S_FK       S_A
-------- ---------- ---------- ---------- ---
YES      NO         NO         NO         NO


SQL> select * from DBA_SUPPLEMENTAL_LOGGING;

MIN PRI UNI FOR ALL PRO SUB
--- --- --- --- --- --- ---
YES NO  NO  NO  NO  NO  NO

 

(3) 최소 min 단위 supplemental logging 기능 비활성화

SQL>alter database drop supplemental log data;

Database altered.

* 비활성화하고 확인시 1. 과 같은 결과를 확인 할 수 있음

 

 

3. table 단위 supplemental logging 기능 활성화 및 비활성화

 

(1) table 단위 supplemental logging 기능 활성화

SQL> alter database add supplemental log data (ALL) columns;

Database altered.

 

(2) 활성화 확인

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
IMPLICIT


SQL> 
set line 200
col S_PK for a10
col S_UI for a10
col S_FK for a10
select SUPPLEMENTAL_LOG_DATA_MIN S_MIN, 
SUPPLEMENTAL_LOG_DATA_PK S_PK,
SUPPLEMENTAL_LOG_DATA_UI S_UI,
SUPPLEMENTAL_LOG_DATA_FK S_FK,
SUPPLEMENTAL_LOG_DATA_ALL S_ALL from v$database;

S_MIN    S_PK       S_UI       S_FK       S_A
-------- ---------- ---------- ---------- ---
IMPLICIT NO         NO         NO         YES


SQL> select * from DBA_SUPPLEMENTAL_LOGGING;

MIN PRI UNI FOR ALL PRO SUB
--- --- --- --- --- --- ---
YES NO  NO  NO  YES NO  NO

 

(3) 최소 min 단위 supplemental logging 기능 비활성화

SQL>alter database drop supplemental log data;

Database altered.

* 비활성화하고 확인시 1. 과 같은 결과를 확인 할 수 있음

 

 

4. table 단위 supplemental logging 기능 활성화 및 비활성화

 

(1) table 단위 supplemental logging 기능 활성화

SQL> alter table KING.supplier add supplemental log data (ALL) columns;

Table altered.

 

(2) 활성화 확인

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO


SQL> 
set line 200
col S_PK for a10
col S_UI for a10
col S_FK for a10
select SUPPLEMENTAL_LOG_DATA_MIN S_MIN, 
SUPPLEMENTAL_LOG_DATA_PK S_PK,
SUPPLEMENTAL_LOG_DATA_UI S_UI,
SUPPLEMENTAL_LOG_DATA_FK S_FK,
SUPPLEMENTAL_LOG_DATA_ALL S_ALL from v$database;

S_MIN    S_PK       S_UI       S_FK       S_A
-------- ---------- ---------- ---------- ---
NO       NO         NO         NO         NO


SQL> select * from DBA_SUPPLEMENTAL_LOGGING;

MIN PRI UNI FOR ALL PRO SUB
--- --- --- --- --- --- ---
NO  NO  NO  NO  NO  NO  NO

* 활성화 했지만 위 테이블로 확인 결과 NO로 확인 됨

 

● 테이블 단위로 활성화 할 경우 확인 테이블

SQL> select * from dba_log_groups;

OWNER                          LOG_GROUP_NAME                 TABLE_NAME                     LOG_GROUP_TYPE      ALWAYS      GENERATED
------------------------------ ------------------------------ ------------------------------ ------------------- ----------- --------------
SYS                            SEQ$_LOG_GRP                   SEQ$                           USER LOG GROUP      ALWAYS      USER NAME
SYS                            IDNSEQ$_LOG_GRP                IDNSEQ$                        USER LOG GROUP      ALWAYS      USER NAME
SYS                            ENC$_LOG_GRP                   ENC$                           USER LOG GROUP      ALWAYS      USER NAME
GSMADMIN_INTERNAL              SHARD_TS$LOG_GRP               SHARD_TS                       USER LOG GROUP      ALWAYS      USER NAME
KING                           SYS_C007549                    KING_EXAMPLE_TB002             ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007550                    KING_TABLE                     ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007551                    KING_EXAMPLE_TB                ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           ARKCDC_96763                   MEMBER                         USER LOG GROUP      ALWAYS      USER NAME
KING                           ARKCDC_96765                   BOARD                          USER LOG GROUP      ALWAYS      USER NAME
KING                           SYS_C007556                    SAMPLE_T                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007557                    SAMPLE_R                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007558                    SAMPLE_C                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007559                    EMP                            ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007560                    OGG_20240507                   ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007561                    TT                             ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007562                    SAMPLE_E                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C007563                    SAMPLE_Z                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME
KING                           SYS_C008350                    SUPPLIER                       ALL COLUMN LOGGING  ALWAYS      GENERATED NAME

18 rows selected.

 

(3) 최소 min 단위 supplemental logging 기능 비활성화

SQL> alter table KING.supplier drop supplemental log data (ALL) columns;

Database altered.

* 비활성화하고 확인시 1. 과 같은 결과를 확인 할 수 있음

 

 

 

참고

1. https://dbfactory86.tistory.com/22

2. https://positivemh.tistory.com/415

3. https://bestugi.tistory.com/21