-------------- "Erman Arslan's Oracle Forum is available now. Click here to ask a question. " --------------
Wednesday, December 31, 2014
Monday, December 29, 2014
EBS 12.2 -- Create Accounting error ORA-14411
Concurrent Programs like Create accounting may encounter ORA-14411: The DDL cannot be run concurrently with other DDLs errors.
Like the following Journal Import;
So , the ORA-14411 error arises when multiple sessions try to do the ddl on the same table..
Like the following Journal Import;
General Ledger: Version : 12.0.0 Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved. GLLEZL module: Journal Import +-----------------------------These concurrent programs do ddl operations using their routines like gluddl , do_ddl, do_apps_ddl and etc.. Normally, these ddl operations are database ddl operations, in other words , these operations are done in the database . Thus they are dependet to the rules of the underlying database.. (For EBS this database is Oracle ,as known)------------------------------ ----------------+ >> gluddl() 26-DEC-2014 20:45:58 -- gluddl ad.do_ddl error buffer begin do_ddl(APPLSYS, SQLGL, 17, $statement$, GL_INTERFACE): private_do_ddl(APPS, APPLSYS, GL, 17, $statement$, GL_INTERFACE): do_at_tab_a_seq_acd_ind(GL, $statement$): : do_apps_ddl(GL, $statement$): : substr($statement$,1,255)=' Alter table GL_INTERFACE add (code_combination_id_interim NUMBER(15))' -- gluddl ad.do_ddl error buffer end -- gluddl Message Dictionary Start: do_ddl(APPLSYS, SQLGL, 17, $statement$, GL_INTERFACE): private_do_ddl(APPS, APPLSYS, GL, 17, $statement$, GL_INTERFACE): do_at_tab_a_seq_acd_ind(GL, $statement$): : do_apps_ddl(GL, $statement$): : substr($statement$,1,255)=' Alter table GL_INTERFACE add (code_combination_id_interim NUMBER(15))' APP-FND-01388: Cannot read value for profile option GL_GLLEZL_ARCHIVE_ROWS in routine &ROUTINE. APP-FND-01388: Cannot read value for profile option GL_RECORDS_TO_PROCESS in routine &ROUTINE. APP-FND-01388: Cannot read value for profile option GL_JI_ALWAYS_GROUP_BY_DATE in routine &ROUTINE. APP-FND-01388: Cannot read value for profile option GL_DEBUG_MODE in routine &ROUTINE. -- gluddl Message Dictionary End <x gluddl() 26-DEC-2014 20:45:58 ORA-14411: The DDL cannot be run concurrently with other DDLs ORA-0651 <x gllcnt() 26-DEC-2014 20:45:58 Error in: gllcnt Function return status: 0 Function Err Message: gluddl failed ....
So , the ORA-14411 error arises when multiple sessions try to do the ddl on the same table..
In such situation, you need to connect to the database, and look for the active sessions to find the session that does the ddl on relevant tables, thus preventing your concurrent programs to do their jobs..
Once you find out the problematic session, we identify its source and take the corrective actions..
I m saying "we need to identify its source" , because we don't usually do ddls on this kind of Standart tables!
What I m trying to say is; the session that does the ddl on the standart table and blocks the ddls against that standart table may belong to anohter Create Accounting process which left behind or forgotten.. Maybe the problem is running the same process again and again concurrently.. So , If you Identify such a session, contact your Functional Team, there may be someting functionally broke.
Lastly, once the cause of issue is found, just kill the session or cancel the concurrent request which the blocker session belongs.
Sunday, December 21, 2014
EBS R12 -- 5-byte UTF8 encoding not supported.
Using EBS XML reports, you may encounter problems because of junk characters..
You may see "5-byte UTF8 encoding not supported." in your OPP logfile..
When you encounter such a problem, you may see that your PDF or RTF output is not produced.. You may see only an XML output.. In other words; this problem will make OPP not be able to produce the desired output..
Also , the functionals or developers will directly send this problem to us (Apps Dbas).. They will redirect this problem to us, as they may think that it is related with configuration..
Here I m writing this post to state the real thing that causes theses type of problems. As mentioned in my first sentence, the problem arises because of junk characters..
Junk characters such as �..
That �.. for example corresponds to 11111000 in ISO 8859-1 character set , and this creates the problem as when the bytes are converted to UFT8..
You may see "5-byte UTF8 encoding not supported." in your OPP logfile..
When you encounter such a problem, you may see that your PDF or RTF output is not produced.. You may see only an XML output.. In other words; this problem will make OPP not be able to produce the desired output..
Also , the functionals or developers will directly send this problem to us (Apps Dbas).. They will redirect this problem to us, as they may think that it is related with configuration..
Here I m writing this post to state the real thing that causes theses type of problems. As mentioned in my first sentence, the problem arises because of junk characters..
Junk characters such as �..
That �.. for example corresponds to 11111000 in ISO 8859-1 character set , and this creates the problem as when the bytes are converted to UFT8..
I mean the parser sees 11111000 and then thinks that it is a start of a 5 byte UTF8 sequence.
The solution can be replacing this character in Sql .. Using char() ..
That 's it.. I hope you will find this useful.
EBS R12/LINUX -- 32 bit Application tier with large memory -- OMM-KILLER problem
Another 32 bit problem, this time OMM-killer comes into play and kills processes without knowing the importance of them.. Sometimes , OMM-killer kills an application processes and make the Application Web Service to be down, and sometimes it kill a backup process and affect production system backups..
The reason behind the utilization of OMM-killer is using an 32 bit Linux OS and installing a big sized memory(like 64 GBs)..
The problem arises in Oracle/Redhat Linux 5 and 6 32 bit, as those operating systems can not handle the memory stably when the installed memory is more than 16GB..
16 GB memory however , can be used with Oracle/Redhat Linux 32 bit Operating Systems without any problems..
The reason behind this incapability is not being able to use HugePages.. As you may know, starting with Oracle Linux 5 for example; hugemem is not option..
So even if you can use PAE to address a big memory, and even if you can address a big memory like 64 bit, you will end up with OMM-killer killing your processes...
I mean even our UEK kernel supports PAE and can address those big sized RAMs, we 'll still have problems in runtime..
Note: Checking PAE support:
What I try to say is;
OMM-killer kill your processes because it see a shortage in LowMem area. Hugepage option in earlier Linux releases address this problem by making us able to use a 4-4 split rather tan 1-3 memory split.
You will have a better idea , when you read my related post about Linux 32 bit memory addressing ,->
http://ermanarslan.blogspot.com.tr/2013/11/linux-ebs-11i-uek-kernel-pae-support32.html
Okay, lets list the recommended solution for this particular environment;
1) Why not go for 64 bit? EBS Release 12 supports 64 bit in Apps Tier too!
2) Deinstall those RAMs, and use 16GB Ram in your PAE enabled Linux 32 bit envrionment
- Also, increase the lowmem zone protection ..
For Oracle Linux 5/Rhel 5 and higher:
add the following line into /etc/sysctl.conf
The reason behind the utilization of OMM-killer is using an 32 bit Linux OS and installing a big sized memory(like 64 GBs)..
The problem arises in Oracle/Redhat Linux 5 and 6 32 bit, as those operating systems can not handle the memory stably when the installed memory is more than 16GB..
The reason behind this incapability is not being able to use HugePages.. As you may know, starting with Oracle Linux 5 for example; hugemem is not option..
So even if you can use PAE to address a big memory, and even if you can address a big memory like 64 bit, you will end up with OMM-killer killing your processes...
I mean even our UEK kernel supports PAE and can address those big sized RAMs, we 'll still have problems in runtime..
Note: Checking PAE support:
Go to /boot ;
cat config-2.6.32-300.10.1.el5uek. (your kernel) | grep CONFIG_X84_PAE;
If a line like CONFIG_X86_PAE=y is returned, yhen you kernel supports PAE and it is active right now.
What I try to say is;
OMM-killer kill your processes because it see a shortage in LowMem area. Hugepage option in earlier Linux releases address this problem by making us able to use a 4-4 split rather tan 1-3 memory split.
You will have a better idea , when you read my related post about Linux 32 bit memory addressing ,->
http://ermanarslan.blogspot.com.tr/2013/11/linux-ebs-11i-uek-kernel-pae-support32.html
Okay, lets list the recommended solution for this particular environment;
1) Why not go for 64 bit? EBS Release 12 supports 64 bit in Apps Tier too!
2) Deinstall those RAMs, and use 16GB Ram in your PAE enabled Linux 32 bit envrionment
- Also, increase the lowmem zone protection ..
For Oracle Linux 5/Rhel 5 and higher:
add the following line into /etc/sysctl.conf
vm.lowmem_reserve_ratio = 256 256 250
Also execute following for current setting.
echo "256 256 250" > /proc/sys/vm/lowmem_reserve_ratio
For earlier releases :
echo "250" > /proc/sys/vm/lower_zone_protection
3)You may try with OMM disabled: (not recommended) --just in caseAdd a line to /etc/sysctl.conf and reboot
vm.oom-kill = 0
Also execute following for current setting.
echo "256 256 250" > /proc/sys/vm/lowmem_reserve_ratio
For earlier releases :
echo "250" > /proc/sys/vm/lower_zone_protection
3)You may try with OMM disabled: (not recommended) --just in caseAdd a line to /etc/sysctl.conf and reboot
vm.oom-kill = 0
EBS -- Analyzing Concurrent Processing --CP analyzer
CP analyzer is a great tool to analyze Concurrent Processing environment in EBS instances.. It gives you a detailed and clear picture in HTML format.. Thus, you gather almost all the information you need to investigate the distinct problem causes in Concurrent Processing environment, without struggling with OS commands or complicated sql statements..
I strongly recommend to use CP analyzer in making Health Checks for EBS environments.
CP analyzer is available via the following Oracle Doc:
Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)
CP analyzer can be used in any EBS envrionment with version 11.5.10.2 to 12.2.3 [Release 11.5 to 12.2]
I strongly recommend to use CP analyzer in making Health Checks for EBS environments.
CP analyzer is available via the following Oracle Doc:
Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1411723.1)
CP analyzer can be used in any EBS envrionment with version 11.5.10.2 to 12.2.3 [Release 11.5 to 12.2]
It does te following analysis and provide an HTML for you to view those analysis;
- Immediate Analysis and Output of Concurrent Environment
- E-Business Applications Concurrent Processing Analyzer Overview
- Total Purge Eligible Records in FND_CONCURRENT_REQUESTS
- E-Business Suite Version
- Concurrent Processing Database Parameter Settings
- Applied ATG Patches
- Known 1-Off Patches on top of Rollups
- E-Business Applications Concurrent Request Analysis
- Long Running Reports During Business Hours
- Elapsed Time History of Concurrent Requests
- Requests Currently Running on a System
- FND_CONCURRENT_REQUESTS Totals
- Running Requests
- Total Pending Requests by Status Code
- Count Pending Regularly Scheduled/Non Regularly-Scheduled Requests
- Count of Pending Requests on Hold/Not on Hold
- Listing of Scheduled Requests
- Listing of Pending Requests on Hold
- Listing of Pending Requests Not on Hold
- Volume of Daily Concurrent Requests for Last Month
- Identify/Resolve the Pending/Standby Issue, if Caused by Run Alone Flag
- Tablespace Statistics for the fnd_concurrent tables
- E-Business Applications Concurrent Manager Analysis
- Concurrent Managers Active and Enabled
- Concurrent Manager Processes by Workshift
- Active Manager for Applications that are not Installed/Used
- Total Target Processes for Request Managers (Excluding Off-Hours)
- Request Managers with Incorrect Cache Size
- Concurrent Manager Request Summary by Manager
- Check Manager Queues for Pending Requests
- Check the Configuration of OPP
- E-Business Applications Concurrent Processing Analyzer Overview
- Identifies Concurrent System Setup and configurations
- Identifies and recommends Concurrent Best Practices
- Easy to add Tool for regular Concurrent Maintenance
- Execute Analysis anytime to compare trending from past outputs
EBS 12.2-- Output Post Processor(OPP) Performance in RTF and Excel reports.
The need to investigate the performance of OPP have arised by a long running Report in a Production EBS 12.2 instance. The name of report was Chart of Accounts - Segment Values Listing (XML)..
The issue was there for all the XML reports actually.. Especially, XML ereports which produce big sized RTF and Excel outputs..
In this blog post;
I will directly jump to the conclusino and then give the diagnostics & actions whic was taken to fix the issue. I m giving the conclusion first because; the conclusion is not differ according to the our actions, whatever the actions are...
Okay.. This is a limitation problem.
I mean, as OPP is a Java Concurrent Manager dedicated to BI Publisher, and as BI Publisher is all about java processing; and because Concurrent Tier uses 32 Bit JVM in EBS 12.2; we are under control of the limits in 32 Bit JVM ..
Altough, EBS 12.2 uses 10.1.3.4.2 version of BI Publisher, it is still uses a 32 bit JVM in XML processing.
So, in such a large RTF processing/producing , there is nothing that can be done about the performance of OPP....
Note that:
These are the facts about the BIP output formats from an RTF layout template type:
- PDF output has the most efficient file size
- RTF output generates several times larger than pdf for the same xml data set size
- Excel output (which is really XHTML format) can be 10X larger than the pdf for the same xml data set size
What are the performance characteristics of 64-bit versus 32-bit VMs?
Generally, the benefits of being able to address larger amounts of memory come with a small performance loss in 64-bit VMs versus running the same application on a 32-bit VM. This is due to the fact that every native pointer in the system takes up 8 bytes instead of 4. The loading of this extra data has an impact on memory usage which translates to slightly slower execution depending on how many pointers get loaded during the execution of your Java program. The good news is that with AMD64 and EM64T platforms running in 64-bit mode, the Java VM gets some additional registers which it can use to generate more efficient native instruction sequences. These extra registers increase performance to the point where there is often no performance loss at all when comparing 32 to 64-bit execution speed.
The performance difference comparing an application running on a 64-bit platform versus a 32-bit platform on SPARC is on the order of 10-20% degradation when you move to a 64-bit VM. On AMD64 and EM64T platforms this difference ranges from 0-15% depending on the amount of pointer accessing your application performs
Anyways.. Lets explore what I have done; when the issue was escalated to me..
Note that: the cause is :32 bit JVM limitations in here but still; I m giving these info to you, because it might help you diagnosing similar issues in EBS 12.2
I have done the following diagnostics;
Process 8527 attached - interrupt to quit
[ Process PID=8527 runs in 32 bit mode. ]
futex(0xf7743bd8, FUTEX_WAIT, 8530, NULL
strace -p 8530 (the thead that main process was waiting)
Process 8530 attached - interrupt to quit
[ Process PID=8530 runs in 32 bit mode. ]
futex(0xf7507644, FUTEX_WAIT_PRIVATE, 1, NULL
The problematic thing was GLXRLSEG_XML
It starts at 10:06 , still continuing at 11:40
[12/12/14 10:06:11 AM] [11816:RT636361] XML Publisher post-processing action complete.
[12/12/14 10:06:12 AM] [11816:RT636361] Completed post-processing actions for request 636361.
[12/12/14 10:11:28 AM] [OPPServiceThread0] Post-processing request 636387.
[12/12/14 10:11:28 AM] [11816:RT636387] Executing post-processing actions for request 636387.
[12/12/14 10:11:28 AM] [11816:RT636387] Starting XML Publisher post-processing action.
[12/12/14 10:11:28 AM] [11816:RT636387]
Template code: GLXRLSEG_XML
java.lang.Thread.State: RUNNABLE
at oracle.xdo.parser.v2.XSLExprValue.mergeOrderedList(XSLExprValue.java:551)
OS Thread analysis: ps -eLo pid,ppid,tid,pcpu,comm | grep 19686
19686 17301 19686 0.0 java
19686 17301 19687 0.0 java
19686 17301 19688 0.0 java
19686 17301 19689 0.0 java
19686 17301 19690 0.0 java
19686 17301 19691 0.0 java
19686 17301 19692 0.0 java
19686 17301 19693 0.0 java
19686 17301 19694 0.0 java
19686 17301 19695 0.0 java
19686 17301 19696 0.0 java
19686 17301 19697 0.0 java
19686 17301 19698 0.0 java
19686 17301 19699 0.0 java
19686 17301 19700 0.0 java
19686 17301 19701 0.0 java
19686 17301 19703 0.0 java
19686 17301 19718 0.0 java
19686 17301 19719 0.0 java
19686 17301 19720 0.0 java
19686 17301 19721 0.0 java
19686 17301 19722 0.0 java
19686 17301 19723 0.0 java
19686 17301 19724 0.0 java
19686 17301 19725 0.0 java
19686 17301 19726 0.0 java
19686 17301 19727 0.0 java
19686 17301 19728 0.0 java
19686 17301 19729 0.0 java
19686 17301 19730 0.0 java
19686 17301 19733 0.0 java
19686 17301 19734 0.0 java
19686 17301 20212 99.4 java The only thread that does the work is this.
19686 17301 20302 0.0 java
1)
In XML Publisher Administrator responsibility >Administration > Configuration
- under : Properties -> General -> set a temporary directory
This directory should be at least 5 times larger than the largest anticipated data file.
Support recommends the directory to be at least 5GB.
2)
XML Publisher Administrator responsibility ->
Navigation Path : XML Publisher Administrator responsibility
- query the data definition
- on the View Data Definition page select Edit Configuration button
- set 'Use XML Publisher's XSLT processor' to True
- set 'Enable scalable feature of XSLT processor' to True
- set 'Enable XSLT runtime optimization' to True
Note that these property values, when set at the Data Definition level, takes precedence over values set at the Site level.
3)
in System Administrator > System
- set Concurrent:OPP Process Timeout = 43200
- set Concurrent:OPP Response Timeout = 43200
(these values are for 12 hours in seconds)
4)
5)
The actions above could at least made the report to successfully complete in 1 hours..To decrease this completion time; one should use BI Publisher Enterprise 11g product is 64-bit as it will use the 64-bit JVM which is exponentially larger.
By the way, I said using BIP 64 is a solution, but BIP 64 bit is a separate application/rquired to be installed separatley. BIP 64 bit can not be embedded into EBS, as it is not 32 bit..
BI Publisher 32 bit however,can be embedded into EBS.. It comes embedded in EBS. Oracle used the libraries from it to EMBED it into EBS, to replace/upgrade the aging 5.6.3 version.
They were able to do this because 10.1.4.3.2 is still 32-bit.
The issue was there for all the XML reports actually.. Especially, XML ereports which produce big sized RTF and Excel outputs..
In this blog post;
I will directly jump to the conclusino and then give the diagnostics & actions whic was taken to fix the issue. I m giving the conclusion first because; the conclusion is not differ according to the our actions, whatever the actions are...
Okay.. This is a limitation problem.
I mean, as OPP is a Java Concurrent Manager dedicated to BI Publisher, and as BI Publisher is all about java processing; and because Concurrent Tier uses 32 Bit JVM in EBS 12.2; we are under control of the limits in 32 Bit JVM ..
Altough, EBS 12.2 uses 10.1.3.4.2 version of BI Publisher, it is still uses a 32 bit JVM in XML processing.
So, in such a large RTF processing/producing , there is nothing that can be done about the performance of OPP....
Note that:
These are the facts about the BIP output formats from an RTF layout template type:
- PDF output has the most efficient file size
- RTF output generates several times larger than pdf for the same xml data set size
- Excel output (which is really XHTML format) can be 10X larger than the pdf for the same xml data set size
What are the performance characteristics of 64-bit versus 32-bit VMs?
Generally, the benefits of being able to address larger amounts of memory come with a small performance loss in 64-bit VMs versus running the same application on a 32-bit VM. This is due to the fact that every native pointer in the system takes up 8 bytes instead of 4. The loading of this extra data has an impact on memory usage which translates to slightly slower execution depending on how many pointers get loaded during the execution of your Java program. The good news is that with AMD64 and EM64T platforms running in 64-bit mode, the Java VM gets some additional registers which it can use to generate more efficient native instruction sequences. These extra registers increase performance to the point where there is often no performance loss at all when comparing 32 to 64-bit execution speed.
The performance difference comparing an application running on a 64-bit platform versus a 32-bit platform on SPARC is on the order of 10-20% degradation when you move to a 64-bit VM. On AMD64 and EM64T platforms this difference ranges from 0-15% depending on the amount of pointer accessing your application performs
Anyways.. Lets explore what I have done; when the issue was escalated to me..
Note that: the cause is :32 bit JVM limitations in here but still; I m giving these info to you, because it might help you diagnosing similar issues in EBS 12.2
I have done the following diagnostics;
- Traced the process(the running thread inside of it) using strace;
Process 8527 attached - interrupt to quit
[ Process PID=8527 runs in 32 bit mode. ]
futex(0xf7743bd8, FUTEX_WAIT, 8530, NULL
strace -p 8530 (the thead that main process was waiting)
Process 8530 attached - interrupt to quit
[ Process PID=8530 runs in 32 bit mode. ]
futex(0xf7507644, FUTEX_WAIT_PRIVATE, 1, NULL
- Looking at the load of the server
- Java version: Java version is
- The java program itself: Problematic process is :
- OS , hostname and the kernel info: uname -a
- Concurrent processing analysis using CP Analzer:
There is no load on the server. Java HotSpot(TM) Server VM (build 23.25-b01, mixed mode)
[appldev@erpdev scripts]$ top
top - 17:55:21 up 21:27, 9 users, load average: 2.06, 1.49, 1.21
Tasks: 413 total, 2 running, 411 sleeping, 0 stopped, 0 zombie
Cpu(s): 14.6%us, 1.1%sy, 0.0%ni, 78.0%id, 6.1%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 12198304k total, 12032184k used, 166120k free, 39948k buffers
Swap: 16383992k total, 318672k used, 16065320k free, 6002308k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8527 appldev 20 0 822m 193m 19m S 99.8 1.6 15:46.39 java
/u01/apps/fs1/EBSapps/comn/util/jdk32/bin/java -version
java version "1.7.0_25"
Java(TM) SE Runtime Environment (build 1.7.0_25-b15)
Java HotSpot(TM) Server VM (build 23.25-b01, mixed mode)
/u01/apps/fs1/EBSapps/comn/util/jdk32/bin/java -Dxdo.xliff.source=EBS -DCLIENT_PROCESSID=3969 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Djava.security.egd=file:///dev/urandom -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx600m -Ddbcfile=/u01/apps/fs1/inst/apps/DEV_erpdev/appl/fnd/12.0.0/secure/DEV.dbc -Dcpid=11756 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/apps/fs_ne/inst/DEV_erpdev/logs/appl/conc/log/FNDOPP11756.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController
Linux erpdev 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Dec 13 06:58:20 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
- OPP manager log:
The problematic thing was GLXRLSEG_XML
It starts at 10:06 , still continuing at 11:40
[12/12/14 10:06:11 AM] [11816:RT636361] XML Publisher post-processing action complete.
[12/12/14 10:06:12 AM] [11816:RT636361] Completed post-processing actions for request 636361.
[12/12/14 10:11:28 AM] [OPPServiceThread0] Post-processing request 636387.
[12/12/14 10:11:28 AM] [11816:RT636387] Executing post-processing actions for request 636387.
[12/12/14 10:11:28 AM] [11816:RT636387] Starting XML Publisher post-processing action.
[12/12/14 10:11:28 AM] [11816:RT636387]
Template code: GLXRLSEG_XML
- The running java code from thread dump:
at oracle.xdo.parser.v2.XSLExprValue.mergeOrderedList(XSLExprValue.java:551)
- Java threads stack Dump using jstack:
Worker-3" daemon prio=10 tid=0x23a00c00 nid=0x41b7 in Object.wait() [0x21669000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at oracle.ias.cache.TaskQ.waitForWork(TaskQ.java:255)
- locked <0x3354ad50> (a oracle.ias.cache.TaskQ)
at oracle.ias.cache.TaskQ.getTask(TaskQ.java:138)
- locked <0x3354ad50> (a oracle.ias.cache.TaskQ)
at oracle.ias.cache.WorkerThread.run(ThreadPool.java:303)
"9786:RT639072" daemon prio=10 tid=0x24002c00 nid=0x3a28 runnable [0x216b9000]
java.lang.Thread.State: RUNNABLE
at oracle.xdo.parser.v2.XSLExprValue.mergeOrderedList(XSLExprValue.java:551)
at oracle.xdo.parser.v2.XPathAxis.getDescNodeList(XPathAxis.java:200)
at oracle.xdo.parser.v2.XPathStep.evaluate(XPathStep.java:405)
at oracle.xdo.parser.v2.PathExpr.evaluate(XSLNodeSetExpr.java:873)
at oracle.xdo.parser.v2.ComparisonExpr.evaluate(XSLExpr.java:1457)
at oracle.xdo.parser.v2.XSLExprBase.testBooleanExpr(XSLExprBase.java:298)
at oracle.xdo.parser.v2.AndExpr.evaluate(XSLExpr.java:441)
at oracle.xdo.parser.v2.XSLExprBase.testBooleanExpr(XSLExprBase.java:298)
at oracle.xdo.parser.v2.XPathFunctionCall.evaluate(XPathFunctionCall.java:695)
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at oracle.ias.cache.TaskQ.waitForWork(TaskQ.java:255)
- locked <0x3354ad50> (a oracle.ias.cache.TaskQ)
at oracle.ias.cache.TaskQ.getTask(TaskQ.java:138)
- locked <0x3354ad50> (a oracle.ias.cache.TaskQ)
at oracle.ias.cache.WorkerThread.run(ThreadPool.java:303)
"9786:RT639072" daemon prio=10 tid=0x24002c00 nid=0x3a28 runnable [0x216b9000]
java.lang.Thread.State: RUNNABLE
at oracle.xdo.parser.v2.XSLExprValue.mergeOrderedList(XSLExprValue.java:551)
at oracle.xdo.parser.v2.XPathAxis.getDescNodeList(XPathAxis.java:200)
at oracle.xdo.parser.v2.XPathStep.evaluate(XPathStep.java:405)
at oracle.xdo.parser.v2.PathExpr.evaluate(XSLNodeSetExpr.java:873)
at oracle.xdo.parser.v2.ComparisonExpr.evaluate(XSLExpr.java:1457)
at oracle.xdo.parser.v2.XSLExprBase.testBooleanExpr(XSLExprBase.java:298)
at oracle.xdo.parser.v2.AndExpr.evaluate(XSLExpr.java:441)
at oracle.xdo.parser.v2.XSLExprBase.testBooleanExpr(XSLExprBase.java:298)
at oracle.xdo.parser.v2.XPathFunctionCall.evaluate(XPathFunctionCall.java:695)
- Os thead analysis:
19686 17301 19686 0.0 java
19686 17301 19687 0.0 java
19686 17301 19688 0.0 java
19686 17301 19689 0.0 java
19686 17301 19690 0.0 java
19686 17301 19691 0.0 java
19686 17301 19692 0.0 java
19686 17301 19693 0.0 java
19686 17301 19694 0.0 java
19686 17301 19695 0.0 java
19686 17301 19696 0.0 java
19686 17301 19697 0.0 java
19686 17301 19698 0.0 java
19686 17301 19699 0.0 java
19686 17301 19700 0.0 java
19686 17301 19701 0.0 java
19686 17301 19703 0.0 java
19686 17301 19718 0.0 java
19686 17301 19719 0.0 java
19686 17301 19720 0.0 java
19686 17301 19721 0.0 java
19686 17301 19722 0.0 java
19686 17301 19723 0.0 java
19686 17301 19724 0.0 java
19686 17301 19725 0.0 java
19686 17301 19726 0.0 java
19686 17301 19727 0.0 java
19686 17301 19728 0.0 java
19686 17301 19729 0.0 java
19686 17301 19730 0.0 java
19686 17301 19733 0.0 java
19686 17301 19734 0.0 java
19686 17301 20212 99.4 java The only thread that does the work is this.
19686 17301 20302 0.0 java
Okay, after collecting the diagnostics info, I have taken following actions to tune OPP, and I acutally tuned the OPP process but these did not fix the problem.
In XML Publisher Administrator responsibility >Administration > Configuration
- under : Properties -> General -> set a temporary directory
This directory should be at least 5 times larger than the largest anticipated data file.
Support recommends the directory to be at least 5GB.
2)
XML Publisher Administrator responsibility ->
Navigation Path : XML Publisher Administrator responsibility
- query the data definition
- on the View Data Definition page select Edit Configuration button
- set 'Use XML Publisher's XSLT processor' to True
- set 'Enable scalable feature of XSLT processor' to True
- set 'Enable XSLT runtime optimization' to True
Note that these property values, when set at the Data Definition level, takes precedence over values set at the Site level.
3)
in System Administrator > System
- set Concurrent:OPP Process Timeout = 43200
- set Concurrent:OPP Response Timeout = 43200
(these values are for 12 hours in seconds)
4)
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
set DEVELOPER_PARAMETERS = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
rebooted the server (for futex wait)
7)
Changed OPP thead counts, made it to run single thead; then made it to run in several thread.. It was just like a Guessing Game
6)
Changed the java process to use urandom instead of random
Stopped the NTP
8)
/etc/init.d/ntp stop
then;
date `date +"%m%d%H%M%C%y.%S"`
then;
date `date +"%m%d%H%M%C%y.%S"`
The actions above could at least made the report to successfully complete in 1 hours..To decrease this completion time; one should use BI Publisher Enterprise 11g product is 64-bit as it will use the 64-bit JVM which is exponentially larger.
By the way, I said using BIP 64 is a solution, but BIP 64 bit is a separate application/rquired to be installed separatley. BIP 64 bit can not be embedded into EBS, as it is not 32 bit..
BI Publisher 32 bit however,can be embedded into EBS.. It comes embedded in EBS. Oracle used the libraries from it to EMBED it into EBS, to replace/upgrade the aging 5.6.3 version.
They were able to do this because 10.1.4.3.2 is still 32-bit.
Sunday, December 14, 2014
EBS 12.2 -- Weblogic Aware Administration
In EBS 12.2 , Weblogic is a core component for us as Apps Dbas..
To have such a capable but also complex Application Server in our Technology Stack brings us the requirement to have advanced administration knowledge about it.. Also , our forms server, oacore server ,oafm and more reside in Weblogic.. So if our Weblogic is down, then we can say that EBS is down from the users perspective.. (http server, listeners, concurrent managers and the database is up actually:) )
Okay, I said advanced , because I have faced the situations which made me spend times to solve..
I mean, if you say "this is EBS , Weblogic is just a component comes built-in, so I manage the Weblogic using EBS tools, and I solve the problems using autoconfig and similar operations in conjuction with Oracle support document" , you will have hard times.. You need to follow oracle support doc, that is for sure but there are times that we need to take deeps dive in to the Weblogic to solve some of our problems, or lets say : to diagnose the problems..
I have seen this, I have done this.. Look at my posts about EBS 12.2.
Weblogic is involved in patching, in cloning, in restarting, in java deployments , in performance problems and so on.. Weblogic itself is also need to be patched rarely..
Sometimes, you connect in to the Weblogic using admin console to take necessary actions and sometimes you wont find an admin console to connect to, that's why using manual approaches..
Anyways, In this post I tried to draw attention to Weblogic in EBS 12.2.. Java knowledge is also important, one should understand the java error stack , one should able to deal with java command line parameters like xmx,xms,permsize so on..
One last thing... If you are not in TXK and AD.C delta 5 patch levels, you will be a Weblogic specialist one day, as there are bugs that you need to diagnose (especially performance)and solve in the previous patch levels.
Essential Weblogic related Bugs fixed in for AD Delta 5 and TXK Delta 5
To have such a capable but also complex Application Server in our Technology Stack brings us the requirement to have advanced administration knowledge about it.. Also , our forms server, oacore server ,oafm and more reside in Weblogic.. So if our Weblogic is down, then we can say that EBS is down from the users perspective.. (http server, listeners, concurrent managers and the database is up actually:) )
Okay, I said advanced , because I have faced the situations which made me spend times to solve..
I mean, if you say "this is EBS , Weblogic is just a component comes built-in, so I manage the Weblogic using EBS tools, and I solve the problems using autoconfig and similar operations in conjuction with Oracle support document" , you will have hard times.. You need to follow oracle support doc, that is for sure but there are times that we need to take deeps dive in to the Weblogic to solve some of our problems, or lets say : to diagnose the problems..
I have seen this, I have done this.. Look at my posts about EBS 12.2.
Weblogic is involved in patching, in cloning, in restarting, in java deployments , in performance problems and so on.. Weblogic itself is also need to be patched rarely..
Sometimes, you connect in to the Weblogic using admin console to take necessary actions and sometimes you wont find an admin console to connect to, that's why using manual approaches..
Anyways, In this post I tried to draw attention to Weblogic in EBS 12.2.. Java knowledge is also important, one should understand the java error stack , one should able to deal with java command line parameters like xmx,xms,permsize so on..
One last thing... If you are not in TXK and AD.C delta 5 patch levels, you will be a Weblogic specialist one day, as there are bugs that you need to diagnose (especially performance)and solve in the previous patch levels.
Essential Weblogic related Bugs fixed in for AD Delta 5 and TXK Delta 5
- Performance improvements have been made for managing application tier services, including implementation of the Managed Server Independence Mode feature (
-msimodeparameter toadstrtal.sh) to allow application tier services to be started or stopped without the WebLogic Administration Server running. - On a multi-node application tier system configuration, remote connectivity is no longer required for packaging the Oracle E-Business Suite WebLogic Server domain.
- JVM heap size (-Xms and -Xmx) has been increased to 1 GB for the WebLogic Administration Server and all managed servers.
EBS 12.2 -- adop phase=prepare pasteConfig.sh hangs java.lang.OutOfMemoryError
While applying a little 5kb patch using online patching (which is a must bytheway), you may encounter a hand situation.. Spending hours to find out the causes and correcting them during a Project implementation, is not something we want to deal.. Also inability to apply a 5kb patch even in 1 hour is does not seem good from the Business perspective..
The problem that I will mention in the below paragraps is a good example for the unwanted situations we as Apps Dba may encounter in EBS 12.2.3..
Lets review the online patching in EBS 12.2.. So , to apply a patch, we start a patching cycle using adop phase=prepare and after that we continue with phase=apply, phase=finalize, phase=cutover and phase=cleanup.
This problematic situation arises in the "prepare" phase, as we cant complete the prepare phase in hours..
There is definetely a hang situation , but we have a lot of place to collect the data for our diagnosis..
We have some clues though.. For example : adop log files..
Anyways, when you see a line starting with pasteConfig.sh in the tail of the related log file, you are there where I was 1 day ago :)
Lets clarify the situation..
In pasteConfig , adop tries to pack and unpack Weblogic domain to supply the Weblogic consistency accross the patches. It deals with files and it runs java programs against those files..
The problem that I will mention in the below paragraps is a good example for the unwanted situations we as Apps Dba may encounter in EBS 12.2.3..
Lets review the online patching in EBS 12.2.. So , to apply a patch, we start a patching cycle using adop phase=prepare and after that we continue with phase=apply, phase=finalize, phase=cutover and phase=cleanup.
This problematic situation arises in the "prepare" phase, as we cant complete the prepare phase in hours..
There is definetely a hang situation , but we have a lot of place to collect the data for our diagnosis..
We have some clues though.. For example : adop log files..
Anyways, when you see a line starting with pasteConfig.sh in the tail of the related log file, you are there where I was 1 day ago :)
Lets clarify the situation..
In pasteConfig , adop tries to pack and unpack Weblogic domain to supply the Weblogic consistency accross the patches. It deals with files and it runs java programs against those files..
These java programs often encounter OutOfMemoryError ... sometimes it is about java heaps, sometimes you ll not able to see the problematic memory area, but here is what you need to do if you see OutOfMemoryError in the related log file while running PasteConfig.sh during adop prepare session.
1)export CONFIG_JVM_ARGS="-Xms1024m -Xmx2048m"(OR MAYBE MORE) in your terminal and restart the adop phase=prepare
2)Edit the related scripts in COMMON_TOP/adopclone_hostname/bin, and change the values from 600M to 2000M in the related files. (you can use grep -R 600M * in the above directory to find the files that contains 600M xmx limit )
3) Delete the old weblogic logiles; and use /dev/null > to the current weblogic logfiles .. Because; your log size may be increased recently.. pasteConfig creates a CLONINGCLIENT***/packed_template.jar firstly , and it unpacks it .. When this file becomes bigger, the risk to get outOfmemoryError increases.
Note that: we make the controls using the logfile located in admin/clone directory ..
For example: /u01/oracle/TEST/fs2/inst/apps/TEST_erptest/admin/log/clone/wlsT2PApply log
Following lines from the related log file describes the real problem that makes adop to hang ;
Configuring the Server - AdminServer
Configuring the Server - oacore_server1
Configuring the Server - forms_server1
Configuring the Server - oafm_server1
Configuring the Server - forms-c4ws_server1
Configuring the Machine - erptest
Configuring the Cluster - forms_cluster1
Configuring the Cluster - oacore_cluster1
Configuring the Cluster - oafm_cluster1
Configuring the Cluster - forms-c4ws_cluster1
Configuring the Datasource - EBSDataSource
Exiting WebLogic Scripting Tool.
INFO : Dec 13, 2014 7:25:31 PM - CLONE-23283 Unpack Domain.
<< read template from "/tmp/CLONINGCLIENT8433880607085395265/packed_template.jar"
>> succeed: read template from "/tmp/CLONINGCLIENT8433880607085395265/packed_template.jar"
<< set config option ServerStartMode to "prod"
>> succeed: set config option ServerStartMode to "prod"
<< write Domain to "/u01/oracle/TEST/fs1/FMW_Home/user_projects/domains/EBS_domain_TEST"
Exception in thread "Thread-1" java.lang.OutOfMemoryError
So , as you see there is an java.lang.OutOfMemoryError there .. It is encountering the unpacking phase which confirms the thing that I mentioned in the 3th step of the below action plan.
Configuring the Server - oacore_server1
Configuring the Server - forms_server1
Configuring the Server - oafm_server1
Configuring the Server - forms-c4ws_server1
Configuring the Machine - erptest
Configuring the Cluster - forms_cluster1
Configuring the Cluster - oacore_cluster1
Configuring the Cluster - oafm_cluster1
Configuring the Cluster - forms-c4ws_cluster1
Configuring the Datasource - EBSDataSource
Exiting WebLogic Scripting Tool.
INFO : Dec 13, 2014 7:25:31 PM - CLONE-23283 Unpack Domain.
<< read template from "/tmp/CLONINGCLIENT8433880607085395265/packed_template.jar"
>> succeed: read template from "/tmp/CLONINGCLIENT8433880607085395265/packed_template.jar"
<< set config option ServerStartMode to "prod"
>> succeed: set config option ServerStartMode to "prod"
<< write Domain to "/u01/oracle/TEST/fs1/FMW_Home/user_projects/domains/EBS_domain_TEST"
Exception in thread "Thread-1" java.lang.OutOfMemoryError
So , as you see there is an java.lang.OutOfMemoryError there .. It is encountering the unpacking phase which confirms the thing that I mentioned in the 3th step of the below action plan.
ACTION PLAN:
1)export CONFIG_JVM_ARGS="-Xms1024m -Xmx2048m"(OR MAYBE MORE) in your terminal and restart the adop phase=prepare
2)Edit the related scripts in COMMON_TOP/adopclone_hostname/bin, and change the values from 600M to 2000M in the related files. (you can use grep -R 600M * in the above directory to find the files that contains 600M xmx limit )
3) Delete the old weblogic logiles; and use /dev/null > to the current weblogic logfiles .. Because; your log size may be increased recently.. pasteConfig creates a CLONINGCLIENT***/packed_template.jar firstly , and it unpacks it .. When this file becomes bigger, the risk to get outOfmemoryError increases.
You will see that when you delete the logs, this file will be smaller and you wont get outOfmemory error.. So dont save up big weblogic files in any of the Managed Server directories. Backup them then delete them if you need to store them..
4)export TIMEDPROCESS_TIMEOUT=-1 ->to disable the timeout. Just in case ,as we dont want a timeout.
5)restart the adop phase=prepare
These action will do the job( they are required actions+ some extras).. If the issue persists; feel free to comment.
Also see :
EBS 12.2 -- Multiple records shown in Bank Details page -- Patch 18517084:R12.IBY.C.
I m writing this post, as you may not find this patch information directly in your first search on Oracle Support.. (at least I could not find it in my first try :) )
Anyways ; Patch 18517084:R12.IBY.C saved my day and this patch must be applied as a post requisite after a 12.2.3 upgrade.. I think so because, the issue is obvious..
In EBS 12.2.3 , we had a problem with external bank accounts, as in Bank details Page we were seeing a lot of multiplied records..
After some analysis, it was concluded that the is was not related with data or web pages , it was actually related with the views that the corresponding web pages were using..
Fortuneatly, Patch 18517084 was there to help us, it was a patch for recreating those 3 views..
The view codes come with this Patch were free of problems.. These new view codes have fixed the issue.
Note that : This bug is fixed in 12.2.4 , but if you are in 12.2.3 you need to apply the patch 18517084.18517084 - SUPPLIERS BANK DETAIL PAGE: MANY DUPLICATE BANK RECORDS ARE SHOWN AFTER UPGRADE
Anyways ; Patch 18517084:R12.IBY.C saved my day and this patch must be applied as a post requisite after a 12.2.3 upgrade.. I think so because, the issue is obvious..
In EBS 12.2.3 , we had a problem with external bank accounts, as in Bank details Page we were seeing a lot of multiplied records..
After some analysis, it was concluded that the is was not related with data or web pages , it was actually related with the views that the corresponding web pages were using..
Fortuneatly, Patch 18517084 was there to help us, it was a patch for recreating those 3 views..
The view codes come with this Patch were free of problems.. These new view codes have fixed the issue.
Note that : This bug is fixed in 12.2.4 , but if you are in 12.2.3 you need to apply the patch 18517084.18517084 - SUPPLIERS BANK DETAIL PAGE: MANY DUPLICATE BANK RECORDS ARE SHOWN AFTER UPGRADE
EBS 12.2 -- Publish FSG report completed/warning -- oracle.apps.xdo.XDOException: No corresponding LOB data found
You may encounter errors when running Publish FSG reports in R12.2 .. Especially for languages different than US.
In the logfile of the concurrent request you may see the following;
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException:
oracle.apps.xdo.XDOException: No corresponding LOB data found :SELECT L.FILE_DATA FILE_DATA,DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH, L.LANGUAGE LANGUAGE, L.TERRITORY TERRITORY, B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY DEFAULT_TERRITORY,B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.START_DATE START_DATE, B.END_DATE END_DATE, B.TEMPLATE_STATUS TEMPLATE_STATUS, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME, B.DATA_SOURCE_CODE DATA_SOURCE_CODE, L.LOB_TYPE LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE L.APPLICATION_SHORT_NAME= :1 AND L.LOB_CODE = :2 AND L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND L.LOB_CODE = B.TEMPLATE_CODE AND (L.LOB_TYPE = 'TEMPLATE' OR L.LOB_TYPE = 'MLS_TEMPLATE') AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND L.TERRITORY= B.DEFAULT_TERRITORY ))
+---------------------------------------+
Cause:
In the logfile of the concurrent request you may see the following;
oracle.apps.fnd.cp.opp.PostProcessorException: oracle.apps.fnd.cp.opp.PostProcessorException:
oracle.apps.xdo.XDOException: No corresponding LOB data found :SELECT L.FILE_DATA FILE_DATA,DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH, L.LANGUAGE LANGUAGE, L.TERRITORY TERRITORY, B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY DEFAULT_TERRITORY,B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.START_DATE START_DATE, B.END_DATE END_DATE, B.TEMPLATE_STATUS TEMPLATE_STATUS, B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME, B.DATA_SOURCE_CODE DATA_SOURCE_CODE, L.LOB_TYPE LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE L.APPLICATION_SHORT_NAME= :1 AND L.LOB_CODE = :2 AND L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND L.LOB_CODE = B.TEMPLATE_CODE AND (L.LOB_TYPE = 'TEMPLATE' OR L.LOB_TYPE = 'MLS_TEMPLATE') AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND L.TERRITORY= B.DEFAULT_TERRITORY ))
+---------------------------------------+
Cause:
The report template is not present in the system, or the template is present but it is not configured for the FSG report use. Another possibility is that there may be a logical problem in the tables which have the template - report definitions.
Solution:
What you need to do is ;
1. Navigate to XML Publisher Administrator > Templates.
2. Query for Template NAME_OF_THE_TEMPLATE
3. Click on download icon and download the template.
4. Update the template and load it again with a new name and click 'Apply' button.
Then retry the FSG report..
If you have no template defined for your language here; download the one which is there for US , and update it for your language too.. This will at least prevent the report to completed in warning..Note that: If this is a standard report and if you cant find its template for your language; then open an SR and request the template.
In my case the template was FSGXML basic template. It was normal for FSG report to complete with warnings because there was no template associated with Turkish language for the Report. In this case; the solution was to download the US template and upload it back again both for US and TR. This solved the problem..
Solution:
What you need to do is ;
1. Navigate to XML Publisher Administrator > Templates.
2. Query for Template NAME_OF_THE_TEMPLATE
3. Click on download icon and download the template.
4. Update the template and load it again with a new name and click 'Apply' button.
Then retry the FSG report..
If you have no template defined for your language here; download the one which is there for US , and update it for your language too.. This will at least prevent the report to completed in warning..Note that: If this is a standard report and if you cant find its template for your language; then open an SR and request the template.
In my case the template was FSGXML basic template. It was normal for FSG report to complete with warnings because there was no template associated with Turkish language for the Report. In this case; the solution was to download the US template and upload it back again both for US and TR. This solved the problem..
Friday, December 12, 2014
EBS 12.2 -- GATHER_SCHEMA_STATS , ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt
This may be seen in a Fresh EBS 12.2.* system, while gathering the statistics using "Gather Schema Statistics" concurrent program.The cause of this issue is the duplicate rows in FND_HISTOGRAM_COLS. This makes the FND_STATS building a wrong command for gathering the histogram information.. A wrong command like below;
dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COLUMN_NAME SIZE COLUMN_SIZE FOR
COLUMNS COLUMN_NAME SIZE COLUMN_SIZE');
This issue is completely addressed in Oracle Doc: 11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)
The issue is said to be present for 11g databases, as a command like the one above will work on 9i and 10G databases.
Okay..
I can say that it is applicable for EBS 12.2 too..
Because we encounter the same errors, and we use 11gR2 bundled with EBS.
For the solution, you may follow the document mentioned above or you can use the following dynamic sql to build the delete commands for you..
Use the dynamic sql below to prepare the delete commands you need.. Then execute them.. Dont forget to commit..
select 'delete from FND_HISTOGRAM_COLS where table_name='''||table_name||''' and column_name='''|| column_name||''' and rownum=1;' as "FIRST execute this",
'delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='''||table_name||'''
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
)' as "then execute this"
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1
dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COLUMN_NAME SIZE COLUMN_SIZE FOR
COLUMNS COLUMN_NAME SIZE COLUMN_SIZE');
The issue is said to be present for 11g databases, as a command like the one above will work on 9i and 10G databases.
Okay..
I can say that it is applicable for EBS 12.2 too..
Because we encounter the same errors, and we use 11gR2 bundled with EBS.
For the solution, you may follow the document mentioned above or you can use the following dynamic sql to build the delete commands for you..
Use the dynamic sql below to prepare the delete commands you need.. Then execute them.. Dont forget to commit..
select 'delete from FND_HISTOGRAM_COLS where table_name='''||table_name||''' and column_name='''|| column_name||''' and rownum=1;' as "FIRST execute this",
'delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='''||table_name||'''
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
)' as "then execute this"
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1
Once you delete the duplicate and obsoleted rows in FND_HISTOGRAM_COLS tables, retry Gathering Statistics using Gather Schema Statistics concurrent program..
Thursday, December 11, 2014
Linux -- XARGS --some programs accept their arguments from STDIN and some of them accept as command line arguments
When you try to use pipe to supply a file name(as an argument) to a command such as "touch", you will receive an error like in the following example:
If we echo a name and use a unnamed pipe (|) to supply the name of the file to touch ; we will receieve a missing file operand error.
echo erm | touch
If we echo a name and use a unnamed pipe (|) to supply the name of the file to touch ; we will receieve a missing file operand error.
echo erm | touch
touch: missing file operand
Try `touch --help' for more information.
When we use an unnamed pipe for doing this kind of work; we acutally connect the stdout of echo command, to the stding of touch command. So, this error is acutally normal, because touch program does not accept its arguments to be passed as stdin..
Try `touch --help' for more information.
When we use an unnamed pipe for doing this kind of work; we acutally connect the stdout of echo command, to the stding of touch command. So, this error is acutally normal, because touch program does not accept its arguments to be passed as stdin..
On the other hand; if we use an unnamed pipe to count the processes running in the system; like the example below;
ps -ef | wc -l
422
We wont encounter errors, as wc command can receive its inputs from stdin..
So far so good..
Well, but what if we want to supply the arguments to a command that accepts its inputs as command line arguments (like touch command in the example above) ?
We can accomplish this task using xargs command/program in conjunction with a pipe.
"xargs" can build and execute command lines from standard input
For example: to supply an argument from echo command to touch command; we can use;
echo erm3 | xargs touch
Above command will create a file named erm3 in the current working directory of our process.
Lastly, I will give an example to supply an output of Oracle 's Sqlplus program as an input to a touch command ..
We first create a named pipe->
mknode /tmp/erman.pipe p
ls -al /tmp/oracle.pipe
prw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 /tmp/erman.pipe
Then we connect to sqlplus and make it spool to our erman.pipe , while directing what's written to oracle.pipe to touch command as arguments using xargs...
[appldev@ermandev erman]$ sqlplus apps/apps
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Dec 11 11:01:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set escape on;
SQL> host nohup xargs touch < /tmp/erman.pipe \&
SQL> spool /tmp/oracle.pipe
SQL> nohup: appending output to `nohup.out'
SQL> select * from dual;
D
-
X
SQL> spool off
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
So , we chck our current working directory , and see that touch command have created the files with the names of the strings that sqlplus produced as an output.
[appldev@ermandev erman]$ ls -lrt
total 0
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 X
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 SQL>
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 spool
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 select
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 off
-rw------- 1 appldev appldev 0 Dec 11 11:02 nohup.out
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 from
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 dual;
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 D
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 *
We first create a named pipe->
mknode /tmp/erman.pipe p
ls -al /tmp/oracle.pipe
prw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 /tmp/erman.pipe
Then we connect to sqlplus and make it spool to our erman.pipe , while directing what's written to oracle.pipe to touch command as arguments using xargs...
[appldev@ermandev erman]$ sqlplus apps/apps
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Dec 11 11:01:36 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set escape on;
SQL> host nohup xargs touch < /tmp/erman.pipe \&
SQL> spool /tmp/oracle.pipe
SQL> nohup: appending output to `nohup.out'
SQL> select * from dual;
D
-
X
SQL> spool off
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
So , we chck our current working directory , and see that touch command have created the files with the names of the strings that sqlplus produced as an output.
[appldev@ermandev erman]$ ls -lrt
total 0
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 X
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 SQL>
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 spool
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 select
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 off
-rw------- 1 appldev appldev 0 Dec 11 11:02 nohup.out
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 from
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 dual;
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 D
-rw-rw-r-- 1 appldev appldev 0 Dec 11 11:02 *
This was just a demonstration, this kind of things can be used to do more complex and useful works.
Consider, deleting some files which reside in the filesystem but deciding which file to delete by querying an Oracle Table.
Consider EBS.. Consider purging Concurrent Request log & out files...
Consider, deleting some files which reside in the filesystem but deciding which file to delete by querying an Oracle Table.
Consider EBS.. Consider purging Concurrent Request log & out files...
EBS 12.2 - A roadmap to use Oracle Database 12c in conjuction with EBS 12.2
Oracle EBS 12.2.4 has certifications with Oracle Database 12c.. At the moment, the certification is for Oracle Databsae 12.1.0.1.0.
So, if we want to build a fresh high-end EBS system with a latest APPS version and with a latest supported Oracle Database , the roadmap is as follows;
We Install 12.2.0
Oracle Database 1.2.1.0.2.0 is certified with only EBS 12.1.3 currently.
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1)
We upgrade 12.2.0 to 12.2.4
Oracle E-Business Suite Release 12.2.4 Readme (Doc ID 1617458.1)
We ugrade and start using Oracle Database 12c with EBS 12.2.4
Interoperability Notes Oracle EBS 12.2 with Oracle Database 12c Release 1 (Doc ID 1926201.1)
Sunday, December 7, 2014
RAC-- 11.2.0.3 RAC Installation on 2 node Linux 6.5 64 bit, a comprehensive installation from Storage to RAC
In this post, I will try to explain 11.2.0.3 RAC Installation on an environment consisting of 2 Linux nodes.
Oracle Grid and RDBMS versions mentioned in this document are 11.2.0.3 64 bit.
Lets take a quick look of the system components used in this paper;
OS: Oracle Linux 6.5 Enterprise Edition 64bit ( booted with UEK) are used for the OS layer.
Machine: Server are HP, There are 4 Local Disks on each node. These disks are used for the OS installation (root). The raid configuration used for these 4 local disks is Raid 1+0
Storage : used in the environment is Netapp. Storage connects to the servers through fiber(FCP) from 4 different paths.
A separete lun created for each node. These luns are mapped to the nodes for storing the Grid and Rdbms softwares.
6 luns for ASM are mapped on each node. These luns are used for ASM DATA and FRA Diskgroups.
Network: Both virtual and private networks are redundant.(ethernet devices and network switches are redundant.)
As can be understood from the title, we do a RAC installation but we do all the work from the raid configuration to OS installation, from the multipath the ASM disk creation and so on..
So; I will explain the installation process screen by screen (not step by step :)) in the following paragraphs ..
For now, lets take a look at the installation steps;
1) Raid configuration of Local Disks
2) Mounting OS Installation ISO from the ILOM and installing Oracle Linux 6.5 64 bit to our rac nodes.
--Note: Firmware bug error displayed in first boot may be ignored.The functionality and performance of the operating system and the server are not affected.
3) Disabling selinux , iptables and ip6tables once our OS Installation is finished.
4) Configuring public and private interconnect as bond devices.(active-backup mode)
5) Configuring sysctl and limits.conf on our rac nodes. (Note that these configurations come configured by default if you install preinstall rpm during the OS installation)
6) Checking the rpms for Oracle installation , and installing the missing ones.
7) Resetting oracle OS user's password and adding the needed groups and users.
8) Preparing the hosts files.
9) Syncing the dates initially.Configuring the ntp daemons.
10) Installing telnet on each node.(optional)
11) Checking the /dev/shm size on each node and increasing it if needed.(consider memtarget..)
Testing of the Oracle Unbreakable Enterprise Kernel includes both the operating system and the full Oracle application stack running on top of it, verifying stability and correct behavior in all situations. The test suite includes installation, functional, stress and destructive tests, which are executed under varying workloads (e.g. I/O or CPU intensive), with various database sizes. Several parameters are varied during the full run of the test suites, which also include crash scenarios in Oracle Real Application Cluster configurations.
Oracle Grid and RDBMS versions mentioned in this document are 11.2.0.3 64 bit.
Lets take a quick look of the system components used in this paper;
OS: Oracle Linux 6.5 Enterprise Edition 64bit ( booted with UEK) are used for the OS layer.
Machine: Server are HP, There are 4 Local Disks on each node. These disks are used for the OS installation (root). The raid configuration used for these 4 local disks is Raid 1+0
Storage : used in the environment is Netapp. Storage connects to the servers through fiber(FCP) from 4 different paths.
A separete lun created for each node. These luns are mapped to the nodes for storing the Grid and Rdbms softwares.
6 luns for ASM are mapped on each node. These luns are used for ASM DATA and FRA Diskgroups.
Network: Both virtual and private networks are redundant.(ethernet devices and network switches are redundant.)
As can be understood from the title, we do a RAC installation but we do all the work from the raid configuration to OS installation, from the multipath the ASM disk creation and so on..
So; I will explain the installation process screen by screen (not step by step :)) in the following paragraphs ..
For now, lets take a look at the installation steps;
1) Raid configuration of Local Disks
2) Mounting OS Installation ISO from the ILOM and installing Oracle Linux 6.5 64 bit to our rac nodes.
--Note: Firmware bug error displayed in first boot may be ignored.The functionality and performance of the operating system and the server are not affected.
3) Disabling selinux , iptables and ip6tables once our OS Installation is finished.
4) Configuring public and private interconnect as bond devices.(active-backup mode)
5) Configuring sysctl and limits.conf on our rac nodes. (Note that these configurations come configured by default if you install preinstall rpm during the OS installation)
6) Checking the rpms for Oracle installation , and installing the missing ones.
7) Resetting oracle OS user's password and adding the needed groups and users.
8) Preparing the hosts files.
9) Syncing the dates initially.Configuring the ntp daemons.
10) Installing telnet on each node.(optional)
11) Checking the /dev/shm size on each node and increasing it if needed.(consider memtarget..)
12) Checking the update level of oracleasm on each node., increasing it if necessary.13) Checking oracleasm kernel driver.. (it comes built in with the Oracle Linux 6 installation)
14) Checking the oracleasm-support ..(If you choose it ; it can be installed during the Oracle Linux 6 installation)
15).Installation of oracleasmlib.
16) Uploading the installation files to node 1, and unzipping them.
17) Installation of libHBAAPI rpm (prereq for host utils) on each node. (rpm can be found in the installation media)
18) Installation of Netapp host utils on each node.
19) After the luns are mapped, configuring the multipaths on each node.(adding some disks to blacklist if needed)
20) Aligning the SAN disks planned to be used for the storing Grid and Rdbms software on each node.. Formatting them ext3/or ext4 and mounting them on each node.. Also adding the needed lines to te fstab file on each node.
21) Aligning the SAN disk planned to be used for ASM
22) Configuring dns on each node.(resolv.conf) (Dns admin should add the public, vip and scan ip addresses in to the customer's dns server. We should be able to resolve them before the installation.)23) Creating the installation/oracle directories(optional)
24) Configuring the .bash_profile of the root user to make the root user be able to execute crs command(optional)
25) Initializing oracleasm on each node.
14) Checking the oracleasm-support ..(If you choose it ; it can be installed during the Oracle Linux 6 installation)
15).Installation of oracleasmlib.
16) Uploading the installation files to node 1, and unzipping them.
17) Installation of libHBAAPI rpm (prereq for host utils) on each node. (rpm can be found in the installation media)
18) Installation of Netapp host utils on each node.
19) After the luns are mapped, configuring the multipaths on each node.(adding some disks to blacklist if needed)
20) Aligning the SAN disks planned to be used for the storing Grid and Rdbms software on each node.. Formatting them ext3/or ext4 and mounting them on each node.. Also adding the needed lines to te fstab file on each node.
21) Aligning the SAN disk planned to be used for ASM
22) Configuring dns on each node.(resolv.conf) (Dns admin should add the public, vip and scan ip addresses in to the customer's dns server. We should be able to resolve them before the installation.)23) Creating the installation/oracle directories(optional)
24) Configuring the .bash_profile of the root user to make the root user be able to execute crs command(optional)
25) Initializing oracleasm on each node.
26) Creating oracle ASM disks from node 1.
27) Installing Grid Infrastructure using oracle Os user in node 1.. (executing root.sh scripts when requested by the installer)
28) Adding the Diskgroup for FRA using asmca
29) Installing Rdbms software using oracle OS user. (executing root.sh scripts when requested by the installer)
Before going into the installation screenshots, lets identify the technologies implemented during this work;
Ilom:
Actual name of the technology is ILO, which server management technology that comes embedded with the HP servers. By using ILOM, we connect to the servers console using ILOM's own ethernet port and do the management using our browser (without a need for going in to the System Room)
ILOM can be found most Proliant servers of the 300 and above series.
So, we do our Linux 6.5 installation remotely using ILOM.
These raid configuration can also be called as "stripe of mirrors”. It requires minimum of 4 disks.
27) Installing Grid Infrastructure using oracle Os user in node 1.. (executing root.sh scripts when requested by the installer)
28) Adding the Diskgroup for FRA using asmca
29) Installing Rdbms software using oracle OS user. (executing root.sh scripts when requested by the installer)
Before going into the installation screenshots, lets identify the technologies implemented during this work;
Ilom:
Actual name of the technology is ILO, which server management technology that comes embedded with the HP servers. By using ILOM, we connect to the servers console using ILOM's own ethernet port and do the management using our browser (without a need for going in to the System Room)
ILOM can be found most Proliant servers of the 300 and above series.
So, we do our Linux 6.5 installation remotely using ILOM.
Raid Configuration 1+0:
The idea in this configuration is "data in the same groups are mirrored and data accross the groups are striped." It is something like displayed in the following picture.
So for 4 disks -> we can say that:
we mirror the data in Disk 1 and Disk 2, also we mirror the data in Disk 3 and Disk 4. Then we concatenate disk two disk groups together in a single structure. This single structure is named as Raid 1+0.
The fault tolerancy is high..
Given 4 disks; , even if 2 disks fails (one in each group), the RAID 10 is still functional.
So we build a Raid 1+0 group on each node to place our OS files.
Oracle Linux 6.5 :
Oracle Linux is an open-source operating system available under the GNU General Public License (GPLv2). Suitable for general purpose or Oracle workload.
With Oracle Linux 6.5, the latest Unbreakable Enterprise Kernel Release 3 is installed as the default kernel, enabling customers and partners to test, validate and certify against the most up-to-date Oracle Linux distribution.
So we build a Raid 1+0 group on each node to place our OS files.
With Oracle Linux 6.5, the latest Unbreakable Enterprise Kernel Release 3 is installed as the default kernel, enabling customers and partners to test, validate and certify against the most up-to-date Oracle Linux distribution.
So, we install Oracle Linux 6.5 64 bit to our Rac servers.
Uek 3:
The Unbreakable Enterprise Kernel Release 3 is Oracle's third major release of its heavily tested and optimized operating system kernel for Oracle Linux 6, x86_64 platforms..
The Unbreakable Enterprise Kernel Release 3 is based on the mainline Linux kernel version 3.8.13 and boasts a wide range of new features and improvements relevant for enterprise workloads.
So , we choose to install Oracle Linux 6 and boot it with Uek 3.. It is a verified against all of the application stack and Rac ..
UPDATE: Internal testing suggests that there is no problem running "sqlplus" with SELinux in "enforcing" mode on RHEL6/OL6. The problem only affects RHEL5/OL5.
It is a firewall on Linux. It is used to set up, maintain, and inspect the tables of IP packet filter rules in the Linux kernel.
We disable it before the installation for making a clean installation.
Later, it may be configured if needed. But if we need to configure it, we should configure it with the rules which do not affect the cluster interconnect interfaces, protocols and network address space.
IPv6 version of Iptables. It is a firewall.Ip6tables is used to set up, maintain, and inspect the tables of IPv6 packet filter rules in the Linux kernel.We just disable it before the installation. We dont use IPV6 anyways.
The devices in /dev/mapper are created early in the boot process. Use these devices to access the multipathed devices, for example when creating logical volumes , or creating ASM diskgroups in our case..
The devices in /dev/mpath are provided as a convenience so that all multipathed devices can be seen in one directory. These devices are created by the udev device manager and may not be available on startup when the system needs to access them. We dont use these devices for creating logical volumes or filesystems.
/dev/dm-n are for internal use only and should never be used. These dm-n are created by some udev rules and they may be changed according to the sequence of device activation. These dm-n device names seems like kernel names for corresponding devices. Maybe they are there for protecting the standards, as their root directory is /dev..
Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations.
So far so good...
At this point ,we know what we are dealing with.. We have the idea about the components which need to be configured/implemented/used in the pipeline and we have the method for installing our RAC environment as well.
Lets proceed with installation screens by briefly explaining them..
We start with Raid configuration.. We configure Raid 1+0 for our 4xlocal disks on our HP nodes.
Click here to read more/ for the installation screenshots
Selinux:
Selinux provides a fine grained level of access control in Linux Operating System.
In our installation, we 'll disable selinux ,because it may block the network traffic as follows;
PRVF-7617 : Node connectivity between "racnode01 : 10.0.0.1" and "racnode02 : 10.0.0.2" failed
Result: TCP connectivity check failed for subnet "10.0.0.0"
We dont want to face these kind of security error, and that's why disable selinux before the installation.
On the other hand; it seems running Selinux in enforcing mode on OL6 seems okay(see below), but as I said; I prefer to disable it unless it is really needed.
By default, RHEL 6 x86_64 Linux is installed with SELinux as "enforcing". This is fine for the 11gR2 installation process. However, to subsequently run "sqlplus", switch SELinux to the "Permissive" mode. See NOTE 454196.1 "./sqlplus: error on libnnz11.so: cannot restore segment prot after reloc" for more details.
UPDATE: Internal testing suggests that there is no problem running "sqlplus" with SELinux in "enforcing" mode on RHEL6/OL6. The problem only affects RHEL5/OL5.
Iptables:
We disable it before the installation for making a clean installation.
Later, it may be configured if needed. But if we need to configure it, we should configure it with the rules which do not affect the cluster interconnect interfaces, protocols and network address space.
Ip6tables:
Bond devices/bonding:
/dev/shm:
Recent 2.6 Linux kernel builds have started to offer /dev/shm as shared memory in the form of a ramdisk, more specifically as a world-writable directory that is stored in memory with a defined limit in /etc/default/tmpfs. /dev/shm support is completely optional within the kernel config file. It is included by default in both Fedora and Ubuntu distributions, where it is most extensively used by the Pulseaudio application.Oracle uses /dev/shm to pass segments from SGA(shared memory) to PGA (process memory).. I think it just gives back the shared memory back to OS ,and OS gives it to the processes in case they needed. Giving back the unused memory should be enough for managing the PGA,as is not a limit and it s is not a fixed memory area resides in memory..So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
Recent 2.6 Linux kernel builds have started to offer /dev/shm as shared memory in the form of a ramdisk, more specifically as a world-writable directory that is stored in memory with a defined limit in /etc/default/tmpfs. /dev/shm support is completely optional within the kernel config file. It is included by default in both Fedora and Ubuntu distributions, where it is most extensively used by the Pulseaudio application.Oracle uses /dev/shm to pass segments from SGA(shared memory) to PGA (process memory).. I think it just gives back the shared memory back to OS ,and OS gives it to the processes in case they needed. Giving back the unused memory should be enough for managing the PGA,as is not a limit and it s is not a fixed memory area resides in memory..So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
As we deal with 11gR2 Rac , we configure this /dev/shm according to desired our memory_target value.
So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
So far so good.. On the other hand, 10g Oracle does not use memory_target , naturally it does not use a /dev/shm based memory access..
sysctl.conf:
sysctl is an interface that allows you configure some aspects of a running Linux kernel. We canfigure the needed settings for Oracle(like shared memory size, semaphore counts etc..) using /etc/sysctl.conf , and activate this change using sysctl -p command.
sysctl is an interface that allows you configure some aspects of a running Linux kernel. We canfigure the needed settings for Oracle(like shared memory size, semaphore counts etc..) using /etc/sysctl.conf , and activate this change using sysctl -p command.
limits.conf:
The pam_limits.so module applies ulimit limits, nice priority and number of simultaneous login sessions limit to user login sessions. Using /etc/security/limits.conf file we configure the limits(such as number of open files, process count etc..) for our oracle user.
The pam_limits.so module applies ulimit limits, nice priority and number of simultaneous login sessions limit to user login sessions. Using /etc/security/limits.conf file we configure the limits(such as number of open files, process count etc..) for our oracle user.
oracleasm kernel driver:
oracleasm is a Linux kernel driver also known as the Oracle ASMLib kernel driver.
This is an open-source (GPL) kernel driver and is available from Oracle as source and binary
RPMs. Note that although this driver is provided under an open source license, it has not been accepted into the mainline Linux kernel.
oracleasm is a Linux kernel driver also known as the Oracle ASMLib kernel driver.
This is an open-source (GPL) kernel driver and is available from Oracle as source and binary
RPMs. Note that although this driver is provided under an open source license, it has not been accepted into the mainline Linux kernel.
oracleasm kernel driver is included in Oracle Linux 6.
oracleasmlib:
ASMLib is an optional support library for the Automatic Storage Management feature of the Oracle Database. Automatic Storage Management (ASM) simplifies database administration and greatly reduces kernel resource usage (e.g. the number of open file descriptors). It eliminates the need for the DBA to directly manage potentially thousands of Oracle database files, requiring only the management of groups of disks allocated to the Oracle Database. ASMLib allows an Oracle Database using ASM more efficient and capable access to the disk groups it is using.
ASMLib is an optional support library for the Automatic Storage Management feature of the Oracle Database. Automatic Storage Management (ASM) simplifies database administration and greatly reduces kernel resource usage (e.g. the number of open file descriptors). It eliminates the need for the DBA to directly manage potentially thousands of Oracle database files, requiring only the management of groups of disks allocated to the Oracle Database. ASMLib allows an Oracle Database using ASM more efficient and capable access to the disk groups it is using.
multipath/device mapper/dm-n devices:
Using multipath (Device mapper multipathing) we can access a device from multiple I/O paths. When I say I/O paths, I mean accessing the same disk/IO resource from different ways .
For example : Suppose we have 2 hba in our host and we connected these hbas to 2 SAN controller which accessing one single storage disk. Then we can say that have 2 ways to access that particular disk.
Way1 ) HBA1 -> Storage Controller 1 > Disk(shelf)
Way2 ) HBA1 -> Storage Controller 2 > Disk(shelf)
These paths can be used in active-active for increasing the performance or they can be used as active-passive for providing failover.
Also, we can even increase these paths by putting an SAN switch in front of the Storage Controllers and etc..
So , multipath appears in this stage. Once configured, we see underlying devices as a single multipath device, and make the IO using it. So multipath provides a single device on top of the underlying devices.
For example: In the example above; Linux will see the same disk 2 times. Lets say /dev/sdb and /dev/sdc..
Using multipath (Device mapper multipathing) we can access a device from multiple I/O paths. When I say I/O paths, I mean accessing the same disk/IO resource from different ways .
For example : Suppose we have 2 hba in our host and we connected these hbas to 2 SAN controller which accessing one single storage disk. Then we can say that have 2 ways to access that particular disk.
Way1 ) HBA1 -> Storage Controller 1 > Disk(shelf)
Way2 ) HBA1 -> Storage Controller 2 > Disk(shelf)
These paths can be used in active-active for increasing the performance or they can be used as active-passive for providing failover.
Also, we can even increase these paths by putting an SAN switch in front of the Storage Controllers and etc..
So , multipath appears in this stage. Once configured, we see underlying devices as a single multipath device, and make the IO using it. So multipath provides a single device on top of the underlying devices.
For example: In the example above; Linux will see the same disk 2 times. Lets say /dev/sdb and /dev/sdc..
These devices are actually representing the same thing.
Multipath creates a new device such as /dev/mapper/mpathn, /dev/mpath/mpathn, and /dev/dm-n for representing these devices as a single device and reroutes the IO to the underlying devices..
The devices in /dev/mpath are provided as a convenience so that all multipathed devices can be seen in one directory. These devices are created by the udev device manager and may not be available on startup when the system needs to access them. We dont use these devices for creating logical volumes or filesystems.
/dev/dm-n are for internal use only and should never be used. These dm-n are created by some udev rules and they may be changed according to the sequence of device activation. These dm-n device names seems like kernel names for corresponding devices. Maybe they are there for protecting the standards, as their root directory is /dev..
Anyways we dont use these dm-n devices, we use /dev/mapper devices instead.
/etc/multipath.conf is the configuration file of multipath.Supported devices can be found in the multipath.conf.defaults file. If your storage array supports DM-Multipath and is not configured by default in this file, you may need to add them to the multipath.conf.
Multipath consists of a kernel module(dm-multipath), a command line program(multipath), a daemon(multipathd) and another command line program (kpartx)
Okay lets introduce these components;
So, if we want to have a fault tolerant IO path and if we have the multi physical paths, then device mapper is what we need to implement.
In this RAC installation, we configured and used multipath devices for both ASM and ext4 drives.
libHBAAPI:
The libhbaapi library is the Host Bus Adapter (HBA) API library for Fibre
Channel and Storage Area Network (SAN) resources. It contains a unified API
that programmers can use to access, query, observe and modify SAN and Fibre
Channel services.
The libhbaapi library is the Host Bus Adapter (HBA) API library for Fibre
Channel and Storage Area Network (SAN) resources. It contains a unified API
that programmers can use to access, query, observe and modify SAN and Fibre
Channel services.
We install it , because it is a prereq for Netapp Host Utils package.
Netapp Host utils:
The Host Utilities include a set of software programs to help you use your Linux host with storage systems.
The Host Utilities include a set of software programs to help you use your Linux host with storage systems.
We use this utils to manage our San luns, see their status and more.
For example "sanlun" command comes with NetApp host utils.
Disk alignment:
Host file system need to be aligned with a Storage Lun, in order to achieve best performance.
This can be accomplished specifying the correct starting sector or logical block address (LBA) for the partition, and filesystem should be configured to use the same or multiple of the Storage block size..
On Linux , you can configure a partition and filesystem using fdisk (x option)
Alignment is important, and should be considered for Storage IO performance.Otherwise, you can end up with the following;
It is almost equal -> doing 2 Storage IO for 1 OS IO..
Host file system need to be aligned with a Storage Lun, in order to achieve best performance.
This can be accomplished specifying the correct starting sector or logical block address (LBA) for the partition, and filesystem should be configured to use the same or multiple of the Storage block size..
On Linux , you can configure a partition and filesystem using fdisk (x option)
Alignment is important, and should be considered for Storage IO performance.Otherwise, you can end up with the following;
It is almost equal -> doing 2 Storage IO for 1 OS IO..
Oracle Grid:
The Oracle Grid Infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities. If you plan to use Oracle Restart or Oracle Automatic Storage Management (Oracle ASM), you must install Oracle Grid Infrastructure before installing your database. Oracle Grid Infrastructure for a standalone server is the software that includes Oracle Restart and Oracle ASM. Oracle combined the two infrastructure products into a single set of binaries that is installed as the Oracle Grid Infrastructure home. Oracle Grid Infrastructure should be installed before installing Oracle Database 11g Release 2.Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations.
Oracle ASM also supports a general purpose file system for your application needs including Oracle Database binaries. Oracle ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.
So, in order to use RAC and ASM we first install the Grid Infrastructure to our nodes, and install Oracle Rdbms afterwards.
Oracle RDBMS:
It is the Oracle Database. In this post ,we will see the instructions related to Oracle Database 11GR2.
It is the Oracle Database. In this post ,we will see the instructions related to Oracle Database 11GR2.
So far so good...
At this point ,we know what we are dealing with.. We have the idea about the components which need to be configured/implemented/used in the pipeline and we have the method for installing our RAC environment as well.
Lets proceed with installation screens by briefly explaining them..
We start with Raid configuration.. We configure Raid 1+0 for our 4xlocal disks on our HP nodes.
Monday, December 1, 2014
EBS 12.2 -- The Late Charges Report (XDODTEXE) java.lang.OutOfMemoryError in post processing // allocLargeObjectOrArray
Couple of days ago, a concurrent program that suppose to produce an XML output encountered an java.lang.OutOfMemoryError.
I have found it useful to write about this incident as it was an unsual OutofMemoryError, because it was actually failing in "allocLargeObjectOrArray".
When I checked the OPP log, I found the error stack as follows;
Caused by: java.lang.OutOfMemoryError: allocLargeObjectOrArray: [Ljava/lang/Object;, size 16400
at oracle.xdo.parser.v2.XMLDocument.xdkIncCurrentId(XMLDocument.java:3020)
at oracle.xdo.parser.v2.XMLNode.xdkInit(XMLNode.java:2758)
at oracle.xdo.parser.v2.XMLNode.<init>(XMLNode.java:423)
at oracle.xdo.parser.v2.CharData.<init>(CharData.java:118)
at oracle.xdo.parser.v2.XMLText.<init>(XMLText.java:168)
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:2869)
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:3000)
at oracle.xdo.parser.v2.XMLDocument.createTextNode(XMLDocument.java:735)
at oracle.xdo.parser.v2.DocumentBuilder.characters(DocumentBuilder.java:553)
at oracle.xdo.parser.v2.NonValidatingParser.reportCharacters(NonValidatingParser.java:1680)
at oracle.xdo.parser.v2.NonValidatingParser.parseText(NonValidatingParser.java:1391)
at oracle.xdo.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1268)
at oracle.xdo.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:338)
at oracle.xdo.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:285)
at oracle.xdo.parser.v2.XMLParser.parse(XMLParser.java:289)
So the failing code was trying to allocate an object or array with a size of 16400, but it was failing because of some JVM limits.
J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx3096m -XXtlasize1024k -XXlargeobjectlimit1024k
Note that : different jvm versions uses different argument names for the same thing. You will see that some arguments become obsoleted when you use a higher level jdk..
Okay.. After updating the command line java arguments for both OPP and Concurrent Program , I restarted the apps tier, and rerun the Concurrent Program..
/u01/oracle/TEST/fs2/FMW_Home/jrockit32/bin/java -DCLIENT_PROCESSID=10685 -Dhttp.proxyHost= -Dhttp.proxyPort= -Dhttp.nonProxyHosts= -Djava.awt.headless=true -server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx3096m -XXtlasize1024k -XXlargeobjectlimit1024k -Ddbcfile=/u01/oracle/TEST/fs2/inst/apps/TEST_erptest/appl/fnd/12.0.0/secure/TEST.dbc -Dcpid=17165 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/oracle/TEST/fs_ne/inst/TEST_erptest/logs/appl/conc/log/FNDOPP17165.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController
<AFJSMARG oa_var="s_afjsmarg" osd="LINUX_X86-64">-server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc</AFJSMARG>
I wanted to put OPP into maximum performance mode :)
I have found it useful to write about this incident as it was an unsual OutofMemoryError, because it was actually failing in "allocLargeObjectOrArray".
When I checked the OPP log, I found the error stack as follows;
at oracle.xdo.parser.v2.XMLDocument.xdkIncCurrentId(XMLDocument.java:3020)
at oracle.xdo.parser.v2.XMLNode.xdkInit(XMLNode.java:2758)
at oracle.xdo.parser.v2.XMLNode.<init>(XMLNode.java:423)
at oracle.xdo.parser.v2.CharData.<init>(CharData.java:118)
at oracle.xdo.parser.v2.XMLText.<init>(XMLText.java:168)
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:2869)
at oracle.xdo.parser.v2.XMLDocument.createNodeFromType(XMLDocument.java:3000)
at oracle.xdo.parser.v2.XMLDocument.createTextNode(XMLDocument.java:735)
at oracle.xdo.parser.v2.DocumentBuilder.characters(DocumentBuilder.java:553)
at oracle.xdo.parser.v2.NonValidatingParser.reportCharacters(NonValidatingParser.java:1680)
at oracle.xdo.parser.v2.NonValidatingParser.parseText(NonValidatingParser.java:1391)
at oracle.xdo.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1268)
at oracle.xdo.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:338)
at oracle.xdo.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:285)
at oracle.xdo.parser.v2.XMLParser.parse(XMLParser.java:289)
So the failing code was trying to allocate an object or array with a size of 16400, but it was failing because of some JVM limits.
Okay. this was not related with xmx, xms (heap related params) or perm size, but It was related with something else.
Anyways; I will keep it short.
XXtlasize and XXlargeobjectlimit was the cause.. Increasing xmx was also a good idea.Anyways; I will keep it short.
So, I updated the fnd_cp_services for opp as follows;
Also update the problematic concurrent program's arguments as;
-Xms2048M -Xmx3096MNote that : different jvm versions uses different argument names for the same thing. You will see that some arguments become obsoleted when you use a higher level jdk..
Note that: The JVM used in EBS 12.2 was 32 bits. Even if the EBS is 64 bit, it uses 32 bit jvm for these processes. The Java version used by OPP in this environment is 1.6.. So you may encounter errors if using the above params in 1.7. Note that -> these XX parameters are for Jrockit only.
/u01/oracle/TEST/fs2/FMW_Home/jrockit32/bin/java -version
java version "1.6.0_29"
Java(TM) SE Runtime Environment (build 1.6.0_29-b11)
Oracle JRockit(R) (build R28.2.0-79-146777-1.6.0_29-20111005-1807-linux-ia32, compiled mode)
Note that : In 12.2 by default;
/u01/oracle/TEST/fs2/FMW_Home/jrockit32/bin/java -version
java version "1.6.0_29"
Java(TM) SE Runtime Environment (build 1.6.0_29-b11)
Oracle JRockit(R) (build R28.2.0-79-146777-1.6.0_29-20111005-1807-linux-ia32, compiled mode)
Note that : In 12.2 by default;
| Application Tier Location |
JDK Version
|
|---|---|
<AS 10.1.2 Oracle Home>/jdk/bin
| 1.7.0_25 (32-bit) |
<FMW_HOME>/webtier/jdk/jre
| 1.6.0_29 (64-bit) |
<COMMON_TOP>/util/jdk32
| 1.7.0_25 (32-bit) |
<COMMON_TOP>/util/jdk64
| 1.7.0_25 (64-bit) |
| Database Tier Location |
JDK Version
|
|---|---|
<11gR2 Oracle Home>/jdk
| 1.5.0_30 (64-bit) |
<11gR2 Oracle Home>/appsutil/jre
| 1.7.0_25 (64-bit) |
Okay.. After updating the command line java arguments for both OPP and Concurrent Program , I restarted the apps tier, and rerun the Concurrent Program..
This time, It did not failed because of the outofmemory error. On the other hand, it failed while waiting the OPP to do its work.. Actually, the concurrent program encountered a timeout while waiting for OPP response..
Checked the OPP process , and saw that it was running with my params;
Note1 : there were 2 mx defined in the command line , but mx3096 was overwritting the first one , so there were no problem.
Note2 : If you dont want to see the first mx ;
Edit the contextfile; the following line and make an autoconfig.. But as I said -> no need<AFJSMARG oa_var="s_afjsmarg" osd="LINUX_X86-64">-server -Xmx384m -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc</AFJSMARG>
Then I realized that ; the output size of the program was 511 MB!
This was a huge output for OPP to process.
For the solution , I updated the timeout profile and the arguments of OPP concurrent manager(from the Forms screens) as follows ;
1)Increased the related timeouts(Concurrent:OPP Process Timeout and Concurrent:OPP Response Timeout -> 20000 almost 5 hours)
2)Increased the max thread count of OPP (oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=10)
I wanted to put OPP into maximum performance mode :)
Anyways; after these modifications, I rerun the concurrent program.
Note that : the problematic program was a standart concurrent program..(The Late Charges Report (XDODTEXE))
So, after making these changes, program did not encounter any errors.. On the other hand, it is still continuing..
This was normal, as It is a huge output for OPP to process and the server is a VM..
Later , it turned out that ; this program must have been executed by giving some arguments.. So , if an input argument would have been given to this program, it would complete in 3 seconds! :) Tested and verified that..
In conclusion, I have spent 1 day in total for this problem, but it was a pleasure to work with OPP in this kind of advanced level.
In conclusion, I have spent 1 day in total for this problem, but it was a pleasure to work with OPP in this kind of advanced level.
Subscribe to:
Posts
(
Atom
)