Skip to content

Oracle 排障配置与调整

 

这篇文章是为更好地了解增量检查点（Checkpoint），并对检查点（Checkpoint）优化所用的下列四个初始化参数进行了描述：

–  FAST_START_MTTR_TARGET

–  LOG_CHECKPOINT_INTERVAL

–  LOG_CHECKPOINT_TIMEOUT

–  LOG_CHECKPOINTS_TO_ALERT

此外，本文档还介绍了如何解释和处理检查点错误：ALERT<sid>.LOG 文件中报告的“Checkpoint not Complete（检查点未完成）” 和“Cannot Allocate New Log（无法分配新日志）”。

 

 

 

1.  什么是检查点？

检查点是一种将内存中的已修改数据块与磁盘上的数据文件进行同步的数据库事件。通过Checkpoint Oracle 确保了被 transaction 修改过数据可以被同步
至磁盘。Oracle transaction 提交的时候不会将已修改数据块同步写入磁盘上。

检查点有两个用途：(1) 确保数据一致性，和 (2) 实现更快的数据库恢复。如何更快地恢复？由于直至检查点生成时所有的数据库更改均已记录在数据文件
中，因此无需再应用先于检查点的 redo 日志条目。
检查点必须确保高速缓存中所有已修改的缓冲数据均已切实写入到相应的数据文件中，以避免在发生崩溃（实例或磁盘故障）时可能会出现的数据丢失。
 Oracle 只有在特定条件下才会将脏缓存写入磁盘：

- shadow process 需要扫描的数据块个数超过 db_block_buffers 参数的四分之一。
 - 每三秒钟。
 - 生成一个检查点时。

检查点通过五种类型的事件来实现：

- 每次切换 redo 日志文件时。
 - 达到 LOG_CHECKPOINT_TIMEOUT 的延迟时。
 - 当前 redo 日志文件中已存在了大小为 （LOG_CHECKPOINT_INTERVAL* OS块的大小（bytes））的数据。
 - 直接由 ALTER SYSTEM SWITCH LOGFILE 命令实现。
 - 直接使用 ALTER SYSTEM CHECKPOINT 命令实现。
 在检查点期间会发生以下操作：
 -  DBWR 将缓冲区缓存中所有已修改的数据库块写回到数据文件中，
 -  检查点进程 (ckpt) 更新所有数据文件的文件头，以反映上一个检查点发生的时间 (SCN)

2. 检查点和性能

检查点的优化常常会使数据库管理员左右为难。频繁的检查点会实现更快的数据库恢复，但也会导致数据库性能降低。那么，DBA 如何解决这一问题呢？

根据数据库中的数据文件数量，检查点将会是一种高度占用资源的操作，因为所有数据文件头在检查点期间都会被冻结。关于检查点的频率设置，需要对性能进行权衡。
检查点频率越高，就能在数据库崩溃后更快地实现恢复。这也是为什么一些不太能忍受意外系统停机的客户现场常常会选择此选项的原因。但是，在很多情况下，频繁
的检查点可能会导致性能降低，这使得上述观点并不能完全站稳脚跟。 我们假设数据库已启动，且有 95% 的时间处于运行状态，剩下 5% 未运行时间是由于出现偶发
的实例崩溃或硬件故障，需要进行数据库恢复。对于大多数的客户现场而言，优化 95% 的性能相比于极少的 5% 停机时间要更具意义。

本文档假设性能是您的首要考虑事项，并由此给出相应的建议。因此，您的目标是通过优化尽量减少检查点的频率。

优化检查点涉及到下面四个关键初始化参数：

-  FAST_START_MTTR_TARGET
 -  LOG_CHECKPOINT_INTERVAL
 -  LOG_CHECKPOINT_TIMEOUT
 -  LOG_CHECKPOINTS_TO_ALERT

下面将详细讨论这些参数。

同时，还针对alert日志中出现的“checkpoint not complete”消息给出了处理建议，这些消息说明redo 日志和检查点需要优化。

3. 与增量检查点相关的参数 注意：日志文件切换将始终覆盖由以下参数引起的检查点。

 

  • FAST_START_MTTR_TARGET
自 Oracle 9i 以来，FAST_START_MTTR_TARGET 参数已成为优化增量检查点目标的首选方法。通过 FAST_START_MTTR_TARGET，您可以指定数据库执行单实例的崩溃恢
复所要花费的秒数。基于内部统计信息，增量检查点会自动调整检查点目标，以满足 FAST_START_MTTR_TARGET 的要求。
 V$INSTANCE_RECOVERY.ESTIMATED_MTTR 显示当前预计的平均恢复时间 (MTTR)（以秒为单位）。即使未指定 FAST_START_MTTR_TARGET，也同样会显示此值。
 V$INSTANCE_RECOVERY.TARGET_MTTR 显示由系统强制执行的有效 MTTR 目标（以秒为单位）。
 V$MTTR_TARGET_ADVICE 显示在当前的 MTTR 设置下由当前的工作负载产生的 I/O 数量，以及在其他 MTTR 设置下将由当前的工作负载产生的预计 I/O 数量。此视图
可帮助用户在运行时性能和设置 FAST_START_MTTR_TARGET 以实现快速恢复之间进行权衡。


  • LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_INTERVAL 参数指定增量检查点目标应滞后于当前日志尾的最大 redo 块数量。
 如果指定了 FAST_START_MTTR_TARGET，就不应设置 LOG_CHECKPOINT_INTERVAL 或将其设置为 0。在大多数 Unix 系统上，操作系统块大小都是 512 字节。
 也 就是说，将 LOG_CHECKPOINT_INTERVAL 的值设置为 10,000 就意味着增量检查点目标相对于当前日志尾的滞后不得超过 5,120,000 (5M) 字节。以此计算，如果
 redo 日志的大小为 20M，则会对每个日志产生 4 个检查点。

LOG_CHECKPOINT_INTERVAL 会影响检查点的发生时间，这意味着应特别注意此参数的设置，保持其随 redo 日志文件的大小变化而更新。检查点的频率是影响数据库从
意外故障中恢复所需时间的因素之一。检查点之间的间隔越长，则在发生系统崩溃时，数据库恢复所需的 时间就越长。检查点间隔越短意味着数据库的恢复速度越快，
但是代价是检查点操作会消耗更多的资源。

此参数还会影响在恢复的前滚阶段期间完成数据库恢复操作所需的时间。实际的恢复时间取决于此时间，以及其他因素，例如故障类型（实例或系统崩溃、介质故障等）
以及需要应用的归档 redo 日志数量。


  • LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_TIMEOUT 参数指定增量检查点目标应滞后于当前日志尾的最长秒数。
 换句话说，它指定缓冲区缓存中的脏缓存可以保持脏状态的时间。
 检查点频率影响数据库从意外故障中恢复所需的时间。检查点之间的间隔越长，数据库恢复所需的时间就越多。

