Oracle 数据泵(expdp/impdp) 操作手册

 

1 注意事项

  • 检查数据库版本（用于决定导出时生成为哪个版本的dmp头文件） select version from v$instance; 也可以用sqlplus -v 查看。
  • 检查字符集是否一致（字符集不一致，不能导入） select userenv('language') from dual;
  • 检查数据量及磁盘空间（决定采取什么样的方式导出及导入）

2 前期准备

 

2.1 创建目录

create directory DUMP_DIR as '&PATH';
grant read,write on directory dump_dir to &oper_user;

2.2 检查字符集

确保两端字符集是一致的。不然数据导入后会出现乱码。当然这在安装数据库的时候就应该考虑到。

select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

经检查两端一致。

2.3 ASM磁盘组空间

  • 查看原库数据文件大小

      SQL> elect sum(bytes)/(1024*1024*1024) from dba_data_files;

SUM(BYTES)/(1024*1024*1024)
---------------------------
                 2529.21808

    当然这里面包含了system,sysaux,undo,temp,users 等。所以只要满足这个空间大小，就可以满足业务数据所需要的空间。

  • 扩展ASM磁盘组 grid 用户通过asmca 添加 asm 磁盘。

    asmca -adddisk -diskGroupName DATA -diskList /dev/mapper/asm-data10,/dev/mapper/asm-data15,/dev/mapper/asm-data20,/dev/mapper/asm-data25,/dev/mapper/asm-data30 -sysAsmPassword Sys123ora -silent

  • 检查磁盘组状态

    col name for a12
col path for a25
set lines 32767 pages 500
select name,path,group_number,disk_number,header_status,mount_status,mode_status,state,total_mb,free_mb from v$asm_disk order by 1 nulls first;
select group_number,name,state,type,total_mb,free_mb,offline_disks from v$asm_diskgroup;

    执行结果如下:

    ....... 内容过多，省略 ........
ARCH_0000    /dev/mapper/asm-data3                4           0 MEMBER       CACHED  ONLINE  NORMAL       512078     511972
DATA_0000    /dev/mapper/asm-data2                3           0 MEMBER       CACHED  ONLINE  NORMAL       512078     512046
DATA_0001    /dev/mapper/asm-data10               3           1 MEMBER       CACHED  ONLINE  NORMAL       512078     512053
DATA_0002    /dev/mapper/asm-data15               3           2 MEMBER       CACHED  ONLINE  NORMAL       512078     512053
DATA_0003    /dev/mapper/asm-data20               3           3 MEMBER       CACHED  ONLINE  NORMAL       512078     512054
DATA_0004    /dev/mapper/asm-data25               3           4 MEMBER       CACHED  ONLINE  NORMAL       614478     614449
DATA_0005    /dev/mapper/asm-data30               3           5 MEMBER       CACHED  ONLINE  NORMAL       614478     614448
MGMT_0000    /dev/mapper/asm-data1                1           0 MEMBER       CACHED  ONLINE  NORMAL       512076     478248
OCR_0000     /dev/mapper/asm-ocr2                 2           0 MEMBER       CACHED  ONLINE  NORMAL        10240       9948
OCR_0001     /dev/mapper/asm-ocr1                 2           1 MEMBER       CACHED  ONLINE  NORMAL        10240       9952
OCR_0002     /dev/mapper/asm-ocr3                 2           2 MEMBER       CACHED  ONLINE  NORMAL        10240       9952
SYSTEM_0000  /dev/mapper/asm-data4                5           0 MEMBER       CACHED  ONLINE  NORMAL       512076     508780

48 rows selected.

SQL>
GROUP_NUMBER NAME         STATE       TYPE     TOTAL_MB    FREE_MB OFFLINE_DISKS
------------ ------------ ----------- ------ ---------- ---------- -------------
           1 MGMT         MOUNTED     EXTERN     512076     478248             0
           2 OCR          MOUNTED     NORMAL      30720      29852             0
           4 ARCH         MOUNTED     EXTERN     512078     511972             0
           5 SYSTEM       MOUNTED     EXTERN     512076     508780             0
           3 DATA         MOUNTED     EXTERN    3277268    3277103             0

    磁盘大小，磁盘、磁盘组的状态也都正常。

2.4 在目标库中创建表空间

 

2.4.1 11G及之前

原库查询表空间信息

  select tablespace_name, file_name,(bytes/(1024*1024*1024)) as file_size,autoextensible from dba_data_files order by tablespace_name;
  -- 或者通过以下语句直接生成创建表空间语句
