Oracle Apps R12 : How to find the list of responsibilities assigned to user(s)

Posted on December 17, 2008. Filed under: DBA/Admin Tips, R12, Scripts | Tags: , , |

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

Read Full Post | Make a Comment ( 1 so far )

R12 on 11g Anyone yet?

Posted on November 8, 2008. Filed under: Database, R12, Upgrade | Tags: , , , |

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 )

How to identify if a port is listening on a unix server

Posted on October 6, 2008. Filed under: Apps Technology Stack, DBA/Admin Tips, Diagnostics, Operating System, R12, TroubleShooting | Tags: , , , |

We often get into a situation wherein we have to check if a particular port is being used by some process.
Command

netstat -an | grep [port number] | grep LISTEN

Command to identify the OS process associated with a port

lsof -i tcp:[port number]

cheers,

OraclePitStop

Read Full Post | Make a Comment ( 1 so far )

Blank AppsLogin / AppsLocalLogin Page in R12?

Posted on September 12, 2008. Filed under: Apps Technology Stack, Configuration, Diagnostics, R12, TroubleShooting | Tags: , , , |

There were quite a few times i had encountered this blank page issue and the root cause was not the same all the times. There are many reasons for this issue to occur. Listed below are some of them.

Scenario 1: After a clone

Solution :

1. adopmnctl.sh stopall — to shutdown opmn managed services viz., opmn, forms, oacore & oc4j
2. cd $COMMON_TOP/_pages
3. mv _pages _pages_old
4. cd $FND_TOP/patch/115/bin or export PATH=$FND_TOP/patch/115/bin:$PATH
5. Compile all jsps.

perl ojspCompile.pl –compile –flush -p 2

(-p represents parallel threads, if you have enough horse power on your server, you can increase it to 8 or 10)

6. adopmnctl.sh startall

Scenario 2: Database Listener is down

Solution: Obvious answer, start the database listener.

Scenario 3: Database Archive Locaion is 100% full

Solution: Either temporarily move the archive files to another location or delete older archive files (provided you had taken a cold backup sometime).

Scenario 4: IAS Cache initialization failed

This issue occurs only if you have more than one mid-tier and you had enabled Distributed Java Caching.

Error Message in log file:

Exception in static block of jtf.cache.CacheManager. Stack trace is: oracle.apps.jtf.base.resources.FrameworkException:
IAS Cache initialization failed.
The Distributed Caching System failed to initialize on port: 12357.
The list of hosts in the distributed caching system is: [node 1] [node 2].
The port [port no] should be free on each host running the JVMs.
at oracle.apps.jtf.cache.IASCacheProvider.init(IASCacheProvider.java:214)
at oracle.apps.jtf.cache.CacheManager.activateCache(CacheManager.java:1451)

In this scenario, s_java_object_cache_port value was listening on node 2 but not on node 1. So whichever connection went to node 1 it returned a blank page and occasionally it displayed 500 Internal Server error.

Solution:

Autoconfig

1) Bring down opmn services on both the boxes.
2) Verify no process is listening for s_java_object_cache_port value. click here to view the commands.
2) Change s_java_object_cache_port on all the nodes in the context file
3) Run autoconfig

Non-Autoconfig

s_java_object_cache_port value is referrenced in two places

1) $INST_TOP/ora/10.1.3/javacache/admin/javacache.xml
2) Profile option name JTF_DIST_CACHE_PORT

Change the port value to a new number and bounce opmnctl services. Dont forget to change it in $CONTEXT_FILE. (To take effect during next autoconfig)

Verification:

Use commands listed in step 2 of autoconfig option.

If the new port mentioned still doesnt listen on the boxes you can use below workaround. This workaround will resolve the “IAS Cache initialization failed” even if the port is not listening.

Workaround

Set LONG_RUNNING_JVM=false in $INST_TOP/ora/10.1.3/j2ee/oacore/config/oc4j.properties
Bounce opmn services.

In 11i, if the database listener is down or if the archive location is full, atleast we get the internal server page while trying to access the login page, but in R12 nothing happens except for the blank page and apache error log will have an entry “oc4j_socket_recvfull timed out” with additional error message mod_oc4j: request to OC4J [mid-tier servername:ajp port] failed: Connect failed

So the moral of the story is recompiling the jsps is not always the solution if you get a blank login page.

cheers,
Ram.

Read Full Post | Make a Comment ( 2 so far )

How to change Java heap size in R12 to avoid java.lang.OutOfMemoryError: Java heap space error?

Posted on September 11, 2008. Filed under: Apps Technology Stack, Configuration, Diagnostics, Operating System, R12, Technology, TroubleShooting | Tags: , , , , |

Recently we had encountered a situation, wherein end users where not able to get the R12 login page and
it just hangs, but all the opmn processess were up and running – adopmnctl.sh status gives status as
alive for all the components viz., HTTP, oafm, oacore & forms. The same environment was available and
end users were able to access without any isuses 15 minutes before. This error happens only when
multiple people login to the same page and perform simillar activity like employee self service form
etc. So where and what exactly could be the problem.

