During business events such as month-end close, only a small group of users are allowed to access EBS website. This can not be accomplished by placing it in maintenance mode. But we can disable most responsibilities to restrict and manage users' access without changing users' accounts.
-- disable responsibilities. Assume GL_SuperUser, AR_SuperUser are two special responsibilities
SQL> update FND_RESPONSIBILITY
set end_date = sysdate
where application_id = 30003
and RESPONSIBILITY_ID in (select responsibility_id
from FND_RESPONSIBILITY_TL
where application_id = 30003
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))
;
Commit;
-- After maintenance event, run sql to enable the responsibilities (without bouncing Apps services)
SQL> update FND_RESPONSIBILITY
set end_date = null
where application_id = 30003
and RESPONSIBILITY_ID in (select responsibility_id
from FND_RESPONSIBILITY_TL
where application_id = 30003
and Responsibility_name not in ('GL_SuperUser', 'AR_SuperUser'))
;
Commit;
When a responsibility was created, it would be associated with an Application. Below query will find how many responsibilities were created for each application.
SQ> select a.application_id, b.application_short_name, count(*)
from FND_RESPONSIBILITY a , fnd_application b
where a.application_id = b.application_id
group by a.application_id, application_short_name
order by a.application_id;
application_id application_short_name count(*)
--------------- ---------------------------- ---------
0 FND 24
1 SYSADMIN 3
99 OAM 1
101 SQLGL 27
140 OFA 7
... ... ...
9001 MTH 2
9004 INL 1
30003 PUGL 20 <== custom stuff
30004 PUAR 3
30005 PUFND 4
Additionally, below queries can answer questions:
1) the list of responsibilities for a user
2) the list of responsibilities for an application
SQL> SELECT user_name, frt.RESPONSIBILITY_NAME, furg.end_date, fr.application_id
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
AND fu.user_name = 'EBSuserName'
-- AND fr.application_id = 30003
ORDER BY 1,2;
How many users use an application with each responsibility?
SQL> SELECT fr.application_id, frt.RESPONSIBILITY_NAME, count(*)
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
group by fr.application_id, frt.RESPONSIBILITY_NAME
ORDER BY 1,2;
Friday, January 8, 2016
Monday, January 4, 2016
Files holding EBS R12.1 configuration info
In addition to $CONTEXT_FILE, $APPL_TOP/customXXXX.env, and other standard files, such as appsweb.cfg for Forms, there are other files hold configuration data for the customization of EBS instance. Good understanding where are those files helps instance refresh and troubleshooting.
======================================
1. $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp
This file is used by autoconfig to generate file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf. See Doc ID 1322704.1
NOTES: When creating a custom folder, there is a risk that a patch may later bring a newer version of template file. AutoConfig may fail with conflicts error because the patch only copies the template files to upper (standard) folder. In this case, the template file in custom folder has to be re-created.
For example, a concurrent job completed with Error :
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided
To correct this, a workaround on setting up a value in rwbuilder.conf file as follows:
(a) Create a custom directory under $FND_TOP/admin/template:
$ mkdir $FND_TOP/admin/template/custom
(b) Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to this custom directory.
(c) Change line in file rwbuilder_conf_1012.tmp in custom directory from
<property name="cacheSize" value="0"/>
to
<property name="cacheSize" value="50"/>
(d) Save it and then run autoconfig. rwbuilder.conf will get generated and kept with new content.
2. $FND_TOP/admin/template/ssl_conf_1013.tmp
This file is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf (same as $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/ssl.conf)
For example, use it to enable TLS1.0 (Doc ID 1937646.1).
Also use one line to rotate ssl_engine_log file (daily):
Replace
SSLLog %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log
with
SSLLog "|%s_weboh_oh%/Apache/Apache/bin/rotatelogs %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log %s_ohs_log_rotation_time%"
3. $FND_TOP/admin/template/httpd_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf
Add lines to this file to call a shell script .sh file on OS by a custom URL ebsSiteName.domain.com/CUST/bin for a custom webpage or button on a Form.
Find the true path by "grep -w s_at $CONTEXT_FILE" for s_at and replace it in below lines
<IfModule mod_alias.c>
ScriptAlias /cgi-bin/ "%s_weboh_oh%/Apache/Apache/cgi-bin/"
ScriptAlias /CUST/bin/ "%s_at%/cust/html/bin/" <== to define a directory on file system
#
# "%s_weboh_oh%/Apache/Apache/cgi-bin" should be changed to whatever your ScriptAliased
# CGI directory exists, if you have that configured.
#
<Directory "%s_weboh_oh%/Apache/Apache/cgi-bin">
... ... ...
</Directory>
<Directory "%s_at%/cust/html/bin"> <== add those lines. (replace s_at)
AllowOverride None
Options None
Order allow,deny
Allow from all
</Directory>
</IfModule>
4. $FND_TOP/admin/template/oracle_apache_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf
For example, add one line:
include "%s_weboh_oh%/Apache/modplsql/conf/plsql.conf"
NOTES: Script adtmplreport.sh can find template file for Apache configuration files.
$ cd $AD_TOP/bin
$ adtmplreport.sh contextfile=$CONTEXT_FILE target=$INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf
It will generate a log file showing which template file is for ssl.conf.
Other files, such as
- forms_server_xml_1013.tmp
- orion_web_xml_1013.tmp
==========================================
AUTOCONFIG does not modify below files, but read them
==========================================
5. $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf
Add below lines to this DAD file to hold the password for custom PLSQL webpages.
<Location /pls/EBSQA>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername apps
PlsqlDatabasePassword appsPWD
plsqlDatabaseConnectString dbServerName.domain.com:1562:EBSQA
PlsqlAuthenticationMode Basic
PlsqlDefaultPage fnd_web.ping
</Location>
This file holds custom Forms location.
… … …
#Begin Customizations
MYGL_TOP=$APPL_TOP/mygl
#End Customizations
======================================
Files shall be in $FND_TOP/admin/template/custom
======================================
1. $FND_TOP/admin/template/custom/rwbuilder_conf_1012.tmp
This file is used by autoconfig to generate file $INST_TOP/ora/10.1.2/reports/conf/rwbuilder.conf. See Doc ID 1322704.1
NOTES: When creating a custom folder, there is a risk that a patch may later bring a newer version of template file. AutoConfig may fail with conflicts error because the patch only copies the template files to upper (standard) folder. In this case, the template file in custom folder has to be re-created.
For example, a concurrent job completed with Error :
REP-0069: Internal error
REP-57054: In-process job terminated:Finished successfully but output is voided
To correct this, a workaround on setting up a value in rwbuilder.conf file as follows:
(a) Create a custom directory under $FND_TOP/admin/template:
$ mkdir $FND_TOP/admin/template/custom
(b) Copy the file $FND_TOP/admin/template/rwbuilder_conf_1012.tmp to this custom directory.
(c) Change line in file rwbuilder_conf_1012.tmp in custom directory from
<property name="cacheSize" value="0"/>
to
<property name="cacheSize" value="50"/>
(d) Save it and then run autoconfig. rwbuilder.conf will get generated and kept with new content.
2. $FND_TOP/admin/template/ssl_conf_1013.tmp
This file is used to generate $INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf (same as $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/ssl.conf)
For example, use it to enable TLS1.0 (Doc ID 1937646.1).
Also use one line to rotate ssl_engine_log file (daily):
Replace
SSLLog %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log
with
SSLLog "|%s_weboh_oh%/Apache/Apache/bin/rotatelogs %s_logs_dir%/ora/10.1.3/Apache/ssl_engine_log %s_ohs_log_rotation_time%"
3. $FND_TOP/admin/template/httpd_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf
Add lines to this file to call a shell script .sh file on OS by a custom URL ebsSiteName.domain.com/CUST/bin for a custom webpage or button on a Form.
Find the true path by "grep -w s_at $CONTEXT_FILE" for s_at and replace it in below lines
<IfModule mod_alias.c>
ScriptAlias /cgi-bin/ "%s_weboh_oh%/Apache/Apache/cgi-bin/"
ScriptAlias /CUST/bin/ "%s_at%/cust/html/bin/" <== to define a directory on file system
#
# "%s_weboh_oh%/Apache/Apache/cgi-bin" should be changed to whatever your ScriptAliased
# CGI directory exists, if you have that configured.
#
<Directory "%s_weboh_oh%/Apache/Apache/cgi-bin">
... ... ...
</Directory>
<Directory "%s_at%/cust/html/bin"> <== add those lines. (replace s_at)
AllowOverride None
Options None
Order allow,deny
Allow from all
</Directory>
</IfModule>
4. $FND_TOP/admin/template/oracle_apache_conf_1013.tmp
This file is used to generate file $INST_TOP/ora/10.1.3/Apache/Apache/conf/oracle_apache.conf
For example, add one line:
include "%s_weboh_oh%/Apache/modplsql/conf/plsql.conf"
NOTES: Script adtmplreport.sh can find template file for Apache configuration files.
$ cd $AD_TOP/bin
$ adtmplreport.sh contextfile=$CONTEXT_FILE target=$INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf
It will generate a log file showing which template file is for ssl.conf.
Other files, such as
- forms_server_xml_1013.tmp
- orion_web_xml_1013.tmp
==========================================
AUTOCONFIG does not modify below files, but read them
==========================================
5. $IAS_ORACLE_HOME/Apache/modplsql/conf/dads.conf
Add below lines to this DAD file to hold the password for custom PLSQL webpages.
<Location /pls/EBSQA>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername apps
PlsqlDatabasePassword appsPWD
plsqlDatabaseConnectString dbServerName.domain.com:1562:EBSQA
PlsqlAuthenticationMode Basic
PlsqlDefaultPage fnd_web.ping
</Location>
6. Files in $INST_TOP/certs/Apache folder.
They are ssl certificates if ssl is enabled for the site.
7. $APPL_TOP/admin/adsign.txt and $APPL_TOP/admin/adkeystore.dat
They are used for Java Signing.
8. $FND_TOP/fndenv.env
$CUSTOM_TOP will be defined in this file.
… … …
# Call devenv.env to set up development environment
. ${FND_TOP}/${APPLUSR}/devenv.env
#Begin Customizations
MYGL_TOP=$APPL_TOP/mygl; export MYGL_TOP
#End Customizations
MYGL_TOP=$APPL_TOP/mygl; export MYGL_TOP
#End Customizations
===============================
AUTOCONFIG modify and re-create them
===============================
9. $INST_TOP/ora/10.1.2/forms/server/default.envThis file holds custom Forms location.
… … …
#Begin Customizations
MYGL_TOP=$APPL_TOP/mygl
#End Customizations
Sunday, January 3, 2016
Building an Index ONLINE
Creating an index on a busy table may fail when user's DML is using the table (in 11G database).
SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index. Oracle will create an extra log table to keep track of any data changes without locking the table. There are restrictions on Online Index Building. One of them is that parallel DML is not supported during online index building. Oracle Database returns an error, if you specify ONLINE and then issue parallel DML statements.
Besides, other options, such as PARALLEL and NOLOGGING, will speed up the index creation.
SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name PARALLEL ( DEGREE 4 ) nologging online;
Index created.
With parallel, Oracle may automatically spawn parallel executions for every single SELECT statement. After that, the two options can be turned off by below statement:
SQL> alter index owner.index_IDX1 logging noparallel;
Extra notes: statement to turn on monitoring index usage (usually shall exclude PK from monitoring):
SQL> alter index owner.index_IDX1 monitoring usage ;
SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index. Oracle will create an extra log table to keep track of any data changes without locking the table. There are restrictions on Online Index Building. One of them is that parallel DML is not supported during online index building. Oracle Database returns an error, if you specify ONLINE and then issue parallel DML statements.
Besides, other options, such as PARALLEL and NOLOGGING, will speed up the index creation.
SQL> CREATE INDEX owner.index_IDX1 ON owner.table_name(col1, clm_id, col2) TABLESPACE tablespace_name PARALLEL ( DEGREE 4 ) nologging online;
Index created.
With parallel, Oracle may automatically spawn parallel executions for every single SELECT statement. After that, the two options can be turned off by below statement:
SQL> alter index owner.index_IDX1 logging noparallel;
Extra notes: statement to turn on monitoring index usage (usually shall exclude PK from monitoring):
SQL> alter index owner.index_IDX1 monitoring usage ;
Tuesday, December 22, 2015
Apply EBS R12.1 CPU patches (October 2015)
Apps CPU (Critical Patch Update) patches involve different areas. Normally CPU patches are applied at the same time to EBS apps and two ORACLE_HOMEs. Doc ID 2051000.1 has the instruction for October 2015 CPU patches.
1. Database patches
In my 12.1.0.2 database, database October 2015 PSU and other patches were applied. But most of them are for other purposes, and are not pre-requisites for EBS Oct 2015 CPU.
Patch Conflict patch
21520444 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015)
6880880 Latest OPatch
X 19382851 Already included in 12.1.0.2.5 PSU
X 19393542 Already included in 12.1.0.2.5 PSU
X 19627012 Already included in 12.1.0.2.5 PSU
X 19649152 Already included in 12.1.0.2.5 PSU
20204035 12.1.0.2.0 version. No conflicts
13542050 A mutex related hang with holder around 65534 (0xfffe)
19855835 Upgrade slow when reorganizing large stats history tables
20879889 Open cursor leak from DML on table with a materialized view log
20807398 ORA-600 [kgl-hash-collision] with fix to bug 20465582 installed
21091518 Extend fix of bug 18304693 to Partition Views
2. Patch 21845960 (CPU Oct2015 Fusion Middleware 10.1.3.5)
$ cd $INST_TOP/ora/10.1.3
$ . xxxx.env <== source the env file there
$ echo $ORACLE_HOME <== make sure it points to 10.1.3 IAS_ORACLE_HOME
/path/to/apps/tech_st/10.1.3
$ unzip p21845960_101350_LINUX.zip
$ export OPATCH_PLATFORM_ID=46 <== need it on 64-bit server
$ cd 21845960
$ opatch napply <== take about 15 minutes and roll back previously installed CPU patches
Notes:
(1) After 21845960 was applied, "opatch lsinventory | grep 21845960" will show nothing because it is a CPU patch. But, below line shall show 21845942 (one of the included patches) was installed
$ opatch lsinventory | grep 21845942
(2) Without "export OPATCH_PLATFORM_ID=46" on 64-bit server, opatch exited with error message "NApply failed to apply the following patches "21815758". After that, I tried "opatch napply -skip_duplicate" (I used this option when applied database SPU patch), it says "The input directory could not processed". Then, I went to sub-folder 21815758 and used "opatch apply" to apply it successfully.
3. Patch 21103001
NOTES: This patch replaces CPU Oct2014 Fusion Middleware 10.1.2.3 patch 19434967. See Doc ID 437878.1 CHANGE LOG of Oct 16, 2015.
Start a new OS session or $ . .profile <== run the main env file
$ echo $ORACLE_HOME <== make sure it is the 10.1.2 ORACLE HOME
/path/to/apps/tech_st/10.1.2
(1) p14825718_10123_LINUX.zip (asked by 21103001 README)
Verify conditions are met to apply Patch 14825718 (Step 3.2 notes of Document 437878.1):
- 12881480, 13808590, 14041415, 14262118, 14614795, 14577216, 9593176 were NOT applied.
- 6995251, 7121788 were APPLIED.
$ cd 14825718
$ opatch apply
Note1: README of Bundle Patch is not applicable to E-Business Suite Customers.
Note2: It may rollback some patches. If you get below message, answer "N" to continue:
If you continue, all patches will be rolled back and the new patch (14825718) will be installed.
Do you want to STOP?
Please respond Y|N > N
(2) p6640838_10106_Linux-x86-64.zip (asked by 21103001 README)
This patch 6640838 only added missing components to OUI 10.1.0.6.0, not install a new OUI
Steps with screenshots: Doc ID 1301320.1 How to patch OUI for installing overlay patches on top of Forms Bundle Patch - 9593176
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.pre_6640838
to reserve the higher version of opatch.
$ cd cd/Disk1/install
$ ./runInstaller -ignoreSysPrereqs
$ cd $ORACLE_HOME
$ mv OPatch OPatch_delete
$ mv OPatch.pre_6640838 OPatch
(3) p8551790_10123_LINUX.zip (asked by 21103001 README)
$ cd 8551790
$ opatch apply
(4) p21103001_101232_LINUX.zip
$ cd 21103001
$ opatch apply <== very quick
Note1: Post steps are not needed for EBS. See "Post Steps For Patch 19434967 Files Do Not Exist (Doc ID 1945012.1)"
Note2: If opatch version is older than 1.0.0.0.63, it may give a misleading error message:
The patch directory area must be a number.
ERROR: OPatch failed because of problems in patch area.
4. Run the new adgrants.sql as SYSDBA ( get it from 21507207/admin/adgrants.sql after unzip p21507207_12.1.0_R12_LINUX.zip). Without it, ADFIXUSER.sql will fail.
SQL> @adgrants.sql apps ( <- lower case)
... ... ...
Start of Creating AD_JAR context
PL/SQL procedure successfully completed.
End of Creating AD_JAR context
Creating PL/SQL Package AD_DBMS_METADATA.
Package created.
Package body created.
End of Creating PL/SQL Package AD_DBMS_METADATA.
Creating PL/SQL Package AD_ZD_SYS
Package created.
Package body created.
End of Creating PL/SQL Package AD_ZD_SYS.
PL/SQL procedure successfully completed.
Commit complete.
5. Check pre-requisites on EBS apps
select * from ad_bugs where bug_number in (
'9239089', -- R12.AD.B.Delta.3
'19273341', -- R12.BNE.B.delta.4
'8919491', -- R12.ATG_PF.B.Delta.3
'18936791', -- R12.FWK.B.delta.4 see Don ID 1931412.1
'19393542' -- 12.1.0.2 database patch. It will not show up here :)
);
6. Apply R12.1 Oct2015 CPU 21507207
In my system, I merged 19273341, 18936791, 21507207 together and applied them in one run by adpatch. It may take more than 1 hour on first node (around 18 minutes silence on afprgshrlog.sql !).
1. Database patches
In my 12.1.0.2 database, database October 2015 PSU and other patches were applied. But most of them are for other purposes, and are not pre-requisites for EBS Oct 2015 CPU.
Patch Conflict patch
21520444 Combo of 12.1.0.2.5 OJVM PSU and 12.1.0.2.5 DB PSU (Oct 2015)
6880880 Latest OPatch
X 19382851 Already included in 12.1.0.2.5 PSU
X 19393542 Already included in 12.1.0.2.5 PSU
X 19627012 Already included in 12.1.0.2.5 PSU
X 19649152 Already included in 12.1.0.2.5 PSU
20204035 12.1.0.2.0 version. No conflicts
13542050 A mutex related hang with holder around 65534 (0xfffe)
19855835 Upgrade slow when reorganizing large stats history tables
20879889 Open cursor leak from DML on table with a materialized view log
20807398 ORA-600 [kgl-hash-collision] with fix to bug 20465582 installed
21091518 Extend fix of bug 18304693 to Partition Views
2. Patch 21845960 (CPU Oct2015 Fusion Middleware 10.1.3.5)
$ cd $INST_TOP/ora/10.1.3
$ . xxxx.env <== source the env file there
$ echo $ORACLE_HOME <== make sure it points to 10.1.3 IAS_ORACLE_HOME
/path/to/apps/tech_st/10.1.3
$ unzip p21845960_101350_LINUX.zip
$ export OPATCH_PLATFORM_ID=46 <== need it on 64-bit server
$ cd 21845960
$ opatch napply <== take about 15 minutes and roll back previously installed CPU patches
Notes:
(1) After 21845960 was applied, "opatch lsinventory | grep 21845960" will show nothing because it is a CPU patch. But, below line shall show 21845942 (one of the included patches) was installed
$ opatch lsinventory | grep 21845942
(2) Without "export OPATCH_PLATFORM_ID=46" on 64-bit server, opatch exited with error message "NApply failed to apply the following patches "21815758". After that, I tried "opatch napply -skip_duplicate" (I used this option when applied database SPU patch), it says "The input directory could not processed". Then, I went to sub-folder 21815758 and used "opatch apply" to apply it successfully.
3. Patch 21103001
NOTES: This patch replaces CPU Oct2014 Fusion Middleware 10.1.2.3 patch 19434967. See Doc ID 437878.1 CHANGE LOG of Oct 16, 2015.
Start a new OS session or $ . .profile <== run the main env file
$ echo $ORACLE_HOME <== make sure it is the 10.1.2 ORACLE HOME
/path/to/apps/tech_st/10.1.2
(1) p14825718_10123_LINUX.zip (asked by 21103001 README)
Verify conditions are met to apply Patch 14825718 (Step 3.2 notes of Document 437878.1):
- 12881480, 13808590, 14041415, 14262118, 14614795, 14577216, 9593176 were NOT applied.
- 6995251, 7121788 were APPLIED.
$ cd 14825718
$ opatch apply
Note1: README of Bundle Patch is not applicable to E-Business Suite Customers.
Note2: It may rollback some patches. If you get below message, answer "N" to continue:
If you continue, all patches will be rolled back and the new patch (14825718) will be installed.
Do you want to STOP?
Please respond Y|N > N
(2) p6640838_10106_Linux-x86-64.zip (asked by 21103001 README)
This patch 6640838 only added missing components to OUI 10.1.0.6.0, not install a new OUI
Steps with screenshots: Doc ID 1301320.1 How to patch OUI for installing overlay patches on top of Forms Bundle Patch - 9593176
$ mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.pre_6640838
to reserve the higher version of opatch.
$ cd cd/Disk1/install
$ ./runInstaller -ignoreSysPrereqs
$ cd $ORACLE_HOME
$ mv OPatch OPatch_delete
$ mv OPatch.pre_6640838 OPatch
(3) p8551790_10123_LINUX.zip (asked by 21103001 README)
$ cd 8551790
$ opatch apply
(4) p21103001_101232_LINUX.zip
$ cd 21103001
$ opatch apply <== very quick
Note1: Post steps are not needed for EBS. See "Post Steps For Patch 19434967 Files Do Not Exist (Doc ID 1945012.1)"
Note2: If opatch version is older than 1.0.0.0.63, it may give a misleading error message:
The patch directory area must be a number.
ERROR: OPatch failed because of problems in patch area.
4. Run the new adgrants.sql as SYSDBA ( get it from 21507207/admin/adgrants.sql after unzip p21507207_12.1.0_R12_LINUX.zip). Without it, ADFIXUSER.sql will fail.
SQL> @adgrants.sql apps ( <- lower case)
... ... ...
Start of Creating AD_JAR context
PL/SQL procedure successfully completed.
End of Creating AD_JAR context
Creating PL/SQL Package AD_DBMS_METADATA.
Package created.
Package body created.
End of Creating PL/SQL Package AD_DBMS_METADATA.
Creating PL/SQL Package AD_ZD_SYS
Package created.
Package body created.
End of Creating PL/SQL Package AD_ZD_SYS.
PL/SQL procedure successfully completed.
Commit complete.
5. Check pre-requisites on EBS apps
select * from ad_bugs where bug_number in (
'9239089', -- R12.AD.B.Delta.3
'19273341', -- R12.BNE.B.delta.4
'8919491', -- R12.ATG_PF.B.Delta.3
'18936791', -- R12.FWK.B.delta.4 see Don ID 1931412.1
'19393542' -- 12.1.0.2 database patch. It will not show up here :)
);
6. Apply R12.1 Oct2015 CPU 21507207
In my system, I merged 19273341, 18936791, 21507207 together and applied them in one run by adpatch. It may take more than 1 hour on first node (around 18 minutes silence on afprgshrlog.sql !).
Saturday, December 19, 2015
Upgrade R12.1.1 to R12.1.3
I followed Doc ID 1080973.1 in below steps to apply patch 9239090 for upgrading R12.1.1 to R12.1.3 on an Oracle 11.2.0.3.0 database.
Current system status:
SQL> select * from ad_bugs where bug_number in ('9239089', '9239090', '9239095', '9817770', '9966055');
only 9239089 was applied.
$ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
Server version: Oracle-Application-Server-10g/10.1.3.5.0 Oracle-HTTP-Server
Server built: Jul 21 2009 11:12:22
$ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)
$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35
sun_plugin_version=1.6.0_06
Pre steps in database (to avoid headaches during the upgrading):
1. Setup two database parameters:
_disable_fast_validate=TRUE <-- this parameter shall be removed after upgrade completed.
pga_aggregate_target=2G
2. Follow Doc ID 1112223.1 to grant XDBADMIN the right role to avoid ORA-31000 error with csrrsreg.sql.
SQL> show user
apps
SQL> select * from USER_ROLE_PRIVS;
USERNAME GRANTED_ROLE ADM DEF OS_
----------------------------------- ------------------------------------------------- ------- ------ ---
APPS AQ_ADMINISTRATOR_ROLE YES YES NO
APPS CTXAPP NO YES NO
APPS JAVASYSPRIV NO YES NO
APPS RESOURCE NO YES NO
APPS XDBADMIN NO YES NO
3. Follow Doc ID 1357760.1 & 1081227.1 to avoid autoconfig error from jtfictx.sh
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-10758: index owner does not have the privilege to use file or URL datastore
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 96
Fixes:
SQL> create role apps_ctx_role;
SQL> grant apps_ctx_role to apps;
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'apps_ctx_role');
SQL> grant apps_ctx_role to JTF; -- Grant it to the index owners
SQL> grant apps_ctx_role to AMV;
SQL> grant apps_ctx_role to APPLSYS;
SQL> grant apps_ctx_role to HR;
SQL> grant apps_ctx_role to IBC;
SQL> grant apps_ctx_role to OKC;
4. Follow Doc ID 1551854.1 & 847687.1 to avoid error from EGOSILDU.sql
DECLARE
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at line 408
SQL> SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc';
ATTR_GROUP_ID
-------------------------
2
SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected
SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected
Fixes: Open a SR with Oracle Support to get a datafix script from them. And run it as sysdba:
SQL> @Partition_Fix_Script_Ver2.sql
Notes: After I used $AD_TOP/sql/adutconf.sql to confirm module EGO is inactive, I let the failed worker to quit and then skipped EGOSILDU.sql in my Dev instance. Seems ignoring the error worked fine.
5. Make sure account OLAPSYS is not locked.
SQL> select username, account_status from dba_users like '%OLAP%';
If locked, it will hit errors in Doc ID 1054417.1:
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 984
ORA-06512: at "SYS.DBMS_AW", line 937
ORA-33262: Analytic workspace ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 908
ORA-06512: at "APPS.MSD_AW_LOADER", line 10
Pre steps on Apps side:
1. If any schema owner password (such as HR) was changed by Sql*plus abnormally (not by FNDCPASS), the upgrade will fail because adpatch would not have the right password for that module. I had to tell all workers to quit and then "kill" adpatch process on OS level because adpatch kept remembering the bad password. After I used FNDCPASS to change the wrong password, I re-ran adpatch from the beginning and it worked.
2. Apply patch 10257394 (Doc ID 1286779.1) to avoid error with IEX_SCORE_COMP_DET_U1
(or, copy iexscore.lct from patch .zip file and manually replace the old one)
Apply the patch:
$ unzip 6 .zip files (for patch 9239090) to same folder /path/to/R1213CD
($ unzip xxxX.zip -d /path/to/R1213CD)
$ adadmin to enable maintenance mode
$ cd /path/to/R1213CD/9239090 <== make folder 9239090 10GB free and rw permissions
$ adpatch <== it takes 3 or more hours
Then, optional step: start apps to do a quick check out.
Post steps:
$ cd 9239095
$ adpatch
$ cd 9817770
$ adpatch
$ cd 9966055
$ adpatch
$ cd $AD_TOP/bin
$ perl admkappsutil.pl ==> then copy appsutil.zip file to DB server.
$ adconfig.sh on DB tier
$ adautocfg.sh on each apps tiers
disable maintenance mode
start apps services
remove _disable_fast_validate=TRUE
NOTES: In a R12.1.1 instance, a lot of database sessions kept in INACTIVE for a long time (15+ days) and never got closed. So, the number of db sessions increased daily and exceeded easily database parameter PROCESSES (3000). We had to recycle Apps services each two weeks. Modules AR and GL are used most in this instance and most idle sessions are from "e::bes:oracle.apps.icx.security.session.created" by JDBC Thin Client. We tried and did not find a fix to this problem. But, after the instance was upgraded to R12.1.3 and its database was upgraded to 12c, the idle session problem went away surprisingly.
Current system status:
SQL> select * from ad_bugs where bug_number in ('9239089', '9239090', '9239095', '9817770', '9966055');
only 9239089 was applied.
$ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v
Server version: Oracle-Application-Server-10g/10.1.3.5.0 Oracle-HTTP-Server
Server built: Jul 21 2009 11:12:22
$ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)
$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35
sun_plugin_version=1.6.0_06
Pre steps in database (to avoid headaches during the upgrading):
1. Setup two database parameters:
_disable_fast_validate=TRUE <-- this parameter shall be removed after upgrade completed.
pga_aggregate_target=2G
2. Follow Doc ID 1112223.1 to grant XDBADMIN the right role to avoid ORA-31000 error with csrrsreg.sql.
SQL> show user
apps
SQL> select * from USER_ROLE_PRIVS;
USERNAME GRANTED_ROLE ADM DEF OS_
----------------------------------- ------------------------------------------------- ------- ------ ---
APPS AQ_ADMINISTRATOR_ROLE YES YES NO
APPS CTXAPP NO YES NO
APPS JAVASYSPRIV NO YES NO
APPS RESOURCE NO YES NO
APPS XDBADMIN NO YES NO
3. Follow Doc ID 1357760.1 & 1081227.1 to avoid autoconfig error from jtfictx.sh
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-10758: index owner does not have the privilege to use file or URL datastore
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at line 96
Fixes:
SQL> create role apps_ctx_role;
SQL> grant apps_ctx_role to apps;
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'apps_ctx_role');
SQL> grant apps_ctx_role to JTF; -- Grant it to the index owners
SQL> grant apps_ctx_role to AMV;
SQL> grant apps_ctx_role to APPLSYS;
SQL> grant apps_ctx_role to HR;
SQL> grant apps_ctx_role to IBC;
SQL> grant apps_ctx_role to OKC;
4. Follow Doc ID 1551854.1 & 847687.1 to avoid error from EGOSILDU.sql
DECLARE
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at line 408
SQL> SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc';
ATTR_GROUP_ID
-------------------------
2
SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected
SQL> SELECT 1 FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE ATTR_GROUP_ID = 2
AND ROWNUM = 1;
no rows selected
Fixes: Open a SR with Oracle Support to get a datafix script from them. And run it as sysdba:
SQL> @Partition_Fix_Script_Ver2.sql
Notes: After I used $AD_TOP/sql/adutconf.sql to confirm module EGO is inactive, I let the failed worker to quit and then skipped EGOSILDU.sql in my Dev instance. Seems ignoring the error worked fine.
5. Make sure account OLAPSYS is not locked.
SQL> select username, account_status from dba_users like '%OLAP%';
If locked, it will hit errors in Doc ID 1054417.1:
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 984
ORA-06512: at "SYS.DBMS_AW", line 937
ORA-33262: Analytic workspace ODPCODE does not exist.
ORA-06512: at "SYS.DBMS_AW", line 908
ORA-06512: at "APPS.MSD_AW_LOADER", line 10
Pre steps on Apps side:
1. If any schema owner password (such as HR) was changed by Sql*plus abnormally (not by FNDCPASS), the upgrade will fail because adpatch would not have the right password for that module. I had to tell all workers to quit and then "kill" adpatch process on OS level because adpatch kept remembering the bad password. After I used FNDCPASS to change the wrong password, I re-ran adpatch from the beginning and it worked.
2. Apply patch 10257394 (Doc ID 1286779.1) to avoid error with IEX_SCORE_COMP_DET_U1
(or, copy iexscore.lct from patch .zip file and manually replace the old one)
Apply the patch:
$ unzip 6 .zip files (for patch 9239090) to same folder /path/to/R1213CD
($ unzip xxxX.zip -d /path/to/R1213CD)
$ adadmin to enable maintenance mode
$ cd /path/to/R1213CD/9239090 <== make folder 9239090 10GB free and rw permissions
$ adpatch <== it takes 3 or more hours
Then, optional step: start apps to do a quick check out.
Post steps:
$ cd 9239095
$ adpatch
$ cd 9817770
$ adpatch
$ cd 9966055
$ adpatch
$ cd $AD_TOP/bin
$ perl admkappsutil.pl ==> then copy appsutil.zip file to DB server.
$ adconfig.sh on DB tier
$ adautocfg.sh on each apps tiers
disable maintenance mode
start apps services
remove _disable_fast_validate=TRUE
NOTES: In a R12.1.1 instance, a lot of database sessions kept in INACTIVE for a long time (15+ days) and never got closed. So, the number of db sessions increased daily and exceeded easily database parameter PROCESSES (3000). We had to recycle Apps services each two weeks. Modules AR and GL are used most in this instance and most idle sessions are from "e::bes:oracle.apps.icx.security.session.created" by JDBC Thin Client. We tried and did not find a fix to this problem. But, after the instance was upgraded to R12.1.3 and its database was upgraded to 12c, the idle session problem went away surprisingly.
Subscribe to:
Comments (Atom)