Oracle 建议使用 LOG_CHECKPOINT_INTERVAL 而不是 LOG_CHECKPOINT_TIMEOUT 来控制检查点间隔，后者会每“n”秒启动一次检查点，而不管事务频率。这可能会导致
在事务量变化的情况下出现不必要的检查点。只要可能，就必须避免不必要的检查点，以实现最佳性能。

许多人会有这样一种误解：将 LOG_CHECKPOINT_TIMEOUT 设置为给定值之后，系统就会按该间隔启动日志切换，从而启用用于stand-by数据库配置的恢复窗口。日志切
换会引起检查点，但检查点并不会引起日志切换。引起日志切换的唯一方式是使用 ALTER SYSTEM SWITCH LOGFILE 进行手动操作或重新调节 redo 日志大小，以引起
更为频繁的切换。这由操作系统块而非时间间隔控制。在线 redo 日志的大小对性能和恢复至关重要。



  • LOG_CHECKPOINTS_TO_ALERT
通过 LOG_CHECKPOINTS_TO_ALERT，您可以将检查点记录到alert日志中。

这样做有助于确定检查点是否按所需频率发生。
 在 Oracle9i 之前，此参数为静态参数。
 Oracle 通常建议将此参数设置为 TRUE，因为开销很小，可以忽略不计，但alert日志中的信息可能会非常有用。

 

 

4. Redo 日志和检查点

每次切换日志时都会发生一次检查点。如果上一个检查点已在进行中，由日志切换引起的检查点将覆盖当前检查点。

此时就需要大小合适的 redo 日志，以避免因频繁的日志切换而引起不必要的检查点。另外，增量检查点目标和日志尾之间的间隔也会受“最小在线日志文件大小的 90%”设置所限制。这样可确保在大多数情况下，日志切换不必等待检查点。因此，日志文件大小应配置得足够大。一个好的办法是，最多每二十分钟切换一次日志。日志文件过小会增加检查点活动并降低性能。Oracle 建议用户将所有在线日志文件设置为同一大小，且每个线程至少拥有两个日志组。若要监视日志切换发生的速度，以及随后的检查点发生的速度，alert日志是一个很有价值的工具。
 
以下是通过alert日志发现日志切换过于频繁的示例：
 

Fri May 16 17:15:43 1997
Thread 1 advanced to log sequence 1272
  Current log# 3 seq# 1272 mem# 0: /prod1/oradata/logs/redologs03.log
Thread 1 advanced to log sequence 1273
  Current log# 1 seq# 1273 mem# 0: /prod1/oradata/logs/redologs01.log
Fri May 16 17:17:25 1997
Thread 1 advanced to log sequence 1274
  Current log# 2 seq# 1274 mem# 0: /prod1/oradata/logs/redologs02.log
Thread 1 advanced to log sequence 1275
  Current log# 3 seq# 1275 mem# 0: /prod1/oradata/logs/redologs03.log
Fri May 16 17:20:51 1997
Thread 1 advanced to log sequence 1276
  Current log# 1 seq# 1276 mem# 0: /prod1/oradata/logs/redologs01.log
 

如果 redo 日志每 3 分钟切换一次，您就会察觉到性能降低。这表明 redo 日志不够大，不能有效地处理该事务负载。

5. 了解检查点错误消息（“Cannot allocate new log”和“Checkpoint not complete”）

有时，可以在 alert.log 文件中看到以下相应消息：

  Thread 1 advanced to log sequence 248
    Current log# 2 seq# 248 mem# 0: /prod1/oradata/logs/redologs02.log
  Thread 1 cannot allocate new log, sequence 249
  Checkpoint not complete
 

此信息表明 Oracle 希望重新使用某个 redo 日志文件，但当前的检查点位置仍位于该日志中。在这种情况下，Oracle 必须等到检查点位置通过该日志。由于增量检查点目标相对于当前日志尾的滞后绝不会超过最小日志文件大小的 90% 以上，因此，如果 DBWR 写入速度过慢，或者在日志全满之前发生日志切换，或者日志文件过小，就会遇到这种情况。在数据库等待检查点时，redo 生成过程会停止，直到完成日志切换。

 

6. Oracle 版本信息

在 Oracle8i 中，初始化参数 FAST_START_IO_TARGET 会使增量检查点自动调整其目标，从而使恢复所需的数据块数量不多于 FAST_START_IO_TARGET 设置的值。自 Oracle 9i 开始，已弃用此参数，取而代之的是参数 FAST_START_MTTR_TARGET。

 

7. 使用 Statspack 确定检查点问题

可以每 15 分钟左右收集一次 Statspack 快照，这些快照报告将收集有关在该时间段已开始的检查点数量、已完成的检查点数量及检查点发生时写入的数据库缓冲数量的有用信息。此外，还包含关于 redo 活动的统计信息。通过收集和比较这些快照报告，可以完整地了解不同时期的检查点性能。

Statspack 报告中另一个值得关注的内容是等待事件，下面的等待事件明确指出了 redo 日志吞吐量和检查点的问题：

log file switch (checkpoint incomplete)
log file switch (archiving needed)
log file switch/archive
log file switch (clearing log file)
log file switch completion
log switch/archive
log file sync



如果上述等待事件中的一个或多个频繁地出现，且相关数值较大，那您就需要采取行动了，例如添加更多的在线 redo 日志文件，或增加其大小和/或修改检查点参数。
Oracle数据库实例检查点（Checkpoint）优化及故障排除最佳实践

关于重建索引有用与否的讨论有很多。一般而言，极少需要重建 B 树索引，基本原因是 B 树索引很大程度上可以自我管理或自我平衡。

认为需要重建索引的最常见理由有：
– 索引碎片在不断增加
– 索引不断增加，删除的空间没有重复使用
– 索引 clustering factor （群集因子）不同步

事实上，大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。
Clustering factor 群集因子可以反映给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，要改变集群因子只能通过重组表的数据。

另外，重建索引的影响非常明显，请仔细阅读以下说明：

1. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充：

analyze index ... validate structure;

尽管这是一种有效的索引检查方法，但是它在分析索引时会获取独占表锁。特别对于大型索引，它的影响会是巨大的，因为在此期间不允许对表执行 DML 操作。虽然该方法可以在不锁表的情况下在线运行，但是可能要消耗额外的时间。

2. 重建索引的直接结果是 REDO 活动可能会增加，总体的系统性能可能会受到影响。

插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。重建索引后，它将连接的更为紧凑；但是，随着对表不断执行 DML 操作，必须再次分割索引，直到索引达到平衡为止。结果，重做活动增加，且索引分割更有可能对性能产生直接影响，因为我们需要将更多的 I/O、CPU 等用于索引重建。经过一段时间后，索引可能会再次遇到“问题”，因此可能会再被标记为重建，从而陷入恶性循环。因此，通常最好是让索引处于自然平衡和（或）至少要防止定期重建索引。

3. 通常是优先考虑index coalesce（索引合并），而不是重建索引。索引合并有如下优点：

- 不需要占用近磁盘存储空间 2 倍的空间
- 可以在线操作
- 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大，请见第 2 点中的解释。

