"마이그레이션 작업순서"의 두 판 사이의 차이
DB CAFE
잔글 (Dbcafe님이 사용자:Dbcafe 문서를 DB CAFE:마이그레이션 작업순서 문서로 이동했습니다) |
(→1.원본의 오브젝트 갯수 계산) |
||
23번째 줄: | 23번째 줄: | ||
-- 2. INVALIED 상태인 오프젝트 갯수 확인 | -- 2. INVALIED 상태인 오프젝트 갯수 확인 | ||
− | + | <source lang=sql> | |
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
33번째 줄: | 33번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
− | + | </source> | |
-- 3. TABLESPACE,USER,ROLE,SYNONYM 생성 | -- 3. 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> | |
-- 4.tablespace | -- 4.tablespace | ||
SPOOL TABLESPACE.SQL | SPOOL TABLESPACE.SQL | ||
+ | <source lang=sql> | ||
SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL | SELECT DBMS_METADATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) AS DDL | ||
FROM DBA_TABLESPACES | FROM DBA_TABLESPACES | ||
WHERE TABLESPACE_NAME NOT IN ('SYS','SYSAUC','SYSTEM','TEMP','USERS','TOOLS'),(SELECT VALUE FROM V$PARAMETER WHERE NAME ='undo_tablespace')); | WHERE TABLESPACE_NAME NOT IN ('SYS','SYSAUC','SYSTEM','TEMP','USERS','TOOLS'),(SELECT VALUE FROM V$PARAMETER WHERE NAME ='undo_tablespace')); | ||
SPOOL OFF; | SPOOL OFF; | ||
− | + | </source> | |
+ | <source lang=sql> | ||
HOST perl -e's/^//g' TABLESPACE.SQL | HOST perl -e's/^//g' TABLESPACE.SQL | ||
+ | </source> | ||
+ | <source lang=sql> | ||
HOST perl -e's/$//g' TABLESPACE.SQL | HOST perl -e's/$//g' TABLESPACE.SQL | ||
− | + | </source> | |
-- 5.user | -- 5.user | ||
SPOOL USER.SQL | SPOOL USER.SQL | ||
+ | <source lang=sql> | ||
SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL | SELECT DBMS_METADATA.GET_DDL('USER',USERNAME) AS DDL | ||
FROM DBA_USERS | FROM DBA_USERS | ||
59번째 줄: | 64번째 줄: | ||
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 | -- role | ||
SPOOL ROLE.SQL | SPOOL ROLE.SQL | ||
+ | <source lang=sql> | ||
SELECT DBMS_METADATA.GET_DDL('ROLE_GRANT',USERNAME) | SELECT DBMS_METADATA.GET_DDL('ROLE_GRANT',USERNAME) | ||
FROM DBA_USERS | FROM DBA_USERS | ||
68번째 줄: | 74번째 줄: | ||
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> | |
-- 6.SYSTEM.SQL | -- 6.SYSTEM.SQL | ||
SPOOL SYSTEM.SQL | SPOOL SYSTEM.SQL | ||
+ | <source lang=sql> | ||
SELECT DBMS_METADATA.GET_DDL('SYSTEM_GRANT',USERNAME) | SELECT DBMS_METADATA.GET_DDL('SYSTEM_GRANT',USERNAME) | ||
FROM DBA_USERS | FROM DBA_USERS | ||
77번째 줄: | 84번째 줄: | ||
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> | |
-- 7.SYNONYM.SQL | -- 7.SYNONYM.SQL | ||
SPOOL SYNONYM.SQL | SPOOL SYNONYM.SQL | ||
+ | <source lang=sql> | ||
CREATE 'CREATE PUBLIC SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | CREATE 'CREATE PUBLIC SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | ||
FROM DBA_SYNONYMS | FROM DBA_SYNONYMS | ||
WHERE OWNER = 'PUBLIC' | WHERE OWNER = 'PUBLIC' | ||
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | AND TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | ||
− | + | </source> | |
CREATE 'CREATE SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | CREATE 'CREATE SYNONYM' || SYNONYM_MAME || ' FOR ' || TABLE_OWNER ||'.'|| TABLE_NAME ||';' | ||
FROM DBA_SYNONYMS | FROM DBA_SYNONYMS | ||
WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM','SCOTT','WMSYS','ORDSYS','MDSYS','',''); | ||
− | + | </source> | |
SPOOL OFF; | SPOOL OFF; | ||
+ | <source lang=sql> | ||
HOST perl -e's/^//g' SYNONYM.SQL | HOST perl -e's/^//g' SYNONYM.SQL | ||
+ | </source> | ||
+ | <source lang=sql> | ||
HOST perl -e's/$//g' SYNONYM.SQL | HOST perl -e's/$//g' SYNONYM.SQL | ||
+ | </source> | ||
-- 8.CHARTER SET 확인 | -- 8.CHARTER SET 확인 | ||
+ | <source lang=sql> | ||
select * from nls_database_parameters; | select * from nls_database_parameters; | ||
− | + | </source> | |
-- 9.EXPORT 이전할 데이터 백업 | -- 9.EXPORT 이전할 데이터 백업 | ||
+ | <source lang=sql> | ||
expdp | expdp | ||
− | + | </source> | |
-- 10.Target DB 생성 | -- 10.Target DB 생성 | ||
원본소스의 문자셋과일치 해야함 | 원본소스의 문자셋과일치 해야함 | ||
108번째 줄: | 122번째 줄: | ||
-- 13. utlrp스크립트를 실행 | -- 13. utlrp스크립트를 실행 | ||
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | -- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | ||
+ | <source lang=sql> | ||
@?/rdbms/admin/utlrp.sql | @?/rdbms/admin/utlrp.sql | ||
− | + | </source> | |
-- 14.타겟 DB 오브젝트 갯수 확인 | -- 14.타겟 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 | ||
128번째 줄: | 143번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
− | + | </source> | |
-- 15.invaild 갯수 확인 | -- 15.invaild 갯수 확인 | ||
+ | <source lang=sql> | ||
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
139번째 줄: | 155번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
+ | </source> |
2019년 12월 18일 (수) 22:01 판
마이그레이션 작업순서
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
;
-- 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
;
-- 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);
-- 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
-- 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
-- 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
-- 6.SYSTEM.SQL 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
-- 7.SYNONYM.SQL 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',,); </source> SPOOL OFF;
HOST perl -e's/^//g' SYNONYM.SQL
HOST perl -e's/$//g' SYNONYM.SQL
-- 8.CHARTER SET 확인
select * from nls_database_parameters;
-- 9.EXPORT 이전할 데이터 백업
expdp
-- 10.Target DB 생성 원본소스의 문자셋과일치 해야함
-- 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성
-- 12.Import 진행
-- 13. utlrp스크립트를 실행 -- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활
@?/rdbms/admin/utlrp.sql
-- 14.타겟 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
;
-- 15.invaild 갯수 확인
SELECT OWNER
, OBJECT_NAME
, OBJECT_TYPE
, STATUS
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','')
ORDER BY 1
;