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。