안녕하세요, Surmin입니다.
오늘은 데이터베이스의 export 및 import 과정에서 발생하는 UNDO 메시지에 대해 이야기해 보려고 합니다. 이와 관련된 정보를 공유드리니, 참고하시기 바랍니다.
감사합니다!
실습 환경 설정
-ORACLE 19c 싱글
- 자동 확장(autoextension) 비활성화(undo,temp)
- 대용량 테스트 데이터 준비
- UNDO와 TEMP tablespace 크기를 작게 설정 및 변경
Test
UNDO tablespace 설정
- 초기 크기: 작게 설정 (예: 100MB)
- AUTOEXTEND: OFF
CREATE UNDO TABLESPACE ssmall_undo_tbs
DATAFILE '/u01/app/oracle/oradata/ORA19C/ssmall_undo01.dbf'
SIZE 1M
AUTOEXTEND OFF;TEMP tablespace 설정
- 초기 크기: 작게 설정 (예: 100MB)
- AUTOEXTEND: OFF
CREATE TEMPORARY TABLESPACE ssmall_temp_tbs
TEMPFILE '/u01/app/oracle/oradata/ORA19C/ssmall_temp01.dbf'
SIZE 2M
AUTOEXTEND OFF;현제 undo, temp 확인 및 교체
#현제 설정된 temp 확인
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
---------------------------
TEMP
#현제 설정된 undo 확인
SQL> SHOW PARAMETER undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
#undo TBS 변경
ALTER SYSTEM SET UNDO_TABLESPACE = ssmall_undo_tbs;
#temp TBS변경
SQL> alter database default temporary tablespace ssmall_temp_tbs;
#확인
SQL> SHOW PARAMETER undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string SMALL_UNDO_TBS
SQL> SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
SMALL_TEMP_TBSTest 테이블 및 인덱스 생성 후 데이터 생성
-- 여러 인덱스가 있는 테이블 생성
CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
col1 VARCHAR2(100),
col2 VARCHAR2(100),
col3 DATE
);
-- 추가 인덱스 생성
CREATE INDEX idx_test_1 ON test_table(col1);
CREATE INDEX idx_test_2 ON test_table(col2);
CREATE INDEX idx_test_3 ON test_table(col3);
#확인
-- 라인 크기와 페이지 크기 설정
SET LINESIZE 150
SET PAGESIZE 50
-- 컬럼 포맷 설정
COLUMN index_name FORMAT A30
COLUMN index_type FORMAT A15
COLUMN uniqueness FORMAT A10
COLUMN status FORMAT A10
COLUMN column_name FORMAT A20
COLUMN column_position FORMAT 999
-- 쿼리 실행
SELECT i.index_name,
i.index_type,
i.uniqueness,
i.status,
c.column_name,
c.column_position
FROM user_indexes i
JOIN user_ind_columns c ON i.index_name = c.index_name
WHERE i.table_name = 'TEST_TABLE'
ORDER BY i.index_name, c.column_position;DECLARE
v_start_date DATE := TO_DATE('2023-01-01', 'YYYY-MM-DD');
v_batch_size NUMBER := 10000; -- 한 번에 10,000건씩 처리
v_total_rows NUMBER := 1000000; -- 총 100만 건
v_commit_unit NUMBER := 10000; -- 10,000건마다 커밋
BEGIN
FOR i IN 1..CEIL(v_total_rows/v_batch_size) LOOP
INSERT /*+ APPEND */ INTO test_table (id, col1, col2, col3)
SELECT
(i-1)*v_batch_size + LEVEL AS id,
'VALUE_' || DBMS_RANDOM.STRING('A', 10) AS col1,
'DATA_' || DBMS_RANDOM.STRING('X', 10) AS col2,
v_start_date + DBMS_RANDOM.VALUE(0, 365) AS col3
FROM DUAL
CONNECT BY LEVEL <= LEAST(v_batch_size, v_total_rows - (i-1)*v_batch_size);
COMMIT; -- 배치단위 커밋
END LOOP;
END;
/
#temp 사용량확인
#데이터 생성 시에는 다른 temp 로 잠시 교체하기
SELECT tablespace_name,
tablespace_size/1024/1024 as "Total Size (MB)",
allocated_space/1024/1024 as "Allocated (MB)",
free_space/1024/1024 as "Free (MB)"
FROM dba_temp_free_space;
TABLESPACE_NAME Total Size (MB) Allocated (MB) Free (MB)
------------------------------ --------------- -------------- ----------
SMALL_TEMP_TBS 100 3 98
TEMP 2000 8 1999
#undo 사용량 확인 (EXPIRED,UNEXPIRED 합치면됨)
#데이터 생성 시에는 다른 undo로 잠시 교체하기
SQL> SELECT tablespace_name,
status,
sum(bytes)/1024/1024 MB
FROM dba_undo_extents
GROUP BY tablespace_name, status;
TABLESPACE_NAME STATUS MB
------------------------------ --------- ----------
UNDOTBS1 UNEXPIRED 9.0625
SMALL_UNDO_TBS EXPIRED 92.5
SMALL_UNDO_TBS UNEXPIRED 6.25
UNDOTBS1 EXPIRED 13.1875#덤프 경로확인
SET LINESIZE 150
SET PAGESIZE 50
-- 컬럼 포맷 설정
COLUMN OWNER FORMAT A10
COLUMN DIRECTORY_NAME FORMAT A20
COLUMN DIRECTORY_PATH FORMAT A50
COLUMN ORIGIN_CON_ID FORMAT 999999
SQL> SELECT * FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- -------------------- -------------------------------------------------- -------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/ORA19C/dpdump/ 0
-- Export 실행 (FULL)
expdp \"/ as sysdba\" \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=full_export_test.dmp \
LOGFILE=full_export_test.log \
FULL=y \
PARALLEL=4 \ # 병렬 처리 증가export 결과
- ORA-30036: unable to extend segment by 8 in undo tablespace 'SSMALL_UNDO_TBS' ⇒ undo가 작아서 Error 발생함을 확인
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYS
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-06512: at "SYS.KUPV$FT", line 1744
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 1099
ORA-39080: failed to create queues "KUPC$C_1_20250206164058_0" and "KUPC$S_1_20250206164058_0" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1605
ORA-00604: error occurred at recursive SQL level 2
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7791
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7940
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 631
ORA-30036: unable to extend segment by 8 in undo tablespace 'SSMALL_UNDO_TBS'
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 1138
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 1333
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 627
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8016
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7931
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7780
ORA-06512: at "SYS.KUPC$QUE_INT", line 1553
ORA-06512: at line 1
ORA-06512: at "SYS.KUPC$QUEUE_INT", line 158
ORA-06512: at "SYS.KUPV$FT_INT", line 1002
ORA-06512: at "SYS.KUPV$FT", line 1646
ORA-06512: at "SYS.KUPV$FT", line 1103#import Test
impdp system/oracle directory=pump_dir dumpfile=dev_data.dmp tables=DEVP.NEWD remap_schema=DEVP:USERP remap_table=DEVP.NEWD:NEW remap_tablespace=DEV:PRD table_exists_action=REPLACE nologfile=yimport 결과
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_TABLE_02 for user SYSTEM
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-06512: at "SYS.KUPV$FT", line 1744
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 1099
ORA-39080: failed to create queues "KUPC$C_1_20250206165401_0" and "KUPC$S_1_20250206165401_0" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1605
ORA-00604: error occurred at recursive SQL level 2
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7791
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7940
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 631
ORA-30036: unable to extend segment by 8 in undo tablespace 'SSMALL_UNDO_TBS'
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 1138
ORA-06512: at "SYS.DBMS_PRVTAQIM", line 626
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 8016
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7931
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7780
ORA-06512: at "SYS.KUPC$QUE_INT", line 1553
ORA-06512: at line 1
ORA-06512: at "SYS.KUPC$QUEUE_INT", line 158
ORA-06512: at "SYS.KUPV$FT_INT", line 1002
ORA-06512: at "SYS.KUPV$FT", line 1646
ORA-06512: at "SYS.KUPV$FT", line 1103- 해결 방법
- undo 리사이즈 혹은 AUTOEXTEND 활성화
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORA19C/ssmall_undo01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;다시 시도
expdp \"/ as sysdba\" \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=full_export_test.dmp \
LOGFILE=full_export_test.log \
FULL=y \
PARALLEL=4
-Error 발생
- temp 공간 부족
xport_test.log FULL=y PARALLEL=4
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01652: unable to extend temp segment by 128 in tablespace SSMALL_TEMP_TBS
Resumable stmt: BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_01', 'SYS', 0, 0); END;
Resumable stmt status: SUSPENDED
Resumable stmt start: 02/06/25 17:06:44 stmt suspend: 02/06/25 17:06:44
#확인
SQL> SELECT tablespace_name,
tablespace_size/1024/1024 as "Total Size (MB)",
allocated_space/1024/1024 as "Allocated (MB)",
free_space/1024/1024 as "Free (MB)"
FROM dba_temp_free_space; 2 3 4 5
TABLESPACE_NAME Total Size (MB) Allocated (MB) Free (MB)
------------------------------ --------------- -------------- ----------
SMALL_TEMP_TBS 100 58 99
SSMALL_TEMP_TBS 2 2 0 #꽉참
TEMP 2000 8 1999
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORA19C/ssmall_temp01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED;다시 시도
[oracle@oracle19c dpdump]$ expdp \"/ as sysdba\" \
> DIRECTORY=DATA_PUMP_DIR \
> DUMPFILE=full_export_test.dmp \
> LOGFILE=full_export_test.log \
> FULL=y \
> PARALLEL=4 \
..
Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_02 is:
/u01/app/oracle/admin/ORA19C/dpdump/full_export_test.dmp
Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at Thu Feb 6 17:20:17 2025 elapsed 0 00:04:06
모니터링 결과 확인
SQL> SELECT tablespace_name,
status,
sum(bytes)/1024/1024 MB
FROM dba_undo_extents
GROUP BY tablespace_name, status; 2 3 4 5
TABLESPACE_NAME STATUS MB
------------------------------ ---------- ----------
UNDOTBS1 UNEXPIRED 9.0625
SMALL_UNDO_TBS EXPIRED 51.6875
SMALL_UNDO_TBS UNEXPIRED 13.75
SSMALL_UNDO_TBS UNEXPIRED 10.25
UNDOTBS1 EXPIRED 13.1875
SSMALL_UNDO_TBS EXPIRED .125
6 rows selected.
SQL> SELECT tablespace_name,
tablespace_size/1024/1024 as "Total Size (MB)",
allocated_space/1024/1024 as "Allocated (MB)",
free_space/1024/1024 as "Free (MB)"
FROM dba_temp_free_space;
TABLESPACE_NAME Total Size (MB) Allocated (MB) Free (MB)
------------------------------ --------------- -------------- ----------
SMALL_TEMP_TBS 100 58 99
SSMALL_TEMP_TBS 102 69 101
TEMP 2000 8 1999