Oracle DBLink Migration (오라클 DB링크 이용한 마이그레이션)

반응형

DB링크 이용한 오라클 마이그레이션 9i -> 11gR2

 

# 마이그레이션 환경

SOURCE DB   :  Oracle 9i Enterprise
TARGET  DB   :  Oracle 11gR2 Enterprise

#설 명 :

OS 의 데이터파일 형식의 엔디안 값의 문제로 백업파일 CLONE DB restore 할수 없었으며 

export 받을 공간 이  부족하여 디비링크를 이용한 CTAS 방식으로 마이그레이션 계획을 잡았습니다. 

 

#사전 고려사항

1.  Service Name 과 ORACLE_SID는 다르고 NLS_CHARACTER_SET은 동일한 상태 2.  파티셔닝 테이블 사용 유무 (오라클 Standard Version 파티셔닝 테이블 사용할수 없음) 3.  LOB 형식 테이블 CTAS 불가능 export 이관 필요(커서사용하여 가능) 4. 관계형 테이블 순서대로 INSERT INTO  SELECT 수행시   부모테이블  자식테이블 순서로  마이그레이션 필요 (DB DownTime 필요) 사전오브젝트 검증 5. 대량의 DML 이 발생 되기에  REDO 사이즈 , UNDO 사이즈, 아카이브 모드 ,기타  조정이 필요

 

#시나리오 구성

 

# SOURCE DB 신규 테이블 스페이스 생성

CREATE TABLESPACE test_data

  DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'

  SIZE 1M AUTOEXTEND ON NEXT 30G;

 

 

#SOURCE DB 유저 신규 생성 및 테이블스페이스 템프 테이블스페이스 지정

CREATE USER test_user IDENTIFIED BY test_user

  DEFAULT TABLESPACE test_data

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON test_data;

 

 

#SOURCE DB 생성된 유저 권한 부여

GRANT CREATE SESSION, CREATE TABLE TO test_user;

GRANT connect, resource to test_user ;

GRANT CREATE synonym to test_user;

GRANT CREATE VIEW to test_user;

GRANT CREATE TRIGGER TO test_user;

 

 

#SOURCE DB 테이블 생성(PRIMARY KEY)

CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

 

 

#SOURCE DB 데이터 삽입

INSERT /*+ APPEND */ INTO test_tab (id, description)

SELECT level,'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

COMMIT;

 

 

#SOURCE DB 인덱스 생성

CREATE INDEX TEST_INDEX ON test_tab(id);

 

 

#SOURCE DB 시퀀스 생성

CREATE sequence test_seq

  start with 1

  increment BY 1

  maxvalue 100000;

 

 

#SOURCE DB 트리거 생성

-- 트리거 관련 테이블 생성
CREATE TABLE ExamData
(
ID NUMBER(10) PRIMARY KEY,
NAME VARCHAR2(20)
);
CREATE TABLE ExamMemo
(
ID NUMBER(10) PRIMARY KEY,
memo VARCHAR2(20) ,
CONSTRAINT FX_EXAM_ID FOREIGN KEY(ID) REFERENCES EXAMDATA(ID)
);



Create or replace TRIGGER trg_ExamData

AFTER DELETE OR INSERT OR UPDATE ON  ExamData for each row

BEGIN
   IF INSERTING THEN
      INSERT INTO ExamMemo VALUES (:new.id,'insert');
   ELSIF UPDATING THEN
      INSERT INTO ExamMemo VALUES (:new.id,'update');
   ELSIF DELETING THEN
      INSERT INTO ExamMemo VALUES (:new.id,'delete');
   END IF;
END;
/

 

 

#SOURCE DB 프로시저 생성

CREATE OR REPLACE PROCEDURE INSERT_TEST_PROCEDURE
     /* IN  Parameter */
     (v_empno    IN    NUMBER)    
     IS
     BEGIN
     INSERT /*+ APPEND */ INTO test_tab (id, description)
     SELECT level,
       'Description for ' || level
     FROM   dual  
     CONNECT BY level <= v_empno;
     COMMIT;
END INSERT_TEST_PROCEDURE;

/

 

#SOURCE DB 뷰 생성

CREATE OR REPLACE VIEW NAME_QUERY AS SELECT * FROM TEST_TAB;

 

 

#SOURCE DB 시노미 생성

CREATE SYNONYM TEST_TABLE FOR TEST_USER.EXAMDATA;

 

 

 

#SOURCE  오브젝트 및 정보 조회

#유저정보 조회
select username  from dba_users;



#캐릭터 셋 확인
SELECT * FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET'



#해당 ROLES 조회
SELECT * FROM USER_ROLES;
SELECT USERNAME, GRANTED_ROLE FROM USER_ROLE_PRIVS;



#TAB_PRIVS 조회

SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM USER_TAB_PRIVS;



#SESSION_PRIVS 조회
SELECT * FROM SESSION_PRIVS;



