행위

DB CAFE

마이그레이션 작업순서

DB CAFE

(사용자:Dbcafe에서 넘어옴)

마이그레이션 작업순서

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.SQL
HOST perl -e's/$//g' TABLESPACE.SQL

5 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.SQL

6 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.SQL

7 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.SQL
HOST perl -e's/$//g' SYNONYM.SQL

8 8.CHARTER SET 확인

select * from nls_database_parameters;

9 9.EXPORT 이전할 데이터 백업

expdp

10 10.Target DB 생성

원본소스의 문자셋과일치 해야함

11 11.타겟 DB에 테이블스페이스,유저,롤,시노님 딕셔너리 정보를 생성

12 12.Import 진행

13 13.utlrp스크립트를 실행

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

@?/rdbms/admin/utlrp.sql

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;