select decode(row_number()
               over(partition by tablespace_name order by tablespace_name),               1,
               'create tablespace ' || rpad(tablespace_name, first_value(length(tablespace_name)) over ( order by length(tablespace_name) desc )+5, ' '),
               'alter  tablespace ' || rpad(tablespace_name, first_value(length(tablespace_name)) over ( order by length(tablespace_name) desc), ' ') ||
               ' add ') ||
        rpad(' datafile ''&file_path' || lower(tablespace_name) || '_' ||row_number() over(partition by tablespace_name order by tablespace_name) || '.dbf''',
             length(' datafile ''&file_path'||'_'||'.dbf''')+ first_value(length(tablespace_name)) over ( order by length(tablespace_name)desc )+5,' ') ||
       --' size '||decode(ceil(bytes/(1024*1024*1024)),32,31,ceil(bytes/(1024*1024*1024)))||'G autoextend on;'*/
        ' size 1G autoextend on;'
  from dba_data_files
 where tablespace_name not in
       ('SYSTEM', 'SYSAUX', 'TEMP', 'UNDOTBS2', 'USERS')
 order by tablespace_name;

上面语句会直接生成目标库的创建表空间和添加数据文件的语句。直接在目标库执行。

添加完表空间后， 对比两侧表空间总量是否满足数据迁移条件：

set lines 32767 pages 5000
select tablespace_name,sum(bytes)/(1024*1024*1024) as size_gb
from dba_data_files
where tablespace_name not in ('SYSTEM','SYSAUX','USERS') AND
tablespace_name not like 'UNDO%' AND
tablespace_name NOT LIKE 'TEMP%'
group by tablespace_name order by 1;

2.4.2 12C

12C 在数据泵导出时，是可以将表空间的元数据一起导出来的，因此不再需要我们手动去目标库创建。 但是12C 导出表信息时，会完全按照原库的表空间大小导出，如果我们不需要全部数据，那么表空间 就没有必要创建那么大。最好还是自己手动创建一下。

2.5 目标库创建用户

select dbms_metadata.get_ddl('USER',username) from dba_users where

3 常用参数及示例

  • ATTACH

    作用
    当我们使用ctrl+C 退出交互式命令时，可心使用attach参数重新进入到交互模式
语法
    ATTACH=[schema_name.]job_name
    Schema_name用户名,job_name任务名
示例
    Expdp scott/tiger ATTACH=scott.export_job

  • CONTENT

    作用
    限制了导出的内容，包括三个级别：全部／数据／元数据（结构）
语法
   CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
   ALL           -- 导出所有数据，包括元数据及数据
   DATA_ONLY     -- 只导出数据
   METADATA_ONLY -- 只包含元数据
示例
   Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY

  • DIRECTORY

    作用
    此路径可以理解为实际绝对路径在oracle数据库里的别名，是导出文件的存储位置
    路径的创建： create directory &DIRECTORY_NAME AS '&PATH';
    查看已存在路径： select  * from dba_directories;
语法
    directory=[directory_name]
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=lhb.dump

  • DUMPFILE

    作用
    此参数用户命名导出文件，默认是 expdat.dmp. 文件的存储位置如果在文件名前没有指定directory,则会默认存储到directory参数指定的路径下。
语法
    DUMPFILE=[dump_dir:]file_name
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=dump_dir1:a.dmp

  • ESTIMATE

    在使用Expdp进行导出时，Expdp需要计算导出数据大小容量，Oracle可以通过两种方式进行容量估算，一种是通过数据块(blocks)数量、一种是通过统计信息中记录的内容(statistics)估算.

语法结构：
    EXTIMATE={BLOCKS | STATISTICS}
示例：
    Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump_dir DUMPFILE=halberd.dump
    Expdp scott/tiger TABLES=emp ESTIMATE=BLOCKS DIRECTORY=dump_dir DUMPFILE=halberd.dump

  • EXTIMATE_ONLY

    作用
    此参数用于统计导出的数据量大小及统计过程耗时长短。
语法
    EXTIMATE_ONLY={Y | N}
示例
    Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y directory=dump_dir schemas=halberd

  • EXCLUDE

    作用
    此参数用于排除不需要导出的内容，如我们进行全库导出，但是不需要导出用户scott，此时需要在exlude后先指定排除类型为schema,再指定具体的schema。具体使用方法见include参数. EXCLUDE与include的使用方法是一样的
