행위

"마이그레이션"의 두 판 사이의 차이

DB CAFE

(새 문서: 마이그레이션 작업순서 -- 1.원본의 오브젝트 갯수 계산 SELECT OWNER OW , SUM(DECODE(OBJECT_TYPE,'TABLE',1,0)) TBL , SUM(DECODE(OBJECT_TYPE,'INDEX',1,0)) IND , SUM...)
 
 
(같은 사용자의 중간 판 하나는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
 
+
= Oracle DB 마이그레이션 작업순서 =
−
마이그레이션 작업순서
+
== 소스 DB 작업 ==
−
-- 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
 
;
 
;
−
-- 2. INVALIED 상태인 오프젝트 갯수 확인
+
</source>
  
 +
=== [소스] INVALIED 상태인 오프젝트 갯수 확인 ===
 +
<source lang=sql>
 
SELECT OWNER
 
SELECT OWNER
 
, OBJECT_NAME
 
, OBJECT_NAME
30번째 줄: 32번째 줄:
 
ORDER BY 1
 
ORDER BY 1
 
;
 
;
−
 
+
</source>
−
-- 3. TABLESPACE,USER,ROLE,SYNONYM 생성
+
=== [소스] 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
+
=== [소스] 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>
−
-- 5.user
+
=== [소스] 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
+
=== [소스] 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>
−
-- 6.SYSTEM.SQL
+
=== [소스] 시스템 권한 생성 스크립트 ===
 +
<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>
−
-- 7.SYNONYM.SQL
+
=== [소스] 시노님 생성 스크립트 ===
 +
<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>
−
-- 8.CHARTER SET 확인
+
=== [소스] CHARTER SET 확인 ===
 +
<source lang=sql>
 
select * from nls_database_parameters;
 
select * from nls_database_parameters;
 +
</source>
 +
=== [소스] EXPORT 이전할 데이터 백업 ===
 +
<source lang=sql>
 +
expdp
 +
</source>
  
−
-- 9.EXPORT 이전할 데이터 백업
+
----
−
expdp
 
  
−
-- 10.Target DB 생성
+
== 타겟 DB 작업 ==
 +
===[타겟]Target DB 생성 ===
 
원본소스의 문자셋과일치 해야함
 
원본소스의 문자셋과일치 해야함
  
−
-- 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성
+
===[타겟] 테이블스페이스,유저,롤,시노님 딕셔너리 생성 ===
  
−
-- 12.Import 진행
+
===[타겟] Import 진행 ===
  
−
-- 13. utlrp스크립트를 실행
+
=== [타겟]utlrp스크립트 실행 ===
 
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활
 
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활
 +
<source lang=sql>
 
@?/rdbms/admin/utlrp.sql
 
@?/rdbms/admin/utlrp.sql
−
 
+
</source>
−
-- 14.타겟 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
125번째 줄: 146번째 줄:
 
ORDER BY 1
 
ORDER BY 1
 
;
 
;
−
 
+
</source>
−
-- 15.invaild 갯수 확인
+
=== [타겟] invaild 갯수 확인 ===
 +
<source lang=sql>
 
SELECT OWNER
 
SELECT OWNER
 
, OBJECT_NAME
 
, OBJECT_NAME
136번째 줄: 158번째 줄:
 
ORDER BY 1
 
ORDER BY 1
 
;
 
;
 +
</source>
 +
[[category:oracle]]

2023년 7월 14일 (금) 17:22 기준 최신판

thumb_up 추천메뉴 바로가기

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.4 [타겟]utlrp스크립트 실행[편집]

-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활 

@?/rdbms/admin/utlrp.sql

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
;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=마이그레이션&oldid=9166"