Skip to content

Oracle 排障配置与调整 - 9. page

AIX平台Rac Listener offline问题分析

 

接到电话时候是下午4点多，长沙的天气有点闷热，外面正准备下雷雨。客户来电咨询集群2个节点的listener自动offline，问我大概的会是什么原因造成的，现场有个其他公司的Oracle工程师回复可能是Oracle bug导致，用户觉得不靠谱直接联系我了。从用户的反馈的信息来看，不能直接说明就是bug导致，但是第一反应就是vip offline 裙带关系把listener也带下去了，Listener与vip 这一对基友出的问题还少？但是也不排除其他的bug因为listener auto offline的bug虽然不是很多，但是也不是1只手数的过来的，从用户手上获取相关的log后着手进行分析。

分析思路：

一般碰到这类问题，首先要获取查看的日志就是
1.数据库告警日志（2个实例）
2.RAC的告警日志（其实这个不是很重要）
3.CRSD日志
4.RACG相关日志（VIP,ONS）
5.系统日志

因为是2个节点之间的都发生了listener offline，首先检测了2个节点的告警日志，只发现了现场工程师重启集群的log记录如下：
节点1

Wed Aug  6 10:59:19 2014
Thread 1 advanced to log sequence 227 (LGWR switch)
  Current log# 13 seq# 227 mem# 0: +DATA/orcl/onlinelog/redo13.log
Wed Aug  6 13:58:02 2014
Thread 1 advanced to log sequence 228 (LGWR switch)
  Current log# 18 seq# 228 mem# 0: +DATA/orcl/onlinelog/redo10.log
Wed Aug  6 14:47:15 2014
Shutting down instance: further logons disabled
Wed Aug  6 14:47:15 2014
Stopping background process CJQ0
Wed Aug  6 14:47:15 2014

节点2

Wed Aug 6 08:17:37 2014
Thread 2 advanced to log sequence 205 (LGWR switch)
Current log# 14 seq# 205 mem# 0: +DATA/orcl/onlinelog/redo14.log
Wed Aug 6 11:00:24 2014
Thread 2 advanced to log sequence 206 (LGWR switch)
Current log# 15 seq# 206 mem# 0: +DATA/orcl/onlinelog/redo15.log
Wed Aug 6 14:48:12 2014
Reconfiguration started (old inc 12, new inc 14)
List of nodes:
1
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Wed Aug 6 14:48:13 2014
LMS 0: 0 GCS shadows cancelled, 0 closed
Wed Aug 6 14:48:13 2014
LMS 1: 0 GCS shadows cancelled, 0 closed
Wed Aug 6 14:48:13 2014
LMS 3: 0 GCS shadows cancelled, 0 closed
Wed Aug 6 14:48:13 2014
LMS 2: 0 GCS shadows cancelled, 0 closed
...

告警日志并未给我们有用的信息，下来按老习惯直接分析CRSD日志了，具体的日志信息以及分析如下：

1.节点一日志分析

