正常情况下,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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/429786/viewspace-776651/