This is how we approached and resolved the issue.

First we checked apache error log. The following error was reported in the error log.

==============================================================
[warn] [client IP Address ] oc4j_socket_recvfull timed out
[error] [client IP Address ] mod_oc4j: There is no oc4j process (for destination: application://oacore) available to service request.
[error] [client  IP Address] mod_oc4j: request to OC4J [servername:OC4J AJP Port Range for Oacore]
failed: Connect failed
==============================================================

Above error message does give us a hint that the problem is with oacore, but as i said earlier the oacore is alive according to opmn.

Next, we verified the oacore log file.

($INST_TOP/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err)

Error Message in the file

=======================================================
Exception in thread “OC4JMonitorThread” java.lang.OutOfMemoryError: Java heap space
=======================================================

While checking the error in file, parallely we opened another window to see the CPU and memory usage and we could see one java process was taking more than 100% CPU. This process was spawned by the opmn -d process and the process id didnt match with the oacore process id. Hint: adopmnctl.sh status will give the status as well the process id. It looked like a end client java process.

At this stage we had 3 options.

1. Kill the java process which is consuming high cpu.
2. Bounce oacore component
3. Adjust java (jvm) memory parameters

Each of the above options has its own implications and advantages. To minimize the downtime we decided  to kill the java processes, and the moment we killed the java process, all the browsers which were hanging reported instantaneously Internal Server Error. This proved to be a bad decision.

So we moved to the next option, bouncing the oacore service which we are sure will resolve the issue  (temporarily) and it did as expected. Basically when you bounce the services all the existing
connections and processess will be released which results in more available memory when you re-start the services.

Ok, now we tackled the problem and provided a temporary solution but we need to find a long term solution. This is option 3, adjusting java memory size.

Steps to change the heap size.

First, you need to identify how much is the maximum heap size that you can set. Click here.

Once you had identified the maximum heap size, we need to change the configuration files to make it
permanent.

Step 1: Edit opmn.xml file.

Location : $INST_TOP/ora/10.1.3/opmn/conf/

Open opmn.xml

Search for string Xms or Xmx or module-id=”OC4J”

This search should lead you to below location

‘<‘process-type id=”oacore” module-id=”OC4J” status=”enabled” working-dir=”$ORACLE_HOME/j2ee/home”‘>’
‘<‘category id=”start-parameters”‘>’
‘<‘data id=”java-options” value=”-server -verbose:gc -Xmx512M -Xms128M ……]

The default value for Maximum (-Xmx) and Minimum (-Xms) heap sizes are 512M and 128M respectively.

Again here you have options, you can set both Xms and Xmx has the same value as Xmx if you feel all your sessoins require higher memory or set a lower value for Xms and the maximum value for Xmx. Dont forget to change the values under ‘<‘category id=”stop-parameters”‘>’

opmn.xml also contains jvm configurations for other components – oafm & forms.

Step 2: Edit oc4j.properties file.

Location : ($INST_TOP/ora/10.1.3/j2ee/oacore/config)

This step is optional since we had already made changes in opmn.xml but there is no harm in making the change here. This step will come handy for troubleshooting specific components of Oracle viz., configurator, iSupplier or any other option which heavily utilizes/consumes CPU/memory.

Search for string Xms or Xmx or wrapper.

Option 1: If you find any of the above parameters change the values corresponding to the value you had  mentioned in opmn.xml or even more than that, as long as you dont exceed the maximum heap size limit.

Option 2: If you DO NOT find any of the above parameters, then make an entry like this, under the heading “# Java Object Cache Configuration Parameters”

wrapper.bin.parameters=-Xms[Value]M -Xmx[Value]M -XX:NewSize=256M -XX:MaxNewSize=256M

Step 3: Edit Applications Context file

vi $CONTEXT_FILE

Location: $INST_TOP/appl/admin/SID_hostname.xml

search for string s_oacore_jvm_start_options

Change Xms and Xmx value. Repeat the same step for parameter s_oacore_jvm_stop_options.

Changes made in Step 3 will take effect the next time you run autoconfig, whereas Step 1 & 2 changes will take effect the next time you bounce opmn services, but the values are not permanent in the sense these will be wiped off next time you run autoconfig. Yes you can preserve the changes by placing it inbetween Begin and End customizations.

You can also increase the Garbage Collection threads parameter (-XX:ParallelGCThreads) to a higher value if you have JDK 1.5 or more than 2 cpus or more memory. For more information on this you can refer to Metalink Note: 362851 – Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12.

cheers,
oraclePitStop.

Read Full Post | Make a Comment ( 8 so far )

How to identify/determine the maximum JVM Heap Size for OC4j components in R12 / 10gAS

Posted on September 11, 2008. Filed under: Apps Technology Stack, Configuration, Operating System, R12, TroubleShooting | Tags: , , , , , , |

Below command will help you to identify the maximum memory heap size than can be allocated to a JVM
process.

