通過NETWORK_LINK在客戶端執行EXPDP

wailon發表於2013-11-15

正常情況下,EXPDP只能在伺服器操作。但通過NETWORK_LINK可以在客戶端備份遠端伺服器的資料。
以下是具體操作過程:

[oracle@dg ~(03:04:03)]$ export ORACLE_SID=wailon
[oracle@dg ~(03:04:10)]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 03:04:15 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 檢視DBLINK
03:04:33 SYS@wailon> col host for a30
03:04:40 SYS@wailon> select name,host,userid from link$;

NAME                                     HOST                           USERID
---------------------------------------- ------------------------------ ------------------------------
OGG                                      ogg                            SCOTT
DB_WAILON                                wailon                         SCOTT

-- 建立本地的備份目錄MY_DIR
03:04:41 SYS@wailon> select * from all_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SYS                            DATA_PUMP_DIR
/u01/app/oracle/admin/wailon/dpdump/

SYS                            XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS                            GGS_DDL_TRACE
/u01/app/oracle/diag/rdbms/wailon/wailon/trace

SYS                            BACKUP_DIR
/home/oracle/dbbackup


03:04:56 SYS@wailon> create directory my_dir as '/home/oracle/';

Directory created.

03:05:25 SYS@wailon> grant read,write on directory my_dir to scott;

Grant succeeded.

03:05:41 SYS@wailon> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- 通過NETWORK_LINK將遠端資料庫儲存在本地
-- 注意SYS_EXPORT_SCHEMA_01不屬於SCOTT使用者,匯出過程中自動生成
[oracle@dg ~(03:05:43)]$ expdp userid=scott/tiger directory=my_dir dumpfile=scott.dmp schemas=scott network_link=db_wailon

