ORA-39257: Data cannot be remapped for more than 10 columns.
ORA-39257: Data cannot be remapped for more than 10 columns.
前言
还是脱敏数据相关的事情。
使用expdp的remap_data参数对指定列进行一定规则转换后，如果指定转换的列超过10列，则报错ORA-39257。
ORA-39001: invalid argument value ORA-39257: Data cannot be remapped for more than 10 columns.
注意：这是一个BUG，可以用补丁修复。
环境模拟
自己的环境模拟吧。
数据库版本：
SYS@zkm> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SYS@zkm>
构造表（20个字段，超过10个即可）。
create table scott.test (id number); begin for i in 1..19 loop execute immediate 'alter table scott.test add a'||i||' number(1)'; end loop; end; /
SYS@zkm> create table scott.test (id number); Table created. SYS@zkm> begin 2 for i in 1..19 loop 3 execute immediate 'alter table scott.test add a'||i||' number(1)'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SYS@zkm> desc scott.test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER A1 NUMBER(1) A2 NUMBER(1) A3 NUMBER(1) A4 NUMBER(1) A5 NUMBER(1) A6 NUMBER(1) A7 NUMBER(1) A8 NUMBER(1) A9 NUMBER(1) A10 NUMBER(1) A11 NUMBER(1) A12 NUMBER(1) A13 NUMBER(1) A14 NUMBER(1) A15 NUMBER(1) A16 NUMBER(1) A17 NUMBER(1) A18 NUMBER(1) A19 NUMBER(1)
创建包和包体，包中定制数据转换规则，不需要太复杂。
create or replace package scott.pkg_remap is function fn_numeral_1(input_string number) return number; end; / create or replace package body scott.pkg_remap is function fn_numeral_1(input_string number) return number as begin return input_string+1; end; end; /
SYS@zkm> create or replace package scott.pkg_remap 2 is 3 function fn_numeral_1(input_string number) return number; 4 end; 5 / Package created. SYS@zkm> create or replace package body scott.pkg_remap 2 is 3 function fn_numeral_1(input_string number) return number as 4 begin 5 return input_string+1; 6 end; 7 end; 8 / Package body created.
不需要有数据，直接导出就报错了。
create directory dirtmp as '/home/oracle/'; expdp \' / as sysdba\' directory=dirtmp dumpfile=test.dmp logfile=test.log cluster=n tables=scott.test reuse_dumpfiles=y remap_data=scott.test.a1:scott.pkg_remap.fn_numeral_1,scott.test.a2:scott.pkg_remap.fn_numeral_1,scott.test.a3:scott.pkg_remap.fn_numeral_1,scott.test.a4:scott.pkg_remap.fn_numeral_1,scott.test.a5:scott.pkg_remap.fn_numeral_1,scott.test.a6:scott.pkg_remap.fn_numeral_1,scott.test.a7:scott.pkg_remap.fn_numeral_1,scott.test.a8:scott.pkg_remap.fn_numeral_1,scott.test.a9:scott.pkg_remap.fn_numeral_1,scott.test.a10:scott.pkg_remap.fn_numeral_1,scott.test.a11:scott.pkg_remap.fn_numeral_1
SYS@zkm> create directory dirtmp as '/home/oracle/'; Directory created. [oracle@oracle ~]$ expdp \' / as sysdba\' directory=dirtmp dumpfile=test.dmp logfile=test.log cluster=n tables=scott.test reuse_dumpfiles=y remap_data=scott.test.a1:scott.pkg_remap.fn_numeral_1,scott.test.a2:scott.pkg_remap.fn_numeral_1,scott.test.a3:scott.pkg_remap.fn_numeral_1,scott.test.a4:scott.pkg_remap.fn_numeral_1,scott.test.a5:scott.pkg_remap.fn_numeral_1,scott.test.a6:scott.pkg_remap.fn_numeral_1,scott.test.a7:scott.pkg_remap.fn_numeral_1,scott.test.a8:scott.pkg_remap.fn_numeral_1,scott.test.a9:scott.pkg_remap.fn_numeral_1,scott.test.a10:scott.pkg_remap.fn_numeral_1,scott.test.a11:scott.pkg_remap.fn_numeral_1 Export: Release 11.2.0.4.0 - Production on Wed May 13 14:56:27 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39257: Data cannot be remapped for more than 10 columns.
mos上可以搜索到，这个一个BUG。
Bug 14202396 - Datapump remap_data fails if used for more than 10 cols in a table (Doc ID 14202396.8)
下载并打补丁。
解压。
[oracle@oracle ~]$ unzip p14202396_112040_Generic.zip Archive: p14202396_112040_Generic.zip creating: 14202396/ inflating: 14202396/README.txt creating: 14202396/etc/ creating: 14202396/etc/xml/ inflating: 14202396/etc/xml/ShiphomeDirectoryStructure.xml inflating: 14202396/etc/xml/GenericActions.xml creating: 14202396/etc/config/ inflating: 14202396/etc/config/actions.xml inflating: 14202396/etc/config/inventory.xml inflating: 14202396/etc/config/deploy.xml creating: 14202396/files/ creating: 14202396/files/sqlpatch/ creating: 14202396/files/sqlpatch/14202396/ inflating: 14202396/files/sqlpatch/14202396/postdeinstall.sql inflating: 14202396/files/sqlpatch/14202396/postinstall.sql creating: 14202396/files/rdbms/ creating: 14202396/files/rdbms/admin/ inflating: 14202396/files/rdbms/admin/prvtbpm.plb inflating: 14202396/files/rdbms/admin/prvtbpd.plb inflating: 14202396/files/rdbms/admin/prvtkupc.plb inflating: 14202396/files/rdbms/admin/catmeta.sql creating: 14202396/files/rdbms/xml/ creating: 14202396/files/rdbms/xml/xsl/ inflating: 14202396/files/rdbms/xml/xsl/kuemodtb.xsl inflating: 14202396/files/rdbms/xml/xsl/kuetable.xsl inflating: 14202396/files/rdbms/xml/xsl/kumodtab.xsl inflating: 14202396/files/rdbms/xml/xsl/kucolumn.xsl inflating: 14202396/postinstall.sql replace PatchSearch.xml? [y]es, [n]o, [A]ll, [N]one, [r]ename: A inflating: PatchSearch.xml
这不是一个Rolling Patch，
[oracle@oracle 14202396]$ opatch query . |grep rolling Patch is a rolling patch: false Patch is a FMW rolling patch: false
但是可以不需要停止数据库和监听等服务，在补丁目录下的etc/config/inventory.xml文件中有这个字样，
<oneoff_inventory>
<opack_version version="11.1.0.4.0"/>
<reference_id number="14202396"/>
<unique_patch_id>18877117</unique_patch_id>
<date_of_patch year="2015" month="Apr" day="30" time="11:51:05 hrs" zone="PST8PDT"/>
<base_bugs>
<bug number="14202396" description="REMAP_DATA FAILS IF USED FOR MORE THAN 10 COLS IN A TABLE"/>
</base_bugs>
<required_components>
<component internal_name="oracle.rdbms" version="11.2.0.4.0" opt_req="O"/>
<component internal_name="oracle.rdbms.dbscripts" version="11.2.0.4.0" opt_req="O"/>
</required_components>
<os_platforms>
<platform name="Generic Platform 1" id="0"/>
</os_platforms>
<executables></executables>
<instance_shutdown>false</instance_shutdown>
<instance_shutdown_message></instance_shutdown_message>
<online_rac_installable>false</online_rac_installable>
<run_as_root>false</run_as_root>
<sql_migrate>false</sql_migrate>
<wls_prereq_oneoffs></wls_prereq_oneoffs>
<prereq_oneoffs></prereq_oneoffs>
<coreq_oneoffs></coreq_oneoffs>
<overlay_oneoffs></overlay_oneoffs>
<patch_type value="singleton"/>
<product_family value="db"/>
<auto>false</auto>
<applicable_product value=""/>
<products></products>
<update_components></update_components>
</oneoff_inventory>
而且，readme中也没有步骤说明需要停服务。
在以往认知中小补丁除了online patch是可以不需要停机打的，这里按照readme来也不需要，还有需不需要考虑RAC的问题，有点虚。
当然，PSU是肯定要停集群停数据库的。
关于<instance_shutdown>false</instance_shutdown>这一项，网上有点少谈到这个，有时间我研究下。
打补丁（OPatch我的环境是升级过的，不需要考虑OPatch版本的问题了）。
[oracle@oracle 14202396]$ pwd /home/oracle/14202396 [oracle@oracle 14202396]$ opatch apply Oracle Interim Patch Installer version 11.2.0.3.21 Copyright (c) 2020, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc OPatch version : 11.2.0.3.21 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2020-05-13_15-07-51PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 14202396 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y Backing up files... Applying interim patch '14202396' to OH '/u01/app/oracle/product/11.2.0/db_1' Patching component oracle.rdbms, 11.2.0.4.0... Patching component oracle.rdbms.dbscripts, 11.2.0.4.0... Patch 14202396 successfully applied. Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2020-05-13_15-07-51PM_1.log OPatch succeeded.
跑脚本。 sqlplus / as sysdba @?/sqlpatch/14202396/postinstall.sql ...这一步输出略，太多了...
再次导出数据，已经不再报错了。
[oracle@oracle 14202396]$ expdp \' / as sysdba\' directory=dirtmp dumpfile=test.dmp logfile=test.log cluster=n tables=scott.test reuse_dumpfiles=y remap_data=scott.test.a1:scott.pkg_remap.fn_numeral_1,scott.test.a2:scott.pkg_remap.fn_numeral_1,scott.test.a3:scott.pkg_remap.fn_numeral_1,scott.test.a4:scott.pkg_remap.fn_numeral_1,scott.test.a5:scott.pkg_remap.fn_numeral_1,scott.test.a6:scott.pkg_remap.fn_numeral_1,scott.test.a7:scott.pkg_remap.fn_numeral_1,scott.test.a8:scott.pkg_remap.fn_numeral_1,scott.test.a9:scott.pkg_remap.fn_numeral_1,scott.test.a10:scott.pkg_remap.fn_numeral_1,scott.test.a11:scott.pkg_remap.fn_numeral_1 Export: Release 11.2.0.4.0 - Production on Wed May 13 15:16:10 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=dirtmp dumpfile=test.dmp logfile=test.log cluster=n tables=scott.test reuse_dumpfiles=y remap_data=scott.test.a1:scott.pkg_remap.fn_numeral_1,scott.test.a2:scott.pkg_remap.fn_numeral_1,scott.test.a3:scott.pkg_remap.fn_numeral_1,scott.test.a4:scott.pkg_remap.fn_numeral_1,scott.test.a5:scott.pkg_remap.fn_numeral_1,scott.test.a6:scott.pkg_remap.fn_numeral_1,scott.test.a7:scott.pkg_remap.fn_numeral_1,scott.test.a8:scott.pkg_remap.fn_numeral_1,scott.test.a9:scott.pkg_remap.fn_numeral_1,scott.test.a10:scott.pkg_remap.fn_numeral_1,scott.test.a11:scott.pkg_remap.fn_numeral_1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "SCOTT"."TEST" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /home/oracle/test.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 13 15:16:24 2020 elapsed 0 00:00:12
PS：我虚拟机的时间是有问题的（手动狗头）。
后续
由于是生产的RAC环境，最后还是没有打。
搞成dblink导出到（顺便转换）别的库后，直接expdp。
