分类目录归档：Oracle

impdp 创建index提示ORA-00942: table or view does not exist

发表于 2025 年 1 月 19 日 由 惜分飞

在好几次的expdp/impdp迁移数据的过程中都遇到了index创建的过程中提示表不存在的现象提示类似：
impdp-ora-00942

通过观察可以发现index和table不在同一个用户下面,猜测是由于index的用户本身没有访问表的权限,而是通过其他用户比如sys/system或者有dba权限等有访问表和对index所在用户写入数据的用户操作导致,通过试验重现了该现象
创建两个用户,一个有dba权限（用来存放表数据）【xff1】，一个是resource权限用来创建index【xff2】

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 11:36:07 2025

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 XFF1 IDENTIFIED BY oracle ;

User created.

SQL> grant dba to xff1;

Grant succeeded.

SQL> CREATE USER XFF2 IDENTIFIED BY oracle ;

User created.

SQL> grant connect,resource to xff2;

Grant succeeded.

SQL> create table xff1.t_object as select * from dba_objects;

Table created.

SQL> create index xff2.i_object on xff1.t_object(object_id);

Index created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

使用expdp导出数据

C:\Users\XFF>expdp "'/ as sysdba'" dumpfile=xff.dmp schemas=xff1,xff2

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:02 2025

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
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" dumpfile=xff.dmp schemas=xff1,xff2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "XFF1"."T_OBJECT"                           8.774 MB   90627 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
  C:\APP\XFF\ADMIN\ORCL\DPDUMP\XFF.DMP
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Jan 19 11:37:05 2025 elapsed 0 00:00:02