Export: Release 11.2.0.3.0 - Production on Sun Sep 29 03:06:40 2013

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, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  userid=scott/******** directory=my_dir dumpfile=scott.dmp schemas=scott network_link=db_wailon
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 14.62 MB
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."GGS_DDL_HIST"                      5.202 MB    2744 rows
. . exported "SCOTT"."GGS_MARKER"                        2.157 MB    1781 rows
. . exported "SCOTT"."SYS_EXPORT_SCHEMA_01"              558.4 KB    1335 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  10.99 KB     156 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:08:17

-- 匯入資料到另一使用者
[oracle@dg ~(03:08:47)]$ impdp scott/tiger directory=my_dir dumpfile=scott.dmp remap_schema=scott:lrj

Import: Release 11.2.0.3.0 - Production on Sun Sep 29 03:09:58 2013

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, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=my_dir dumpfile=scott.dmp remap_schema=scott:lrj
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"LRJ" already exists
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LRJ"."GGS_DDL_HIST"                        5.202 MB    2744 rows
. . imported "LRJ"."GGS_MARKER"                          2.157 MB    1781 rows
. . imported "LRJ"."SYS_EXPORT_SCHEMA_01"                558.4 KB    1335 rows
. . imported "LRJ"."CHECKPOINT"                          10.26 KB       2 rows
. . imported "LRJ"."DEPT"                                5.984 KB       6 rows
. . imported "LRJ"."EMP"                                 8.640 KB      18 rows
. . imported "LRJ"."GGS_DDL_HIST_ALT"                    10.99 KB     156 rows
. . imported "LRJ"."GGS_SETUP"                           5.546 KB       6 rows
. . imported "LRJ"."SALGRADE"                            5.859 KB       5 rows
. . imported "LRJ"."BONUS"                                   0 KB       0 rows
. . imported "LRJ"."CHECKPOINT_LOX"                          0 KB       0 rows
. . imported "LRJ"."GGS_DDL_COLUMNS"                         0 KB       0 rows
. . imported "LRJ"."GGS_DDL_LOG_GROUPS"                      0 KB       0 rows
. . imported "LRJ"."GGS_DDL_OBJECTS"                         0 KB       0 rows
. . imported "LRJ"."GGS_DDL_PARTITIONS"                      0 KB       0 rows
. . imported "LRJ"."GGS_DDL_PRIMARY_KEYS"                    0 KB       0 rows
. . imported "LRJ"."GGS_DDL_RULES"                           0 KB       0 rows
. . imported "LRJ"."GGS_DDL_RULES_LOG"                       0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 03:10:42


-- 檢查資料是否已匯入
[oracle@dg ~(03:11:06)]$ sqlplus lrj/lrj

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 29 03:15:33 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

03:15:33 LRJ@wailon> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BBED_TEST                      TABLE
BONUS                          TABLE
CHECKPOINT                     TABLE
CHECKPOINT_LOX                 TABLE
DEPT                           TABLE
EMP                            TABLE
GGS_DDL_COLUMNS                TABLE
GGS_DDL_HIST                   TABLE
GGS_DDL_HIST_ALT               TABLE
GGS_DDL_LOG_GROUPS             TABLE
GGS_DDL_OBJECTS                TABLE
GGS_DDL_PARTITIONS             TABLE
GGS_DDL_PRIMARY_KEYS           TABLE
GGS_DDL_RULES                  TABLE
GGS_DDL_RULES_LOG              TABLE
GGS_MARKER                     TABLE
GGS_SETUP                      TABLE
GGS_STICK                      TABLE
GGS_TEMP_COLS                  TABLE
GGS_TEMP_UK                    TABLE
SALGRADE                       TABLE
SYS_EXPORT_SCHEMA_01           TABLE

22 rows selected.

03:15:37 LRJ@wailon> desc SYS_EXPORT_SCHEMA_01
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 PROCESS_ORDER                                                              NUMBER
 DUPLICATE                                                                  NUMBER
 DUMP_FILEID                                                                NUMBER
 DUMP_POSITION                                                              NUMBER
 DUMP_LENGTH                                                                NUMBER
 DUMP_ORIG_LENGTH                                                           NUMBER
 DUMP_ALLOCATION                                                            NUMBER
 COMPLETED_ROWS                                                             NUMBER
 ERROR_COUNT                                                                NUMBER
 ELAPSED_TIME                                                               NUMBER
 OBJECT_TYPE_PATH                                                           VARCHAR2(200)
 OBJECT_PATH_SEQNO                                                          NUMBER
 OBJECT_TYPE                                                                VARCHAR2(30)
 IN_PROGRESS                                                                CHAR(1)
 OBJECT_NAME                                                                VARCHAR2(500)
 OBJECT_LONG_NAME                                                           VARCHAR2(4000)
 OBJECT_SCHEMA                                                              VARCHAR2(30)
 ORIGINAL_OBJECT_SCHEMA                                                     VARCHAR2(30)
 ORIGINAL_OBJECT_NAME                                                       VARCHAR2(4000)
 PARTITION_NAME                                                             VARCHAR2(30)
 SUBPARTITION_NAME                                                          VARCHAR2(30)
 DATAOBJ_NUM                                                                NUMBER
 FLAGS                                                                      NUMBER
 PROPERTY                                                                   NUMBER
 TRIGFLAG                                                                   NUMBER
 CREATION_LEVEL                                                             NUMBER
 COMPLETION_TIME                                                            DATE
 OBJECT_TABLESPACE                                                          VARCHAR2(30)
 SIZE_ESTIMATE                                                              NUMBER
 OBJECT_ROW                                                                 NUMBER
 PROCESSING_STATE                                                           CHAR(1)
 PROCESSING_STATUS                                                          CHAR(1)
 BASE_PROCESS_ORDER                                                         NUMBER
 BASE_OBJECT_TYPE                                                           VARCHAR2(30)
 BASE_OBJECT_NAME                                                           VARCHAR2(30)
 BASE_OBJECT_SCHEMA                                                         VARCHAR2(30)
 ANCESTOR_PROCESS_ORDER                                                     NUMBER
 DOMAIN_PROCESS_ORDER                                                       NUMBER
 PARALLELIZATION                                                            NUMBER
 UNLOAD_METHOD                                                              NUMBER
 LOAD_METHOD                                                                NUMBER
 GRANULES                                                                   NUMBER
 SCN                                                                        NUMBER
 GRANTOR                                                                    VARCHAR2(30)
 XML_CLOB                                                                   CLOB
 PARENT_PROCESS_ORDER                                                       NUMBER
 NAME                                                                       VARCHAR2(30)
 VALUE_T                                                                    VARCHAR2(4000)
 VALUE_N                                                                    NUMBER
 IS_DEFAULT                                                                 NUMBER
 FILE_TYPE                                                                  NUMBER
 USER_DIRECTORY                                                             VARCHAR2(4000)
 USER_FILE_NAME                                                             VARCHAR2(4000)
 FILE_NAME                                                                  VARCHAR2(4000)
 EXTEND_SIZE                                                                NUMBER
 FILE_MAX_SIZE                                                              NUMBER
 PROCESS_NAME                                                               VARCHAR2(30)
 LAST_UPDATE                                                                DATE
 WORK_ITEM                                                                  VARCHAR2(30)
 OBJECT_NUMBER                                                              NUMBER
 COMPLETED_BYTES                                                            NUMBER
 TOTAL_BYTES                                                                NUMBER
 METADATA_IO                                                                NUMBER
 DATA_IO                                                                    NUMBER
 CUMULATIVE_TIME                                                            NUMBER
 PACKET_NUMBER                                                              NUMBER
 INSTANCE_ID                                                                NUMBER
 OLD_VALUE                                                                  VARCHAR2(4000)
 SEED                                                                       NUMBER
 LAST_FILE                                                                  NUMBER
 USER_NAME                                                                  VARCHAR2(30)
 OPERATION                                                                  VARCHAR2(30)
 JOB_MODE                                                                   VARCHAR2(30)
 QUEUE_TABNUM                                                               NUMBER
 CONTROL_QUEUE                                                              VARCHAR2(30)
 STATUS_QUEUE                                                               VARCHAR2(30)
 REMOTE_LINK                                                                VARCHAR2(4000)
 VERSION                                                                    NUMBER
 JOB_VERSION                                                                VARCHAR2(30)
 DB_VERSION                                                                 VARCHAR2(30)
 TIMEZONE                                                                   VARCHAR2(64)
 STATE                                                                      VARCHAR2(30)
 PHASE                                                                      NUMBER
 GUID                                                                       RAW(16)
 START_TIME                                                                 DATE
 BLOCK_SIZE                                                                 NUMBER
 METADATA_BUFFER_SIZE                                                       NUMBER
 DATA_BUFFER_SIZE                                                           NUMBER
 DEGREE                                                                     NUMBER
 PLATFORM                                                                   VARCHAR2(101)
 ABORT_STEP                                                                 NUMBER
 INSTANCE                                                                   VARCHAR2(60)
 CLUSTER_OK                                                                 NUMBER
 SERVICE_NAME                                                               VARCHAR2(100)
 OBJECT_INT_OID                                                             VARCHAR2(32)


-- 未使用NETWORK_LINK匯出遠端資料庫時,沒有SYS_EXPORT_SCHEMA_01表
[oracle@dg ~(03:27:34)]$ expdp userid=scott/tiger directory=my_dir dumpfile=scott1.dmp schemas=scott reuse_dumpfiles=y
Export: Release 11.2.0.3.0 - Production on Sun Sep 29 03:27:41 2013

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, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode has set reuse_dumpfiles=true parameter.
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  userid=scott/******** directory=my_dir dumpfile=scott1.dmp schemas=scott reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.37 MB
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
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/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."GGS_DDL_HIST"                      6.825 MB    3618 rows
. . exported "SCOTT"."GGS_MARKER"                        2.828 MB    2333 rows
. . exported "SCOTT"."CHECKPOINT"                        10.26 KB       2 rows
. . exported "SCOTT"."DEPT"                              5.984 KB       6 rows
. . exported "SCOTT"."EMP"                               8.640 KB      18 rows
. . exported "SCOTT"."GGS_DDL_HIST_ALT"                  12.75 KB     209 rows
. . exported "SCOTT"."GGS_SETUP"                         5.546 KB       6 rows
. . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CHECKPOINT_LOX"                        0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_COLUMNS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_LOG_GROUPS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_OBJECTS"                       0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PARTITIONS"                    0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_PRIMARY_KEYS"                  0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES"                         0 KB       0 rows
. . exported "SCOTT"."GGS_DDL_RULES_LOG"                     0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/scott1.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:28:47

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/429786/viewspace-776651/,如需轉載,請註明出處,否則將追究法律責任。

相關文章