从本质理解oracle数据泵（expdp/impdp）原理 -m6米乐安卓版下载

原创范伟林 2022-02-16

2530

一、前言

相信数据泵我们会经常用到，但是没有理解其原理和普通exp/imp的区别，往往遇到一些错误就很难进行排查；

其实数据泵的本质就是后台存储过程作业，由dw进程进行工作，此进程可以并发多个运行，相关作业状态可以由dba_datapump_jobs视图查看。在终端关闭或者ctrl c啥的依然不会影响作业的运行，若要进行作业管理需要attach；

利用数据泵可以做到逻辑的备份，数据迁移，传输表空间等，dba比较常用的工具；

下面我们做个简单案例，环境为12c版本。

二、数据导出(expdp)

数据的导出为数据库的存储过程作业，所以需要用到目录对象，定义导出dump文件、日志文件等的路径信息；
导出任务发起，作业会数据库自动创建，作业会自动创建master表记录作业信息，作业状态也会记录在dba_datapump_jobs视图
数据导出源端为数据库，目标端为dump文件，也就是灵活将数据库的相关对象写入到dump物理文件，理解链接关系，跟目标库是无关系的
当然需要用到exp_full_database角色权限，目录对象的执行读写权限

1、查看涉及的表空间信息

表：

set line 300 pages 100
col tablespace_name for a40
col owner for a30
select distinct tablespace_name,owner from dba_tables
where owner in (select username from dba_users where account_status='open' and username not in ('sys','system','dbsnmp'))
order by 2;

索引：

set line 300 pages 100
col tablespace_name for a40
col owner for a30
select distinct tablespace_name,owner from dba_indexes
where owner in (select username from dba_users where account_status='open' and username not in ('sys','system','dbsnmp'))
order by 2;

note：规划需要导出的schema，找出其数据涉及的表空间，方便后续的数据导入规划

2、查看数据库逻辑大小

select sum(bytes)/1024/1024/1024 from dba_segments;

note：也可以事先查出比较大的表，例如千万级别以上的，然后在导的过程中看进度也心里有数，也可以单独拎出来导

3、创建目录对象

准备目录
mkdir /backup/dumpfile
chown oracle.oinstall /backup/dumpfile
创建
create directory mydir as '/backup/dumpfile';

note：这里默认是用sys进行创建，普通用户需要进行授权

4、导出数据

创建参数文件

