Neo-Oracle Home

알고는 있었지만 테스트를 한번도 안해둬서 생각났을 때 해보고 기록을 남긴다.

tnsnames.ora의 tnsnames를 사용해 가능한줄 알았는데 계정/비밀번호가 필요해서 DB link를 통해 가능하다.

remote의 DB에 접속해 scott 스키마만 local에서 expdp로 받는 테스트이다.

1. local DB에 system으로 연결하여 remote 측으로 연결할 dblink를 만든다.
$ sqlplus system/orakle

SQL> create database link exptest
 connect to system
 identified by oracle
 using 'test';

※구분을 위해 local DB의 system 비밀번호는 orakle로 하였다. test는 tnsnames.ora에 설정해두었다.

2. dblink가 정상 동작하는지 확인한다.

3. directory를 생성한다.(system 유저로)
SQL> create directory pump as '/oracle/expdp';

4. expdp를 실행한다.
[oracle@11g expdp]$ expdp system/orakle schemas=scott network_link=test directory=pump dumpfile=scott.dmp flashback_time=sysdate

Export: Release 11.2.0.3.0 - Production on Wed Apr 2 14:45:52 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott network_link=test directory=pump dumpfile=scott.dmp flashback_time=sysdate
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /oracle/expdp/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:46:18

일단 remote는 11.2.0.4이고 local은 11.2.0.3인데 schema 단위로 가능한 것을 테스트했다.
full=y로 진행시 에러가 났는데 아마도 remote측 SGA가 너무 작은게 문제일 것으로 생각된다.