使用impdp导入数据（把xff用户映射到u中）

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff1:u1,xff2:u2

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 11:37:16 2025

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
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" dumpfile=xff.dmp remap_schema=xff1:u1,xff2:u2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U1"."T_OBJECT"                             8.774 MB   90627 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE INDEX "U2"."I_OBJECT" ON "U1"."T_OBJECT" ("OBJECT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE
(INITIAL 65536 NEXT  1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"U2"."I_OBJECT" creation failed
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sun Jan 19 11:37:17 2025 elapsed 0 00:00:00

重现了ORA-39083 ORA-00942错误,根据经验确认可能是由于impdp导入的时候,创建index切换到当前index所属用户进行,而该用户没有访问需要创建index对应的表的权限导致,通过impdp sqlfile解析出阿里.sql文件进行分析,确认是该情况导致.
index

建议一般情况下,不要把表和index创建到不同用户(schema)下面,更不要使用第三用户来操作.

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

数据泵导出 (expdp) 和导入 (impdp)工具性能降低分析参考

发表于 2025 年 1 月 18 日 由 惜分飞

适用于:

Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) – 版本 N/A 和更高版本
Oracle Cloud Infrastructure – Database Service – 版本 N/A 和更高版本
Oracle Database Cloud Service – 版本 N/A 和更高版本
Oracle Database – Enterprise Edition – 版本 10.1.0.2 到 12.2.0.1 [发行版 10.1 到 12.2]
Oracle Database Backup Service – 版本 N/A 和更高版本
本文档所含信息适用于所有平台
用途

本文档提供了有关使用数据泵导入导出工具传输数据时所遇到的性能相关问题的可能原因。

适用范围

本文的目标受众是 Oracle10g 和 Oracle11g 数据库的用户，并且使用 Export Data pump 工具从 Oracle 源数据库中导出数据，并使用 Import Data pump 工具将这些数据导入到 Oracle 目标数据库中。本文档仅适用于新的 Export Data Pump (expdp) 和 Import Data Pump (impdp) 客户端，不适用于原始的导出 (exp) 和导入 (imp) 客户端。对于 Oracle10g 及更高版本，我们建议使用数据泵在 Oracle 数据库之间传输数据。

详细信息

简介

从版本 10g (10.1.0) 开始，Oracle 引入了新的 Oracle 数据泵技术，通过该项技术，用户能够以极快的速度将数据和元数据从一个数据库移动到另一个数据库。此项技术是 Oracle 新的数据移动工具（“Export Data pump”和“Import Data pump”）的基础。

在某些情况下，使用数据泵客户端卸载或加载数据时，可能会遇到性能问题。本文档将提供有关安装和配置设置的详细信息，这些设置可能会对数据泵客户端的性能产生影响；还将提供有关如何检查数据泵在某一特定时刻正在进行哪些操作的详细信息；此外，还将讨论一些会对性能产生影响的已知缺陷。

参数

在此部分列出了可能会对数据泵导出或导入作业的性能产生影响的数据泵参数。此外，还列出了一些通用数据库参数 (init.ora/spfile)，我们已知这些参数可能会对数据泵作业产生影响。
如果您遇到了数据泵性能问题并需要解决它，且作业中使用了以下一个或多个参数，请先检查以下备注，并查看在不使用该参数或以不同方式使用该参数的情况下此性能问题是否重现。

  1. 数据泵参数：PARALLEL
    …
    有关详细信息，另请参阅：
    Note:365459.1 “Parallel Capabilities of Oracle Data Pump”
    .
  2. 数据泵参数：DUMPFILE
    …
    .
  3. Export Data Pump 参数：ESTIMATE
    …
    有关Export Data Pump 参数 ESTIMATE 的详细信息，另请参阅：
    Note.786165.1 “Understanding the ESTIMATE and ESTIMATE_ONLY parameter in Export DataPump”
    .
  4. Export Data Pump 参数：FLASHBACK_SCN and FLASHBACK_TIME
    …
    .
  5. Import Data Pump 参数：TABLE_EXISTS_ACTION
    …
    .
  6. Import Data Pump 参数：REMAP_SCHEMA 或 REMAP_TABLESPACE
    …
    与此问题相关的详细信息，另请参阅下面的“缺陷详细信息”部分，以及：
    Note:429846.1 “Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters”
    .
  7. 数据库参数： CURSOR_SHARING
    …
    与此问题相关的详细信息，另请参阅下面的“缺陷详细信息”部分，以及：
    Note:94036.1 “Init.ora Parameter “CURSOR_SHARING” Reference Note”
    Note:421441.1 “Datapump Import With dblink Going Slow With cursor_sharing Set to ‘force’”
    .
  8. 导出/Import Data Pump 参数：STATUS

    监视正在进行的数据泵作业。此状态信息仅写入到您的标准输出设备中，而不写入到日志文件中（如果存在一个有效的日志文件）。

检查数据泵的活动

已知缺陷概述

下面概述了各个 Oracle10g 和 Orace11g 版本中已知的性能相关缺陷。请参阅概述之后的内容部分，以了解有关这些缺陷和可能的变通方案的详细信息。

注意 1：除了数据泵特定的缺陷，其它组件例如与优化器相关的缺陷也会在数据泵作业期间对性能产生影响。下面仅列出了一些影响最大的缺陷。

注意 2：使用指定的 NETWORK_LINK 参数执行导入时，影响 Export Data Pump 的缺陷也会对 Import Data Pump 产生影响。这些缺陷只在 Export Data Pump 部分列出一次。

Export DataPump (expdp):

10.1.0.1.0  至  10.1.0.3.0
- Bug 3447032 – Import Data Pump is slow when importing statistics
- Bug:4513695 - Poor performance for SELECT with ROWNUM=1 with literal replacement
- Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s
- Bug:5464834 - Export Data Pump runs out of memory (ORA-4030) when many tables are involved
- Bug:5590185 - Consistent Export Data Pump is slow when exporting row data
- Bug:5928639 - Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT
- Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables

10.1.0.4.0  至 10.1.0.5.0  以及  10.2.0.1.0  至 10.2.0.3.0
- Bug:4513695 - Poor performance for SELECT with ROWNUM=1 with literal replacement
- Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s
- Bug:5464834 - Export Data Pump runs out of memory (ORA-4030) when many tables are involved
- Bug:5590185 - Consistent Export Data Pump is slow when exporting row data
- Bug:5928639 - Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT
- Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables
- Bug 5573425 - Slow Datapump with wrong results due to subquery unnesting and complex view

10.2.0.4.0
- Bug 7413726 - Poor EXPDP performance when db COMPATIBLE=10.2.0.3 or 10.2.0.4 (duplicate of Bug 7710931)
- Bug 7710931 - DataPump export is extremely slow when extracting schema
- Bug 6460304 - (affects earlier versions as well) Expdp domain index dump using RULE Optimizer and slow
- Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp

11.1.0.6.0
- Bug 7585314 - OCSSD.BIN consumes much too much CPU while running Datapump
- Bug 7722575 - DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp

11.1.0.7.0
- Bug 8363441 - Very Expensive Sql Statement During Datapump Import With Many Subpartitions
- Bug 7722575 - DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
- Bug 8904037 - LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS

11.2.0.1
- Bug 10178675 - expdp slow with processing functional_and_bitmap/index
- Bug 10194031 - EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
11.2.0.2
- Unpublished Bug 12780993 DATA PUMP PERFORMANCE FOR ESTIMATE=STATISTICS IN EXPORT IS BAD
11.2.0.3
- <Unpublished Bug 12780993> DATA PUMP PERFORMANCE FOR ESTIMATE=STATISTICS IN EXPORT IS BAD
- Bug 13573203 SLOW INDEX EXPORT DUE TO PERFORMANCE ISSUE WITH METADATA KU$_INDEX_COL_VIEW
- Bug 13717234 - Datapump export for transport is slow handling a large number of objects
- Bug 13914808 QUERY AGAINST KU$_INDEX_VIEW KU$ SLOW EVEN AFTER USING METADATA FROM 13844935
- Bug 14192178 - EXPDP of partitioned table can be slow
- Bug 14794472 - EXPDP TOO SLOW HAVING TOO MANY TABLESPACES
- Bug 16138607 - SLOW EXPDP AFTER 11.2.0.3 UPGRADE
- Bug 16298117 - TTS EXPDP TAKING 26 HOURS TO COMPLETE, MOST OF TIME PROCESSING INDEX INFO
- Bug 16856028 - EXPORT DATAPUMP SLOW ON DATAGUARD STANDBY INSTANCE
- Bug 18793246 - EXPDP slow showing base object lookup during datapump export causes full table scan per object
- Bug 20446613 - EXPORTING NON-STREAMS TABLE FROM STRADMIN SCHEMA OVER NETWORK LINK IS SLOW
- Bug 20236523 - DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY

Note::
1)
对于11.2.0.3, patch 16038089 中包含了以下修复:
- Bug 12325243 - SLOW PERFORMANCE ON EXPDP FUNCTIONAL AND BITMAP INDEXES
- Unpublished Bug 12780993 – DATA PUMP PERFORMANCE FOR ESTIMATE=STATISTICS IN EXPORT IS BAD
- Bug 13573203 - SLOW INDEX EXPORT DUE TO PERFORMANCE ISSUE WITH METADATA KU$_INDEX_COL_VIEW
- Bug 13844935 - QUERY AGAINST KU$_INDEX_VIEW SLOW IN 11.2.0.3
- Bug 14192178 - BUG 14006804 FIX DOES NOT RESOLVE THE PERFORMANCE ISSUE