语法
    EXCLUDE=object_type[:name_clause] [,object_type[:name_clause] ]
    name_clause
        "='object_name'"
        "in ('object_name'[,'object_name',....])"
        "in (select_clause) "
    Object_type对象类型，如：table,view,procedure,schema等
    name_clause指定名称的语句,如果不具体指定是哪个对象，则此类所有对象都不导出, select 语句中表名不要加用户名。用户名，通过schemas 指定。

示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=VIEW
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=TABLE:\" IN\(\'TEMP\',\'GRADE\'\)\"
    EXCLUDE=TABLE:"='APPLICATION_AUDIT'"

  • FILESIZE

    作用
    用于指定单个导出的数据文件的最大值，与％U一起使用。比如，我们需要导出100G的数据，文件全部存储到一个文件内，在文件传输时，会耗费大量的时间，此时我们就可以使用这个参数，限制每个文件的大小，在传输导出文件时，就可以多个文件同时传送，大大的节省了文件传输时间。提高了工作的效率。
语法
  FILESIZE=integer[B | K | M | G]
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd%U.dup FILESIZE=20g

  • FLASHBACK_SCN／FLASHBACK_TIME

    作用
    基于undo 及scn号(时间点)进行的数据导出。使用此参数设置会进行flashback query的功能，查询到对应指定的SCN时的数据，然后进行导出。只要UNDO不被覆盖，无论数据库是否重启，都可以进行导出. flashback_time参数与flashback_scn的原理是一样的。在导出的数据里保持数据的一致性是很有必要的。这个。。我想，没谁傻忽忽的把这两个参数一起使用吧？所以我就不提醒你两个参数不可以同时使用了。
语法
   FLASHBACK_SCN=scn_value
   FLASHBACK_TIME 有多种设定值的格式：
   flashback_time=to_timestamp (localtimestamp)
   flashback_time=to_timestamp_tz (systimestamp)
   flashback_time="TO_TIMESTAMP (""25-08-2003 14:35:00""， ""DD-MM-YYYY HH24:MI:SS"")"  使用此格式可能会遇到ORA-39150错误。
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_SCN= 12345567789
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_TIME= to_timestamp (localtimestamp)

  • FULL

    作用
   指定导出内容为全库导出。这里需要特别注意的是,expdp 不能导出sys用户对象。即使是全库导出也不包含sys用户。
语法
   FULL={Y | N}
示例
   expdp \'\/ as sysdba\' directory=dump_dir full=y

  • HELP

    作用
    当我们对参数的意义不了解时，或者忘记参数怎么写时，就可以用这个参数，来寻求帮助，实际上和操作系统里的man命令是一样的。
示例
    impdp -help
    expdp help=y

  • INCLUDE

    作用
    限制范围，指定自己想要的内容，比如要导出某个用户的某张表。
语法
    INCLUDE = object_type[:name_clause],object_type[:name_clause]
示例
    impdp dbmon/dbmon_123 directory=dump_dir network_link=zjzwb2 SCHEMAS=AICBS remap_schema=aicbs:aicbsb include=table:\"IN\(SELECT TABLE_NAME FROM dbmon.TABLES_TOBE_MASKED\)\"  LOGFILE=zjzwb.log transform=segment_attributes:n
    PARFILE中设置:
        INCLUDE=table:"in(select table_name from dba_tables where owner='AA')"
        INCLUDE=TABLE:"IN('TEST1','TEST2')"
        include=table:"like 'SEC#_%'escape'#'"
        在include、exclude参数中，在escape语句中，不能用\作为转义符！可以选用选用其他特
        殊字符作为转义符。如果确实要用\,也要可以用ascii码代替： include=table:"like
        'SEC\_%'escape chr(92)" .
    SHELL环境设置:
        INCLUDE=TABLE:\"IN\(SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=\'AA\'\)\"
        INCLUDE=TABLE:\"IN\(\'TEST1\',\'TEST2\'\)\"
说明
    当导入命令在目标端发起时，select 子句所涉及的表要在源端，并且dblink 所使用的用户有访问的权限。

  • JOB_NAME

    作用
    指定任务名，如果不指定的话，系统会默认自动命名：SYS_EXPORT_mode_nn
语法
    JOB_NAME=&JOB_NAME
其他
    查看有哪些expdp/impdp job，可以通过dba_datapump_jobs查看，其实你通过v$session.action也可以查看到
    大多与attach参数一起使用，重新进行expdp交互命令时使用。

  • LOGFILE

    作用： 指定导出日志名称。默认是：expdp.log
语法
    LOGFILE=[DIRECTORY:]file_name   , 如果参数值里没有指定路径，会默认使用directory参数值所指向的路径。
    directory ： 存储路径,
    file_name :日志文件名