#해당 유저 인덱스 조회
SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,STATUS,TABLESPACE_NAME FROM USER_INDEXES;



#해당 유저 테이블 조회
SELECT * FROM TAB;

 

#데이터 조회
SELECT COUNT(*) FROM 테이블명

 

 

#SOURCE DB 오브젝트 조회

 

select object_name, object_type, status from user_objects order by 2


OBJECT_NAME                         OBJECT_TYPE            STATUS
-------------------------------- ------------------ -------
SYS_C002761                      INDEX                    VALID
SYS_C002762                      INDEX                    VALID
TEST_TAB_PK                      INDEX                    VALID
INSERT_TEST_PROCEDURE            PROCEDURE                VALID
TEST_SEQ                         SEQUENCE                 VALID
TEST_TABLE                       SYNONYM                  VALID
EXAMDATA                         TABLE                    VALID
EXAMMEMO                         TABLE                    VALID
TEST_TAB                         TABLE                    VALID
TRG_EXAMDATA                     TRIGGER                  VALID
NAME_QUERY                       VIEW                     VALID

 

 

#SOURCE DB 메타정보 EXPORT

 

exp system/oracle file=/home/oracle/exp_user4.dmp owner=test_user indexes=yes grants=yes rows=no
OR
exp test_user/test_user file=/home/oracle/exp_user7.dmp owner=test_user indexes=yes grants=yes rows=no  STATISTICS=NONE

*메타정보IMP시 인덱스를 제외한뒤  데이터 마이그레이션 후  인덱스 재생성 하는 방법을 권장.

 

#TARGET DB 테이블스페이스 생성

CREATE TABLESPACE test_data DATAFILE '/home/oracle/product/11g/test_data01.dbf'

SIZE 1M AUTOEXTEND ON NEXT 1M;

 

#TARGET DB 유저생성 생성

CREATE USER test_user IDENTIFIED BY test_user default tablespace test_data;

 

 ##TARGET DB  USER  생성된 유저 권한 부여

GRANT CREATE SESSION, CREATE TABLE TO test_user;
GRANT connect, resource to test_user ;
GRANT create synonym to test_user;
GRANT create view to test_user;
GRANT create index to test_user;

 

#TARGET DB Link 생성

CREATE DATABASE LINK DBLK_B_DB
CONNECT TO TEST_USER
IDENTIFIED BY TEST_USER
USING '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.33.134)(PORT = 1521))) (CONNECT_DATA =(SID = orcl)))';

 

#TARGET DB Link  권한 설정

GRANT CREATE DATABASE LINK, DROP DATABASE LINK TO test_user;

GRANT CREATE DATABASE LINK TO test_user;

DB Link 하기 위해서는 권한이 필요하다. 만약 권한이 없다면 다음으로 권한 설정을 해준다.

 

#DB Link 삭제관련

DROP DATABASE LINK  DBLK_B_DB;

 

 

# SOURCE DMP 파일  TARGET IMPORT

SOURCE DB -> TARGET DB DMP 파일 이동
# META정보 IMP
$imp system/oracle file=/home/oracle/exp_user7.dmp full=y
OR
$imp test_user/test_user file=/home/oracle/exp_user7.dmp

 

 

#TARGET DB OBJECT 조회

select object_name, object_type, status from user_objects order by 2;

OBJECT_NAME                         OBJECT_TYPE             STATUS
-------------------------------- ------------------- -------
SYS_C0011103                            INDEX              VALID
TEST_TAB_PK                             INDEX              VALID
SYS_C0011102                            INDEX              VALID
INSERT_TEST_ PROCEDURE                  PROCEDURE          VALID
TEST_SEQ                                SEQUENCE           VALID
TEST_TABLE                              SYNONYM            VALID
EXAMMEMO                                TABLE              VALID
EXAMDATA                                TABLE              VALID
TEST_TAB                                TABLE              VALID
TRG_EXAMDATA                            TRIGGER            VALID
NAME_QUERY                              VIEW               VALID

 

 

#TARGET DB 인덱스 조회

SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,STATUS,TABLESPACE_NAME FROM USER_INDEXES;

 

 

#CTAS  DATA MIGRAITON

 

#TARGET DB 데이터 삽입

TRUNCATE TABLE 테이블명 ;

ALTER TABLE 테이블명 NOLOGGING; 

INSERT INTO /*+APPEND */ 테이블명 SELECT * FROM 테이블명@디비링크명;   

COMMIT;                                                                                                                                                                                                             INSERT INTO /*+APPEND */ 

ALTER TABLE 테이블명 LOGGING;

 

SELECT *   /*+ parallel(t,4) */ * from 테이블명 @디비링크명 
parallel 옵션과 여러 힌트를 사용하여 마이그레이션 성능에 효과를 늘릴수있습니다.

 

 

#TARGET DB 데이터 검증 

SELECT * FROM EXAMDATA;

SELECT * FROM EXAMMEMO;

SELECT * FROM TEST_TAB;