注意：例如，如要将索引转移到其他表空间，则需要重建索引。

综上所述，强烈建议不要定期重建索引，而应使用合适的诊断工具，下面分享一个Oracle推荐的分析索引结构的脚本以及使用建议：

 

1）. 创建一个将包含索引统计信息表的用户

2）. 为此用户分配“dba”权限。并授予选择dba_tablespaces;<user>

3）. 执行位于脚本部分中的代码。

如果脚本以 SYS 以外的用户身份运行，则在创建包正文时可能会遇到 ORA-942 错误。

使用定义者权限调用过程时，角色将丢失，因此除非显式授予以下 SELECT 权限，否则创建包正文将失败：

grant select on dba_tablespaces to <user>;
grant select on dba_indexes to <user>;
grant select on dba_tables to <user>;
grant select on dba_ind_columns to <user>;
grant select on dba_tab_cols to <user>;
grant select on dba_objects to <user>;
grant select on v_$parameter to <user>;

并且执行分析脚本前需要分析索引的统计信息，以下为例：

 

SQL> exec dbms_stats.gather_schema_stats(‘SCOTT’);

SQL> exec index_util.inspect_schema (‘SCOTT’);

 

脚本样例：

 

CREATE TABLE index_log (
 owner          VARCHAR2(30),
 index_name     VARCHAR2(30),
 last_inspected DATE,
 leaf_blocks    NUMBER,    
 target_size    NUMBER,
 idx_layout     CLOB);

ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);

CREATE TABLE index_hist (
 owner          VARCHAR2(30),
 index_name     VARCHAR2(30),
 inspected_date DATE,
 leaf_blocks    NUMBER,    
 target_size    NUMBER,
 idx_layout     VARCHAR2(4000));

ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY  (owner,index_name,inspected_date);

--
-- Variables:
--  vMinBlks: Specifies the minimum number of leaf blocks for scanning the index
--            Indexes below this number will not be scanned/reported on
--  vScaleFactor: The scaling factor, defines the threshold of the estimated leaf block count
--                to be smaller than the supplied fraction of the current size.
--  vTargetUse : Supplied percentage utilisation. For example 90% equates to the default pctfree 10
--  vHistRet : Defines the number of records to keep in the INDEX_HIST table for each index entry
--

CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS
vMinBlks     CONSTANT POSITIVE := 1000;
vScaleFactor CONSTANT NUMBER := 0.6;
vTargetUse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  
vHistRet     CONSTANT POSITIVE := 10;  -- (#) records to keep in index_hist
 procedure inspect_schema (aSchemaName IN VARCHAR2);
 procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER);
END index_util;
/

CREATE OR REPLACE PACKAGE BODY index_util IS
procedure inspect_schema (aSchemaName IN VARCHAR2) IS
 begin
 FOR r IN (select table_owner, table_name, owner index_owner, index_name, leaf_blocks
           from dba_indexes  
           where owner = upper(aSchemaname)
             and index_type in ('NORMAL','NORMAL/REV','FUNCTION-BASED NORMAL')
             and partitioned = 'NO'  
             and temporary = 'N'  
             and dropped = 'NO'  
             and status = 'VALID'  
             and last_analyzed is not null  
           order by owner, table_name, index_name) LOOP

   IF r.leaf_blocks > vMinBlks THEN
   inspect_index (r.index_owner, r.index_name, r.table_owner, r.table_name, r.leaf_blocks);
   END IF;
  END LOOP;
 commit;
end inspect_schema;
procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwner IN VARCHAR2, aTableName IN VARCHAR2, aLeafBlocks IN NUMBER) IS
 vLeafEstimate number;  
 vBlockSize    number;
 vOverhead     number := 192; -- leaf block "lost" space in index_stats
 vIdxObjID     number;
 vSqlStr       VARCHAR2(4000);
 vIndxLyt      CLOB;
 vCnt          number := 0;
  TYPE IdxRec IS RECORD (rows_per_block number, cnt_blocks number);
  TYPE IdxTab IS TABLE OF IdxRec;
  l_data IdxTab;
begin  
  select a.block_size into vBlockSize from dba_tablespaces a,dba_indexes b where b.index_name=aIndexName and b.owner=aIndexOwner and a.tablespacE_name=b.tablespace_name;
 select round (100 / vTargetUse *       -- assumed packing efficiency
              (ind.num_rows * (tab.rowid_length + ind.uniq_ind + 4) + sum((tc.avg_col_len) * (tab.num_rows) )  -- column data bytes  
              ) / (vBlockSize - vOverhead)  
              ) index_leaf_estimate  
   into vLeafEstimate  
 from (select  /*+ no_merge */ table_name, num_rows, decode(partitioned,'YES',10,6) rowid_length  
       from dba_tables
       where table_name  = aTableName  
         and owner       = aTableOwner) tab,  
      (select  /*+ no_merge */ index_name, index_type, num_rows, decode(uniqueness,'UNIQUE',0,1) uniq_ind  
       from dba_indexes  
       where table_owner = aTableOwner  
         and table_name  = aTableName  
         and owner       = aIndexOwner  
         and index_name  = aIndexName) ind,  
      (select  /*+ no_merge */ column_name  
       from dba_ind_columns  
       where table_owner = aTableOwner  
         and table_name  = aTableName
         and index_owner = aIndexOwner   
         and index_name  = aIndexName) ic,  
      (select  /*+ no_merge */ column_name, avg_col_len  
       from dba_tab_cols  
       where owner = aTableOwner  
         and table_name  = aTableName) tc  
 where tc.column_name = ic.column_name  
 group by ind.num_rows, ind.uniq_ind, tab.rowid_length;

 IF vLeafEstimate < vScaleFactor * aLeafBlocks THEN
  select object_id into vIdxObjID
  from dba_objects  
  where owner = aIndexOwner
    and object_name = aIndexName;
   vSqlStr := 'SELECT rows_per_block, count(*) blocks FROM (SELECT /*+ cursor_sharing_exact ' ||
             'dynamic_sampling(0) no_monitoring no_expand index_ffs(' || aTableName ||
             ',' || aIndexName || ') noparallel_index(' || aTableName ||
             ',' || aIndexName || ') */ sys_op_lbid(' || vIdxObjID ||
             ', ''L'', ' || aTableName || '.rowid) block_id, ' ||
             'COUNT(*) rows_per_block FROM ' || aTableOwner || '.' || aTableName || ' GROUP BY sys_op_lbid(' ||
             vIdxObjID || ', ''L'', ' || aTableName || '.rowid)) group by rows_per_block order by rows_per_block';
   execute immediate vSqlStr BULK COLLECT INTO l_data;
  vIndxLyt := '';

   FOR i IN l_data.FIRST..l_data.LAST LOOP
    vIndxLyt := vIndxLyt || l_data(i).rows_per_block || ' - ' || l_data(i).cnt_blocks || chr(10);
   END LOOP;

   select count(*) into vCnt from index_log where owner = aIndexOwner and index_name = aIndexName;

   IF vCnt = 0   
    THEN insert into index_log values (aIndexOwner, aIndexName, sysdate, aLeafBlocks, round(vLeafEstimate,2), vIndxLyt);
    ELSE vCnt := 0;

         select count(*) into vCnt from index_hist where owner = aIndexOwner and index_name = aIndexName;

         IF vCnt >= vHistRet THEN
           delete from index_hist
           where owner = aIndexOwner
             and index_name = aIndexName
             and inspected_date = (select MIN(inspected_date)
                                   from index_hist
                                   where owner = aIndexOwner
                                     and index_name = aIndexName);
         END IF;

          insert into index_hist select * from index_log where owner = aIndexOwner and index_name = aIndexName;

         update index_log  
         set last_inspected = sysdate,
             leaf_blocks = aLeafBlocks,
             target_size = round(vLeafEstimate,2),
             idx_layout = vIndxLyt
        where owner = aIndexOwner and index_name = aIndexName;

   END IF;
  END IF;
 END inspect_index;