示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log

  • NETWORK_LINK

    作用
    此参数只有在导入（impdp）时使用，可通过本地数据库里的db_link连接到其他数据库A，将数据库A的数据直接导入到本地数据库。中间可节省导出数据文件，传送数据文件的过程。很方便。
语法
    network_link=[db_link]
示例
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp NETWORK_LINK=to_tjj SCHEMAS=halberd logfile=halberd.log

  • NOLOGFILE

    作用
    不写导入导出日志，这个笔者是灰常灰常滴不建议设置为“Y”滴。
语法
    nologfile=[y|n]

  • PARALLEL

    作用
    指定导出／导入时使用多少个并发，默认是1.
语法
    parallel=[digit]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd dumpfile=halberd%U.dmp parallel=8 logfile=halberd.log
    NOTE1

    这里需要特别注意的是,在RAC环境中使用parallel大于1时，需要将directory 设置为共享路径，或者加上参数cluster=n.否则会报错类似如下:

    ORA-31693: Table data object "owner"."table_name" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "file_name" for write
ORA-19505: failed to identify file "file_name"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
    NOTE2
    并行数随意指定，或者达不到预期效果，或者对数据库产生影响。 为parallel 设置一个合适的值，需要从另外两点入手:
    • db_writer_processes 该参数限制了最张可开启的最大写进程数
    • dumpfile参数应使用xxx%U.dmp 这种用法,使每个进程都有文件可写，避免多个进程同时写一个文件的情况(ORA-39095)

  • PARFILE

    作用
    参数文件，这个参数文件里，存储着一些参数的设置。比如上面说过的，parallel,network_link,等。导出时，可以使用此参数，expdp/impdp会自动读取文件中的参数设置，进行操作。
语法
    PARFILE=[directory_path] file_name
示例
    expdp \'\/ as sysdba\' parfile=halberd.par

   cat halberd.par
   directory=dump_dir
   logfile=test.log
   schemas=test
   query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
   transform=segment_attributes:n
   network_link=to_aibcrm
   table_exists_action=append
   impdp \'\/ as sysdba\' parfile=test.par

  • QUERY

    作用
    此参数指定在导入导出时的限制条件，和SQL语句中的 "where" 语句是一样儿一样儿滴
语法
    QUERY=([schema.] [table_name:] query_clause, [schema.] [table_name:] query_clause,……)
    CONTENT=METADATA_ONLY, EXTIMATE_ONLY＝Y,TRANSPORT_TABLESPACES.
示例
   Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query="WHERE deptno<>20"

  • SCHEMAS

    作用
    指定导出／导入哪个用户
