expdp和impdp会带着表的supplemental log

最新推荐文章于 2025-06-07 10:00:00 发布

原创最新推荐文章于 2025-06-07 10:00:00 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle DB 同时被 2 个专栏收录

772 篇文章

订阅专栏

Oracle GoldenGate

101 篇文章

订阅专栏

本文详细介绍了如何通过expdp和impdp工具导出和导入表的supplementallog，确保逻辑复制软件正常运行，并避免出现ORA-01403错误。

做ogg，hvr的工程师都知道table的supplemental log，这个对逻辑复制软件相当重要，若是表级不开启这个supplemental log，在目的端会遇到ora-01403 数据未找到。
经过试验证实，expdp和impdp会带着表的supplemental log

[oracle@gg321 ~]$ expdp sys/orace directory=dumpdir dumpfile=a1.dmp logfile=a1_exp.log tables=u1.a1

Export: Release 10.2.0.4.0 - Production on Monday, 31 August, 2015 13:39:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=dumpdir dumpfile=a1.dmp logfile=a1_exp.log tables=u1.a1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "U1"."A1"                    42.87 KB      34 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u02/a1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 13:39:59

[oracle@gg321 ~]$ impdp sys/oracle directory=dumpdir dumpfile=a1.dmp logfile=a1_imp.log REMAP_SCHEMA=U1:scott

Import: Release 10.2.0.4.0 - Production on Monday, 31 August, 2015 14:20:45

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

UDI-00008: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02":  /******** AS SYSDBA directory=dumpdir dumpfile=a1.dmp logfile=a1_imp.log REMAP_SCHEMA=u1:scott 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."A1"                       42.87 KB      34 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_02" successfully completed at 14:21:07

[oracle@gg321 ~]$

上面的expdp和impdp的输出是没有体现log group的.需要查询dba_log_groups和DBA_LOG_GROUP_COLUMNS就可以看到表的log group确实被expdp和impdp了。