案例讨论数据泵日常应用

zcs0237 · 发表于 2013-10-14 14:19

本帖最后由 zcs0237 于 2013-11-13 22:04 编辑

讨论题目：大家可以针对以下任何一个或者几个问题作出回复。

--你在工作中在什么情况下使用过逻辑导入导出？经常使用还是很少使用?
--使用数据泵备份有哪些注意事项?
--提高数据泵的速度的方法有哪些？
-- yangtingkun文章：进行大批量泵入可先设为NOARCHIVELOG，你有没有这采取过这种办法？
--华数的兄弟迁移十来G的数据时两个库字符集不兼容，你有什么简捷稳妥的方法？
--最近用exp做过增量备份吗(exdp已不支持)？
--分享逻辑导入导出的其它知识、使用体验

讨论时间：2013.10.14——2013.10.31
讨论奖品：活动结束后将抽取5-10名会员赠送itpub12周年徽章一枚+666pub币。

欢迎大家分享工作中的数据泵经验技巧，挖掘这个小工具的价值!
wanghao2979
ydjh460
super_sky
iqlife
hexel

zcs0237 · 发表于 2013-10-14 14:19

本帖最后由 zcs0237 于 2013-10-14 14:52 编辑

抛砖引玉：
--expdp-impdp实验手册.pdf全部测试成功
--可按本文先后顺序复制文中代码进行调试
--本人才疏学浅，发现错误欢迎立即提出，方便及时更正

zcs0237 · 发表于 2013-10-14 14:19

本帖最后由 zcs0237 于 2013-10-14 14:19 编辑

一、不管是导入还是导出必须做的准备工作
1、首先建立DIRECTORY对象
SYS@zcs11g > create or replace directory bk as '/bak/';
2、需要为数据库用户授予使用DIRECTORY对象权限。
SYS@zcs11g > grant read,write on directory bk to public;
SYS@zcs11g > col directory_path for a30
select directory_name,directory_path from dba_directories where directory_name like '%BK%';
BK             /bak/
SYS@zcs11g > --drop directory bk;
3、不能直接指定OS目录，只能存放在DIRECTORY对象对应的OS目录中
$ expdp directory=bk

二、用户账号的准备
1、配置scott
SQL>  alter user scott identified by tiger;
SQL>  alter user scott account unlock;
2、配置system
SQL>  alter user system identified by manager;
SQL>  alter user system account unlock;

三、数据泵知识补充
1、数据泵概念/原理 --Master Table/MT

用来保存整个过程中的细节信息,它使导出或者导入的重启动才变为可能。
--Master Process/MP
每一个导出或者导入的Job都会产生一个MP. 它控制整个Job,包括和客户端通信,创建并控制worker 进程。
--Worker Process/WP
是并行化进行实际的卸载与装载元数据和表数据的进程。进程的数量和PARALLEL指定的数值相等 .在整个Job过程中,该数量可以进行调整(标准版下并行度为1)。
2、EXPDP是数据库内部定义的任务，与客户端无关，执行过程中按Ctrl+C进入export命令行
Export> status                      //查看任务名及详细信息
Export> stop_job                   //停止任务
Export> exit                         //退出export界面
$ expdp zcs/zcs attach=任务名称       //再次连接到这个任务
Export> start_job                   //重新启动导出

zcs0237 · 发表于 2013-10-14 14:19

本帖最后由 zcs0237 于 2013-10-14 13:53 编辑

--如果导出的schema为当前schema,则不需要指定schema过滤条件
一、导出scott模式下的emp表
$ expdp scott/tiger directory=bk grants=n constraints=n indexes=n dumpfile=emp.pmp tables=emp
. . exported "SCOTT"."EMP"             8.570 KB    14 rows
--Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:  /bak/emp.pmp
--Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 04:20:42
二、补充：使用数据泵生成建表的DDL语句
$ impdp system/admin full=y directory=bk dumpfile=emp.pmp nologfile=y sqlfile=all.sql
$ egrep -v 'SET EVENTS|--'  /bak/all.sql
CREATE TABLE "SCOTT"."EMP" ... ;
三、模拟误删除数据
SYS@zcs11g > alter table scott.emp rename to empold;  //或drop table scott.emp;
四、导入emp表到scott模式
$ impdp scott/tiger directory=bk dumpfile=emp.pmp tables=emp ignore=y
. . imported "SCOTT"."EMP"                            8.570 KB    14 rows
--Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 04:23:17
SYS@zcs11g > select count(*) from scott.emp;
      14

