Applying online patch on 11gr2

2011/02/22 by 1 Comment

在Oracle 11g中提出了online patch(也叫hot patch)的特性；Hot patching允许我们在实例始终在线的情况下安装，启用或禁用一个修复补丁或者诊断补丁。针对7*24在线的业务系统，hot patch为我们提供了一条既能避免当机时间而又可以实施补丁的途径。在Oracle 11g中我们可以使用Opatch命令行工具针对任意数据库实施在线补丁(前提是该补丁是一个hot patch)。一般来说在线补丁(hot patches)只能是那些代码修改范围小且复杂度很低的补丁，举例来说它们往往是一些诊断补丁(diagnostic patches)或者小bug的修复(small bug fixes)。值得注意的是hot patching将需要消耗额外的内存，决定其消耗内存数量的因素是:1.补丁本身的大小,2.实例中的进程总数；举例来说某个补丁的大小正好为一个OS page的大小(一般为4kB)，那么当实例中运行的进程总数为1000时，则该hot patching所额外消耗的内存总数为4kB*1000=4MB。

hot patches与常规Conventional patches对比具有可在线实施和安装快的特性，如下图:
online patching

在实际生产环境中，相信没有多少朋友实施过hot patching，一来国内目前还没有普及11g的使用，二来hot patching的数量在所有interim patch中只占极少数；一直以来都想写这样一篇关于hot patching的博文，唯一妨碍我写作的问题是在11.2.0.1下找不到可实施的online interim patch；以MOS->patches&upgrade目前的分类我们很难找出某个base release下可用的hot patch，当然这并不妨碍补丁专栏的使用。为了这个令人郁闷的问题，我特意去提交了一个Service Request，得到的回复:

I have tried to find the patches which support online patching on 11.2.0.1 version,
but I also can not find them because there are too many patches and there is no catalog for the patches
which support online patching, and I can only check the patch readme to confirm whether that patch supports online patching.

I found one patch which supports online patching, but this patch is for 11.2.0.2 version.
The patch no. is 10188727.

Sorry for the inconvenience brought to you. Hope the above update can help you.
If the above patch is not what you want, then please update the SR and I will continue for your issue.

这其中提到的patch 10188727，可以从Note<RDBMS Online Patching Aka Hot Patching [ID 761111.1]>中找到，另外一个可找到的hot patch是11.1.0.6上的6198642<DUMMY PATCH FOR TESTING DB11 PATCHING>，不过很可惜该补丁只有Linux x86一个平台版本的。所以我不得不先将11.2.0.1的测试库升级到了11.2.0.2上:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

[maclean@rh2 OPatch]$ ps -ef|grep pmon|grep -v grep
maclean  22481     1  0 19:19 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 OPatch]$ pmap -d 22481
22481:   ora_pmon_PROD
Address           Kbytes Mode  Offset           Device    Mapping
0000000000400000  180232 r-x-- 0000000000000000 008:00002 oracle
000000000b602000    1820 rwx-- 000000000b002000 008:00002 oracle
000000000b7c9000     300 rwx-- 000000000b7c9000 000:00000   [ anon ]
000000000dbef000     436 rwx-- 000000000dbef000 000:00000   [ anon ]
0000000060000000 2050048 rwxs- 0000000000000000 000:00009   [ shmid=0x550001 ]
0000003e09a00000     112 r-x-- 0000000000000000 008:00001 ld-2.5.so
0000003e09c1b000       4 r-x-- 000000000001b000 008:00001 ld-2.5.so
0000003e09c1c000       4 rwx-- 000000000001c000 008:00001 ld-2.5.so
0000003e09e00000    1336 r-x-- 0000000000000000 008:00001 libc-2.5.so
0000003e09f4e000    2044 ----- 000000000014e000 008:00001 libc-2.5.so
0000003e0a14d000      16 r-x-- 000000000014d000 008:00001 libc-2.5.so
0000003e0a151000       4 rwx-- 0000000000151000 008:00001 libc-2.5.so
0000003e0a152000      20 rwx-- 0000003e0a152000 000:00000   [ anon ]
0000003e0a200000     520 r-x-- 0000000000000000 008:00001 libm-2.5.so
0000003e0a282000    2044 ----- 0000000000082000 008:00001 libm-2.5.so
0000003e0a481000       4 r-x-- 0000000000081000 008:00001 libm-2.5.so
0000003e0a482000       4 rwx-- 0000000000082000 008:00001 libm-2.5.so
0000003e0a600000       8 r-x-- 0000000000000000 008:00001 libdl-2.5.so
0000003e0a602000    2048 ----- 0000000000002000 008:00001 libdl-2.5.so
0000003e0a802000       4 r-x-- 0000000000002000 008:00001 libdl-2.5.so
0000003e0a803000       4 rwx-- 0000000000003000 008:00001 libdl-2.5.so
0000003e0aa00000      88 r-x-- 0000000000000000 008:00001 libpthread-2.5.so
0000003e0aa16000    2044 ----- 0000000000016000 008:00001 libpthread-2.5.so
0000003e0ac15000       4 r-x-- 0000000000015000 008:00001 libpthread-2.5.so
0000003e0ac16000       4 rwx-- 0000000000016000 008:00001 libpthread-2.5.so
0000003e0ac17000      16 rwx-- 0000003e0ac17000 000:00000   [ anon ]
0000003e0ae00000      28 r-x-- 0000000000000000 008:00001 librt-2.5.so
0000003e0ae07000    2048 ----- 0000000000007000 008:00001 librt-2.5.so
0000003e0b007000       4 r-x-- 0000000000007000 008:00001 librt-2.5.so
0000003e0b008000       4 rwx-- 0000000000008000 008:00001 librt-2.5.so
0000003e0da00000      84 r-x-- 0000000000000000 008:00001 libnsl-2.5.so
0000003e0da15000    2044 ----- 0000000000015000 008:00001 libnsl-2.5.so
0000003e0dc14000       4 r-x-- 0000000000014000 008:00001 libnsl-2.5.so
0000003e0dc15000       4 rwx-- 0000000000015000 008:00001 libnsl-2.5.so
0000003e0dc16000       8 rwx-- 0000003e0dc16000 000:00000   [ anon ]
00002abec920e000       8 rwx-- 00002abec920e000 000:00000   [ anon ]
00002abec9210000       4 r-x-- 0000000000000000 008:00002 libodmd11.so
00002abec9211000    1024 ----- 0000000000001000 008:00002 libodmd11.so
00002abec9311000       4 rwx-- 0000000000001000 008:00002 libodmd11.so
00002abec9312000     360 r-x-- 0000000000000000 008:00002 libcell11.so
00002abec936c000    1020 ----- 000000000005a000 008:00002 libcell11.so
00002abec946b000      36 rwx-- 0000000000059000 008:00002 libcell11.so
00002abec9474000       4 rwx-- 00002abec9474000 000:00000   [ anon ]
00002abec9475000     848 r-x-- 0000000000000000 008:00002 libskgxp11.so
00002abec9549000    1024 ----- 00000000000d4000 008:00002 libskgxp11.so
00002abec9649000       8 rwx-- 00000000000d4000 008:00002 libskgxp11.so
00002abec9665000       4 rwx-- 00002abec9665000 000:00000   [ anon ]
00002abec9666000    2580 r-x-- 0000000000000000 008:00002 libnnz11.so
00002abec98eb000    1020 ----- 0000000000285000 008:00002 libnnz11.so
00002abec99ea000     264 rwx-- 0000000000284000 008:00002 libnnz11.so
00002abec9a2c000       8 rwx-- 00002abec9a2c000 000:00000   [ anon ]
00002abec9a2e000      96 r-x-- 0000000000000000 008:00002 libclsra11.so
00002abec9a46000    1020 ----- 0000000000018000 008:00002 libclsra11.so
00002abec9b45000       4 rwx-- 0000000000017000 008:00002 libclsra11.so
00002abec9b46000       4 rwx-- 00002abec9b46000 000:00000   [ anon ]
00002abec9b47000     136 r-x-- 0000000000000000 008:00002 libdbcfg11.so
00002abec9b69000    1020 ----- 0000000000022000 008:00002 libdbcfg11.so
00002abec9c68000       8 rwx-- 0000000000021000 008:00002 libdbcfg11.so
00002abec9c6a000    6832 r-x-- 0000000000000000 008:00002 libhasgen11.so
00002abeca316000    1020 ----- 00000000006ac000 008:00002 libhasgen11.so
00002abeca415000     136 rwx-- 00000000006ab000 008:00002 libhasgen11.so
00002abeca437000      24 rwx-- 00002abeca437000 000:00000   [ anon ]
00002abeca43d000       8 r-x-- 0000000000000000 008:00002 libskgxn2.so
00002abeca43f000    1020 ----- 0000000000002000 008:00002 libskgxn2.so
00002abeca53e000       4 rwx-- 0000000000001000 008:00002 libskgxn2.so
00002abeca53f000       4 rwx-- 00002abeca53f000 000:00000   [ anon ]
00002abeca540000     656 r-x-- 0000000000000000 008:00002 libocr11.so
00002abeca5e4000    1020 ----- 00000000000a4000 008:00002 libocr11.so
00002abeca6e3000      12 rwx-- 00000000000a3000 008:00002 libocr11.so
00002abeca6e6000     628 r-x-- 0000000000000000 008:00002 libocrb11.so
00002abeca783000    1024 ----- 000000000009d000 008:00002 libocrb11.so
00002abeca883000       8 rwx-- 000000000009d000 008:00002 libocrb11.so
00002abeca885000      44 r-x-- 0000000000000000 008:00002 libocrutl11.so
00002abeca890000    1020 ----- 000000000000b000 008:00002 libocrutl11.so
00002abeca98f000       4 rwx-- 000000000000a000 008:00002 libocrutl11.so
00002abeca990000       4 rwx-- 00002abeca990000 000:00000   [ anon ]
00002abeca991000       4 r-x-- 0000000000000000 008:00001 libaio.so.1.0.1
00002abeca992000    2044 ----- 0000000000001000 008:00001 libaio.so.1.0.1
00002abecab91000       4 rwx-- 0000000000000000 008:00001 libaio.so.1.0.1
00002abecab92000      16 rwx-- 00002abecab92000 000:00000   [ anon ]
00002abecab96000      20 r-x-- 0000000000000000 008:00001 libnuma.so.1
00002abecab9b000    2044 ----- 0000000000005000 008:00001 libnuma.so.1
00002abecad9a000       4 rwx-- 0000000000004000 008:00001 libnuma.so.1
00002abecad9b000    1280 rwx-- 00002abecad9b000 000:00000   [ anon ]
00002abecaef5000      40 r-x-- 0000000000000000 008:00001 libnss_files-2.5.so
00002abecaeff000    2044 ----- 000000000000a000 008:00001 libnss_files-2.5.so
00002abecb0fe000       4 r-x-- 0000000000009000 008:00001 libnss_files-2.5.so
00002abecb0ff000       4 rwx-- 000000000000a000 008:00001 libnss_files-2.5.so
00002abecb100000    1700 rwx-- 00002abecb100000 000:00000   [ anon ]
00002abecb2a9000      28 rwx-- 0000000000000000 000:00011 zero
00002abecb2b0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2c0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2d0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2e0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb2f0000      64 rwx-- 0000000000000000 000:00011 zero
00002abecb300000     164 rwx-- 0000000000057000 000:00011 zero
00002abecb329000       8 rwx-- 00002abecb329000 000:00000   [ anon ]
00002abecb32b000       4 rwxs- 0000000000000000 008:00002 hc_PROD.dat
00002abecb32c000      40 r-x-- 0000000000000000 008:00002 libnque11.so
00002abecb336000    1020 ----- 000000000000a000 008:00002 libnque11.so
00002abecb435000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002abecb436000    1048 rwx-- 00002abecb436000 000:00000   [ anon ]
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2291488K    writeable/private: 7840K    shared: 2050052K

