Oracle各种类型坏块说明和处理

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle各种类型坏块说明和处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

 

CORRUPTION EXPLANATION
What is a corruption?
Note 840978.1 : Physical and Logical block corruption

How to force reformat a corrupted block?
Note 336133.1 : How to Format Corrupted Block Not Part of Any Segment

 How to identify corruptions in a database?
Note 472231.1 : How to identify all the Corrupted Objects in the Database reported by RMAN
Note 819533.1 : How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY

Known corruptions caused by OS/HW Issues
Note 1323649.1 : Known Corruption issues caused by 3rd party Software Provider
TYPES OF CORRUPTIONS
SEGMENT CORRUPTED  How to handle Segment Corruptions (Table, Index, LOB, Long, IOT, Temporary This section refers to BLOCK CORRUPTION affecting database segments.
CONTROLFILE CORRUPTION Note 48808.1 : OERR: ORA-00227 corrupt block detected in controlfile: (block %s, # blocks %s) Primary Note / Troubleshooting, Diagnostic and Solution
UNDO CORRUPTION Note 1950230.1 : Solving UNDO Corruption

Note 281429.1 : Basic Steps to be Followed While Solving ORA-00600 [4194]/[4193] Errors Without Using Unsupported parameter
Note 39283.1   : ORA-600 [4194] “Undo Record Number Mismatch While Adding Undo Record”
Note 431652.1 : How to Change the Existing Undo Tablespace to a New Undo Tablespace
DICTIONARY INCONSISTENCY It refers to inconsistencies between Data Dictionary tables. These tables are owned by the user SYS and are stored in the SYSTEM tablespace to keep track of the users, tables, indexes, etc that are created in the database.

Example of this inconsistency is:

‘Problem: OBJ$.OWNER# not in USER$” which refers to a user in table OBJ$ that does not exist in USER$. To identify known Data Dictionary inconsistencies run script hcheck:

Note 136697.1 : “hcheck.sql” Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
REDO CORRUPTION Note 332672.1 : ORA-354 ORA-353 and ORA-312 on Redo Log Group members

Note 1031381.6 : How to Dump Redo Log File Information.
ROW & COLUMN CORRUPTION It means that a column does not contain a valid value corresponding to its declaration; example: column was declared as date but the stored date is invalid.  If the block structure is ok; example, dbverify nor rman with the check logical option detect any issue then this is not a block corruption but it is included here for completeness.

Note 428526.1 : Baddata Script To Check Database For Corrupt column data
Note 976591.1 : How To validate a date/timestamp column
Note 869305.1 : How To identify a ‘corrupt’ row when error is raised but no row information provided
Note 136620.1 : Sanity Check of Oracle NUMBERS, How to Find and Patch
TABLE/INDEX INCONSISTENCY Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by “analyze validate structure cascade”.

See section “Identify TABLE / INDEX Mismatch” in:

Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
HOW TO HANDLE SEGMENT CORRUPTIONS
TABLE CORRUPTION Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g
INDEX CORRUPTION Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g
Note 438143.1 : Use of dbms_metadata.get_ddl() to extract Index DDL
Note 394143.1 : How Could I Format The Output From Dbms_metadata.Get_ddl Utility?
LOB / LOBSEGMENT Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g

Note 452341.1 : How to detect Lob Corruption
Note 293515.1 : ORA-1578 ORA-26040 in a LOB segment – Script to solve the errors
LONG ** Contact Oracle Support **  –>> Generic notes about these objects: * None Public*
Index Organized Table IOT Reference the IOT section in:

Note 28814.1 : Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g

Also reference section “SKIP ORA-600 in IOT” in:

Note 1527738.1 : SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE
TEMPORARY Note 1332088.1 : How to clear a block corruption in a TEMPORARY segment
PACKAGE INFORMATION AND EXAMPLES
DBMS_REPAIR Note 556733.1 : DBMS_REPAIR script
Note 68013.1: DBMS_REPAIR example
DBMS_METADATA Note 438143.1 : Use of dbms_metadata.get_ddl() to extract Index DDL
Note 394143.1 : Q How Could I Format The Output From Dbms_metadata.Get_ddl Utility?
Note 332077.1 : Why Does DBMS_METADATA.GET_DDL Not Show All Lines?
Note 188838.1 : Using DBMS_METADATA To Get The DDL For Objects
TOOLS
DBV Note 35512.1   : DBVERIFY – Database file Verification Utility (7.3.2 – 10.2)
Note 269028.1 : DBV Reports Corruption Even After Drop/Recreate Object
Note 336133.1 : How to Format Corrupted Block Not Part of Any Segment
EXPORT Note 214369.1 : Using The Export Utility To Check For Database Corruption
RMAN Note 472231.1 : How to identify all the Corrupted Objects in the Database with RMAN
TRANSPORTABLE TABLESPACE Note 733824.1 : How To Recreate a database using TTS
DATA RECOVERY ADVISOR Note 1579579.1 : Primary Note For Oracle Data Recovery Advisor (DRA)
ORA600/7445 Error Lockup tool Note 153788.1 : ORA-600/ORA-7445 Error Look-up Tool
GENERIC LINKS
PATCHSET UPDATES (PSU) & CRITICAL PATCH UPDATES (CPU) Note 268895.1 : Oracle Database Server Patchset Information, Versions: 8.1.7 to 11.2.0
Note 1061295.1 : Patch Set Updates – One-off Patch Conflict Resolution
Note 161549.1 : Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms
Note 756671.1 : Oracle Recommended Patches — Oracle Database
Note 742060.1 : Release Schedule of Current Database Releases
Note 161818.1 : Oracle Database (RDBMS) Releases Support Status Summary
COMMON ERRORS
ORA-1578 ORA-1578 The data block indicated was corrupt.  This was a physical corruption, also called a media corruption. The cause is unknown but is most likely external to the database. If ORA-26040 is also signaled, the corruption is due to NOLOGGING or UNRECOVERABLE operations.
ORA-1410 This error is raised when an operation refers to a ROWID in a table for which there is no such row.
The reference to a ROWID may be implicit from a WHERE CURRENT OF clause or directly from a WHERE ROWID=… clause.
ORA-1410 indicates the ROWID is for a BLOCK that is not part of this table.
ORA-8103 The object has been deleted by another user since the operation began; example: another session truncated or dropped the segment while the SQL statement was still active.
If the error is reproducible, following may be the reasons:
a.) The header block has an invalid block type.
b.) The data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header. See dba_objects.data_object_id and compare it to the decimal value stored in the block (field seg/obj).
ORA-8102 An ORA-08102 indicates that there is a mismatch between the key(s) stored in the index and the values stored in the table. What typically happens is the index is built and at some future time, some type of corruption occurs, either in the table or index, to cause the mismatch.
ORA-1498 Generally this is a result of an ANALYZE … VALIDATE … command.
This error generally manifests itself when there is inconsistency in the data/Index block. Some of the block check errors that may be found:-
a.) Row locked by a non-existent transaction
b.) The amount of space used is not equal to block size
c.) Transaction header lock count mismatch.
While support are processing the tracefile it may be worth the re-running the ANALYZE after restarting the database to help show if the corruption is consistent or if it ‘moves’.
Send the tracefile to support for analysis.
If the ANALYZE was against an index you should check the whole object. Eg: Find the tablename and execute:
ANALYZE TABLE xxx VALIDATE STRUCTURE CASCADE;
ORA-1499 An error occurred when validating an index or a table using the ANALYZE command.
One or more entries does not point to the appropriate cross-reference.
ORA-752 or ORA-600 [3020] Media recovery detected a lost write of a data block.  A data block write to storage was lost during normal database operation on the primary database.