2)
相对于Patch 16038089，下边两个patch是更好的选择：
11.2.0.3 - Patch 15893700
11.2.0.3.3或更高 – MLR Patch 14742362
这是因为这两个patch包含了Patch 16038089中所有的修复，同时还修复了其它一些之前patch没有修复的性能问题。

3)
所有8个 bug 都在Patch 14742362中修复并已包含11.2.0.4补丁集中，详见：
Note 1562142.1 - 11.2.0.4 Patch Set – List of Bug Fixes by Problem Type

11.2.0.4
- Bug 14794472 - EXPDP TOO SLOW HAVING TOO MANY TABLESPACES
- Bug 13717234 - Datapump export for transport is slow handling a large number of objects
- Bug 16856028 - EXPORT DATAPUMP SLOW ON DATAGUARD STANDBY INSTANCE
- Bug 18469379 - Data pump export estimate phase takes a long time to determine if table is empty
- Bug 18793246 - EXPDP slow showing base object lookup during datapump export causes full table scan per object
- Bug 19674521 - EXPDP takes a long time when exporting a small table
- Bug 20111004 - “COMMENT ON COLUMN” statement waits 1 second on “Wait for Table Lock”
- Bug 20236523 - DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY
- Bug 20548904 - EXPDP HANG IN METADA_ONLY ON A PARTITION TABLE WITH AROUND 40000 SUBPARTITIONS
- Bug 20446613 - EXPORTING NON-STREAMS TABLE FROM STRADMIN SCHEMA OVER NETWORK LINK IS SLOW
- Bug 24560906 - HIGH CPU USAGE FOR PROCESS ORA_Q001_DBT11 AND ORA_Q007_DBT11
- BUG 27634991 - EXPDP FREQUENTLY WAITS ON ‘STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY’
Note:
在11.2.0.4上发布的merge patch 20883577包含了以下bug的fix: 18469379, 18793246, 19674521, 20236523 and 20548904
在11.2.0.4上发布的merge patch 21443197包含了以下bug的fix: 18082965 18469379 18793246 20236523 19674521 20532904 20548904

12.1.0.1
- Bug 18469379 - Data pump export estimate phase takes a long time to determine if table is empty
- Bug 18793246 - EXPDP slow showing base object lookup during datapump export causes full table scan per object
- Unpublished Bug 18720801 – DATAPUMP EXPORT IS SLOW DUE TO EXPORT OF SYNOPSES
- Bug 20111004 - “COMMENT ON COLUMN” statement waits 1 second on “Wait for Table Lock”
Note:
MLR Patch 23526956 released on top of 12.1.0.1 contains the fixes for the bugs: 18469379, 18793246
12.1.0.2
- Bug 18793246 - EXPDP slow showing base object lookup during datapump export causes full table scan per object
- Bug 20236523 - DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY
- Bug 20548904 - EXPDP HANG IN METADA_ONLY ON A PARTITION TABLE WITH AROUND 40000 SUBPARTITIONS
- Bug 21128593 - UPDATING THE Primary TABLE AT THE END OF DP JOB IS SLOW STARTING WITH 12.1.0.2

Note:
在12.1.0.2上发布的merge patch 20687195包含了以下bug的fix: 18793246, 20236523 and 20548904
在12.1.0.2上发布的merge patch 21554480包含了以下bug的fix: 18793246, 20236523, 20548904 and 21128593.
在12.1.0.2上发布的merge patch 26949116包含了以下bug的fix: 18793246, 20236523, 20532904, 20548904, 21128593, 22273229, 22862597 and 25139545
12.2.0.1
- Bug 26368590 – EXPDP Is Slow While Updating Primary Table After Upgrade To 12.2
- Bug 27144324 – LONG WAIT TIME AT THE END OF 12.2.0.1 DATAPUMP EXPORT JOB
- Bug 27277810 – DATAPUMP EXPORT EXTREMELY SLOW FETCHING COMMENT OBJECTS
- Bug 24707852 – APPSST12201::PERFORMANCE ISSUE WITH EXPDP DURING FULL DATABASE EXPORT
- Bug 28100495 – DATAPUMP SLOW FOR EMPTY TABLES WHEN UNLOADING TABLE_DATA

- Bug 27634991 - EXPDP FREQUENTLY WAITS ON ‘STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY’
- Uunpublished Bug 26736110 – DATAPUMP METADATA EXPORT IS SLOW FOR INDEXES WITH A HIGH PARTITION COUNT
- Bug 29959025 - EXPDP RUNNING LONG TIME QUERYING KU$_SUBPARTITION_EST_VIEW WHEN PROCESSING TABLE_DATA
- Uunpublished Bug 27277810 – DATAPUMP EXPORT EXTREMELY SLOW FETCHING COMMENT OBJECTS

Note:
MLR Patch 27194328 released on top of 12.2.0.1 contains the fixes for the bugs: 26368590 and 27144324
MLR Patch 28398639 released on top of 12.2.0.1 contains the fixes for the bugs: 24707852, 26117287, 26368590, 27144324 and 28100495

MLR Patch 29019842 released on top of 12.2.0.1 contains the fixes for the bugs: 28398639, 23103778, 24829009, 25786141, 27277810 and 27499636

MLR Patch 31189193 released on top of 12.2.0.1 contains the fixes for the bugs:
23103778, 24707852, 24829009, 25786141, 26117287, 26368590, 26736110, 27144324, 27277810, 27499636, 28100495, 28357349, 29613245, 29959025

MLR Patch 31176656 released on top of 12.2.0.1.200114 DBRU contains the fixes of the following bugs:
23103778, 24707852, 24829009, 2578614, 26117287, 26368590, 26736110, 27144324, 27277810, 27499636, 28100495, 28357349, 29613245, 29959025
MLR Patch 31567975 released on top of 12.2.0.1.200714DBJUL2020RU contains the fixes of the following bugs:
23103778,24707852,24829009,25786141,26117287,26368590,26736110,27144324,27277810,27499636,28100495,28357349,29613245,29959025

