Online DDL(add column default + not null) 시나리오

최근 실시간 트랜잭션이 있는 테이블에 DDL을 중단없이 할 수 있을까?에 대한 질문을 받고 고민을 하게되었습니다.

단순 add면 상관없지만 default + not null이 있는 경우에는 작업 중에 트랜잭션은 대기해야 하는 상황이 발생하기 때문입니다.

단순 리오그 방법 중 하나로 정리를 했었는데, 사실 본래의 기능은 ONLINE으로 테이블의 구조를 변경할 수 있게 하는 기능이었습니다. 이것을 이용하여 가상의 시나리오를 진행해보도록 하겠습니다.

 

 

참고 : 이전의 제가작성한 redefinition 글

https://bae9086.tistory.com/81

TABLE_REDEFINITION

https://bae9086.tistory.com/400

Online Redefinition시 Block 감소량 비교

 

Oracle  Redefinition의 작동원리는 MVIEW를 활용하여 임시테이블을 만들고 그 임시테이블과 원본테이블의 변경사항을 실시간으로 적용시킬 수 있습니다. 그 중 MVIEW LOG를 이용하는데 이는 캡쳐형식으로 원본과 임시테이블의 데이터를 동기화 합니다.

 

아래는 MVIEW의 글입니다.

참고 : https://bae9086.tistory.com/525

Materialized View (MView)

 

 

시나리오.

-----------------------------------------------------------------

원본테이블 REF_ORIGINAL

임시테이블 REF_NEW

추가할 컬럼 E varchar2(30) default 'DEF' not null

------------------------------------------------------------------

1.세션1에서는 1초마다 데이터를 INSERT하는 자동 쉘 수행.

2.세션2에서는 Redefinition기능을 사용하여 임시테이블 생성하여 default와 not null을 추가

3.임시테이블과 원본테이블 sync

4. 전환

입니다.

 

1. 세션1에서는 1초마다 데이터를 INSERT하는 자동 쉘 수행

CREATE TABLE REF_ORIGINAL(A VARCHAR2(20) primary key, B NUMBER, C NUMBER,D VARCHAR2(30));

 

OS상에 쉘파일 생성

vi auto_ins.sh
sqlplus -s ysbae/oracle@PDB <<EOF
set timing on
insert into REF_ORIGINAL(A,B,C,D)
select dbms_random.string('x',10),
       timestamp_to_scn(sysdate),
           mod(timestamp_to_scn(sysdate),5),
           'ABC'||dbms_random.string('x',10)
from dual;
commit;
exit
EOF

 

while true문을 사용하여 1초마다 수행하게 수행

$ while true;do sh auto_ins.sh sleep 1; done;
...
Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.01

Commit complete.

...

 

2.세션2에서는 Redefinition기능을 사용하여 임시테이블 생성하여 default와 not null을 추가

 

임시테이블 생성 

컬럼을 추가한 상태의 임시테이블을 생성합니다.

CREATE TABLE REF_NEW (A VARCHAR2(20) primary key, B NUMBER, C NUMBER,D VARCHAR2(30) , E VARCHAR2(30)  DEFAULT 'DEF' NOT NULL);

 

redefinition 가능여부 체크

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname => 'YSBAE',
    tname => 'REF_ORIGINAL',
    options_flag => DBMS_REDEFINITION.CONS_USE_ROWID -- 실시간 DML 가능하게 함
  );
END;
/


PL/SQL procedure successfully completed.

 

에러없이 수행되면 가능하다고 볼 수 있습니다.

PK가 없는 테이블일 시 아래에러가 발생할 수 있습니다.

ORA-12089: cannot online redefine table "YSBAE"."REF_ORIGINAL" with no primarykey
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5943
ORA-06512: at line 1

 

테이블 건수 확인

SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;  2    3

  COUNT(*)
----------
      5604
         0

 

redefinition 수행

col_mapping 부분이 중요한데 앞의 A는 원본테이블의 컬럼 뒤의 A는 임시테이블의 컬럼입니다. 저는 추가한 컬럼 이외의 컬럼은 이름과 구조가 같으므로 모두 동일한 이름으로 매핑해줍니다.

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'YSBAE',
    orig_table => 'REF_ORIGINAL',
    int_table => 'REF_NEW',
    col_mapping => 'A A, B B, C C, D D',
    options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
  );
END;
/

 

건수확인시 비슷하게 건수가 들어왔습니다.

SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;  2    3

  COUNT(*)
----------
      6163
      6121

 

임시테이블과 동일한 이름의 MVIEW가 생겼고, 원본테이블을 쿼리형식으로 데이터를 가지고있습니다. 

I_SNAP$ 으로시작되는 인덱스도 자동으로 생성되는데 M_ROW$$컬럼인 인덱스가 생성되어 있습니다.