2014-08-06 14:13:57.778: [  CRSAPP][11095]32CheckResource error for ora.his-ora1.vip error code = 1
==>可以发现在14点13分57秒vip服务异常
2014-08-06 14:13:57.818: [  CRSRES][11095]32In stateChanged, ora.his-ora1.vip target is ONLINE
2014-08-06 14:13:57.819: [  CRSRES][11095]32ora.his-ora1.vip on his-ora1 went OFFLINE unexpectedly
==>集群将vip服务状态标记为offline
2014-08-06 14:13:57.820: [  CRSRES][11095]32StopResource: setting CLI values
2014-08-06 14:13:57.825: [  CRSRES][11095]32Attempting to stop `ora.his-ora1.vip` on member `his-ora1`
2014-08-06 14:13:58.418: [  CRSRES][11095]32Stop of `ora.his-ora1.vip` on member `his-ora1` succeeded.
==>集群停止ora1的vip服务
2014-08-06 14:13:58.419: [  CRSRES][11095]32ora.his-ora1.vip RESTART_COUNT=0 RESTART_ATTEMPTS=0
2014-08-06 14:13:58.423: [  CRSRES][11095]32ora.his-ora1.vip failed on his-ora1 relocating.
==>ora1.vip漂移失败
2014-08-06 14:13:58.520: [  CRSRES][11095]32StopResource: setting CLI values
2014-08-06 14:13:58.525: [  CRSRES][11095]32Attempting to stop `ora.his-ora1.LISTENER_HIS-ORA1.lsnr` on member `his-ora1`
==>由于裙带关系crs把listener资源也关闭了
2014-08-06 14:15:15.951: [  CRSRES][11095]32Stop of `ora.his-ora1.LISTENER_HIS-ORA1.lsnr` on member `his-ora1` succeeded.
==>同上
2014-08-06 14:15:16.022: [  CRSRES][11095]32Attempting to start `ora.his-ora1.vip` on member `his-ora2`
2014-08-06 14:15:17.882: [  CRSRES][11095]32Start of `ora.his-ora1.vip` on member `his-ora2` succeeded.
2014-08-06 14:15:24.997: [  CRSRES][11185]32startRunnable: setting CLI values

2.节点二日志分析

2014-07-14 17:35:59.469: [  CRSRES][11648]32Start of `ora.orcl.orcl2.inst` on member `his-ora2` succeeded.
2014-08-06 14:14:06.866: [  CRSAPP][11469]32CheckResource error for ora.his-ora2.vip error code = 1
==>同样可以发现在14点14分06秒vip服务异常，和节点1前后相差9秒
2014-08-06 14:14:06.879: [  CRSRES][11469]32In stateChanged, ora.his-ora2.vip target is ONLINE
2014-08-06 14:14:06.881: [  CRSRES][11469]32ora.his-ora2.vip on his-ora2 went OFFLINE unexpectedly
==>集群将ora2.vip服务状态标记为offline
2014-08-06 14:14:06.881: [  CRSRES][11469]32StopResource: setting CLI values
2014-08-06 14:14:06.886: [  CRSRES][11469]32Attempting to stop `ora.his-ora2.vip` on member `his-ora2`
2014-08-06 14:14:07.473: [  CRSRES][11469]32Stop of `ora.his-ora2.vip` on member `his-ora2` succeeded.
==>集群停止ora2.vip服务
2014-08-06 14:14:07.474: [  CRSRES][11469]32ora.his-ora2.vip RESTART_COUNT=0 RESTART_ATTEMPTS=0
2014-08-06 14:14:07.479: [  CRSRES][11469]32ora.his-ora2.vip failed on his-ora2 relocating.
==>ora2.vip漂移失败
2014-08-06 14:14:07.524: [  CRSRES][11469]32StopResource: setting CLI values
2014-08-06 14:14:07.528: [  CRSRES][11469]32Attempting to stop `ora.his-ora2.LISTENER_HIS-ORA2.lsnr` on member `his-ora2`
2014-08-06 14:15:16.008: [  CRSRES][11548]32startRunnable: setting CLI values
2014-08-06 14:15:24.982: [  CRSRES][11469]32Stop of `ora.his-ora2.LISTENER_HIS-ORA2.lsnr` on member `his-ora2` succeeded.
==>由于裙带关系crs把listener资源也关闭了
2014-08-06 14:15:25.013: [  CRSRES][11469]32Attempting to start `ora.his-ora2.vip` on member `his-ora1`
2014-08-06 14:15:27.911: [  CRSRES][11469]32Start of `ora.his-ora2.vip` on member `his-ora1` succeeded.

从以上的CRSD日志可以发现，故障的发生确实是因为VIP的异常offline导致listener也被offline导致；故障的发生时间点为14：13分后，两个节点前后vip服务出现异常，导致offline的产生，并且并未restart，vip在漂移失败后crs将LISTENER进程offline。由此可以判断出监听资源offline的根源为vip的原因导致，这与我在客户通电话时候获取的信息得出判断是一致的。

