이런 기능이 있다는 것은 오래 전부터 알고 있었는데,
이해도 잘 안되고 사용할 일도 없을 것 같아서 잊고 지내다가,
최근에 읽고 있는 책을 통해 필요성을 인식하게 되었습니다.
읽고 있는 책이 Oracle9i를 기준으로 한 책이라
original export/import utility를 사용한 방법에 대해서 설명되어 있는데,
여기서는 Oracle10g부터 제공되기 시작한 pump export/import utility를 사용한 방법도
같이 소개해 보고자 합니다.
Oracle10g이상을 사용하고 있음에도 불구하고,
여전히 original export/import utility를 단지 익숙하다는 이유로 사용하고 계신 분이 많을텐데,
최근에 등장한 Oracle11gR2에도 original export/import utility가 지원되지만,
언젠가는(3,4년 후 정도 될 것 같습니다) original utility는
pump utility에 의해 완전히 대체될 예정이므로,
하루라도 빨리 pump export/import utility를 익혀두는 편이 좋습니다.
우선 transportable tablespace기능의 장단점에 대해 간단히 소개하고자 합니다.
일반적으로 tablespace전체를 <A> database에서 <B> database로 복사하는 경우,
<A>의 해당 tablespace안에 담겨있는 schemas를 export한 후,
<B>의 datasbase에 import하는 방식을 사용합니다.
다들 아시다시피 import는 export에 비해 훨씬 많은 시간(5배정도)이 걸립니다.
만약 이 tablespace가 20~30GB정도 되는 대용량 tablespace인 경우,
import에 많은 시간이 걸리게 될 것입니다.
transportable tablespace기능을 사용하게 되면,
tablespace에 들어있는 data를 넣다/뺐다(export/import) 할 필요가 없고,
오직 필요한 것은 meta data만을 export/import하면 되기 때문에,
훨씬 시간을 절약할 수 있습니다.
대신, export/import을 수행할 경우, data가 깨끗하게 들어가게 되므로,
tablespace내에서 발생하고 있던 기존의 fragmentation을 해소시킬 수 있지만,
transportable tablespace기능을 사용할 경우, fragmentation은 그대로 남아 있게 됩니다.
이제 구체적인 작업을 설명하겠습니다.
1. 이동시키고자 하는 tablespace(target database)가 transportable한지
체크합니다.
|
SQL> exec dbms_tts.transport_set_check('USERS', true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected |
만약 다른 tablespace를 참조하는 objects가 있거나 하는 경우에는
(예를 들어 table은 여기 있지만 index는 다른 tablespace에 있거나 하는 경우) ,
위의 select문의 결과로 표시되게 됩니다.
operation상의 실수로 인해, 일부 table이 다른 tablespace에 있거나 하는 경우,
"alter table <table_name> move tablespace <tablespace_name>"을 통해서
간단히 table이 속한 tablespace를 변경할 수 있습니다.
(이 경우, index가 invalid상태가 되기 때문에 rebuild해줄 필요가 있음)
2. target database를 read only mode로 변경시킵니다.
read only mode로 변경하지 않는 경우,
"ORA-29335: tablespace 'USERS' is not read only"
와 같은 error가 발생하게 됩니다.
|
SQL> alter tablespace users read only;
Tablespace altered. |
3. <A> database상에서 target tablespace의 meta data만을 export합니다.
(1) original export를 사용하는 경우
⇒ exp system/**** transport_tablespace=y tablespaces=users file=users.dmp ...
(2) pump export를 사용하는 경우
⇒ expdp system/**** transport_tablespaces=users dumpfile=users.dmp ...
※ original export의 경우에는 current directory에 dump file이 생성되지만, pump export의
경우에는 지정한 directory object가 가리키는 directory에 생성됩니다.
그러므로 이 directory object는 실행 전에 생성해 놓아야 합니다. 위의 경우에는
directory object를 지정하지 않았는데, 그런 경우에는 "data_pump_dir"이라는
directory object가 default로 사용됩니다.
4. target tablespace에 해당하는 datafile을 <A> host에서 <B> host로
복사합니다.
저의 경우, <B> host상에서 "wget" command를 사용해서 ftp protocol를 사용해서 파일을
전송받았는데, tablespace의 datafile의 크기가 23GB나 되는 관계로 19분15초라는 긴 시간이
걸렸습니다. 참고로, Windows의 경우, "ocopy"를 사용하도록 합니다.
|
$ wget ftp://<A_Host>//usr/local/oracle/.../datafile/users.dbf --15:24:47-- ftp://…//usr/local/oracle/…/datafile/users.dbf => `users.dbf' Connecting to 192.168.10.11:21... connected. Logging in as oracle ... Logged in! ==> SYST ... done. ==> PWD ... done. … 100%[========================>] 23,065,534,464 54.8M/s in 19m 15s |
5. <B> host로의 전송이 끝났다면, 앞에서 read only mode로 변경한
tablespace를 <A> database상에서 read write mode로 되돌려 놓습니다.
|
SQL> alter tablespace users read write;
Tablespace altered.
|
6. <B> database상에 target tablespace에 속한 schemas 및 해당 schemas와
관련된 user-defined roles를 생성합니다.
불행히도 original utility를 사용해도 pump utility를 사용해도
schemas 및 user-defined roles를 import시에 자동으로 생성해 주지 않습니다.
왜냐하면 이 정보들은 target tablespace에 종속된 정보가 아니기 때문입니다.
(1) original export를 사용하는 경우
<A> database상에서 dba_users를 select하여, default_tablespace가 target database로
된 schemas를 파악하고, 해당 schema에 접속하여 session_privs 및 session_roles를
select 하여, <B> database상에 생성해 준다.
schemas를 생성하기 전에 target tablespace가 존재하지 않으면 default tablespace로
설정할 수 없기 때문에 임시로 taget tablespace를 생성한다.
|
SQL> connect system/***@<A_DB> Connected.
SQL> select username from dba_users where default_tablespace = 'USERS';
SQL> connect hr/***@<A_DB> Connected.
/* target tablespace에 속한 user가 가지는 privileges를 체크한다 */ SQL> select * from session_privs;
/* target tablespace에 속한 user가 가지는 roles를 체크한다 */ SQL> select * from session_roles;
SQL> connect system/***@<B_DB> Connected.
/* 임시로 target tablespace와 동일한 이름의 tablespace를 생성한다 */ SQL> create tablespace users; Tablespace created.
SQL> create user hr identified by **** default tablespace users 2 quota unlimited on users; User created.
/* <A> database에서 발견한 user-defined roles를 생성한다 */ SQL> create role user_role01; Role created.
SQL> create role user_role02; Role created.
/* 위의 session_roles를 select한 결과로 표시된 roles를 grant한다 */ SQL> grant connect, resource, user_role01, user_role02 to hr; Grant succeeded.
SQL> connect hr/***@<B_DB> Connected.
SQL> select * from session_privs;
SQL> connect system/***@<B_DB> Connected.
/* 위에서 session_privs의 결과와 비교해 부족한 privileges를 grant한다 */ SQL> grant create database link to hr; Grant succeeded. |
(2) pump export를 사용하는 경우
위와 동일한 방법을 사용해도 전혀 상관없지만, pump import의 "sqlfile"이란 option을
사용하여 schema생성 및 privilege설정과 관련된 meta data를 SQL*Plus command 형식으로
얻을 수 있습니다.
1) 우선 위의 예와 동일한 방법으로 해당 tablespace에 종속된 schemas를 추출합니다.
|
SQL> connect system/***@<A_DB> SQL> select username from dba_users where default_tablespace = 'USERS';
|
2) <A> host상에서 pump export를 사용하여 <A> database의 전체 meta data를
추출합니다.
|
$ export ORACLE_SID=<A_DB> $ expdp system/**** full=y dumpfile=A_meta.dmp content=metadata_only Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, ... Copyright (c) 2003, 2007, Oracle. All rights reserved. ... Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY |
3) 해당 dump file을 ftp등을 사용해서 <B> host에 전송합니다.
4) <B> host상에서 pump import를 사용하여 meta data를 생성하는 SQL*Plus command가
지정한 file에 기록되도록 합니다.
|
$ export ORACLE_SID=B_INSTANCE $ impdp system/**** schemas=hr,oe,bi,sh,pm,ix dumpfile=A_meta.dmp sqlfile=meta.sql
Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 09 December, ... Copyright (c) 2003, 2007, Oracle. All rights reserved. ... Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS |
※ "sqlfile" option을 사용하면 meta data만 file에 기록할 뿐 data는 import되지
않습니다. original import의 "show" option과 비슷하다고 보면 됩니다.
5) "meta.sql" file을 열어보면, 다음과 같은 내용이 있는데, 암호화된 password와
tablespace를 생성하기 위한 SQL*Plus command 제외한 나머지(user생성, role생성,
grant문, 등등)는 여기서 다 얻을 수 있습니다.
|
... CREATE USER HR IDENTIFIED BY VALUES 'S:922ACE90A412C' DEFAULT TABLESPACE "USERS" ... -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT CREATE DATABASE LINK TO HR; GRANT CREATE SEQUENCE TO HR; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE -- new object type path: SCHEMA_EXPORT/TABLESPACE_QUOTA |
6) 임시로 target tablespace와 동일한 이름의 tablespace를 생성합니다.
7) "meta.sql"에서 추출한 SQL*Plus command를 <B> database상에서 실행하여,
관련된 schemas를 생성하고 privileges and roles를 grant합니다.
8) 앞의 step-6)에서 임시로 생성한 tablespace를 제거합니다.
⇒ drop tablespace users;
7. 위의 <step-3>에서 export한 dump file을 import합니다.
(1) original export를 사용하는 경우
|
$ imp system/**** transport_tablespace=y file=users.dmp log=users_imp.log datafiles='/B_DB/datafile/users.dbf'
Import: Release 11.1.0.7.0 - Production on Wed Dec 9 19:34:55 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. …
|
(2) pump export를 사용하는 경우
|
$ impdp system/orabong transport_datafiles=/B_DB/datafile/users.dbf dumpfile=users.dmp logfile=work_dir:users_imp.log
Import: Release 11.1.0.7.0 - ... on Wednesday, 09 December, 2009 17:18:26 Copyright (c) 2003, 2007, Oracle. All rights reserved.
... Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
|
※ 위의 시간을 보면 import에 2분이 채 안걸렸다는 것을 알 수 있습니다.
8. 이동된 tablespace는 이동하기 전과 동일한 상태인 read only mode로
있기 때문에, <B> database상에서 transport된 tablespace를
read write mode로 변경합니다. (<step-5> 참조)
9. 이제 이동된 tablespace가 <B> database상에서 제대로 인식이 되는지,
그리고 해당 tablespace에 속했던 schemas 및 속한 objects들도 제대로
존재하는지 체크하는 일만 남았습니다.
혹시 모르니까 "utlrp.sql"을 사용하여 전체 objects를 recompile해 주면
좋을 것 같습니다.
이 작업에 소요된 전체시간을 계산하면, 30분 이하입니다.
저의 경우 아직 익숙하지 않아서 2시간 정도 걸렸지만,
schemas 전체를 export한 후 import할 때 걸리는 시간이 4시간 정도임을 감안하면,
transportable tablespace기능을 사용함으로서
훨씬 적은 시간에 data의 이동이 가능했다는 것을 알 수 있습니다.
중요한 내용이 하나 있는데,
<A> host와 <B> host의 O/S는 동일해야 하며,
<A> database와 <B> database의 version 및 compatibility도 동일해야 합니다.
만약 동일한 Oracle version이라고 해도, "compatible" initialization parameter에
설정된 값이 틀리면, error가 발생하게 됩니다.
※ Oracle9i까지는 반드시 O/S가 일치해야 했는데,
Oracle10g부터는 O/S가 일치하지 않아도
transportable tablespace기능을 이용할 수 있습니다.
제가 Oracle Enterprise Linux 5.3 64bit와 Windows XP 32bit간에
tablespace를 이동시켜보았는데 성공적으로 이루어 졌습니다.
저의 경우, <A> 및 <B> database가 둘다 11.1.0.7.0 이었는데,
<A>는 compatible이 11.1.0.7.0 이었고, <B>는 11.1.0.0.0 인 상태로 import를 수행했더니,
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 11.1.0.7.0 cannot be used by release 11.1.0.0.0
이라는 error가 발생했습니다.
이런 경우, <B> database의 "compatible" parameter를 변경한 후
database를 restart한 후에야 import를 할 수 있습니다.
'Oracle > Migration' 카테고리의 다른 글
| [펌] exp / imp 명령어 (0) | 2017.08.17 |
|---|