SELECT * FROM dba_mviews;
OWNER|MVIEW_NAME|CONTAINER_NAME|QUERY                                                                 |QUERY_LEN|UPDATABLE|UPDATE_LOG|MASTER_ROLLBACK_SEG|MASTER_LINK|REWRITE_ENABLED|REWRITE_CAPABILITY|REFRESH_MODE|REFRESH_METHOD|BUILD_MODE|FAST_REFRESHABLE|LAST_REFRESH_TYPE|LAST_REFRESH_DATE|LAST_REFRESH_END_TIME|STALENESS|AFTER_FAST_REFRESH|UNKNOWN_PREBUILT|UNKNOWN_PLSQL_FUNC|UNKNOWN_EXTERNAL_TABLE|UNKNOWN_CONSIDER_FRESH|UNKNOWN_IMPORT|UNKNOWN_TRUSTED_FD|COMPILE_STATE|USE_NO_INDEX|STALE_SINCE|NUM_PCT_TABLES|NUM_FRESH_PCT_REGIONS|NUM_STALE_PCT_REGIONS|SEGMENT_CREATED|EVALUATION_EDITION|UNUSABLE_BEFORE|UNUSABLE_BEGINNING|DEFAULT_COLLATION|ON_QUERY_COMPUTATION|
-----+----------+--------------+----------------------------------------------------------------------+---------+---------+----------+-------------------+-----------+---------------+------------------+------------+--------------+----------+----------------+-----------------+-----------------+---------------------+---------+------------------+----------------+------------------+----------------------+----------------------+--------------+------------------+-------------+------------+-----------+--------------+---------------------+---------------------+---------------+------------------+---------------+------------------+-----------------+--------------------+
YSBAE|REF_NEW   |REF_NEW       |select A A, B B, C C, D D from "YSBAE"."REF_ORIGINAL"   "REF_ORIGINAL"|       70|N        |          |                   |           |               |                  |DEMAND      |FAST          |PREBUILT  |DIRLOAD_DML     |                 |                 |                     |ERROR    |                  |                |                  |                      |                      |              |                  |ERROR        |N           |           |             0|                     |                     |YES            |                  |               |                  |USING_NLS_COMP   |N                   |

SELECT * FROM dba_indexes WHERE table_name IN ('REF_ORIGINAL','REF_NEW');

OWNER|INDEX_NAME     |INDEX_TYPE|TABLE_OWNER|TABLE_NAME  |TABLE_TYPE|UNIQUENESS|COMPRESSION|PREFIX_LENGTH|TABLESPACE_NAME|INI_TRANS|MAX_TRANS|INITIAL_EXTENT|NEXT_EXTENT|MIN_EXTENTS|MAX_EXTENTS|PCT_INCREASE|PCT_THRESHOLD|INCLUDE_COLUMN|FREELISTS|FREELIST_GROUPS|PCT_FREE|LOGGING|BLEVEL|LEAF_BLOCKS|DISTINCT_KEYS|AVG_LEAF_BLOCKS_PER_KEY|AVG_DATA_BLOCKS_PER_KEY|CLUSTERING_FACTOR|STATUS|NUM_ROWS|SAMPLE_SIZE|LAST_ANALYZED          |DEGREE |INSTANCES|PARTITIONED|TEMPORARY|GENERATED|SECONDARY|BUFFER_POOL|FLASH_CACHE|CELL_FLASH_CACHE|USER_STATS|DURATION|PCT_DIRECT_ACCESS|ITYP_OWNER|ITYP_NAME|PARAMETERS|GLOBAL_STATS|DOMIDX_STATUS|DOMIDX_OPSTATUS|FUNCIDX_STATUS|JOIN_INDEX|IOT_REDUNDANT_PKEY_ELIM|DROPPED|VISIBILITY|DOMIDX_MANAGEMENT|SEGMENT_CREATED|ORPHANED_ENTRIES|INDEXING|AUTO|CONSTRAINT_INDEX|
-----+---------------+----------+-----------+------------+----------+----------+-----------+-------------+---------------+---------+---------+--------------+-----------+-----------+-----------+------------+-------------+--------------+---------+---------------+--------+-------+------+-----------+-------------+-----------------------+-----------------------+-----------------+------+--------+-----------+-----------------------+-------+---------+-----------+---------+---------+---------+-----------+-----------+----------------+----------+--------+-----------------+----------+---------+----------+------------+-------------+---------------+--------------+----------+-----------------------+-------+----------+-----------------+---------------+----------------+--------+----+----------------+
YSBAE|SYS_C007610    |NORMAL    |YSBAE      |REF_NEW     |TABLE     |UNIQUE    |DISABLED   |             |USERS          |        2|      255|         65536|    1048576|          1| 2147483645|            |             |              |         |               |      10|YES    |     1|         24|         6121|                      1|                      1|             6030|VALID |    6121|       6121|2025-05-13 09:48:52.000|DEFAULT|DEFAULT  |NO         |N        |Y        |N        |DEFAULT    |DEFAULT    |DEFAULT         |NO        |        |                 |          |         |          |YES         |             |               |              |NO        |NO                     |NO     |VISIBLE   |                 |YES            |NO              |FULL    |NO  |YES             |
YSBAE|I_SNAP$_REF_NEW|NORMAL    |YSBAE      |REF_NEW     |TABLE     |UNIQUE    |DISABLED   |             |USERS          |        2|      255|         65536|    1048576|          1| 2147483645|            |             |              |         |               |      10|YES    |     1|         25|         6121|                      1|                      1|              165|VALID |    6121|       6121|2025-05-13 09:48:50.000|1      |1        |NO         |N        |N        |N        |DEFAULT    |DEFAULT    |DEFAULT         |NO        |        |                 |          |         |          |YES         |             |               |              |NO        |NO                     |NO     |VISIBLE   |                 |YES            |NO              |FULL    |NO  |NO              |
YSBAE|SYS_C007608    |NORMAL    |YSBAE      |REF_ORIGINAL|TABLE     |UNIQUE    |DISABLED   |             |USERS          |        2|      255|         65536|    1048576|          1| 2147483645|            |             |              |         |               |      10|YES    |      |           |             |                       |                       |                 |VALID |        |           |                       |1      |1        |NO         |N        |Y        |N        |DEFAULT    |DEFAULT    |DEFAULT         |NO        |        |                 |          |         |          |NO          |             |               |              |NO        |NO                     |NO     |VISIBLE   |                 |YES            |NO              |FULL    |NO  |YES             |