那么现在的问题就比较明显了，原因是vip异常offline导致，那么vip offline的根源是什么？而且是2个vip几乎同时都offline了，一般vip的offline的原因，我个人是个归纳如下：

1.不少bug会导致vip offline
2.网络波动导致ping timeout超时
3.网络故障，比如交换机或者网路，网卡问题
4.网关设错等

但是这里集群的日志信息已经没有其他的信息可以告诉我们其他有助于我们定位vip offline的根源了，下来从系统层面判断是否存在异常信息的告警，这里是AIX所有采用errpt -a的方式查找最新的报错，2个主机最新的错误信息都是2014年7月3号，离现在太久了，而且这期间并未发生类似的故障，基本初步排除系统方面的问题。那到底是不是网络的问题？还是bug的问题？
这个时候一方面可以对VIP服务采用crsctl debug log resource ora.his-ora2.vip:1的方式在两个节点上开启trace跟踪，然后对trace的日志进行分析，另一方面通过加大ping time out时间的方式来避免偶尔的网络波动导致的vip 资源进程offline的情况，这2方面的执行命令如下：

1.debug vip 进程

To increase the trace level, issue
crsctl debug log resource :1

To turn off this tracing use:
crsctl debug log res :0

2.延长vip检测time out时间

修改racgvip文件，将PING_TIMEOUT=" -c 1 -w 1"改为PING_TIMEOUT=" -c 1 -w 3"

日志方面已经没有可以给我们有帮助的信息了，此时电话用户告知目前的诊断情况并建议对网络方面进行排查，检测整体rac相关网络的波动异常情况，其次对当前的vip进程进行trace根据trace文件进行分析以及增加time out时间。庆幸的是正在准备做trace的时候，用户来电反应下午14：13分与rac相关的交换机发生failover，整个过程持续了一段时间，由此可以判断在此过程由于无法ping通导致vip offine，进而出现客户反应的现象。但是我还是不放心，给vip进程做了trace，等些时间再关掉再关掉检测trace。

在后续的检测中我也发现一个问题，那就是节点的priv网关和pub网关是一样的，在rac环境中我是不建议这种设置的，如下2图：
节点1公有网卡节点1私有网卡

Rac Listener offline问题分析

遭遇ora-00600 [6711]

错误如下：

 

Sat Jul 26 10:22:23 2014
Errors in file /oracle/admin/icdb/udump/icdb_ora_459112.trc:
ORA-00600: internal error code, arguments: [6711], [4257353], [1], [4255900], [0], [], [], []
Sat Jul 26 10:24:07 2014
Errors in file /oracle/admin/icdb/udump/icdb_ora_459112.trc:
ORA-00600: internal error code, arguments: [6711], [4257353], [1], [4255900], [0], [], [], []
Sat Jul 26 10:31:38 2014
Errors in file /oracle/admin/icdb/udump/icdb_ora_56557800.trc:
ORA-00600: 内部错误代码, 参数: [6711], [4257353], [1], [4255900], [0], [], [], []
Sat Jul 26 10:31:59 2014
Errors in file /oracle/admin/icdb/udump/icdb_ora_56557800.trc:
ORA-00600: 内部错误代码, 参数: [6711], [4257353], [1], [4255900], [0], [], [], []
Sat Jul 26 11:00:30 2014
Errors in file /oracle/admin/icdb/bdump/icdb_m000_56557774.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []
Sat Jul 26 11:01:00 2014
Errors in file /oracle/admin/icdb/udump/icdb_ora_57344088.trc:
ORA-00600: internal error code, arguments: [qertbFetchByRowID], [], [], [], [], [], [], []
Sat Jul 26 12:00:39 2014
Errors in file /oracle/admin/icdb/bdump/icdb_m000_63307938.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []
Sat Jul 26 12:06:47 2014
Thread 1 advanced to log sequence 32908 (LGWR switch)
  Current log# 1 seq# 32908 mem# 0: /database/icdb/redo01.log
