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

增加默认值列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参数会无效。

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

exp跳过某些表导出数据

有一个需求,某个用户有很多张表,但是只能使用exp导出,而且想跳过其中某几张表,其他对象包括依赖关系都需要。针对这样的情况,通过分析exp的视图脚本,在exu10tabs视图进行修改,跳过某些表即可
修改exu10tabs视图
本测试为11.2.0.4版本,不同版本数据库,可能视图名称和语句有一定出入,请别照搬,exu10tabs在$ORACLE_HOME/rdbms/admin/catexp.sql中

CREATE OR REPLACE VIEW exu10tabs (
                objid, dobjid, name, owner, ownerid, tablespace, tsno, fileno,
                blockno, audit$, comment$, clusterflag, mtime, modified, tabno,
                pctfree$, pctused$, initrans, maxtrans, degree, instances,
                cache, tempflags, property, deflog, tsdeflog, roid, recpblk,
                secondaryobj, rowcnt, blkcnt, avgrlen, tflags, trigflag,
                objstatus, xdbool)
      AS                                                      /* Heap tables */
        SELECT
                o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
                t$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
                NVL(t$.bobj#, 0), o$.mtime,
                DECODE(BITAND(t$.flags, 1), 1, 1, 0), NVL(t$.tab#, 0),
                MOD(t$.pctfree$, 100), t$.pctused$, t$.initrans, t$.maxtrans,
                NVL(t$.degree, 1), NVL(t$.instances, 1),
                DECODE(BITAND(t$.flags, 8), 8, 1, 0),
                MOD(TRUNC(o$.flags / 2), 2), t$.property,
                DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
                t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
                NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
                t$.flags, t$.trigflag, o$.status,
                (SELECT COUNT(*)
                    FROM sys.opqtype$ opq$
                    WHERE opq$.obj# = o$.obj# AND
                          BITAND(opq$.flags, 32) = 32 )
        FROM    sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
                sys.com$ c$
        WHERE   t$.obj# = o$.obj# AND
                t$.ts# = ts$.ts# AND
                u$.user# = o$.owner# AND
                o$.obj# = c$.obj#(+) AND
                c$.col#(+) IS NULL AND
                BITAND(o$.flags,128) != 128 AND      /* Skip recycle bin */
                BITAND(t$.property, 64+512) = 0 AND /*skip IOT and ovflw segs*/
                BITAND(t$.flags, 536870912) = 0    /* skip IOT mapping table */
                and o$.name not in('T_XIFENFEI','T_ORASOS')  --增加需要跳过表
      UNION ALL                                         /* Index-only tables */
        SELECT  o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
                i$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
                NVL(t$.bobj#, 0), o$.mtime,
                DECODE(BITAND(t$.flags, 1), 1, 1, 0),
                NVL(t$.tab#, 0), 0, 0, 0, 0,
                NVL(t$.degree, 1), NVL(t$.instances, 1),
                DECODE(BITAND(t$.flags, 8), 8, 1, 0),
                MOD(TRUNC(o$.flags / 2), 2), t$.property,
                DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
                t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
                NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
                t$.flags, t$.trigflag, o$.status,
                (SELECT COUNT(*)
                    FROM sys.opqtype$ opq$
                    WHERE opq$.obj# = o$.obj# AND
                          BITAND(opq$.flags, 32) = 32 )
        FROM    sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
                sys.com$ c$, sys.ind$ i$
        WHERE   t$.obj# = o$.obj# AND
                u$.user# = o$.owner# AND
                o$.obj# = c$.obj#(+) AND
                c$.col#(+) IS NULL AND
                BITAND(o$.flags,128) != 128 AND      /* Skip recycle bin */
                BITAND(t$.property, 64+512) = 64 AND /* IOT, but not overflow*/
                t$.pctused$ = i$.obj# AND/* For IOTs, pctused has index obj# */
                i$.ts# = ts$.ts#

准备测试条件
创建用户xifenfei,在该用户下面创建四个表,其中有t_xifenfei和t_orasos需要跳过

[oracle@localhost ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 21:38:18 2016

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> create user xifenfei identified by oracle;

User created.

SQL> grant dba to xifenfei;

Grant succeeded.

SQL> conn xifenfei/oracle
Connected.
SQL> create table t_xifenfei as select * from dba_tables;

Table created.

SQL> create table t_xifenfei_exp as select * from dba_tables;

Table created.

SQL> create table t_orasos as select * from dual;

Table created.

SQL> create table xff_t_orasos as select * from dual;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_ORASOS                       TABLE
T_XIFENFEI                     TABLE
T_XIFENFEI_EXP                 TABLE
XFF_T_ORASOS                   TABLE

exp导出数据测试

[oracle@localhost ~]$ exp xifenfei/oracle owner=xifenfei file=/tmp/www.xifenfei.com.dmp

Export: Release 11.2.0.4.0 - Production on Sun Feb 21 21:40:23 2016

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 users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XIFENFEI 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XIFENFEI 
About to export XIFENFEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XIFENFEI's tables via Conventional Path ...
. . exporting table                 T_XIFENFEI_EXP       3374 rows exported
. . exporting table                   XFF_T_ORASOS          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

通过测试,我们发现,在xifenfei用户中有四个表,按照我们的设想跳过了事先配置的表.通过修改exu10tabs脚本,完美实现exp跳部分表

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