SELECT * FROM dba_ind_columns WHERE index_name='I_SNAP$_REF_NEW';

INDEX_OWNER|INDEX_NAME     |TABLE_OWNER|TABLE_NAME|COLUMN_NAME|COLUMN_POSITION|COLUMN_LENGTH|CHAR_LENGTH|DESCEND|COLLATED_COLUMN_ID|
-----------+---------------+-----------+----------+-----------+---------------+-------------+-----------+-------+------------------+
YSBAE      |I_SNAP$_REF_NEW|YSBAE      |REF_NEW   |M_ROW$$    |              1|          255|        255|ASC    |                  |


select * From dba_mview_logs;


LOG_OWNER|MASTER      |LOG_TABLE         |LOG_TRIGGER|ROWIDS|PRIMARY_KEY|OBJECT_ID|FILTER_COLUMNS|SEQUENCE|INCLUDE_NEW_VALUES|PURGE_ASYNCHRONOUS|PURGE_DEFERRED|PURGE_START|PURGE_INTERVAL|LAST_PURGE_DATE        |LAST_PURGE_STATUS|NUM_ROWS_PURGED|COMMIT_SCN_BASED|STAGING_LOG|
---------+------------+------------------+-----------+------+-----------+---------+--------------+--------+------------------+------------------+--------------+-----------+--------------+-----------------------+-----------------+---------------+----------------+-----------+
YSBAE    |REF_ORIGINAL|MLOG$_REF_ORIGINAL|           |YES   |NO         |NO       |NO            |NO      |NO                |YES               |NO            |           |              |2025-05-14 15:08:10.000|                0|              0|YES             |NO         |

 

3.임시테이블과 원본테이블 sync

sync명령어는 언제든 계속 날릴 수 있습니다. 날리면 그때 시점으로 원본과 임시테이블을 동기화 해줍니다.

BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('YSBAE', 'REF_ORIGINAL', 'REF_NEW');
END;
/

 

SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;  2    3

  COUNT(*)
----------
      6636
      6632

 

4. 전환(마무리)

REF_ORIGINAL이 E컬럼이 정상적으로 추가된것을 확인하였습니다.

다만 아무리 sync를 자주 날리고 근접하게 데이터를 동기화한다고 해도 실시간으로 들어오는 경우에는, 전환시에 데이터 유실이 조금 있을 수 밖에 없는거 같습니다. 그래도 중단없이 작업할 수 있는 강력한 방법임에는 확인을 하였습니다.

SQL> exec dbms_redefinition.finish_redef_table('YSBAE','REF_ORIGINAL','REF_NEW');


PL/SQL procedure successfully completed.

 

SELECT * FROM REF_ORIGINAL rn ;

A         |B      |C|D            |E  |
----------+-------+-+-------------+---+
8MFZN799CS|3895142|2|ABCR6QWHCW8X1|DEF|
J3RL3VHYCN|3895168|3|ABC7DTBLOQH3D|DEF|
QFDP93AYST|3895168|3|ABCT4TSHSFJT6|DEF|
...


SQL> select count(*) from REF_ORIGINAL
UNION ALL
select count(*) from REF_NEW;

  COUNT(*)
----------
      7475
      7457

 

Online Redefinition은 "MVIEW 기반 CDC (Change Data Capture) 기술을 재정의용으로 활용한 것"이라고 볼 수 있습니다.