Sat Jul 26 12:09:41 2014
Thread 1 advanced to log sequence 32909 (LGWR switch)
  Current log# 2 seq# 32909 mem# 0: /database/icdb/redo02.log
Sat Jul 26 12:13:06 2014
Thread 1 advanced to log sequence 32910 (LGWR switch)
  Current log# 3 seq# 32910 mem# 0: /database/icdb/redo03.log
Sat Jul 26 12:16:03 2014
Thread 1 advanced to log sequence 32911 (LGWR switch)
  Current log# 1 seq# 32911 mem# 0: /database/icdb/redo01.log
Sat Jul 26 12:18:58 2014
Thread 1 advanced to log sequence 32912 (LGWR switch)
  Current log# 2 seq# 32912 mem# 0: /database/icdb/redo02.log
Sat Jul 26 13:00:41 2014
Errors in file /oracle/admin/icdb/bdump/icdb_m000_7668146.trc:
ORA-00600: internal error code, arguments: [kcbz_check_objd_typ], [0], [0], [1], [], [], [], []
Sat Jul 26 13:00:44 2014
Errors in file /oracle/admin/icdb/bdump/icdb_m000_7668146.trc:
ORA-00600: internal error code, arguments: [13013], [5001], [9001], [12637871], [26], [12613723], [3], []

可以确认
ORA-00600: internal error code, arguments: [qertbFetchByRowID]
ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []以及为BUG

7329252 10.2.0.4.4, 10.2.0.5, 11.1.0.7.5, 11.2.0.1 ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE

 

剩下就是

[kcbz_check_objd_typ]， [13013]，[6711]

先分析6711的错误，根据对整体日志的分析过程如下：

[root@ludatou /]# cat alert_icdb.log | grep 6711 |wc -l
39
[root@ludatou /]# cat alert_icdb.log | grep 4257353 |wc -l
31
[root@ludatou /]# cat alert_icdb.log | grep 4255900 |wc -l
31
[root@ludatou /]# cat alert_icdb.log | grep -v 4257353 |wc -l
171179
[root@ludatou /]# cat alert_icdb.log | grep -v 4257353 |grep 6711 |wc -l
8
[root@ludatou /]# cat alert_icdb.log | grep -v 4257353 |grep 6711
Thread 1 cannot allocate new log, sequence 6711
Thread 1 advanced to log sequence 6711 (LGWR switch)
 Current log# 3 seq# 6711 mem# 0: /database/icdb/redo03.log
 Current log# 3 seq# 6711 mem# 0: /database/icdb/redo03.log
Thread 1 advanced to log sequence 16711 (LGWR switch)
 Current log# 1 seq# 16711 mem# 0: /database/icdb/redo01.log
Thread 1 advanced to log sequence 26711 (LGWR switch)
 Current log# 2 seq# 26711 mem# 0: /database/icdb/redo02.log

通过以上的分析我们可以发现，6711的错误集中的对应数据块都为
[6711], [4257353], [1], [4255900]

SQL> SELECT dbms_utility.data_block_address_block(4257353) "BLOCK",
 2 dbms_utility.data_block_address_file(4257353) "FILE" from dual;

 BLOCK FILE
---------- ----------
 63049 1

SQL> SELECT dbms_utility.data_block_address_block(4255900) "BLOCK",
 2 dbms_utility.data_block_address_file(4255900) "FILE" from dual;

 BLOCK FILE
---------- ----------
 61596 1

set linesize 200;
select segment_name, segment_type
from dba_extents
where relative_fno = 1
and (663049 between block_id and block_id + blocks or
61596 between block_id and block_id + blocks);
Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li |

遭遇ORA-00603/27102|Linux-x86_64 Error: 12

这个问题来的比较急，来邮件时候是下午4点多，但是数据库运行是正常的，从告警日志上看是进程无法获取内存了，导致客户端进程宕掉。

27102错误一般是oracle用户的系统资源限制设置过小导致，但是这里的情况却是不一样。

具体先看如下告警日志：
 

