"마이그레이션"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
− | + | = Oracle DB 마이그레이션 작업순서 = | |
− | 마이그레이션 작업순서 | + | == 소스 DB 작업 == |
− | + | === [소스] 오브젝트 갯수 계산 === | |
− | + | <source lang=sql> | |
SELECT OWNER OW | SELECT OWNER OW | ||
, SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL | , SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL | ||
19번째 줄: | 19번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
− | + | </source> | |
+ | === [소스] INVALIED 상태인 오프젝트 갯수 확인 === | ||
+ | <source lang=sql> | ||
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
30번째 줄: | 32번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
− | + | </source> | |
− | + | === [소스] TABLESPACE,USER,ROLE,SYNONYM 생성. === | |
− | + | <source lang=sql> | |
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); | EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); | ||
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE); | EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE); | ||
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE); | EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE); | ||
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE); | EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE); | ||
− | + | </source> | |
− | + | === [소스] tablespace 생성 스크립트 추출 및 생성 === | |
+ | <source lang=sql> | ||
SPOOL TABLESPACE.SQL | SPOOL TABLESPACE.SQL | ||
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL | SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL | ||
47번째 줄: | 50번째 줄: | ||
HOST perl -e's/^//g' TABLESPACE.SQL | HOST perl -e's/^//g' TABLESPACE.SQL | ||
HOST perl -e's/$//g' TABLESPACE.SQL | HOST perl -e's/$//g' TABLESPACE.SQL | ||
− | + | </source> | |
− | + | === [소스] user 생성 스크립트 === | |
+ | <source lang=sql> | ||
SPOOL USER.SQL | SPOOL USER.SQL | ||
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL | SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL | ||
56번째 줄: | 60번째 줄: | ||
HOST perl -e's/^//g' USER.SQL | HOST perl -e's/^//g' USER.SQL | ||
HOST perl -e's/$//g' USER.SQL | HOST perl -e's/$//g' USER.SQL | ||
+ | </source> | ||
− | + | === [소스] role 생성 스크립트 === | |
+ | <source lang=sql> | ||
SPOOL ROLE.SQL | SPOOL ROLE.SQL | ||
SELECT DBMS_METADATA.GET_DDL('ROLE_GRANT',USERNAME) | SELECT DBMS_METADATA.GET_DDL('ROLE_GRANT',USERNAME) | ||
65번째 줄: | 71번째 줄: | ||
HOST perl -e's/^//g' ROLE.SQL | HOST perl -e's/^//g' ROLE.SQL | ||
HOST perl -e's/$//g' ROLE.SQL | HOST perl -e's/$//g' ROLE.SQL | ||
− | + | </source> | |
− | + | === [소스] 시스템 권한 생성 스크립트 === | |
+ | <source lang=sql> | ||
SPOOL SYSTEM.SQL | SPOOL SYSTEM.SQL | ||
SELECT DBMS_METADATA.GET_DDL('SYSTEM_GRANT',USERNAME) | SELECT DBMS_METADATA.GET_DDL('SYSTEM_GRANT',USERNAME) | ||
72번째 줄: | 79번째 줄: | ||
WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | ||
SPOOL OFF; | SPOOL OFF; | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
HOST perl -e's/^//g' SYSTEM.SQL | HOST perl -e's/^//g' SYSTEM.SQL | ||
HOST perl -e's/$//g' SYSTEM.SQL | HOST perl -e's/$//g' SYSTEM.SQL | ||
− | + | </source> | |
− | + | === [소스] 시노님 생성 스크립트 === | |
+ | <source lang=sql> | ||
SPOOL SYNONYM.SQL | SPOOL SYNONYM.SQL | ||
CREATE 'CREATE PUBLIC SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | CREATE 'CREATE PUBLIC SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | ||
87번째 줄: | 98번째 줄: | ||
SPOOL OFF; | SPOOL OFF; | ||
+ | </source> | ||
+ | |||
+ | <source lang=sql> | ||
HOST perl -e's/^//g' SYNONYM.SQL | HOST perl -e's/^//g' SYNONYM.SQL | ||
HOST perl -e's/$//g' SYNONYM.SQL | HOST perl -e's/$//g' SYNONYM.SQL | ||
− | + | </source> | |
− | + | === [소스] CHARTER SET 확인 === | |
+ | <source lang=sql> | ||
select * from nls_database_parameters; | select * from nls_database_parameters; | ||
+ | </source> | ||
+ | === [소스] EXPORT 이전할 데이터 백업 === | ||
+ | <source lang=sql> | ||
+ | expdp | ||
+ | </source> | ||
− | -- | + | ---- |
− | |||
− | + | == 타겟 DB 작업 == | |
+ | ===[타겟]Target DB 생성 === | ||
원본소스의 문자셋과일치 해야함 | 원본소스의 문자셋과일치 해야함 | ||
− | + | ===[타겟] 테이블스페이스,유저,롤,시노님 딕셔너리 생성 === | |
− | + | ===[타겟] Import 진행 === | |
− | + | === [타겟]utlrp스크립트 실행 === | |
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | -- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | ||
+ | <source lang=sql> | ||
@?/rdbms/admin/utlrp.sql | @?/rdbms/admin/utlrp.sql | ||
− | + | </source> | |
− | + | === [타겟] 타겟 DB 오브젝트 갯수 확인 === | |
− | + | <source lang=sql> | |
SELECT OWNER OW | SELECT OWNER OW | ||
, SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL | , SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL | ||
125번째 줄: | 146번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
− | + | </source> | |
− | + | === [타겟] invaild 갯수 확인 === | |
+ | <source lang=sql> | ||
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
136번째 줄: | 158번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
+ | </source> | ||
[[category:oracle]] | [[category:oracle]] |
2023년 7월 14일 (금) 17:22 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 Oracle DB 마이그레이션 작업순서[편집]
1.1 소스 DB 작업[편집]
1.1.1 [소스] 오브젝트 갯수 계산[편집]
SELECT OWNER OW
, SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL
, SUM(DECODE(OBJECT_TYPE,'INDEX',1,0)) IND
, SUM(DECODE(OBJECT_TYPE,'SYNONYM',1,0)) SYN
, SUM(DECODE(OBJECT_TYPE,'SEQUENCE',1,0)) SQN
, SUM(DECODE(OBJECT_TYPE,'CLUSTER',1,0)) CLU
, SUM(DECODE(OBJECT_TYPE,'DATABASE LINK',1,0)) TBL
, SUM(DECODE(OBJECT_TYPE,'PACKAGE',1,0)) IND
, SUM(DECODE(OBJECT_TYPE,'PACKAGE BODY',1,0)) SYN
, SUM(DECODE(OBJECT_TYPE,'PROCEDURE',1,0)) SQN
, SUM(DECODE(OBJECT_TYPE,'FUNCTION',1,0)) CLU
FROM DBA_OBJECTS
GROUP BY OWNER
HAVING OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','')
ORDER BY 1
;
1.1.2 [소스] INVALIED 상태인 오프젝트 갯수 확인[편집]
SELECT OWNER
, OBJECT_NAME
, OBJECT_TYPE
, STATUS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','')
ORDER BY 1
;
1.1.3 [소스] TABLESPACE,USER,ROLE,SYNONYM 생성.[편집]
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);
1.1.4 [소스] tablespace 생성 스크립트 추출 및 생성[편집]
SPOOL TABLESPACE.SQL
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME NOT IN ('SYS','SYSAUC','SYSTEM','TEMP','USERS','TOOLS'),(SELECT VALUE FROM V$PARAMETER WHERE NAME ='undo_tablespace'));
SPOOL OFF;
HOST perl -e's/^//g' TABLESPACE.SQL
HOST perl -e's/$//g' TABLESPACE.SQL
1.1.5 [소스] user 생성 스크립트[편집]
SPOOL USER.SQL
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL
FROM DBA_USERS
WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','','');
SPOOL OFF;
HOST perl -e's/^//g' USER.SQL
HOST perl -e's/$//g' USER.SQL
1.1.6 [소스] role 생성 스크립트[편집]
SPOOL ROLE.SQL
SELECT DBMS_METADATA.GET_DDL('ROLE_GRANT',USERNAME)
FROM DBA_USERS
WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','','');
SPOOL OFF;
HOST perl -e's/^//g' ROLE.SQL
HOST perl -e's/$//g' ROLE.SQL
1.1.7 [소스] 시스템 권한 생성 스크립트[편집]
SPOOL SYSTEM.SQL
SELECT DBMS_METADATA.GET_DDL('SYSTEM_GRANT',USERNAME)
FROM DBA_USERS
WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','','');
SPOOL OFF;
HOST perl -e's/^//g' SYSTEM.SQL
HOST perl -e's/$//g' SYSTEM.SQL
1.1.8 [소스] 시노님 생성 스크립트[편집]
SPOOL SYNONYM.SQL
CREATE 'CREATE PUBLIC SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';'
FROM DBA_SYNONYMS
WHERE OWNER = 'PUBLIC'
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','','');
CREATE 'CREATE SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';'
FROM DBA_SYNONYMS
WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','','');
SPOOL OFF;
HOST perl -e's/^//g' SYNONYM.SQL
HOST perl -e's/$//g' SYNONYM.SQL
1.1.9 [소스] CHARTER SET 확인[편집]
select * from nls_database_parameters;
1.1.10 [소스] EXPORT 이전할 데이터 백업[편집]
expdp
1.2 타겟 DB 작업[편집]
1.2.1 [타겟]Target DB 생성[편집]
원본소스의 문자셋과일치 해야함
1.2.2 [타겟] 테이블스페이스,유저,롤,시노님 딕셔너리 생성[편집]
1.2.3 [타겟] Import 진행[편집]
1.2.5 [타겟] 타겟 DB 오브젝트 갯수 확인[편집]
SELECT OWNER OW
, SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL
, SUM(DECODE(OBJECT_TYPE,'INDEX',1,0)) IND
, SUM(DECODE(OBJECT_TYPE,'SYNONYM',1,0)) SYN
, SUM(DECODE(OBJECT_TYPE,'SEQUENCE',1,0)) SQN
, SUM(DECODE(OBJECT_TYPE,'CLUSTER',1,0)) CLU
, SUM(DECODE(OBJECT_TYPE,'DATABASE LINK',1,0)) TBL
, SUM(DECODE(OBJECT_TYPE,'PACKAGE',1,0)) IND
, SUM(DECODE(OBJECT_TYPE,'PACKAGE BODY',1,0)) SYN
, SUM(DECODE(OBJECT_TYPE,'PROCEDURE',1,0)) SQN
, SUM(DECODE(OBJECT_TYPE,'FUNCTION',1,0)) CLU
FROM DBA_OBJECTS
GROUP BY OWNER
HAVING OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','')
ORDER BY 1
;
1.2.6 [타겟] invaild 갯수 확인[편집]
SELECT OWNER
, OBJECT_NAME
, OBJECT_TYPE
, STATUS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','')
ORDER BY 1
;