zcs0237 · 发表于 2013-10-14 14:20

本帖最后由 zcs0237 于 2013-10-14 13:55 编辑

一、content条件（content=data_only,content=metadata_only）
1、content=all（默认）
$ expdp scott/tiger directory=bk grants=n indexes=n constraints=n dumpfile=a.pmp tables=emp
--Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP"                            8.570 KB    14 rows
--Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
2、content=metadata_only
$ expdp scott/tiger directory=bk grants=n indexes=n constraints=n dumpfile=b.pmp tables=emp content=metadata_only
--Processing object type TABLE_EXPORT/TABLE/TABLE
--Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
3、content=data_only
$ expdp scott/tiger directory=bk grants=n indexes=n constraints=n dumpfile=c.pmp tables=emp content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
--Total estimation using BLOCKS method: 64 KB
. . exported "SCOTT"."EMP"                            8.570 KB    14 rows
--Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
二、query条件（可以使用LIKE, NOT LIKE,IN, NOT IN, = , != 等符号）
$ expdp scott/tiger directory=bk dumpfile=d.pmp query=emp:'"where rownum<2"'
. . exported "SCOTT"."DEPT"                            5.937 KB    4 rows
. . exported "SCOTT"."EMP"                            8.039 KB    1 rows
. . exported "SCOTT"."EMPOLD"                         8.570 KB    14 rows
. . exported "SCOTT"."SALGRADE"                         5.867 KB    5 rows
. . exported "SCOTT"."BONUS"                               0 KB    0 rows
-- Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:18:27
三、include条件（include仅包含与exclude仅排除不能同时用）
1、创建参数文件
$ cat << zcs  > a.par
directory=bk
dumpfile=e.pmp
logfile=e.log
include=TABLE:"IN ('EMP')"
zcs
2、调用参数文件
$ expdp scott/tiger parfile=a.par
. . exported "SCOTT"."EMP"                            8.570 KB    14 rows
--Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
--Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:  /bak/e.pmp
--Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 04:46:17

zcs0237 · 发表于 2013-10-14 14:20

本帖最后由 zcs0237 于 2013-10-14 14:17 编辑

--表空间传输是把一个数据库上的数据文件附加到另一个数据库中，就像复制文件一样快

05.1-准备工作
测试环境说明：同一机器上安装了两套数据库
--oracle1用户，对应database10g，实例名称为orcl1，数据文件在/orcl1
--oracle2用户，对应database10g，实例名称为orcl2，数据文件在/orcl2
一、源和目标数据库必须使用相同的字符集和国家字符集
SYS@zcs11g>  select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';
-- AL16UTF16
SYS@zcs11g >  select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
-- AL32UTF8
$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8  --客户端的字符集
二、检查源、目标平台的字节顺序(ENDIAN_FORMAT相同无需转换)
SYS@zcs11g > select d.platform_name,endian_format from v$transportable_platform tp, v$database d where tp.platform_name = d.platform_name;
Linux IA (32-bit)       Little

05.2-在database10g=orcl1库上导出表空间zcs

一、源数据库创建用户、表空间、表
1、创建被传输的表空间
# su - oracle1
$ sqlplus / as sysdba
SYS@orcl1>  create tablespace zcs datafile '/tmp/zcs.dbf' size 10m;
2、创建用户，并将表空间作为默认表空间，并授权，在该表空间创建测试用表
SYS@zcs11g >  drop user zcs cascade;
create user zcs identified by zcs default tablespace zcs;
grant connect,resource to zcs;
create table zcs.test as select * from scott.emp;
二、传的表空间一定要“自包含”（没有任何对象引用指向外部表空间集）
SYS@zcs11g > --exec dbms_tts.transport_set_check('ZCS', TRUE);
SYS@zcs11g > --select * from transport_set_violations;
--no rows selected（如果表空间集满足自包含检查，则视图返回空记录）
三、产生传输表空间集
1、创建目录对象，并设置可读可写
SYS@zcs11g >  create or replace directory tmp as '/tmp/';
grant read,write on directory tmp to public;
select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name like '%TMP%';
--TMP    /tmp/
2、将表空间设置为只读
SYS@orcl1> alter tablespace zcs read only;
3、使用数据泵导出传输表空间的源数据
SYS@orcl1> host expdp \'/ as sysdba\'  directory=tmp transport_tablespaces=zcs dumpfile=zcs.pmp
-- Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 04:10:17
4、将表空间的数据文件和导出的DMP文件传送到目标数据库平台上
# su - oracle2
$ cp /tmp/zcs.dbf /orcl2
5、将源库的表空间恢复为可读写
SYS@orcl1>  alter tablespace zcs read write;
切记一定记得先做第4步，否则在目标库导放时会报以下错误
--ORA-19722: datafile /orcl2/zcs.dbf is an incorrect version

