R12
Oracle Apps R12 : How to find the list of responsibilities assigned to user(s)
SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = ‘US’
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;
— ADD CONDITION ON START_DATE FOR RESPONSIBILITIES ADDED AFTER A SPECIFIC DATE LIKE “WHERE FURGA.START_DATE > TO_DATE(’22-JUL-2008′)”
cheers,
OraclePitStop
Interesting enhancements in 12.0.6 – R12.ATG_PF.A.DELTA.6
Oracle Applications Technology Stack Release Update Pack 6 (R12.ATG_PF.A.DELTA.6)
Autoconfig has two new options
1. Parallel Mode
2. Profile Mode
Parallel Mode
This mode enables autoconfig to be executed parallel across all mid-tiers. Oh man, i love this feature … it really helps when you have multiple mid-tiers.
Just add -parallel to your normal adconfig command
Profile Mode
This option provides a drill down HTML report of the autoconfig run.
Just add -profile to your normal adconfig command
For more information on above options refer to Metalink Note 742110.1
cheers,
OraclePitStop
Unable to generate forwarding URL. Exception: oracle.apps.fnd.cache.CacheException
While trying to access the login page below error is reported.
“Unable to generate forwarding URL. Exception: oracle.apps.fnd.cache.CacheException”
This simple message doesnot give much information. Digging into logs yielded below detailed error message.
$INST_TOP/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
html: Servlet error
oracle.apps.fnd.cache.CacheException
at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:228)
at oracle.apps.fnd.profiles.Profiles.getProfileOption(Profiles.java:1485)
at oracle.apps.fnd.profiles.Profiles.getProfile(Profiles.java:354)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfileFromDB(ExtendedProfileStore.java:211)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getSpecificProfile(ExtendedProfileStore.java:171)
at oracle.apps.fnd.profiles.ExtendedProfileStore.getProfile(ExtendedProfileStore.java:148)
at oracle.apps.fnd.common.logging.DebugEventManager.configureUsingDatabaseValues(DebugEventManager.java:1259)
at oracle.apps.fnd.common.logging.DebugEventManager.configureLogging(DebugEventManager.java:1114)
at oracle.apps.fnd.common.logging.DebugEventManager.internalReinit(DebugEventManager.java:1083)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:1050)
at oracle.apps.fnd.common.logging.DebugEventManager.reInitialize(DebugEventManager.java:1037)
at oracle.apps.fnd.common.AppsLog.reInitialize(AppsLog.java:595)
at oracle.apps.fnd.common.AppsContext.initLog(AppsContext.java:941)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:926)
at oracle.apps.fnd.common.AppsContext.initializeContext(AppsContext.java:891)
at oracle.apps.fnd.common.AppsContext.<init>(AppsContext.java:751)
at oracle.apps.fnd.common.WebAppsContext.<init>(WebAppsContext.java:1014)
at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:525)
at oracle.apps.fnd.sso.AppsLoginRedirect.AppsSetting(AppsLoginRedirect.java:120)
at oracle.apps.fnd.sso.AppsLoginRedirect.init(AppsLoginRedirect.java:170)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.loadServlet(HttpApplication.java:2
232)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.findServlet(HttpApplication.java:4
622)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.findServlet(HttpApplication.java:4
546)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpApplication.getRequestDispatcher(HttpApplicati
on.java:2822)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpRequestHandler.doProcessRequest(HttpRequestHan
dler.java:740)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.HttpRequestHandler.processRequest(HttpRequestHandl
er.java:451)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:299)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].server.http.AJPRequestHandler.run(AJPRequestHandler.java:187)
at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
at com.evermind[Oracle Containers for J2EE 10g (10.1.3.0.0) ].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableR
esourcePooledExecutor.java:303)
at java.lang.Thread.run(Thread.java:595)
Caused by: oracle.apps.jtf.base.resources.FrameworkException: Error in IAS Cache: java.lang.NullPointerException: null Connection
at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:712)
at oracle.apps.jtf.cache.CacheManager.getInternal(CacheManager.java:4802)
at oracle.apps.jtf.cache.CacheManager.get(CacheManager.java:4624)
at oracle.apps.fnd.cache.AppsCache.get(AppsCache.java:216)
… 30 more
Caused by: oracle.apps.jtf.base.resources.FrameworkException:
An exception occurred in the method CacheAccess.get
null
The base exception is:
null Connection
at oracle.apps.jtf.base.resources.FrameworkException.convertException(FrameworkException.java:607)
at oracle.apps.jtf.base.resources.FrameworkException.addException(FrameworkException.java:585)
at oracle.apps.jtf.base.resources.FrameworkException.<init>(FrameworkException.java:66)
at oracle.apps.jtf.base.resources.FrameworkException.<init>(FrameworkException.java:88)
at oracle.apps.jtf.base.resources.FrameworkException.<init>(FrameworkException.java:202)
at oracle.apps.jtf.base.resources.FrameworkException.<init>(FrameworkException.java:218)
at oracle.apps.jtf.base.resources.FrameworkException.<init>(FrameworkException.java:249)
… 34 more
There could be many root causes for this error. If you look closely into the errorstack, it complains about null connection.
Caused by:
oracle.apps.jtf.base.resources.FrameworkException:
Error in IAS Cache:
java.lang.NullPointerException:
null Connection
Every access to the login page (http://machine.domain.com:port/OA_HTML/AppsLogin) (in R12) establishes a connection to the database as GUEST user using the DBC file. (In 11i, the url page is a static html which doesnt establish a connection)
If the mid-tier gets a connection, it displays the login page else it throws “oracle.apps.fnd.cache.CacheException”.
Now lets look at some possible reasons for not getting a connection.
1. Database is down
2. Database is up but the listener is down.
3. Archive Location is full, which prevents new connections till freed-up.
4. The instance was recently cloned and the GUEST user password is not in sync.
When the instance is cloned from source, there is a remote chance that the guest user password is not in sync. By sync, i mean the profile option value and the dbc file value for guest user password.
Steps to verify the values (perform below steps in the mid-tier)
4.1 DBC File value
$ grep -i GUEST_USER_PWD $FND_SECURE/[SID].dbc
GUEST_USER_PWD=GUEST/GUEST
4.2 Profile Option Value
sqlplus apps/[apps_pwd]
select fnd_profile.value(‘GUEST_USER_PWD’) from dual;
FND_PROFILE.VALUE(‘GUEST_USER_PWD’)
——————————————————————————–
GUEST/GUEST
Values from 4.1 and 4.2 should be in sync and in our case it is. Now lets connect to the database using the guest password.
4.3 GUEST User Connectivity Check
sqlplus apps/[apps_pwd]
select FND_WEB_SEC.VALIDATE_LOGIN(‘GUEST’,’GUEST’) from dual;
FND_WEB_SEC.VALIDATE_LOGIN(‘GUEST’,’GUEST’)
——————————————————————————–
Y
5. Ok, i didnt do a clone and the GUEST user password is in sync. What next.
The next possible reason, the database is not allowing any new sessions. May be the maximum number of sessions defined at the database level had already reached.
If you had followed all the steps sequentially as defined above, you would have realised that database does accept new connections, since you were able to verify the GUEST user password profile option value. If SESSIONS parameter value had been reached you would have got the error ORA-00018: maximum number of sessions exceeded.
6. Now what, my database is accepting new sessions.
Next option is to look for stale database connections.
ps -fu [oracle_user] | grep -i ‘LOCAL=NO’
This command will give you a list of stale connections. Use below command to kill them and to free up resources.
CAUTION: BELOW COMMAND WILL KILL THE CONNECTIONS
ps -fu [oracle_user] | grep -i ‘LOCAL=NO’ | grep -v grep | awk ‘{print “kill -9 “$2}’ | ksh
Hope one of the above mentioned steps resolves your issue when you encounter “Unable to generate forwarding URL. Exception: oracle.apps.fnd.cache.CacheException”
cheers,
OraclePitStop.
How to enable trace with BIND variables and WAITs for a concurrent program?
In this link, we had mentioned about tracing a concurrent program, but the drawback of this method is it doesnt yield information about the BIND variable values. So in this post we will talk about it.
Scenario
X submitted a concurrent request and its running for a long time and never completes. X approached you to see what is the problem with the concurrent request.
Steps
1. Inform X to logout from his ERP session.
2. Login as sysadmin user
3. System Administrator -> Profile (System)
4. Values: User = X, Profile = Initialization SQL Statement – Custom (you can also mention Initialization%Custom)
5. Under user field enter the following
begin fnd_ctl.fnd_sess_ctl(”,”,’TRUE’,’TRUE’,’LOG’,’ALTER SESSION SET EVENTS=”10046 TRACE NAME CONTEXT FOREVER, LEVEL 12” TRACEFILE_IDENTIFIER=”[ANY_IDENTIFIER]”‘);end;
[ANY_IDENTIFIER] = Any value to identify the trace file.
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables
All above levels will include the sql statements being executed and not only the binds and waits.
6. If tracing had been enabled at the concurrent program level as mentioned in this link, please disable it by removing the check box. [IMPORTANT]. If you have trace enabled for the concurrent program, then it will take precedence for the profile option.
7. Kill / cancel the long running concurrent request (if its still running)
8. Request X to login to ERP and instruct X to submit the concurrent request again and logout. Nothing else. This will ensure we capture the trace information specific to the concurrent request and not for other actions.
At this stage, you will find a trace file generated at $ORACLE_HOME/admin/SID_Hostname/udump with [ANY_IDENTIFIER] as part of it.
Inside the trace file you will be able to see information about the bind variable values being passed to and used by the concurrent program.
Eg:
Bind#0
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=9fffffffbf2e88d8 bln=07 avl=07 flg=09
value=”[BIND VARIABLE VALUE]”
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=9fffffffbf2e88f8 bln=07 avl=07 flg=09
value=”[BIND VARIABLE VALUE]”
Note: Make sure you remove the profile option value for X after the job is done, else every action of X will generate a trace file in udump directory.
Happy Trouble shooting.
cheers,
OraclePitStop
How to clear cache for a specific component in R12?
As we all know the normal tendency for clearing cache in Oracle Apps is to delete the $COMMON_TOP/_pages directory. In 11i, after deleting we dont have to recompile the jsps but in R12 we have to recompile the pages since s_jsp_main_mode is set to justrun by default which is in turn updated in orion-web.xml. Click here for more info.
Ok coming to this post, how can i clear cache for a specific component of Oracle Apps R12 viz., iprocurement, istore etc., Is this even possible ? Yes it is and this option comes handy when somebody modifies a jsp page which is part of say istore, then it makes sense to clear cache for istore and not for the whole server.
Clearing cache for specific Component/Application in R12
Login as sysadmin
Select “Functional Administrator” responsibility
Go to Core Services ==> Caching Framework ==> Tuning
Query the application (for example iStore) or by Name or code.
Select the component and clear the cache.
Note: For some specific configuration changes, we need to bounce Apache also.
Clearing Global Cache in R12
a) Go to Functional Administrator Responsibility
b) Go to Core Services ==> Caching Framework
c) Go to Global configuration and clear all the cache from there.(Only If you want to clear all the cache)
Hope this helps…..
Thanks,
Sam.
About Sam: Sam is a guest author for this blog and had performed various installations & upgrades. He is well versed with tech stack components and applications maintenance. He can be contacted at gksam4u@gmail.com
Read Full Post | Make a Comment ( 1 so far )R12 on 11g Anyone yet?
All,
We did a fresh R12.0.4 installation and applied Rup5 on Fin and Hrms. Now, we are upgrading this db to 11g. Later, we have plans to put the datafiles to ASM and also RAC the instance. So, we sure will have many posts on this. (Already i have few related to 11g crs and asm but planning to arrange the posts in some order)
OK. Now for this !!
Has anyone upgraded a fresh install R12.0.4 database from 10.2.0.3 to 11.1.0.6 ?
We are performing this upgrade on solaris 64 bit using DBUA.
DBUA throws the following error performing Post Upgrade:
+++++++++++++++++
ORA-00904: “E”.”OBJNUM”: Invalid identifier
ORA-06512: at line 31
+++++++++++++++++
I checked the dbua log files and the error is because there is no column called ‘objnum’ in the sys.enabled$indexes table which is aliased as “E”. Looks like a bug. I have raised an SR on this. Lets see what they say !!.
I looked up in metalink and google. No help !! Looks like we are the first to perform this upgrade?
Thanks,
OraclePitStop team
Read Full Post | Make a Comment ( 1 so far )OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred
While trying to apply a patch or listing inventory, using opatch utility (be it on the Database node or for the Applications Tech Stack) below error is encountered:
—————————————————————————
LsInventory: OPatch Exception while accessing O2O
OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred:
OPatch Exception:
OUI found no such ORACLE_HOME set in the environment
Can not get details for given Oracle Home
An exception occurs
null
There is no Interim Patch
OPATCH_JAVA_ERROR : An exception of type “OPatchException” has occurred:
Can not get a list of inventory on this home.
ERROR: OPatch failed because of Inventory problem.
—————————————————————————
Scenario
Imagine you are trying to apply a patch to 10.1.3 Tech Stack Oracle Home on R12.
What did i do wrong ?
1. Did i source the corrent environment file ?
Yes, i did, my echo $ORACLE_HOME command points to 10.1.3 home instead of the default 10.1.2 home. hmmm ok
2. Am i using the correct opatch utility?
yes, which opatch is pointing to $ORACLE_HOME(10.1.3)/Opatch/opatch
3. Is the inventory location accessible ?
Yes, oraInst.loc under Oracle Home (10.1.3) is pointing to a valid directory.
$ cat oraInst.loc
inventory_loc=/var/opt/oracle/oraInventory
4. Does the inventory has information about the Oracle Home you are trying to patch?
How do i check that ….
$ cd /var/opt/oracle/oraInventory
$ ls -latr
total 112
drwxrwxr-x 3 username dba ..
drwxrwxr-x 2 username dba oui
-rwxrwxr-x 1 username dba sessionContext.ser
drwxrwxr-x 2 username dba ContentsXML
-rwxrwxr-x 1 username dba oraInstaller.properties
drwxrwxr-x 2 username dba Contents
drwxrwx— 3 username dba logs
-rwxrwxr-x 1 username dba install.platform
drwxrwxr-x 6 username dba .
$ cd ContentsXML
$ vi inventory.xml
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>10.1.0.5.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_2″ LOC=”[masked_oracle_home]/tech_st/10.1.2″ TYPE=”O” IDX=”1″/>
</HOME_LIST>
</INVENTORY>
[[[[DO YOU SEE AN ENTRY FOR THIS ORACLE HOME (10.1.3)]]]]
ooops … i dont …. THIS IS THE ISSUE
So what should i do now?
Pretty simple, add an entry for this Oracle Home just above or below HOME_LIST tag.
<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_3″ LOC=”[masked_oracle_home]/tech_st/10.1.3″ TYPE=”O” IDX=”2″/>
After making changes,
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>10.1.0.5.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”SID_TOOLS__masked_oracle_home_tech_st_10_1_2″ LOC=”[masked_oracle_home]/tech_st/10.1.2″ TYPE=”O” IDX=”1″/>
<HOME NAME=”SID_WEBOH__masked_oracle_home_tech_st_10_1_3″ LOC=”[masked_oracle_home]/tech_st/10.1.3″ TYPE=”O” IDX=”2″/>
</HOME_LIST>
</INVENTORY>
Save the file.
Now you retry the same command it should go through.
If you dont want to update your inventory.xml which is under the global inventory location, you can pass a parameter to the opatch utility to use a different inventory location provided that inventory has information about this oracle home.
opatch -invPtrLoc [New .loc file location]
Happy troubleshooting !!!!
cheers,
OraclePitStop.
How to associate / map / include / exclude a concurrent program to / from a specific concurrent manager in 11i & R12?
Depending on the business need, at times we might have to exclude / include a concurrent program from/to a specific concurrent manager. Say for example you have created a custom concurrent queue (CCQ) and custom concurrent program (CCP) and you want the CCP to be run only through the CCQ and not through the Standard Manager.
Steps
Login as sysadmin
System Administrator -> Concurrent : Manager -> Define
Press F11 and Search for the CCP
Click on Specialization Rules
Goto the last record and press down arrow (this will add a new row)
Select Include for Include / Exclude Column
Select Program for Type
Select appropriate application type for Application
Select the CCP for Name
Click on floppy icon (to save)
Apart from including to the CCQ, lets also exclude from Standard Manager
Press F11 and Search for the Standard Manager
Click on Specialization Rules
Verify if you already have a row for the CCP (Most probably you will not have one, because the default concurrent queue is always Standard Manager. If in case you have a row for the CCP, perform below steps without adding a new row or Goto the last record and press down arrow (this will add a new row)
Select Exclude for Include / Exclude Column
Select Program for Type
Select appropriate application type for Application
Select the CCP for Name
Click on floppy icon (to save)
Above steps can also be used on PCP setups, wherein you want to run a concurrent program only on one of the CM nodes and not on the other. The needs might differ between organizations but the method to exclude / include remains the same.
cheers,
OraclePitStop.
How to configure Workflow Mailer in 11i & R12
One of the simple configuration in Oracle Apps is the Java Workflow Mailer configuration. The steps are simple but the key point is verifying and validation the pre-reqs before proceeding with the configuration. This post had been divided into two parts 1) Pre-reqs validation and 2) Workflow Mailer configuration.
Pre-reqs validation include SMTP & IMAP server access, dedicated email id and folders creation.
You can download the document here.
cheers,
OraclePitStop.
How to enable / generate trace for a form 11i / R12
This step can be performed by any user who has access to the professional approach (ie., the forms not the jsp). By access i meant any responsibility which launches the form.
Steps: (In the forms window)
1. Click on Help -> Diagnostics -> Trace
2. Under trace you have 5 options – select the appropriate option
3. Key in the apps password in the prompted window.
4. The next window will specify the logfile name and location.
cheers,
OraclePitStop
« Previous Entries
OraclePitStop - Lets share some knowledge 