This is reporting a lost write during media recovery.

Reference the next article:

Note 1265884.1 : Resolving ORA-00752 or ORA-600 [3020] During Standby Recovery
ORA-26040 Trying to access data in block that was loaded without redo generation using the NOLOGGING/UNRECOVERABLE option.
This Error raises always together with ORA-1578
ORA-600 [12700] Oracle is trying to access a row using its ROWID, which has been obtained from an index.
A mismatch was found between the index rowid and the data block it is pointing to. The rowid points to a non-existent row in the data block. The corruption can be in data and/or index blocks.
ORA-600 [12700] can also be reported due to a consistent read (CR) problem.
ORA-600 [3020] This is called a ‘STUCK RECOVERY’.
There is an inconsistency between the information stored in the redo and the information stored in a database block being recovered.

This error indicates a lost write or a lost change in the database
ORA-600 [4194] A mismatch has been detected between Redo records and rollback (Undo) records.
Oracle is validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block.
This error is reported when the validation fails.
ORA-600 [4193] A mismatch has been detected between Redo records and Rollback (Undo) records.
Oracle is validating the Undo block sequence number in the undo block against the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
ORA-600 [4137] While backing out an undo record (i.e. at the time of rollback) Oracle found a transaction id mismatch indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.
This would indicate a corrupted rollback segment.
ORA-600 [6101] Not enough free space was found when inserting a row into an index leaf block during the application of undo.
ORA-600 [2103] Oracle is attempting to read or update a generic entry in the control file.
If the entry number is invalid, ORA-600 [2130] is logged.
ORA-600 [4512] Oracle is checking the status of transaction locks within a block.
If the lock number is greater than the number of lock entries, ORA-600 [4512] is reported followed by a stack trace, process state and block dump.
This error possibly indicates a block corruption.
ORA-600 [2662] A data block SCN is ahead of the current SCN.
The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN identified by the process that is normally close to the database scn.
If the SCN is less than the dependent SCN then ORA-600 [2662] is signaled.
ORA-600 [4097] Oracle is accessing a rollback segment header to review if a transaction has been committed.
However, the xid given is in the future of the transaction table.
This could be due to a rollback segment corruption issue.
ORA-600 [4000] It means that Oracle has tried to find an undo segment number in the data dictionary and this undo segment number was not found.
ORA-600 [6006] Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
ORA-600[6006] is usually caused by a media corruption problem related to either a lost write to disk or a corruption on disk.
ORA-600 [4552] This assertion is raised because Oracle is trying to unlock the rows in a block, but receive an incorrect block type.
The second argument is the block type received.
ORA-600[6856] Oracle is checking that the row slot that is about to be freed is not already on the free list.
This internal error is raised when this check fails.
ORA-600[13011] During a delete operation Oracle is deleting from a view via an instead-of trigger or an Index organized table and have exceeded a 5000 pass count
ORA-600[13013] During the execution of an UPDATE statement, after several attempts (Arg [a] passcount) Oracle is unable to get a stable set of rows that conform to the WHERE clause.

Note 816784.1 : How to resolve ORA-00600 [13013], [5001]
ORA-600[13030]  
ORA-600[25012] Oracle is trying to generate the absolute file number given a tablespace number and relative file number and cannot find a matching file number or the file number is zero.
ORA-600[25026] Looking up/checking a tablespace invalid tablespace ID and/or rdba found
ORA-600[25027] Invalid tsn and/or relative file number found
ORA-600 [kcbz_check_objd_typ_3] An object block buffer in memory is checked and is found to have the wrong object id. This is most likely due to corruption.
ORA-600 [kdsgrp1] Error may be caused by:

Case 1. A row referenced in an index that does not exist in the table

ORA-1499 may be produced by analyze:

analyze table <table name> validate structure cascade online;
Case 2. An non-existent rowid pointed to by a chained row

Run an export (exp) or Full Table Scan to identify if there is a permanent invalid chained row.
ORA-600[kddummy_blkchk] 

ORA-600[kdblkcheckerror]

ORA-600 [kdbBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktfBlkCheckError]
ORA-600 [ktsBlkChekError]
ORA-600 [ktspBlkCheckError]
ORA-600 [ktfbnBlkCheckError]
ORA-600 [ktuBlkCheckError]
ORA-600 [kdliBlkCheckError]
ORA-600 [kdxdBlkCheckError]
ORA-600 [kdiBlkCheckError]

 ORA-600 [kddummy_blkchk] is for 10g and ORA-600[kdblkcheckerror] for 11g onward.

These errors report a Logical Block Corruption

If the error is raised in a data guard physical standby database, follow the next article:

Note 2821699.1 : Resolving Logical Block Corruption Errors in a Physical Standby Database
ORA-600[ktadrprc-1] Orphan segment or invalid rdba in Index,Table,Partition etc.  Example:  An entry in sys.ind$ does not exist in sys.seg$

Note 136697.1 : “hcheck.sql” Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
ORA-600[ktsircinfo_num1] This exception occurs when there are problems obtaining the row cache information correctly from sys.seg$. In most cases there is no information in sys.seg$.

Note 136697.1 : “hcheck.sql” Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c
ORA-600[qertbfetchbyrowid] This error might be that a row was not found in an Index.  Perform the check in section “Identify TABLE / INDEX Mismatch” in:

Note 836658.1 : Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes
ORA-600[ktbdchk1-bad dscn] This exception is raised when Oracle is performing a sanity check on the dependent SCN and fail.
The dependent scn is greater than the current scn.

参考:Primary Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)

fio测试io,导致磁盘文件系统损坏故障恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：fio测试io,导致磁盘文件系统损坏故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

运行在云平台上的达梦数据库,部署了DMDataWatch(类似oracle的dataguard),在重启主备库进行扩容内存之后,发现两个机器数据库所在磁盘的分区信息,lvm信息全部丢失,通过查看history文件,最终确认在24年9月份做了一次fio的性能测试,但是当时数据没有出问题,一直运行至今,现在重启两个机器系统之后,均重现同样问题,导致主备容灾,备份全备异常.
fio
通过对磁盘进行分析,发现客户说描述的分区信息(从磁盘2T,三个分区data,bak,arch,而且分区大小也一直10:6:4),但是没有发现lvm信息
scan-part

对发现的分区进行恢复,发现达梦数据库相关文件,备份文件,以及arch归档日志
dm-1
dm-2
dm-3
把恢复的数据传输到客户新划分磁盘的达梦数据库服务器上,然后达梦厂商使用备份还原,和原库启动均正常,运气不错,完成了本次的达梦数据故障恢复.
通过这次故障,有几点我们需要引以为戒:
1.千万不要在生产环境做类似磁盘写操作的io性能测试,比如fio,orion等
2.对于有主备架构的系统,尽量不要同时进行维护,在确认一个库正常之后,再操作另外一个库

ORA-742 写丢失常见bug记录

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-742 写丢失常见bug记录

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

我们经常会遇到数据库异常down,然后启动的时候报ORA-00742错误,一般是在recover或者open的过程中遇到

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

SQL> recover database;
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread %d sequence %d block %d
ORA-00312: online log 3 thread 1: '/oradata/shrdh/redo03.log'

关于该错误,Oracle官方解释：由于操作系统或者存储或者Oracle导致redo发生写丢失

[oracle@www.xifenfei.com:/home/oracle]$ oerr ora 742
00742, 00000, "Log read detects lost write in thread %s sequence %s block %s"
// *Cause:  Either a write issued by Oracle was lost by the underlying
//          operating system or storage system or an Oracle internal error
//          occurred.
// *Action: The trace file shows the lost write location. Dump the problematic
//          log file to see whether it is a real lost write. Contact Oracle
//          Support Services.