END index_util;
/

脚本执行输出的样例：

 

SQL> select owner, index_name, last_inspected, leaf_blocks, target_size 

  from index_log


OWNER INDEX_NAME LAST_INSP LEAF_BLOCKS TARGET_SIZE

------------------------------ ------------------------------ --------- ----------- -----------

SYS I_ARGUMENT1 17-JUN-10 432 303

SYS I_ARGUMENT2 17-JUN-10 282 186

SYS I_COL1 17-JUN-10 288 182

SYS I_DEPENDENCY1 17-JUN-10 109 103

SYS I_DEPENDENCY2 17-JUN-10 136 95

SYS I_H_OBJ#_COL# 17-JUN-10 258 104

SYS WRH$_SQL_PLAN_PK 17-JUN-10 118 59

SYS WRI$_ADV_PARAMETERS_PK 17-JUN-10 210 121

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 17-JUN-10 2268 1313

SYS I_WRI$_OPTSTAT_H_ST 17-JUN-10 1025 963

SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 17-JUN-10 338 191






 








SQL> select idx_layout from index_log where owner='SCOTT' and index_name='T_IDX';



IDX_LAYOUT 

------------

104 - 1 

204 - 1 

213 - 1 

219 - 1 

221 - 2 

222 - 1 

223 - 2 

224 - 1 

225 - 1 

230 - 1 

231 - 3 

235 - 3 

236 - 1 

238 - 3 

239 - 2 

241 - 1 

242 - 2 

243 - 1 

245 - 3 

247 - 1 

249 - 1 

250 - 1 

252 - 3 

255 - 1 

257 - 2 

263 - 2 

264 - 1 

267 - 1 

268 - 1 

276 - 1 

283 - 1 

296 - 1 

345 - 1














SQL> select to_char(inspected_date,'DD-MON-YYYY HH24:MI:SS') inspected_date, 

leaf_blocks, target_size 

from index_hist 

where index_name='T_IDX';



INSPECTED_DATE LEAF_BLOCKS TARGET_SIZE

-------------------- ----------- -----------

10-MAR-2010 10:04:04 432 303

10-APR-2010 10:04:03 435 430

10-MAY-2010 10:04:02 431 301










 


 


 


 


 


 

		        
			索引重建的必要性与影响以及相关分析索引结构的脚本
        
		

    

   

 




				
					


 

   

	    

 		
之前发表过smon一些相关的文章，主要是讨论回滚方面的事宜，最近在项目上碰到pmon的相关问题，因此也学习了一些相关的知识，10g之前的版本跟踪方式主要局限于操作系统版本的命令跟踪，这里不做讨论


 


11g 跟踪命令


 


从 11.1.0.7 到 18.0, 可以使用下面的命令来启动 tracing:


alter system set events=’immediate trace name listener_registration level 3′;


当收集结束后，使用下面的命令来停止跟踪:


alter system set events=’immediate trace name listener_registration level 0′;


19c开始使用下面的命令


开启 Trace:

alter system set events ‘trace[LREG] disk highest’;

alter system set events = ‘immediate trace name LREG_STATE level 3’;


要关闭 Trace:

alter system set events ‘trace[LREG] disk disable’;

Trace的默认路径是:

$ORACLE_BASE/diag/rdbms/trace/

ls -l | grep -i lreg


这会将 PMON 的信息写入到名字包含 pmon 的 trace 文件中，存放在后台进程 trace 目录。Trace 文件会显示类似下面的信息：


Start Registration Information

——————————

Last update: 1188938571 (99 seconds ago)

Flag: 0x4, 0x0

State: succ=1, wait=0, fail=0

Listeners:

0 – (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)): <— 监听地址

state=1, err=0

nse[0]=0, nse[1]=0, nte[0]=0, nte[1]=0, nte[2]=0

ncre=0

Instance: <— 实例名

flg=0, upd=0

info=(HOST=) <– 主机名

node load=57, max=40960 <– 节点负载

inst load=1, max=170 <– 实例负载

Services:

0 – : <– 服务名

flg=4, upd=6

goodness=0, delta=1 <– Goodness 和 Delta 值

1 – _XPT:

flg=4, upd=0

goodness=0, delta=0

2 – XDB:

flg=5, upd=6

goodness=0, delta=1

Handlers:

0 – Dedicated

flg=80002002, upd=2

services=,_XPT

hdlr load=22, max=149

Dispatchers:

0 – D000:

addr=(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=33099))

inf=DISPATCHER , pid: 10850>

flg=1004, upd=0

services=XDB

hdlr load=0, max=1022

CMON Handlers:

Listen Endpoints:

—————————-

End Registration Information

—————————-


注意：Oracle Net Server trace，TNS 监听 trace 和 event 10257 在 11g 仍然可用，之前的文章介绍过跟踪监听的方法。详见下面 12c 之前版本的介绍。


 


12c LREG 故障排除


 


从 12c 开始，引入了新的后台进程 ora_lreg_sid-name。

在之前的版本中，PMON 负责处理实例注册。12c 中，LREG（Listener REGistration）接管实例注册逻辑。


LREG:


将实例信息注册到监听。

是每个数据库实例的关键后台进程（如果被杀死，oracle 将宕机）。

接管旧版本中 PMON 的一些职责，并且在 listener.log 中更新 service_update，service_register，service_died 信息。


跟踪 LREG 的方法与跟踪 PMON 的方法相同：


开启 Oracle Net 服务器端 sqlnet trace 会从实例启动时开始跟踪 LREG。

旧的 PMON trace 现在跟踪 LREG：alter system set events = ‘10257 trace name context forever, level 5’;

监听注册信息也可以通过这种方式被转储到 ora_lreg trace 文件中：alter system set events = ‘immediate trace name listener_registration level 3’;

可以动态跟踪 LREG。


 


12c 之前版本，使用下面的方法跟踪 PMON 注册问题 A) Oracle Net server 和 listener traces 或者 B) PMON tracing


