Oracle/Oracle Migration

Oracle TTS (Transportable Tablespace) MIGRATION

의미와의미 2017. 11. 8. 10:24
반응형

TTS (Transportable Tablespace) MIGRATION 




테스트 시나리오 

#SOURCE

 

CONN / AS SYSDBA

 

#ENDIAN 확인

col platform_name for a35

SELECT * FROM v$transportable_platform order by platform_id;

 

SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT

     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

 

#캐릭터 셋 확인

SELECT * FROM NLS_DATABASE_PARAMETERS

WHERE PARAMETER = 'NLS_CHARACTERSET';

 

 

#테이블 스페이스 생성

CREATE TABLESPACE test_data

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

  SIZE 1M AUTOEXTEND ON NEXT 1M;

 

#유저 생성

CREATE USER test_user IDENTIFIED BY test_user

  DEFAULT TABLESPACE test_data

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON test_data;

 

 

#유저 권한 부여

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;

 

 

CONN test_user/test_user

 

 

#테이블 생성(PRIMARY KEY)

CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

 

 

#데이터 삽입

 

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

SELECT level,'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

COMMIT;

 

 

#인덱스 생성

CREATE INDEX TEST_INDEX ON test_tab(id);

 

 

#시퀀스 생성

CREATE sequence test_seq

  start with 1

  increment BY 1

  maxvalue 100000;

 

#트리거 생성

 

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;

/

 

#프로시저 생성

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;

/    

 

#뷰 생성

CREATE OR REPLACE VIEW NAME_QUERY AS SELECT * FROM TEST_TABLE

 

#시노미 생성

CREATE SYNONYM TEST_TABLE FOR TEST_USER.EXAMDATA

 

 

#해당 유저 OBJECT 조회

col object_name for a40

SELECT OBJECT_NAME,OBJECT_TYPE,STATUS,TEMPORARY FROM USER_OBJECTS;

SELECT COUNT(*) FROM USER_OBJECTS;

 

#해당 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;



# 테이블 스페이스 데이터 파일 조회

SELECT TABLESPACE_NAME, FILE_NAME , STATUS FROM DBA_DATA_FILES;

 

 

 

#해당 테이블스페이스 TTS 검사

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => '테이블스페이스명1,테이블스페이스명2', incl_constraints => TRUE);

 

SELECT * FROM transport_set_violations;

 

 

#해당 테이블 스페이스 READ ONLY

 

ALTER TABLESPACE test_data READ ONLY;

 

 

#expdp 구성

 

 

CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

 

 

#expdp export tablespace metadata

 

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log

 

 

 

#RMAN을 이용한 DATAFILE 변환

 

$ rman target /

RMAN> convert tablespace 'tablespace_name'

to platform="Linux IA (32-bit)"

db_file_name_convert='/해당 파일.dbf','/tmp/해당파일/dbf';

 

 

 

 

#SOURCE 변환 파일 TARGET 쪽으로 FTP 이동

 

$scp ip:/tmp/해당파일/dbf  /u01/convert/backup/

 

 

#SOURCE 해당 테이블 스페이스 변경

ALTER TABLESPACE test_data READ WRITE;

 

 

 

#TARGET

 

 

CONN / AS SYSDBA

 

#TARGET ENDIAN 확인

SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

 

#캐릭터 셋 확인

SELECT * FROM NLS_DATABASE_PARAMETERS

WHERE PARAMETER = 'NLS_CHARACTERSET'

 

 

-- SOURCE 변환 파일 TARGET 에서 한번 더 변환 후 위치 지정(필수x)

$rman target /

RMAN> CONVERT DATAFILE '/u01/convert/backup/SOURCE 변환 파일.DBF' FORMAT '/u01/app/oracle/oradata/DB11GB/test_data01.dbf;

 

 

#TARGET 해당 유저 생성

 

CREATE USER test_user IDENTIFIED BY test_user;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

 

#TARGET import

 

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp1.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log

 

 

*REMAP_SCHEMA=<source_user>:<target_user>

 

 

 

#TARGET 해당 테이블 스페이스 변경

ALTER TABLESPACE test_data READ WRITE;

SELECT tablespace_name, plugged_in, status FROM   dba_tablespaces WHERE  tablespace_name = 'TEST_DATA';

 

#TABLE 조회

SELECT  * FROM TAB;

SELECT COUNT(*) FROM 테이블명;

 

#해당 유저 OBJECT 조회

col object_name for a40

SELECT OBJECT_NAME,OBJECT_TYPE,STATUS,TEMPORARY FROM USER_OBJECTS;

SELECT COUNT(*) FROM USER_OBJECTS;

 

#해당 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;

 


 

일부 오브젝트 시퀀스나 프로시저 펑션은  롤 등이 TTS 넘어가지 않을경우.(메타정보 출력 후)

 

 

source

expdp test_user/test_user dumpfile=metadata3.dmp directory=temp_dir include=procedure,function,sequence

 

target

impdp system/oracle  dumpfile=metadata3 directory=temp_dir full=y

 

 

조회

반응형