Oracle官方关于ORA-00742的主要bug有:
ORA-742-BUG-1
ORA-742-BUG-2
由于redo写丢失已经发生,一般发生这种情况,有备份使用备份进行不完全恢复,没有备份考虑强制拉库,如果是dg库问题,可以考虑从主库把日志重新传过去

Oracle 19c 202501补丁（RUs+OJVM）

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：Oracle 19c 202501补丁（RUs+OJVM）

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

19.0.0.0
 Description  Database Update  GI Update  Windows Bundle Patch
 JAN 2025 (19.26.0.0.0) 37260974  37257886 37486199
 OCT2024 (19.25.0.0.0) 36912597  36916690  36878821
 JUL2024 (19.24.0.0.0) 36582781  36582629  36521936
 APR2024 (19.23.0.0.0) 36233263  36233126  36219938
 JAN2024 (19.22.0.0.0) 35943157  35940989  35962832
 OCT2023 (19.21.0.0.0) 35643107  35642822  35681552
 JUL2023 (19.20.0.0.0) 35320081  35319490  35348034
 APR2023 (19.19.0.0.0) 35042068  35037840  35046439
 JAN2023 (19.18.0.0.0) 34765931  34762026  34750795
 Oct2022 (19.17.0.0.0) 34419443  34416665  34468114
 JUL2022 (19.16.0.0.0) 34133642  34130714  34110685
 APR2022 (19.15.0.0.0) 33806152  33803476  33829175
 JAN2022 (19.14.0.0.0) 33515361  33509923  33575656
 OCT2021(19.13.0.0.0) 33192793  33182768  33155330
 JUL2021 (19.12.0.0.0) 32904851  32895426  32832237
 APR2021 (19.11.0.0.0) 32545013  32545008  32409154
 JAN2021 (19.10.0.0.0) 32218454  32226239  32062765
 OCT2020 (19.9.0.0.0) 31771877  31750108  31719903
 JUL2020  (19.8.0.0.0) 31281355  31305339  31247621
 APR2020 (19.7.0.0.0) 30869156  30899722  30901317
 JAN2020 (19.6.0.0.0) 30557433  30501910  30445947
 OCT2019 (19.5.0.0.0) 30125133  30116789  30151705
 JUL2019 (19.4.0.0.0) 29834717  29708769   NA
 APR2019 (19.3.0.0.0) 29517242  29517302   NA
19.0.0.0
 Description  OJVM Update  OJVM + DB Update  OJVM + GI Update
 JAN2025 (19.26.0.0.250121)  37102264  37262172  37262208
 OCT2024 (19.25.0.0.241015)  36878697  36866623  36866740
 JUL2024 (19.24.0.0.240716)  36414915  36522340  36522439
 APR2024 (19.23.0.0.240416)  36199232  36209492  36209493
 JAN2024 (19.22.0.0.240116)  35926646  36031426  36031453
 OCT2023 (19.21.0.0.231017)  35648110  35742413  35742441
 JUL2023 (19.20.0.0.230718)  35354406  35370174  35370167
 APR2023 (19.19.0.0.230418)  35050341  35058163  35058172
 JAN2023 (19.18.0.0.230117)  34786990  34773489  34773504
 OCT2022 (19.17.0.0.221018)  34411846  34449114  34449117
 JUL2022 (19.16.0.0.220719)  34086870  34160831  34160854
 APR2022 (19.15.0.0.220419)  33808367  33859194  33859214
 JAN2022 (19.14.0.0.220118)  33561310  33567270  33567274
 OCT2021 (19.13.0.0.211019)  33192694  33248420  33248471
 JUL2021 (19.12.0.0.210720)  32876380  32900021  32900083
 APR2021 (19.11.0.0.210420)  32399816  32578972  32578973
 JAN2021 (19.10.0.0.210119)  32067171  32126828  32126842
 OCT2020 (19.9.0.0.201020)  31668882  31720396  31720429
 JUL2020 (19.8.0.0.200714)  31219897  31326362  31326369
 APR2020 (19.7.0.0.200414)  30805684  30783543  30783556
 JAN2020 (19.6.0.0.200114)  30484981  30463595  30463609
 OCT2019 (19.5.0.0.191015)  30128191  30133124  30133178
 JUL2019 (19.4.0.0.190716)  29774421  29699079  29699097
 APR2019 (19.3.0.0.190416)  29548437  29621253  29621299

参考:Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)

避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

适用于:

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

本文档的目的是发布一些推荐的修复措施，以期避免 19c 中与数据库性能相关的问题。这也包括一些影响性能的 ORA-600/ORA-7445 及其他错误。

关于 SQL 性能，请参考以下文档:

Document 2773715.1 Things to Consider to Avoid SQL Performance Problems on 19c

适用范围

本文档列出了部分影响数据库性能的已知问题。一些问题的修复包含在数据库发布更新（DBRU）中，但有些尚未包含。

提醒: 数据库发布更新（RU）具有累积性。例如，2021 年 4 月的发布更新补丁包含之前所有发布更新的内容。因此，建议始终使用包含大多数已知问题修复的最新 DBRU（数据库发布更新）。
Patch 33515361 - Database Jan 2022 Release Update (19.14) - Latest

请参考以下文档获取最新的数据库发布更新（DBRU）信息。

Document 2521164.1 Oracle Database 19c Proactive Patch Information

对于尚未包含在发布更新（RU）中的错误，请下载并安装适用于您的数据库版本和操作系统的单独补丁。如果在MOS上找不到适用于您特定版本和操作系统的补丁，请提交服务请求，提供所需补丁的详细信息。请附上已应用补丁的列表（使用 opatch lsinventory -detail 命令），以及您打算应用的其他补丁。

有关自助升级和最佳实践，请参考以下内容:-

Document 1919.2 19c Database Self-Guided Upgrade with Best Practices
Document 555.1 Oracle Database 19c Important Recommended One-off Patches

详细信息

19c 数据库性能已知 Bug 修复列表:

Bug            描述 是否包含在RU? 备注
Document 30329209.8 High wait on row cache mutex after upgrading to 12.2.0.1 and above 是，从 19.8 起 应用 19.8 或以上
Document 31933451.8 High row cache mutex contention 是，从 19.13 起 应用 19.13 或以上
Document 29523216.8 Major performance bug for dc_users row cache 是，从 19.7 起 应用 19.7 或以上
Document 30712670.8 High row cache mutex contention for queries with dblink (dc_props / dc_cdbprops) 是，从 19.10 起 应用 19.10 或以上
Document 30431274.8 High row cache mutex contention (ktatminextsz) – regression of 22909260 是，从 19.7 起 应用 19.7 或以上
Document 29628647.8 High CPU for DESCRIBE command due to contention in dc_users rowcache 是，从 19.10 起 应用 19.10 或以上
Document 32043701.8 row cache lock for sequences in RAC due to S-optimization feature for dc_sequences 否 申请临时性补丁
Document 30489582.8 Hanganalyze trace unable to identify the blocker of “row cache lock” in RAC 是，从 19.10 起 应用 19.10 或以上
Document 30327149.8 GEN0 process in RAC waiting on ktatminextsz while reading rowcache 是，从 19.7 起 应用 19.7 或以上
Document 30720844.8 CLMN process waits on ‘library cache: mutex X’ and/or might cause ORA-600 [kglrfcl_1] 是，从 19.8 起 应用 19.8 或以上
Document 30384121.8 LCK process in RAC holds mutex in kglHandleMessage causing database hang 是，从 19.7 起 应用 19.7 或以上
Document 32356628.8 Huge waits on ‘library cache: mutex X’ with audit enabled for ‘select any table’ privilege 是，从 19.12 起 应用 19.12 或以上
Document 28889389.8 High waits on ‘cursor:mutex X’ after upgrade 是，从 19.10 起 应用 19.10 或以上
Document 31211220.8Document 33163187.8 High version count (cursor leaks) due to BIND_EQUIV_FAILURE mismatchChild Cursor Increase Due To “Bind Mismatch” Even When Using Same Bind Values 被替换是，从 19.14 起 应用 19.14 或以上
Document 34304965.8Document 35778398.8

