Month: April 2014

Bigfiles

It’s always the combinations that catch you out. Bigfile tablespaces have their uses – especially in big systems Materialized views have their users – especially in big systems There’s absolutely no reason why the two technologies should interfere with each other … until you find a bug ! Running an example, stripped to the bare minimum, […]

Migration and upgrade DB2 V9.1 to V9.7

近期参与了某客户DB2 migration and upgrade的项目,客户源库环境:操作系统是AIX 5.3.0.8 数据库版本为DB2 V9 .1FP 8 64bit,目标库操作系统AIX 7.1.5数据库版本为DB2 V9 .7FP9 64bit。当时给客户提供了三种迁移升级方案,大致方案会在下面描述:
迁移方案一 db2look + db2move :

1、目标系统搭建 用户 文件系统 数据库 表空间 其他对象的创建.
1) 在源端用db2look 导出相关数据库定义:

  1. db2look –d dbname –l –o dbnametbspace.ddl
  2. db2look –d dbname –e –x –o dbname.ddl

(去掉系统function explain_get_msgs和NOT EXTENDED STORAGE的定义),将相关脚本通过FTP传输到目标库相应目录
2)在目标端创建创建文件系统、数据库及表空间等

  1. db2 create db dbname using codeset UTF-8 territory CN PAGESIZE 8192
  2. db2 –tvf dbnametbspace.ddl >dbnametbspace.log
  3. db2 –tvf dbname.ddl >dbname.log

这里注意数据库字符集应该和源库一致,同时在创建bufferpool的时候在V9.1里面可以使用NOT EXTENDED STORAGE属性,在V9.7中已经不支持需要将其删掉。
3)对照源数据库,验证对比数据库对象是否数目相同,并修改目标系统修改参数;

  1. db2 “select count(*) from syscat.tables where tabschema not like ‘SYS%’ and type=’A’”
  2. db2 “select count(*) from syscat.tables where tabschema not like ‘SYS%’ and type=’T’”
  3. db2 “select count(*) from syscat.tables where tabschema not like ‘SYS%’ and type=’V’”

对应源环境,修改目标库相关参数:

  1. db2 get db cfg > db.cfg
  2. db2 get dbm cfg > dbm.cfg
  3. db2set > db2set.cfg

2、 源系统数据导出,创建文件系统存放导出数据

  1. db2move dbname export >export.log

3、 在目标系统准备好导入数据的空间,通过ftp将数据放到目标,目标系统导入数据。
1) 在ddl 找出自增长的表的名字 单独处理。修改tab.list 去掉含有增长列的表,然后:

  1. db2move dbname load

2) 单独导入自增长的数据
找到相关表记录存在自增长列的表名

  1. db2 load from xxx.ixf of ixf MODIFIED BY  IDENTITYOVERRIDE  insert into tabname NONRECOVERABLE

4、 查询现有系统 sequences的大小 ,更改目标系统sequences 的起始大小。

  1. db2 “select nextval for secquencename  from sysibm.sysdummy1”
  2. db2 “ALTER SEQUENCE secquencename RESTART WITH xxxx”

5、 统计信息的处理
统计信息备选方案:

  1. db2look –d dbname –m –o dbstats.dml
  2. db2  -tvf dbstats.dml

临时表空间更换目录方法:
新建与原来临时表空间相同页大小的临时表空间,指定新的路径

  1. db2 “Create temporary tablespace tbsname pagesize <>K using (’<new path>’)”

在线删除现有的临时表空间(可能会,报出信息,说表空间正在使用,可以继续执行这个命令,直到该命令执行成功)。

  1. db2 “drop tablespace tbsname”

迁移方案二:利用在线数据库备份复原并迁移到 9.7

1、 在目标机器安装9.1 和 9.7 两个数据库
2、 在9.1的环境中恢复数据库。并验证恢复成功
3、 验证 是否可以升级

  1. $DB2DIR/instance/db2iupgrade [ -u fencedID ] InstNamea