语法
    schemas=schema_name[,schemaname,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd
  • REMAP_SCHEMA
 只在导入时使用
作用
    当把用户A的对象导入到用户（其实应该叫schema，将就看吧）B时，使用此参数，可实现要求
格式
    remap_schema=schema1: schema2
示例
    impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log remap_schema=scott:halberd
  • TABLES
作用
    指定导出哪些表。
格式
    TABLES=[schema.]table_name[:partition_name][,[schema.]table_name[:partition_name]]
说明
    Schema 表的所有者；table_name表名；partition_name分区名.可以同时导出不同用户的不同的表
示例
    expdp \'\/ as sysdba\' directory=dump_dir tables=emp.emp_no,emp.dept
  • TABLESPACES
作用
    指定导出／导入哪个表空间。
语法
    tablespaces=tablespace_name[,tablespace_name,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir tablespace=user
  • REMAP_TABLESPACE
作用
    只有在导入时使用，用于进行数据的表空间迁移。 把前一个表空间中的对象导入到冒号后面的表空间
用法
    remap_tablespace=a:b
说明
   a: 数据所在的原表空间; b： 目标表空间
示例
   impdp \'\/ as sysdba\' directory=dump_dir tables=emp.dept remap_tablespace=user:user1
  • TRANSPORT_FULL_CHECK
     检查需要进行传输的表空间与其他不需要传输的表空间之间的信赖关系，默认为N。当设置为“Y”时，会对表空间之间的信赖关系进行检查，如A（索引表空间）信赖于B（表数据表空间），那么传输A而不传输B，则会出错，相反则不会报错。
  • TRANSPORT_TABLESPACES
作用
    列出需要进行数据传输的表空间
格式
     TRANSPORT_TABLESPACES＝tablespace1[,tablespace2,.............]
  • TRANSFORM
作用
    此参数只在导入时使用，是一个用于设定存储相关的参数,有时候也是相当方便的。假如数据对应的表空间都存在的话，就根本用不到这个参数，但是，假如数据存储的表空间不存在，使用此参数导入到用户默认表空间就可以了。更灵活的，可以使用remap_tablespace参数来指定。
格式
    transform=transform_name:value[bject_type]
    transform_name = [OID | PCTSPACE | SEGMENT_ATTRIBUTES | STORAGE]:[Y|N]
    segment attributes:段属性包括物理属性、存储属性、表空间和日志,Y 值按照导出时的存储属性导入，N时按照用户、表的默认属性导入
    storage:默认为Y，只取对象的存储属性作为导入作业的一部分
    oid:  owner_id,如果指定oid=Y(默认)，则在导入过程中将分配一个新的oid给对象表，这个参数我们基本不用管。
    pctspace:通过提供一个正数作为该转换的值，可以增加对象的分配尺寸，并且数据文件尺寸等于pctspace的值(按百分比)
示例
    transform=segment_attributes:n --表示将用户所有对象创建到用户默认表空间，而不再考虑原来的存储属性。
  • VERSION
       此参数主要在跨版本之间进行导数据时使用，更具体一点，是在从高版本数据库导入到低版本数据库时使用,从低版本导入到高版本，这个参数是不可用的。默认值是:compatible。此参数基本在导出时使用，导入时基本不可用。
VERSION={COMPATIBLE | LATEST | version_string}
COMPATIBLE       ： 以参数compatible的值为准，可以通过show parameter 查看compatible参数的值
LATEST           ： 以数据库版本为准
version_string   ： 指定版本。如： version=10.2.0.1
  • SAMPLE
       SAMPLE 给出导出表数据的百分比，参数值可以取.000001~100（不包括100）。不过导出过程不会和这里给出的百分比一样精确，是一个近似值。
     格式： SAMPLE=[[schema_name.]table_name:]sample_percent
     示例： SAMPLE="HR"."EMPLOYEES":50
  • table_exists_action
此参数只在导入时使用。
作用：导入时，假如目标库中已存在对应的表，对于这种情况，提供三种不同的处理方式：append,truncate,skip,replace
格式： table_exists_action=［append | replace| skip |truncate］
说明： append :   追加数据到表中
       truncate:  将目标库中的同名表的数据truncate掉。
       skip ：      遇到同名表，则跳过，不进行处理，注意：使用此参数值时，与该表相关的所有操作都会skip掉。
       replace:    导入过程中，遇到同名表，则替换到目标库的那张表（先drop,再创建）。
示例：  table_exists_action=replace
  • SQLFILE
只在导入时使用！
作用： 使用此参数时，主要是将DMP文件中的metadata语句取出到一个单独的SQLfile中，而数据并不导入到数据库中
格式： sqlfile=&file_name.sql
示例： impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log sqlfile=halberd.sql
legacy mode
在11g中，才有这种模式。这种模式里兼容了以前版本中的部分参数，如：consistent,reuse_dumpfiles等（其实我现在也就知道这两个参数，哈哈，以后再遇到再补充）
  • consistent
这个是保持数据一致性的一个参数。在11g中使用时，如果设置 consistent=true,则会默认转换成 flashback_time参数，时间设置为命令开始执行的那个时间点。
格式： consistent=[true|false]
  • reuse_dumpfiles
作用：重用导出的dmp文件 。假如第一次我们导失败了，虽然导出失败，但是dmp文件 还 是会生成的。在修改导出命令，第二次执行时，就可以 加上这个参数。
格式： reuse_dumpfile=[true|false]
  • partition_options
1 NONE 不对分区做特殊处理。在系统上的分区表一样创建。
2 DEPARTITION 每个分区表和子分区表作为一个独立的表创建，名字使用表和分区（子分区）名字的组合。
3 MERGE 将所有分区合并到一个表
注意：如果导出时使用了TRANSPORTABLE参数，这里就不能使用NONE和MERGE

4 常用语句示例

  • expdp导出
1)导出表
expdp  tables=dbmon.lihaibo_exp dumpfile=sms.dmp DIRECTORY=dump_dir;
2)并发导出parallel，指定job名
我们需要特别注意一点，parallel 一定要与 dumpfile=...%U.dmp结合 使用，或者有多个表需要同时导出。单表，或者其他诸如network_link方式，指定parallel,也无法开启并发进程
expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3%U.dmp parallel=4 job_name=scott3
3)全表
expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;
4)导出表，并指定表中的内容
expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query="WHERE deptno=20";
5)导出表空间
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;
6)导出全库
expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;
  • impdp导入
