Oracle Migration 11.2.0.4(Enterprise) TO 19c (Standard) Partition Table Merge(오라클 마이그레이션 파티션테이블 머지)

 

Oracle Migration 11.2.0.4(Enterprise) TO 19c (Standard)

#환경구성

 

Server	AS-IS	TO-BE
OS	Solaris	Linux7.8
DB Version	11.2.0.4	19.3
DB License	Enterprise	Standard
NLS_CHARACTERSET	AL32UTF8	AL32UTF8

* Enterprise 에서 지원하는 Partition table을 Standard 버전에서 사용할 수 없습니다.

EXPDP/IMPDP Migration : Partition Table-> 데이터 마이그레이션 -> 일반 Table  

#AS-IS EXPDP 설정

export ORACLE_SID=SID
#mkdir -p /app/oracle/work
#chown -R oracle:oinstall /app/oracle/work
#chmod -R 775 /app/oracle/work

 

#AS-IS 디렉토리 권한

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE
SQL> CREATE DIRECTORY DATAPUMP AS '/app/oracle/work';
SQL> GRANT READ, WRITE ON DIRECTORY DATAPUMP to system
SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH,OWNER FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DATAPUMP'

 

#TO-BE IMPDP 설정
export ORACLE_SID=SID
#mkdir -p /app/oracle/work
#chown -R oracle:oinstall /app/oracle/work
#chmod -R 775 /app/oracle/work

 

#TO-BE 디렉토리 권한

SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE
SQL> CREATE DIRECTORY DATAPUMP AS '/app/oracle/work';
SQL> GRANT READ, WRITE ON DIRECTORY DATAPUMP to system
SQL> SELECT DIRECTORY_NAME,DIRECTORY_PATH,OWNER FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DATAPUMP'

 

#테이블 스페이스 데이터파일 조회

set lines 200
set pages 100
col file_name for a45
col bytes for 999999999999
col maxbytes for 999999999999
select tablespace_name, file_name, bytes, maxbytes
from dba_data_files
order by 1,2;
spool off

 

#DB 오브젝트 조회

spool /app/oracle/work/objects.txt
set lines 200
set pages 100
col owner for a30
select owner, object_type, status, count(*)
from dba_objects
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))
group by owner, object_type, status
order by 1,2,3;
spool off

 

#DB 유저 조회

spool /app/oracle/work/users.txt
set lines 200
set pages 100
col a40 username
select username, default_tablespace, temporary_tablespace
from dba_users
order by 1,2,3;
spool off

 

#DB 권한 조회
spool /app/oracle/work/sys_privs.txt
set lines 200
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))
order by 1,2,3;
spool off

spool /app/oracle/work/sys_privs.txt
set lines 200
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))
order by 1,2,3;
spool off

spool /app/oracle/work/role_privs.txt
set lines 200
col grantee for a40
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))
order by 1,2,3;
spool off

 

#테이블 ROW COUNT 조회

spool table_rows.txt
set lines 200
set lines 100
col owner for a30
col table_name for a30
col count for a30
select owner,
       table_name,
       extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c
                                                from ' || '"' || owner || '"' || '.' || '"' || table_name || '"')),'/ROWSET/ROW/C') count
from dba_tables
where table_name not in ('BIN%','_default%')
and iot_type is null
and owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'))
order by 1,2;
spool off

 

#EXPDP 수행

$expdp system/oracle@SID directory=DATAPUMP dumpfile= SCHEMAS.dmp schemas=SCHEMAS_1,SCHEMAS_2,SCHEMAS_3 logfile=SCHEMAS.log job_name= SCHEMAS_JOB

 

#IMPDP 수행

동일 테이블스페이스 데이터파일 생성

Create tablespace [테이블스페이스명] DATAFILE ‘/경로/.DBF’ SIZE 30G AUTOEXTEND ON;
Create tablespace [테이블스페이스명] DATAFILE ‘/경로/.DBF’ SIZE 30G AUTOEXTEND ON;
Create tablespace [테이블스페이스명] DATAFILE ‘/경로/.DBF’ SIZE 30G AUTOEXTEND ON;
Alter tablespace [테이블스페이스명] ADD DATAFILE ‘/경로/.DBF’ SIZE 30G AUTOEXTEND ON;
Alter tablespace [테이블스페이스명] ADD DATAFILE ‘/경로/.DBF’ SIZE 30G AUTOEXTEND ON;

 

#IMPDP 수행

$ scp SCHEMAS.dmp 10.127.1.41:/app/oracle/work --덤프파일 TO-BE 이동
$ impdp system/welcome1@TO_BE_SID schemas= SCHEMAS_1,SCHEMAS_2,SCHEMAS_3 
PARTITION_OPTIONS=MERGE TABLE_EXISTS_ACTION=REPLACE directory= 
DATAPUMP dumpfile= SCHEMAS.dmp job_name= SCHEMAS_JOB logfile= SCHEMAS.log

Partition Table -> 기본 Table  마이그레이션 
옵션 PARTITION_OPTIONS=MERGE
*마이그레이션 이후 Partition Table -> Table 전환 테이블 통계정보 재 수집

 

PARTITION_OPTIONS=MERGE 옵션을 통해 파티션테이블을 일반테이블 형태로 IMPORT 합니다.