Fri Jul 25 12:51:08 CST 2014
Thread 1 advanced to log sequence 5862 (LGWR switch)
  Current log# 3 seq# 5862 mem# 0: +DG1/wasudb/onlinelog/group_3.263.842714473
Fri Jul 25 12:51:10 CST 2014
ARC1: Standby redo logfile selected for thread 1 sequence 5861 for destination LOG_ARCHIVE_DEST_2
Fri Jul 25 12:51:57 CST 2014
Thread 1 cannot allocate new log, sequence 5863
Checkpoint not complete
  Current log# 3 seq# 5862 mem# 0: +DG1/wasudb/onlinelog/group_3.263.842714473
Fri Jul 25 12:51:59 CST 2014
Thread 1 advanced to log sequence 5863 (LGWR switch)
  Current log# 1 seq# 5863 mem# 0: +DG1/wasudb/onlinelog/group_1.261.842714471
Fri Jul 25 12:52:02 CST 2014
ARC1: Standby redo logfile selected for thread 1 sequence 5862 for destination LOG_ARCHIVE_DEST_2
Fri Jul 25 13:01:45 CST 2014
Errors in file /oracle/admin/wasudb/udump/wasudb_ora_13870.trc:
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 108
Additional information: 3080192
Fri Jul 25 13:01:48 CST 2014
Errors in file /oracle/admin/wasudb/udump/wasudb_ora_13870.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 108
Additional information: 3080192

告警的分析在第一段话就已经带出，在此时我们通过ulimitd -a查看oracle用户的分配信息，都是非常大的，不存在过小的设置。
在这种情况下非常有必要看下这个ora_13870.trc文件，此文件部分信息如下：

/oracle/admin/wasudb/udump/wasudb_ora_13870.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:	Linux
Node name:	localhost
Release:	3.8.13-16.2.1.el6uek.x86_64
Version:	#1 SMP Thu Nov 7 17:01:44 PST 2013
Machine:	x86_64
Instance name: wasudb
Redo thread mounted by this instance: 1
Oracle process number: 51
Unix process pid: 13870, image: oracle@localhost

*** 2014-07-25 13:01:44.015
*** SERVICE NAME:(SYS$USERS) 2014-07-25 13:01:43.995
*** SESSION ID:(6209.59205) 2014-07-25 13:01:43.995
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
81% 3328 MB, 213622 chunks: "pmuccst: adt/record       "
         koh-kghu call   ds=0x7f8e128632f8  dsprt=0x6ae9740
13%  544 MB, 8868 chunks: "permanent memory          "  SQL
         sort subheap    ds=0x7f8e121f7a00  dsprt=0x7f8e12891058
 5%  209 MB, 13443 chunks: "pmucalm coll              "
         koh-kghu call   ds=0x7f8e128632f8  dsprt=0x6ae9740
 0% 7467 KB, 8948 chunks: "free memory               "
         session heap    ds=0x7f8e12866290  dsprt=0x6aed2c0
 0% 1284 KB,  22 chunks: "permanent memory          "
         pga heap        ds=0x6ae9740  dsprt=(nil)
 0%  424 KB,   6 chunks: "free memory               "
         top call heap   ds=0x6aed0a0  dsprt=(nil)
 0%  331 KB,  82 chunks: "kxsFrame4kPage            "
         session heap    ds=0x7f8e12866290  dsprt=0x6aed2c0
 0%  274 KB,  10 chunks: "static frame of inst      "
         koh-kghu sessi  ds=0x7f8e11fabe48  dsprt=0x7f8e12866290
 0%  224 KB,   7 chunks: "kxsFrame32kPage           "
         session heap    ds=0x7f8e12866290  dsprt=0x6aed2c0
 0%  128 KB,   1 chunk : "free memory               "
         KSFD vec heap   ds=0x6aeed80  dsprt=0x6ae9740

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
4103 MB total:
  4101 MB commented, 1286 KB permanent
   537 KB free (0 KB in empty extents),
    3546 MB,   3 heaps:   "koh-kghu call  "            51 KB free held
     554 MB,   1 heap:    "session heap   "