A) 搜集匹配的 Oracle Net Server trace 和 Listener Trace 文件


服务器端 TRACE：


1. 在文件 SQLNET.ORA 中添加下面的参数来开启 Oracle Net Server tracing：


DIAG_ADR_ENABLED=off # Disable ADR if database version 11g TRACE_LEVEL_SERVER = 16 # Enable level 16 trace

TRACE_DIRECTORY_SERVER = # Control trace file location


2. 使用特权用户通过 SQL*Plus 连接数据库：


SQL> connect / as sysdba

Connected.

SQL> select spid from V$process, V$session where audsid=userenv(‘SESSIONID’) and paddr=addr;


SPID

————

3940

生成的 trace 文件的名字，将包含上面的返回值。


3. 执行注册命令：


SQL > alter system register

SQL > exit

4. 关闭服务器端 trace：


如果需要禁用 trace，那么可以删除 SQLNET.ORA 中刚加入的参数。到 TRACE_DIRECTORY_SERVER 设置的路径下，找到名字包含 SPID 值的 trace 文件。文件中会包含 alter system register 命令：


Listener tracing:

1. 在 listener.ora 中添加下面的参数，然后 reload listener：


DIAG_ADR_ENABLED_ =off # 如果数据库版本是 11g，需要关闭 ADR。

TRACE_LEVEL_ = 16 # 启用 level 16 trace

TRACE_TIMESTAMP_ = ON # 设置 trace 文件中的时间戳

TRACE_DIRECTORY_ = # 设置 trace 文件路径


2. 执行‘alter system register’强制注册：


SQL> alter system register;

System altered.


listener trace 文件中会看到类似下面的信息：

（信息会由于版本不同或者单节点、RAC 等因素有细微差别）


nsglgrDoRegister: inst loads: ld1:17 mld1:10240 ld2:1 mld2:248

nsglgrDoRegister: instance flags – req:0 cur:16

nsglgrDoRegister: Creating new service: “XDB.*****.com”.

nsglgrDoRegister: service:..oracle.com flag:3 goodness:0 delta:1

nsglgrDoRegister: Creating new service: “..oracle.com”.

nsglgrDoRegister: service:..oracle.com flag:2 goodness:0 delta:1


B) 12c 之前的版本启用 PMON trace 的方法：

1. 找到 PMON 的进程 ID：


SQL> select SPID,PROGRAM from v$process;


SPID PROGRAM

———————— ————————————————

PSEUDO

10096 oracle@ (PMON)

10098 oracle@ (PSP0)

10100 oracle@ (VKTM)

10104 oracle@ (GEN0)

10106 oracle@ (DIAG)

10108 oracle@ (DBRM)

10110 oracle@ (DIA0)

10112 oracle@ (MMAN)

10114 oracle@ (DBW0)

10116 oracle@ (LGWR)


SPID PROGRAM

———————— ————————————————

10118 oracle@ (CKPT)

10120 oracle@ (SMON)

10122 oracle@ (RECO)

10124 oracle@ (MMON)

10126 oracle@ (MMNL)

10128 oracle@ (D000)

10130 oracle@ (S000)

10175 oracle@ (Q000)

10280 oracle@ (SMCO)

22191 oracle@ (TNS V1-V3)

10159 oracle@ (QMNC)


SPID PROGRAM

———————— ————————————————

10177 oracle@ (Q001)

10173 oracle@ (CJQ0)

22186 oracle@ (W000)


25 rows selected.


2. 对 PMON 做 oradebug：


SQL> oradebug setospid 10096

Oracle pid: 2, Unix process id: 10096, image: oracle@(PMON)


3. 对进程设置 event：


SQL> oradebug Event 10257 trace name context forever, level 16

Statement processed.

4. trace 文件的位置可以通过下面的命令查看：


SQL> oradebug tracefile_name

Trace file /app/oracle/diag/rdbms///trace/_pmon_10096.trc

5. 执行注册命令，或者等待 PMON 注册（默认轮询时间是60秒）：


SQL> alter system register;

System altered.


6. 关闭 event：


SQL> oradebug Event 10257 trace name context OFF;

Statement processed.


到 trace 所在的目录下并上传 trace。

注册成功会显示类似下面的信息：


Trace file /app/oracle/diag/rdbms///trace/_pmon_10096.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name:

Release: 2.6.18-238.19.1.0.1.el5

Version: #1 SMP Fri Jul 15 04:42:13 EDT 2011

Machine: x86_64

Instance name:

Redo thread mounted by this instance: 1

Oracle process number: 2

Unix process pid: 10096, image: oracle@ (PMON)


 


Received ORADEBUG command (#1) ‘Event 10257 trace name context forever, level 16’ from process ‘Unix process pid: 22065, image: ‘


Finished processing ORADEBUG command (#1) ‘Event 10257 trace name context forever, level 16’


err=-300 lbflgs=0x0 tbtime=0 tntime=0 etime=300 srvs=1 nreqs=0 sreqs=0 asrvs=1

error=-300 etime=300 control=0 integral=0 lasterr=-300 lastetm=300

kmmlrl: status: succ=1, wait=0, fail=0

kmmlrl: update for process drop delta: 3166 3166 25 28 149

kmmgdnu:

goodness=0, delta=1,

flags=0x5:unblocked/not overloaded, update=0x6:G/D/-

kmmgdnu:

goodness=0, delta=1,

flags=0x4:unblocked/not overloaded, update=0x6:G/D/-

kmmlrl: 25 processes

kmmlrl: instance load 1


 


Bug 5755010 Listener registration never completes

详细信息： 尽管并没有注册失败的错误，但是监听注册会失败

修复版本：10.2.0.4 and 11.1.0.6


Bug 8232287 PMON stops registering its services (ORA-12516 errors)

详细信息：pmon 停止注册到监听，会引发 ORA-12516 错误。

修复版本：10.2.0.5 and 11.2.0.1


Bug 7133740One Instance of Bug ( 2 – NODE RAC DATABASE) I Crashed Due To Ora-600

请参考：


Document 759083.1 Connections get TNS-12520 error on RAC & PMON is stuck on ‘ges cancel’ wait event


Document 779318.1 Repeating ‘* Service_died * 0′ Messages In The 9i R2 Listener.Log File


Document 419824.1RAC Instance Status Shows Ready Zero Handlers For The Service


Document 1130713.1 Pmon Spins While Cleaning Dead Process


诊断方法：


1.检查 Oracle net 名字解析方式是否正确。例如：SQLNET.ORA 文件中的 NAMES.DIRECTORY_PATH 。Oracle net 会尝试正确的名字解析方式。


2.确保使用了正确的网络管理文件：SQLNET.ORA 和 TNSNAMES.ORA。Note:464410.1 Search Order for TNS files – listener.ora, sqlnet.ora, tnsnames.ora ..etc.


3.检查在数据库启动之前是否设置了 TNS_ADMIN ，如果是 RAC 环境，是否使用 srvctl 设置了 TNS_ADMIN，TNS_ADMIN 可以 影响搜索顺序。数据库只在启动的时候读取环境变量。如果在启动数据库之后设置了 TNS_ADMIN，然后做了修改，修改后的值是不会被读取到的。


4.检查 LOCAL_LISTENER 或者 REMOTE_LISTENER 使用的网络服务名是否可以 tnsping 通。如果不通，重建条目或者参考第6步。


5.确保使用的主机名与 nslookup 返回的结果相同，并且返回的地址是预期的。


C:\>nslookup


Server:

Address: ….


Name:

Address:….


6.修改 LOCAL_LISTENER 或者 REMOTE_LISTENER 的值，确保不使用名字解释方式。不使用网络管理文件，确认是否问题出在网络管理文件或是没找到它们。

例如 LOCAL_LISTENER


sqlplus / as sysdba

SQL>alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521));