[maclean@rh2 ~]$  cd $ORACLE_HOME/OPatch
[maclean@rh2 OPatch]$ unzip p10188727_112020_Linux-x86-64.zip
Archive:  p10188727_112020_Linux-x86-64.zip
   creating: 10188727/
   creating: 10188727/files/
   creating: 10188727/files/lib/
   creating: 10188727/files/lib/libserver11.a/
  inflating: 10188727/files/lib/libserver11.a/kkopq.o
   creating: 10188727/etc/
   creating: 10188727/etc/config/
  inflating: 10188727/etc/config/inventory.xml
  inflating: 10188727/etc/config/actions.xml
  inflating: 10188727/etc/config/deploy.xml
   creating: 10188727/etc/xml/
  inflating: 10188727/etc/xml/GenericActions.xml
  inflating: 10188727/etc/xml/ShiphomeDirectoryStructure.xml
  inflating: 10188727/README.txt
   creating: 10188727/online/
   creating: 10188727/online/files/
   creating: 10188727/online/files/hpatch/
  inflating: 10188727/online/files/hpatch/bug10188727.pch
   creating: 10188727/online/etc/
   creating: 10188727/online/etc/config/
  inflating: 10188727/online/etc/config/inventory.xml
  inflating: 10188727/online/etc/config/actions.xml
  inflating: 10188727/online/etc/config/deploy.xml
   creating: 10188727/online/etc/xml/
  inflating: 10188727/online/etc/xml/GenericActions.xml
  inflating: 10188727/online/etc/xml/ShiphomeDirectoryStructure.xml 

[maclean@rh2 OPatch]$ opatch query 10188727 -all
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/product/11.2.0/dbhome_2
Central Inventory : /s01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/product/11.2.0/dbhome_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_20-05-21PM.log

Patch history file: /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch_history.txt

--------------------------------------------------------------------------------
 Patch created on 2 Dec 2010, 01:44:13 hrs PST8PDT
 Need to shutdown Oracle instances: true
 Patch is roll-backable: true
 Patch is a "Patchset Update": false
 Patch is a rolling patch: true
 Patch has sql related actions: false
 Patch is an online patch: false
 Patch is a portal patch: false
 Patch is an "auto-enabled" patch: false

 List of platforms supported:
   226: Linux x86-64

 List of bugs to be fixed:
   10188727: AFTER UPGRADING TO 11.2.0.2 SOME SQLS FAIL WITH ORA-7445 [KKEIDC()+180] ERROR

 This patch is a "singleton" patch.

 This patch belongs to the "db" product family 

 List of executables affected:
   ORACLE_HOME/bin/oracle

 List of optional components:
   oracle.rdbms:  11.2.0.2.0

 List of optional actions:
   Update /s01/product/11.2.0/dbhome_2/lib/libserver11.a with /kkopq.o
   cd /s01/product/11.2.0/dbhome_2/rdbms/lib
     ; make -f ins_rdbms.mk ioracle ORACLE_HOME=/s01/product/11.2.0/dbhome_2

  Possible XML representation of the patch:

     10188727

--------------------------------------------------------------------------------
OPatch succeeded.

[maclean@rh2 OPatch]$ ./opatch query -is_online_patch 10188727
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/product/11.2.0/dbhome_2
Central Inventory : /s01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/product/11.2.0/dbhome_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_19-45-33PM.log

Patch history file: /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch_history.txt

--------------------------------------------------------------------------------
 Patch is an online patch: false

OPatch succeeded.

/* 虽然Opatch返回"online patch: false"，但实际上这是一个online patch,
    造成以上问题的原因可能是Opatch版本低 */

