《
Oracle
Applications DBA 基础》 - 11 - 12 FNDCPASS ,FND_STATS 和 FNDLOAD
==================================================
11 和 12 节 介绍 Oracle Applications 里几种常用的工具, FNDCPASS, FND_STATS,
FND_LOAD.
FNDCPASS 是用来修改fnd_user和fnd_ oracle _userid上面用户的密码的。
### 例子 1: 修改 apps 用户的password
shutdown apps tier,
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS password
然后 运行 AUTOCONFIG, 再start app tier 。
### 例子 2: 修改 sysadmin 用户的password
不用 shutdown app tier,
FNDCPASS apps/password 0 Y system/manager USER SYSADMIN password
然后 restart apache, forms, reports, 806 listener, and cm
### 例子 3: 修改 gl, pa, etc. 用户的password
不用 shutdown app tier
FNDCPASS apps/password 0 Y system/manager ALLORACLE password
FNDLOAD 可以用来在不同的环境中copy configuration。例如,业务人员可能在
UAT 设好config,可以用这个工具download 下来再传给Production,这样就避免
人工输入可能出现的错误。 下面举几个例子:
- Using FNDLOAD to download Concurrent Program definition
select APPLICATION_ID, CONCURRENT_PROGRAM_NAME
from fnd_concurrent_programs
where CONCURRENT_PROGRAM_NAME='FNDSCURS';
select APPLICATION_SHORT_NAME
from fnd_application where APPLICATION_ID = 0;
### download the definition to file myACTIVEUSER.ldt
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct myactiveuser.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="FNDSCURS"
- Using FNDLOAD to download Lookup type and its value <419136.1>
select LOOKUP_TYPE from FND_LOOKUP_TYPES where lookup_type like 'FND%' order by 1
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct mylookup.ldt FND_LOOKUP_TYPE VIEW_APPSNAME=FND LOOKUP_TYPE="FND_CP_PROGRAM_TYPE"
- Using FNDLOAD to download profile options set on responsibiity level <566865.1>
select RESPONSIBILITY_KEY from FND_RESPONSIBILITY ;
select PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS
where PROFILE_OPTION_NAME like 'FND%' ;
select USER_PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS_VL
where USER_PROFILE_OPTION_NAME like 'FND%';
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct myresp.ldt FND_RESPONSIBILITY RESP_KEY="SYSTEM_ADMINISTRATOR"
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="ICX_SESSION_TIMEOUT" APPLICATION_SHORT_NAME="ICX"
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="FND_DIAGNOSTICS" APPLICATION_SHORT_NAME="FND"
===========================================
12. statistics
=======================================================
Oracle Applications 并不直接用 DBMS_STATS 来 收集数据库的statistics,而是用自己的工具
FND_STATS。
例子如下:
# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics('MRP'); <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table
SQL> begin <- Partitioned table
2 fnd_stats.gather_table_stats(ownname => 'APPLSYS',
3 tabname => 'WF_ITEM_ACTIVITY_STATUSES',
4 granularity => 'PARTITION');
5 end;
6 /
select PARTITION_NAME from all_tab_partitions where upper(TABLE_NAME) ='GL_JE_LINES';
exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_BATCHES',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');
exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_HEADERS',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');
exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,'JAN09','NOBACKUP',TRUE,'PARTITION');
### check global stats
select table_name, last_analyzed from dba_tables where table_name ='GL_JE_LINES'
### gather table stats
exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,NULL,'NOBACKUP',TRUE,'ALL');
### check which columns do histogram
select table_name,column_name,hsize from applsys.fnd_histogram_cols;
参考资料:
<419728.1> How To Gather Statistics On Oracle Applications 11.5.10(and above)
<169935.1> Troubleshooting Oracle Applications Performance Issues
<429002.1> histogram collection
==================================================
11 和 12 节 介绍 Oracle Applications 里几种常用的工具, FNDCPASS, FND_STATS,
FND_LOAD.
FNDCPASS 是用来修改fnd_user和fnd_ oracle _userid上面用户的密码的。
### 例子 1: 修改 apps 用户的password
shutdown apps tier,
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS password
然后 运行 AUTOCONFIG, 再start app tier 。
### 例子 2: 修改 sysadmin 用户的password
不用 shutdown app tier,
FNDCPASS apps/password 0 Y system/manager USER SYSADMIN password
然后 restart apache, forms, reports, 806 listener, and cm
### 例子 3: 修改 gl, pa, etc. 用户的password
不用 shutdown app tier
FNDCPASS apps/password 0 Y system/manager ALLORACLE password
FNDLOAD 可以用来在不同的环境中copy configuration。例如,业务人员可能在
UAT 设好config,可以用这个工具download 下来再传给Production,这样就避免
人工输入可能出现的错误。 下面举几个例子:
- Using FNDLOAD to download Concurrent Program definition
select APPLICATION_ID, CONCURRENT_PROGRAM_NAME
from fnd_concurrent_programs
where CONCURRENT_PROGRAM_NAME='FNDSCURS';
select APPLICATION_SHORT_NAME
from fnd_application where APPLICATION_ID = 0;
### download the definition to file myACTIVEUSER.ldt
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct myactiveuser.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="FNDSCURS"
- Using FNDLOAD to download Lookup type and its value <419136.1>
select LOOKUP_TYPE from FND_LOOKUP_TYPES where lookup_type like 'FND%' order by 1
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct mylookup.ldt FND_LOOKUP_TYPE VIEW_APPSNAME=FND LOOKUP_TYPE="FND_CP_PROGRAM_TYPE"
- Using FNDLOAD to download profile options set on responsibiity level <566865.1>
select RESPONSIBILITY_KEY from FND_RESPONSIBILITY ;
select PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS
where PROFILE_OPTION_NAME like 'FND%' ;
select USER_PROFILE_OPTION_NAME
from FND_PROFILE_OPTIONS_VL
where USER_PROFILE_OPTION_NAME like 'FND%';
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct myresp.ldt FND_RESPONSIBILITY RESP_KEY="SYSTEM_ADMINISTRATOR"
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="ICX_SESSION_TIMEOUT" APPLICATION_SHORT_NAME="ICX"
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct myprofile.ldt PROFILE PROFILE_NAME="FND_DIAGNOSTICS" APPLICATION_SHORT_NAME="FND"
===========================================
12. statistics
=======================================================
Oracle Applications 并不直接用 DBMS_STATS 来 收集数据库的statistics,而是用自己的工具
FND_STATS。
例子如下:
# sqlplus apps/<apps_pwd>
SQL> exec fnd_stats.gather_schema_statistics('MRP'); <- One schema
SQL> exec fnd_stats.gather_schema_statistics('ALL'); <- All schemas
SQL> exec fnd_stats.gather_table_stats('MRP','MRP_FORECAST_DATES'); <- One table
SQL> begin <- Partitioned table
2 fnd_stats.gather_table_stats(ownname => 'APPLSYS',
3 tabname => 'WF_ITEM_ACTIVITY_STATUSES',
4 granularity => 'PARTITION');
5 end;
6 /
select PARTITION_NAME from all_tab_partitions where upper(TABLE_NAME) ='GL_JE_LINES';
exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_BATCHES',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');
exec FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_HEADERS',10,8,NULL,'NOBACKUP',TRUE,'GLOBAL');
exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,'JAN09','NOBACKUP',TRUE,'PARTITION');
### check global stats
select table_name, last_analyzed from dba_tables where table_name ='GL_JE_LINES'
### gather table stats
exec apps.FND_STATS.GATHER_TABLE_STATS('GL','GL_JE_LINES',10,8,NULL,'NOBACKUP',TRUE,'ALL');
### check which columns do histogram
select table_name,column_name,hsize from applsys.fnd_histogram_cols;
参考资料:
<419728.1> How To Gather Statistics On Oracle Applications 11.5.10(and above)
<169935.1> Troubleshooting Oracle Applications Performance Issues
<429002.1> histogram collection