例如两节点 RAC 中的 REMOTE_LISTENER


SQL>alter system set REMOTE_LISTENER=’ (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)))’;


7.检查 HOST 到 ip 地址的转换是否允许注册。


8.使用 IPC 替代 TCP 完成 LOCAL_LISTENER 的注册，验证问题是否与 TCP 或主机名有关。


sqlplus / as sysdba

SQL>alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=IPC)(KEY=KEY1))’;

Key 值必须与 LISTENER.ORA 文件中的 IPC 地址一致。

		        
			在oracle 数据库运行时使用event跟踪 pmon进程动态注册
        
		

    

   

 




				
					


 

   

	    

 		
bug: MEMORY_TARGET not supported on this system

SQL> startup open;

ORA-00845: MEMORY_TARGET not supported on this system


解决方案：原因是/dev/shm 必须大于 MEMORY_TARGET。


$ df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 1.8G 0 1.8G 0% /dev

tmpfs 1.9G 635M 1.2G 35% /dev/shm

tmpfs 1.9G 9.2M 1.8G 1% /run

tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup

/dev/mapper/ol-root 10G 3.9G 6.2G 39% /

/dev/sda1 397M 199M 198M 51% /boot

/dev/mapper/ol-home 3.0G 58M 3.0G 2% /home

/dev/mapper/ol-u01 30G 18G 13G 58% /u01

/dev/mapper/ol-tmp 3.0G 59M 3.0G 2% /tmp

vmhgfs-fuse 159G 133G 27G 84% /mnt/hgfs

tmpfs 370M 0 370M 0% /run/user/54322

tmpfs 370M 16K 370M 1% /run/user/42

tmpfs 370M 0 370M 0% /run/user/54321

tmpfs 370M 0 370M 0% /run/user/0


通过如下命令解决即可


mount -o size=2560m /dev/shm


 

		        
			Dataguard bug ： MEMORY_TARGET not supported on this system ORA-00845
        
		

    

   

 




				
					


 

   

	    

 		
概述


 


某客户于2021/06/07凌晨01点43分左右，出现应用程序无法连接，前台业务无法办理等情况。在此情况下代维科技工程师在接到故障支持电话后，及时进行响应，通过远程的方式接入数据库进行故障排除，快速解决了问题使得业务恢复正常使用。


问题及相关日志分析


1. 数据库告警日志分析分析


通过对数据库告警日志查看，节点1在01:22:14出现ORA-29970错误，LMD进程无法响应，在01:22:29被LMHB进程将实例终止，实例终止后自动启动。节点2在01:29:31被LMS0进程将实例终止，实例终止后自动启动。


1节点：


Mon Jun 07 01:22:14 2021


LMD0 (ospid: 10613) has not called a wait for sub 0 secs.


LMD1 (ospid: 10617) has not called a wait for sub 0 secs.


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lmhb_10637.trc  (incident=688375) (PDBNAME=CDB$ROOT):


ORA-29770: global enqueue process LMD0 (OSID 10613) is hung for more than 70 seconds


Incident details in: /u01/app/oracle/diag/rdbms/cdb/cdb1/incident/incdir_688375/cdb1_lmhb_10637_i688375.trc


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lmhb_10637.trc  (incident=688376) (PDBNAME=CDB$ROOT):


ORA-29770: global enqueue process LMD1 (OSID 10617) is hung for more than 70 seconds


Incident details in: /u01/app/oracle/diag/rdbms/cdb/cdb1/incident/incdir_688376/cdb1_lmhb_10637_i688376.trc


LOCK_DBGRP: GCR_SYSTEST debug event locked group GR+DB_CDB by memno 0


ERROR: Some process(s) is not making progress.


LMHB (ospid: 10637) is terminating the instance.


Please check LMHB trace file for more details.


Please also check the CPU load, I/O load and other system properties for anomalous behavior


ERROR: Some process(s) is not making progress.


LMHB (ospid: 10637): terminating the instance due to error 29770


Mon Jun 07 01:22:24 2021


System state dump requested by (instance=1, osid=10637 (LMHB)), summary=[abnormal instance termination].


System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_diag_10575_20210607012224.trc


Mon Jun 07 01:22:26 2021


License high water mark = 591


Mon Jun 07 01:22:29 2021


Instance terminated by LMHB, pid = 10637


Mon Jun 07 01:22:29 2021


USER (ospid: 21601): terminating the instance


Mon Jun 07 01:22:29 2021


Instance terminated by USER, pid = 21601


Mon Jun 07 01:22:32 2021


Starting ORACLE instance (normal) (OS id: 21660)


Mon Jun 07 01:22:32 2021


CLI notifier numLatches:37 maxDescs:3986


Mon Jun 07 01:22:32 2021


**********************************************************************


Mon Jun 07 01:22:32 2021


Dump of system resources acquired for SHARED GLOBAL AREA (SGA)


 


Mon Jun 07 01:22:32 2021


Per process system memlock (soft) limit = UNLIMITED


Mon Jun 07 01:22:32 2021


Expected per process system memlock (soft) limit to lock


SHARED GLOBAL AREA (SGA) into memory: 128G


Mon Jun 07 01:22:32 2021


Available system pagesizes:


4K, 2048K


Mon Jun 07 01:22:32 2021


Supported system pagesize(s):


Mon Jun 07 01:22:32 2021


PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)


Mon Jun 07 01:22:32 2021


2048K            66823           65538           65538        NONE


Mon Jun 07 01:22:32 2021


Reason for not supporting certain system pagesizes:


Mon Jun 07 01:22:32 2021


4K – Large pagesizes only


Mon Jun 07 01:22:32 2021


**********************************************************************


LICENSE_MAX_SESSION = 0


LICENSE_SESSIONS_WARNING = 0


Initial number of CPU is 24


 


2节点：


Mon Jun 07 01:25:05 2021


Set master node info


Mon Jun 07 01:26:05 2021


Auto-tuning: Shutting down background process GTXb


Mon Jun 07 01:27:53 2021


IPC Send timeout detected. Sender: ospid 62152 [oracle@node1 (PING)]


Receiver: inst 1 binc 912546309 ospid 10589