4、 停止9.1实例,升级实例

  1. $DB2DIR/instance/db2iupgrade [ -u fencedID ] InstNamea

5、 升级数据库

  1. db2 UPGRADE DATABASE database-alias

6、 迁移后 绑定包

  1. db2 bind BNDPATH/@db2cli.lst blocking all grant public
  2. db2 bind BNDPATH/db2schema.bnd blocking all grant public
  3. db2 bind BNDPATH/ @db2ubind.lst blocking all grant public
  4. db2rbind.$inst.$db.log all

迁移方案三:利用脱机备份还原

1、 在源9.1做脱机数据库备份
2、 在目标9.7 创建实例,并做数据库还原
3、 升级数据库

  1. db2 UPGRADE DATABASE database-alias

4、 迁移后 绑定包

  1. db2 bind BNDPATH/@db2cli.lst blocking all grant public
  2. db2 bind BNDPATH/db2schema.bnd blocking all grant public
  3. db2 bind BNDPATH/ @db2ubind.lst blocking all grant public
  4. db2rbind.$inst.$db.log all

后面两个方案相对于方案一来说操作更容易,但方案一可控性更强。

Oracle 11gR2 and 12cR1 on Oracle Linux 7 beta

I’ve been having a play with Oracle Linux 7 beta over the weekend. Not surprisingly my first thoughts were to install the Oracle database on it. Oracle Linux 7 (OL7) Installation Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 7 (OL7) Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 7 (OL7) […]


Oracle 11gR2 and 12cR1 on Oracle Linux 7 beta was first posted on April 28, 2014 at 7:00 pm.
©2012 “The ORACLE-BASE Blog“. Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Oracle的Central Inventory和Local inventory详解