1) 全用户导入
impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;
2) 用户对象迁移
impdp system/manager DIRECTORY=dump_dir DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system; （SCOTT为原用户，system为目标用户）
3) 导入指定表空间
impdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=example;
4) 全库导入
impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;
5) 表已存在的处理
impdp system/manager DIRECTORY=dump_dir DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append;
6) 表空间迁移
impdp system/manager directory=dump_dir dumpfile=remap_tablespace.dmp logfile=remap_tablespace.log remap_tablespace=A:B (A为原表空间名，B为指定的目标表空间名)

5 交互式命令

  1. 连接到对应的job impdp \'\/ as sysdba\' attach=&job_name 不知道job_name 去哪儿找？看上面的参数:job_name
  2. 查看运行状态： status
  3. 停止导入导出： kill_job(直接kill 掉进程，不自动退出交互模式)
  4. 停止导入导出：stop_job（逐一停止job进程的运行，并退出交互模式）
  5. 修改并发值： parallel
  6. 退出交互模式： exit / exit_client(退出到日志模式，对job无影响)

6 技巧

 

6.1 不生成文件直接导入目标数据库

在一些情况下，我们并没有足够的存储空间允许我们存储导出的dmp文件。这个时候，我们就无计可施了么？ 不是的。我们可以不生成dmp文件，直接将数据抽取到目标数据。在迁移大量数据而没有充足存储空间时，这是一个救命稻草。 最关键的点就是在目标端执行impdp的时候，使用network_link，直接从源库抽取数据。 有两点，我一直心存疑问和不确定,今天终于想明白了:

  • 配置表应该配置在源端
  • network_link 指向源端

示例如下：

cat test.par
directory=dump_dir
logfile=test.log
schemas=test
query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append
impdp \'\/ as sysdba\' parfile=test.par

6.2 通过shell脚本自动导入

此处只关注，impdp 命令在shell脚本中执行，需要转义的地方。

cat import_sr.sh
#!/bin/sh
cd /u01/app
for da in 2012-10 2013-09 2013-08 2013-07 2013-06 2013-05 2013-04 2013-03 2013-02 2013-01 2012-12 2012-11 2014-08 2014-07 2014-06 2014-05 2014-04 2014-03 2014-02 2014-01 2013-12 2013-11 2013-10 2015-07 2015-06 2015-05 2015-04 2015-03 2015-02 2015-01 2014-12 2014-11 2014-10 2014-09 2016-06 2016-05 2016-04 2016-03 2016-02 2016-01 2015-12 2015-11 2015-10 2015-09 2015-08 2017-05 2017-04 2017-03 2017-02 2017-01 2016-12 2016-11 2016-10 2016-09 2016-08 2016-07;
do
impdp \'\/ as sysdba\' parfile=import_sr.par logfile=sr${da}.log query=\" where create_date\> last_day\(add_months\(to_date\(\'$da\',\'yyyy-mm\'\),-1\)\) and create_date \<\=last_day\(to_date\(\'$da\',\'yyyy-mm\'\)\)\"
done

-- 参数文件内容
directory=dump_dir
tables=SR.SR_VOUCHER_FILE_tomig
remap_table=sr.SR_VOUCHER_FILE_tomig:sr_his.sr_voucher_file
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append

6.3 如何导出数百张表

通过命令行参数 tables=… 来指定表名是有限制的，印象里最多是四五十张表。如果单次需要处理的表名超过了这个数量，怎么办？ 就要用到现在我们说的这种方法，通过schemas 与include 来配合使用。

下面先来看下示例：

schemas=test1
include=table:"in(select table_name from tables_tobe_exported where owner='AA')"

tables_tobe_exported :: 在表里存储需要导出的表明细,此表应创建在数据源端。 该表有两个字段，owner, table_name分别对应表的schema与表名。

7 性能相关

 

7.1 wait for unread message on broadcast channel

很多次，当导入，或者导出的时候，被这个等待事件无折腾的茶不思饭不想。而每次遇到这个等待事件 逻辑备份或者迁移所消耗的时间都异常的长。比如本来1小时可以完成的任务，在这个事件的坚持下， 可以延长至五六个小时，甚至更长。

我决心要解决掉它。

7.1.1 查看数据泵发起的会话状态

col sid for 99999
col owner for a8
col job_name for a20
col session_type for a12
col module for a17
col state for a8
col event for a44
col seconds_in_wait for 99999999999
col sql_text for a60
select a.inst_id,a.owner_name as owner,a.job_name,a.session_type,s.sid, s.module, s.state, s.event, s.seconds_in_wait ,substr(sql.sql_text,1,60) as sql_text
from dba_datapump_sessions a left join gv$session s on a.inst_id = s.inst_id and a.saddr = s.saddr
join gv$sql sql on sql.sql_id = s.sql_id and s.inst_id = sql.inst_id
where s.module like 'Data Pump%'
order by s.module, s.sid;