MLR Patch 31875265 released on top of 12.2.0.1.201020DBOCT2020RU contains the fixes of the following bugs:
23103778,24707852,24829009,25786141,26117287,26368590,26736110,27144324,27277810,27499636,28100495,29613245,29959025
MLR Patch 32370538 released on top of 12.2.0.1.210119DBJAN2021RU contains the fixes of the following bugs:
23103778,24707852, 24829009, 25786141,26117287,26368590,26736110,27144324,27277810,27499636,28100495,28357349,29613245,29959025

18.x.0.0
- Bug 27634991 – EXPDP FREQUENTLY WAITS ON ‘STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY’
- Unpublished Bug 26736110 – DATAPUMP METADATA EXPORT IS SLOW FOR INDEXES WITH A HIGH PARTITION COUNT

- Bug 29959025 - EXPDP RUNNING LONG TIME QUERYING KU$_SUBPARTITION_EST_VIEW WHEN PROCESSING TABLE_DATA

- Unpublished Bug 27277810 – DATAPUMP EXPORT EXTREMELY SLOW FETCHING COMMENT OBJECTS
- Bug 28100495 - DATAPUMP SLOW FOR EMPTY TABLES WHEN UNLOADING TABLE_DATA
- Bug 27144324 - LONG WAIT TIME AT THE END OF 12.2.0.1 DATAPUMP EXPORT JOB

Note:
MLR Patch 29611052 released on top of 18.4.0.0.181016DBRU for Bugs: 27144324 and 28100495
MLR Patch 28770317 released on top of 18.3.0.0.180717DBRU for Bugs: 27144324 and 28100495
MLR Patch 30957858 released on top of 18.5.0.0.190115DBRU for Bugs: 26736110, 27144324, 28100495, 28555193 and 29959025
MLR Patch 30736363 released on top of 18.9.0.0.200114DBRU for Bugs: 26736110, 27144324, 27277810, 28100495 and 29959025

19.x.0.0
- Bug 29959025 - EXPDP RUNNING LONG TIME QUERYING KU$_SUBPARTITION_EST_VIEW WHEN PROCESSING TABLE_DATA

- unpublished Bug 28771564 – DATAPUMP EXPORT INVOKED BY A PRIVILEGE USER EXECUTES A QUERY FOR V$OPEN_CURSOR
- unpublished Bug 31050896 – PARALLEL DATAPUMP SLOW ON CONSTRAINTS WHEN USING PRIVILEGED USER
- unpublished Bug 30944402 – SELECT FROM Primary TABLE RUNS SLOW DURING TABLE_DATA EXPORT WHEN THERE ARE MANY SUBPARTITIONS
- Bug 32370367 - EXPDP IN 19.7 THREE TIMES SLOWER THAN IT WAS IN 11.2.0.4
- unpublished Bug 32551008 – CONSOLIDATED BUG OF IMPROVEMENTS TO DATA PUMP / MDAPI PATCHING PROCEDURES
- unpublished Bug 31050896 – Datapump Export Slow on Constraints When Using Privileged User

Note:
MLR Patch 32812124 released on top of 19.10.0.0.210119DBRU contains the fixes for the bugs: 28357349, 28555193, 28771564, 29276889, 29284656, 29613245, 29959025, 30155338,
30157766, 30662417, 30763851, 30822078, 30944402, 30978304, 31200854, 31412130, 31711479, 31976738, 32551008
Import DataPump (impdp):

10.1.0.1.0  至  10.1.0.3.0
- Bug 3447032 – Import Data Pump is slow when importing statistics
- Bug:5292551 - Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
- Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode

10.1.0.4.0
- Bug:5292551 - Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
- Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode

10.1.0.5.0
- Bug 3508675 – Import Data Pump is slow when importing TABLE_DATA
- Bug:5292551 - Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
- Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode

10.2.0.1.0  至  10.2.0.3.0
- Bug:5071931 - Import Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE is slow
- Bug:5292551 - Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
- Bug 6989875 -Transportable Tablespace Import Spins Using CPU
- Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode

10.2.0.4.0
- Bug 7439689 - (affects earlier versions as well) Impdp workeer process spinning on MERGE statement

11.1.0.6.0
- Bug 7585314 - OCSSD.BIN consumes much too much CPU while running Datapump

11.1.0.7.0
- Bug 8363441 - Very Expensive Sql Statement During Datapump Import With Many Subpartitions

11.2.0.2
- Bug 13609098 - IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
- Bug 16396856 - TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU

11.2.0.3
- Bug 13609098 - IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
- Bug 14834638 - Import slow on create partitioned index
- Bug 16396856 - TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU
- Bug 19520061 - IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE
- Bug 20532904 DATAPUMP SLOW FOR PARTITIONED TABLE
- Bug 14192178 - EXPDP of partitioned table can be slow
注意：expdp的bug 14192178的fix对一些impdp/import以及一些DBMS_METADATA的查询也有帮助

11.2.0.4
- Bug 13609098 - IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
- Bug 19520061 - IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE

12.1.0.1
- Bug 16396856 - TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU

12.1.0.2
- Bug 24423416 - IMPDP FOR SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY TAKES HOURS
- Bug 22216154 - TTS IMPORT IS VERY SLOW WHEN THERE ARE A LOT OF OBJECTS INVOLVEDSev 1 SR

- Bug 25786141 – SLOW IMPDP ON THE SYS.KUPW$WORKER.MAIN EXECUTION
- Bug 26960528 – SLOW IMPDP ON THE SYS.KUPW$WORKER.MAIN