很多朋友对Oracle的inventory信息不太了解以至遇到相关的问题不知道如何处理,这篇文章我们将详细讲解Oracle的Central Inventory (oraInventory)和Local Inventory (Oracle Home inventory)
首先我们通过查看$ opatch lsinventory的输出来抛出几个问题:
[oracle@dbnode1 OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory  <<<<<=====什么是Central Inventory?
   from           : /etc/oraInst.loc<<<<<=====oraInst.loc是什么文件,它有什么作用?如果它被删除掉了会怎么样?
OPatch version    : 11.2.0.1.7<<<<<=====这是OPatch版本?
OUI version       : 11.2.0.3.0<<<<<=====OUI version 这里OUI 版本是指的什么?  
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-03-23_21-03-24PM.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-03-23_21-03-24PM.txt
——————————————————————————–
Installed Top-level Products (1):
Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
——————————————————————————–
OPatch succeeded.
这里OPatch version 就是Opatch的版本,它是不同于数据库版本的。
如果OPatch 版本过低那打patch时就会报错,不过这个问题很快可以通过查看patch的readme通过其指定的Note 下载最新的Opatch 来解决。
OUI version 就是安装的ORACLE_HOME的版本
言归正传,什么是Central Inventory (oraInventory)呢 ?
每一个安装了Oracle产品的操作系统上都至少有一个Central Inventory (oraInventory),他通过一个叫做inventory.xml的文件记录了在此操作系统上安装过的Oracle Homes等信息。
实际上Oracle就是通过Central Inventory (oraInventory) 来确定Oracle Home的位置,名称,是否是CRS_HOME及其他节点等信息的。

我们可以具体看一下inventory.xml的内容:
inventory.xml位置就在< Central Inventory >/ContentsXML/inventory.xml
例如:
[oracle@dbnode1 OPatch]$ cd /u01/app/oraInventory/ContentsXML/
[oracle@dbnode1 ContentsXML]$ ls
comps.xml  inventory.xml  libs.xml
[oracle@dbnode1 ContentsXML]$ cat inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/db_1″ TYPE=”O” IDX=”1″/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[oracle@dbnode1 ContentsXML]$ pwd
/u01/app/oraInventory/ContentsXML
这里我们只安装了一个ORACLE_HOME它的名字叫OraDb11g_home1,路径在/u01/app/oracle/product/11.2.0/db_1。
请注意这里TYPE=”O” IDX=”1″
TYPE=”O”意思是这是一个ORACLE数据库的HOME,如果它后面还有CRS=”true”这样的标记就表明这是一个CRS_HOME。
IDX=”1″ 意思就是该HOME是第一个安装的产品。
我们再看一个安装了CRS的inventory.xml输出来对比一下
[oracle@ ContentsXML]$ cat inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”Ora11g_gridinfrahome1″ LOC=”/u01/app/11.2.0/grid” TYPE=”O” IDX=”1″ CRS=”true”>
   <NODE_LIST>
      <NODE NAME=”nascds10″/>
      <NODE NAME=”nascds11″/>
   </NODE_LIST>
</HOME>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/db_1″ TYPE=”O” IDX=”2″>
   <NODE_LIST>
      <NODE NAME=”nascds10″/>
      <NODE NAME=”nascds11″/>
   </NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
通过对比我们已经非常明显的看到inventory.xml 里记录了详细ORACLE_HOME信息。
问题是Oracle是如何知道< Central Inventory >在哪里的呢?
答案很多人可能已经猜到了就是opatch lsinventory里列出来的
from           : /etc/oraInst.loc
这个/etc/oraInst.loc是有专业名称的,它的名字就叫Central Inventory Pointer File。
这个指向文件在不同的操作系统上有不同的默认位置,例如:
Linux And AIX — /etc/oraInst.loc
Other Unix Platforms — /var/opt/oracle/oraInst.loc
Windows — The pointer is located in the registry key:
\\HKEY_LOCAL_MACHINE\\Software\Oracle\inst.loc
Opatch就是通过Central Inventory Pointer File找到< Central Inventory >的路径,然后读取ORACLE_HOME的详细信息的。
现在又有新的问题了?
1.我们是否可以删除Central Inventory Pointer File?或者如果Central Inventory Pointer File丢失了会怎么样?
2. Central Inventory丢失或者损坏了会怎么样?
3. 我们能否手工修改Central Inventory的内容?
我先回答这几个问题,然后通过几个实验来看一下具体的情况。
答案:
1.不能删除Central Inventory Pointer File,如果Central Inventory Pointer File丢失了可以手工重建该文件。如果Central Inventory Pointer File丢失或者损坏那么opatch的所有命令都将失败。
2.也不能删除Central Inventory的文件,如果Central Inventory的文件是损坏的,比如内容不完整,或者内容是错误的,都将导致opatch失败。Central Inventory在ORACLE_HOME完好的情况下可以通过重建的方式解决以上问题。
3.Oracle官方不support手工修改Central Inventory的内容。

请注意以下所有的实验都不要在生产环境操作,以免导致Inventory错误.
实验1: 模拟Central Inventory Pointer File丢失的场景
[root@dbnode1]# mv /etc/oraInst.loc /etc/oraInst.loc.bak  <<<<<<=====这里手工mv了oraInst.loc文件,实验结束后需要手工再mv 回来
[root@dbnode1]# exit
exit
[oracle@dbnode1]$ cd /u01/app/oracle/product/11.2.0/db_1/OPatch/
[oracle@dbnode1 OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : n/a
   from           :
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : n/a<<<<<<=========
OPatch cannot find a valid oraInst.loc file to locate Central Inventory. <<<<<<=========
OPatch failed with error code 104<<<<<<=========
[oracle@dbnode1 OPatch]$
通过这个实验我们看到当Oracle不能找到一个有效的Central Inventory Pointer File时Opatch以失败告终。
这个时候如果我们确定Central Inventory是完好无损的情况下,可以在不同平台对应的默认路径下手工创建或者编辑oraInst.loc 文件
例如在linux平台,正常的内容如下
$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall
实验2: 模拟Central Inventory丢失的场景
[oracle@dbnode1 app]$ mv oraInventory oraInventory.bak<<<<<<=====这里手工删除了oraInventory
[oracle@dbnode1 app]$ ls
oracle  oraInventory.bak
[oracle@dbnode1 app]$ cd /u01/app/oracle/product/11.2.0/db_1/OPatch/
[oracle@dbnode1 OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-03-24_12-15-04PM.log
OPatch failed to locate Central Inventory.  <<<<<<=====报错无法加载Central Inventory.
Possible causes are:
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.
Possible causes are:
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
OPatch failed with error code 73<<<<<<=====
这个实验可以说明当Central Inventory 丢失时Opatch也是失败的。
实验3 模拟Central Inventory内容损坏
为实验目的我们手工修改ORACLE_HOME LOC成/u01/app/oracle/product/11.2.0/db_100 实际上这个目录是不存在的。但请注意手工修改inventory.xml的任何内容都是不support的。
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/db_100″ TYPE=”O” IDX=”1″/>
[oracle@dbnode1 OPatch]$ cd /u01/app/oraInventory/ContentsXML/
[oracle@dbnode1 ContentsXML]$ cp inventory.xml inventory.xml.bak
[oracle@dbnode1 ContentsXML]$ ls
comps.xml  inventory.xml  inventory.xml.bak  libs.xml
[oracle@dbnode1 ContentsXML]$ vi inventory.xml
[oracle@dbnode1 ContentsXML]$ cat inventory.xml
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2011, Oracle. All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.3.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”OraDb11g_home1″ LOC=”/u01/app/oracle/product/11.2.0/db_100″ TYPE=”O” IDX=”1″/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
[oracle@dbnode1 ContentsXML]$ /u01/app/oracle/product/11.2.0/db_1/OPatch/opatch lsinventory
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-03-24_12-25-43PM.log
List of Homes on this system:
  Home name= OraDb11g_home1, Location= “/u01/app/oracle/product/11.2.0/db_100”
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.<<<=========
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo
OPatch failed with error code 73
Central Inventory 如果损坏或者丢失是可以重建的。
重建过程非常简单,也不需要停机。我们来看下面的实验
实验4 重建Central Inventory
[oracle@dbnode1 ~]$ cd /u01/app
[oracle@dbnode1 app]$ ls
oracle  oraInventory
[oracle@dbnode1 app]$ mv oraInventory oraInventory.bak
[oracle@dbnode1 app]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-03-25_17-13-48PM_1.log
OPatch failed to locate Central Inventory.
Possible causes are:
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
LsInventorySession failed: OPatch failed to locate Central Inventory.<<<==== Central Inventory 被删除了,这个报错就是期待的报错。
Possible causes are:
    The Central Inventory is corrupted
    The oraInst.loc file specified is not valid.
OPatch failed with error code 73<<<<<<<<<<<<<=================
[oracle@dbnode1 app]$ cd $ORACLE_HOME/oui/bin
[oracle@dbnode1 bin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/oui/bin
[oracle@dbnode1 bin]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME=”/u01/app/oracle/product/11.2.0/db_1″ ORACLE_HOME_NAME=”OraDb11g_home1″
Starting Oracle Universal Installer…
Checking swap space: must be greater than 500 MB.   Actual 2996 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
‘AttachHome’ was successful.
[oracle@dbnode1 bin]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-03-25_17-16-37PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-03-25_17-16-37PM.txt
——————————————————————————–
Installed Top-level Products (1):
Oracle Database 11g                                                  11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.
Interim patches (1) :
Patch  17540582     : applied on Mon Mar 24 17:08:31 CST 2014
Unique Patch ID:  16985511
Patch description:  “Database Patch Set Update : 11.2.0.3.9 (17540582)”
   Created on 7 Jan 2014, 03:01:22 hrs PST8PDT
Sub-patch  16902043; “Database Patch Set Update : 11.2.0.3.8 (16902043)”
Sub-patch  16619892; “Database Patch Set Update : 11.2.0.3.7 (16619892)”
Sub-patch  16056266; “Database Patch Set Update : 11.2.0.3.6 (16056266)”
Sub-patch  14727310; “Database Patch Set Update : 11.2.0.3.5 (14727310)”
Sub-patch  14275605; “Database Patch Set Update : 11.2.0.3.4 (14275605)”
Sub-patch  13923374; “Database Patch Set Update : 11.2.0.3.3 (13923374)”
Sub-patch  13696216; “Database Patch Set Update : 11.2.0.3.2 (13696216)”
Sub-patch  13343438; “Database Patch Set Update : 11.2.0.3.1 (13343438)”
   Bugs fixed:
     13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
     13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
   ……………
     13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
     16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
     14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
     13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404
——————————————————————————–
OPatch succeeded.
[oracle@dbnode1 bin]$ cd /u01/app
[oracle@dbnode1 app]$ ls -l
total 12
drwxrwxr-x 9 oracle oinstall 4096 Nov 24 23:25 oracle
drwxrwx— 4 oracle oinstall 4096 Mar 25 17:16 oraInventory<<<<<<<==新创建的Central Inventory
drwxrwx— 5 oracle oinstall 4096 Mar 24 17:12 oraInventory.bak
Central Inventory里只记录了Oracle的HOME信息,但是在这个HOME下打了哪些patch Oracle是怎么知道的呢?
答案就是Local Inventory (Oracle Home inventory)。
Local Inventory (Oracle Home inventory) 存在于每一个ORACLE_HOME中,它记录了这个HOME中的相关信息,比如这个HOME中包含的组件,打过的补丁集(patchset 信息),打过的小补丁和PSU等信息。这些信息被记录在Local Inventory 中的comps.xml文件。
ORACLE_HOME/inventory/ContentsXML/comps.xml
同样Local Inventory (Oracle Home inventory)里的任何文件都是不允许手工修改的。
一个重要的信息是Oracle的Local Inventory (Oracle Home inventory)如果丢失或者损坏时无法重建的,只能通过重新安装ORACLE软件的方式解决。

参考文章:
FAQs on Central Inventory and Oracle Home Inventory (Local Inventory) in Oracle RDBMS (Doc ID 564192.1)
Steps To Recreate Central Inventory(oraInventory) In RDBMS Homes (Doc ID 556834.1)
Steps to Recreate Central Inventory in Real Applications Clusters (Doc ID 413939.1)

参与此主题的后续讨论,请回复blog,或者访问我们的中文社区,跟帖 “Oracle的Central Inventory和Local inventory详解

Siebel Open UI: Default Applet Override

One of the lesser known facts about Siebel Open UI – as of Innovation Pack 2013 which introduced database-stored manifest data – is that we can not only register JavaScript files (or web templates) with specific user interface objects but also create default entries for them, so the files will be loaded for each object of the same type. Something we could call a “global override”.

Using a global override is superior to using the postload event handler which might soon become bloated with all the stuff you want to achieve on non-specific objects.

Recently, I had some time to investigate this feature a bit more closely. Some of my dear readers might remember the “See-Through Applets” which involves a double-click event handler. When a form applet is double-clicked, the labels are replaced with metadata from the business component layer. Another double click reveals the table and column names where the data is stored. The final solution I presented used a postload event handler.

You can find a detailed description of the see-through applets here and here.

Siebel form applet in “see-through” mode 😉

So let’s see how I adjusted the see-through applet code to work with a global override. The goal was again to enable the functionality on every form applet with a minimum effort.

1. Create a physical renderer extension

The first thing I did was to create a physical renderer extension file. Just the “normal” thing

2. Register the custom file as a default entry

Next, I followed the usual steps to register the file in the Manifest Files view of the Administration – Application screen.

In the Manifest Administration view, the big difference was to query for the seeded DEFAULT FORM APPLET entries in the UI Objects list and copy the existing entry for the Physical Renderer usage type.

Then I proceeded to add an expression and my custom file (as usual). Here is the result:

Click to enlarge

The Open UI framework will read all entries and ensure that the as-delivered files and the custom overrides are loaded whenever a form applet needs to be renderered.

3. Implement the double-click handler

Now I was able to migrate the code which defines the double-click functionality from my custom postload event handler to the new default physical renderer extension’s ShowUI method. In fact I could comment out some lines as it was no longer necessary to establish a “connection” to the form applet. Another benefit of staying inside the framework 😉

The ShowUI method of the override physical renderer. Click to enlarge.

4. Test

I retired (i.e. commented out) the code in the postload event handler and reloaded the browser cache and voilá, the same as before but without extraneous code in the postload event handler.

Summary

“Global overrides” or default entries are a great alternative to clogging your postload event with JavaScript code. At the time of writing, there are various seeded default entries for form, list and tree applets which you should copy to create your custom default entry. Future versions might see other default entries such as for views.

have a nice day

@lex

SQL Server 2014: In-memory tables, Bw-Tree, and storage

SQL Server 2014 introduces hash indexes with in-memory optimized tables. I described some of their aspects in a previous blog post. These indexes are very efficient with lookup search operations, but have some limitations with operations like range scans, inequality predicates or scan order operations. These limitations are linked to the hash index design that stores rows in the index in a random order. Fortunately, non-clustered indexes for in-memory optimized tables (aka range indexes or Bw-Tree) solve this issue and, like hash indexes, involve a data row chain with their own structure on the leaf level.

In this blog post I would like to share with you an interesting consideration on Bw-Tree storage. Like a traditional b-tree index structure, accessing in-memory table data rows themselves first requires to go through the Bw-Tree structure prior to retrieving the corresponding row data in the chain.

At the leaf level of the Bw-Tree we have different pointers to the data (concerned in the first place) in the row chain. The pages in the index are linked together by logical pointers (called page identifiers or PID).

These PID are then translated to a physical address or a flash offset of a page in-memory or to a stable media storage. The Bw-Tree layer interacts with the cache layer that “abstracts” the physical page by using a table that maps the logical pages with the physical pages. Beside, others non-leaf pages in the Bw-Tree structure use the same mechanism. Here is a simplified schema for retrieving the data through the Bw-Tree structure:

  • Bw-Tree –> Mapping Table –> Physical Page

Abstracting the physical layer has some advantages. Indeed, changing the physical location of a page only requires to change the corresponding mapping to the mapping table. The Bw-Tree path will not be affected by this change because each page is logically linked to their own PID.

Now let’s demonstrate an interesting behavior of Bw-Tree (range indexes) with several extreme tests. For this purpose, we create an in-memory optimized table named hekaton_table with the following definition:

 

CREATE TABLE dbo.hekaton_table
(
       id INT NOT NULL PRIMARY KEY NONCLUSERED HASH WITH ( BUCKET_COUNT = 1048576 ),
       col1 CHAR(100) COLLATE French_BIN2 NOT NULL,
       INDEX idx_hekaton_table_col1 NONCLUSTERED ( col1 )
)
WITH
(
       MEMORY_OPTIMIZED = ON,
       DURABILITY = SCHEMA_AND_DATA
)

 

This table contains a hash index as a primary key on the id column and a nonclustered range index idx_hekaton_table_col1 that concerns the col1 column.

We will use the DMVs sys.dm_db_xtp_nonclustered_index_stats, sys.dm_db_xtp_memory_consumers and sys.dm_db_xtp_xtp_memory_stats during the test.

The first DMV sys.dm_db_xtp_nonclustered_index_stats includes statistics about operations on non-clustered indexes in memory-optimized tables.

select
       o.name as table_name,
       i.name as index_name,
       nis.internal_pages,
       nis.delta_pages,
       nis.leaf_pages
from sys.dm_db_xtp_nonclustered_index_stats as nis
       join sys.objects as o
             on o.object_id = nis.object_id
       join sys.indexes as i
             on i.object_id = nis.object_id
                    and i.index_id = nis.index_id
where i.name = ‘idx_hekaton_table_col1’;

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats

 

The second DMV sys.dm_db_xtp_memory_consumers provides useful information on the memory consumers. We are concerned by the Range index heap consumer in this example.

 

select
       o.name as table_name,
       i.name as index_name,
       xmc.memory_consumer_desc,
       xmc.allocated_bytes / 1024 as allocated_kb,
       xmc.used_bytes
from sys.dm_db_xtp_memory_consumers as xmc
       join sys.indexes as i
             on xmc.object_id = i.object_id
                    and xmc.index_id = i.index_id
where i.name = ‘idx_hekaton_table_col1’;

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats_2

 

The third DMV sys.dm_db_xtp_table_memory_stats provides information about the size of an in-memory table inside the memory.

 

select
       memory_used_by_table_kb,
       memory_used_by_indexes_kb
from sys.dm_db_xtp_table_memory_stats
where [object_id] = object_id(‘hekaton_table’)

 

For the first test, we will introduce 200K rows of data in the col1 column with a high cardinality. All rows will have a distinct value.

 

declare @i int = 1;
 
while @i 200000
begin
       insert dbo.hekaton_table values (@i, ‘t_’ + cast(@i as varchar(10)));
       set @i += 1;
end

 

Then we take a look at the result of all DMVs:

  • sys.dm_db_xtp_nonclustered_index_stats

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats_3

 

  • sys.dm_db_xtp_memory_consumers

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats_4

 

  • sys.dm_db_xtp_xtp_memory_stats

 

billet_6_-_sys_dm_db_xtp_table_kb_7

 

In the second test, we will introduce the same number of data rows in the col1 column with a low cardinality. All rows will have the same value. Here is the results of the second test:

  • sys.dm_db_xtp_nonclustered_index_stats

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats_5

 

  • sys.dm_db_xtp_memory_consumers

 

billet_6_-_sys_dm_db_xtp_nonclustered_index_stats_6

 

  • sys.dm_db_xtp_xtp_memory_stats

 

billet_6_-_sys_dm_db_xtp_table_kb_8

 

Wow! Only one leaf page with 400 bytes is used during the second test versus 5363 pages allocated with 26485848 bytes during the first test. We retrieved the same total size for the in-memory data itself (29687 KB).

What’s the matter?

In fact, there is a big difference between traditional non-clustered indexes and Bw-Tree. Unlike traditional non-clustered indexes where there is a pointer per row regardless of the uniqueness of the index key, the Bw-Tree has only one pointer per unique index value. This changes the amount of used storage.

For a table stored in-memory, this is an interesting optimization Laughing

How about the data inserted during the second test?

Rows with the same value are grouped in the same row chain as shown below:

 

billet_6_-_nonclustered_range_index

 

The use of the sys.dm_db_xtp_nonclustered_index_stats DMV also introduces new pages concepts. I advise you to have a look at the Microsoft research on Bw-Tree.

As shown above, there are two others page type columns:

  • The internal pages that represent the top level of the Bw-Tree (root and non-leaf pages in a classical B-Tree structure)
  • The delta pages that are “special pages” containing an operation code (insert, delete) and a memory value which is the memory address in the first row in a chain of records

These pages are part of a special mechanism that allows to incrementally update the page state in a latch-free manner.

Remember: With in-memory tables, locks and latchs do not exist anymore! I will soon prepare another blog post dedicated to this interesting mechanism.

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569