分类目录归档:逻辑备份/恢复

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在客户端字符集和服务端一致,就不会出现该问题

发表在 逻辑备份/恢复 | 标签为 , , | 评论关闭

IMP-00098: INTERNAL ERROR: impgst2

有网友找到我们,imp导入数据库报IMP-00098: INTERNAL ERROR: impgst2错误,原始环境已经彻底破坏,无法通过数据文件恢复
20191120122739


20191120122750

通过分析,该表有218列

3240997713: TABLE "SWIP_ENTITY_TRX"
3241009738: BIND information for 218 columns
 col[  1] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[  2] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[  3] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[  4] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[  5] type 2 max length 22
 col[  6] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[  7] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[  8] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[  9] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 10] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 11] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 12] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 13] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 14] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 15] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 16] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 17] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 18] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 19] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 20] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 21] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 22] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 23] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 24] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 25] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[ 26] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[ 27] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[ 28] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 29] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 30] type 12 max length 7
 col[ 31] type 12 max length 7
 col[ 32] type 12 max length 7
 col[ 33] type 12 max length 7
 col[ 34] type 12 max length 7
 col[ 35] type 12 max length 7
 col[ 36] type 12 max length 7
 col[ 37] type 12 max length 7
 col[ 38] type 12 max length 7
 col[ 39] type 12 max length 7
 col[ 40] type 12 max length 7
 col[ 41] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 42] type 12 max length 7
 col[ 43] type 2 max length 22
 col[ 44] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 45] type 2 max length 22
 col[ 46] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 47] type 2 max length 22
 col[ 48] type 2 max length 22
 col[ 49] type 2 max length 22
 col[ 50] type 2 max length 22
 col[ 51] type 2 max length 22
 col[ 52] type 2 max length 22
 col[ 53] type 2 max length 22
 col[ 54] type 2 max length 22
 col[ 55] type 2 max length 22
 col[ 56] type 2 max length 22
 col[ 57] type 2 max length 22
 col[ 58] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 59] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 60] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 61] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 62] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 63] type 2 max length 22
 col[ 64] type 2 max length 22
 col[ 65] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 66] type 2 max length 22
 col[ 67] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 68] type 1 max length 2048 cset 873 (AL32UTF8) form 1
 col[ 69] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 70] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[ 71] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[ 72] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 73] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 74] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 75] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 76] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 77] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 78] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 79] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 80] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 81] type 12 max length 7
 col[ 82] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[ 83] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[ 84] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 85] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 86] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 87] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 88] type 1 max length 1 cset 873 (AL32UTF8) form 1
 col[ 89] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 90] type 1 max length 1000 cset 873 (AL32UTF8) form 1
 col[ 91] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 92] type 1 max length 100 cset 873 (AL32UTF8) form 1
 col[ 93] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 94] type 12 max length 7
 col[ 95] type 1 max length 1000 cset 873 (AL32UTF8) form 1
 col[ 96] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[ 97] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[ 98] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[ 99] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[100] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[101] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[102] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[103] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[104] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[105] type 1 max length 80 cset 873 (AL32UTF8) form 1
 col[106] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[107] type 12 max length 7
 col[108] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[109] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[110] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[111] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[112] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[113] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[114] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[115] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[116] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[117] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[118] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[119] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[120] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[121] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[122] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[123] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[124] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[125] type 2 max length 22
 col[126] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[127] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[128] type 12 max length 7
 col[129] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[130] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[131] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[132] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[133] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[134] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[135] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[136] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[137] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[138] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[139] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[140] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[141] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[142] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[143] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[144] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[145] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[146] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[147] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[148] type 2 max length 22
 col[149] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[150] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[151] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[152] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[153] type 12 max length 7
 col[154] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[155] type 1 max length 19 cset 873 (AL32UTF8) form 1
 col[156] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[157] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[158] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[159] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[160] type 1 max length 1 cset 873 (AL32UTF8) form 1
 col[161] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[162] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[163] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[164] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[165] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[166] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[167] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[168] type 1 max length 200 cset 873 (AL32UTF8) form 1
 col[169] type 1 max length 20 cset 873 (AL32UTF8) form 1
 col[170] type 2 max length 22
 col[171] type 2 max length 22
 col[172] type 2 max length 22
 col[173] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[174] type 12 max length 7
 col[175] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[176] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[177] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[178] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[179] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[180] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[181] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[182] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[183] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[184] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[185] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[186] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[187] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[188] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[189] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[190] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[191] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[192] type 2 max length 22
 col[193] type 12 max length 7
 col[194] type 12 max length 7
 col[195] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[196] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[197] type 1 max length 1 cset 873 (AL32UTF8) form 1
 col[198] type 12 max length 7
 col[199] type 2 max length 22
 col[200] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[201] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[202] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[203] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[204] type 1 max length 40 cset 873 (AL32UTF8) form 1
 col[205] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[206] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[207] type 1 max length 10 cset 873 (AL32UTF8) form 1
 col[208] type 12 max length 7
 col[209] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[210] type 1 max length 14 cset 873 (AL32UTF8) form 1
 col[211] type 1 max length 12 cset 873 (AL32UTF8) form 1
 col[212] type 1 max length 24 cset 873 (AL32UTF8) form 1
 col[213] type 96 max length 1 cset 873 (AL32UTF8) form 1
 col[214] type 1 max length 100 cset 873 (AL32UTF8) form 1
 col[215] type 2 max length 22
 col[216] type 1 max length 100 cset 873 (AL32UTF8) form 1
 col[217] type 1 max length 100 cset 873 (AL32UTF8) form 1
 col[218] type 1 max length 100 cset 873 (AL32UTF8) form 1