05.3-在database11g=orcl2库上导入表空间zcs

1、在目标数据库中，创建目录，授予目录读写权限
$ sqlplus / as sysdba
SYS@zcs11g > create directory tmp as '/tmp/';
grant read,write on directory tmp to public;
2、创建用户，授予connect,resource角色授予给用户
SYS@zcs11g > create user zcs identified by zcs default tablespace users;
grant connect,resource to zcs;
3、使用数据库泵，导入到目标数据库orcl2中
SYS@zcs11g >  host impdp \'/ as sysdba\' transport_datafiles=/orcl2/zcs.dbf directory=tmp dumpfile=zcs.pmp
--Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 04:14:30
6、将被导入的表空间设置为可读可写
SYS@zcs11g >  alter tablespace zcs read write;
7、测试传输结果
SYS@zcs11g > select table_name,tablespace_name from dba_tables where owner='ZCS';
TEST                         ZCS
8、为使用方便，指定zcs的默认表空间
SYS@zcs11g >  alter user zcs default tablespace zcs;

zcs0237 · 发表于 2013-10-14 14:26

本帖最后由 zcs0237 于 2013-10-14 15:11 编辑

一、用estimate_only预估需要的空间
$ expdp \'/ as sysdba\' directory=bk estimate_only=y full=y
-- Total estimation using BLOCKS method: 119.6 MB
--Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 05:50:03
二、完整的数据库导出（要求用户拥有EXP_FULL_DATABASE角色）
$ time expdp \'/ as sysdba\' directory=bk full=y compress=n statistics=none parallel=2 filesize=90m direct=y  dumpfile=orcl2_%U.pmp
--Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /bak/orcl2_01.pmp，/bak/orcl2_02.pmp，/bak/orcl2_03.pmp
--Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 04:36:14
--real 9m40.506s
三、模拟误删数据
SYS@zcs11g >  drop user scott cascade;
四、完整的数据库恢复（要求用户拥有EXP_FULL_DATABASE角色）
$ time impdp  \'/ as sysdba\' directory=bk file=/bak/orcl2_01.pmp,/bak/orcl2_02.pmp,/bak/orcl2_03.pmp full=y ignore=y parallel=2
--Job "SYS"."SYS_IMPORT_FULL_01" completed with 8437 error(s) at 04:52:06
--real 11m32.828s
SYS@zcs11g > select count(*) from dba_tables where owner='SCOTT';
--    4

sunner888 · 发表于 2013-10-14 15:23

lnwf5188588 · 发表于 2013-10-14 15:24

--在什么情况下使用逻辑导入导出?
  1、特定的表备份或迁移
  2、跨度比较大的异构数据库迁移
  3、其他情况

--使用数据泵备份有哪些注意事项?
  1、必须先建立directory，并且要导出和导入的用户对这个目录必须有读写权限
  2、一般情况下，只能在数据库服务端执行
  3、要导出和导入的用户用户必须有exp_full_database的权限

--提高数据泵的速度的方法有哪些？
  1、加并行，建议设置和系统cpu数量相当
  2、尽量减少redo的产生，可以对相对表设置为nologging，导出后再恢复为logging
  3、保证临时表空间大小足够可用

--华数的兄弟迁移十来G的数据时碰到字符集不兼容，你有什么简捷稳妥的方法？
  10来G的数据可以用exp进行迁移，在客户端设置和目标库相同的NLS_LANG，对源库进行导出，然后再导入到目标库

zcs0237 · 发表于 2013-10-14 15:28

本帖最后由 zcs0237 于 2013-10-14 15:54 编辑

lnwf5188588 发表于 2013-10-14 15:24
--在什么情况下使用逻辑导入导出?
1、特定的表备份或迁移
2、跨度比较大的异构数据库迁移

回复好快呀

抱歉，原来我没描述清楚，现在改了一下：
--华数的兄弟迁移十来G的数据时两个库字符集不兼容，你有什么简捷稳妥的方法？

[讨论] 案例讨论数据泵日常应用

第01部分-数据泵实验准备

第02部分-Table Mode案例：误删数据的恢复

第03部分-Table Mode案例：高级过滤

第05部分-Tablespace Mode案例：表空间传输

第06部分-Full Mode案例：作为物理备份的补充