EBS 11i升级到R12
Posted on July 5, 2010 Filed Under E-Business Suite
花了大概一周时间完成了一套11i系统(到12.1.1)的升级，之所以耗费如此多时间，简单归纳了一下大致有以下原因：
- 历史遗留的配置问题（比如用中文做关键词），此类问题导致数据转换错误。
- 客户化（索引，物化视图等）导致升级错误。
- 测试机性能差，耗时颇巨。期间多次由于内存的原因，导致java程序出错。
- 很多R12本身的升级过程中的bug需要处理。
- 部分业务数据升级时出错，可能是旧数据完整性被破坏所致。
- 一些前期工作跳过没错，这本来可以缩短升级过程的一部分排错时间。
- 其他的一些可在升级之前就预先解决的问题。
整体上说，机器的性能问题很关键，有些步骤运行动辄数个小时，有些是升级程序有优化余地而事先没有发现，这些一般都在metalink上有了解决办法。对于升级，很多人第一反应就是克隆环境升级，然后导入最新数据并进行主备切换，其实这种方案对于多数企业而言很难，因为升级过程中会修改大量的表结构，甚至很多应用级别的调整，我很怀疑有哪家公司能够做到对Oracle EBS的数据结构了解的如此洞彻明晰，至少国内没有。这是我第一次对11i进行升级，积累了一些经验，在此分享。
升级前的准备
升级前的准备包括三方面，一个是应用本身的准备工作，这个在升级手册里已经详细说明；另一个就是对服务器性能做一个充分的评估，确保整个升级过程不会跳到deadline之外；还有一个，就是将所有能提前做的工作都做好，比如invalid objects，韩语分词补丁，OLAP升级等等，这些在数据库升级阶段不是必须的，但是EBS升级时却是无法直接忽略的步骤，此外可靠的网络连接也很重要，patching过程中断线后虽然可以继续，但是光启动就要花不少时间。
升级过程中的注意事项
这里依旧按照升级步骤来进行说明。
- Disable AOL Audit Trail (conditional)
关闭所有审计功能，包括标准功能提供的，以及客户自行在DB级别上添加的。 - Shut down application tier listeners and concurrent managers (required)
- Migrate database to at least Oracle10g Release 2 (conditional)
这一步之前已经做了，考虑到停机时间，分两步走是比较合适的方案。 - Update init.ora with upgrade parameters (required)
同上，升级数据库时已经基本做好了。 - Disable custom triggers, constraints, and indexes (conditional)
注意：最好将所有直接关联到标准表的客户化索引和Materialized View都drop掉，这很可能和升级程序有冲突。比如客户化索引，很可能升级过程中就会创建同样的索引（不同名），也可能修改表结构，这些操作都可能引起错误。 - Drop MRC schema (conditional)
- Back up the database (recommended)
- Ensure that Maintenance Mode is enabled (required)
在11i环境中启用维护模式，如果数据被一些外部程序所用，则最好在升级期间停掉。 - Apply AD 12.1.1 upgrade driver (required)
先取消appltest的环境变量(.profile)，然后source新的APPS_host.env，该文件在新的$APPL_TOP目录下，如apps/apps_st/appl/APPSTEST_erptest.env。
adpatch 时可能会报如下错误：ld.so.1: adpatch: fatal: .../apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64
原因是如下错误所导致的：
进 apps/tech_st/10.1.2/lib32 目录ls -lldflags -> /d4/R12/ab/apps/R1211XB9/apps/tech_st/10.1.2/lib/ldflags
ldflags链接到了一个不存在的文件上，这是R12本身的bug，解决办法：
$ rm $ORACLE_HOME/lib32/ldflags $ ln -s $ORACLE_HOME/lib/ldflags $ORACLE_HOME/lib32/ldflags
然后 $ORACLE_HOME/bin/genclntsh -32 重新生成libclntsh.so.10.1。
注意：$ORACLE_HOME请指向10.1.3目录，否则在Post-Upgrade还得一番折腾。 - Run the American English upgrade patch driver (required)
这一步耗时最长，问题也最多。在我的测试环境中，因为机器内存小(有两套克隆环境)，patching 过程经常报java错误，需要留意检查adworkxxx.log日志，若发现错误可以尝试用adctrl去failed该job等待其重新处理即可通过。该步骤会把测试机拖得非常慢，每个adworker会带出一个java进程，占用内存非常大，所以将worker设定为一个比较小的数值会更好，比如8个，等之后运行sql等占用内存较少的步骤时，再将adworker数目调大。错误：
CREATE OR REPLACE SYNONYM APPS.CST_ACCRUAL_ACCOUNTS FOR BOM.CST_ACCRUAL_ACCOUNTS AD Worker error: The following ORACLE error: ORA-00955: name is already used by an existing object
同义词和一个视图名称一样了。这个错误很低级，我的解决办法是 DROP VIEW APPS.CST_ACCRUAL_ACCOUNTS。
错误:
A database error occurred: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column The error occurred while executing the following statement: INSERT INTO FND_SEED_STAGE_ENTITY (SEQ, CONFIG_ID, TOP_ENTITY_SEQ, LAST_DB_UPDATED_BY, LAST_DB_UPDATE_DATE, LAST_FILE_UPDATED_BY, LAST_FILE_UPDATE_DATE, ACTION, EXEC_STATUS, COMMIT_FLAG, BIND_VAR_METADATA, PARENT_PK_VALUE ,BIND_VALUE0 ,BIND_VALUE1 ,BIND_VALUE2 ,BIND_VALUE3 ,BIND_VALUE4 ,BIND_VALUE5 ,BIND_VALUE6,BIND_VALUE7 ,BIND_VALUE8 ,BIND_VALUE9 ,BIND_VALUE10 ,BIND_VALUE11 ,BIND_VALUE12 ,BIND_VALUE13 ,BIND_VALUE14 ,BIND_VALUE15 ,BIND_VALUE16 ,BIND_VALUE17 ,BIND_VALUE18 ,BIND_VALUE19 ,BIND_VALUE20 ,BIND_VALUE21 ,BIND_VALUE22 ,BIND_VALUE23 ,BIND_VALUE24 ,BIND_VALUE25 ,BIND_VALUE26 ,BIND_VALUE27 ,BIND_VALUE28 ,BIND_VALUE29 ,BIND_VALUE30) VALUES (:seq, :config_id, :top_entity_seq, NULL, NULL, NULL, NULL, 'IGNORE', '0', NULL, :bvar_metadata, :parent_pk_value ,:bind_value0 , :bind_value1 ,:bind_value2 ,:bind_value3 ,:bind_value4 ,:bind_value5 ,:bind_value6 ,:bind_value7 ,:bind_value8 ,:bind_value9 ,:bind_value10 ,:bind_value11 ,: bind_value12 ,:bind_value13 ,:bind_value14 ,:bind_value15 ,:bind_value16 ,:bind_value17 ,:bind_value18 ,:bind_value19 ,:bind_value20 ,:bind_value21 ,:bind_va lue22 ,:bind_value23 ,:bind_value24 ,:bind_value25 ,:bind_value26 ,:bind_value27 ,:bind_value28 ,:bind_value29 ,:bind_value30)
旧的NLS_LANG是American_America.ZHS16GBK，新的NLS_LANG是American_America.UTF8（这是rapidwiz默认赋值的），如果在安装就设置NLS_LANG和旧的一致，则不会出现问题，但是也无法转换form等编码了。
解决办法：用另一用户登录，修改NLS_LANG，手工执行fndload，然后adctrl用隐藏选项8来跳过该步骤。错误:
如果升级数据库时没有升级OLAP，则会出现一个OLAP相关的错误，此时需要升级OLAP到64位。也可以通过重建AW来解决：exec dbms_aw.execute('aw delete zpb.zpbcode') exec dbms_aw.execute('aw delete zpb.zpbdata') exec dbms_aw.execute('aw delete zpb.zpbannot') @appl_top/zpb/12.0.0/patch/115/SQL/zpbmakeaws.sqlNote:
EPB AW’s Cannot Be Attached Or Do Not Exist [ID 795247.1]错误4:
eamsnupd.sql 步骤出错，此步进行前需要预先设置好Installed Base中相关参数和配置文件。
Note:
Enterprise asset Management (EAM) Upgrade Notes to R12 – Integration and Licence [ID 884201.1]注：自R12起，IB和eAM将紧密集成。Oracle 真应当将这部分内容写到升级文档中，不了解Installed Base就尝试升级是个悲剧。
phase=A232 eamdffup.sql出错：
sqlplus -s APPS/***** @.../apps/apps_st/appl/eam/12.0.0/patch/115/sql/eamdffup.sql DECLARE * ERROR at line 1: ORA-06501: PL/SQL: program error ORA-06512: at "APPS.FND_FLEX_DSC_API", line 1626 ORA-06501: PL/SQL: program error ORA-06512: at "APPS.FND_FLEX_DSC_API", line 298 ORA-01403: no data found ORA-06512: at line 177
仔细检查问题sql，发现由以下数据引起的：
SELECT * FROM fnd_descr_flex_contexts_tl f WHERE f.application_id = 426 AND f.descriptive_flex_context_code IN ('PE', 'PG');解决办法：将弹性域名称从中文（能源计量：电能/能源计量：气体）修改为英文。
phase=A250 apilnupg.sql有严重的性能问题，1天都跑不完
解决办法：create index AP_INVOICE_DISTRIBUTIONS_N25 on AP_INVOICE_DISTRIBUTIONS_ALL(PARENT_REVERSAL_ID);
Note:
R12 : Performance Issue While Runing apilnupg.sql (Upgrade to 12.1.1) [ID 942694.1]phase=A260 appdstln.sql 报错
sqlplus -s APPS/***** @.../apps/apps_st/appl/ap/12.0.0/patch/115/sql/appdstln.sql &un_ap &batchsize 7 16 DECLARE * ERROR at line 1: ORA-01400: cannot insert NULL into ("AP"."AP_PAYMENT_HIST_DISTS"."INVOICE_DISTRIBUTION_ID") ORA-06512: at line 38解决办法：修改AP_PAYMENT_HIST_DISTS.INVOICE_DISTRIBUTION_ID 和 AP_PAYMENT_HIST_DISTS.AMOUNT 允许为空，找到那些为空的数据查明原因。正式环境升级时应当避免该情况发生。
注：这其实不是真正意义上的“解决”办法，关键是找到哪些数据有问题，为什么有问题。phase=A322 csxruprf.sql 报错
sqlplus -s APPS/***** @.../apps/apps_st/appl/cs/12.0.0/patch/115/sql/csxruprf.sql DECLARE * ERROR at line 1: ORA-20002: ORA-20001: -28102-ORA-28102: policy does not exist. ORA-06512: at line 146
解决办法：
SELECT nvl(sr_agent_security, 'XXXX') FROM cs_system_options;
如果查询结果为ANONE，那么它认为已经创建policy，此处只需要生效即可，而实际上系统中并未存在这些policy。通过手工执行相关sql，并跳过该job继续。
phase=A327 FNDFFVGN 报错
.../apps/apps_st/appl/fnd/12.0.0/bin/FNDFFVGN &ui_apps 0 Y 2 401 'MTLL' 'MTL_ITEM_LOCATIONS_KFV' 'Y' Log filename : .../apps/apps_st/appl/admin/TEST/log/l9017672.req Report filename : .../apps/apps_st/appl/admin/TEST/out/o9017672.out Segmentation Fault - core dumped AD Worker error: The above program failed with error code -117. See the AD Worker log file and/or the program log file for details.
解决办法：这个只是编译弹性域，可以跳过该步骤，应用起来后重新编译弹性域。
- Run the NLS upgrade patch driver (conditional)
- Apply latest product patches (required)
- Synchronize NLS and American English product patches (conditional)
- Disable Maintenance Mode (required)
- Reset init.ora parameters (required)
数据库升级时已经做了 - Back up Oracle E-Business Suite (recommended)
- 出错
FRM-30064: Unable to parse statement select upet.type_name EPC_type_name, upec.name category_name, upet.type_id, upec.category_id
from mgd_idencoding_category upec,
mgd_idencoding_type upet
where upec.category_id = upet.category_id
and ((NVl(upet.partition_value,0)= 0 and upet.category_id = 1 ) or upet.category_id <> 1)
order by upec.category_id,type_name.
ORA-00942: table or view does not exist
Record Group EPC_RULE_RG
Form: WMSLABEL
FRM-30085: Unable to adjust form for output.
解决办法：这个用不到，当作成功继续下一步。
- 出错
.../apps/tech_st/10.1.2/bin/rwconverter userid=APPS/***** source=.../apps/apps_st/appl/xtr/12.0.0/reports/ZHS/XTRTMBLT.rdf dest=.../apps/apps_st/appl/admin/TEST/out/tmp001.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes ld.so.1: rwconverter: fatal: librw.so: open failed: No such file or directory Killed ERROR [code=-119] generating report "XTRTMBLT.rdf" from input file .../apps/apps_st/appl/xtr/12.0.0/reports/ZHS/XTRTMBLT.rdf
是编译rdf的程序有问题，参考文档：
On R12.1.1/Solaris Platform , Generating Oracle Reports Files failed on : " ld.so.1: rwconverter: fatal: librw.so: open failed: No such file or directory", How to get a Potential quick Solution ?. [ID 1067786.1]
标准功能的升级并不难，虽然中途有很多问题，但是整体上没有多大难度(事实上这世界上的事情只有烦的，没有难的)。最耗精力和时间的，估计反而是那些凌乱的客户化应用。在ERP项目实施过程中，虽然每个人都知道文档的重要性，但是数年下来，回过头来整理时，往往会发现很多文档消失不见，或者有文档但是形同于无。这些是应用升级后需要耗费时间的地方。
对于客户化应用的升级，没有标准答案。
EBS 11i数据库升级(9i->10g)几点事项
Posted on June 9, 2010 Filed Under Database, E-Business Suite
最近几日在评估和测试EBS的系统升级(11i升级到R12)，虽然官方的升级文档里介绍的比较详细了，但是依旧会出现一些容易疏忽的问题，这里做一些记录。这里并不是单纯的数据库升级，需要考虑EBS的特殊应用。对于升级方案，标准的有三种：
- 同时升级数据库和应用。
- 先升级数据库到10gR2，然后升级应用到R12。
- 先升级数据库到11gR2，然后升级应用到R12。
其中第2和第3种方案有点类似，都是将整个升级方案划分为两个阶段，只是数据库版本不同。实际上，从技术上看，第1种方案也是两步走，只是在应用层跳过一些过渡性的补丁直接升级到R12。
在数据库升级方面，升级路线主要有下面两种：
- 路线1: 9.2.0.6 -> 9.2.0.8 -> 11.2.0.1
如果是Solaris系统，则要求系统版本至少为 Solaris 10 Update 6。 - 路线2: 9.2.0.6 -> 10.2.0.1 -> 10.2.0.4
注：最新patchset是10.2.0.5，只是升级文档依旧停留在10.2.0.4。
从一般认识上讲，Oracle数据库在下一个大版本出来后，上一个版本才被认为是相对更稳定的，所以此处选择第二条路线。
数据库升级方式简单而言有以下几种：
- DBUA，直接通过图形化工具升级，这个最简单。
- Manual，类似于上一种方式，只是手工进行各个步骤的升级操作。
- Export/Import
有利于数据表的整理，重构数据库，比如修改字符集、数据文件、表空间参数等，在升级时间上，比DBUA和Manual两种方式都要长。 - 利用数据复制等技术，升级备用环境后再切换。
由于允许停机，并且暂无特殊的要求，所以这里使用DBUA做升级操作。
在升级之前，建议卸载statspack，并且先解决Invalid Objects的问题。此外，数据库升级后DBLINK需要重建，所以先准备相关重建脚本。下面按照文档步骤升级数据库9.2.0.6 至 10.2.0.4，每个步骤都列在下面，对几个步骤中需要额外关注的地方做了备注。
- Verify software versions
检查现在软件版本，基本上应该不会有问题的，留意一下AD版本，最新版本是11i.AD.I.7，不过要求11i.AD.I.6就可以了。 - Migrate to Oracle Portal 10g (conditional)
- Deregister the current database server (conditional)
- Update application tier context file with new database listener port number (conditional)
- Export OLAP analytical workspaces (conditional)
- Prepare to create the 10.2.0 Oracle home
设置ORACLE_HOME环境变量，如 export ORACLE_HOME=/u08/test/proddb/10.2.0 - Install the base 10.2.0 software
安装10gR2软件，不要选择升级现有数据库，因为要先打一些补丁。这个过程最后会写oraclehomproperties.xml，要保证对应目录有可写权限。比如我曾遭遇了这个错误
inventory/ContentsXML/oraclehomproperties.xml (Permission denied). - Install Oracle Database 10g Products from the 10g Companion CD
- Perform 10.2.0.4 patch set pre-installation tasks
这是正常的打数据库补丁操作，留意几个环境变量的修改就可以了。如：export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PERL5LIB=$ORACLE_HOME/perl/lib:$ORACLE_HOME/perl/lib/site_perl:$PERL5LIB
- Perform 10.2.0.4 patch set installation tasks
- Create nls/data/9idata directory
- Apply additional 10.2.0.4 RDBMS patches
- Shut down Applications server processes and database listener
- Prepare to upgrade
- Upgrade the database instance
执行utlu102i.sql查看升级后的变动情况，然后执行DBMS_STATS.GATHER_SCHEMA_STATS 收集统计数据以加快升级速度。在升级的时候如果没有列出所需的ORACLE_HOME，则需要检查并整理一下oratab。 - Modify initialization parameters
对于sga_target等参数，最好先计算以下之前的SGA大小再设置，其余的参考文档设置即可。 - Additional database configuration
- Perform 10.2.0.4 patch set post-installation tasks
- Install Oracle Data Mining and OLAP
- Natively compile PL/SQL code (optional)
- Fix Korean lexers
升级韩语分词，对于简体中文用户不必进行。
注意：如果将来需要升级EBS至R12，则这一步是必须的，否则升级过程中会出错：Uploading from staging tables Error loading seed data for CS_KB_SOLN_CATEGORIES_VL: CATEGORY_ID = 1, ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexUpdate ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column for primary key DRG-13201: KOREAN_LEXER is desupported ORA-30576: ConText Option dictionary loading error
- Import OLAP analytical workspaces (conditional)
同上，如果将来需要升级EBS至R12，则这一步是必须的，稍微不同的时，到时可以不必做“升级”操作，而是直接删除重建：exec dbms_aw.execute('aw delete zpb.zpbcode'); exec dbms_aw.execute('aw delete zpb.zpbdata'); exec dbms_aw.execute('aw delete zpb.zpbannot'); sqlplus '/ as sysdba' @$APPL_TOP/zpb/12.0.0/patch/115/SQL/zpbmakeaws.sql - Start the new database listener (conditional)
- Run adgrants.sql (conditional)
- Grant create procedure privilege on CTXSYS
- Implement and run AutoConfig
先检查TNS_ADMIN变量是否指向新的路径，并检查listener.ora，看SID_LIST_TEST中是否已经添加了相应的SID，netca创建的listener.ora可能会缺少这个信息。
这里尤其需要留意的是，adbldxml.pl 创建配置文件后，如果做adconfig.pl会报错：Can't locate object method "runPipedCmd" via package "ADX::util::Sysutil" at /u08/test/proddb/10.2.0/appsutil/bin/adconfig.pl line 806.
这是由于PERL5LIB没有包含新的appsutil中的perl lib所致，手工加上再执行就可以了。
afdbprf.sh 这一步还会报错：ORA-12504: TNS:listener was not given the SID in CONNECT_DATA
手工执行可以通过，可能是afdbprf.sh 中传递变量有问题。
- Gather statistics for SYS schema
dbms_stats.gather_schema_stats这一步没有问题，但是执行dbms_stats.gather_fixed_objects_stats时会报错：declare * ERROR at line 1: ORA-00600: internal error code, arguments: [1350], [1], [13], [], [], [], [], [] ORA-06512: at "SYS.DBMS_STATS", line 13210 ORA-06512: at "SYS.DBMS_STATS", line 13517 ORA-06512: at "SYS.DBMS_STATS", line 14039 ORA-06512: at line 3 ORA-06512: at line 33
这应该是个BUG，据说在11gR2中被修复了。
- Re-create custom database links (conditional)
升级之前就已经准备好了DBLINK的重建脚本，直接重建即可。 - Re-create grants and synonyms
- Apply Oracle Receivables patch
- Restart Applications server processes (conditional)
按标准步骤做完后，数据库成功升级到10.2.0.4，升级后需要留意以下事项：
- 检查 .profile ，使用使用新的环境变量。
- 如果有快速启动脚本之类的，看是否指向了新的路径。
- 检查AutoConfig新创建的env文件，看PATH变量是否正确，删除指向旧数据库的路径。
- 用adadmin重新生成messages并编译包，否则Web页面登录可能会报错：
oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.sql.SQLException: ORA-00003: Message 3 not found; product=RDBMS; facility=ORA ORA-06512: ? "SYS.DBMS_UTILITY", line 70 ORA-06512: ? line 1
- 最好转换SYSTEM表空间到Local Management。
主要参考文档
Database Preparation Guidelines for an E-Business Suite Release 12.1.1 Upgrade [ID 761570.1]
Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0) [ID 362203.1]
关闭功能顾问的那些诊断模式
Posted on May 28, 2010 Filed Under E-Business Suite
相对于SAP而言，Oracle ERP的另一个显著特征或许就是大量的BUG和补丁吧，也或许正因为如此，Oracle ERP中的故障诊断越来越便捷，方式也越来越，有Server级的诊断（如Form Server），有DB级的诊断，也有功能级的诊断（输出程序步骤和关键业务数据）。对于Form/Report Server之类的故障诊断，通常有专门的技术顾问负责，多数情况下，功能顾问在Oracle Support的建议下也会进行各种形式的、针对各个模块和功能的诊断，有的记录在表里，有的形成日志文件，也有的直接显示在页面上。对于不影响用户操作的诊断，事后很容易就忘记了关闭，于是大量的五花八门的被遗弃的debug和trace偷偷占用着宝贵的系统资源。
通常，功能顾问们涉及的诊断功能可直接通过配置文件启用，主要有下面这些：
- FND：启用调试日志 启用日志。如果为否 (‘N’)，则运行时不会进行记录和发出预警。
- OE：调试 选择“是”以激活 Oracle 订单分录管理系统表单用户退出和并发程序中的调试信息
- OE：调试跟踪 选择“是”以激活 oracle 订单分录管理系统并发程序的跟踪输出
- OSO：启用调试消息 用于显示/隐藏调试消息的 Sales Online 配置文件
- PA：调试模式 指明是否按调试模式运行项目会计管理系统报表和流程；在调试模式下运行时，线索会被打开，附加信息会打印至日志文件
- PO：将调试工作流设置为“打开” 启用/禁用 PO 工作流调试模式
- PO：启用对接收处理程序的 SQL 跟踪 启用数据库 SQL 跟踪，以便调试接收事务处理程序。
- RCV：调试模式 如果将此模式设置为“是”，则将在日志文件中打印消息。
- 初始化 SQL 语句 – 自定义 初始化会话的自定义 SQL 语句
其中“初始化 SQL 语句 – 自定义”比较特殊，可以自行设置event。它的格式如：
BEGIN fnd_ctl.fnd_sess_ctl('','','','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER=' || '''' ||'4269824.999' || '''' || ' EVENTS =' || '''' ||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ' || '''');END;
其实就相当于每次开启会话时自动执行了类似如下的命令：
alter session set events='10046 trace name context forever, level 12' ;
用下面的SQL可以查询到各个级别的配置文件设置情况：
SELECT n.user_profile_option_name NAME,
to_char(v.last_update_date, 'yyyy-mm-dd') "Last Updated",
decode(v.level_id,
10001,
'Site',
10002,
'Application',
10003,
'Responsibility',
10004,
'User',
10005,
'Server',
10007,
'SERVRESP',
'UnDef') level_set,
decode(to_char(v.level_id),
'10001',
'',
'10002',
app.application_short_name,
'10003',
rsp.responsibility_key,
'10005',
svr.node_name,
'10006',
org.name,
'10004',
usr.user_name,
'10007',
'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_vl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND usr.user_id(+) = v.level_value
AND v.level_id = 10004 -- User
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
ORDER BY n.user_profile_option_name,
level_set;
对于懒人们，不妨做成自动预警，每周末检查一次就可以了。
计划管理器(Planning Manager)技术概要
Posted on May 27, 2010 Filed Under E-Business Suite, Supply Chain
计划管理器简介
计划管理器属于Immediate类型的可执行并发程序，作为MRP 管理器的子例程运行。MRP 管理器是一种特殊的并发管理器，和计划管理器是完全不同的存在。它主要作用在以下几个方面：
- 针对发运冲减MDS
- 针对完工产品冲减MPS
- 预测冲减
- MRP接口数据的处理
- 相关数据清理
实际上，计划管理器和相关的MPS/MRP冲减工作流程是通过下列存储过程完成的：
- MRP_MANAGER_PK 这个包里面的各个过程执行不同的任务。
- MRP_UPDATE_MRP_INFO_PK.mrp_update_mrp_cols 该过程用于MPS Relief Worker
- MRP_AUTO_REDUCE_PK.mrp_auto_reduce_mps 该过程用于每日清理
计划管理器的启动
进入供应链管理员(Supply Chain Planner)职责，在设置菜单里有计划管理器的设置。
间隔时间是指计划管理器每次隔多久处理一次数据，它是指每次的开始时间的间隔长度，如果执行时间超过间隔时间，则执行结束后等待该时间后再次执行。若间隔时间太短，则可以近乎当作不间断执行，这时的冲减也是最及时的，但是系统资源消耗较大。同时，间隔时间过短，日志会更加大，如果启用了DEBUG模式，则每日的日志将可能多出数百兆(视业务数据多少有所不同)。
如果计划管理器处于Active状态，则设置窗口中的Messages界面会显示每次执行的时间。该界面仅仅告知每次的执行时间，并没有多大保留意义，可以安全删除。如果想停止计划管理器，则直接根据请求号取消相应的并发请求即可。需要留意的是，由于计划管理器是依附于MRP管理器执行的，因此在异常诊断时，应当先确认MRP管理器是正常运行的。
每次重新启动计划管理器时，都会自动提交“Planning Manager Worker (once-a-day tasks)”。每天，计划管理器都会分配一个新的请求号，开始新的请求号时，“Planning Manager Worker (once-a-day tasks)”工作流程也会被自动提交。这也是个并发程序（派生类型）， 该程序主要执行各类清理工作。其中，对于MRP相关接口表的清理，主要取决于配置文件 MRP:Interface Table History Days 设置的保留天数。涉及的MRP接口表包括：
- MRP_FORECAST_INTERFACE
- MRP_SCHEDULE_INTERFACE
- MRP_RELIEF_INTERFACE
- MRP_FORM_QUERY
- MRP_WORKBENCH_CRITERIA
- MRP_WORKBENCH_QUERY
- MRP_LOAD_PARAMETERS
MDS Relief
创建新的订单时，会有记录插入到MTL_DEMAND，当发运确认后，相关记录会插入MTL_TRANSACTIONS_INTERFACE，接口数据被验证通过才会在MTL_MATERIAL_TRANSACTIONS产生新的纪录，在此同时，如果库存事务管理器判断出所处理的记录是销售订单，就会插入记录到MRP_RELIEF_INTERFACE以供冲减MDS。
在MRP_RELIEF_INTERFACE中的MDS待冲减条目的各字段含义如下：
- RELIEF_TYPE 为1
- PROCESS_STATUS 为2，表示等待处理
- DISPOSITION_ID 为MTL_DEMAND.DEMAND_ID
对于MDS冲减，计划管理器仅仅处理符合以上条件的记录。
MPS Relief
之前介绍过 MPS Relief Worker，这里补充一下MPS冲减的数据来源。
当采购申请、采购订单、接收事务等采购相关数据产生时，会有记录插入到MTL_SUPPLY，这些数据都作为供应用于MPS冲减。当然离散任务的发放、完工时，会有记录插入到WIP_DISCRETE_JOBS，此时也会触发冲减。对于冲减记录的产生，在系统内部实际上是通过Trigger来实现的，大致有以下Trigger:
- MTL_SUPPLY_T 作用于表MTL_SUPPLY
- WIP_DISCRETE_JOBS_BRI 作用于表WIP_DISCRETE_JOBS，针对新增离散任务
- WIP_DISCRETE_JOBS_BRU 作用于表WIP_DISCRETE_JOBS，针对离散任务状态的修改
- WIP_DISCRETE_JOBS_BRD 作用于表WIP_DISCRETE_JOBS，针对离散任务的删除
在MRP_RELIEF_INTERFACE中的MPS待冲减条目的各字段含义如下：
- RELIEF_TYPE 为2
- PROCESS_STATUS 为2，表示等待处理
- DISPOSITION_ID 为若来源于MTL_SUPPLY，则该字段视来源分别可能是REQ_HEADER_ID, SHIPMENT_HEADER_ID, PO_HEADER_ID，若来源于WIP_DISCRETE_JOBS，则该字段对应于WIP_ENTITY_ID。
相关的配置文件
MRP:Interface Table History Days 接口数据保留日期，仅删除已完成的记录
MRP:Planning Manager Batch Size 指定每个Worker的处理记录数量
MRP:Planning Manager Max Workers 最大同时启动多少个Worker
MRP:Perform Planning Manager Functions in Loads 让主计划程序执行计划管理器的相关功能
MRP:Consume MDS 启用MDS冲减
MRP:Consume MPS 启用MPS冲减
MRP:Debug Mode 启用诊断模式，会在日志中输出运行过程中的相关信息
MRP:Trace Mode DB级的诊断，Trace文件产生在USER_DUMP_DEST目录下
Planning Manager诊断过程和10046事件
Posted on May 26, 2010 Filed Under Database, E-Business Suite
在一次Planning Manager性能问题的诊断中，用到了Oracle ERP环境中非常典型的诊断操作和过程，特全程记录于此。
当发现某个程序存在性能问题时，首先当然是快速检查一下SESSION的相关信息，看看主要在执行或等待些什么：
SELECT stat.sid,
n.name,
n.class,
stat.value
FROM v$sesstat stat,
v$statname n
WHERE stat.statistic# = n.statistic#
AND stat.sid = 121
ORDER BY upper(n.name);
这里观察到bytes received via SQL*Net from client, bytes sent via SQL*Net to client 和 SQL*Net roundtrips to/from client 都在飙升。这对应了两个events：SQL*Net message to client和SQL*Net message from client，这两个事件数值大并不意味着网络状况不好，前者仅仅表示Oracle数据库将数据放入TCP send buffer的时间，而后者表示从客户端接收到反馈指令的时间，两者反映了SQL*Net和数据库之间的时间消耗，而不是网络传输的时间消耗。基于对系统架构的了解，排除SQL*Net的性能问题，那么两个事件预示了什么呢？
SELECT * FROM v$sess_io v WHERE v.sid = 61;
观察会话中的数据库读取情况，CONSISTENT_GETS 值在持续增长。结合上面SQL*Net的情况，大致可以判断出该程序在反复执行某个查询语句，每次获取的数据量很小但频率很高。我们知道，客户端在读取数据时，可能影响的因素有Array Fetch Size、SQL*Net中的SDU和数据库服务器每次读取的数据量，简单计算SQL*Net每次传输大致的数据量后将问题定位到SQL的性能问题上。
开始跟踪Planning Manager的全过程。Planning Manager 是一种特殊的并发程序(Immediate)，它直接依附于并发管理器MRP Manager执行。但是作为并发请求，它的常规诊断方式是一样的。如果有计划管理器在运行，则先停掉它，然后启用Trace：
关于详细操作和Trace文件的查找可参考另一篇文章《跟踪(Trace)并发请求》
进入MRP计划管理员设置界面，启动Planning Manager。对于其他独立的并发请求，可以直接手工提交。等待Planning Manager处理完预先准备的接口数据后，立即取消该请求，并取消Trace。
接下来就是对Trace文件的解读。对于并发请求的Trace，其实就是对应的一次10046事件(event)，该事件可简单分为以下四个级别：
- Level 1: 跟踪SQL
- Level 4: Level 1 + 跟踪绑定变量
- Level 8: Level 1 + 跟踪等待事件
- Level 12: Level 4 + Level 8 (跟踪SQL, 绑定变量和等待事件)
手工方式的话可通过下面的命令启用：
alter session set events='10046 trace name context forever, level 8' ;
它和下面的命令类似：
ALTER SESSION SET SQL_TRACE = TRUE
只是前一个命令，可以根据需要指定不同的level，比如此处的level 8，表示跟踪到SQL和等待事件，而不获取绑定变量的数据。也可以通过包DBMS_System来启用，例如：
EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true ); EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
在某些场合下，针对并发请求的跟踪可能会产生不止一个Trace文件，此时第一个Trace文件中会指明第二个Trace文件标识，如：
PARSE #41:c=0,e=565,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=19728618248238 EXEC #41:c=0,e=781,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=19728618249248 ===================== PARSING IN CURSOR #41 len=54 dep=1 uid=44 oct=42 lid=44 tim=19728618250006 hv=4121095646 ad='f7b931f8' ALTER SESSION SET TRACEFILE_IDENTIFIER='XZB_CR8909488' END OF STMT PARSE #41:c=0,e=249,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=19728618250001 *** TRACE DUMP CONTINUES IN FILE ……/udump/test_ora_14443_XZB_CR8909488.trc ***
先来大致看一下Trace文件中都有些什么。
Dump file ……/udump/test_ora_14443.trc Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production ORACLE_HOME = /u08/test/proddb/9.2.0 System name: SunOS Node name: erptest Release: 5.10 Version: Generic_118833-03 Machine: sun4u Instance name: TEST Redo thread mounted by this instance: 1 Oracle process number: 59 Unix process pid: 14443, image: oracle@erptest (TNS V1-V3)
这是文件头，显示了相关环境信息。接着往下翻，关键在具体某条SQL的执行情况上，如下：
===================== PARSING IN CURSOR #75 len=332 dep=0 uid=44 oct=3 lid=44 tim=19728619902280 hv=3581312695 ad='f2b4eb00' select count(*) into :b0 from wip_requirement_operations wro ,mtl_system_items items where ((((((wro.inventory_item_id=items.inventory_item_id and wro.organization_id=items.organization_id) and items.bom_item_type=4) and wro.wip_entity_id=:b1) and wro.wip_supply_type=6) and wro.organization_id=:b2) and wro.inventory_item_id=:b3) END OF STMT PARSE #75:c=10000,e=2221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=19728619902273 EXEC #75:c=0,e=2841,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19728619905363 WAIT #75: nam='db file sequential read' ela= 6392 p1=66 p2=135783 p3=1 …… FETCH #74:c=0,e=1248,p=0,cr=36,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777309437 WAIT #74: nam='SQL*Net message to client' ela= 6 p1=1952673792 p2=1 p3=0 WAIT #74: nam='SQL*Net message from client' ela= 338 p1=1952673792 p2=1 p3=0 EXEC #74:c=0,e=256,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777310344 WAIT #74: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0 WAIT #74: nam='SQL*Net message from client' ela= 205 p1=1952673792 p2=1 p3=0 EXEC #75:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777310795 WAIT #75: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0 FETCH #75:c=30000,e=26138,p=0,cr=2028,cu=0,mis=0,r=1,dep=0,og=4,tim=19728777336976 WAIT #75: nam='SQL*Net message from client' ela= 278 p1=1952673792 p2=1 p3=0 FETCH #74:c=0,e=749,p=0,cr=36,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777338168 WAIT #74: nam='SQL*Net message to client' ela= 2 p1=1952673792 p2=1 p3=0 WAIT #74: nam='SQL*Net message from client' ela= 304 p1=1952673792 p2=1 p3=0 EXEC #74:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777338769 WAIT #74: nam='SQL*Net message to client' ela= 1 p1=1952673792 p2=1 p3=0 WAIT #74: nam='SQL*Net message from client' ela= 227 p1=1952673792 p2=1 p3=0 EXEC #75:c=0,e=272,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19728777339533 WAIT #75: nam='SQL*Net message to client' ela= 7 p1=1952673792 p2=1 p3=0 FETCH #75:c=30000,e=27212,p=0,cr=2028,cu=0,mis=0,r=1,dep=0,og=4,tim=19728777366810 WAIT #75: nam='SQL*Net message from client' ela= 415 p1=1952673792 p2=1 p3=0 ……
这里显示了cursor语句的执行情况，发现编号为#74和#75的语句执行次数非常多，并且间隔执行，每次execute速度很快，fetch消耗时间相对较多，每次读取后立即发送给客户端。（格式的解读参考附注）
由于无法查看源代码，也没有任何技术文档，所以这里，大胆猜测这两个语句在一个LOOP中执行的可能性非常大。Tkprof格式化后，迅速定位最耗时的那个步骤：
select count(*) into :b0
from
wip_requirement_operations wro ,mtl_system_items items where
((((((wro.inventory_item_id=items.inventory_item_id and wro.organization_id=
items.organization_id) and items.bom_item_type=4) and wro.wip_entity_id=:b1)
and wro.wip_supply_type=6) and wro.organization_id=:b2) and
wro.inventory_item_id=:b3)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 8890 0.89 0.92 0 0 0 0
Fetch 8890 223.66 231.45 1951 18028921 0 8890
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17781 224.56 232.37 1951 18028921 0 8890
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
8890 SORT AGGREGATE
0 NESTED LOOPS
8890 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
8890 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (object id 18977994)
0 TABLE ACCESS BY INDEX ROWID WIP_REQUIREMENT_OPERATIONS
61341000 INDEX RANGE SCAN WIP_REQUIREMENT_OPERATIONS_N1 (object id 18977444)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1951 0.08 11.65
SQL*Net message to client 8891 0.00 0.03
SQL*Net message from client 8891 0.01 2.82
latch free 19 0.01 0.12
到这里终于确定了主要问题所在，下一步就是对该语句进行优化。由于无法修改代码，因此只能从执行计划角度考虑。检查该语句的执行计划，仔细检查发现虽然利用了索引WIP_REQUIREMENT_OPERATIONS_N1，看上去很漂亮，但是仔细检查索引字段发现这是个组合索引，若走这个索引或导致大量的逻辑块读。到此，整个诊断过程也顺利结束了。
附Trace格式解读：
PARSING IN CURSOR len Length of SQL statement. dep Recursive depth of the cursor. uid Schema user id of parsing user. oct Oracle command type. lid Privilege user id. ela Elapsed time. 8i: in 1/1000th of a second, 9i: 1/1'000'000th of a second tim Timestamp. hv Hash id. ad SQLTEXT address (see?v$sqlarea?and?v$sqltext). PARSE, EXEC or FETCH #n n = number of cursor c cpu time e elapsed time p physical reads cr consistant reads cu current mode reads mis miss in cache r rows processed dep recursive depth og optimizer goal tim time
详见 http://www.adp-gmbh.ch/ora/misc/trace_file_format.html
有用的链接：
- 《SQL*Net Performance Tuning Using Underlying Network Protocols》
- 《SQL*Net message to client wait isn’t really what it’s thought to be》
Update
2010-05-27 其实是可以看到源代码的，下一篇文章会详细阐述。
我们需要监控什么？
Posted on May 24, 2010 Filed Under Database
今天一个很偶然的时间里，我想看看我们的系统有没有异常，其中某个检查点是通过下面这个SQL进行的：
SQL> SELECT local_tran_id, 2 state, 3 fail_time 4 FROM dba_2pc_pending 5 / LOCAL_TRAN_ID STATE FAIL_TIME ---------------------- ---------------- --------- 31.16.528671 collecting 25-FEB-10
通常情况下，我关注更多地是业务数据异常，很少动手检查此类底层的问题，也不会发现任何异常，但是这次，它来了。当然，在我发现下面这个错误之前，它丝毫不是个问题：
SQL> commit force '31.16.528671';
commit force '31.16.528671'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 31.16.528671
SQL> exec dbms_transaction.purge_lost_db_entry('');
BEGIN dbms_transaction.purge_lost_db_entry(''); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
一种可能的情况是连接的远程数据库或DBLINK此时不可用，由于最近系统修改比较多，且数据不重要，已经没有追查线索的必要了。解决办法参考Metalink [ID 290405.1]：
1.) alter session set "_smu_debug_mode" = 4;
2.) commit; -- so that the call to DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is the first
-- step of the transaction
3.) execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
如果业务数据并不重要，那么解决办法非常简单。这里觉得有点遗憾的是，过去了如此许久才发现问题。基本上，我们的监控处于最原始的手工阶段，这也是为什么我最近评估各类自动化监控系统的主要原因之一。我曾发过一个感叹：
我觉得，如果出一本数据库监控专题的书籍肯定会火，专门介绍如何设置度量和策略，介绍每个监控点。其实这类监控范围，也或许代表了DBA工作职责和经验的体现吧。(via Twitter)
企业的信息化是一个渐进的过程，我们因需求而变化。之前，我们曾经希望有一整套的现成的方案告诉我们如何监控，需要监控什么。但是现在，我基本上可以肯定，每个企业的情况都不相同，寻求一套普适的方案并不值得推荐，因需而定，这才是一个正常的成长过程。
所以我收回之前的Tweet……
Update
1. 将零碎的知识整理成系统性的规范，并进行有序管理，这是当前应该做的事。但是常常独木难支，并且缺少一套有效的知识管理系统来支撑这种渐进的优化。
2. 近日陆续梳理ERP系统的方方面面，发现很多待优化的模块，也发现了很多日常工作中完全可能避免的性能问题。将个人的良好经验形成团队的规范行为，是一个不断积累的过程。尤其的，可以将一些常规项目集成到监控系统中去。
3. 有时候，让功能顾问去解决功能问题可能是个错误的选择，他们更倾向于面对业务问题。纯技术顾问也解决不了，需兼而得之。
……