------------------------------------------------------
Summary of subheaps at depth 1
4091 MB total:
  4084 MB commented, 87 KB permanent
  7603 KB free (132 KB in empty extents),
     2 KB uncommented freeable with mark,     3328 MB, 213622 chunks:  "pmuccst: adt/record       "
     552 MB,   1 heap:    "kxs-heap-w     "            7408 KB free held

=========================================
REAL-FREE ALLOCATOR DUMP FOR THIS PROCESS
-----------------------------------------

Dump of Real-Free Memory Allocator Heap [0x7f8e1283e000]
mag=0xfefe0001 flg=0x5000003 fds=0x7 blksz=65536
blkdstbl=0x7f8e1283e010, iniblk=68608 maxblk=262144 numsegs=115
In-use num=65638 siz=8978432, Freeable num=0 siz=0, Free num=1 siz=6619136

==========================================
INSTANCE-WIDE PRIVATE MEMORY USAGE SUMMARY
------------------------------------------

Dumping Work Area Table (level=1)
=====================================

  Global SGA Info
  ---------------

    global target:    40000 MB
    auto target:      31163 MB
    max pga:           2048 MB
    pga limit:         4096 MB
    pga limit known:  0
    pga limit errors:     0

    pga inuse:         6534 MB
    pga alloc:         7384 MB
    pga freeable:       707 MB
    pga freed:        5776451 MB
    pga to free:          0 %

    pga auto:          1160 MB
    pga manual:           0 MB

    pga alloc  (max): 15183 MB
    pga auto   (max):  4383 MB
    pga manual (max):    10 MB

    # workareas     :     0
    # workareas(max):   115



================================
PER-PROCESS PRIVATE MEMORY USAGE
--------------------------------

Private memory usage per Oracle process

-------------------------
Top 10 processes:
-------------------------
(percentage is of 8065 MB total allocated memory)
51% pid 51: 4102 MB used of 4105 MB allocated  < = CURRENT PROC
 7% pid 101: 536 MB used of 537 MB allocated
 3% pid 127: 52 MB used of 211 MB allocated (158 MB freeable)
 2% pid 94: 37 MB used of 138 MB allocated (101 MB freeable)
 1% pid 215: 101 MB used of 120 MB allocated (18 MB freeable)
 1% pid 216: 101 MB used of 119 MB allocated (18 MB freeable)
 1% pid 254: 101 MB used of 119 MB allocated (18 MB freeable)
 1% pid 278: 101 MB used of 119 MB allocated (18 MB freeable)
 1% pid 155: 100 MB used of 116 MB allocated (15 MB freeable)
 1% pid 156: 111 MB used of 111 MB allocated

-------------------------
All processes:
-------------------------
(session detail when over 403 MB allocated)

pid 2: 295 KB used of 374 KB allocated
pid 3: 294 KB used of 374 KB allocated
pid 4: 388 KB used of 502 KB allocated
pid 5: 8205 KB used of 14 MB allocated (3136 KB freeable)
pid 6: 8409 KB used of 13 MB allocated (2112 KB freeable)
pid 7: 8356 KB used of 15 MB allocated (4160 KB freeable)
....................（省略很多）

通过trace中的信息我们可以看到PER-PROCESS PRIVATE MEMORY USAGE的TOP 10 process以及all process的信息，其中PID为51的进程（TOP 1）马上就入了大头的法眼~进程开销PGA已经4个G了，基本可以联想到肯定是超大DML事务带有排序等没有批量提交导致把内存撑爆！经过检查发现是一个存储过程在跑大量数据的处理，验证了我的判断，协助客户发给应用开发处理，并建议缩小事务，尽量采用批量的提交方式。

从以上也可以看出，硬件资源是有限的，即使给oracle用户分配的资源再多，只要应用开发设计得不合理，你永远都不会满足这应用的需求！

Database Dataguard Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li | |   遭遇ORA-00603/27102|Linux-x86_64 Error: 12

