分类目录归档:数据库

Oracle 19c 202501补丁(RUs+OJVM)-19.26

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)

发表在 Oracle安装升级 | 标签为 , , , , , | 评论关闭

避免 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)

发表在 Oracle | 标签为 | 评论关闭

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)

发表在 Oracle | 标签为 , , | 评论关闭