java -mx[value]m -version

Start with a higher value like 4G or higher and slowly cut down to the maximum allowed value.

Sample

$ java -mx4096m -version

Invalid maximum heap size: -Xmx4096m
The specified size exceeds the maximum representable size.
Could not create the Java virtual machine.

As you can see my system cant support 4Gig for a JVM.

Now lets reduce to a lesser value, viz., 3Gig

$ java -mx3072m -version
java version “1.5.0.04”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0.04-_27_jul_2006_10_52)
Java HotSpot(TM) Server VM (build 1.5.0.04 jinteg:07.27.06-16:18 IA64, mixed mode)

Now its clear that the maximum allowed value is 3gig.

cheers,
OraclePitStop.

Read Full Post | Make a Comment ( 3 so far )

How to copy / migrate printer definitions to a new instance using FNDLOAD

Posted on May 15, 2008. Filed under: Configuration, printer | Tags: , , , , , , |

Imagine below situation:

1. Target had been cloned from source
2. Couple of new printers had been configured on source after the clone.
3. Now you want the same printer configurations on target instance similar to source.

Options:

1. Perform the steps manually on target instance
2. Clone target from source
3. Use FNDLOAD (Generic Loader Utility)

We will discuss FNDLOAD option in this post.

How to download all printer definitions

1. Download the printer definitions from source

FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER

2. Connect to target instance
3. Get the source printer definition file (source_printer_def.ldt) from source (ftp, scp or any method that you like)
4. Create a backup of the target system printer information
4.1. Connect to sqlplus as apps user and below commands

create table fnd_printer_bk as (select * from fnd_printer);
select printer_name from fnd_printer_bk;

5. Now upload the printer definitions into the target system

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER

SQL> select printer_name from fnd_printer;

Above sql should list the new printers that are uploaded.

How to download a specific printer definition

FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppinf.lct source_printer_def.ldt FND_PRINTER PRINTER_NAME=[printer name]

This is one of the functionality of FNDLOAD. Refer to this metalink note : 274667.1 for other features of FNDLOAD.

You can follow this link to migrate forms, menus & responsibilities.

Happy Printer Migrations !!!

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 1 so far )

DISPLAY variable in 11i and R12

Posted on April 14, 2008. Filed under: R12 | Tags: , , , , , |

Is DISPLAY variable must in R12 to display chart/graphs and reports?

Before going into that, lets see whats the impact of setting a wrong value to the DISPLAY variable in 11i.

1) Charts & Graphs cant be viewed (it will be blank with x mark)
2) Reports cannot be viewed in browser (like concurrent request output etc.,)

Troubleshooting the display issue is another big task on non-autoconfig enabled 11i instance, because its set in so many places viz., adcmctl.sh, adrepctl.sh, adfrmctl.sh $APPL_TOP/SID.env $ORACLE_HOME/SID.env.

Coming back to the need for DISPLAY variable value in R12, the answer is partly yes, partly no, in the sense, DISPLAY variable is must but the value can be anything. Excuse Me, what did you just say. Yes, the value can be pointing to any server any value in the format of :. eg. myhost:0.0

In R12, as you all know the mid-tier architecture is totally different from 11i and due to the inclusion of Oracle 10g AS, DISPLAY variable value doesnt make much of difference. In Oracle 10gAS, the REPORTS_DEFAULT_DISPLAY variable determines the need for a DISPLAY variable.

Accepted values for REPORTS_DEFAULT_DISPLAY are YES / NO.

YES – Overrides the need for DISPLAY Variable value even if it is set.
NO – Makes use of the DISPLAY variable.

REPORTS_DEFAULT_DISPLAY achives this functionality (elimination of DISPLAY) by use of J2SE or JDK 1.5 and above. This functionality is available on all OS Platforms except AIX.

For more information on this topic you can refer to Metalink

Note ID: 260256.1 Removal Of Display For Headless Server And Printer Dependencies In Reports 10.1.2

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

Where are the log files located in R12?

Posted on April 6, 2008. Filed under: R12, TroubleShooting | Tags: , , , , |

For DBA’s who mostly worked on 11i environments, finding the log files (Concurrent manager log, apache log etc.,) on a R12 environment might pose a threat initially, because these log files no longer reside in their old location ie., $APPLCSF/$APPLLOG or $APACHE_TOP/Apache/logs.

In R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)

Concurrent Reqeust related logs

$LOG_HOME/appl/conc -> location for concurrent requests log and out files
$LOG_HOME/appl/admin -> location for mid tier startup scripts log files

Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home)

$LOG_HOME/ora/10.1.3/Apache -> Location for Apache Error and Access log files
$LOG_HOME/ora/10.1.3/j2ee -> location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn -> location for opmn related log files

Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)

$LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports

Related metalink notes to enable additional debugging

419839.1 – How to enable Apache, OC4J and OPMN logging in Oracle Applications R12
422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN

Happy troubleshooting !!!

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 2 so far )

Liked it here?
Why not try sites on the blogroll...