Document 35925654.8

 [ROW CACHE] 19c Tracking Bug for Row Cache Bug Fixes – RegressedFURTHER FIXES FOR ROW CACHE ON TOP OF 34304965 – Regressed & replaced with

SESSIONS DEADLOCK ON ROW CACHE MUTEX AND SHARED POOL LATCH

 N/AN/A

是，从 19.24 起

 N/AN/A

应用 19.24 或以上
Document 20319830.8 Latch Get and Free Functions Available for Shared Parent-Child Latches 是，从 19.11 起 应用 19.11 或以上
Document 31602782.8 ORA-12850/ORA-12872 or huge waits on ‘cursor: pin S wait on X’ with parallel execution 是，从 19.14 起 应用 19.14 或以上
Document 31753692.8 High waits on ‘cursor: pin S wait on X’ and ‘cursor: mutex X’ with PX_MISMATCH 是，从 19.10 起 应用 19.10 或以上
Document 30293345.8 Waits for latch: MGA Shared Context Latch After Migration to 18c 或以上 是，从 19.8 起 应用 19.8 或以上
Document 30614411.8 Huge delay in LGWR BOC (Broadcast-on-commit) processing in RAC 是，从 19.8 起 应用 19.8 或以上
Document 31827912.8 High waits for ‘log file sync’ & ‘remote write sync’ in RAC ADG with Fast-Start Failover (FSFO) 是，从 19.10 起 应用 19.10 或以上
Document 31176502.8 LGWR Hangs during log switch after Upgrade to 19c in RAC due to Cache Fusion Write Hang 是，从 19.11 起 应用 19.11 或以上
Document 31331038.8 ORA-600 error in ADG SYNC mode on Exadata with PMEMlog 否 如果是 Exadata 申请临时补丁
Document 32249371.8 High ‘log file parallel write’ waits on Exadata due to single HCA bottleneck 是，从 19.13 起 应用 19.13 或以上
Document 32498752.8 ORA-600 [ksu_get_available_pso: numa mismatch] signaled when using parallel LGWR 是，从 19.14 起 应用 19.14 或以上
Document 30978554.8 GC hang on read-mostly object or ORA-481 in RAC 是，从 19.9 起 应用 19.9 或以上
Document 32035536.8 Sessions Blocked by ‘gc current request’ in RAC 是，从 19.11 起 应用 19.11 或以上
Document 28697526.8 Session Hangs On ‘gc cr request’ And Other Sessions Wait On ‘cr request retry’ 是，从 19.9 起 应用 19.9 或以上
Document 32227352.8 High CPU with set role command 是，从 19.11 起 应用 19.11 或以上
Document 31812824.8 Slow performance with set role command 是，从 19.12 起 应用 19.12 或以上
Document 28889730.8 “Insert As Select” or a “Create Table As Select” command consume huge space 是，从 19.4 起 应用 19.4 或以上
Document 32379140.8 LCK process in RAC crashes due to ORA-07445 [kjcvmsn()+128] [SIGSEGV] 是，从 19.12 起 应用 19.12 或以上
Document 30240930.8 Scheduler Jobs Time Classified as Background Instead of Foreground 是，从 19.9 起 应用 19.9 或以上
Document 29932310.8 AWR Report Generation Takes Long time in 19c 是，从 19.10 起 应用 19.10 或以上
Document 31489731.8 ORA-600/ORA-7445 While Shared Pool Memory is Being Freed 是，从 19.18 起 应用 19.18 或以上
Document 31892767.8 Improvement to Temp Space Shrink (Affects on Cloud 19c) 否 申请临时补丁 (仅影响 Cloud 19c)
Document 32465193.8 ORA-4031 Due to high SQL Monitoring allocations in Shared Pool 是，从 19.13 起 应用 19.13 或以上
Document 31820859.8 ORA-4025 Due To 65535 Active Locks Limit Reached On Select NLS_CHARSET_ID 是，从 19.9 起 应用 19.9 或以上
Document 30887989.8 ORA-00001 While Generating AWR Snapshot in non-CDB with Resource Manager enabled 是，从 19.13 起 应用 19.13 或以上
Document 29423227.8 Drop Partition with global indexes hangs on library cache lock 是，从 19.11 起 应用 19.11 或以上
Document 32234161.8 Performance Slow due to High CPU post July 2020 DBRU (19.8) caused by Space Management slave processes (Wnnn) 是，从 19.10 起 应用 19.10 或以上
Document 29454450.8 High waits on “latch: cache buffers chains” in RAC 是，从 19.9 起 应用 19.9 或以上
Document 31563138.8 Securefile mutex waits when many inserts occurring on Securefile compressed LOB 是，从 19.11 起 应用 19.11 或以上
Document 32103628.8 High Latch Free Waits While Flushing Top Segment Statistics in AWR 是，从 19.15 起 应用 19.15 或以上
Document 33123985.8 DBW0 Process Generate Huge Traces With Dumping DBWR Process State After DBRU 19.11 是，从 19.13 起 应用 19.13 或以上
Document 32936537.8 Significant Contention on “library cache: mutex X” While accessing Interval or Auto-List Partitioned table Concurrently 是，从 19.16 起 应用 19.16 或以上
Document 32148419.8 High Row Cache Lock Waits on Alter Table Exchange Partition in RAC Environment 是，从 19.12 起 应用 19.12 或以上
Document 30662963.8Document 34284147.8 High contention for “latch: MGA shared context root latch” When Many Sessions are Logging outHigh contention for “latch: MGA shared context root latch” When Many Sessions are Logging out 被替换是，从 19.17 起 N/A应用 19.17 或以上
Document 32550751.8 ONLY FOR AIX: Performance issues due to MGA related operations in AIX 是，从 19.12 起 应用 19.12 或以上
Document 33352794.8 ONLY FOR AIX: High waits on ‘latch: MGA shared context root latch’ and ‘latch: MGA shared context latch’ even with Fix 32550751 是，从 19.13 起 应用 19.13 或以上
Document 32117253.8 High “enq: RO – fast object reuse” waits & active checkpoint queue latch gets 是，从 19.12 起 应用 19.12 或以上
Document 30710917.8 Cursor: mutex S waits due to High Version Count For SQL Statements using Bind variables and DBLINK From 12.2 是，从 19.14 起 应用 19.14 或以上
Document 33025005.8 Waits on ‘latch: cache buffers chains’ after Database Upgrade from 12.1.0.2 to 19c 是，从 19.15 起 应用 19.15 或以上
Document 31387123.8 High Waits on ‘enq: IV – contention’ observed in RAC Standby environment 是，从 19.13 起 应用 19.13 或以上
Document 32225742.8 Gathering Statistics in Primary DB Results in ORA-4061/ORA-4065 in Secondary DB 是，从 19.13 起 应用 19.13 或以上
Document 32069508.8 High Latch: Cache Buffers Chains Contention in Standby Database 是，从 19.13 起 应用 19.13 或以上
Document 33803836.8 Regression in 19.14 Due to Bug Fix 32119144 是，从 19.18 起 应用 19.18 或以上
Document 33163187.8 High Version Count due To “Bind Mismatch” Even When Using Same Bind Values 是，从 19.14 起 应用 19.14 或以上
Document 32755517.8 High Version count with USER_BIND_PEEK_MISMATCH for SQLs with bind peeking disabled 是，从 19.13 起 应用 19.13 或以上
Document 33121934.8 Library cache lock / load lock / mutex x during connection storm due to update user$ 是，从 19.16 起 应用 19.16 或以上
Document 36587533.8 RESULT CACHE: GLOBAL FLUSH SHOULD ALWAYS CLEAR BYPASS FLAG EVEN IF THE RESULT CACHE IS UNINITIALIZED 是，从 19.24 起 应用 19.24 或以上

