oracle 数据泵备份还原,oracle10g 数据泵 备份和恢复

一：备份

1。data pump包括下面几个部件：

The command-line clients, expdp and impdp

The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)

The DBMS_METADATA PL/SQL package (also known as the Metadata API)

2。expdp, impdp和原先的exp,imp不兼容，也就是用exp导出的文件用impdp是无法导入的。

3。data pump不支持XML schemas

4。使用前必须要先创建目录：

SQL> CREATE DIRECTORY dpump_dir as '/oracle/oradata/ORCL10/pumpdata';

5。给导入导出的用户赋予目录的读写权限：

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO scott;

6。设置操作系统环境变量：

export DATA_PUMP_DIR=dpump_dir

7。执行datapump export

[zhangleyi@as software]$ expdp scott/tiger schemas=wanyan dumpfile=wanyan_db165_200611031630.dmp DIRECTORY=dpump_dir

Export: Release 10.1.0.2.0 - Production on Thursday, 26 February, 2004 22:08

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=emp dumpfile=emp.dmp DIRECTORY=dpump_dir

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SCOTT"."EMP" 7.820 KB 14 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/oracle/oradata/ORCL10/pumpdata/emp.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 22:09

二，恢复：

impdp wanyan/wanyan dumpfile=emp.dmp directory=dump_dir logfile=emp.log

处理对象类型 TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA

. . 导入了 "WANYAN"."WY_B_USER"                        80.75 MB  376099 行

. . 导入了 "WANYAN"."TEMP_INSIGHT"                     40.24 MB  537133 行

. . 导入了 "WANYAN"."temp_user"                        26.20 MB  534528 行

. . 导入了 "WANYAN"."WY_D_TEMPLATE"                    9.368 MB    2223 行

. . 导入了 "WANYAN"."WY_B_SURVEY"                      2.568 MB     953 行

. . 导入了 "WANYAN"."WY_B_BLOG"                        182.8 KB      22 行

. . 导入了 "WANYAN"."WY_D_ANSWERDETAIL"                441.8 KB    6021 行

. . 导入了 "WANYAN"."WY_D_ANSWER"                      342.1 KB    5919 行

. . 导入了 "WANYAN"."WY_D_GROUP"                       143.2 KB      20 行

. . 导入了 "WANYAN"."WY_B_ARTICLE"                     74.09 KB      42 行

. . 导入了 "WANYAN"."WY_D_EXAMRESULT"                  58.05 KB     269 行

. . 导入了 "WANYAN"."WY_D_INBOX"                       61.48 KB     424 行

. . 导入了 "WANYAN"."WY_D_KEYWORDS"                    44.06 KB    2532 行

. . 导入了 "WANYAN"."WY_D_SENDBOX"                     61.82 KB     442 行

. . 导入了 "WANYAN"."WY_B_ADMINUSER"                   8.140 KB       8 行

. . 导入了 "WANYAN"."WY_B_COLUMN"                      11.38 KB      20 行

. . 导入了 "WANYAN"."WY_B_COMMENT"                     31.37 KB     217 行

. . 导入了 "WANYAN"."WY_B_ROLE"                        6.031 KB       7 行

. . 导入了 "WANYAN"."WY_D_ATTENTION"                   6.367 KB      20 行

. . 导入了 "WANYAN"."WY_D_COMMEND"                     7.625 KB      12 行

. . 导入了 "WANYAN"."WY_D_MAILTEMPLATES"               14.55 KB      11 行

. . 导入了 "WANYAN"."WY_D_PREVIEW"                     7.476 KB       7 行

. . 导入了 "WANYAN"."WY_D_SOURCE"                      9.375 KB     120 行

. . 导入了 "WANYAN"."WY_D_USERSCORERECORD"             8.164 KB      21 行

. . 导入了 "WANYAN"."WY_R_FRIENDBLOG"                  5.992 KB       1 行

. . 导入了 "WANYAN"."WY_R_GROUPMEMBER"                 20.98 KB     246 行

. . 导入了 "WANYAN"."WY_R_GROUPNOTE"                   27.80 KB     191 行

. . 导入了 "WANYAN"."WY_R_ROLE_PERMISSION"             20.46 KB     441 行

. . 导入了 "WANYAN"."WY_D_COMMONMESSAGE"                   0 KB       0 行

. . 导入了 "WANYAN"."WY_D_INVITATION"                      0 KB       0 行