Mon Jun 07 01:29:25 2021


LMD0 (ospid: 62168) received an instance eviction notification from instance 1 [2]


Mon Jun 07 01:29:26 2021


Received an instance abort message from instance 1


Mon Jun 07 01:29:26 2021


Received an instance abort message from instance 1


Mon Jun 07 01:29:26 2021


Please check instance 1 alert and LMON trace files for detail.


Mon Jun 07 01:29:26 2021


Please check instance 1 alert and LMON trace files for detail.


Mon Jun 07 01:29:26 2021


LMS0 (ospid: 62192): terminating the instance due to error 481


Mon Jun 07 01:29:26 2021


System state dump requested by (instance=2, osid=62192 (LMS0)), summary=[abnormal instance termination].


System State dumped to trace file /u01/app/oracle/diag/rdbms/cdb/cdb2/trace/cdb2_diag_62123_20210607012926.trc


Mon Jun 07 01:29:26 2021


ORA-1092 : opitsk aborting process


Mon Jun 07 01:29:27 2021


License high water mark = 1251


Mon Jun 07 01:29:31 2021


Instance terminated by LMS0, pid = 62192


Mon Jun 07 01:29:31 2021


USER (ospid: 85262): terminating the instance


Mon Jun 07 01:29:31 2021


Instance terminated by USER, pid = 85262


Mon Jun 07 01:29:33 2021


Starting ORACLE instance (normal) (OS id: 85397)


Mon Jun 07 01:29:33 2021


CLI notifier numLatches:37 maxDescs:3986


Mon Jun 07 01:29:33 2021


**********************************************************************


Mon Jun 07 01:29:33 2021


Dump of system resources acquired for SHARED GLOBAL AREA (SGA)


 


Mon Jun 07 01:29:33 2021


Per process system memlock (soft) limit = UNLIMITED


Mon Jun 07 01:29:33 2021


Expected per process system memlock (soft) limit to lock


SHARED GLOBAL AREA (SGA) into memory: 128G


Mon Jun 07 01:29:33 2021


Available system pagesizes:


4K, 2048K


Mon Jun 07 01:29:33 2021


Supported system pagesize(s):


Mon Jun 07 01:29:33 2021


PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)


Mon Jun 07 01:29:33 2021


2048K            67200           65538           65538        NONE


Mon Jun 07 01:29:33 2021


Reason for not supporting certain system pagesizes:


Mon Jun 07 01:29:33 2021


4K – Large pagesizes only


Mon Jun 07 01:29:33 2021


**********************************************************************


LICENSE_MAX_SESSION = 0


LICENSE_SESSIONS_WARNING = 0


Initial number of CPU is 24


查看更多日志，发现在关闭之前，出现了较多的ORA-04031错误。


Mon Jun 07 01:18:14 2021


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_ora_14795.trc  (incident=691727) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^1069″,”kglseshtTable”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lmd0_10613.trc  (incident=707486) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 8504 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(5,0)”,”ges big msg pool”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


Mon Jun 07 01:18:16 2021


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_ora_14832.trc  (incident=691688) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^320″,”kglseshtTable”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


Mon Jun 07 01:18:16 2021


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_ora_14837.trc  (incident=691735) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^1234″,”kglseshtTable”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


Mon Jun 07 01:18:18 2021


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_ora_14912.trc  (incident=691871) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^159″,”kglseshtTable”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_lmd0_10613.trc  (incident=707487) (PDBNAME=CDB$ROOT):


ORA-04031: unable to allocate 8504 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(5,0)”,”ges big msg pool”)


Use ADRCI or Support Workbench to package the incident.


See Note 411.1 at My Oracle Support for error and packaging details.


DDE: Problem Key ‘ORA 4031’ was completely flood controlled (0x6)


Further messages for this problem key will be suppressed for up to 10 minutes


Mon Jun 07 01:18:33 2021


Errors in file /u01/app/oracle/diag/rdbms/cdb/cdb1/trace/cdb1_m000_15479.trc:


ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^2147″,”kglseshtTable”)


 


 


2. AWR报告信息分析


 


拉取了宕机前半小时的AWR报告，从AWR报告中得知，shared pool中ges enqueues和ges resource dynamic分别达到了18G和16G。


3. Oracle官方参考文献


 


根据Oracle官方文档记载：ORA-04031 Errors Occurring with High “ges resource dynamic” & “ges enqueues” Memory Usage In The Shared Pool (Doc ID 2063751.1)，该问题是Oracle的BUG，该BUG出现于Oracle RAC 12.1.0.1至12.1.0.2，此问题在Oracle RAC 12.2中被修复，移动护理数据库版本为Oracle RAC 12.1.0.2。


 


 


4. 数据库硬解析比例高的SQL语句


SQL_TEXT


———————————————————————————————————————————————————————————————————————————————————-


unshared count


————–


SELECT A.PATIENT_ID, A.VISIT_ID,A.ORDER_NO,A.ORDER_SUB_NO,TO_CHAR(T.BED_NO) BED_NO,TO_CHAR(START_DATE_TIME,’YYYY-MM-DD HH24:MI:SS’) AS START_DATE_TIME, TO_   FROM  V_EMR_TIME,’YYYY-MM-DD HH24:MI:SS’) AS STOP_DATE_TIME


ORDERS_PERFORMAN  AND  A.PATIENT_ID=’690086500ID’,’7′)


8′              AND (A.STOP_DATE_TIME IS NULL OR A.STOP_DATE_TIME >= TO_DATE(‘2021-06-07’||’ 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)) AND A.START_DATE_TIME <=


TO_DATE(‘2021-06-07 07:52:53’, ‘YYYY-MM-DD HH24:MI:SS’)


35162


 


update KYEEHIS.hl7_send_records set type = ‘1’ where rec_id = ‘1003839362’


31808


 


AND T.BARCODE<>’1# IS NOT NULL2021-06-07′, ‘YYYY-MM-DD’)


ORDER BY T.OPER_TIME  DESC日(Q12)’, ‘1/12小时’, ‘1/8小时’)


14151


 


