[B&R] ORA-39083 ORA-00439 oracle 파티션 datapump (Enterprise Edition to Standard Edition2)

Oracle/Backup&Recovery

[B&R] ORA-39083 ORA-00439 oracle 파티션 datapump (Enterprise Edition to Standard Edition2)

신문지한장 2024. 12. 2. 17:21

OS : CentOS 7.9

DB(AS-IS) : Oracle Database 19c(19.23.0) - EE

DB(TO-BE) : Oracle Database 19c(19.23.0) - SE2

1. 파티션 현황 확인 ( AS-IS )

SQL> col OWNER for a30
select OWNER, OBJECT_TYPE, COUNT(*) from dba_objects where owner='TESTUSER'
group by owner, object_type
order by owner;

OWNER                          OBJECT_TYPE               COUNT(*)
------------------------------ ----------------------- ----------
TESTUSER                       TABLE PARTITION                 12
TESTUSER                       TABLE                            1

2. expdp 수행 ( AS-IS )

[orclej1]oradb1:/backup/orclej1> :expdp system/manager0 directory=datapump dumpfile=expdp_full_20241202.dmp logexpdp_full_20241202.log full=y

Export: Release 19.0.0.0.0 - Production on Mon Dec 2 14:30:01 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=datapump dumpfile=expdp_full_20241202.dmp lo=expdp_full_20241202.log full=y

3. impdp 수행 ( TO-BE )

[seorcl19]se-ejtest:/home/oracle> impdp system/manager0 directory=datapump dumpfile=expdp_full_20241202.dmp logfi   le=impdp_full_20241202.log schemas=TESTUSER

Import: Release 19.0.0.0.0 - Production on Mon Dec 2 14:38:20 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=datapump dumpfile=expdp_full_20241202.dmp lo   gfile=impdp_full_20241202.log schemas=TESTUSER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"TESTUSER"."PTABLE" failed to create with error:
ORA-00439: feature not enabled: Partitioning

Failing sql is:
CREATE TABLE "TESTUSER"."PTABLE" ("COL1" VARCHAR2(8 BYTE), "COL2" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA   NS 255  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TESTTBS"  PARTITIO   N BY RANGE ("COL1")  (PARTITION "P202101"  VALUES LESS THAN ('20210200') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN   S 255  NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0    FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TESTT   BS"  READ WRITE , PARTITION "P202102"  VALUES LESS THAN ('20210300') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25   5  NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREE   LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TESTTBS"     READ WRITE , PARTITION "P202103"  VALUES LESS THAN ('20210400') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  N   OCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELIST   S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TESTTBS"  REA   D WRITE , PARTITION "P202104"  VALUES LESS THAN ('20210500') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOM   PRESS LOGGING  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1    FREELIST GROUPS 1 BUFFER_POOL D

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Mon Dec 2 14:38:26 2024 elapsed 0 00:00:06

● 해당 에러 발생

* ORA-39083: Object type TABLE:"TESTUSER"."PTABLE" failed to create with error:
* ORA-00439: feature not enabled: Partitioning

: SE2에서는 Partitioning 기능을 제공하지 않아 발생하는 에러

4. "Partition_options=merge" 해당 옵션 추가 후 impdp 재수행 ( TO-BE )

[seorcl19]se-ejtest:/oracle/app/oracle/product/19.0.0/rdbms/lib> impdp system/manager0 directory=datapump dumpfile=ele=impdp_full_20241202.log schemas=TESTUSER Partition_options=merge

Import: Release 19.0.0.0.0 - Production on Mon Dec 2 15:09:05 2024
Version 19.23.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** directory=datapump dumpfile=expdp_full_20241202.dmp logfichemas=TESTUSER Partition_options=merge
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"TESTUSER" already exists

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "TESTUSER"."PTABLE":"P202102"               6.566 MB  364239 rows
. . imported "TESTUSER"."PTABLE":"P202109"               6.564 MB  364136 rows
. . imported "TESTUSER"."PTABLE":"P202104"               6.564 MB  364113 rows
. . imported "TESTUSER"."PTABLE":"P202103"               6.563 MB  364087 rows
. . imported "TESTUSER"."PTABLE":"P202105"               6.562 MB  363991 rows
. . imported "TESTUSER"."PTABLE":"P202106"               6.559 MB  363841 rows
. . imported "TESTUSER"."PTABLE":"P202108"               6.551 MB  363363 rows
. . imported "TESTUSER"."PTABLE":"P202107"               6.550 MB  363327 rows
. . imported "TESTUSER"."PTABLE":"P202111"               6.547 MB  363156 rows
. . imported "TESTUSER"."PTABLE":"P202110"               6.537 MB  362620 rows
. . imported "TESTUSER"."PTABLE":"P202101"               3.278 MB  181693 rows
. . imported "TESTUSER"."PTABLE":"P202112"               3.273 MB  181434 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Mon Dec 2 15:09:13 2024 elapsed 0 00:00:06

* 정상 impdp 확인

5. 파티션 현황 확인 ( TO-BE ) 파티션은 없으나 테이블 1ea로 정상 impdp 수행됨

[seorcl19]se-ejtest:/oracle/app/oracle/product/19.0.0/rdbms/lib> sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 2 15:32:05 2024
Version 19.23.0.0.0

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


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> col OWNER for a30
select OWNER, OBJECT_TYPE, COUNT(*) from dba_objects where owner='TESTUSER'
group by owner, object_type
order by owner;

OWNER                          OBJECT_TYPE               COUNT(*)
------------------------------ ----------------------- ----------
TESTUSER                       TABLE                            1