示例：

   INST_ID OWNER    JOB_NAME             SESSION_TYPE    SID MODULE            STATE    EVENT                                        SECONDS_IN_WAIT SQL_TEXT
---------- -------- -------------------- ------------ ------ ----------------- -------- -------------------------------------------- --------------- ------------------------------------------------------------
         1 SYS      SYS_IMPORT_SCHEMA_03 MASTER           79 Data Pump Master  WAITING  wait for unread message on broadcast channel               0 BEGIN :1 := sys.kupc$que_int.receive(:2); END;
         1 SYS      SYS_IMPORT_SCHEMA_03 WORKER           73 Data Pump Worker  WAITING  direct path write                                          0 CREATE INDEX "BOSS"."PK_PDSERVICEINST" ON "BOSS"."T_PDSERVIC

这里，我们看到Master的等待事件，是 waiting for unread message on broadcast channel. 而实际的操作进程正在创建索引，等待事件是 "direct path read"。也就是说数据泵真正的等待是 在创建索引时，将数据读取至PGA, 这个没有什么办法去解决。只能期待Oracle 研发可以想到优化的方法。

但是很多时候，在这里查不出真正的等待事件，或者说我们查看的时候，影响最大的等待事件并没有显现出来。 通常的做法是采用10046进行会话追踪。通过tkprof工具分析trace文件，查看最关键的根源给了我们什么 提示。

7.1.2 追踪数据泵会话

 
  1. 10046

    一般采用10046追踪即可。 

    alter system set events ‘10046 trace name context forever, level 12’;
-- 开始执行导入或者导出工作
ALTER SYSTEM SET events ‘10046 trace name context off’;

    执行完后，查找最新的 <DB_NAME>_dmnn_<spid>.trc <db_name>_dwnn_<spid>.trc .

  2. **
  3. 查找trace文件

    先来认识下trace文件的格式：

    • Data Pump Master Control Process (MCP).

    Format : <SID>_dm<number>_<process_id>.trc Example: ORCL_dm00_2896.trc or: ORCL_dm01_3422.trc (for second active Master Control Process) Location: BACKGROUND_DUMP_DEST or <ADR_HOME>/trace

    • Data Pump Worker Process trace file.

    Format : <SID>_dw<number>_<process_id>.trc Example: ORCL_dw01_2936.trc or: ORCL_dw01_2844.trc and ORCL_dw02_2986.trc (if PARALLEL=2) Location: BACKGROUND_DUMP_DEST or <ADR_HOME>/trace

    • Data Pump Shadow Process trace file.

    Format : <SID>_ora_<process_id>.trc Example: ORCL_ora_3020.trc Location: USER_DUMP_DEST or <ADR_HOME>/trace

    Oracle 10G 与11G 中trace文件的默认存放路径发生了改变: 

    --Oracle 10G

SHOW PARAMETER dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
background_dump_dest                 string      <background_dump_dest_location>
user_dump_dest                       string      <user_dump_dest_location>
...


--Oracle 11G

SHOW PARAMETER diag

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
diagnostic_dest                      string      <diagnostic_dest_location>
    NOTE
    Oracle中 如果 BACKGROUND_DUMP_DEST and/or USER_DUMP_DEST 没有
  4. 分析trace文件
    tkprof <trace_file> <out_file> waits=y sort=exeela

    示例如下:

    Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  wait for unread message on broadcast channel
                                                698        1.00        684.41
  Disk file operations I/O                        2        0.00          0.00
  Streams AQ: enqueue blocked on low memory
                                                 21       60.00       1201.93
  db file sequential read                        28        0.00          0.03
  class slave wait                                1        0.00          0.00
  library cache: mutex X                          1        0.00          0.00
  direct path write                               2        0.00          0.00
  reliable message                               20        0.00          0.00
  db file scattered read                          1        0.00          0.00
  enq: RO - fast object reuse                     9        0.01          0.03
  enq: CR - block range reuse ckpt                9        0.00          0.05

    在这个示例中,我们通过"Total Waited" 列可以看出，最耗时的是"Streams AQ: enqueue blocked on low memory".

    在上面的示例中，我们发现了等待事件 " Streams AQ: enqueue blocked on low memory"这个等待事件是说，Steam_pool_size过小了。 也可能是个BUG。

  5. 针对stream_pool_size
    1. 原因

      在stream_pool_size 上引发数据泵缓慢的原因，有可能真的是小了。

      可能遇到了BUG:17365043,会导致在"Streams AQ: enqueue blocked on low memory" 上等待1分钟。该问题在12.2.0.1中被修复。 如果按照此文档不能修复问题，可以参考以下内容：

      Note.1990633.1 –Expdp Is Very Slow After Upgrade From 11.2.0.3 To 11.2.0.4 With Wait On AQ: enqueue blocked on low memory 该文档中的问题在BUG 18828868 中被修复,而BUG18828868 被 Bug 21286665 所取代。

    2. 解决方法
      1. 临时解决方案 停止数据泵，重启oracle database ，重新发起expdp/impdp命令。但是有可能根本解决不了，或者在中途再次出现。
      2. 调整streams_pool_size 给该参数设置一个固定的值。比如128M /256M 等。

      首先查看 stream_pool 是否被调整过。 

      select component,current_size/1024/1024,last_oper_type,last_oper_time from v$sga_dynamic_components;