SELECT RECORD_TIME,ALL_S     WHERE PATIENT_ID=’1110234022′ AND VISIT_ID=’3′ AND PG_CODE=’8′ –AND RECORD_TIME BETWEEN TO_DATE(‘2021-06-07 00:00:00′,’YYYY-MM-DD HH24:MI:SS’) AND TO_DATE(‘2021-06-07 23:59:59


‘,’YYYY-MM-DD HH24:MI:SS     ORDER BY RECORD_TIME DESC


7829


 


select distinct barcode,’1’ exec_fg,nurse_name,nurse oper_nurse,” oper_time,order_exec_id,order_id,order_no,order_text,patient_id,visit_id                         where patient_id = ‘6900_exec_log


767269’                  and order_exec_id = ‘288132019’


6180


 


SELECT T.PATIENT_ID,            TO_DATE(TO_CHAR(T.EVENT_DATE,’YYYY-MM-DD’)||’ ‘|| TO_CHAR(T.TIME_POINT, ‘HH24:MI:SS’),


‘YYY      WHAND T.PATIENT_ID = ’40TAMP_CODEP_DICT K


10479126’                       AND (T.STAMP_CODE = ‘10003083’ OR T.STAMP_CODE = ‘10003084’)


5894


 


select t.* from KYEEHIS.docs_ord and order_no = ‘49712239’47427′


and exec_time = TO_DATE(‘2021-06-07 09:00:00′,’YYYY-MM-DD HH24:MI:SS’)


5070


 


SELECT REC_ID, PATIENT_ID, VISIT_ID, MODULE_CODE, MODULE_NAME, CREATE_TIME, ENBALE_VALUE FROM DOCS_OPERATION_MODULE_REC WHERE PATIENT_ID=’4010110870′ AND VISIT_ID=’3′ AND MODULE_CODE=’19’ AND ENBALE_VALUE=’Y’


4849


 


SELECT PATIENT_ID,TO_CHAR(VISIT_ID) AS VISIT_ID,INP_NO,WARD_CODE,WARD_NAME,DEPT_CODE,DEPT_NAME,PATIENT_NAME,SEX,TO_CHAR(BED_NO) AS BED_NO, BED_LABEL,DATE_OF_BIRTH,DECODE(NURSE_LEVEL,’特级护理’,’0′,’一级护理’,’1′,’二级护理’,’2′,’三级护理’,’3′) NURSE_L


EVEL,STATUS,CHARGE_TYPE,ADMISSION_DATE,DEPTED_TIME,DIAGNOSIS,MEAL,ALLERGY,ADDRESS, LEAVE_TIME,DOCTOR_IN_CHARGE ,       DOCTOR_NAME,       NURSE_IN_CHARGE,  NURSE_NAME,H_CODE,TYPE FROM KYEEHIS.v_his_pats_discharge T WHERE T.PATIENT_ID=’6901544170


‘ AND T.VISIT_ID=5 UNION  SELECT PATIENT_ID,TO_CHAR(VISIT_ID) AS VISIT_ID,INP_NO,WARD_CODE,WARD_NAME,DEPT_CODE,DEPT_NAME,PATIENT_NAME,SEX,TO_CHAR(BED_NO) AS BED_NO, BED_LABEL,DATE_OF_BIRTH,DECODE(NURSE_LEVEL,’特级护理’,’0′,’一级护理’,’1′,’二级护理’,’


2′,’三级护理’,’3′) NURSE_LEVEL,STATUS,CHARGE_TYPE,ADMISSION_DATE,DEPTED_TIME,DIAGNOSIS,MEAL,ALLERGY,ADDRESS,SYSDATE AS LEAVE_TIME,DOCTOR_IN_CHARGE ,          DOCTOR_NAME,       NURSE_IN_CHARGE,       NURSE_NAME,H_CODE,TYPE FROM KYEEHIS.V_HIS_PATS_IN_HOS


4139


 


select d.item_name,d.item_value  and t.patient_id = ‘6900510081’_eval_report_detail_rec d


and t.visit_id = ‘3’          and d.enabled_value = ‘Y’sionRecords’


4061


 


 


10 rows selected.




三、总结与后续处理建议


1. 问题总结


移动护理数据库版本是ORACLE RAC 12.1.0.2.0，本次故障发生前出现内存不足相关ORA-04031以及后台进程LMD无影响等报错信息，并最终被LMHB监测进程终止了数据库实例的运行。检查数据库后台TRACE及AWR信息报告，可以发现问题发生前shared pool内在中主要有组件ges resource dynamic和ges enqueues占用了35GB内存；


参照ORACLE官方文档信息该版本存在BUG 21373473，会导致shared pool中的ges resource dynamic和ges enqueues等组件占用内存不断增多，会导致数据库LMD后台进程无响应，从而导致实例终止宕机，这与本次问题的理解及后台TRACE吻合，因此判断为此BUG导致的数据库宕机。


同时数据库中存在硬解析比例高的问题，在2.4章节中已经将数据库中TOP 10的硬解析语句进行了抓取提供，需要软件开发商配合对此类SQL进行优化及使用SQL绑定变量等改写。


2. 问题详细分析过程


1.故障时间状态：在2021年6月7日凌晨00:30 – 01:00，移动护理数据库shared pool内存池中的ges enqueues和ges resource dynamic组件分别达到了18GB和16.8GB;


移动护理数据库在正常运行状态时ges enqueues和ges resource dynamic分别大约为3G和1G。


 


2.故障相关报错信息：问题时段这两个内存组件占用的内存显著异常，导致Oracle内存池剩余内存不足，引起数据库中进程出现ORA-04031的内存错误及后台进程LMD进程无响应(ORA-29770: global enqueue process LMD1 (OSID 10617) is hung for more than 70 seconds)。


报错信息为：ORA-04031: unable to allocate 12312 bytes of shared memory (“shared pool”,”unknown object”,”KKSSP^1234″,”kglseshtTable”)


 


3.报错信息与BUG的对应匹配：以上信息与ORA-04031 Errors Occurring with High “ges resource dynamic” & “ges enqueues” Memory Usage In The Shared Pool (Doc ID 2063751.1)及ORA-4031 due to Huge “ges resource dynamic” and “ges enqueues” and Instance Terminates: ORA-29770: ‘SGA: allocation forcing component growth’ (Doc ID 1951758.1)文档中的BUG 21373473描述吻合：ORA-04031 errors occurring in a RAC instance with the trace file showing high memory usage for “ges resource dynamic” and “ges enqueues” memory in the shared pool.  This can cause LMD processes to become unresponsive leading to an instance termination.


 


4.数据库实例被LMHB进程终止的原因：从11gR2版本，新增加LMHB进程，通过LMHB进程来定期监控lmon/lms/lmd/lck0等进程，如果LMHB进程发现某一个重要后台进程一段时间内(默认70秒)未更新自己的状态信息，会进行分析，如必要则通过重启本地节点的方式来解决问题。本次即为lmd进程超过70秒无响应后，LMHB对数据库进程进行了KILL，从而引起数据库宕机。


 


5. 后续处理及建议


    

  1. BUG问题的解决
    2. 



根据Oracle官方参考文献，解决该问题有两种方式：


    

  1. 解决方案1
    2. 



修改数据库参数，_GES_DIRECT_FREE_RES_TYPE=”CTARAHDXBB”，该参数是静态参数，需要重启数据库生效，可采取2个节点轮流修改的方法，减少业务停机时间，推荐采用此方案。


    

  1. 解决方案2
    2. 



安装数据库补丁，补丁号Patch:21260431，需要安排业务停机时间安装补丁。


 


    

  1. 数据库硬解析比例高的解决
    2. 



在2.4章节中已经将数据库中TOP 10的硬解析语句进行了抓取提供，需要软件开发商配合对此类SQL进行优化及使用SQL绑定变量等改写，从而提升系统性能，提高系统的稳定性。

		        
			ORA-29970 故障处理一例