2014年7月最新的PSU和CPU补丁信息

关于数据库的补丁需要注意的地方：

 1.12.1.0.1.4 PSU依然是个non-composite的补丁

 2.没有关于Client-only installations 的新补丁发布

Patch Information 12.1.0.1.4 Comments
Final Patch –
PSU On-Request platforms

Table 8 describes the available patches for Oracle Database 12.1.0.1.4.

Table 8 Patch Availability for Oracle Database 12.1.0.1.4

Product Home Patch Advisory Number Comments
Oracle Database home Database 12.1.0.1.4 PSU Patch 18522516, orGI 12.1.0.1.4 PSU Patch 18705901 for Linux x86-64, Solaris X64 & Solaris SPARC, or

GI 12.1.0.1.4 PSU Patch 18705972 for AIX, HP IA & zLinux

Microsoft Windows 32-Bit & x86-64 BP 11 Patch 19062327, or later;

Quarterly Full Stack download for Exadata (July 2014) 12.1.0.1 BP Patch 19069261

 CVE-2013-3751, CVE-2013-3774, CVE-2014-4236, CVE-2014-4237, CVE-2014-4245 GI 12.1.0.1.4 PSU Patch 18705901 is applicable to Exadata database servers running Oracle Database 12.1.0.1. For more information, see Note 888828.1GI 12.1.0.1.4 PSU Patch 18705972 for AIX, HP IA & zLinux only contains DB PSU 12.1.0.1.4 sub-patch and no clusterware sub-patches

3.1.4.3 Oracle Database 11.2.0.4

Table 9 describes the Error Correction information for Oracle Database 11.2.0.4.

Table 9 Error Correction information for Oracle Database 11.2.0.4

Patch Information 11.2.0.4 Comments
Final Patch January 2018
SPU On-Request platforms HP-UX PA RISCIBM: Linux on System Z

32-bit client-only platforms except Linux x86
PSU On-Request platforms 32-bit client-only platforms except Linux x86

Table 10 describes the available patches for Oracle Database 11.2.0.4.

Table 10 Patch Availability for Oracle Database 11.2.0.4

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.2.0.4 SPU Patch 18681862, orDatabase 11.2.0.4.3 PSU Patch 18522509, or

GI 11.2.0.4.3 PSU Patch 18706472, or

Microsoft Windows (32-Bit) & x64 (64-Bit) BP 7 Patch 18842982, or later;

Quarterly Database Patch for Exadata (July 2014) 11.2.0.4.9 BP Patch 18840215, or

Quarterly Full Stack download for Exadata (July 2014) BP Patch 19067488

 CVE-2014-4236, CVE-2014-4237, CVE-2014-4245

3.1.4.4 Oracle Database 11.2.0.3

Table 11 describes the Error Correction information for Oracle Database 11.2.0.3.

Table 11 Error Correction information for Oracle Database 11.2.0.3

Patch Information 11.2.0.3 Comments
Final Patch July 2015
SPU On-Request platforms HP-UX PA RISCIBM: Linux on System Z

32-bit client-only platforms except Linux x86
PSU On-Request platforms 32-bit client-only platforms except Linux x86

Table 12 describes the available patches for Oracle Database 11.2.0.3.

Table 12 Patch Availability for Oracle Database 11.2.0.3

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.2.0.3 SPU Patch 18681866, orDatabase 11.2.0.3.11 PSU Patch 18522512, or

GI 11.2.0.3.11 PSU Patch 18706488, or

Quarterly Database Patch for Exadata (July 2014) 11.2.0.3.24 BP Patch 18835772, or

Quarterly Full Stack download for Exadata (July 2014) 11.2.0.3 BP Patch 19067489, or

Microsoft Windows 32-Bit BP 32 Patch 18940193, orlater

Microsoft Windows x86-64 BP 32 Patch 18940194, orlater

 CVE-2014-4245
Oracle Database home CPU Patch 13705478 Released April 2012 OC4J 10.1.3.4 one-off patch (Special OPatch needed, see README)