vi /home/oracle/wl_full.par
userid=system/*******
directory=dumpdir
dumpfile=wl20220216_%u.dmp
logfile=wl20220216_exp.log
job_name=wl_export_full
logtime=all
schema=wl
exclude=statistics
compression=all
parallel=8
cluster=no

note：也可以直接写导出参数，看个人习惯；参数比较多，可以得知数据泵很灵活功能强大，可以-help查看所有参数，这里列举几个参数；

userid 为用户凭证，普通用户操作需要授权
directory 为目录对象
dumpfile 为导出文件名称
logfile 为导出日志名称
job_name 为作业名称，自定义作业名称方便管理
logtime all为记录每个环节的时间
schema 为模式名称，通俗讲也就是导出哪个业务用户的数据
exclude 为排除不导出的内容，这里列举为统计信息，当然可以是其他的对象信息
compression 为导出数据的压缩级别，all为数据和元数据全部压缩，最高压缩类型，当然可以结合压缩算法compression_algorithm达到更高级别的压缩
parallel 为并行度，也就是定义dw进程的个数
cluster 为rac特有参数，定义作业是否在每个节点运行

执行任务

expdp parfile=/home/oracle/wl_full.par

5、查看作业日志也状态

观察导出日志

也就是logfile参数定义的日志文件

tail -1000f wl20220216_exp.log

export: release 12.2.0.1.0 - production on thu oct 15 11:54:07 2020
米乐app官网下载 copyright (c) 1982, 2017, oracle and/or its affiliates.  all rights reserved.
;;; 
connected to: oracle database 12c enterprise edition release 12.2.0.1.0 - 64bit production
15-oct-20 11:54:19.635: ;;; **************************************************************************
15-oct-20 11:54:19.640: ;;; parfile values:
15-oct-20 11:54:19.645: ;;;  parfile:  parallel=8
15-oct-20 11:54:19.650: ;;;  parfile:  cluster=n
15-oct-20 11:54:19.655: ;;;  parfile:  schemas=wl
15-oct-20 11:54:19.661: ;;;  parfile:  logtime=all
15-oct-20 11:54:19.666: ;;;  parfile:  logfile=wl20220216_exp.log
15-oct-20 11:54:19.671: ;;;  parfile:  dumpfile=wl20220216_%u.dmp
15-oct-20 11:54:19.754: ;;;  parfile:  directory=mydir
15-oct-20 11:54:19.760: ;;; **************************************************************************
15-oct-20 11:54:20.427: flashback automatically enabled to preserve database integrity.
15-oct-20 11:54:21.601: starting "sys"."sys_export_schema_01":  "/******** as sysdba" parfile=/home/oracle/wl_full.par
15-oct-20 11:54:24.230: processing object type schema_export/system_grant
15-oct-20 11:54:24.273: processing object type schema_export/role_grant
15-oct-20 11:54:24.274: processing object type schema_export/default_role
15-oct-20 11:54:24.275: processing object type schema_export/user
15-oct-20 11:54:25.968: processing object type schema_export/pre_schema/procact_schema
15-oct-20 11:54:26.146: processing object type schema_export/db_link
15-oct-20 11:54:26.773: processing object type schema_export/synonym/synonym
15-oct-20 11:54:26.900: processing object type schema_export/table/index/statistics/functional_index/index_statistics
15-oct-20 11:54:26.904: processing object type schema_export/package/package_body
15-oct-20 11:54:27.075: processing object type schema_export/table/index/statistics/index_statistics
15-oct-20 11:54:27.222: processing object type schema_export/table/statistics/table_statistics
15-oct-20 11:54:27.621: processing object type schema_export/sequence/sequence
15-oct-20 11:54:28.311: processing object type schema_export/table/table_data
15-oct-20 11:54:29.010: processing object type schema_export/table/procact_instance
15-oct-20 11:54:29.999: processing object type schema_export/table/grant/owner_grant/object_grant
15-oct-20 11:54:31.714: processing object type schema_export/package/package_spec
15-oct-20 11:54:31.830: processing object type schema_export/function/function
15-oct-20 11:54:31.969: processing object type schema_export/procedure/procedure
15-oct-20 11:54:32.145: processing object type schema_export/package/compile_package/package_spec/alter_package_spec
15-oct-20 11:54:32.157: processing object type schema_export/function/alter_function
15-oct-20 11:54:32.163: processing object type schema_export/procedure/alter_procedure
15-oct-20 11:54:32.582: processing object type schema_export/table/comment
15-oct-20 11:54:32.630: processing object type schema_export/table/table
15-oct-20 11:54:32.972: processing object type schema_export/table/index/functional_index/index
15-oct-20 11:54:34.086: processing object type schema_export/table/constraint/ref_constraint
15-oct-20 11:54:34.612: processing object type schema_export/table/constraint/constraint
15-oct-20 11:54:34.678: processing object type schema_export/view/view
15-oct-20 11:54:34.783: processing object type schema_export/table/index/index
15-oct-20 11:54:47.347: processing object type schema_export/statistics/marker
15-oct-20 11:58:02.392: . . exported "wl"."t_test_record"                        18.99 gb 66499480 rows
15-oct-20 11:59:22.653: . . exported "wl"."t_test"                            30.47 gb 70834724 rows

观察作业状态

真正管理作业需要attach作业进去操作

查看作业

set line 300 pages 100
col owner_name for a20
col job_name for a30
col state for a20
select owner_name,job_name,state from dba_datapump_jobs;

owner_name	     job_name			    state
-------------------- ------------------------------ --------------------
system		     sjj_export_full		    executing

登录作业

expdp system attach=sjj_export_full

查看作业状态

status

export> status 
job: sjj_export_full
  operation: export                         
  mode: full                           
  state: stop pending                   
  bytes processed: 32,384,054,664
  percent done: 99
  current parallelism: 8
  job error count: 0
  job heartbeat: 6
  dump file: /backup/dumpfile/full20220216_%u.dmp
  dump file: /backup/dumpfile/full20220216_01.dmp
    bytes written: 15,032,143,872
  dump file: /backup/dumpfile/full20220216_02.dmp
    bytes written: 3,542,888,448
  dump file: /backup/dumpfile/full20220216_03.dmp
    bytes written: 3,009,998,848
  dump file: /backup/dumpfile/full20220216_04.dmp
    bytes written: 2,373,156,864
  dump file: /backup/dumpfile/full20220216_05.dmp
    bytes written: 3,188,301,824
  dump file: /backup/dumpfile/full20220216_06.dmp
    bytes written: 948,051,968
  dump file: /backup/dumpfile/full20220216_07.dmp
    bytes written: 37,437,628,416
  dump file: /backup/dumpfile/full20220216_08.dmp
    bytes written: 2,978,820,096
  
worker 1 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  object start time: wednesday, 16 february, 2022 20:35:08
  object status at: wednesday, 16 february, 2022 22:03:31
  process name: dw00
  state: work waiting                   
  
worker 2 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: direct_path
  object start time: wednesday, 16 february, 2022 20:33:35
  object status at: wednesday, 16 february, 2022 20:40:42
  process name: dw01
  state: work waiting                   
  
worker 3 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: direct_path
  object start time: wednesday, 16 february, 2022 20:33:35
  object status at: wednesday, 16 february, 2022 21:16:26
  process name: dw02
  state: work waiting                   
  
worker 4 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: direct_path
  object start time: wednesday, 16 february, 2022 20:33:38
  object status at: wednesday, 16 february, 2022 20:40:37
  process name: dw03
  state: work waiting                   
  
worker 5 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  object start time: wednesday, 16 february, 2022 20:36:11
  object status at: wednesday, 16 february, 2022 20:38:13
  process name: dw04
  state: executing                      
  object schema: wl
  object name: t_test
  object type: database_export/schema/table/table_data
  completed objects: 4
  total objects: 939
  completed rows: 430,816
  worker parallelism: 1
  
worker 6 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: external_table
  object start time: wednesday, 16 february, 2022 20:33:35
  object status at: wednesday, 16 february, 2022 20:41:10
  process name: dw05
  state: work waiting                   
  
worker 7 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: direct_path
  object start time: wednesday, 16 february, 2022 20:33:35
  object status at: wednesday, 16 february, 2022 20:40:31
  process name: dw06
  state: work waiting                   
  
worker 8 status:
  instance id: 1
  instance name: wldb1
  host name: wldb1
  access method: direct_path
  object start time: wednesday, 16 february, 2022 20:34:00
  object status at: wednesday, 16 february, 2022 20:40:31
  process name: dw07
  state: work waiting                   
export>

note：若导出日志长时间没反应，可以时不时查看作业status，观察相关对象是否在变化，判断作业是否正常。

其他操作：
stop_job，停止作业，可以继续启动，dba_datapump_jobs信息依然存在
start_job，继续启动停止的作业
kill_job，强制终止作业，dba_datapump_jobs信息会清除

三、数据导入

导入跟导出原理差不多

数据的导入也是数据库的存储过程作业，所以需要用到目录对象，定义dump文件、日志文件等的路径信息；
导入任务发起，作业会数据库自动创建，作业会自动创建master表记录作业信息，作业状态也会记录在dba_datapump_jobs视图
数据导入源端为dump文件，目标端为数据库，也就是灵活将dump文件的相关对象写入到目标数据库，理解链接关系，跟源库是无关系的
当然需要用到imp_full_database角色权限，目录对象的执行读写权限

1、创建用户和表空间

create tablespace test datafile size 31g autoextend on;
create user test identified by "test" default tablespace test quota unlimited on test;

note：关键是定义好表空间，用户可以不用创建
若不同表空间需要利用remap_tablespace重新映射表空间
若需要导入不同的用户，可以利用remap_schema重新映射用户
当然表结构和数据也可以重新映射

2、创建目录对象

准备目录
mkdir /backup/dumpfile
chown oracle.oinstall /backup/dumpfile
创建
create directory mydir as '/backup/dumpfile';

3、导入数据