COMPONENT                      CURRENT_SIZE/1024/1024 LAST_OPER_TYP LAST_OPER
------------------------------ ---------------------- ------------- ---------
shared pool                                       320 GROW          24-MAY-19
large pool                                         32 SHRINK        22-MAY-19
java pool                                          16 STATIC
streams pool                                       16 SHRINK        24-MAY-19
DEFAULT buffer cache                              496 SHRINK        24-MAY-19
KEEP buffer cache                                   0 STATIC
RECYCLE buffer cache                                0 STATIC
DEFAULT 2K buffer cache                             0 STATIC
DEFAULT 4K buffer cache                             0 STATIC
DEFAULT 8K buffer cache                             0 STATIC
DEFAULT 16K buffer cache                            0 STATIC
DEFAULT 32K buffer cache                            0 STATIC
Shared IO Pool                                      0 STATIC
ASM Buffer Cache                                    0 STATIC

      从上面的查询结果 来看，shared_pool 空间不足，导致从large_pool 和streams_pool 回收了一部分内存。

      我们手动把内存调整大一些。

      ALTER SYSTEM SET streams_pool_size=256m SCOPE=both sid='*';

      1. 关闭streams pool 自动调整 如果前两个方法不能解决问题，可以尝试关闭streams_pool_size 自动调整功能。

        alter system set “_disable_streams_pool_auto_tuning”=TRUE;

        关闭隐藏参数需要重启数据。

      2. 安装补丁 Patch 24560906.
  6. 数据泵trace参数

    在使用数据泵的时候，可以添加TRACE参数. 默认:0480300 0490300(0480300+0010000) 可以输出 expdp/impdp详细内容 0690300(0490300+0200000) 可以输出 队列服务信息

    示例: 

    expdp \'\/ as sysdba\' full=y dumpfile=full%U.dmp trace=0690300

    如果发现

8 数据迁移后的对象对比

我们用数据泵迁移完数据后，总是要核对一下数据是否全部迁移过去。 一般来说，我们是针对数据对象类型进行核对，如果两端的个数是一致的，那么基本认定 迁移是没有问题的。

但是经常会遇到两种特殊情况，一种是LOB对象个数对不上(一般目标库比源库少)，一种是索引个数对不上。很多 时间目标端比源库还多。

针对索引，由于一些字段特别是LOB字段，Oracle会自建索引，索引名自动命名（这种类型的索引由Oracle 自动维护，不做对比）。 由于索引是创建在用户名+表名+字段名列表 上的。因此可以认为用户名+表名+字段名列表代表了一个索引，而且是人为创建的索引。 只要这部分索引对得上，那么索引的迁移就没有问题。

那么为了能精确核对。整理了以下脚本，用以进行数据类型核对.

NOTE
在源库执行此SQL前，先purge dba_recyclebin;
purge dba_recyclebin;
select object_type,count(*) from dba_objects
where owner in (owner_list)
  AND OBJECT_TYPE NOT LIke 'INDEX%'
  group by object_type
union
select 'INDEX',COUNT(*) FROM(
select table_owner,table_name,
listagg(column_name,',') within group( order by column_position)
from dba_ind_columns@tooldqa01
where table_owner in (owner_list)
group by table_owner,table_name);

Author: Halberd E-mail: halberd.lee@gmail.com Tel:18258160531

Created: 2020-07-22 Wed 18:55

Validate

posted @ 2020-07-13 11:49  halberd.lee  阅读(4167)  评论(0)  编辑  收藏  举报