3.1.4.5 Oracle Database 11.1.0.7

Table 13 describes the Error Correction information for Oracle Database 11.1.0.7.

Table 13 Error Correction information for Oracle Database 11.1.0.7

Patch Information 11.1.0.7 Comments
Final Patch July 2015
SPU On-Request platforms –
PSU On-Request platforms –

Table 14 describes the available patches for Oracle Database 11.1.0.7.

Table 14 Patch Availability for Oracle Database 11.1.0.7

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.1.0.7 SPU Patch 18681875, orDatabase 11.1.0.7.20 PSU Patch 18522513, or

Microsoft Windows (32-Bit) BP 57 Patch 18944207, orlater

Microsoft Windows x86-64 BP 57 Patch 18944208, orlater

 CVE-2014-4245 Patches also applicable to Fusion Middleware 11.1.1.x and 11.1.2.x installations
Oracle Database home OHT BP Patch 16801095 Released July 2013 OHT BP 10.1.3.5 for DB Control
Oracle Database home CPU Patch 13705478 Released April 2012 OC4J 10.1.3.3 one-off patch (Special OPatch needed, see README)
Oracle CRS home CRS 11.1.0.7.7 PSU Patch 11724953 Released April 2011 Non-security content only
Oracle Database home CPU Patch 9288120 Released April 2011 Database UIXFor Oracle Secure Enterprise Search 11.1.2.x installations, follow the instructions in Note 1359600.1
Oracle Database home CPU Patch 10073948 Released April 2011 Enterprise Manager Database Control UIXNot applicable to Oracle Secure Enterprise Search 11.1.2.x
Oracle Database home CPU Patch 11738232 Released April 2011 Warehouse BuilderNot applicable to Oracle Secure Enterprise Search 11.1.2.x

Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li |

遭遇ORA-3927错误的不规范处理操作

客户早上碰到问题，我正好检查下系统，在告警日志中碰到3927错误，大致如下：

Thu Jul 17 09:53:13 2014
ORA-1654: unable to extend index DYMPOPSA.IDX_P17_TB_SP_LOG by 8192 in tablespace               DYMPOPS
Thu Jul 17 09:53:13 2014
ORA-1654: unable to extend index DYMPOPSA.IDX_P17_TB_SP_LOG by 8192 in tablespace               DYMPOPS
Thu Jul 17 09:53:18 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA2.dbf'
RESIZE 500M
Thu Jul 17 09:53:18 2014
ORA-3297 signalled during: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA2.dbf'
RESIZE 500M...
Thu Jul 17 09:53:28 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 500M
Thu Jul 17 09:53:28 2014
ORA-3297 signalled during: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 500M...
Thu Jul 17 09:53:45 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 2000M
Thu Jul 17 09:53:45 2014
Completed: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 2000M
Thu Jul 17 09:54:12 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 3000M

发现在17号表空间DYMPOPS满了，有人尝试通过resize的方式去扩大datafile，从500M开始一直尝试到3000m,终于成功了。其中在小于3000M时候，系统报错如下：

ORA-3297 signalled during: ALTER DATABASE DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf' RESIZE *M...

通过以上可以看出几个问题：

1.首先ORA-3297的错误是告诉你file contains used data beyond requested RESIZE value，意思是实际数据文件使用的数据量要大于你所resize的指定值，所以最好在做这个操作的时候，最好先查一下dba_free_space，所要更改的这个数据文件的一些信息，比如实际使用数据量是多少等；

2.其次日志中显示是在早上9点多时候执行操作，这个点对这个系统来讲是高峰期，在这个时候需要考虑整体的资源使用情况，一般不建议在高峰时间resize datafile，建议通过增加datafile的方式来处理ORA-1654表空间空间不足的情况.

如果要问第二个问题为什么？请考虑下resize datafile的原理以及系统处理高负荷状况下resize datafile可能造成的对系统的影响.

Database Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li | |   ora 1654 ora 3297