Conventional export
3241011300: start of table data

由于某种原因导致该dmp异常了,而且客户的主要数据都在这个表里面,因此找我们进行恢复处理.通过工具扫描,确定dmp基本上是好的.进行二次处理,把该dmp中这个表重新恢复成dmp文件,然后导入数据库,完成恢复(包括解决某些工具有汉字乱码问题,和sqlldr换行导入问题等)
20191120145616


发表在 逻辑备份/恢复 | 标签为 , , , , | 评论关闭

增加默认值列exp DIRECT=Y导出,导入遭遇ORA-01400

最近的一次数据迁移中使用exp导出遭遇到ORA-01400: cannot insert NULL into 错误,凭着经验对其进行了重现,确定是由于增加默认值的列,使用DIRECT=Y 导出导致该问题
创建测试表
创建一张表,并且增加带默认值而且不为空的列,然后使用exp DIRECT=Y 导出数据

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>  create table t_xifenfei as select * from dba_objects;

Table created.

SQL> alter table t_xifenfei add (c_xifenfei varchar2(30) default 'www.xifenfei.com' not null);

Table altered.

SQL> select c_xifenfei from t_xifenfei where rownum<10;

C_XIFENFEI
------------------------------
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com

9 rows selected.

[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000  COMPRESS=NO \
>  BUFFER=102400000 STATISTICS=none DIRECT=Y recordlength=65535 file=/tmp/t_xifenfei.dmp 

Export: Release 11.2.0.4.0 - Production on Tue Aug 21 15:56:52 2018

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Direct Path ...
. . exporting table                     T_XIFENFEI
........
                                                        86421 rows exported
Export terminated successfully without warnings.

测试导入数据
导入到另外一个用户下面

[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei.dmp fromuser=xff touser=xff1

Import: Release 11.2.0.4.0 - Production on Tue Aug 21 15:57:53 2018

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

Export file created by EXPORT:V11.02.00 via direct path

Warning: the objects were exported by XFF, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing XFF's objects into XFF1
. . importing table                   "T_XIFENFEI"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")
Column : SYS
Column : ICOL$
Column : 
Column : 20
Column : 2
Column : TABLE
Column : 24-AUG-2013:11:37:35
Column : 24-AUG-2013:11:47:37
Column : 2013-08-24:11:37:35
Column : VALID
Column : N
Column : N
Column : N
Column : 1
Column : 
Column : 
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("XFF1"."T_XIFENFEI"."C_XIFENFEI")

这里可以看到,故障已经重现ORA-01400: cannot insert NULL into,无法将空值插入到刚刚新加的默认值的列中

使用常规导出

[oracle@bogon ~]$ exp xff/oracle tables=t_xifenfei FEEDBACK=10000  
COMPRESS=NO BUFFER=102400000 STATISTICS=none  file=/tmp/t_xifenfei1.dmp 

Export: Release 11.2.0.4.0 - Production on Tue Aug 21 16:00:50 2018

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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI
........
                                                        86421 rows exported
Export terminated successfully without warnings.

导入数据

[oracle@bogon ~]$ imp xff1/oracle file=/tmp/t_xifenfei1.dmp fromuser=xff touser=xff1

Import: Release 11.2.0.4.0 - Production on Tue Aug 21 16:04:47 2018

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

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by XFF, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing XFF's objects into XFF1
. . importing table                   "T_XIFENFEI"      86421 rows imported
Import terminated successfully without warnings.
[oracle@bogon ~]$ 
[oracle@bogon ~]$ sqlplus xff1/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 21 16:10:16 2018

Copyright (c) 1982, 2013, Oracle.  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

SQL> select c_xifenfei from t_xifenfei where rownum<10;

C_XIFENFEI
------------------------------
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com
www.xifenfei.com

9 rows selected.

通过测试证明,对于11g新特性增加默认值,而且非为空的列,如果使用exp常规方法导入,再导出没有任何问题,如果使用DIRECT=Y特性,将导致导入失败(ORA-01400).这是由于11g的快速增加列和默认值且不为空的新特性导致的(直接修改字典,并没有真正的去修改数据底层存储).
查询mos发现匹配bug: Bug 13961541 : EXP WITH DIRECT=Y CAN NOT HANDLE ADD COLUMN DEFAULT NOT NULL FEATURE IN 11G

发表在 逻辑备份/恢复 | 标签为 , | 评论关闭