常见已知问题

问题 1: 在 19c AWR 报告中缺少表空间级别的 IO 统计数据

解决方案: 该 Bug Document 25416731.8 已在 19.8 版本及以上修复。请应用 19.8 或更高版本，并执行以下步骤。

要在 19.X 版本的 AWR 报告中恢复表空间 IO 统计数据，请以 SYS 身份运行以下命令:

$ sqlplus / as sysdba

exec dbms_workload_repository.modify_table_settings(table_name => ‘WRH$_FILESTATXS’, flush_level => ‘TYPICAL’);
exec dbms_workload_repository.modify_table_settings(table_name => ‘WRH$_DATAFILE’, flush_level => ‘TYPICAL’);
exec dbms_workload_repository.modify_table_settings(table_name => ‘Tempfile Group’, flush_level => ‘TYPICAL’);
exec dbms_workload_repository.modify_table_settings(table_name => ‘WRH$_TEMPSTATXS’, flush_level => ‘TYPICAL’);
exec dbms_workload_repository.modify_table_settings(table_name  => ‘WRH$_TEMPFILE’, flush_level => ‘TYPICAL’);

当新的 AWR 快照生成时，您将开始获得用于检查 IO 性能的表空间 IO 统计数据。

供您参考：如果您在 PDB 层级生成 AWR 快照并且在 AWR 报告中缺少表空间 IO 统计数据，您可能还需要在 PDB 中运行这些命令。

该 Bug Document 34733173.8 从 19.22RU 起被修复了. 如果您应用了补丁 34733173 这些命令就不需要了。

Document 25416731.8 - Bug 25416731 – Tablespace IO Statistics Missing From AWR Report
Document 34733173.8 - Bug 34733173 – Tablespace IO Stats and File IO Stats Data Must Be Included in AWR Reports From Oracle 19C, 21C and 23ai
Document 3008056.1 - AWR Report Under File IO Stats Shows No Data Exists For This Section Of The Report.

问题 2: SQL 子游标的高版本计数持续超过 1024

解决方案：请参考以下文档以控制 SQL 子游标的版本计数，该计数持续超过 1024。

Document 2431353.1 High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance

问题 3: SQL 语句中列数过多导致的高 CPU 使用率。short stack 显示在 qosdGetOptDir、qosdInitDirCtx 和 qosdUpdExprExecStatsRws 上存在函数旋转。可能会出现高锁等待，包括 RAC 中的 GES 锁。

解决方案：在系统级别设置 _column_tracking_level=1。该参数是动态的。这样做是为了避免大量列使用跟踪，这可能会导致更高的 CPU 使用率。默认值在 11.2 和 12.1 中为 1，从 12.2 开始更改为 21 及以上。

问题 4: 如果数据库从 11.2 升级到 19c，那么从 12c 开始，LGWR 的架构发生了变化，采用了并行自适应 LGWR。这可能会导致 LGWR 吞吐量变慢，有时会在 19c 中导致前台会话出现“log file sync”等待。这是由于并行 LGWR 的自适应行为与串行 LGWR 之间存在一些缺陷所导致的。

解决方案：在生产环境之前，在用户验收测试（UAT）中评估新的 LGWR 架构（默认启用）。要使用旧的 LGWR 架构，请设置以下参数：

_use_single_log_writer=TRUE /* default value: ADAPTIVE */

注意：这不是一个动态参数。它需要重启数据库。

问题 5: 在19c数据库中进行分区维护时，高库缓存锁等待。

解决方案： Document 2619066.1 High Library Cache Lock Waits After Upgrading To 19C During Partition Index Maintenance

请参阅以下文档以排查与库缓存相关的等待问题：-

Document 444560.1 Troubleshooting Library Cache: Lock, Pin and Load Lock
Document 1353015.1 How to Identify Hard Parse Failures Causing Library Cache contention
Document 2746493.1 How To Trace Overall Library Cache Objects Invalidation Happening At Particular Period

19c 最佳实践

1. Database Testing

Oracle Real Application Testing 选项使您能够在进行生产环境升级之前，在开发或用户验收测试（UAT）中对 Oracle 数据库进行真实世界的测试。通过捕获生产工作负载并在生产部署之前评估系统更改对这些工作负载的影响，Oracle 实际应用测试最大限度地降低了与升级后系统更改相关的不稳定性风险。SQL 性能分析器和数据库重放是 Oracle 实际应用测试的关键组件。.

Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/ratug/introduction-to-oracle-database-testing.html
Document 1464274.1 Primary Note for Real Application Testing Option

2. 使用 Performance Hub

EM Express 的性能中心（Performance Hub）功能提供了一个活跃报告，汇总了指定时间段内的所有性能数据。该报告是完全交互式的，其内容保存在 HTML 文件中，您可以通过网页浏览器离线访问。有关性能中心的更多信息，请参考以下教程。

使用 EM Express 的性能中心（Perf Hub）: https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-monitor-perf/index.html?opt-release-19c#UsePerformanceHub
不使用 EM Express 的性能中心（Perf Hub）: Document 2436566.1 Monitoring Database Performance Using Performance Hub Report

3. 实时监控 ADDM

一种预测工具，用于主动预见 19c 生产环境中的性能问题，并采取纠正措施以避免任何系统停机情况。

Document 2763576.1 Proactively Detecting Database Performance Problem Using Real-Time ADDM

4. 下载最新的 ORAchk / EXAchk

建议下载并安装最新的 AHF 或 ORAchk/EXAchk，这可以用于检查任何异常并采取纠正措施。

Document 2550798.1 Autonomous Health Framework (AHF) – Including TFA and ORAchk/EXAChk

5. 安装 OS Watcher

建议在升级后安装最新版本的 OS Watcher，以便在需要时收集与操作系统相关的信息。

Document 301137.1 OS Watcher User Guide
Document 461053.1 OS Watcher Analyzer User Guide

有关数据库性能的更多信息：-

Document 402983.1 Primary Note: Database Performance Overview
Document 1306791.2 Information Center: Oracle Exadata Database Machine

免责声明: 为了避免在打补丁或打完补丁后出现任何问题，建议在 UAT 环境中应用上述补丁并进行验证，然后再应用到生产环境。本文件将在每个季度（在发布季度 RU/RUR 期间）与新的候选补丁进行修订。

转载：避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)

Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]

Applies to:
Oracle Database – Enterprise Edition – Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service – Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) – Version N/A and later
Oracle Cloud Infrastructure – Database Service – Version N/A and later
Oracle Database Cloud Exadata Service – Version N/A and later
IBM AIX on POWER Systems (64-bit)