[maclean@rh2 OPatch]$ tree 10188727
10188727
|-- README.txt
|-- etc
|   |-- config
|   |   |-- actions.xml
|   |   |-- deploy.xml
|   |   `-- inventory.xml
|   `-- xml
|       |-- GenericActions.xml
|       `-- ShiphomeDirectoryStructure.xml
|-- files
|   `-- lib
|       `-- libserver11.a
|           `-- kkopq.o
`-- online
    |-- etc
    |   |-- config
    |   |   |-- actions.xml
    |   |   |-- deploy.xml
    |   |   `-- inventory.xml
    |   `-- xml
    |       |-- GenericActions.xml
    |       `-- ShiphomeDirectoryStructure.xml
    `-- files
        `-- hpatch
            `-- bug10188727.pch

/* 可以从以上目录结构中看到包含了online子目录，我们可以直接观察其inventory.xml信息文件 */

[maclean@rh2 OPatch]$ cat 10188727/online/etc/config/inventory.xml |grep instance
 <instance_shutdown>false</instance_shutdown>
 <instance_shutdown_message></instance_shutdown_message>

/* 以上instance_shutdown为false说明其可以作为online patch实施 */

[maclean@rh2 OPatch]$ cd 10188727

/* opatch online patching的具体语法如下 */
opatch apply online -connectString  <SID>:<USERNAME>:<PASSWORD>:<NODE1>, \
<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,... 

[maclean@rh2 10188727]$ opatch apply online -connectString PROD:sys:password
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/product/11.2.0/dbhome_2
Central Inventory : /s01/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/product/11.2.0/dbhome_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_19-49-44PM.log

Patch history file: /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch_history.txt

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.
ApplySession applying interim patch '10188727' to OH '/s01/product/11.2.0/dbhome_2'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '10188727' for restore. This might take a while...
Backing up files affected by the patch '10188727' for rollback. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be installed on active database instances.
Installing and enabling the online patch 'bug10188727.pch', on database 'PROD'.

ApplySession adding interim patch '10188727' to inventory

Verifying the update...
Inventory check OK: Patch ID 10188727 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10188727 are present in Oracle Home.
OPatch succeeded.

[maclean@rh2 OPatch]$ opatch lsinventory -detail|tail -21
Interim patches (1) :

Patch (online) 10188727: applied on Thu Feb 17 19:49:52 CST 2011
Unique Patch ID:  13202318
   Created on 2 Dec 2010, 01:44:15 hrs PST8PDT
   Bugs fixed:
     10188727
   Files Touched:
     bug10188727.pch --> ORACLE_HOME/hpatch/bug10188727.pch
   Instances Patched:
     PROD
   Patch Location in Inventory:
     /s01/product/11.2.0/dbhome_2/inventory/oneoffs/10188727
   Patch Location in Storage area:
     /s01/product/11.2.0/dbhome_2/.patch_storage/10188727_Dec_2_2010_01_44_15

告警日志alert.log中的信息:
Patch file bug10188727.pch is out of sync with oracle binary; performing fixup
Patch file bug10188727.pch has been synced with oracle binary
Patch bug10188727.pch Installed - Update #1
Patch bug10188727.pch Enabled - Update #2

[maclean@rh2 OPatch]$ ps -ef|grep pmon|grep -v grep
maclean  22481     1  0 19:19 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 OPatch]$ pmap -d 22481|tail -10
00002abecb435000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002abecb436000    1048 rwx-- 00002abecb436000 000:00000   [ anon ]
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 再次观察pmon进程的内存信息，可以看到pmap输出中多出了2个小的oracle正文镜像和
    名为bug10188727.so的共享库文件，而该后台进程的private memory由原来的7840k上升到7848k，
    实际增幅为8k */

/* 此外可以通过oradebug命令将该online patch禁用，虽然并不推荐这样做 */

SQL> oradebug patch disable  bug10188727.pch;
Statement processed.

SQL>  oradebug patch disable bug10188727.pch;
Patch file already disabled

[maclean@rh2 hpatch]$ pmap -d 22481|tail -8
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 但disable掉online patch并不会导致在线补丁额外消耗的内存被回收 */

/* 当然我们还可以很方便地启用它 */

SQL> oradebug patch enable bug10188727.pch;
Statement processed.
SQL>  oradebug patch enable bug10188727.pch;
Patch file already enabled

[maclean@rh2 ~]$ cd $ORACLE_HOME/hpatch

[maclean@rh2 hpatch]$ ls -l
total 368
-rw-r--r-- 1 maclean oinstall 177874 Feb 17 19:49 bug10188727.pch
-rwx------ 1 maclean oinstall      1 Feb 17 19:49 bug10188727.pchPROD.fixup
-rwx------ 1 maclean oinstall 176850 Feb 17 19:49 bug10188727.so
-rw------- 1 maclean oinstall    712 Feb 17 20:13 orapatchPROD.cfg

/* 注意不要在实例启动时删除以上hpatch目录及目录下任何文件，这可能导致instance出现意外 */

[maclean@rh2 hpatch]$ cd $ORACLE_HOME/OPatch

/* 我们还能够将online patch rollback回滚掉，如以下语法 */
opatch rollback -id <patchID> -connectString  <SID>:<USERNAME>:<PASSWORD>:<NODE1>, \
<SID2>:<USERNAME>:<PASSWORD>:<NODE2>,  ...

[maclean@rh2 OPatch]$ opatch rollback -id 10188727 -connectString PROD:sys:password -invPtrLoc /s01/product/11.2.0/dbhome_2/oraInst.loc
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /s01/product/11.2.0/dbhome_2
Central Inventory : /s01/oraInventory
   from           : /s01/product/11.2.0/dbhome_2/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /s01/product/11.2.0/dbhome_2/oui
Log file location : /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch2011-02-17_20-18-00PM.log

Patch history file: /s01/product/11.2.0/dbhome_2/cfgtoollogs/opatch/opatch_history.txt

RollbackSession rolling back interim patch '10188727' from OH '/s01/product/11.2.0/dbhome_2'

The patch should be applied/rolled back in '-all_nodes' mode only.
Converting the RAC mode to '-all_nodes' mode.

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files affected by the patch '10188727' for restore. This might take a while...

Patching component oracle.rdbms, 11.2.0.2.0...
The patch will be removed from active database instances.
Disabling and removing online patch 'bug10188727.pch', on database 'PROD'

RollbackSession removing interim patch '10188727' from inventory

OPatch succeeded.

告警日志alert.log中的remove信息:
Patch bug10188727.pch Disabled - Update #5
Patch bug10188727.pch Removed - Update #6
Thu Feb 17 20:18:07 2011
Online patch bug10188727.pch has been disabled
Online patch bug10188727.pch has been removed

[maclean@rh2 trace]$ pmap -d 22481|tail -8
00002abecb53c000       8 r-x-- 000000000c64e000 008:00002 oracle
00002abecb53e000    5052 r-x-- 00000000000bd000 008:00002 oracle
00002abecba2d000     140 r-x-- 0000000000000000 008:00002 bug10188727.so
00002abecba50000    1024 ----- 0000000000023000 008:00002 bug10188727.so
00002abecbb50000       8 rwx-- 0000000000023000 008:00002 bug10188727.so
00007fff3342d000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2297720K    writeable/private: 7848K    shared: 2050052K

/* 显然rollback回滚掉该online interim patch也不足以回收内存，唯一的方法是重启实例 */

SQL> startup force;

[maclean@rh2 trace]$ ps -ef|grep pmon|grep -v grep
maclean  25563     1  0 20:22 ?        00:00:00 ora_pmon_PROD

[maclean@rh2 trace]$ pmap -d 25563|tail -8
00002aaf2f324000       4 rwxs- 0000000000000000 008:00002 hc_PROD.dat
00002aaf2f325000      40 r-x-- 0000000000000000 008:00002 libnque11.so
00002aaf2f32f000    1020 ----- 000000000000a000 008:00002 libnque11.so
00002aaf2f42e000       4 rwx-- 0000000000009000 008:00002 libnque11.so
00002aaf2f42f000    1048 rwx-- 00002aaf2f42f000 000:00000   [ anon ]
00007fffdfa84000      84 rwx-- 00007ffffffea000 000:00000   [ stack ]
ffffffffff600000    8192 ----- 0000000000000000 000:00000   [ anon ]
mapped: 2291488K    writeable/private: 7840K    shared: 2050052K

/* That's ok!
    ash to ash, dust to dust!　*/

References:<RDBMS Online Patching Aka Hot Patching [ID 761111.1]>

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug, Oracle补丁patch Tagged With: , ,

Oracle Advanced Security:Column Encryption Overhead

2011/02/17 by Leave a Comment

在Oracle 10g中出现了column encryption列加密特性，通过对列上的数据加密实现数据安全性的目的。当然实现这一加密特性是有代价的，一方面会导致所加密列数据每行所占磁盘空间字节数增长，另一方面会消耗更多的cpu和内存资源。

当使用Oracle的TDE(transparent data encryption)加密数据表的某一列时将导致该表上每行数据所占用的空间大致增加33-51个字节，这几十个字节用作以下用途：

  • 其中20个字节用以对加密值的完整性检查,该部分可以通过’nomac’选项来省略
  • 同时加密会填补加密值到16个字节(如果列本身长度不够的话)，举例来说如果是9个字节长度的number类型，那么加密该number字段时就会需要将该数据填补到16个字节，也就是额外地多用了7个字节
  • 加密时默认采用salt选项(default)，salt是指一串长度为16个字节的随机string，在数据被正式加密前这串string将会被添加到列上，这种做法使得黑客无法通过比对已知的密文来匹配加密值(steal patterns of ciphertext to known ciphertext);salt总是位于加密数据的末尾；该部分可以通过no salt选项来省略。

注意默认使用salt选项加密的列是不能创建索引的，所以强烈建议加密列时强制使用no salt选项！加密列上的索引不支持范围扫描操作(Range scans on encrypted columns can’t use index)，而加密表空间(encryption tablespace)没该限制。

SQL> create table enctab (t1 int encrypt);
Table created.

SQL>  create index ind_enc on enctab(t1);
create index ind_enc on enctab(t1)
*
ERROR at line 1:
ORA-28338: Column(s) cannot be both indexed and encrypted with salt

SQL> create table news (t1 varchar2(1) encrypt);
Table created.

/*以默认的salt和mac选项创建示例用表 */

SQL> insert into news values('1');
1 row created.

SQL> commit;
Commit complete.

/* 该列本身的长度为1个字节 */

SQL> select dump(t1,16) from news;

DUMP(T1,16)
--------------------------------------------------------------------------------
Typ=1 Len=1: 31

/* 以下为该行的dump信息，可以看到加密值增长到了52字节 */
col  0: [52]
de 76 08 74 2a c0 e3 94 89 e6 a8 3b 22 54 ca e5 af 4d eb a0 26 a7 e5 c2 f5
c0 e5 3a a0 09 9a 08 fa 56 2a 92 a0 83 b3 7f 0b 99 03 ad 12 78 d4 03 ec 6e
b3 c2

针对加密列可以使用’nomac’和no salt选项来减少性能损耗，其中’no mac’选项用以允许Oracle省略在加密数据中产生和存放message authentication code(MAC，信息验证代码)，如上文所述的这部分代码用以对加密值的完整性检查，会占用20个字节的空间。通过使用’nomac’选项可以有效较少加密和后续操作所额外消耗的cpu周期，同时为加密值的存储减少20个字节的开销。
另外no salt选项用以省略加密中加入的16个字节的随机字符串(string)，在能保证列值都唯一的情况下(攻击者无法通过已知密文比对的方式来解密)，使用该选项可以有效减少cpu周期和每个单元16字节的空间开销。

askmac.cn>create table Maclean (t1 varchar2(16) encrypt no salt 'nomac');

/* 注意这里的nomac要被单引号括起来 */

askmac.cn>alter table table_name modify column_name encrypt [using ] [no salt] ['nomac'];

此外目前列加密不支持外键约束，造成这种限制的原因是每张表都有其唯一的密钥(encryption key)；而表空间加密则不存在这种限制，即便某个从属表不在加密表空间上。

SQL> create table man (t1 int primary key );
Table created.

SQL> create table woman(t1 int encrypt);
Table created.

SQL>  alter table woman add constraint fk foreign key(t1) references man(t1);
 alter table woman add constraint fk foreign key(t1) references man(t1)
                                                 *
ERROR at line 1:
ORA-28335: referenced or referencing FK constraint column cannot be encrypted

列加密特性对于表连接(table joining)来说是透明的，即便作为连接条件的列被加密了也是如此(join tables is transparent,even if the columns for join condition are encrypted)。同时分区键是不能作为加密列的,否则将出现ORA-28346: an encrypted column cannot serve as a partitioning column错误。
此外加密列索引存在诸多限制，总结加密列索引(Indexes On Encrypted Columns)的几个restrictions:

  1. 只有使用no salt选项加密的列上才允许创建索引
  2. 加密列上不支持位图索引
  3. 加密列不支持外键
  4. 加密列上创建的索引只能做等式查询，因为不能做Range scan所以如between,like等非等式查询是不支持的；这种限制是由于索引中的数据也被加密了，所以实际上数据是以加密后的形式来排序的。所有非等式查询的条件均无法利用到索引，而使用全表扫描。
  5. 如果应用不使用等式查询的话，那么建议不要在加密列上创建索引，因为这样无益与性能，反而会增加性能开销。

而加密表空间(TDE Tablespace Encryption)不存在以上关于索引的限制，甚至在加密表空间上的表的索引在非加密表空间上也不会影响其使用，包括Range Scans;显然这一点出乎许多人的意料:

SQL> select tablespace_name,ENCRYPTED from dba_tablespaces  where tablespace_name in ('ENC','USERS');
TABLESPACE_NAME                ENC
------------------------------ ---
ENC                            YES
USERS                          NO

SQL> create table tv tablespace enc as select * from dba_objects;
Table created.

SQL> create index pk_tv on tv(object_id) tablespace enc;
Index created.

SQL> set autotrace on;
SQL> select 1 from tv where object_id=9999;
         1
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2009574168
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_TV |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=9999)

/* 将该索引移动至非加密表空间上 */

SQL> alter index pk_tv rebuild tablespace users;
Index altered.

SQL> select 1 from tv where object_id=9999;

         1
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 2009574168
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PK_TV |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=9999)

/* 可以看到虽然索引被移动到非加密表空间上但仍可以被利用到并range scan */

SQL> create bitmap index obj_typ on tv (object_type) tablespace users;
Index created.

/* 创建位图索引也没有问题 */

当然表空间加密也仍然存在一些限制:

  • 加密表空间不能使用传统的exp/imp工具导入导出，而只能使用datapump工具
  • 显然External Large Objects (BFILEs)这种存储在数据库外的外部大对象也不受支持。
Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , ,

解决Oracle错误ORA-15061一例

2011/01/30 by Leave a Comment

一套Linux上的11.2.0.1系统，告警日志中出现以下错误:

ORA-00202: control file: '+DATA/controlfile/current.256.7446483424'
ORA-17505: ksfdrsz:1 Failed to resize file to size 612 blocks
ORA-15061: ASM operation not supported [41]
WARNING: Oracle Managed File +FRA in the recovery area is orphaned by the control file.
The control file can not keep all recovery area files due to space limitation.
krse.c
Archived Log entry 200 added for thread 1 sequence 200 ID 0x3739c2f0 dest 1:

RMAN backup failed with

Rman backup error:
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 
ORA-19510: failed to set size of 6800 blocks for file "+FRA" (block size=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 6800 blocks
ORA-15061: ASM operation not supported [41]

[oracle@rh2 ~]$ oerr ora 15061
15061, 00000, "ASM operation not supported [%s]"
// *Cause:  An ASM operation was attempted that is invalid or not supported
//          by this version of the ASM instance.
// *Action: This is an internal error code that is used for maintaining
//          compatibility between software versions and should never be
//          visible to the user; contact Oracle support Services.
//

提交SR后，根据Oracle GCS确认为BUG:9788316:

1.The following error indicates that it failed to resize the controlfile to 612 blocks. If the DB_BLOCK_SIZE is 8192, 
then 612 blocks is not more than 5MB. According to the results of the query on V$ASM_DISKGROUP in 'results01.txt' file, 
the ASM diskgroup +DATA has 108605 MB free space. So, the ASM diskgroup +DATA has enough space for the 612 blocks.

2. By the way, please confirm whether you have recently applied PSU #1. Anyway, 
please try to relink the Oracle executables, as shown here. Before you run the "relink" command, 
make sure to shutdown both the ASM instance and target database.

$ORACLE_HOME/bin/relink all

3 After relinking the Oracle executables, please confirm whether you are still 
experiencing the same ORA-15061 error.

ORA-15061: ASM Operation Not Supported [41] After Apply PSU #1 (Doc ID 1126113.1)
ORA-15061 reported while doing a file operation with 11.1 or 11.2 ASM after PSU applied in database home (Doc ID 1070880.1)

Hdr: 9788316 11.2.0.1.1 RDBMS 11.2.0.1.0 ASM PRODID-5 PORTID-267
Abstract: AFTER APPLY PSU 1 (11.2.0.1.1) ON RDBMS HOME UNABLE TO RESIZE ASM DATAFILE.

*** 06/07/10 02:09 pm ***
—-
3-1827355081

PROBLEM:
——–
1) After apply PSU 1 (11.1.0.2.1) on the 11.2.0.1.0 RDBMS Oracle Home
customer is unable to resize an ASM datafile:
============================================================
SQL> alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M;
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560M
*
ERROR at line 1:
ORA-1237: cannot extend datafile 4
ORA-1110: data file 4: ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
ORA-17505: ksfdrsz:1 Failed to resize file to size 71680 blocks
ORA-15061: ASM operation not supported [41]

============================================================

2) DB alertlog reports (alert_brg13ed1.log):
============================================================

Mon Jun 07 11:27:57 2010
Stopping background process CJQ0
Mon Jun 07 12:30:28 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 536870915
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 536870915…
Mon Jun 07 13:07:40 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560m
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560m…
Mon Jun 07 13:49:50 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 800M
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 800M…
Mon Jun 07 13:58:51 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560M
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M…
Mon Jun 07 14:25:43 2010

============================================================

3) ASM alert.log does not report any problem.

DIAGNOSTIC ANALYSIS:
——————–
(see below)

WORKAROUND:
———–
None

RELATED BUGS:
————-

REPRODUCIBILITY:
—————-

TEST CASE:
———-

STACK TRACE:
————

SUPPORTING INFORMATION:
———————–

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-

DIAL-IN INFORMATION:
——————–

IMPACT DATE:
————

*** 06/07/10 02:09 pm ***
4) I can confirm that at (Jun 07 10:55:59 EDT 2010) customer installed the
Patch: 9355126 (on the Grid OH), which includes the fix for Bug: 8898852:
============================================================

Invoking OPatch 11.2.0.1.2

Oracle Interim Patch Installer version 11.2.0.1.2

Oracle Home : /u01/grid/oracle/product/grid
Central Inventory : /u01/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.2.0.1.2
OUI version : 11.2.0.1.0
OUI location : /u01/grid/oracle/product/grid/oui
——————————————————————————
—
Installed Top-level Products (1):

Oracle Grid Infrastructure
11.2.0.1.0
There are 1 products installed in this Oracle Home.

Installed Products (87):

There are 87 products installed in this Oracle Home.

Interim patches (1) :

Patch 9355126 : applied on Mon Jun 07 10:55:59 EDT 2010
Unique Patch ID: 12175902
Created on 5 Feb 2010, 07:38:30 hrs PST8PDT
Bugs fixed:
8974548, 8898852
============================================================

5) Also, I can confirm that at (Mon May 03 00:01:14 EDT 2010) customer
installed the 11.2.0.1.1 Patch Set Update (PSU #1) which include as well the
patch for Bug: 8898852 (on the RDBMS OH):
============================================================
Invoking OPatch 11.2.0.1.2

Oracle Interim Patch Installer version 11.2.0.1.2

Oracle Home : /u01/app/oracle/product/11.2.0/db1
Central Inventory : /u01/app/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.2.0.1.2
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.2.0/db1//oui

——————————————————————————
—
Installed Top-level Products (1):

Oracle Database 11g
11.2.0.1.0
There are 1 products installed in this Oracle Home.

Installed Products (134):

. 11.2.0.1.0
There are 134 products installed in this Oracle Home.

Interim patches (1) :

Patch 9352237 : applied on Mon May 03 00:01:14 EDT 2010
Unique Patch ID: 12366369
Created on 6 Apr 2010, 05:03:41 hrs PST8PDT
Bugs fixed:
8661168, 8769239, 8898852, 8801119, 9054253, 8706590, 8725286, 8974548
8778277, 8780372, 8769569, 9027691, 9454036, 9454037, 9454038, 8761974
7705591, 8496830, 8702892, 8639114, 8723477, 8729793, 8919682, 8818983
9001453, 8475069, 9328668, 8891929, 8798317, 8820324, 8733749, 8702535
8565708, 9036013, 8735201, 8684517, 8870559, 8773383, 8933870, 8812705
8405205, 8822365, 8813366, 8761260, 8790767, 8795418, 8913269, 8897784
8760714, 8717461, 8671349, 8775569, 8898589, 8861700, 8607693, 8642202
8780281, 9369797, 8780711, 8784929, 8834636, 9015983, 8891037, 8828328
8570322, 8832205, 8665189, 8717031, 8685253, 8718952, 8799099, 8633358
9032717, 9321701, 8588519, 8783738, 8796511, 8782971, 8756598, 9454385
8856497, 8703064, 9066116, 9007102, 8721315, 8818175, 8674263, 9352237
8753903, 8720447, 9057443, 8790561, 8733225, 9197917, 8928276, 8991997,
8837736
============================================================

6) But the problem persists:
============================================================
SQL> alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M;
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560M
*
ERROR at line 1:
ORA-1237: cannot extend datafile 4
ORA-1110: data file 4: ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
ORA-17505: ksfdrsz:1 Failed to resize file to size 71680 blocks
ORA-15061: ASM operation not supported [41]

============================================================

7) 11.2.0.1.1 Patch Set Update (PSU #1) should include the fix for bug:
8898852 (ORA-15061: ASM operation not supported [41]).
This PSU contains a fix that adds an operation to the ASM protocol.
The problem is that the PSU has been applied correctly to the RDBMS
home, but the fix is not correctly applied to the ASM HOME.

So either install that patch to the ASM HOME, or if that has been done
check why it failed.

A common reason for failure is a permission problem. Check bug 9711074
and its duplicates for more background. And check documentation bug 8629483
for the solution.

How do I know the fix is not missing? This error message is the key:
ORA-15061: ASM operation not supported [41]
(especially with operation 41)

If the patch was applied to the grid home, then probably the relink failed
due to permission problems.

ORA-15061: ASM Operation Not Supported [41] After Apply PSU #1 [ID 1126113.1]

pplies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
1) After apply PSU 1 (11.2.0.1.1 ) on the Grid Infrastructure Oracle Home
customer is unable to resize an ASM datafile:

SQL> alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M;
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560M
*
ERROR at line 1:
ORA-1237: cannot extend datafile 4
ORA-1110: data file 4: ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
ORA-17505: ksfdrsz:1 Failed to resize file to size 71680 blocks
ORA-15061: ASM operation not supported [41]

2) DB alertlog reports (alert_brg13ed1.log):

Mon Jun 07 11:27:57 2010
Stopping background process CJQ0
Mon Jun 07 12:30:28 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 536870915
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 536870915…
Mon Jun 07 13:07:40 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560m
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560m…
Mon Jun 07 13:49:50 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 800M
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 800M…
Mon Jun 07 13:58:51 2010
alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’
resize 560M
ORA-1237 signalled during: alter database datafile
‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M…
Mon Jun 07 14:25:43 2010

3) ASM alert.log does not report any problem.

Changes
PSU 1 (11.2.0.1.1 ) patchset was installed on the Grid Infrastructure Oracle Home
Cause

The PSU #1 (11.2.0.1.1 ) patch on the Grid Infrastructure Oracle Home was not correctly linked by opatch.

The PSU #1 (11.2.0.1.1 ) patch on the Grid Infrastructure Oracle Home needs to be relinked.
Solution
Relink the PSU #1 patch on the Grid Infrastructure Oracle Home as follow:

1) Shutdown the database instances.

2) Shutdown the ASM instance.

3) Relink the Grid Infrastructure Oracle Home as follow:

script /tmp/relink_GI.txt

env | sort

$ORACLE_HOME/bin/relink all

exit

4) Startup the ASM instance.

5) Startup the databases.

6) Resize the datafile:

SQL> alter database datafile ‘+DATA/brg13ed/datafile/ts_brg_users.1279.711064345’ resize 560M;

该bug一般是由于对grid infrastructure实施了PSU 1 (11.2.0.1.1 )后，Oracle binary没有正确link导致的，可行的解决方案是relink all，但这需要重启instance!

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug Tagged With: , , , ,

Advice on upgrading to 11.2.0.2 and converting to RAC

2011/01/13 by 1 Comment

Question:Problem Description: Currently we are running 11.2.0.1 Grid on a 2 nodes. We are facing DB crash issue and different bugs with current environment. One of the option Oracle provided is to upgrade Grid to 11.2.0.2 and convert to real RAC.

There are 11.2.0.1 database under the clusterware. These DBs are currently actively running on one node and cold failover HA enabled by Oracle’s perl scripts. We also have 10g standalone DBs running under the same clusterware.

We are planning the upgrade of Grid from 11.2.0.1 to 11.2.0.2, and also converting the cold faliover HA DBs to the real RAC 2 nodes or RAC 1 node.

We will need Oracle’s advice on the technical road map and procedures on this. Also the issues we might face. The upgrade is planned to start in one week from now. We really appreciate that Oracle get back to us as quickly as possible. Thanks.

Answer:
Please follow the below document for manual upgrade or using dbua.
Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)
Complete checklist to upgrade the database to 11g R2 using DBUA (Doc ID 870814.1)
Please follow the document to convert the single instance to RAC.
How to Convert 10g Single-Instance database to 10g RAC using Manual Conversion procedure (Doc ID 747457.1)

1. Our current version of Grid/clusterware is 11.2.0.1. Two versions of RDBMS are running currently on the cluster: 11.2.0.1 and 10.2.0.5. They are all on ASMs. +ASM1 is running on node 1 while +ASM2 is running on node 2. Some DBs are Standalone DBs and some DBs are Oracle active-passive HA with failover controlled by the Oracle perl scripts.
We are planning to upgrade Grid to 11.2.0.2 and converting Oracle HA to the real RAC 2 node or RAC 1 node. At the sametime, we are planning to move other system’s DBs onto the same cluster. The current DB versions for that system have 10.2.0.4,10.2.0.1, 9.2.0.7, 9.2.0.5.

Ans:

Oracle 9i Databases were not managed by Oracle Clusterware 11.1 already.The Clusterware has exceeded the limitation. So kindly request to upgrade the 9i database ASAP to manage these databases on 11gR2 clusterware.

2. Does Oracle have any suggestion on the order of upgrade to 11.2.0.2 and the converting to real RAC from Oracle HA under clusterware?

Ans:

There is no documentation. I assume that you have installed clusterware,ASM and RDBMS has been installed on active-passive(Oracle HA) nodes. You need to disable the pearl script and bring up the clusterware ,ASM and RDBMS instance on passive node.

3. In note 1212703.1 Oracle provided, patch 9974223 is mentioned if multicast is enabled only for 224.0.0.251 address. I am confused on the instructions in the readme of patch 9974223. It mentions to apply both on CRS_HOME and RDBMS HOME.

Ans:

If we upgrade RDBMS to 11.2.0.2 as well becides the Grid 11.2.0.2 upgrade, is it required to apply the patch to the RDBMS HOME too, or just optional? If it is required, does the patch only need to be applied in the RDBMS, which has RAC DB running? Does it need to apply on the Standalone DB on the Grid/ASM?

The patch 9974223 is applicable for CRS/RDBMS. You can apply this patch on both home. Yes. Please apply the patch on RDBMS home also. You no need to apply for standalone DB on the Grid/ASM.

1. Per Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip [ID 1210883.1 ], Redundant Interconnect without any 3rd-party IP failover technology (bond, IPMP or similar) is supported natively by Grid Infrastructure starting from 11.2.0.2. The note is for the interconnect.
What about the Oracle VIP? We already binded the 2nd public interface of IPMP to the node-vip (nodeapps) in order to resolve the DB listener down issue when IPMP failover to 2nd interface , do we have to undo it before the 11.2.0.2 upgrade?h

Ans: – no need to undo ipmp what is already configured.

2. In the note Oracle provided: http://download.oracle.com/docs/cd/E11882_01/install.112/e17213/procstop.htm#CEGHBJDB, It says:
=========
– To upgrade existing 11.2.0.1 Oracle Grid Infrastructure installations to Oracle Grid Infrastructure 11.2.0.2, you must first do at least one of the following:

Patch the release 11.2.0.1 Oracle Grid Infrastructure home with the 9413827 and 9706490 patches.

Install Oracle Grid Infrastructure Patch Set 1 (GI PSU1) or Oracle Grid Infrastructure Patch Set 2 (GI PSU2).

– Oracle Clusterware and Oracle ASM upgrades are always out-of-place upgrades. With 11g release 2 (11.2), you cannot perform an in-place upgrade of Oracle Clusterware and Oracle ASM to existing homes.
==========

If the”Oracle Clusterware and Oracle ASM upgrades are always out-of-place upgrades for 11.2″, why still need to patch the existing 11.2.0.1 Grid CRS HOME as mentioned above? If it is out of place, then Grid will have a new CRS HOME.

Ans:- Yes. Still you need to apply the recommended patches before upgrade. The reason is due to known issues, it is recommended to apply the patch before upgrade.
Ofcourse the grid infra needs to be installed on new home. Please go thorough the Grid installation document for more detail.

1. Our Grid is 11.2.0.1.0, we only need to apply one patch 9413827?

Yes. Please apply the 9413827 only.

2. Do we need to apply 9413827 to the RDBMS Home for 2 HA DBs that are under CRS control right before we upgrade the Grid to 11.2.0.2?

Yes. Please apply it.

Please refer the below document to check RAC option is enabled or not.
How to check RAC Option is currently linked into the Oracle Binary (Doc ID 284785.1)

RAC one node is only supported with DB version of 11g under 11.2.0.2 Grid for Solaris 10, correct?

Yes. It is supported. Please find the certification metrics as below

OS Product Certified With Version Status
10 11gR2 64-bit Oracle Clusterware 11g Certified
10 11gR2 64-bit Oracle Solaris Cluster 3.3 Certified
10 11gR2 64-bit Oracle Solaris Cluster 3.2 Certified
10 11gR2 64-bit Veritas Storage Foundation for Oracle RAC 5.1 Certified
10 11gR2 64-bit Veritas Storage Foundation for Oracle RAC 5.0 Certified

We have some DBs with 10g version. Right now they are standalone DBs and can not be upgraded for now. Does Oracle support 10g database with RAC 2 nodes under 11.2.0.2 Grid on Solaris 10?

Yes. It is supported.

Filed Under: Oracle, Oracle 11g新特性, Oracle RAC, Oracle补丁patch Tagged With: , ,

如何确定所打Patch是否需要停机

2010/12/28 by 1 Comment

Oracle在11g中提出了online patching(也可以叫做hot patch)的概念，有效减少了因实施one-off patch而导致的系统停机时间。但我们如何得知哪些Patch是可以online apply的，而哪些Patch是必须关闭实例(shutdown instance)后应用的呢？
下面我们就介绍一种简单有效地方法来识别这2种Patch:

1.
从MOS下载所需要的Patch文件

2.
解压该Patch文件(一般为zip压缩包)，cd到/$PATCH_ID/etc/config目录，打开inventory.xml文件

3.
检查xml文件中的instance_shutdown选项，如
<instance_shutdown>true</instance_shutdown>
则说明该one-patch要求offline apply,而不能在线实施
Filed Under: Oracle, Oracle补丁patch Tagged With: , ,

ORA-00600: [qksrcBuildRwo]内部错误一例

2010/11/29 by 1 Comment

一套HP-UX上的11.1.0.7上的系统，在使用11g自带的新特性dictionary health check(数据字典健康检查)功能时发现FILE$基表存在讹误，并且告警日志中伴随出现ORA-00600：[qksrcBuildRwo]内部错误，具体错误信息如下:

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
91337 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91334 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91331 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 202 on object FILE$ failed
91328 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed
91325 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed
91322 CRITICAL OPEN 30-JUN-09 SQL dictionary health check: file$ pk 42 on object FILE$ failed

ALERT LOG
-----------------------
Display of database log file :

Tue Jun 30 10:32:27 2009
Errors in file /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_18678.trc (incident=20162):
ORA-00600: sis?inen virhekoodi, argumentit: [qksrcBuildRwo], [], [], [], [], [], [], []
Incident details in: /oracle/11.1.0/diag/rdbms/bect/BECT/incident/incdir_20162/BECT_j000_18678_i20162.trc
Tue Jun 30 10:32:57 2009
Trace dumping is performing id=[cdmp_20090630103257]
Tue Jun 30 10:32:59 2009
Sweep Incident[20162]: completed
Tue Jun 30 10:44:15 2009
ORA-1652: unable to extend temp segment by 128 in tablespace FENIX_SECURE
ORA-1652: unable to extend temp segment by 1024 in tablespace FENIX_SECURE
Errors in file /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_21609.trc:
ORA-12012: virhe ty?n 58702 automaattisen suorituksen yhteydess?
ORA-01652: v?liaikaisen segmentin laajennus 1024:lla taulualueeessa FENIX_SECURE ei onnistu


TRACE FILE
----------------------
Display of relevant trace file :

Dump file /oracle/11.1.0/diag/rdbms/bect/BECT/incident/incdir_20162/BECT_j000_18678_i20162.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security and Real Application Testing options
ORACLE_HOME = /oracle/11.1.0
System name: HP-UX
Node name: hellu
Release: B.11.23
Version: U
Machine: ia64
Instance name: BECT
Redo thread mounted by this instance: 1
Oracle process number: 20
Unix process pid: 18678, image: oracle@hellu (J000)


*** 2009-06-30 10:32:27.211
*** SESSION ID:(520.12850) 2009-06-30 10:32:27.211
*** CLIENT ID:(FOOBAR@192.168.60.110@Mozilla/5.0 (Windows; U; Windows NT 5.0; f) 2009-06-30 10:32:27.211
*** SERVICE NAME:(SYS$USERS) 2009-06-30 10:32:27.211
*** MODULE NAME:(DBMS_SCHEDULER) 2009-06-30 10:32:27.211
*** ACTION NAME:(ADV_SQL_TUNING_1246346508268) 2009-06-30 10:32:27.211

Dump continued from file: /oracle/11.1.0/diag/rdbms/bect/BECT/trace/BECT_j000_18678.trc
ORA-00600: sis?inen virhekoodi, argumentit: [qksrcBuildRwo], [], [], [], [], [], [], []

========= Dump for incident 20162 (ORA 600 [qksrcBuildRwo]) ========

*** 2009-06-30 10:32:27.219
----- Current SQL Statement for this session (sql_id=06y1876p6cr8a) -----
/* SQL Analyze(520,1) */
WITH TARGETS AS
 (SELECT COLUMN_VALUE TARGET_GUID
    FROM TABLE(CAST(:B1 AS MGMT_TARGET_GUID_ARRAY)))
SELECT /*+ ORDERED USE_NL(assoc) USE_NL(cfg)
NO_INDEX_FFS(assoc MGMT_POLICY_ASSOC_PK)
INDEX_ASC(assoc MGMT_POLICY_ASSOC_PK)
NO_INDEX_FFS(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX)
INDEX_ASC(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) */
ASSOC.OBJECT_GUID TARGET_GUID, LEAD(ASSOC.OBJECT_GUID, 1) OVER(
 ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER) NEXT_TARGET_GUID, 
POLICY.POLICY_GUID POLICY_GUID, LEAD(POLICY.POLICY_GUID, 1) OVER(
 ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER) NEXT_POLICY_GUID, 
POLICY.POLICY_NAME, POLICY.POLICY_TYPE, DECODE(POLICY.POLICY_TYPE, :B5, 
NVL(CFG.MESSAGE, POLICY.MESSAGE), :B10, CFG.MESSAGE, NULL) MESSAGE, 
DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.MESSAGE_NLSID, POLICY.MESSAGE_NLSID), :B10, 
CFG.MESSAGE_NLSID, NULL) MESSAGE_NLSID, DECODE(POLICY.POLICY_TYPE, :B5, 
NVL(CFG.CLEAR_MESSAGE, POLICY.CLEAR_MESSAGE), :B10, CFG.CLEAR_MESSAGE, NULL) 
CLEAR_MESSAGE, DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.CLEAR_MESSAGE_NLSID, POLICY.CLEAR_MESSAGE_NLSID), :B10, 
CFG.CLEAR_MESSAGE_NLSID, NULL) 
CLEAR_MESSAGE_NLSID, POLICY.REPO_TIMING_ENABLED, :B4, 
POLICY.VIOLATION_LEVEL, 
DECODE(POLICY.POLICY_TYPE, :B5, :B11, 0) VIOLATION_TYPE, POLICY.CONDITION_TYPE, 
POLICY.CONDITION, DECODE(POLICY.POLICY_TYPE, :B5, NVL(CFG.CONDITION_OPERATOR, POLICY.CONDITION_OPERATOR), :B10, 
CFG.CONDITION_OPERATOR, 0) CONDITION_OPERATOR, 
CFG.KEY_VALUE, CFG.KEY_OPERATOR, CFG.IS_EXCEPTION, CFG.NUM_OCCURRENCES, 
NULL EVALUATION_DATE, CAST(MULTISET(
SELECT MGMT_POLICY_PARAM_VAL(PARAM_NAME, CRIT_THRESHOLD, WARN_THRESHOLD, INFO_THRESHOLD)
  FROM MGMT_POLICY_ASSOC_CFG_PARAMS PARAM
 WHERE PARAM.OBJECT_GUID = CFG.OBJECT_GUID AND PARAM.POLICY_GUID = CFG.POLICY_GUID 
AND PARAM.COLL_NAME = CFG.COLL_NAME AND PARAM.KEY_VALUE = CFG.KEY_VALUE AND 
PARAM.KEY_OPERATOR = CFG.KEY_OPERATOR) AS MGMT_POLICY_PARAM_VAL_ARRAY) PARAMS, DECODE(POLICY.CONDITION_TYPE, :B9, CAST(MULTISET(
SELECT MGMT_NAMEVALUE_OBJ.NEW(BIND_COLUMN_NAME, BIND_COLUMN_TYPE)
  FROM MGMT_POLICY_BIND_VARS BINDS
 WHERE BINDS.POLICY_GUID = POLICY.POLICY_GUID) AS MGMT_NAMEVALUE_ARRAY), 
MGMT_NAMEVALUE_ARRAY()) BINDS, DECODE(:B8, 0, MGMT_MEDIUM_STRING_ARRAY(), 1, MGMT_MEDIUM_STRING_ARRAY(CFG.KEY_VALUE), CAST((
SELECT MGMT_MEDIUM_STRING_ARRAY(KEY_PART1_VALUE, KEY_PART2_VALUE, KEY_PART3_VALUE, 
KEY_PART4_VALUE, KEY_PART5_VALUE)
  FROM MGMT_METRICS_COMPOSITE_KEYS COMP_KEYS
 WHERE COMP_KEYS.COMPOSITE_KEY = CFG.KEY_VALUE AND COMP_KEYS.TARGET_GUID = CFG.OBJECT_GUID) AS MGMT_MEDIUM_STRING_ARRAY)) KEY_VALUES
  FROM TARGETS, MGMT_POLICIES POLICY, MGMT_POLICY_ASSOC ASSOC, MGMT_POLICY_ASSOC_CFG CFG
 WHERE POLICY.METRIC_GUID = :B7 AND ASSOC.OBJECT_GUID = TARGETS.TARGET_GUID 
AND ASSOC.POLICY_GUID = POLICY.POLICY_GUID AND POLICY.POLICY_TYPE != :B6 AND 
(POLICY.POLICY_TYPE = :B5 OR ASSOC.COLL_NAME = :B4) AND ASSOC.OBJECT_TYPE = :B3 
AND ASSOC.IS_ENABLED = :B2 AND CFG.OBJECT_GUID = ASSOC.OBJECT_GUID AND CFG.POLICY_GUID = ASSOC.POLICY_GUID 
AND 
CFG.COLL_NAME = ASSOC.COLL_NAME
ORDER BY ASSOC.OBJECT_GUID, POLICY.POLICY_GUID, CFG.EVAL_ORDER, CFG.KEY_VALUE DESC

----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
c0000000bf9ee2e8 7294 package body SYS.DBMS_SQLTUNE_INTERNAL
c0000000bfbe76d0 8 SYS.WRI$_ADV_SQLTUNE
c0000000d513f948 545 package body SYS.PRVT_ADVISOR
c0000000d513f948 2597 package body SYS.PRVT_ADVISOR
c0000000cef08358 241 package body SYS.DBMS_ADVISOR
c0000000bc1e9ff8 718 package body SYS.DBMS_SQLTUNE
c0000000bc31c3a0 1 anonymous block

----- Call Stack Trace -----

Function List (to Full stack) (to Summary stack)

skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <- ksfdmp 
<- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE 
<- 1936 <- dbkePostKGE_kgsf <- 128 <- kgeadse <- kgerinv_internal <- kgerinv 
<- kgeasnmierr <- qksrcBuildRwo <- qknrcAllocate <- $cold_qkadrv <- opitca 
<- kksFullTypeCheck <- rpiswu2 <- kksSetBindType <- kksfbc <- opiexe <- kpoal8 
<- opiodr <- kpoodrc <- rpiswu2 <- kpoodr <- upirtrc <- kpurcsc <- kpuexec 
<- OCIStmtExecute <- qksanAnalyzeSql <- 272 <- qksanAnalyzeSegSql <- kestsaInitialRound 
<- kestsaAutoTuneSql <- kestsaAutoTuneDrv <- kestsTuneSqlDrv <- kesaiExecAction 
<- kesaiTuneSqlDrv <- 176 <- spefcifa <- spefmccallstd <- pextproc <- peftrusted 
<- psdexsp <- rpiswu2 <- psdextp <- pefccal <- pefcal <- pevm_FCAL <- pfrinstr_FCAL 
<- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe <- opiexe <- kpoal8 <- opiodr 
<- kpoodr <- upirtrc <- kpurcsc <- kpuexec <- OCIStmtExecute <- jslvec_execcb 
<- jslvswu <- jslve_execute0 <- jslve_execute <- rpiswu2 <- kkjex1e <- kkjsexe <- kkjrdp 
<- opirip <- opidrv <- sou2o <- opimai_real <- main <- main_opd_entry

以上trace中值得注意的是stack trace记录:dbgeExecuteForError <- dbgePostErrorKGE <- dbkePostKGE_kgsf，通过匹配该stack trace point在MOS上可以找到2个相关的Bug记录:

Bug 8340928: XF11.2PREPEND_DML - TRC - QKSRCBUILDRWO:
When a column in the select list references to a view column which produces
a temp LOB, such as a TO_CLOB() operator, then an ORA-600 can occur
when using the result-cache.

Workaround
 Disable the result-cache

Bug 7314587: STARETL ORA-00600 INTERNAL ERROR CODE, ARGUMENTS [QERNCROWP1], [0], [2] RDBMS:

Ora-600 [Qksrcbuildrwo]
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 - Release: 11.1 to 11.1
Symptoms
Getting ORA-00600 [qksrcBuildRwo] in the alert log file when trying to execute select statement.

ERROR:
--------

ORA-00600: internal error code, arguments: [qksrcBuildRwo], [], [], [], [], [], [], [], [], [],
[], []

----- Call Stack Trace -----
dbgexProcessError dbgeExecuteForError dbgePostErrorKGE dbkePostKGE_kgsf
 kgeadse kgerinv_internal kgerinv kgeasnmierr qksrcBuildRwo
  qknrcAllocate qkadrv qkadrv qkadrv opitca kksLoadChild
   kxsGetRuntimeLock kksfbc kkspsc0 kksParseCursor opiosq0
    opiall0 opikpr opiodr rpidrus skgmstack rpidru rpiswu2
     kprball kprbprsu kkxs_parse kkxsprsclb pevm_icd_call_common
      pfrinstr_ICAL pfrrun_no_tool pfrrun plsql_run peicnt
       kkxexe opiexe opiall0 opikpr opiodr rpidrus skgmstack
        rpidru rpiswu2 kprball kzftExHandler kzftAuditExe kzftChkAudit

Changes
Result cache is enabled (result cache is a new feature in 11g).

From the alert log file
-----------------
result_cache_mode = "AUTO"

Cause
The ORA-00600 [Qksrcbuildrwo] is caused by
unpublished Bug 8340928 XF11.2PREPEND_DML - TRC - QKSRCBUILDRWO

Solution
1. Disable result_cache_mode in the spfile/pfile (remove it) or set it to MANUAL

At session level
------------
SQL> alter session set result_cache_mode='MANUAL';
-- Or
At system level
------------
SQL> alter system set result_cache_mode='MANUAL';
-- Or

2. Apply one off Patch 8340928 if available on My Oracle Support for your Oracle Version and Platform.
-- Or
3. Upgrade to 11.2 where unpublished Bug 8340928 is fixed.

可以确定该qksrcBuildRwo内部错误与字典表FILE$的讹误无关，而是由于11g release1中result cache的相关bug引起的；MOS建议通过不适用结果集缓存(result cache)特性来workaround这个错误，或者干脆升级到11g release(11.2.0.1以上，目前最新为11.2.0.2)。

Filed Under: Oracle, Oracle 11g新特性, Oracle Bug Tagged With: , , ,

Row Cache lock Problem

2010/09/29 by 7 Comments

一套AIX上的11.1.0.7系统，应用启动时出现大量row cache lock等待，具体的systemstate dump信息如下:

FILE VERSIONS
-----------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/database
System name: AIX
Node name: HA5_4ADB01
Release: 3
Version: 5
Machine: 000687C2D900
Instance name: HN4A1


TRACE FILE
---------------------------
Filename=HN4A1_ora_2061038.trc



Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 1 Signature Hash: 0x75bdd0c
 [b] Chain 2 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 2 Signature Hash: 0x75bdd0c
 [c] Chain 3 Signature: 'row cache lock'<='row cache lock' (cycle)
     Chain 3 Signature Hash: 0x75bdd0c
 
===============================================================================
Cycles:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (hn4a.hn4a1)
                   os id: 2114372
              process id: 211, oracle@HA5_4ADB01
              session id: 610
        session serial #: 5
    }
    is waiting for 'row cache lock' with wait info:
    {
                      p1: 'cache id'=0x7
                      p2: 'mode'=0x0
                      p3: 'request'=0x5
            time in wait: 0.491964 sec
      heur. time in wait: 26.859741 sec
           timeout after: 2.508036 sec
                 wait id: 311
                blocking: 1 session
            wait history:
              1.     event: 'row cache lock'
                   wait id: 310             p1: 'cache id'=0x7
               time waited: 2.929713 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              2.     event: 'row cache lock'
                   wait id: 309             p1: 'cache id'=0x7
               time waited: 2.929726 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              3.     event: 'row cache lock'
                   wait id: 308             p1: 'cache id'=0x7
               time waited: 2.929720 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (hn4a.hn4a1)
                   os id: 2118476
              process id: 209, oracle@HA5_4ADB01
              session id: 616
        session serial #: 5
    }
    which is waiting for 'row cache lock' with wait info:
    {
                      p1: 'cache id'=0x7
                      p2: 'mode'=0x0
                      p3: 'request'=0x5
            time in wait: 0.491910 sec
      heur. time in wait: 26.859705 sec
           timeout after: 2.508090 sec
                 wait id: 308
                blocking: 1 session
            wait history:
              1.     event: 'row cache lock'
                   wait id: 307             p1: 'cache id'=0x7
               time waited: 2.929713 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              2.     event: 'row cache lock'
                   wait id: 306             p1: 'cache id'=0x7
               time waited: 2.929729 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
              3.     event: 'row cache lock'
                   wait id: 305             p1: 'cache id'=0x7
               time waited: 2.929715 sec    p2: 'mode'=0x0
                                            p3: 'request'=0x5
    }
    and is blocked by the session at the start of the chain.
 
Chain 1 Signature: 'row cache lock'<='row cache lock' (cycle)
Chain 1 Signature Hash: 0x75bdd0c

...==>many similar chains

PROCESS 211:
  ----------------------------------------
  SO: 0x7000008a1370178, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x7000008a1370178, name=process, file=ksu.h LINE:10706 ID:, pg=0
  (process) Oracle pid:211, ser:3, calls cur/top: 0x7000008ac61a0e8/0x7000008ac61a0e8
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x0)
            int error: 0, call error: 0, sess error: 0, txn error 0
  ksudlp FALSE at location: 0
  (post info) last post received: 0 0 201
              last post received-location: kqr.h LINE:2181 ID:kqrbgl: compatible mode
              last process to post me: 70000089134b800 1 6
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x7000008a153a4c8
    O/S info: user: oracle, term: UNKNOWN, ospid: 2114372
    OSD pid info: Unix process pid: 2114372, image: oracle@HA5_4ADB01
    Short stack dump:
<-ksedsts()+0254<-ksdxfstk()+0028<-ksdxcb()+05d8<-sspuser()+0074<-44c0<-skgpwwait()+00b8<-ksliwat()+0c48<-kslwaitctx()+0150<-kqrigt()+06ac<-kqrLockAndPinPo()+0250<-kqrpre1()+061c<-kqrpre()+001c<-kziavua()+0384<-kpolnb()+0ffc<-kpoauth()+07a0<-opiodr()+0b98<-ttcpip()+115c<-opitsk()+1700<-opiino()+09f0<-opiodr()+0b98<-opidrv()+0440<-sou2o()+0090<-opimai_real()+01b0<-main()+0090<-__start()+0098


    service name: SYS$USERS
    client details:
      O/S info: user: aiuap, term: unknown, ospid: 1234
      machine: HA5-4A26 program: JDBC Thin Client
      application name: JDBC Thin Client, hash value=2546894660
...
         proc=0x7000008a1370178, name=row cache enqueues, file=kqr.h LINE:2004 ID:, pg=0
        row cache enqueue: count=1 session=7000008916dee00 object=7000008dfd84428, request=X
        savepoint=0x3e
        row cache parent object: address=7000008dfd84428 cid=7(dc_users)
        hash=481062cf typ=21 transaction=700000888da17a0 flags=00000002
        own=7000008dfd844f0[7000008bf6e18f0,7000008bf6e18f0] wat=7000008dfd84500[7000008bf5d2750,7000008ef34e818] mode=X
        status=VALID/-/-/-/-/-/-/-/-
        request=N release=FALSE flags=8
        instance lock=QK 481062cf bb6f8fe9
...
      LIBRARY OBJECT LOCK: 7000008ef5069d8 handle=7000008dfd22338 mod=N
      pnc=0 pns=0 cbb=1 rpr=1 exc=1 ilh=0 ctx=0
      use=7000008916dee00 ses=7000008a15c44f0 cnt=1 flg=CNB/[0001] spn=0x4c3d09ea
      LIBRARY HANDLE:7000008dfd22338 bid=105620 hid=a3c59c94 lmd=N pmd=0 sta=VALD
      name=select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
      hash=8973f7c629f09e3f081962c2a3c59c94 idn=0

众多wait chain的源头均是row cache lock，systemstate转储中该等待事件相关的p1，也就是cache id均为7；cache#为7的字典缓存代表着dc_users，即字典中的用户信息(包括用户名，密码等信息):
[Read more…]

Filed Under: Oracle Tagged With: , ,

11g中的db_block_checking参数

2010/09/19 by Leave a Comment

初始化参数DB_BLOCK_CHECKING控制Oracle如何全面检查读写的每个数据块的完整性。启用的检查界别是环境中的故障承受级别(通常很低)与连续检查块所需的开销折中的结果。在11g中db_block_checking参数有了更多的选项，以满足不等的块检验粒度:

SQL> alter system set db_block_checking=AA;
alter system set db_block_checking=AA
*
ERROR at line 1:
ORA-00096: invalid value AA for parameter db_block_checking, must be from among FULL, TRUE, MEDIUM, LOW, OFF, FALSE

/* 可选的有 OFF=FALSE,FULL=TRUE以及MEDIUM和LOW */

不同的DB_BLOCK_CHECKING选项对应不同的检查粒度:

  • OFF或FALSE 不执行任何检查块的操作
  • LOW 在内存中更改块或从磁盘中读取块后对块进行基本检查，其中包括RAC环境中在实例间传输块的情形
  • MEDIUM 包括所有LOW检查，另加检查所有非IOT(索引组织表)块
  • FULL或TRUE 包括所有LOW和MEDIUM检查，另加检查索引块

在客户愿意承担性能开销的前提下，Oracle建议使用FULL值。默认值是OFF，但仍始终启用针对SYSTEM表空间的FULL块检查功能(受到隐式参数_db_always_check_system_ts的控制,默认为TRUE)。通常认为块检查开销的范围在1%~10%之间，在OLTP环境中更接近于10%。
[Read more…]

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

11g新特性SQL执行计划管理(SQL Plan Management) (1)

2010/09/18 by Leave a Comment

数据库系统性能受到查询执行的严重影响。然而SQL语句的执行计划可能因统计信息变化，优化参数变化或方案定义变化等原因而意外改变，Oracle Optimizer优化器往往无法在没有人工干预的情况下准确进化执行计划。在无法保证新的执行计划总是趋于变得更好的情况下，用户倾向于通过存储大纲(stored outline)或锁定统计信息来保证执行计划的问题。然而使用这些方式将不可避免地丧失利用到新的优化器特性以改善SQL语句性能的优势。在保证当前可被接受执行计划的前提下，仅允许采用那些更好的，获益更多的执行计划才是终极方案。

Oracle Database 11g是在解决这一SQL执行计划上处于市场领先地位。SQL Plan Management(SPM)提供了一个完全透明且可控的执行计划进化的框架。在SPM的帮助下优化器自动管理执行计划并保证只有已知或已确认的执行计划才被采用。当一个新的计划出现时，Oracle将不会采用它，直到确认其与当前的执行计划有着相当的，或更好的性能。

SQL Plan Management(SPM)保证数据库运行时性能绝不因为执行计划的改变而大幅下降。为了确保这一点，仅仅那些已被接受的(accepted or trusted)的执行计划将被采用；任何计划的进化都将被追踪并仅在其被评价为无损于性能或有益于性能后被采纳。

SPM主要由三个部分组成：

1.执行计划基线捕捉

创建SQL执行计划基线意味着接受(或者说信任)相关SQL语句的执行计划。SQL计划基线存储在历史计划中，历史计划保存在SQL Management BASE(SMB)中，SMB位于SYSAUX表空间上。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select occupant_name,space_usage_kbytes  from v$sysaux_occupants where occupant_name like '%SQL%';

OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
SQL_MANAGEMENT_BASE                                                            3776

2.SQL计划基线选择

保证仅采用SQL计划基线中已被信任的执行计划，并追踪计划历史中所有新的执行计划。计划历史中包括了受信任的和不受信任的执行计划。不受信任的执行计划可能是未被检验的(unverified)或被拒绝的(rejected)。
[Read more…]

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , , , ,

11.2.0.2补丁集安装体验

2010/09/15 by 2 Comments

使用了Out-of-place Upgrade方式，安装图形界面沿袭了11.2.0.1的风格:
[Read more…]

Filed Under: Oracle Tagged With: , , , , , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号