I will be presenting at a few conferences over the next several months:
Collaborate 11- April 12, 2011 for OAUG in Orlando, Florida
Lock Down! Secure your Oracle R12 E-Business Suite Environment in a week.
Event Type: White Paper / Standard Session
Session ID: 4908
Date: Tuesday, April 12
Time: 3:15 pm - 4:15 pm
Room: W304F
Speaker: Benjamin Prusinski
Description: This session will provide the audience with the technical guidance to implement security best practices for Oracle R12 E-Business Suite.
EMC World in May, 2011 in Las Vegas, NV
http://www.emcworld.com/
Oracle eBusiness Suite on RAC Sizing & Configuration on Vblocks Best Practice
This session will provide an overview of the Vblock Powered Solution for Oracle E-Business Suite and RAC. Participants will learn best practices for sizing and layout of Oracle E-Business and RAC database cluster on a Vblock Platform, how to dynamically scale out the Oracle E-Business application tier and RAC database cluster on a Vblock Platform, how to bi-directionally migrate an Oracle RAC database cluster from Physical to Virtual and how to build an inexpensive but effective high availability environment for Oracle E-Business stack on a Vblock Platform.
ODTUG Kaleidoscope in Long Beach, CA
http://www.kscope11.com/
Performance Tuning 101: Top Tips for Oracle 11gR2
Ben Prusinski
Session 17, 06/30/2011, 8:30 AM-9:30 AM
Learn how to use the new features in Oracle 11g Release 2 to optimize performance for data warehousing and transactional OLTP environments. We will show you some quick wins in terms of leveraging tools such as the new Results Cache and little known tools such as oradebug and lots more. Back to Top
I look forward to see you all at one of these events! Stay tuned for more exciting news coming soon.
Cheers,
Ben
Tuesday, March 29, 2011
Thursday, March 3, 2011
Annoying interruption problem solved for Oracle R12 E-Business Suite
I run most of my test environments using virtual servers with VMWare and Oracle Sun Virtualbox. As such the network connections use DHCP to access wireless internet access points. Recently a new issue came up that gave me hours of grief. After making some changes to my Oracle R12 context file, I was unable to get the middle tier application services started successfully. I received the following errors:
******************************************************
ERROR: Timed out (100000): Interrupted Exception
Your are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance
*****************************************************
A quick search on My Oracle Support found similar issue in Note: 564536.1.
The note recommends changing the timeout parameters for oacore parameter
under the $ORA_CONFIG_HOME/10.1.3/opmn.xml file for the middle application tier server as well as changing the context file parameters s_oacoretimeout, s_formstimeout, and s_oafmtimeout for the middle tier application context file and running autoconfig on the middle tier. I tried this and unfortunately, it did not fix my problem.
So, upon further investigation, I found the solution given by My Oracle Support Note: 412980.1 Problem: oafm, forms, and oacore not starting after IP Address Change.
Aha! I thought since I use DHCP and virtual networks, this plays havoc on my Oracle R12 EBS virtual environments. The root cause of my problem and why the middle tier services failed to start is that the OC4J lock files store the old IP address which are incorrect after an IP address is changed by the DHCP and virtual network adapters whenever I travel to use different networks. To solve my problem, I had to perform the following tasks.
1. Take backup of current middle tier files
2. shutdown all current running middle app tier services with the adopmnctl.sh script
under the $INST_TOP/admin/scripts/home directory
3. delete the lock files under the $INST_TOP/ora/10.1.3/j2ee directories
[applmgr@tusebs scripts]$ echo $INST_TOP
/appstier12/CLONE/inst/apps/CLONE_tusebs
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oacore/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oafm/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/forms/persistence/*
4. Run Autoconfig on the apps middle tier:
[applmgr@tusebs scripts]$ ./adautocfg.sh
Enter the APPS user password:
The log file for this session is located at: /appstier12/CLONE/inst/apps/CLONE_tusebs/admin/log/02101314/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /appstier12/CLONE/inst/apps/CLONE_tusebs
Classpath : /appstier12/CLONE/comn/java/lib/appsborg2.zip:/appstier12/CLONE/comn/java/classes
Using Context file : /appstier12/CLONE/inst/apps/CLONE_tusebs/appl/admin/CLONE_tusebs.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IGS_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
5. Start application middle tier services and verify all is working now
[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps
You are running adstrtal.sh version 120.13.12000000.3
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adopmnctl.sh start
script returned:
****************************************************
You are running adopmnctl.sh version 120.4.12000000.3
Starting Oracle Process Manager (OPMN) ...
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adopmnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adapcctl.sh start
script returned:
****************************************************
You are running adapcctl.sh version 120.6.12000000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adapcctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoacorectl.sh start
script returned:
****************************************************
You are running adoacorectl.sh version 120.11.12000000.2
Starting OPMN managed OACORE OC4J instance ...
adoacorectl.sh: exiting with status 0
adoacorectl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoacorectl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adformsctl.sh start
script returned:
****************************************************
You are running adformsctl.sh version 120.12.12000000.3
Starting OPMN managed FORMS OC4J instance ...
adformsctl.sh: exiting with status 0
adformsctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adformsctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoafmctl.sh start
script returned:
****************************************************
You are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 0
adoafmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoafmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adalnctl.sh start
script returned:
****************************************************
adalnctl.sh version 120.3
Checking for FNDFS executable.
Listener APPS_CLONE has already been started.
adalnctl.sh: exiting with status 2
adalnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adalnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adcmctl.sh start
script returned:
****************************************************
You are running adcmctl.sh version 120.14
Starting concurrent manager for CLONE ...
Starting CLONE_0210@CLONE Internal Concurrent Manager
Default printer is noprint
adcmctl.sh: exiting with status 0
adcmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adcmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/jtffmctl.sh start
script returned:
****************************************************
You are running jtffmctl.sh version 120.3
Validating Fulfillment patch level via /appstier12/CLONE/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for CLONE on port 9300 ...
jtffmctl.sh: exiting with status 0
.end std out.
.end err out.
****************************************************
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0
adstrtal.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adstrtal.log for more information ...
[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adapcctl.sh status
You are running adapcctl.sh version 120.6.12000000.2
Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...
Processes in Instance: CLONE_tusebs.tusebs.localdomain
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 25028 | Alive
OC4J | forms | 24953 | Alive
OC4J | oacore | 24866 | Alive
HTTP_Server | HTTP_Server | 24812 | Alive
adapcctl.sh: exiting with status 0
And Voila! Issue has been resolved and no more annoying interrupted exception!
******************************************************
ERROR: Timed out (100000): Interrupted Exception
Your are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance
*****************************************************
A quick search on My Oracle Support found similar issue in Note: 564536.1.
The note recommends changing the timeout parameters for oacore parameter
So, upon further investigation, I found the solution given by My Oracle Support Note: 412980.1 Problem: oafm, forms, and oacore not starting after IP Address Change.
Aha! I thought since I use DHCP and virtual networks, this plays havoc on my Oracle R12 EBS virtual environments. The root cause of my problem and why the middle tier services failed to start is that the OC4J lock files store the old IP address which are incorrect after an IP address is changed by the DHCP and virtual network adapters whenever I travel to use different networks. To solve my problem, I had to perform the following tasks.
1. Take backup of current middle tier files
2. shutdown all current running middle app tier services with the adopmnctl.sh script
under the $INST_TOP/admin/scripts/home directory
3. delete the lock files under the $INST_TOP/ora/10.1.3/j2ee directories
[applmgr@tusebs scripts]$ echo $INST_TOP
/appstier12/CLONE/inst/apps/CLONE_tusebs
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oacore/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oafm/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/forms/persistence/*
4. Run Autoconfig on the apps middle tier:
[applmgr@tusebs scripts]$ ./adautocfg.sh
Enter the APPS user password:
The log file for this session is located at: /appstier12/CLONE/inst/apps/CLONE_tusebs/admin/log/02101314/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /appstier12/CLONE/inst/apps/CLONE_tusebs
Classpath : /appstier12/CLONE/comn/java/lib/appsborg2.zip:/appstier12/CLONE/comn/java/classes
Using Context file : /appstier12/CLONE/inst/apps/CLONE_tusebs/appl/admin/CLONE_tusebs.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IGS_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
5. Start application middle tier services and verify all is working now
[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps
You are running adstrtal.sh version 120.13.12000000.3
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adopmnctl.sh start
script returned:
****************************************************
You are running adopmnctl.sh version 120.4.12000000.3
Starting Oracle Process Manager (OPMN) ...
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adopmnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adapcctl.sh start
script returned:
****************************************************
You are running adapcctl.sh version 120.6.12000000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adapcctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoacorectl.sh start
script returned:
****************************************************
You are running adoacorectl.sh version 120.11.12000000.2
Starting OPMN managed OACORE OC4J instance ...
adoacorectl.sh: exiting with status 0
adoacorectl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoacorectl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adformsctl.sh start
script returned:
****************************************************
You are running adformsctl.sh version 120.12.12000000.3
Starting OPMN managed FORMS OC4J instance ...
adformsctl.sh: exiting with status 0
adformsctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adformsctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoafmctl.sh start
script returned:
****************************************************
You are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance ...
adoafmctl.sh: exiting with status 0
adoafmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoafmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adalnctl.sh start
script returned:
****************************************************
adalnctl.sh version 120.3
Checking for FNDFS executable.
Listener APPS_CLONE has already been started.
adalnctl.sh: exiting with status 2
adalnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adalnctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adcmctl.sh start
script returned:
****************************************************
You are running adcmctl.sh version 120.14
Starting concurrent manager for CLONE ...
Starting CLONE_0210@CLONE Internal Concurrent Manager
Default printer is noprint
adcmctl.sh: exiting with status 0
adcmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adcmctl.txt for more information ...
.end std out.
.end err out.
****************************************************
Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/jtffmctl.sh start
script returned:
****************************************************
You are running jtffmctl.sh version 120.3
Validating Fulfillment patch level via /appstier12/CLONE/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for CLONE on port 9300 ...
jtffmctl.sh: exiting with status 0
.end std out.
.end err out.
****************************************************
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0
adstrtal.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adstrtal.log for more information ...
[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adapcctl.sh status
You are running adapcctl.sh version 120.6.12000000.2
Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...
Processes in Instance: CLONE_tusebs.tusebs.localdomain
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 25028 | Alive
OC4J | forms | 24953 | Alive
OC4J | oacore | 24866 | Alive
HTTP_Server | HTTP_Server | 24812 | Alive
adapcctl.sh: exiting with status 0
And Voila! Issue has been resolved and no more annoying interrupted exception!
Friday, February 25, 2011
Kick off event for San Diego Oracle Applications User Group (SDOAUG) in March 2011
As a new board member for the San Diego Oracle Applications User Group (SDOAUG) in San Diego, we are having a kickoff networking event in March.
On March 10th we are having our Annual Kickoff Networking Event at Karl Strauss Brewery from 4:30pm-7:30pm in Sorrento Valley. If you are not part of the SDOAUG membership list please send me your contact info or go to http://sdoaug.org/ to register yourself as a member to receive all future SDOAUG notifications.
Food, good beer and conversation will be flowing fast and steady so be sure to sign up and attend the premier kickoff event of the year for San Diego! Hope to see you all there!
Cheers,
Ben Prusinski, SDOAUG Events Chair, San Diego Oracle Applications User Group
On March 10th we are having our Annual Kickoff Networking Event at Karl Strauss Brewery from 4:30pm-7:30pm in Sorrento Valley. If you are not part of the SDOAUG membership list please send me your contact info or go to http://sdoaug.org/ to register yourself as a member to receive all future SDOAUG notifications.
Food, good beer and conversation will be flowing fast and steady so be sure to sign up and attend the premier kickoff event of the year for San Diego! Hope to see you all there!
Cheers,
Ben Prusinski, SDOAUG Events Chair, San Diego Oracle Applications User Group
Tuesday, December 28, 2010
How to list all default profiles for Oracle R12 EBS Account
While checking profile options set for the SYSADMIN account, I ran across this useful SQL script from
My Oracle Support Note 201945.1
How to list E-Business Suite Profile Option values for all levels using SQLPlus
Let's run the script logged into Oracle R12 (12.1.1) in our test instance with APPS schema and run the SQL script:
set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl 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 upper(p.profile_option_name) in (
select profile_option_name from fnd_profile_options_tl
where upper(profile_option_name) like upper('%&profile_name%')
and upper(profile_option_name) in (select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name) like upper('%&user_profile_name%')))
and usr.user_id (+) = v.level_value
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 short_name, user_profile_option_name, level_id, level_set;
/
The script will prompt you for the profile name which in our case is SYSADMIN to check for default profile settings.
Enter value for profile_name: SYSADMIN
SHORT_NAME NAME LEVEL_SET CONTEXT VALUE
------------------------------ ---------------------------------------- --------------- ------------------------------ ----------------------------------------
ADI_WHEN_SYSADMIN_OUTPUT_VIEW ADI: Allow Sysadmin to View all Output Site Y
The above script is very useful when reviewing security values and profile settings for Oracle R12 E-Business Suite accounts.
Some additional useful MOS notes and scripts:
Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?
My Oracle Support Note 201945.1
How to list E-Business Suite Profile Option values for all levels using SQLPlus
Let's run the script logged into Oracle R12 (12.1.1) in our test instance with APPS schema and run the SQL script:
set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl 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 upper(p.profile_option_name) in (
select profile_option_name from fnd_profile_options_tl
where upper(profile_option_name) like upper('%&profile_name%')
and upper(profile_option_name) in (select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name) like upper('%&user_profile_name%')))
and usr.user_id (+) = v.level_value
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 short_name, user_profile_option_name, level_id, level_set;
/
The script will prompt you for the profile name which in our case is SYSADMIN to check for default profile settings.
Enter value for profile_name: SYSADMIN
SHORT_NAME NAME LEVEL_SET CONTEXT VALUE
------------------------------ ---------------------------------------- --------------- ------------------------------ ----------------------------------------
ADI_WHEN_SYSADMIN_OUTPUT_VIEW ADI: Allow Sysadmin to View all Output Site Y
The above script is very useful when reviewing security values and profile settings for Oracle R12 E-Business Suite accounts.
Some additional useful MOS notes and scripts:
Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?
Tuesday, December 21, 2010
Changing Weblogic Admin Password
Recently, I completed a new installation for Oracle 11g Weblogic server as part of the Oracle 11g SOA Suite configuration. Unfortunately I forgot the password. Here is how to reset the password for the weblogic admin account with Oracle 11g Weblogic (10.3.3).
Step 1: Set the environment for your Weblogic domains:
In our case, we are running Linux so we change to the Middleware directory set by the MIDDLEWARE_HOME variable
$ cd $MIDDLEWARE_HOME/user_projects/domains/{your_domain}/bin
where your_domain is the domain_name for your Weblogic domain as shown in below example:
$ cd user_projects/domains/fmw_domain/bin
Execute the setDomainEnv.sh script
. ./setDomainEnv.sh
Step 2:Run the copy command before we change the admin password for weblogic
$ java -cp /weblogic1033/oracle/wlserver_10.3/server/lib/weblogic.jar:$CLASSPATH weblogic.security.utils.AdminAccount weblogic mypass1 .
where weblogic is the admin username and mypass1 is the new password
Step 3: Reset the weblogic admin password with the following command:
$ java weblogic.security.utils.AdminAccount weblogic mypass1 .
where weblogic is the admin user account and mypass1 is the new password. Don't forget the period at the end of the command or this will not work.
In the future, if you do not want to set a password for the weblogic admin user, you can modify the boot.properties file as discussed in the following My Oracle Support note:
980292.1
Starting AdminServer Without Username/Password Prompt in WebLogic Server 10.3
Cheers,
Ben
Step 1: Set the environment for your Weblogic domains:
In our case, we are running Linux so we change to the Middleware directory set by the MIDDLEWARE_HOME variable
$ cd $MIDDLEWARE_HOME/user_projects/domains/{your_domain}/bin
where your_domain is the domain_name for your Weblogic domain as shown in below example:
$ cd user_projects/domains/fmw_domain/bin
Execute the setDomainEnv.sh script
. ./setDomainEnv.sh
Step 2:Run the copy command before we change the admin password for weblogic
$ java -cp /weblogic1033/oracle/wlserver_10.3/server/lib/weblogic.jar:$CLASSPATH weblogic.security.utils.AdminAccount weblogic mypass1 .
where weblogic is the admin username and mypass1 is the new password
Step 3: Reset the weblogic admin password with the following command:
$ java weblogic.security.utils.AdminAccount weblogic mypass1 .
where weblogic is the admin user account and mypass1 is the new password. Don't forget the period at the end of the command or this will not work.
In the future, if you do not want to set a password for the weblogic admin user, you can modify the boot.properties file as discussed in the following My Oracle Support note:
980292.1
Starting AdminServer Without Username/Password Prompt in WebLogic Server 10.3
Cheers,
Ben
SQL Developer
In the past as a DBA and developer, I used Quest TOAD on a regular basis. Now with the freely available SQL Developer tool from Oracle, there is the perfect solution for a robust and easy to use development and DBA tool.
Oracle SQL Developer now has the following utilities:
1. Data Modeler- instead of ErWIN you can do it from SQL Developer
2. SQL and PL/SQL Development
3. Third Party Database Migration tool- migrate from MySQL and DB2 or Microsoft SQL
Server
4. DBA tools for monitoring user sessions, killing processes, etc.
5. Search feature for online Oracle Documentation
In my book on database migrations, Migrating to Oracle Expert Secrets for Migrating from SQL Server and MySQL from Rampant Tech Press, I discuss how to use the migration tools available in SQL Developer. Sue Harper of Oracle also has excellent discussions from the Oracle SQL Developer product management team. Let's take a quick peak into some of the new and useful goodies available in the latest version of SQL Developer - the swiss army knife of Oracle development tools!
The main window and menu has list of tools available:

I like the database copy utility when I need to clone a database fast:
Tools->Database Copy

Next up is the cool feature for data modeling. I will write more about this in a future blog post as the new data modeling tool merits a blog post of its own. You can generate new data models for physical or logical database designs or re-engineer a current database environment as well as gather DDL for the entire database or schemas.
The Unload Wizard is interesting tool which I plan to explore and test in the future. This allows you to dump data in various formats such as XML, PDF and flat file formats.

I also like the Diff Wizard tool to check for synchronization issues between database environments. I find this particularly useful after I perform a database refresh to a DEV environment from Production to ensure that the development database is in sync with production. SQL Developer provides a simple option as shown below to perform this task.

We can choose options for taking the diff in the databases

Once the report completes it shows the diff between the two databases:

Finally, with the DBA mindset, I like the monitoring capabilities to view sessions and trace expensive SQL statements. No longer must I use SQL scripts to view SQL statements and explain plans. Its so much easier as shown below.

And last but not least is the way cool search functionality for checking online Oracle documentation and even the Tom Kyte AskTom site:

This makes the latest version of the Oracle SQL Developer tool a one stop center for many development and DBA tasks and best of all, it is FREE!
Cheers,
Ben
Oracle SQL Developer now has the following utilities:
1. Data Modeler- instead of ErWIN you can do it from SQL Developer
2. SQL and PL/SQL Development
3. Third Party Database Migration tool- migrate from MySQL and DB2 or Microsoft SQL
Server
4. DBA tools for monitoring user sessions, killing processes, etc.
5. Search feature for online Oracle Documentation
In my book on database migrations, Migrating to Oracle Expert Secrets for Migrating from SQL Server and MySQL from Rampant Tech Press, I discuss how to use the migration tools available in SQL Developer. Sue Harper of Oracle also has excellent discussions from the Oracle SQL Developer product management team. Let's take a quick peak into some of the new and useful goodies available in the latest version of SQL Developer - the swiss army knife of Oracle development tools!
The main window and menu has list of tools available:
I like the database copy utility when I need to clone a database fast:
Tools->Database Copy
Next up is the cool feature for data modeling. I will write more about this in a future blog post as the new data modeling tool merits a blog post of its own. You can generate new data models for physical or logical database designs or re-engineer a current database environment as well as gather DDL for the entire database or schemas.
The Unload Wizard is interesting tool which I plan to explore and test in the future. This allows you to dump data in various formats such as XML, PDF and flat file formats.
I also like the Diff Wizard tool to check for synchronization issues between database environments. I find this particularly useful after I perform a database refresh to a DEV environment from Production to ensure that the development database is in sync with production. SQL Developer provides a simple option as shown below to perform this task.
We can choose options for taking the diff in the databases
Once the report completes it shows the diff between the two databases:
Finally, with the DBA mindset, I like the monitoring capabilities to view sessions and trace expensive SQL statements. No longer must I use SQL scripts to view SQL statements and explain plans. Its so much easier as shown below.
And last but not least is the way cool search functionality for checking online Oracle documentation and even the Tom Kyte AskTom site:
This makes the latest version of the Oracle SQL Developer tool a one stop center for many development and DBA tasks and best of all, it is FREE!
Cheers,
Ben
Wednesday, December 1, 2010
Oracle R12 E-Business Suite video from OTN LAD Conference
My Oracle R12 E-Business Suite Performance Tuning video from the OTN LAD Conference is now available in Spanish.
http://www.vimeo.com/16914910
Special thanks to fellow Oracle ACE in Peru- Miguel Palacios for the video.
Hope all Oracle users enjoy the video.
Cheers,
Ben
http://www.vimeo.com/16914910
Special thanks to fellow Oracle ACE in Peru- Miguel Palacios for the video.
Hope all Oracle users enjoy the video.
Cheers,
Ben
Subscribe to:
Posts (Atom)