12.2.0.1
- Bug 25786141 – SLOW IMPDP ON THE SYS.KUPW$WORKER.MAIN EXECUTION

Note:
MLR Patch 29019842 released on top of 12.2.0.1 contains the fixes for the bugs: 28398639, 23103778, 24829009, 25786141, 27277810 and 27499636

18.x.0.0
- unpublished Bug 30822078 – IMPDP VERY SLOW DUE TO PROCESS REORDERING

19.x.0.0
- unpublished Bug 28950868 – KN:LNX:IMPORT (IMPDP) DOESNOT COMPLETE SINCE DW00 PROCESS SPINNING ON HIGH CPU USAGE
- unpublished Bug 30822078 – IMPDP VERY SLOW DUE TO PROCESS REORDERING

- unpublished BUG 31200854 – ADB-D: IMPORT PERFORMANCE OF PACKAGE_BODY

 

NOTE:
=====
1/当在12.1.0.2多租户环境下安装datapump patch的post步骤时可能会碰到Bug 23321125 - “DPLOAD DOESN’T CREATE THE SHARED OBJECTS ACROSS ALL PDBS”.
关于更多信息请参考:
Note 2175021.1 - Alert – Multitenant Customers: The objects created by the post-install steps of 12.1.0.2 Generic DataPump Patches Are not Shared Across All PDBS.
2/对于12.1.0.2版本，从April2019 Bundles开始，正确版本的dpload脚本是安装的一部分，并放置在rdbms / admin路径中。这就是为什么未发布的Bug 25139545 – TRACKING BUG TO INCLUDE DPLOAD.SQL FROM MAIN FOR FIXES ON 1120X AND 1210X,从2019年4月开始的DataPump Merge补丁中不再需要。
有关更多详细信息：Note 2539305.1 - ANNOUNCEMENT – DataPump Customers: Impact of Having the Correct Version of DPLOAD.SQL Part of 12.1.0.2 April2019 Bundles and Recommended Method to Rollback Any DataPump Patch That Conflicts With 12.1.0.2 April2019 Bundles
缺陷详细信息

  1. Bug 3447032 – Import Data Pump is slow when importing statistics
    - 缺陷：Bug 3447032“DBMS_STATS.SET_COLUMN_STATS can be slow (affects IMPORT)”（不是公开的 bug）
    - 症状：导入 INDEX_STATISTICS 或 TABLE_STATISTICS 时，Import（传统客户端）或 Import Data Pump 作业可能显示很长的等待时间
    - 版本：10.1.0.3.0 及更低版本
    - 已在以下版本中修正：10.1.0.4.0 及更高版本；对于某些平台, Patch:3447032 提供了针对 10.1.0.3.0 的修正
    - 打过补丁的文件：exuazo.o  kustat.xsl
    - 变通方案：排除统计信息导入 (EXCLUDE=statistics)，并在导入完成后手动创建统计信息
    - 原因：如何在带有（许多）子分区的表中设置列统计信息的问题
    - 跟踪：SQL 跟踪显示对 DBMS_STATS 包的引用
    - 备注：必须在两个站点（源数据库和目标数据库）上都应用此 bug 的修正，且必须重新生成全部的 Export 或 Export Data Pump 转储文件，以便在导入时获取性能提升。
    .
  2. Bug 3508675 – Import Data Pump is slow when importing TABLE_DATA
    - 缺陷：Bug 3508675“APPSST10G: BAD PLAN WHEN QUERYING ALL_POLICIES WHEN IMPORTING TABLE_DATA”（不是公开的 bug）
    - 症状：在 TABLE_DATA 的导入阶段，impdp 作业可能会显示较高的 CPU 使用率和较慢的运行速度
    - 版本：10.1.0.5.0
    - 已在以下版本中修正：10.2.0.1.0 及更高版本； Patch:3508675 提供了可用于 10.1.0.5.0 的通用修正
    - 打过补丁的文件：prvtbpdi.plb
    - 变通方案：无
    - 原因：伴随 Bug 3369744 的修正而产生，ALL_SYNONYMS 视图不显示同义词的同义词（不是公开的 bug）
    - 跟踪：SQL 跟踪和 AWR 跟踪显示了查询的执行时间和较高 CPU 使用率：
    SELECT count(*) FROM ALL_POLICIES WHERE enable = :y and ins = :y2 and object_name = :tname and object_owner = :sname
    - 备注：该 bug 可能会出现在 Oracle Application 数据库（apps）或导入了许多个表的任何其他目标数据库的 impdp 作业期间。
    .
  3. Bug 4513695 – Export Data Pump of large table can be very slow when CURSOR_SHARING=SIMILAR
    - 缺陷：  Bug:4513695 “Poor performance for SELECT with ROWNUM=1 with literal replacement”
    - 症状：大型表 (100+ Gb) 的 Export Data Pump 作业速度可能要比原始 exp 客户端的导出慢很多（例如，前者的导出时间超过 24 小时）
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本；对于某些平台，Patch:5481520 提供了针对 10.2.0.3.0 的修正
    - 打过补丁的文件：apa.o kko.o kkofkr.o qerco.o
    - 变通方案：如果可能，在开始 Export Data Pump 作业之前先设置 CURSOR_SHARING=EXACT
    - 原因：将 cursor_sharing 设置为 similar 时，基于成本的优化器（Cost Base Optimizer，CBO）中出现查询优化问题
    - 跟踪：Data Pump Worker 跟踪显示“SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ :”SYS_B_0″ FROM … WHERE ROWNUM = :”SYS_B_1″), :”SYS_B_2″) FROM DUAL”的 elapsed fetch 时间值非常高
    - 备注：针对此缺陷的修正只能作为 Bug:5481520 “Wrong results with ROWNUM and bind peeking”
    的修正予以提供。
    .
  4. Bug 5071931 – Import Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE is slow
    - 缺陷：Bug:5071931 “DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW”
    - 症状：在 DDL 的导入阶段，使用 REMAP_SCHEMA 和 REMAP_TABLESPACE 进行的 impdp 作业运行缓慢，例如：TABLE、INDEX、OBJECT_GRANT
    - 版本：10.2.0.1.0 至 10.2.0.3.0
    - 已在以下版本中修正：10.2.0.4.0 及更高版本；Patch:5071931 提供了适用于 10.2.0.3.0 的通用修正，且对于某些平台，该补丁还提供了针对较低版本的修正
    - 打过补丁的文件：prvtmeti.plb
    - 变通方案：如果不需要，则不使用 REMAP_% 参数
    - 原因：将多个转换链接在一起时出现了问题
    - 跟踪：Data Pump Worker 跟踪显示“DBMS_METADATA.CONVERT called”与“DBMS_METADATA.CONVERT returned”之间的 elapsed 时间值较高
    - 备注：此缺陷在 Oracle10g Release 1 中不会重现；有关详细信息，另请参阅
    Note:429846.1 “Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters”.
    .
  5. Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s
    - 缺陷：Bug 5095025“ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP”（不是公开的 bug）
    - 症状：在导出过程式的对象（比如 schema jobs）时，许多 schema（例如 50+）的 schema 级别 expdp 作业可能会因 PGA 耗尽（内存泄露）而失败
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 和更高版本
    - 打过补丁的文件：（ patchset 中）
    - 变通方案：如果可能，运行多个 Export Data Pump 作业，使每个作业都导出较少的 schema
    - 原因：查询优化问题（基于规则的优化器（Rule Based Optimizer，RBO），而不是基于成本的优化器 (CBO)）
    - 跟踪：ORA-4030 和 Data Pump Worker 跟踪可能会显示对以下语句的引用：“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘PROCDEPOBJ_T’, …”
    - 备注：与此缺陷相关的内容：Bug:5464834 、Bug:5928639 和 Bug 5929373（不是公开的 bug）
    .
  6. Bug 5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
    - 缺陷：Bug:5292551 “IMPDP VERY SLOW WHEN IMPORTING A TABLE WITH INITIALIZED COLUMN OF TYPE VARRAY”
    - 症状：导入表数据时，特定表（例如包含 Spatial 数据 MDSYS.SDO_GEOMETRY 的表）的 impdp 作业速度可能会非常慢，且在加载这些表时，Data Pump Worker 进程显示内存使用量在不断增加
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本；对于某些平台，Patch:5292551 提供了针对 10.2.0.3.0 的修正
    - 打过补丁的文件：kpudp.o
    - 变通方案：如果可能，排除这些表：EXCLUDE=TABLE:”in(‘TAB_NAME’, …)，并在第二次的表级别 Import Data Pump 作业中单独导入这些表：TABLES=owner.tab_name
    - 原因：内存没有释放，这导致存在较大数量的已分配内存
    - 跟踪：Heapdump 显示多个 freeable chunk“reeable assoc with marc”或“klcalh:ld_hds”
    - 备注：在运行数天之后，impdp 作业可能会失败，并出现错误，例如 ORA-4030（out of process memory when trying to allocate xxx bytes（在尝试分配 xxx 字节时进程内存不足））或 ORA-31626（job does not exist（作业不存在））或内部错误 ORA-00600 [729]、[12432]、[space leak]。
    .
  7. Bug 5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved
    - 缺陷：Bug:5464834 “ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP”
    - 症状：导出表数据时，许多表（例如 250+）的表级别 expdp 作业可能会因 PGA 耗尽（内存泄露）而失败
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本；Patch:5464834 提供了适用于 10.1.0.4.0 和 10.2.0.3.0 的通用修正
    - 打过补丁的文件：catmeta.sql  prvtmeti.plb
    - 变通方案：如果可能，运行多个 Export Data Pump 作业，使每个作业都导出较少数量的表
    - 原因：查询优化问题（基于规则的优化器 (RBO)，而不是基于成本的优化器 (CBO)）
    - 跟踪：ORA-4030 和Data Pump Worker 跟踪可能显示对以下语句的引用：“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    - 备注：与此缺陷相关的内容：Bug 5095025（不是公开的 bug）、Bug:5928639 和 Bug 5929373（不是公开的 bug）。
  8. Bug 5555463 – Import Data Pump can be slow when importing small LOBs (under 256K)
    - 缺陷：Bug 5555463“PERFORMANCE ISSUES FOR DATAPUMP IMPORT/EXTERNAL_TABLE MODE OF TABLES WITH LOBS”（不是公开的 bug）
    - 症状：在导入包含小 LOB（小于 256 kb 的 LOB）的表时，发生性能下降、高 CPU 使用率以及 LOB redo 生成的情况
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本
    - 打过补丁的文件：（在 patchset 中）
    - 变通方案：无（如果可能，在“Direct Path”模式下运行加载：ACCESS_METHOD=DIRECT_PATH）
    - 原因：在“External Table”模式下加载数据时使用临时 LOB
    - 跟踪：（无详细信息）
    - 备注：在“Direct Path”模式下，相同表数据的 impdp 作业显示更快的性能
    .
  9. Bug 5590185 – Consistent Export Data Pump is slow when exporting row data
    - 缺陷：Bug:5590185 “CONSISTENT EXPORT DATA PUMP JOB (FLASHBACK_TIME) HAS SLOWER PERFORMANCE”
    - 症状：在使用 FLASHBACK_TIME 或 FLASHBACK_SCN 时或在使用 logical standby 或 Streams 时，涉及较大数量表的 expdp 作业运行缓慢
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本；对于某些平台，Patch:5590185 提供了针对 10.2.0.2.0 的修正
    - 打过补丁的文件：prvtbpm.plb
    - 变通方案：如果不需要，则不运行一致性 Export Data Pump 作业
    - 原因：针对数据泵主表的全表扫描
    - 跟踪：SQL 跟踪显示以下语句的执行时间：
    UPDATE “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ SET scn = :1, flags = :2 WHERE (object_path_seqno = :3) AND (base_process_order = :4) AND (process_order > 0)
    - 备注：如果正常的 expdp 作业需要 1 个小时，则现在相同的一致性作业可能需要 8 个小时以上的时间。
    .
  10. Bug 5928639 – Export Data Pump can be very slow if CURSOR_SHARING is not EXACT
    - 缺陷：Bug:5928639 “DATAPUMP EXPORT SLOW WHEN CURSOR_SHARING is not EXACT”
    - 症状：如果涉及到多个表且未将 init.ora 或 spfile 参数 CURSOR_SHARING 设置为 EXACT，则 Export Data Pump 作业的运行速度可能会比较慢
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本，已包含 Bug:5464834 的修正（见上文）
    - 打过补丁的文件：catmeta.sql prvtmeti.plb
    - 变通方案：设置 spfile 参数 CURSOR_SHARING=EXACT
    - 原因：查询优化问题（基于规则的优化器 (RBO)，而不是基于成本的优化器 (CBO)）
    - 跟踪：Data Pump Worker 跟踪文件显示调用 DBMS_METADATA.FETCH_XML_CLOB 的等待时间较长，SQL 跟踪文件显示对以下语句的引用：“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    - 备注：与此缺陷相关的内容：Bug 5095025（不是公开的 bug）、Bug:5464834 和 Bug 5929373（不是公开的 bug）。
    .
  11. Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables
    - 缺陷：Bug 5929373“APPS ST GSI – DATA PUMP TAKES LONG TIME TO EXPORT DATA”（不是公开的 bug）
    - 症状：如果数据库具有多个用户表，则小表的 Export Data Pump 作业的运行速度可能会比较慢
    - 版本：10.1.0.x 和 10.2.0.3.0 及更低版本
    - 已在以下版本中修正：10.2.0.4.0 及更高版本，已包含 Bug:5464834 的修正（见上文）
    - 打过补丁的文件：catmeta.sql prvtmeti.plb
    - 变通方案：无
    - 原因：查询优化问题（基于规则的优化器 (RBO)，而不是基于成本的优化器 (CBO)）
    - 跟踪：Data Pump Worker 跟踪文件显示调用 DBMS_METADATA.FETCH_XML_CLOB 的等待时间较长，SQL 跟踪文件显示对以下语句的引用：“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    - 备注：数据泵可能需一个小时以上的时间来处理表，而原始的导出客户端则只需要两三分钟；与此缺陷相关的内容：Bug 5095025（不是公开的 bug）、Bug:5464834 和 Bug:5928639。
  12. Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
    - 缺陷：Bug 7722575“DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP”
    - 症状：数据泵视图 KU$_NTABLE_DATA_VIEW 和
    KU$_NTABLE_BYTES_ALLOC_VIEW 的定义可能会导致执行计划不甚理想以及数据泵导出视图的查询性能不佳
    - 版本：10.2.0.x 和 11.1.0.X
    - 已在以下版本中修正：10.2.0.5.0 和 11.2
    - 打过补丁的文件：catmeta.sql
    - 变通方案：无
    - 原因：ku$_ntable_data_view 数据泵视图的定义不正确
    - 跟踪：SQL 跟踪文件显示以下语句的执行计划成本过高：
    SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$),  XMLFORMAT.createFormat2(‘TABLE_DATA_T’, ’7′)), 0 ,KU$.BASE_OBJ.NAME , …
    FROM SYS.KU$_TABLE_DATA_VIEW KU$ WHERE ……
  13. Bug 10178675 – expdp slow with processing functional_and_bitmap/index
    - 缺陷：Bug:10178675 “expdp slow with processing functional_and_bitmap/index”
    - 症状：EXPDP 显示以下步骤消耗时间过长：
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    - 版本：10.2.0.4、11.1.0.7、11.2.0.1、11.2.0.2
    - 已在以下版本中修正：11.2.0.3、12.1
    - 打过补丁的文件：prvtmeta.plb、prvtmeti.plb
    - 变通方案：无
    - 原因：导出域索引时，其内部使用的是视图 ku$_2ndtab_info_view。使用 RBO时，此视图上的 select 会生成不良计划并耗费更多时间。
    - 跟踪：Expdp Worker (DW) 显示，执行以下形式的 SQL 花费了很长时间：
    SELECT INDEX_NAME, INDEX_SCHEMA, TYPE_NAME, TYPE_SCHEMA, FLAGS FROM SYS.KU$_2NDTAB_INFO_VIEW WHERE OBJ_NUM=:B1
  14. Bug 10194031 – EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
    - 缺陷：Bug:10194031 - EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
    - 症状：产生 ORA-4030 错误之前，包含 XMLTYPE 列的表的导出速度可能会非常慢。在尝试导出整个用户表或单独的表时，会发生此问题。
    - 版本：11.2.0.1、11.2.0.2
    - 已在以下版本中修正：11.2.0.3、12.1
    - 变通方案：无
    - 原因：对包含 xmltype 数据的表运行 expdp 时，发生内存泄露
  15. Bug 8904037 – LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
    - 缺陷：Bug 8904037 - LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
    - 症状：导出操作在对象类型为 DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM 上花费时间过长。
    - 版本：11.1.0.7, 11.2.0.1
    - 已在以下版本中修正：11.2.0.2, 12.1
    - 变通方案：移除 Workspace Manager 选项
    - 原因：由于在11.1.0.7中引入的函数”setCallStackAsValid”

