"마이그레이션 작업순서"의 두 판 사이의 차이
DB CAFE
(새 문서: 마이그레이션 작업순서 -- 1.원본의 오브젝트 갯수 계산 SELECT OWNER OW , SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL , SUM(DECODE(OBJECT_TYPE,'INDEX',1,0)) IND , SUM...) |
|||
| (같은 사용자의 중간 판 3개는 보이지 않습니다) | |||
| 1번째 줄: | 1번째 줄: | ||
마이그레이션 작업순서 | 마이그레이션 작업순서 | ||
| − | + | == 1.원본의 오브젝트 갯수 계산 == | |
| − | + | <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> | |
| + | |||
| + | == 2. INVALIED 상태인 오프젝트 갯수 확인 == | ||
| + | <source lang=sql> | ||
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
| 30번째 줄: | 33번째 줄: | ||
ORDER BY 1 | ORDER BY 1 | ||
; | ; | ||
| − | + | </source> | |
| − | + | == 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.테이블스페이스 == | |
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.SCHEMA 사용자 == | |
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 | ||
| 56번째 줄: | 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 | ||
| 65번째 줄: | 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.시스템유저 DDL 백업 == | |
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 | ||
| 74번째 줄: | 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.시노님 백업 == | |
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 확인 == | |
| + | <source lang=sql> | ||
select * from nls_database_parameters; | select * from nls_database_parameters; | ||
| + | </source> | ||
| − | + | == 9.EXPORT 이전할 데이터 백업 == | |
| + | <source lang=sql> | ||
expdp | expdp | ||
| − | + | </source> | |
| − | + | == 10.Target DB 생성 == | |
원본소스의 문자셋과일치 해야함 | 원본소스의 문자셋과일치 해야함 | ||
| − | + | == 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성 == | |
| − | + | == 12.Import 진행 == | |
| − | + | == 13.utlrp스크립트를 실행 == | |
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | -- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 | ||
| + | <source lang=sql> | ||
@?/rdbms/admin/utlrp.sql | @?/rdbms/admin/utlrp.sql | ||
| + | </source> | ||
| − | + | == 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 | ||
| 123번째 줄: | 143번째 줄: | ||
GROUP BY OWNER | GROUP BY OWNER | ||
HAVING OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') | HAVING OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') | ||
| − | ORDER BY 1 | + | ORDER BY 1; |
| − | ; | + | </source> |
| − | + | == 15.invaild 갯수 확인 == | |
| − | + | <source lang=sql> | |
SELECT OWNER | SELECT OWNER | ||
, OBJECT_NAME | , OBJECT_NAME | ||
| 134번째 줄: | 154번째 줄: | ||
WHERE STATUS = 'INVALID' | WHERE STATUS = 'INVALID' | ||
AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') | AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') | ||
| − | ORDER BY 1 | + | ORDER BY 1; |
| − | + | </source> | |
2019년 12월 18일 (수) 22:06 기준 최신판
마이그레이션 작업순서
목차
- 1 1.원본의 오브젝트 갯수 계산
- 2 2. INVALIED 상태인 오프젝트 갯수 확인
- 3 3. TABLESPACE,USER,ROLE,SYNONYM 생성
- 4 4.테이블스페이스
- 5 5.SCHEMA 사용자
- 6 6.시스템유저 DDL 백업
- 7 7.시노님 백업
- 8 8.CHARTER SET 확인
- 9 9.EXPORT 이전할 데이터 백업
- 10 10.Target DB 생성
- 11 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성
- 12 12.Import 진행
- 13 13.utlrp스크립트를 실행
- 14 14.타겟/소스 DB 오브젝트 갯수 확인
- 15 15.invaild 갯수 확인
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
;
2 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 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 4.테이블스페이스[편집]
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.SQLHOST perl -e's/$//g' TABLESPACE.SQL5 5.SCHEMA 사용자[편집]
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.SQL6 6.시스템유저 DDL 백업[편집]
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.SQL7 7.시노님 백업[편집]
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.SQLHOST perl -e's/$//g' SYNONYM.SQL8 8.CHARTER SET 확인[편집]
select * from nls_database_parameters;9 9.EXPORT 이전할 데이터 백업[편집]
expdp10 10.Target DB 생성[편집]
원본소스의 문자셋과일치 해야함
11 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성[편집]
12 12.Import 진행[편집]
14 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 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;