Description
Oracle 12c introduces a new default feature of using multiple LGWRs which may lead to DEADLOCK / Database Hang or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery on IBM AIX and potentially on HPUX Itanium 64bit.
The database may become unusable and fail to be OPEN.

Occurrence
This issue is specific to RDBMS version 12c (12.1.0.1 or 12.1.0.2) where the new default feature of using multiple LGWRs is introduced.
It affects databases on IBM AIX and potentially on HPUX Itanium 64bit

Symptoms
ORACLE on IBM AIX or HPUX Itanium 64bit with RDBMS Version 12c.

DEADLOCK or ORA-742 “Log read detects lost write” or ORA-600 [kcrfrgv_nextlwn_scn] during instance OPEN or ORA-600 [krr_process_read_error_2] during Recovery caused by bug 21915719.

PMON may terminate the instance while extensive block recovery is being performed.

A DEADLOCK example is with LG0[n] waiting on ‘LGWR worker group ordering’. Example from a System State Dump trace file:

PROCESS 18: LG01
SO: 0x7000101f95ad720, type: 4, owner: 0x7000101f84195f8, flag: INIT/-/-/0x00
if: 0x3 c: 0x3
   proc=0x7000101f84195f8, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
  (session) sid: 865 ser: 1 trans: 0x0, creator: 0x7000101f84195f
 Current Wait Stack:
   0: waiting for 'LGWR worker group ordering'
      lwn_id=0x58, phase=0x1, =0x0
      wait_id=4947 seq_num=4948 snap_id=1
      wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
      wait times: max=infinite, heur=13 min 21 sec
      wait counts: calls=1 os=267
      in_wait=1 iflags=0x5a0
  There is at least one session blocking this session.
    Dumping 1 direct blocker(s):
      inst: 1, sid: 817, ser: 1
    Dumping final blocker:
      inst: 1, sid: 817, ser: 1
  There are 730 sessions blocked by this session.
.
.
PROCESS 17: LG00
SO: 0x7000101f85bcc60, type: 4, owner: 0x7000101f93eeb20, flag: INIT/-/-/0x00
if: 0x3 c: 0x3
   proc=0x7000101f93eeb20, name=session, file=ksu.h LINE:13590 ID:, pg=0
conuid=0
  (session) sid: 817 ser: 1 trans: 0x0, creator: 0x7000101f93eeb20
  ksuxds FALSE at location: 0
  service name: SYS$BACKGROUND
  Current Wait Stack:
   0: waiting for 'LGWR worker group ordering'
      lwn_id=0x56, phase=0x1, =0x0
      wait_id=1630680 seq_num=57841 snap_id=1
      wait times: snap=13 min 21 sec, exc=13 min 21 sec, total=13 min 21 sec
      wait times: max=infinite, heur=13 min 21 sec
      wait counts: calls=2 os=268
      in_wait=1 iflags=0x15a0
  There is at least one session blocking this session.
    Dumping 1 direct blocker(s):
      inst: 1, sid: 865, ser: 1
    Dumping final blocker:
      inst: 1, sid: 865, ser: 1

The instance may fail to OPEN with errors ORA-600 [kcrfrgv_nextlwn_scn] and/or ORA-600 [krr_process_read_error_2]:

Recovery Session Failed with:

ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krr_process_read_error_2],


Alter database open fails with:

ORA-00600: internal error code, arguments: [kcrfrgv_nextlwn_scn] .....
ORA-600 signalled during: ALTER DATABASE OPEN...

Workaround
Disable the new feature of multiple LGWR worker processes by proactively setting _use_single_log_writer=true.

Setting _use_single_log_writer = true is a safe workaround; it is the behavior before 12c where multiple LGWR worker groups were not available. 

ALTER SYSTEM SET "_use_single_log_writer"=TRUE SID='*' SCOPE=SPFILE;
-- Restart the database or all instances of the RAC database

Note that while _use_single_log_writer=true is not set, then error ORA-600 [kcrfrgv_nextlwn_scn] might be produced avoiding the database to OPEN. Once the problem is introduced, _use_single_log_writer=true may not fix it. _use_single_log_writer = true prevents inconsistencies in the redo log to be introduced which causes that error.
If the parameter does not help, because the problem was already introduced when _use_single_log_writer=true had not been proactively set, then Point in Time Recovery (PITR) or Flashback Database are the options to recover from this situation.
参考:ALERT: Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2] (Doc ID 1957710.1)

ORA-600 ktuPopDictI_1恢复

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-600 ktuPopDictI_1恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库启动报ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4]错误

[oracle@ora19c:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 20 21:42:28 2025
Version 19.24.0.0.0

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

Connected to an idle instance.

sys@ORA19C 21:38:22> startup
ORACLE instance started.

Total System Global Area  763359928 bytes
Fixed Size                  9183928 bytes
Variable Size             457179136 bytes
Database Buffers          289406976 bytes
Redo Buffers                7589888 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Process ID: 3254475
Session ID: 410 Serial number: 22754

数据库alert日志报错

2025-01-20T21:38:30.411924+08:00
ALTER DATABASE OPEN
2025-01-20T21:38:30.437769+08:00
Smart fusion block transfer is disabled:
  instance mounted in exclusive mode.
2025-01-20T21:38:30.445071+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2025-01-20T21:38:30.445125+08:00
Crash Recovery excluding pdb 3 which was cleanly closed.
2025-01-20T21:38:30.445172+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
Endian type of dictionary set to little
2025-01-20T21:38:30.459107+08:00
LGWR (PID:3254425): STARTING ARCH PROCESSES
2025-01-20T21:38:30.466458+08:00
TT00 (PID:3254477): Gap Manager starting
Starting background process ARC0
2025-01-20T21:38:30.474126+08:00
ARC0 started with pid=39, OS id=3254479 
2025-01-20T21:38:30.484228+08:00
LGWR (PID:3254425): ARC0: Archival started
LGWR (PID:3254425): STARTING ARCH PROCESSES COMPLETE
2025-01-20T21:38:30.484325+08:00
ARC0 (PID:3254479): Becoming a 'no FAL' ARCH
ARC0 (PID:3254479): Becoming the 'no SRL' ARCH
2025-01-20T21:38:30.495886+08:00
Redo log for group 3, sequence 447 is not located on DAX storage
Thread 1 opened at log sequence 447
  Current log# 3 seq# 447 mem# 0: /data/oradata/ORA19C/redo03.log
Successful open of redo thread 1
2025-01-20T21:38:30.512816+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
Undo initialization recovery: Parallel FPTR complete: start:2947972792 end:2947972831 diff:39 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 2947972791 end: 2947972831 diff: 40 ms (0.0 seconds)
[3254475] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 2947972831 end: 2947972933 diff: 102 ms (0.1 seconds)
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Incident details in: /data/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_38705/ora19c_ora_3254475_i38705.trc
2025-01-20T21:38:31.482586+08:00
TMON (PID:3254467): STARTING ARCH PROCESSES
Starting background process ARC1
2025-01-20T21:38:31.491744+08:00
ARC1 started with pid=41, OS id=3254483 
Starting background process ARC2
2025-01-20T21:38:31.500274+08:00
ARC2 started with pid=42, OS id=3254485 
Starting background process ARC3
2025-01-20T21:38:31.508426+08:00
ARC3 started with pid=43, OS id=3254487 
TMON (PID:3254467): ARC1: Archival started
TMON (PID:3254467): ARC2: Archival started
TMON (PID:3254467): ARC3: Archival started
TMON (PID:3254467): STARTING ARCH PROCESSES COMPLETE
2025-01-20T21:38:31.715480+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2025-01-20T21:38:31.798619+08:00
Errors in file /data/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_3254475.trc:
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
2025-01-20T21:38:31.798666+08:00
Errors in file /data/app/oracle/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_3254475.trc:
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
Incident details in: /data/app/oracle/diag/rdbms/ora19c/ora19c/incident/incdir_38706/ora19c_ora_3254475_i38706.trc
opiodr aborting process unknown ospid (3254475) as a result of ORA-603
2025-01-20T21:38:32.356148+08:00
ORA-603 : opitsk aborting process
License high water mark = 1
USER(prelim) (ospid: 3254475): terminating the instance due to ORA error 600

分析trace信息

[TOC00001]
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 38706 (ORA 603) ========

*** 2025-01-20T21:38:31.823904+08:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=1h50ks4ncswfn) -----
ALTER DATABASE OPEN
[TOC00003-END]