参考：针对数据泵导出 (expdp) 和导入 (impdp)工具性能降低问题的检查表 (Doc ID 1549185.1)

发表在 逻辑备份/恢复 | 留下评论

19c非归档数据库断电导致ORA-00742故障恢复

发表于 2025 年 1 月 18 日 由 惜分飞

客户一套运行在win平台，非归档的19c数据库,由于异常断电导致数据库启动报ORA-01113,进行recover操作之后报ORA-00742
ora-00742

open之时alert日志报错信息

2025-01-18T00:17:52.205669+08:00
alter database open
2025-01-18T00:17:53.417839+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-01-18T00:17:53.436858+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_4428.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
2025-01-18T00:17:53.442863+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_4428.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...

recover database 数据库的alert日志报错Media Recovery failed with error 742和
ORA-01110 ORA-01208等错误

2025-01-18T00:20:10.196227+08:00
ALTER DATABASE RECOVER  database  
2025-01-18T00:20:10.221244+08:00
Media Recovery Start
 Started logmerger process
2025-01-18T00:20:10.459413+08:00
WARNING! Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 60 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 64 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 65 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 66 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 67 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
2025-01-18T00:20:10.599512+08:00
Parallel Media Recovery started with 12 slaves
2025-01-18T00:20:10.664559+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 2097 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
2025-01-18T00:20:12.644962+08:00
Media Recovery failed with error 742
2025-01-18T00:20:12.759043+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:13.135309+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:13.455536+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 4: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\UNDOTBS01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:14.408212+08:00
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

