다른 명령
Oracle DB 마이그레이션 작업순서
소스 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 ;
[소스] INVALIED 상태인 오프젝트 갯수 확인
SELECT OWNER , OBJECT_NAME , OBJECT_TYPE , STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') ORDER BY 1 ;
[소스] 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);
[소스] 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
[소스] 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
[소스] 시스템 권한 생성 스크립트
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
[소스] 시노님 생성 스크립트
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
[소스] CHARTER SET 확인
select * from nls_database_parameters;
[소스] EXPORT 이전할 데이터 백업
expdp
타겟 DB 작업
[타겟]Target DB 생성
원본소스의 문자셋과일치 해야함
[타겟] 테이블스페이스,유저,롤,시노님 딕셔너리 생성
[타겟] Import 진행
[타겟]utlrp스크립트 실행
-- 오브젝트중에서 상태가 사용못한 상태인것을 찾아서 재컴파일해주는 역활
@?/rdbms/admin/utlrp.sql
[타겟] 타겟 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 ;
[타겟] invaild 갯수 확인
SELECT OWNER , OBJECT_NAME , OBJECT_TYPE , STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' AND OWNER NOT IN ('SYS','SYSTEM','SCOTT','','','','','','') ORDER BY 1 ;