标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,589)
- DB2 (22)
- MySQL (70)
- Oracle (1,459)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (526)
- Oracle安装升级 (83)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (13)
- PostgreSQL恢复 (3)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
- ORA-01595/ORA-600 4194处理
- 从ORA-00283 ORA-16433报错开始恢复
- 近期又遇到ORA-600 16703和ORA-702故障
- RECOVER_YOUR_DATA勒索恢复
- ORA-01033: ORACLE initialization or shutdown in progress 故障处理
- Oracle 19c/21c最新patch信息-202401
- 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
- ORA-600 kcrf_resilver_log_1故障处理
分类目录归档:逻辑备份/恢复
增加默认值列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跳部分表