尝试recover datafile 1

SQL> RECOVER DATAFILE 1;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 1 序列 2097 块 296728 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG'

对于这种情况,比较明显是redo文件有写丢失,导致数据库无法正常的应用redo日志进行恢复,从而无法正常open.这种情况,只能只能选择屏蔽一致性,尝试强制打开数据库

C:\Users\Administrator\Desktop\check_db>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18 00:59:28 2025
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 10103231167 (? 01/17/2025 09:20:12 ??) ???? 1 ????
ORA-00289: ??:
D:\APP\ADMINISTRATOR\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC0000002097_1079211060.0001
ORA-00280: ?? 10103231167 (???? 1) ??? #2097 ?


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
ORA-00283: ??????????????????????????????
ORA-00742: ????????????????????? 1 ?????? 2097 ??? 296960
??????????????????????????????
ORA-00334: ????????????: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG'


ORA-01112: ???????


SQL> alter database open resetlogs;

Database altered.

alert日志对应的信息

2025-01-18T01:00:15.756033+08:00
alter database open resetlogs
2025-01-18T01:00:15.903141+08:00
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 10103247614 time 
.... (PID:4824): Clearing online redo logfile 1 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG
.... (PID:4824): Clearing online redo logfile 2 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO02.LOG
.... (PID:4824): Clearing online redo logfile 3 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
Clearing online log 1 of thread 1 sequence number 2098
Clearing online log 2 of thread 1 sequence number 2096
Clearing online log 3 of thread 1 sequence number 2097
2025-01-18T01:00:19.381697+08:00
.... (PID:4824): Clearing online redo logfile 1 complete
.... (PID:4824): Clearing online redo logfile 2 complete
.... (PID:4824): Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 3599991024 (0xd69380f0)
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG: Thread 1 Group 1 was previously cleared
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO02.LOG: Thread 1 Group 2 was previously cleared
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG: Thread 1 Group 3 was previously cleared
2025-01-18T01:00:19.550821+08:00
Setting recovery target incarnation to 2
2025-01-18T01:00:20.418458+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
Initializing SCN for created control file
Database SCN compatibility initialized to 3
2025-01-18T01:00:25.466167+08:00
Endian type of dictionary set to little
2025-01-18T01:00:25.476174+08:00
Assigning activation ID 3711463735 (0xdd387137)
2025-01-18T01:00:25.491185+08:00
TT00 (PID:3332): Gap Manager starting
2025-01-18T01:00:25.507197+08:00
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG
Successful open of redo thread 1
2025-01-18T01:00:26.162679+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2025-01-18T01:00:26.183694+08:00
TT03 (PID:1896): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2025-01-18T01:00:27.465636+08:00
Undo initialization recovery: err:0 start: 3158125 end: 3158390 diff: 265 ms (0.3 seconds)
Undo initialization online undo segments: err:0 start: 3158390 end: 3158390 diff: 0 ms (0.0 seconds)
Undo initialization finished serial:0 start:3158125 end:3158406 diff:281 ms (0.3 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
2025-01-18T01:00:28.790609+08:00
No Resource Manager plan active
2025-01-18T01:00:30.086561+08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2025-01-18T01:00:31.185369+08:00
TT03 (PID:1896): Sleep 10 seconds and then try to clear SRLs in 3 time(s)
2025-01-18T01:00:31.536626+08:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2025-01-18T01:00:31.638701+08:00
AQPC started with pid=38, OS id=4340 
2025-01-18T01:00:32.717495+08:00
Starting background process CJQ0
2025-01-18T01:00:32.726501+08:00
CJQ0 started with pid=40, OS id=1236 
Completed: alter database open resetlogs

数据库没有明显报错,直接resetlogs成功,直接逻辑导出数据,导入新库,完成本次恢复工作

发表在 Oracle备份恢复 | 标签为 , | 留下评论