[TOC00004]
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95         call     kgdsdst()            7FFEA4EB7360 000000002
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 000000000
ksedst()+58          call     ksedst1()            000000000 000000001
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbkedDefDump()+2434  call     ksedst()             000000000 000000001 ?
7                                                  7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
ksedmp()+577         call     dbkedDefDump()       000000003 000000002
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgexPhaseII()+2092  call     ksedmp()             0000003EB 000000002 ?
                                                   7FFEA4EB15B0 ? 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgexProcessError()  call     dbgexPhaseII()       7F2A059ED6D8 7F2A002BF148
+1871                                              7FFEA4EB8E00 7FFEA4EB16C8 ?
                                                   000000000 ? 000000000 ?
dbgePostErrorKGE()+  call     dbgexProcessError()  7F2A059ED6D8 7F2A002BF148
1851                                               000000001 000000000
                                                   000000000 ? 000000000 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   7F2A05A2D9C0 7F2A058D0050
71                                                 00000025B 000000000 ?
                                                   000000000 ? 000000000 ?
kgeade()+339         call     dbkePostKGE_kgsf()   7F2A05A2D9C0 7F2A058D0050
                                                   00000025B 000000000 ?
                                                   000000000 ? 000000000 ?
kgefecl()+184        call     kgeade()             7F2A05A2D9C0 ? 7F2A05A2DC08 ?
                                                   7F2A058D0050 ? 00000025B ?
                                                   000000000 000000000
adbdrv_options()+48  call     kgefecl()            7F2A05A2D9C0 7F2A058D0050
548                                                000000444 000000001 ?
                                                   014971F9C ? 014973858 ?
opiexe()+31984       call     adbdrv_options()     000000000 7F2A058D0050 ?
                                                   000000444 ? 000000001 ?
                                                   014971F9C ? 014973858 ?
opiosq0()+4560       call     opiexe()             000000004 7F2A058D0050 ?
                                                   7FFEA4EC16D0 000000001 ?
                                                   014971F9C ? 014973858 ?
kpooprx()+287        call     opiosq0()            000000003 7F2A058D0050 ?
                                                   7F2A05A2D9C0 ? 0000000A4
                                                   000000000 000000023
kpoal8()+838         call     kpooprx()            7FFEA4EC5824 7FFEA4EC2F40
                                                   000000013 000000001 000000000
                                                   0000000A4
opiodr()+1253        call     kpoal8()             00000005E 000000026
                                                   7FFEA4EC5820 000000001 ?
                                                   000000000 ? 0000000A4 ?
ttcpip()+1216        call     opiodr()             00000005E 000000026
                                                   7FFEA4EC5820 ? 000000000
                                                   000000000 ? 0000000A4 ?
opitsk()+1916        call     ttcpip()             7F2A05A57B30 ? 000000026 ?
                                                   7FFEA4EC5820 000000000 ?
                                                   7FFEA4EC5280 7FFEA4EC5A80 ?
opiino()+936         call     opitsk()             000000000 000000000
                                                   7FFEA4EC5820 ? 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opiodr()+1253        call     opiino()             00000003C 000000004
                                                   7FFEA4EC7418 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opidrv()+1067        call     opiodr()             00000003C 000000004
                                                   7FFEA4EC7418 ? 000000000
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
sou2o()+165          call     opidrv()             00000003C 000000004
                                                   7FFEA4EC7418 000000000 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
opimai_real()+422    call     sou2o()              7FFEA4EC73F0 00000003C
                                                   000000004 7FFEA4EC7418
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
ssthrdmain()+417     call     opimai_real()        000000000 7FFEA4EC7C08
                                                   000000004 ? 7FFEA4EC7418 ?
                                                   7FFEA4EC5280 ? 7FFEA4EC5A80 ?
main()+256           call     ssthrdmain()         000000000 000000002
                                                   7FFEA4EC7C08 000000001
                                                   000000000 7FFEA4EC5A80 ?
__libc_start_main()  call     main()               000000002 7FFEA4EC7E58
+243                                               7FFEA4EC7C08 ? 000000001 ?
                                                   000000000 ? 7FFEA4EC5A80 ?
_start()+46          call     __libc_start_main()  000DFEF50 000000002
                                                   7FFEA4EC7E58 00746DD60 ?
                                                   000000000 ? 7FFEA4EC5A80 ?
[TOC00004-END]


[TOC00005]
--------------------- Binary Stack Dump ---------------------

对启动过程进行跟踪,确认报错具体位置

PARSING IN CURSOR #140457326129448 len=45 dep=1 tim=11537999627952 hv=2164165332 ad='69329ae8'sqlid='8su8qaa0gx2qn'
select dataobj# from obj$ where name like :1
END OF STMT
PARSE #140457326129448:c=15,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999627952
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(07) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=07  flg=05
  value="I_UNDO2"
EXEC #140457326129448:c=51,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628044
FETCH #140457326129448:c=175,e=175,p=0,cr=11,cu=0,mis=0,r=1,dep=1,og=4,plh=1478545678,tim=11537999628226
STAT #140457326129448 id=1 cnt=1 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=11  card=1)'
STAT #140457326129448 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX SKIP SCAN I_OBJ2 (cr=10 time=177 us cost=26 size=0 card=1)'
CLOSE #140457326129448:c=40,e=40,dep=1,type=1,tim=11537999628290
PARSE #140457326129448:c=5,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628302
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(09) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=09  flg=05
  value="UNDOHIST$"
EXEC #140457326129448:c=31,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628342
WAIT #140457326129448: nam='db file sequential read' ela= 6 file#=1 block#=243 blocks=1 obj#=18 tim=11537999628370
FETCH #140457326129448:c=33,e=33,p=1,cr=5,cu=0,mis=0,r=1,dep=1,og=4,plh=1478545678,tim=11537999628381
CLOSE #140457326129448:c=4,e=4,dep=1,type=3,tim=11537999628409
PARSE #140457326129448:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628430
BINDS #140457326129448:

 Bind#0
  oacdty=01 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=20 fl2=0000 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=7fbec507eb20  bln=32  avl=11  flg=05
  value="I_UNDOHIST1"
