impdp TRANSFORM参数

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:impdp TRANSFORM参数

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在impdp的参数中有一个transform参数,用来实现在创建对象的时候对一些存储参数进行修改,官方关于这个参数说明:

TRANSFORM
要应用于适用对象的元数据转换。
有效的关键字为: OID, PCTSPACE, SEGMENT_ATTRIBUTES 和 STORAGE。

对主要的SEGMENT_ATTRIBUTES和STORAGE参数进行测试

SQL> create user xff identified by oracle default tablespace users;

用户已创建。

SQL> grant dba to xff;

授权成功。

SQL> create table xff.t_1 as select * from dba_objects;

表已创建。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

C:\Users\XFF>expdp xff/oracle tables=t_1 dumpfile=t_1.dmp

Export: Release 11.2.0.4.0 - Production on 星期四 12月 16 20:56:50 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "XFF"."SYS_EXPORT_TABLE_01":  xff/******** tables=t_1 dumpfile=t_1.dmp
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 11 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "XFF"."T_1"                                 8.709 MB   89959 行
已成功加载/卸载了主表 "XFF"."SYS_EXPORT_TABLE_01"
******************************************************************************
XFF.SYS_EXPORT_TABLE_01 的转储文件集为:
  C:\APP\XFF\ADMIN\ORCL\DPDUMP\T_1.DMP
作业 "XFF"."SYS_EXPORT_TABLE_01" 已于 星期四 12月 16 20:56:53 2021 elapsed 0 00:00:02 成功完成

不使用TRANSFORM参数导入效果

C:\Users\XFF>impdp xff/oracle sqlfile=t_2.sql full=y dumpfile=t_1.dmp

Import: Release 11.2.0.4.0 - Production on 星期四 12月 16 21:00:12 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "XFF"."SYS_SQL_FILE_FULL_01"
启动 "XFF"."SYS_SQL_FILE_FULL_01":  xff/******** sqlfile=t_2.sql full=y dumpfile=t_1.dmp
处理对象类型 TABLE_EXPORT/TABLE/TABLE
作业 "XFF"."SYS_SQL_FILE_FULL_01" 已于 星期四 12月 16 21:00:12 2021 elapsed 0 00:00:00 成功完成

对应的t_2.sql文件内容
20211216211113


transform=storage:n参数导入效果

C:\Users\XFF>impdp xff/oracle sqlfile=t_1.sql full=y dumpfile=t_1.dmp transform=storage:n

Import: Release 11.2.0.4.0 - Production on 星期四 12月 16 20:58:04 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "XFF"."SYS_SQL_FILE_FULL_01"
启动 "XFF"."SYS_SQL_FILE_FULL_01":  xff/******** sqlfile=t_1.sql full=y dumpfile=t_1.dmp transform=storage:n
处理对象类型 TABLE_EXPORT/TABLE/TABLE
作业 "XFF"."SYS_SQL_FILE_FULL_01" 已于 星期四 12月 16 20:58:04 2021 elapsed 0 00:00:00 成功完成

对应的t_1.sql文件内容
20211216211352


transform=segment_attributes:n参数导入效果

C:\Users\XFF>impdp xff/oracle sqlfile=t_3.sql full=y dumpfile=t_1.dmp  transform=segment_attributes:n

Import: Release 11.2.0.4.0 - Production on 星期四 12月 16 21:00:36 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "XFF"."SYS_SQL_FILE_FULL_01"
启动 "XFF"."SYS_SQL_FILE_FULL_01":  xff/******** sqlfile=t_3.sql full=y dumpfile=t_1.dmp transform=segment_attributes:n 
处理对象类型 TABLE_EXPORT/TABLE/TABLE
作业 "XFF"."SYS_SQL_FILE_FULL_01" 已于 星期四 12月 16 21:00:36 2021 elapsed 0 00:00:00 成功完成

对应的t_3.sql文件内容
20211216211523


transform=segment_attributes:n除掉了所有存储相关信息,对象数据直接导入到用户默认表空间中
transform=storage:n导入的时候除掉了STORAGE部分,表空间信息依旧存在(也就是说导入到表原始表空间中)

expdp 并行导出单表数据

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:expdp 并行导出单表数据

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在某些情况下,需要使用并行的方法使用 datapump 对单个对象并行导出,导入加快数据迁移的数据
expdp导出操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
  expdp USERNAME/Password@DB_NAME TABLES=LOB_TEST QUERY=LOB_TEST:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, 
>${chunk}\) = ${i}\" directory=DMP dumpfile=lob_test_${i}.dmp logfile= log_test_${i}.log &
  echo $i
done 

impdp导入操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
 impdp USERNAME/Password@DB_NAME  directory=DMP REMAP_TABLE=LOB_TEST:LOB_TEST  remap_schema=source:target 
>dumpfile= lob_test_${i}.dmp logfile=TABLE_imp_log_test_${i}.log  DATA_OPTIONS=DISABLE_APPEND_HINT  CONTENT=DATA_ONLY &
 echo $i
done

在12c版本开始impdp可能会启用ENABLE_PARALLEL_DML特性,需要注意
参考:Optimising LOB Export and Import Performance via Oracle DataPump

datapump network_link遭遇ORA-12899错误

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:datapump network_link遭遇ORA-12899错误

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在给一个客户使用expdp+network_link导出数据,然后通过impdp导入数据的过程中遇到ORA-12899问题.
20210608215836


对原库和现在库进行分析
20210608215825
20210608215817

原库和目标库表结构一致,原库该表存储数据实际长度确实为1,但是在impdp导入的时候提示需要长度为3.通过分析,确认原库的nls_length_semantics参数设置为char了,直接使用impdp+network_link不落地方式导入该表数据成功
20210608215845

根据上述情况,查询相关文档,确认类似记录为:
ORA-12899 When Using IMPDP Over Network Link (Doc ID 414901.1)
ORA-26059 During Impdp Using Export Dump Taken With NETWORK_LINK Option (Doc ID 2266956.1)
虽然都不是完全匹配该问题,但是基本上可以确认expdp的network_link和nls_length_semantics参数是引起该问题的根本原因,在后续的迁移中,尽量保持nls_length_semantics参数一致.

ORA-19921: maximum number of 64 rows exceeded

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-19921: maximum number of 64 rows exceeded

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

rman 登录报ORA-19921错

[oracle@db-base ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 28 11:58:18 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database: 
ORA-19921: maximum number of 64 rows exceeded

connected to target database: ORCL (DBID=1590736012)

RMAN> 

通过检查rman进程发现大量未退出进程

[oracle@db-base trace]$ ps -ef|grep rman
oracle     998   985  0 May18 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    1054  1039  0 Apr18 ?        00:00:10 rman oracle/11.2.0/db_1/bin/rman target /
oracle    1738  1726  0 Apr27 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4294  4281  0 May11 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4655  4642  0 May27 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4955  4943  0 Apr30 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    5712  5700  0 Apr28 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7162  7149  0 May19 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7275  7262  0 Apr17 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7983  7971  0 May12 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8013  8002  0 10:59 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8376  8364  0 May26 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8519  8507  0 11:03 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9196  9184  0 11:10 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9345  9333  0 Apr29 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9420  9407  0 May01 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9831  9818  0 11:16 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   10242 10229  0 May25 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11023 11010  0 Apr10 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11040 11020  0 Apr16 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11345 11332  0 Apr11 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11364 11343  0 Apr12 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11696 11684  0 Apr13 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12008 11998  0 11:39 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12454 12441  0 Apr15 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12680 12667  0 Apr14 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12751 12739  0 May13 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12849 12833  2 11:48 pts/1    00:00:26 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13152 13140  0 May02 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13731 13719  0 Apr05 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13869 13857  0 May24 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14027 14014  0 Apr04 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14073 14061  0 Apr03 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14366 13332  0 12:03 pts/2    00:00:00 grep --color=auto rman
oracle   15073 15061  0 May23 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15263 15251  0 May22 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15766 15753  0 Apr02 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15915 15903  0 May14 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   16805 16793  0 Mar31 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   16953 16939  0 Apr01 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   17648 17635  0 May21 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   17740 17728  0 May03 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   18265 18253  0 Apr09 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   18964 18951  0 May15 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   20731 20719  0 May20 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   21104 21092  0 May04 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23116 23104  0 May16 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23230 23216  0 Apr07 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23969 23956  0 Apr08 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   24092 24079  0 Apr24 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   25648 25636  0 May07 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   25843 25831  0 Apr23 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26261 26248  0 Apr25 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26421 26408  0 May08 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26470 26458  0 Apr22 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26776 26763  0 May05 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   28587 28574  0 Apr26 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29102 29090  0 May09 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29402 29389  0 Apr20 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29628 29613  0 May17 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29638 29625  0 Apr06 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   30118 30105  0 Apr21 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   32536 32523  0 Apr19 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   32609 32597  0 May10 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /

kill相关rman进程

[oracle@db-base trace]$ kill -9 `ps -ef|grep rman|grep -v grep|awk '{print $2}'`

rman 登录正常

[oracle@db-base trace]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 28 12:04:19 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1590736012)

RMAN> 

expdp报ORA-39064 ORA-29285错误

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:expdp报ORA-39064 ORA-29285错误

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

expdp导出数据,报错ORA-39064 ORA-29285错误,导致datapump的logfile记录的日志不全

. . 导出了 "HCP"."HR_ADJ_SAL_ADV_SETUP_M"                  0 KB       0 行
ORA-39064: 无法写入日志文件
ORA-29285: 文件写入错误
. . 导出了 "HCP"."HR_ADJ_SAL_CONFIRM"                      0 KB       0 行

查询数据库NLS_CHARACTERSET

SQL> select NAME, value$ from props$ where name like 'NLS_CHARACTERSET';

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_CHARACTERSET
UTF8

查看客户端字符集


SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

出现这个问题,是由于expdp本身调用UTL_FILE,在Oracle Database PL/SQL Packages and Types Reference中有When data encoded in one character set is read and Globalization Support is told (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NLS_LANG is set, it should be the same as the database character set.
基于这样的情况,通过设置NLS_LANG在客户端字符集和服务端一致,就不会出现该问题