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

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

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

impdp中的DISABLE_ARCHIVE_LOGGING参数测试

在oracle 12c版本中引入了impdp中的TRANSFORM中的DISABLE_ARCHIVE_LOGGING值,可以实现在导入的时候使用nologging处理从而减少日志量也增加速度,但是在force logging情况下该参数无效
创建测试表

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 7 10:20:45 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t_xifenfei as select * from dba_objects;

Table created.

SQL> insert into t_xifenfei select * from t_xifenfei;

217838 rows created.

SQL> /

435676 rows created.

SQL> /

871352 rows created.

SQL> /

1742704 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
   3485408

导出测试表

[oracle@localhost ~]$ expdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes

Export: Release 12.2.0.1.0 - Production on Fri Apr 7 11:55:01 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "XFF"."SYS_EXPORT_TABLE_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei REUSE_DUMPFILES=yes 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xffdb/dpdump/4A93528C587D82CEE055000000000001/t_xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Fri Apr 7 11:55:59 2017 elapsed 0 00:00:58

归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost rdbms]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:43:23 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:46:05 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:47:08 2017 elapsed 0 00:01:02

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:47:30 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     17
Next log sequence to archive   19
Current log sequence           19

这里可以看出来,导入过程使用时间为1分钟多,导入过程日志切换 了3次

归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:49:23 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 08 2017 02:46:05 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:50:00 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20


[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 02:54:49 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 02:55:00 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 02:55:45 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

这里可以看出来当使用了DISABLE_ARCHIVE_LOGGING为Y之后导入日志没有发生切换,导入时间仅为10s.

非归档模式下不使用DISABLE_ARCHIVE_LOGGING导入

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21
SQL> drop table xff.t_xifenfei purge;

Table dropped.

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:22:42 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:23:17 2017 elapsed 0 00:00:27

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:23:49 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Current log sequence           21

这里测试在非归档模式下不设置DISABLE_ARCHIVE_LOGGING,日志量增加不明显,导入时间变为为27秒.

非归档模式下使用DISABLE_ARCHIVE_LOGGING导入

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:24:10 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 08 2017 03:22:43 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

[oracle@localhost ~]$  impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 03:25:51 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 03:26:01 2017 elapsed 0 00:00:10
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 03:26:37 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     20
Current log sequence           22

这里可以看出来在非归档模式下使用DISABLE_ARCHIVE_LOGGING导入时间为10s,日志量也没有明显增加。

在force logging在非归档情况下使用不DISABLE_ARCHIVE_LOGGING参数

[oracle@localhost ~]$ sqlplus xff/oracle@localhost/pdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:07:07 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Sat Apr 08 2017 03:29:36 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> drop table t_xifenfei purge;

Table dropped.
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24
SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:10:39 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:11:02 2017 elapsed 0 00:00:21

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:11:17 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log lsit;
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     22
Current log sequence           24

这可以可以看出来在非归档情况下force logging无明显增加日志量和导入时间

在force logging在归档情况下使用DISABLE_ARCHIVE_LOGGING参数

SQL> drop table xff.t_xifenfei purge;

Table dropped.

SQL> alter system switch logfile;

System altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25

[oracle@localhost ~]$ impdp xff/oracle@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y

Import: Release 12.2.0.1.0 - Production on Sat Apr 8 10:33:28 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":  xff/********@localhost/pdb dumpfile=t_xifenfei.dmp TRANSFORM=DISABLE_ARCHIVE_LOGGING:y 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XFF"."T_XIFENFEI"                          460.6 MB 3485408 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 8 10:34:50 2017 elapsed 0 00:01:15
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Apr 8 10:35:09 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

这里可以看出来在force logging情况下,设置DISABLE_ARCHIVE_LOGGING参数不生效
从上述测试在不管是非归档还是归档情况下使用DISABLE_ARCHIVE_LOGGING都会减小导入时间,减少归档量,但是需要注意如果数据库是force logging情况下,DISABLE_ARCHIVE_LOGGING参数会无效。

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