EXEC #140457326129448:c=39,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999628485
WAIT #140457326129448: nam='db file sequential read' ela= 649 file#=1 block#=78527 blocks=1 obj#=37 tim=11537999629346
WAIT #140457326129448: nam='db file sequential read' ela= 747 file#=1 block#=78505 blocks=1 obj#=37 tim=11537999630122
WAIT #140457326129448: nam='db file sequential read' ela= 628 file#=1 block#=23459 blocks=1 obj#=37 tim=11537999630775
WAIT #140457326129448: nam='db file sequential read' ela= 470 file#=1 block#=78552 blocks=1 obj#=37 tim=11537999631268
WAIT #140457326129448: nam='db file sequential read' ela= 502 file#=1 block#=92776 blocks=1 obj#=37 tim=11537999631797
WAIT #140457326129448: nam='db file sequential read' ela= 512 file#=1 block#=78585 blocks=1 obj#=37 tim=11537999632331
WAIT #140457326129448: nam='db file sequential read' ela= 538 file#=1 block#=78557 blocks=1 obj#=37 tim=11537999632892
WAIT #140457326129448: nam='db file sequential read' ela= 520 file#=1 block#=19641 blocks=1 obj#=37 tim=11537999633440
WAIT #140457326129448: nam='db file sequential read' ela= 502 file#=1 block#=23486 blocks=1 obj#=37 tim=11537999633971
WAIT #140457326129448: nam='db file sequential read' ela= 465 file#=1 block#=23504 blocks=1 obj#=37 tim=11537999634461
WAIT #140457326129448: nam='db file sequential read' ela= 656 file#=1 block#=23488 blocks=1 obj#=37 tim=11537999635141
WAIT #140457326129448: nam='db file sequential read' ela= 429 file#=1 block#=23501 blocks=1 obj#=37 tim=11537999635596
WAIT #140457326129448: nam='db file sequential read' ela= 621 file#=1 block#=92686 blocks=1 obj#=37 tim=11537999636240
WAIT #140457326129448: nam='db file sequential read' ela= 1027 file#=1 block#=92678 blocks=1 obj#=37 tim=11537999637307
WAIT #140457326129448: nam='db file sequential read' ela= 494 file#=1 block#=92680 blocks=1 obj#=37 tim=11537999637831
WAIT #140457326129448: nam='db file sequential read' ela= 515 file#=1 block#=92682 blocks=1 obj#=37 tim=11537999638377
WAIT #140457326129448: nam='db file sequential read' ela= 559 file#=1 block#=92684 blocks=1 obj#=37 tim=11537999638975
WAIT #140457326129448: nam='db file sequential read' ela= 478 file#=1 block#=92683 blocks=1 obj#=37 tim=11537999639502
WAIT #140457326129448: nam='db file sequential read' ela= 402 file#=1 block#=92687 blocks=1 obj#=37 tim=11537999639951
WAIT #140457326129448: nam='db file sequential read' ela= 465 file#=1 block#=92691 blocks=1 obj#=37 tim=11537999640453
WAIT #140457326129448: nam='db file sequential read' ela= 629 file#=1 block#=92694 blocks=1 obj#=37 tim=11537999641112
WAIT #140457326129448: nam='db file sequential read' ela= 507 file#=1 block#=109829 blocks=1 obj#=37 tim=11537999641651
WAIT #140457326129448: nam='db file sequential read' ela= 467 file#=1 block#=109831 blocks=1 obj#=37 tim=11537999642150
WAIT #140457326129448: nam='db file sequential read' ela= 525 file#=1 block#=109833 blocks=1 obj#=37 tim=11537999642695
WAIT #140457326129448: nam='db file sequential read' ela= 823 file#=1 block#=109837 blocks=1 obj#=37 tim=11537999643540
WAIT #140457326129448: nam='db file sequential read' ela= 553 file#=1 block#=109834 blocks=1 obj#=37 tim=11537999644111
WAIT #140457326129448: nam='db file sequential read' ela= 509 file#=1 block#=109835 blocks=1 obj#=37 tim=11537999644650
FETCH #140457326129448:c=1777,e=16184,p=27,cr=38,cu=0,mis=0,r=0,dep=1,og=4,plh=1478545678,tim=11537999644675
2025-01-20T21:40:02.951778+08:00
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

<error barrier> at 0x7ffe9566b3c0 placed dbsdrv.c@5141
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
<error barrier> at 0x7ffe9566b3c0 placed dbsdrv.c@5141
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []
2025-01-20T21:40:04.951374+08:00
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [ktuPopDictI_1], [4], [], [], [], [], [], [], [], [], [], []

通过上述定位确认是select dataobj# from obj$ where name like :1这个sql在查询记录时报错,通过一些技巧绕过该sql,实现数据库正常open

impdp导入数据丢失sys授权问题分析

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：impdp导入数据丢失sys授权问题分析

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在使用expdp/impdp迁移的过程中,偶尔会遇到用户中关于sys对象的授权丢失导致不少pl/sql程序无效,通过测试重现sys授权丢失现象
创建用户并进行sys对象授权给该用户

C:\Users\XFF>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:04:22 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 xff identified by oracle;

User created.

SQL> grant resource,connect to xff;

Grant succeeded.

SQL> grant select on sys.obj$ to xff;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to xff;

Grant succeeded.

SQL> grant select on sys.v_$session to xff;

Grant succeeded.
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

使用exp导出数据

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

Export: Release 11.2.0.4.0 - Production on Sun Jan 19 12:05:35 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=xff
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
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
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 12:05:37 2025 elapsed 0 00:00:02

使用impdp导入数据

C:\Users\XFF>impdp "'/ as sysdba'" dumpfile=xff.dmp remap_schema=xff:nxff

Import: Release 11.2.0.4.0 - Production on Sun Jan 19 12:06:22 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=xff:nxff
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
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sun Jan 19 12:06:23 2025 elapsed 0 00:00:00

验证用户的权限

C:\Users\XFF>sqlplus xff/oracle

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 19 12:09:21 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> show user;
USER is "XFF"
SQL> select count(1) from v$session;

  COUNT(1)
----------
        26

SQL> select count(1) from sys.obj$;

  COUNT(1)
----------
     90656

SQL> desc sys.dbms_lock
PROCEDURE ALLOCATE_UNIQUE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKNAME                       VARCHAR2                IN
 LOCKHANDLE                     VARCHAR2                OUT
 EXPIRATION_SECS                NUMBER(38)              IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION CONVERT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN
 TIMEOUT                        NUMBER                  IN     DEFAULT
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
FUNCTION RELEASE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             NUMBER(38)              IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
FUNCTION REQUEST RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCKHANDLE                     VARCHAR2                IN
 LOCKMODE                       NUMBER(38)              IN     DEFAULT
 TIMEOUT                        NUMBER(38)              IN     DEFAULT
 RELEASE_ON_COMMIT              BOOLEAN                 IN     DEFAULT
PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

SQL> conn nxff/oracle
Connected.
SQL> desc sys.dbms_lock
ERROR:
ORA-04043: object sys.dbms_lock does not exist


SQL> select count(1) from sys.obj$;
select count(1) from sys.obj$
                         *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select count(1) from v$session;
select count(1) from v$session
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

确认通过impdp迁移过去的nxff用户没有之前xff用户里面sys授权的对象的访问权限.通过sqlfile查看expdp导出的dmp文件中ddl内容,确认确实没有sys部分的授权
lost-sys-grant

出现这个问题的原因是由于expdp不会导出sys中对象,所以就丢失了这部分授权信息,可以通过获取语句获取权限,然后执行补全

SQL> select 'grant ' || privilege || ' on ' ||'"'||table_name ||'"'||
  2        ' to ' || grantee || ';' "GRANTS"
  3   from dba_tab_privs
  4  where owner = 'SYS' and privilege not in ('READ', 'WRITE')
  5    and grantee in ('XFF')
  6  order by 1;

GRANTS
--------------------------------------------------------------------------------
grant EXECUTE on "DBMS_LOCK" to XFF;
grant SELECT on "OBJ$" to XFF;
grant SELECT on "V_$SESSION" to XFF;

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

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在好几次的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)工具性能降低分析参考

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

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

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

适用于:

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)