Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, August 31, 2007

The requested URL /oa_servlets/oracle.jsp.JspServlet was not found on this server

Today, Anand contacted me in the evening. They were facing an issue in a clone. Whenever self service interface was launched from AppsLogin page, the following error appeared in the browser:

HTTP-404 The requested URL /oa_servlets/oracle.jsp.JspServlet was not found on this server.

After various searches on metalink, I realised that OAM link was working but the Apps login page was not working. On closer scrutiny I came to know that the login page clicked was pointing to http://justanexample.com/oa_servlets/AppsLocallogin.jsp. This is not supported by Oracle. The value of this URL is provided in the xml file in the context variable s_login_page. The value of s_login_page. The format is http://justanexample.om/oa_servlets/Appslogin. After correcting the value of s_login_page variable in xml file, running autoconfig and restarting services the error disappeared. It is possible that the templates created during pre-clone stage had errors because of which the value was incorrect in the clone.

Thursday, August 30, 2007

65,536 row limit in Excel 10(Office2000) and 11(OfficeXP) increased to 1,048,576 rows in Excel 12(Office2007)

Excel 2000, Excel 2003 (XP) have a row limit of 65536 rows. If you try to import a text file with more rows, it will only import the first 65536 rows. David Gainer (GPM of the Excel team in Microsoft) in his excel 12 blog states that this limit has been increased in Excel 12:

Probably the most common question the Excel team gets from our customers is “when are you going to add more rows/more columns/more rows and more columns”. There are many different scenarios behind these requests. Some customers want to be able to analyze more data than Excel has rows, some customers want to track more daily information than Excel has columns, and other customers want to perform matrix math on large matrices of thousands of elements. There are plenty of other scenarios too. Well, the answer to the question is “in Excel 12.” Specifically, the Excel 12 grid will be 1,048,576 rows by 16,384 columns. That’s 1,500% more rows and 6,300% more columns than in Excel 2003, and for those of you that are curious, columns now end at XFD instead of IV.

This is an exciting feature for us, because it is a feature that helps a very broad range of our customers, and we are looking forward to seeing what folks create with a bigger grid.

Of course, rows and columns aren’t the only things customers have been asking for more of. Next time, I will review all of the other places where Excel 12 gives you “more”.

How can I see if a Critical Patch Update(CPU) is installed ?

I did a little research after Virag Sharma gave the query of select * from dba_registry_history as a comment in my previous post, to check CPU status. This view is available on 9.2.0.8 and 10.2.0.x database releases only. A little more digging revealed the source table:

SQL> select text from dba_views
2 where view_name='DBA_REGISTRY_HISTORY';

TEXT
--------------------------------------------------------------------------------
SELECT action_time, action, namespace, version, id, comments
FROM registry$history

Metalink Note 352783.1 says:

From CPUJan2006 onwards, for the OPatch installed CPU's it is possible to do the following query:

col action_time for a28
col action for a6
col namespace for a9
col version for a10
col id for 99999999
col comments for a10
select * from registry$history;
This select will list the installed CPU's since CPUJAN2006, if you get ora-942 CPUJan2006 has not been installed, previous ones might have been.

Example for RDBMS version 10.2.0.1:

select * from registry$history;

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- ------ --------- ---------- --------- ----------
30-JAN-06 12.31.23.139000 PM CPU SERVER 10.2.0.1.0 4751539 CPUJan2006

Alternatively if you want to avoid OPatch you can "grep" the Opatch inventory for the CPU patch numbers by use of a shell script. But since opatch is needed in 9.2 and up to install the CPU patches, it will be always present in 9.2 and up.

For 8.1.7 and 9.0.1 use of OPatch not possible, however installing a 9.0.1 or 8.1.7 CPU will
create directories and files like %ORACLE_HOME%\cpu\CPUOct2005\patch.log or $ORACLE_HOME/cpu/CPUOct2005/install.log

So by logging in as applmgr you could do

cd $IAS_ORACLE_HOME/cpu
ls -ltr

to see what CPU is applied on IAS_ORACLE_HOME.


Windows command line fanatic

I am a command line fanatic. You can understand this if unix is the OS, but for doing a lot of tasks in windows, I use command line instead of the graphical user interface. Tasks like searching for files are more easily done through command line. For example if I have to find out whether a file whose name has the letters 11g, I can easily do so through the command prompt with the command dir *11g*. Compare this to GUI. I have to first start Windows Explorer or navigate through My Computer to that directory. If there are 500 files in that directory, Windows Explorer will faithfully show all 500. Then scroll and search for all files which have 11g in their name. Pretty tedious to me. Once I find the file names, I can simply type the first few letters of the file name press tab for the full filename to show up and press . Voila, the file opens in whatever application it is attached to. .txt files open in notepad, .doc files open in MS Word and so on. Much more faster than browsing through explorer.

I agree that certain tasks can be done faster on a GUI, but these searching and launching programs part is best done through command line in my opinion.

Some other things I achieve through command line:

Prevent Spyware
Windows is prone to spyware. The best way to prevent this is to login with a non-Administrator account. However you need to certain things as an administrator, for that you have to logout and start all over again ? No. Just launch a command prompt by clicking on start | Run | type: cmd. In your command prompt type:

runas/user:domain or machine name\Admin username cmd.exe

It will prompt you for password. Enter the password, this will launch a new command window as a user with administrator privileges. Anything you launch from this windows will be launched with admin privileges. For example you can start installation of Jinitiator or the latest Java Runtime through this window.

Killing unwanted processes

When you are not logged in as a user with admin rights, you may want to kill some processes through task manager which belong to Administrator user. For achieving this. Launch command prompt and follow the steps above to launch a new windows as admin user. Type: taskmgr to launch task manager as administrator. Kill any unwanted processes easily. Other option is to use the command tasklist to see the processes on command line character mode and use the command taskkill to kill with pid. Here's a copy paste of these commands:

C:\WINDOWS\system32>tasklist

Image Name PID Session Name Session# Mem Usage
========================= ====== ================ ======== ============
System Idle Process 0 Console 0 16 K
System 4 Console 0 44 K
smss.exe 1148 Console 0 48 K
csrss.exe 1284 Console 0 2,456 K
winlogon.exe 1308 Console 0 4,228 K
services.exe 1352 Console 0 1,992 K
lsass.exe 1364 Console 0 2,452 K
ibmpmsvc.exe 1548 Console 0 48 K
ati2evxx.exe 1576 Console 0 444 K
svchost.exe 1600 Console 0 1,556 K
svchost.exe 1656 Console 0 1,520 K
svchost.exe 2040 Console 0 13,496 K
svchost.exe 284 Console 0 1,136 K
svchost.exe 644 Console 0 212 K
ccSvcHst.exe 904 Console 0 3,108 K
AppSvc32.exe 1088 Console 0 1,632 K
spoolsv.exe 1452 Console 0 568 K
AluSchedulerSvc.exe 380 Console 0 484 K
ccSvcHst.exe 420 Console 0 240 K
SMAgent.exe 528 Console 0 64 K
alg.exe 1976 Console 0 144 K
explorer.exe 3012 Console 0 12,956 K
ccApp.exe 2648 Console 0 1,512 K
SynTPLpr.exe 3928 Console 0 300 K
SynTPEnh.exe 4076 Console 0 1,344 K
ctfmon.exe 1224 Console 0 548 K
symlcsvc.exe 3620 Console 0 176 K
svchost.exe 2332 Console 0 116 K
cmd.exe 3564 Console 0 1,112 K
ctfmon.exe 2216 Console 0 1,244 K
iexplore.exe 5936 Console 0 74,008 K
AcroRd32.exe 4228 Console 0 2,660 K
Extranet.exe 5980 Console 0 1,220 K
OUTLOOK.EXE 5132 Console 0 21,416 K
Connect.exe 5896 Console 0 6,420 K
firefox.exe 2020 Console 0 38,496 K
tasklist.exe 2904 Console 0 4,436 K
wmiprvse.exe 3916 Console 0 5,644 K
taskmgr.exe 2588 Console 0 5,072 K

C:\WINDOWS\system32>taskkill /pid 2588
SUCCESS: The process with PID 2588 has been terminated.

Wednesday, August 29, 2007

New post patch for ATG_PF.H RUP5: 6145463

On August 22, 2007, Oracle updated its metalink note 375682.1 (About ATG_PF.H RUP5) :

- Added information on descriptive flexfield context synchronization to New and Changed Features section and New and Changed Components section.
- Added application of patch 6145463 in post-install step 1.

Size of patch 6145463 (SUPPORT NEGATIVE CURRENCY PROFILE VALUE '(XXX)') is only 275 KB. The readme of the patch says:

Description :
After upgrading to the new ATG RUP5 the currency amount value is appended
with "PT" when the profile value Currency: Negative Format parameter is set to
'(XXX)'.

Technical Description :
Added support for Negative Currency Format (XXX) in the java layer.

Its very typical to have additional post patches added to rollup patches due to issues discovered by early birds. However it is very difficult for customers to keep track of post patches. For example, when forms patchset 18 was released, there were 4 post patches. Now we have completely different 6 post patches. If you raise a TAR / SR with Oracle for Forms issues, they may ask you to apply those new post patches first. Patching has been the bane of Apps DBAs who are also called "Patch DBAs" How far you can analyze is an open question. I don't think anyone keeps up with post patches and we encounter them only in case we hit the bug for which they were created. It is a tall order to keep track of the forever changing readmes of ATG rollups and security patches which are mandatory for all instances.

Seven tiers of disaster recovery

Wikipedia has a great article on seven tiers of disaster recovery. They copied portions of this definition with permission from RecoverySpecialties.com.

Tier 0: No off-site data – Possibly no recovery
Tier 1: Data backup with no hot site
Tier 2: Data backup with a hot site
Tier 3: Electronic vaulting
Tier 4: Point-in-time copies
Tier 5: Transaction integrity
Tier 6: Zero or near-Zero data loss
Tier 7: Highly automated, business integrated solution

These tiers were proposed by Share Inc to help identify the various methods of recovering mission-critical computer systems as required to support business continuity.

Liebert Corporation lists 4 tiers of protection

Your Tiers of Protection

Tuesday, August 28, 2007

Know about Oracle Database 11g

Eddie Awad's blog has a good post on 40+ links for knowing the new Oracle database 11g

Jeremy's blog has a post Notes on 11g part 1

If your company is an Oracle partner, you can take several trainings on ilearning.oracle.com free of cost, which Oracle has organized for its partners and employees:

Educational, Technical, Sales & Marketing Resources Help Partners Grow Business

Monday, August 27, 2007

Relink error while installing Oracle Database 10.2.0.3 on Solaris 8

While installing 10.2.0.3 64-bit through runInstaller, I get the following errors on Solaris 8:

INFO: Linking $ORACLE_HOME/precomp/lib/proc

INFO: ld: fatal: library -lclntsh: not found

INFO: ld: fatal: file $ORACLE_HOME/lib//libncds10.a: unknown file type
ld: fatal: library -ldce: not found

I found some metalink notes for this, but they didn't solve the issue:

333348.1 Install 10gR2 on Solaris 64 bits fails with: 'library -ldce: not found'

To implement the solution, you can use the patch for the bug or use a workaround:

A) Use the patch

1. Download patch 4589082 from metalink and apply it following instruction in README file

2. Install 10.2 software

B) Use the workaround

1. Rename /usr/lib/libdce.so to other name
2. Do installation
3. Rename back the file in step 1.

References

Bug 4589082 - Error In Invoking Target 'Relink' Of Makefile, Ld: Library -Ldce: Not Found


I tried both A and B given above, but the issue remains. I also read the bug entry which says this:

PROBLEM STATEMENT:
------------------

Install of Oracle 10gR2 on Solaris fails with:

Error in invoking target 'relink' of makefile

'/dbprop7000/app/oracle/product/prop70/10.2.0

/precomp/lib/ins_precomp.mk'. See

'dbprop7000/app/oracle/oraInventory/installActions2005-

08-31_09-15-12AM.log' for details

.

Installation type perform is: EE (Enterprise Edition)

Problem is related to the fact that the installer checks for DCE:

filename = /usr/lib/libdce.so

INFO: Query Returned: true

INFO: Setting variable 'b_installDce' to 'true'.

and based on that info decides to include DCE as well, while as far as I know DCE is not supported on Solaris.

*** 11/23/05 08:53 am *** 

Uploaded the patch with the fix. This patch needs to be appiled to the

Installation Software (Not to the ORACLE_HOME).

*** 11/30/05 05:17 am ***

Customer confirmed that the patch (applied on the staging area) solved the issue


I finally figured out what was causing this. If we try to install 10.2.0.3 patchset directly without applying 10.2.0.1 first, this error occurs. You should

1. Install 10.2.0.1
2. Apply patch 4589082 to 10.2.0.3 stage
3. Apply patchset 10.2.0.3 on 10.2.0.1 home.

That should resolve it.

Friday, August 24, 2007

Unreleased ATG_PF.H RUP6 patch number is 5903765

Application Technology Group (ATG) covers these products:

510 Application Object Library (AOL),
1479 Oracle BI Publisher (formerly XML Publisher),
518 Oracle Alert,
99 Oracle Applications Manager (OAM),
166 Application Install and Upgrade,
770 Oracle Application Translation (NLS/MLS),
163 eBusiness Intelligence,
205 Oracle Balanced Scorecard,
2216 Oracle Business Intelligence Applications Foundation,
1472 Oracle Applications Framework,
325 Oracle Desktop/Web Applications Integrator,
213 Oracle Tutor,
841 Oracle iSetup,
1745 Oracle Applications Technology Stack,
757 Oracle XML gateway,
240 Oracle Self-Service Web Applications (OSSWA),
995 Mobile Application Server,
1332 Support Diagnostics,
1324 Upgrade Assistant/Maintenance Wizard,
1475 Oracle User Management.

Recently ATG_PF.H RUP5 was released. Internally, Oracle has already planned for ATG_PF.H RUP6. You'll realize this, if you go to metalink and in Quick find select All Sources and key in the search string as ATG_PF.H RUP6. I got 58 hits on this search string. Most of them are describing bugs which have fixes in ATG_PF.H RUP6. The first hit which happened to be note 454884.1, gives you the patch number for ATG_PF.H RUP6 as 5903765.

Thursday, August 23, 2007

11g background processes

My 11g instance shows the following processes:

ora_pmon_eleveng
ora_vktm_eleveng
ora_diag_eleveng
ora_dbrm_eleveng
ora_psp0_eleveng
ora_dia0_eleveng
ora_mman_eleveng
ora_dbw0_eleveng
ora_lgwr_eleveng
ora_ckpt_eleveng
ora_smon_eleveng
ora_reco_eleveng
ora_mmon_eleveng
ora_mmnl_eleveng
ora_d000_eleveng
ora_s000_eleveng
ora_smco_eleveng
ora_fbda_eleveng
ora_qmnc_eleveng
ora_q000_eleveng
ora_q001_eleveng
ora_cjq0_eleveng
ora_w000_eleveng
ora_j000_eleveng

I compared the 11g processes above with a 10g instance and found that 6 new background processes had been introduced in 11g which are:

ora_dbrm_eleveng DB resource manager
ora_dia0_eleveng Diagnosability process 0
ora_fbda_eleveng Flashback data archiver process
ora_vktm_eleveng Virtual Timekeeper
ora_w000_eleveng Space Management Co-ordination process
ora_smc0_eleveng Space Manager process

However while googling the net, I found that 56 new processes were added. I am pasting those here for reference, just in case the original link disappears:

In Oracle 10.2 there were 157 background parameters; in Oracle 11.1 there are 212 background processes

The following table shows the 56 background processess that were added in Oracle 11.1


NameDescription
ACMSAtomic Controlfile to Memory Server
DBRMResource Manager Process
DIA0Diagnosibility Process 0
DIA1Diagnosibility Process 1
DIA2Diagnosibility Process 2
DIA3Diagnosibility Process 3
DIA4Diagnosibility Process 4
DIA5Diagnosibility Process 5
DIA6Diagnosibility Process 6
DIA7Diagnosibility Process 7
DIA8Diagnosibility Process 8
DIA9Diagnosibility Process 9
DSKMslave DiSKMon process
EMNCEMON Coordinator
FBDAFlashback Data Archiver Process
FSFPData Guard Broker FSF0 Pinger
GTX0Global Transaction Process 0
GTX1Global Transaction Process 1
GTX2Global Transaction Process 2
GTX3Global Transaction Process 3
GTX4Global Transaction Process 4
GTX5Global Transaction Process 5
GTX6Global Transaction Process 6
GTX7Global Transaction Process 7
GTX8Global Transaction Process 8
GTX9Global Transaction Process 9
GTXaGlobal Transaction Process 10
GTXbGlobal Transaction Process 11
GTXcGlobal Transaction Process 12
GTXdGlobal Transaction Process 13
GTXeGlobal Transaction Process 14
GTXfGlobal Transaction Process 15
GTXgGlobal Transaction Process 16
GTXhGlobal Transaction Process 17
GTXiGlobal Transaction Process 18
GTXjGlobal Transaction Process 19
KATEKonductor of ASM Temporary Errands
MARKmark AU for resysc koordinator
OFSCOFS CSS
PINGinterconnect latency measurement
RCBGResult Cache: Background
RMS0rac management server
RSMNRemote Slave Monitor
SMC0Space Manager Process
VBG0Volume BG 0
VBG1Volume BG 1
VBG2Volume BG 2
VBG3Volume BG 3
VBG4Volume BG 4
VBG5Volume BG 5
VBG6Volume BG 6
VBG7Volume BG 7
VBG8Volume BG 8
VBG9Volume BG 9
VDBGVolume Driver BG
VKTMVirtual Keeper of TiMe process

The following table shows the one background process that was removed in Oracle 11.1


NameDescription
EMN0Event Monitor Process 0

Popular Bolt-ons to Apps

A Bolt-on is a third party application which enhances the functionality of Oracle Apps by providing facilities which are not present in native Oracle Apps. I am describing those which are known to me in this post. I'll be adding more to this list as and when I learn of new bolt-ons.

Aquire OrgPublisher: Aquire claims that its product OrgPublisher™ is the World’s Most Trusted Organizational Charting Solution. OrgPublisher enables you to eliminate manual, ad-hoc reporting, while keeping critical employee data within your HR and ERP systems current and visible. We’ll help you see what you’ve never seen before.

Imagenow : Document Management solution

Markview 170 Systems: 170 MarkView for Oracle enables tight integration with Oracle, extending the reach of the ERP system through imaging and workflow, and a seamless user interface that utilizes Oracle ERP for data entry, validation, a single data store, and system security.

Logical Apps: The Integrity HealthCheck service from LogicalApps provides an in-depth assessment of applications controls and Sarbanes-Oxley compliance.

Bottomline : provides collaborative payment and invoice automation solutions to corporations, financial institutions and banks around the world. The company’s solutions are used to streamline, automate and manage processes and transactions involving global payments, invoice approval, purchase-to-pay, collections, cash management and document process automation. Hundreds of Oracle customers across the globe trust Bottomline, an Oracle Certified Partner and a Five-Star Partner of the OAUG, to provide solutions that meet their needs for cost reduction, competitive differentiation and optimization of working capital.

Noetix : Business views for Oracle Financials, Order Management, Procurement, Projects, Discrete and Process Manufacturing, Human Resources, Grants, and Service.

Readsoft: Automate the processing of all incoming invoices, from entry to final posting in Oracle E-Business Suite with ReadSoft DOCUMENTS for Invoices and Invoice IT. The software platform, ReadSoft DOCUMENTS, captures information from any document and interprets the information. It doesn’t matter if the documents come in on paper, fax, e-mail or Web. Invoice information is then sent to INVOICE IT, which includes flow monitoring capabilities, approval workflow and EDI — all designed to save you a substantial amount of time and money every day. The software processes the information in a highly automated flow, which can be monitored and supervised. Many of our customers have been able to cut down on their invoice processing costs by as much as 50 percent. Join the 4,500+ worldwide customers who use our technology to lower cost and increase business efficiency.

I am listing only the name of the rest, and would update the description at a later date:

Core Services
Optimum Solutions
Project Partners
Applimation
C3 Business Solutions
ClearOrbit
Colibri
Data Intensity
Innowave
excel4apps
FMT Systems Inc.
Global Software Inc.
Jaros
Kaba
Kbace
Kofax
O2Works
Perficient
More4Apps
Solbbourne
Solix
Teradata
Unitask
Kewill
Sabrix
Tradesphere
Optio
Loftware
Informatica
WebMethods
Lason
Vastera

url_fw.conf The URL Firewall configuration file

The purpose of the URL Firewall is to ensure that only URLs required for the externally exposed functionality can be accessed from the internet. The file is present in $IAS_CONFIG_HOME/Apache/Apache/conf/url_fw.conf

The URL firewall is implemented as a whitelist of URLs required. Any URL request that is not matched in the whitelist is refused. It limits the exposure of Oracle Apps by reducing the attack surface available to external parties.

Integrigy recommends that all the pages which are not being accessed from internet, should be commented in the URL firewall.

On January 25, 2006, David Litchfield of NGS released information about an unfixed security bug in Oracle’s PLSQL Gateway (also referred to as mod_plsql).

This is a critical security vulnerability in mod_plsql, which is used by Oracle Applications 11i. The vulnerability allows an attacker using only a web browser and having access to an Oracle Applications 11i application server to (1) execute any SQL statement or anonymous PL/SQL block as the APPS account or (2) retrieve and view any data accessible by the APPS account.

Integrigy(www.integrigy.com) recommends the following to be done on extranet web tiers:

All access to mod_plsql can be disabled by blocking access to mod_plsql using the URL firewall. In the url_fw.conf file, comment out all references that start with /pls/. There are three groups of Rewrite rules with /pls/ -- (1) on-line help or PLS Help, (2) iReceivables, and (3) iRecruitment. On-line help will be disabled, but so will all access to mod_plsql from the Internet. After Oracle releases a patch to fix the vulnerability in mod_plsql, re-enable the on-line help.

If the iReceivables or iRecruitment modules are being used, carefully review the Rewrite rules to see if these web pages are being used in your implementation by checking the Apache log files in the production environment.

Official Oracle documentation on url_fw.conf is available in Appendix E of Metalink note 287176.1.

Oracle has also published metalink note 460564.1 which has hints and tips for troubleshooting URL firewall.

Wednesday, August 22, 2007

Script for Oracle init.ora undocumented _parameter values

SET ECHO off
REM NAME: TFSXPARA.SQL
REM USAGE:"@path/tfsxpara"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM Must be run as SYS
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Craig A. Shallahamer / Rick Minutella
REM (c)1994 Oracle Corporation
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Lists the parameter values associated with the instance running
REM the currently opened database.
REM
REM In addition to the normal output of show parameters, this script
REM reports the undocumented parameters from the X$KPPI/X$KSPPCV tables.
REM Most of these parameters are undocumented for a very good reason!
REM They are subject to change or disappear in new releases without
REM notice. Making any changes without the advice of Worldwide
REM Support is not advised and may cause performance degradation or
REM damage to your database.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM ------------------------------------------------------------------------
REM Main text of script follows:


ttitle -
center 'All Instance Parameters' skip 2

col ksppinm format a28 heading 'Parameter' justify c trunc
col ksppstvl format a40 heading 'Value' justify c trunc
col ksppstdf format a8 heading 'Default?' justify c trunc

select
a.ksppinm,
b.ksppstvl,
b.ksppstdf
from
x$ksppi a, x$ksppcv b
where a.indx = b.indx
order by a.ksppinm
/

New s_ context file variables introduced in ATG_PF.H RUP5

Following new s_ context file variables have been newly introduced in ATG_PF.H RUP5:

s_fnd_obiee_url
Support for a new profile option FND_OBIEE_URL "Oracle Business Intelligence Suite Enterprise Edition base URL Value" has been added. The profile option is populated by the Autoconfig variable s_fnd_obiee_url. The default value is null. Once OBIEE is installed customers should update this variables value to their equivalent of ://:." editable="Yes/> and rerun Autoconfig.

s_db_plsql_native_library_subdir_count
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT has now been Autoconfig-enabled to provide more flexibility. It is defined as %s_db_plsql_native_library_subdir_count% and has a default value of 149.

Information extracted from: metalink note 375682.1

Monday, August 20, 2007

Easter Egg in Oracle 7.3

Software Easter eggs are messages, graphics, sound effects, or an unusual change in program behavior that mainly occur in a software program in response to some undocumented set of commands, mouse clicks, keystrokes or other stimuli intended as a joke or to display program credits. They are often located in the "About" box of a software. An early use of the term Easter egg was to describe a message hidden in the object code of a program as a joke, intended to be found by persons disassembling or browsing the code.

I found an interesting post on AMIS technology blog by Marco Gralike. I am reproducing it partially here:

Last year our firm (AMIS) celebrated its 15th anniversary. This year Oracle celebrates its 30th year of its existence. In that reflective light, I remembered a nice "Easter egg" regarding the database software. A long time ago, I guess somewhere in 1997, 1998, I was working as a DBA consultant for a big Dutch banking cooperation. Preparing for the upcoming year 2000 in those days we tested a lot of applications and databases regarding the year 2000 problems (among others date formats regarding: YY, YYYY, RRRR…) etc.

An UNIX (AIX) system administrator of this banking cooperation accidentally did a reset to the default date/time in those days of an AIX (version 3.2?) machine (RS/6000) to 01-01-1970. I am not absolutely sure, but I think it was this date. He did this while I was starting an Oracle database (Oracle V. 7.3.x) and to my surprise I got an Oracle error from the database that said something in the lines of…"Sorry, but this date / time setting can’t be correct, because the Oracle Company didn’t exist in those days".

The other day I tested some OS / Oracle Database combinations, in the light of the anniversaries and for some fun, regarding this phenomena to see if I could reproduce the message.

I am a little bit disappointed now, because I couldn’t.

Full article on: http://technology.amis.nl/blog/?p=1880

For more easter eggs in software refer to wikipedia: http://en.wikipedia.org/wiki/Software_Easter_Egg

Enhancement request for utility/script to identify which quarterly CPU is applied

The quarterly critical patch update which is released by Oracle every January, April, July and October for E-Business Suite includes patches for all the techstack:

1. Database patches applied through opatch
2. Application Server patches applied through shell scripts
3. Developer 6i patches applied through shell scripts
4. Jinitiator security enhancements introduced through new Jinitiator versions
5. Apps patches applied through adpatch.

Only after you apply the patches for the whole technology stack, can you say that you have applied the full Critical Patch Update. There is no easy way is available to verify this. We have to query each component of the techstack manually to identify whether an instance is on the most recent CPU.

I logged an enhancement request with Oracle for the creation of a utility/script which can query an instance and identify which quarterly CPU is applied on an instance. For example the utility/script should be able to say whether April 2007 CPU or July 2007 CPU or any of the previous CPUs is applied on the instance. The bug # for this is 6324967. I hope that something is done by Oracle for this and it is released soon.

What's New in the Solaris 10 8/07 Release

While googling for something completely different, I found What's New in the Solaris 10 8/07 Release

It covers these topics:


System Administration Features
Installation Enhancements
Networking Enhancements
Security Enhancements
File System Enhancements
System Resources Enhancements
Desktop Tools
X11 Windowing Features
Language Support Enhancements
Developer Tool Enhancements
New Drivers
System Performance Enhancements
Device Management Features
Console Subsystem Features

This is meant for only those who want to leverage the new features in their environments. May be too technical for most.

Official Documentation of Oracle 11g databse new features

Here's the official documentation of Oracle 11g Database new features

Descriptions of all of the features that are new to Oracle 11g Database Release 1 (11.1) are covered in the following sections:

Which instances should apply DB timezone version 4 patches ?

This question was posed to me recently. As per Oracle, all instances which use 8i or above need to apply DB timezone patches version 4. Here's the quote from Oracle:

"It is not possible to automatically determine time zone use in a database. This is because timezone data is embedded not only in database tables, but also in arguments to user-defined procedures and functions (in both PL/SQL and Java). There is no way for Oracle to definitively tell any customer that they do NOT need to patch. "

So the answer is short and sweet. All ERP instances using RDBMS version 8i and above need to apply timezone patches version 4. Since the patch is very small, the downtime required is also small. You can piggyback this patch with your quarterly CPU or any of the weekend outages.

11.5.3 clone connects to source (Production)

Last night, I got a call from Sadiq regarding an issue with a clone of 11.5.3 instance. As you may recall, all instances pre-11.5.5 were not autoconfig enabled. That means manual cloning. For this clone, Sadiq had followed a document which had painstakingly mentioned each and every file to be manually modified. It was not the Oracle supported method of cloning where you need to do a fresh install of 11.5.3 on target and then start the cloning. After the cloning, he noticed that when you clicked Connect on ICXINDEX.htm, it would take you to the Production instance (Source instance) instead of the target instance. One more thing which was peculiar was that the Cloned instance was using the same instance name and mountpoints as production, but was on a different hostname. After a search on metalink and some logical thinking, I concluded that something was wrong with the DAD. I checked the wdbsvr.app file which was present in $APPL_TOP/iAS/Apache/modplsql/cfg directory. However everything was ok in it. Then I realized that since the instance name of source and production were same, there would be no difference in the DAD entries. The difference had to be in the tnsnames.ora entry which should refer to the the target instance instead of source. Sure enough the $APPL_TOP/iAS/network/admin/tnsnames.ora file was pointing to the source instance. After it was corrected to point to target instance, we tested again but FND_WEB.PING was still showing source insance. I asked Sadiq to stop Apache clear Apache cache present in $OA_HTML/_pages directory and restart Apache. Once that was done, FND_WEB.PING was pointing to the target instance. The problem was solved.

This may have been a well known problem in the heyday of 11.5.3 but doesn't get any ready hits on metalink now. We must remember that there all still many production instances which belong to pre-autoconfig era.

Wednesday, August 15, 2007

Oracle 11g DB startup and shutdown

$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 15 18:24:17 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1303216 bytes
Variable Size 503319888 bytes
Database Buffers 339738624 bytes
Redo Buffers 5169152 bytes
Database mounted.
Database opened.
SQL>


Default dump directory paths have changed from $ORACLE_HOME/admin/*dump to:

SQL> show parameter dump

NAME TYPE VALUE
------------------------------------ ------------------------------
background_core_dump string partial
background_dump_dest string /home/oracle/app/oracle/diag/rdbms/eleveng
/eleveng/trace
core_dump_dest string /home/oracle/app/oracle/diag/r
dbms/eleveng/eleveng/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /home/oracle/app/oracle/diag/r
dbms/eleveng/eleveng/trace

SQL>

A peek inside the alert log:

Shutdown log:

Wed Aug 15 18:24:28 2007
Stopping background process SMCO
Stopping background process FBDA
Shutting down instance: further logons disabled
Stopping background process QMNC
Wed Aug 15 18:24:30 2007
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
Shutting down instance (immediate)
License high water mark = 16
Wed Aug 15 18:24:34 2007
ORA-1089 : opidrv aborting process unknown ospid (17409_3086890688)
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Wed Aug 15 18:24:35 2007
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Aug 15 18:24:35 2007
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 8
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Aug 15 18:24:35 2007
Stopping background process VKTM:
Wed Aug 15 18:24:38 2007
Instance shutdown complete


Startup log:

Wed Aug 15 18:24:38 2007
Instance shutdown complete
Wed Aug 15 18:24:41 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /home/oracle/app/oracle/product/11.1.0/db_1/dbs/arch
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.6.0.
Using parameter settings in server-side spfile /home/oracle/app/oracle/product/11.1.0/db_1/dbs/spfileeleveng.ora
System parameters with non-default values:
processes = 150
memory_target = 812M
control_files = "/home/oracle/app/oracle/oradata/eleveng/control01.ctl"
control_files = "/home/oracle/app/oracle/oradata/eleveng/control02.ctl"
control_files = "/home/oracle/app/oracle/oradata/eleveng/control03.ctl"
db_block_size = 8192
compatible = "11.1.0.0.0"
db_recovery_file_dest = "/home/oracle/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 2G
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=elevengXDB)"
audit_file_dest = "/home/oracle/app/oracle/admin/eleveng/adump"
audit_trail = "DB"
db_name = "eleveng"
open_cursors = 300
diagnostic_dest = "/home/oracle/app/oracle"
Wed Aug 15 18:24:43 2007
PMON started with pid=2, OS id=26498
Wed Aug 15 18:24:43 2007
VKTM started with pid=3, OS id=26500
VKTM running at (100ms) precision
Wed Aug 15 18:24:43 2007
DIAG started with pid=4, OS id=26504
Wed Aug 15 18:24:43 2007
DBRM started with pid=5, OS id=26506
Wed Aug 15 18:24:43 2007
PSP0 started with pid=6, OS id=26508
Wed Aug 15 18:24:43 2007
DSKM started with pid=7, OS id=26510
Wed Aug 15 18:24:43 2007
DIA0 started with pid=8, OS id=26512
Wed Aug 15 18:24:43 2007
MMAN started with pid=7, OS id=26514
Wed Aug 15 18:24:43 2007
DBW0 started with pid=9, OS id=26516
Wed Aug 15 18:24:43 2007
LGWR started with pid=10, OS id=26518
Wed Aug 15 18:24:43 2007
CKPT started with pid=11, OS id=26520
Wed Aug 15 18:24:43 2007
SMON started with pid=12, OS id=26522
Wed Aug 15 18:24:43 2007
RECO started with pid=13, OS id=26524
Wed Aug 15 18:24:43 2007
MMON started with pid=14, OS id=26526
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Wed Aug 15 18:24:43 2007
MMNL started with pid=15, OS id=26528
starting up 1 shared server(s) ...
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /home/oracle/app/oracle
Wed Aug 15 18:24:44 2007
ALTER DATABASE MOUNT
Setting recovery target incarnation to 2
Successful mount of redo thread 1, with mount id 343942063
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Wed Aug 15 18:24:51 2007
ALTER DATABASE OPEN
Thread 1 opened at log sequence 8
Current log# 2 seq# 8 mem# 0: /home/oracle/app/oracle/oradata/eleveng/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Opening with internal Resource Manager plan
Starting background process SMCO
Wed Aug 15 18:24:52 2007
SMCO started with pid=19, OS id=26548
Starting background process FBDA
Wed Aug 15 18:24:52 2007
FBDA started with pid=20, OS id=26550
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Aug 15 18:24:53 2007
QMNC started with pid=21, OS id=26552
Wed Aug 15 18:24:56 2007
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Aug 15 18:25:07 2007
Completed: ALTER DATABASE OPEN
Wed Aug 15 18:29:48 2007
Starting background process CJQ0
Wed Aug 15 18:29:48 2007
CJQ0 started with pid=33, OS id=26826


The alert log is also being created in xml format in /home/oracle/app/oracle/diag/rdbms/eleveng/eleveng/alert/log.xml

Sizing Oracle Applications on Solaris

I found this on the net while looking for a sizing tool from Solaris for Oracle Apps. Please go through the presentation on:

http://www.oracle.com/technology/products/applications/sizing/Sizing_EBS_LongVersion.ppt

According to this presentation, you can have access to Sun's sizing tool for Oracle Apps if you are registered with them as a partner.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

If you get errors similar to this:

SQL> conn / as sysdba
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The cause of this issue is the fact that the environment varialble TWO_TASK is visible. You need to unset TWO_TASK and try sqlplus again.

ORA-12162 When Logging in Using BEQ

If you get ORA-12162 when you try to connect as sysdba:

When trying to connect locally (with BEQ or bequeath protocol) to the database shows "ORA-12162 error tns:net service name is incorrectly spelled"

This is unusual because a TNS:Net service name is not being specified, nor is the environement variable TWO_TASK set.

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 15 16:23:34 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn sys/oracle as sysdba
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


SQL> conn / as sysdba
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

This occurs if environment varialble ORACLE_SID is not set. Once you set ORACLE_SID, the error goes away.

Tuesday, August 14, 2007

How to convert .srpm to .rpm

An SRPM is an RPM package with source code. Unlike a tarball (or an RPM), an SRPM package can be automatically compiled and installed, following instructions in the .spec file included in the SRPM.


rpmbuild --rebuild <.srpm file>


I found an interesting question "Other than rebuild, how do I use an srpm file ?" and its answer on https://www.redhat.com/archives/rpm-list/2001-December/msg00048.html :

On Wed, Dec 05, 2001 at 11:27:24AM -0500, Pete Peterson wrote:
>
> I want to build the latest openssh for Red Hat 6.2. I grabbed the
> SRPM and did an 'rpm --rebuild' on it and created a bunch of new
> RPM files. The only problme is that the default configuration
> wasn't exactly what I wanted. Unfortunately --rebuild removes
> all the intermediate data, so I couldn't modify patch files,
> config files or spec files --- whatever it is that I would have
> to change to, for instance, enable MD5 passwords.
>
> I've read the man page multiple times and read the (unmaintained)
> RPM HOWTO and I can't seem to find a way to to unwrap the pieces
> from an SRPM file so they can be modified. The build instructions
> seem to be telling me that you need a spec file (-b) or a spec
> file embedded in a maybe-compressed tar file (-t).
>
> I'm sure I'm missing something obvious, but could somebody please
> either explain the procedure or point out where in TFM I should read to get
> an explanation that I can understand, for unwinding an SRPM and recreating
> a new RPM?

Do 'rpm -i ', which will install the sources in
_topdir/SOURCES and the specfile in _topdir/SPECS (where "_topdir" is
either whatever you've set it to in ~/.rpmmacros or /usr/src/redhat by
default).

The go into the specfile and look at the configure options in the %build
section. Change them as appropriate.

To rebuild the package, then run 'rpm -ba ' from inside the
SPECS directory and you end up with binary rpms and a source rpm with
your new settings (ready for a future rpm --rebuild ...). If you just
want the binary rpms, just do 'rpm -bp '.

Cheers,
Malcolm

--
Experience is something you don't get until just after you need it.


I recommend appending your initials to the release number in the spec
file so that it's clear that your rebuild has been tweaked and is no
longer stock from the distributor.

Ken
mailto:shiva@well.com
http://www.sewingwitch.com/ken/




Monday, August 13, 2007

How to find open ports on a Windows box

We are planning to implement a bolt-on called Kewill Flagship for advanced shipping solution in one of our instances. Kewill Flagship works on Windows platform. One of the questions asked by security was the ports which are opened by this application. I came across this link: http://www.petri.co.il/quickly_find_local_open_ports.htm

This is a cool command to find out the open ports on a windows box immediately:

C:\WINDOWS>netstat -an | find /i "listening"

Go through the above link to find more details.

Friday, August 10, 2007

Oracle 11g installation notes

I was able to download Oracle database 11g successfully. I began the install on my Oracle Enterprise Linux 4 Server.


-bash-3.00$ xhost +
access control disabled, clients can connect from any host
-bash-3.00$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB. Actual 17012 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1710 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 65536 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2007-08-10_05-43-40PM. Please wait ...-bash-3.00$

Here are the screnshots:



Got 4 warnings.
Checking for elfutils-libelf-devel-0.97; Not found. Failed <<<<
Checking for unixODBC-2.2.11; Not found. Failed <<<<
Checking for unixODBC-devel-2.2.11; Not found. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<
Problem: Some packages required for the Oracle Database 11g to function properly are missing (see above).Recommendation: Install the required packages before continuing with the installation.
Off I went to otn to check the 11g install document which states these pre-requisite packages for Oracle Enterprise Linux 4:

Packages for Asianux 2, Oracle Enterprise Linux 4.0, and Red Hat Enterprise Linux 4.0
The following packages (or later versions) must be installed: binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

Some other warnings:
Kernel parameters
Checking for semopm=100; semopm=32. Failed <<<<
Checking for shmmax=536870912; shmmax=33554432. Failed <<<<
Checking for ip_local_port_range=1024 - 65000;
ip_local_port_range=32768 - 61000. Failed <<<<
Checking for rmem_default=4194304; rmem_default=110592. Failed <<<<
Checking for rmem_max=4194304; rmem_max=131071. Failed <<<<
Checking for wmem_default=262144; wmem_default=110592. Failed <<<<
Checking for wmem_max=262144; wmem_max=131071. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<
Problem: The kernel parameters do not meet the minimum requirements (see above).
Recommendation: Perform operating system specific instructions to update the kernel parameters.
Checking available swap space requirements ...Expected result: 3036MB
Actual Result: 1710MB
Check complete. The overall result of this check is: Failed <<<<
Problem: The system does not have the required swap space.Recommendation: Make more swap space available to perform the install.
Checking Network Configuration requirements ...Check complete.
The overall result of this check is: Failed <<<<
Problem: The install has detected that the primary IP address of the system is DHCP-assigned.Recommendation: Oracle supports installations on systems with DHCP-assigned public IP addresses. However, the primary network interface on the system should be configured with a static IP address in order for the Oracle Software to function properly. See the Installation Guide for more details on installing the software on systems configured with DHCP.
Need to get back to reading the installation document to ensure all pre-requisites are met. The above exercise proves how much time is wasted if you don't plan and execute.

Finally the installation is complete and I have a working 11g Database to play with. I followed the 11g documentation for pre-install requirements on http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/pre_install.htm#BABFDGHJ

I'll be updating this thread with screenshots and issues faced and solved very soon.

Comment without registering on blogger

Rohit is a colleague. He told me that many a times he has wanted to leave a comment on my blog but put it off because blogger wanted him to register first. He said that is the reason there are no comments on my blog even though quite a few people visit it.

I did a little research and found that it is possible to comment without getting registered. Here's what I found out :

http://bookcriticscircle.blogspot.com/2006/11/comments-change.html

A bit of housekeeping: We've gotten several emails from people having problems posting comments to this site, so we've changed the set up a bit (because we really do want people to comment and discuss). From now on, you don't have to have a blogger account to post a comment -- anyone can post without registering for anything. In the comments section, if you don't have a blogger account, just select "other" instead of "blogger," then fill in your name (and url if you have one). Or you can choose "anoymous" if you must, but we hope you'll all ID yourselves in some way ... So comment away!

This above option became visible only after I went to blogger settings, comments tab and changed the value of field "Who can comment" from registered users to anyone.

Oracle 11g Database for Linux available for download

Oracle 11g Database for Linux is now available for download on http://www.oracle.com/technology/software/products/database/index.html

The file size is 1.7 GB. I am downloading it now to set up a test instance and explore all the cool features described in a previous post http://oracleappstechnology.blogspot.com/2007/07/oracle-database-11g-new-features.html

Gather statistics for SYS and SYSTEM schemas before adpatch

It is a best practice to gather statistics for SYS and SYSTEM schemas before begining a patch in Apps through adpatch. Metalink Note 290028.1 further substantiates this as there is a possibility that patches may hang while executing adpcpset.pls, which is called when you apply a patch. I had raised an SR with Oracle to get their confirmation on collecting statistics for SYS, SYSTEM, APPS and APPLSYS schemas and this what they said:

"No applsys or apps is not going to improve adpatch performance.You should gather stats for the SYS and SYSTEM schema. This is the official postion of dvlp."


So be sure to run these two sql statements before begining any adpatch task:

SQL> exec dbms_stats.gather_schema_stats('SYS',cascade=>TRUE,degree=>20);

SQL> exec dbms_stats.gather_schema_stats('SYSTEM',cascade=>TRUE,degree=>20);

Thursday, August 9, 2007

VNC display issue with OUI


The above screenshot might look familiar to some people. This happens when we use RealVNC's default windowing manager twm. This is specified in the hidden directory .vnc which has a file called xstartup:

# cd $HOME/.vnc
# cat xstartup
#!/bin/sh

[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
twm &

You should comment the line twm & and add a new line. Final contents of xstartup will look like this:

oralmpd1@tsgsd1005 # oralmpd1@tsgsd1005 # cat xstartup
#!/bin/sh

[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#twm &
/usr/dt/bin/dtconfig &

Once you use the Solaris dtconfig as windowing manager, this problem doesn't occur. When you start vnc with the default windowin manager for Solaris.

Notes from the field: HTTP-503 ORA-604

An OLTP clone had the following issues:

adstrtal.sh and adstpall.sh while calling adapcctl.sh, adfrmctl.sh, adfmcctl.sh and adfmsctl.sh gave this error:

Cannot complete applications logon. You may have entered an invalid
applications password, or there may have been a database connect error.

PL/SQL ping option in System Administration responsibility failed with:

Service Temporarily Unavailable

The server is temporarily unable to service your request due to maintenance
downtime or capacity problems. Please try again later.

Forms interface did not launch through Self Service page, and generated this error in error_log_pls file present in $IAS_ORACLE_HOME/Apache/Apache/logs


[Thu Dec 14 01:05:25 2006] [error] mod_plsql:

/pls/$ORACLE_SID/fnd_icx_launch.launch HTTP-503 ORA-604

[Thu Dec 14 01:15:42 2006] [error] mod_plsql: /pls/$ORACLE_SID/OracleSSWA.Execute

HTTP-503 ORA-604

[Thu Dec 14 01:16:58 2006] [error] mod_plsql: /pls/$ORACLE_SID/OracleSSWA.Execute

HTTP-503 ORA-604


http://:/dev60cgi/f60cgi worked without issues. All services were up without issues.


Solution:

Following event was put in the init.ora file to trap ORA-604 error:

event = "604 trace name errorstack: 10046 trace name context forever, level 12"

From the trace, it was discovered that on SYS user a trigger of type 'AFTER EVENT' was created on triggering event: LOGON. The name of the trigger is ‘VALIDATE_INCOMING_SESSION’. This trigger checks for the incoming session’s osusername and matches with a custom table customschema.custom_nouser. If the osusername is not present in customschema.custom_nouser table’s NO_OSUSER column, it disallows access by raise_application_error(-20000,'You are not an authorized user of this database.');



SQL> desc customschema.custom_nouser

Name Null? Type
----------------------------------------- -------- ----------------------------
NO_OSUSER VARCHAR2(15)
YES_NO VARCHAR2(1)
USER_TYPE VARCHAR2(15)



Source of the trigger is given below

1 TRIGGER SYS.validate_incoming_session
2 AFTER LOGON ON DATABASE
3 DECLARE
4 v_username VARCHAR2 (30);
5 v_sid NUMBER;
6 v_serial NUMBER;
7 cursor1 INTEGER;
8 vdate DATE;
9 vsid NUMBER;
10 vosuser VARCHAR2 (30);
11 vusername VARCHAR2 (30);
12 vstatus VARCHAR2 (20);
13 vprogram VARCHAR2 (50);
14 v_terminal VARCHAR2 (30);
15 status INTEGER;
16 v_tmp NUMBER;
17 v_count NUMBER;
18 BEGIN
19 SELECT s.SID, s.terminal
20 INTO v_sid, v_terminal
21 FROM SYS.v_$session s, SYS.v_$process p
22 WHERE s.SID = (SELECT SID
23 FROM SYS.v_$mystat
24 WHERE ROWNUM = 1)
25 AND p.addr = s.paddr;
26
27 SELECT COUNT (*)
28 INTO v_tmp
29 FROM SYS.v_$session vs
30 WHERE vs.username IN ('APPLSYSPUB', 'APPS')
31 AND vs.terminal = v_terminal;
32
33 IF (v_tmp = 0)
34 THEN
35 SELECT COUNT (*)
36 INTO v_count
37 FROM (SELECT 1
38 FROM customschema.custom_nouser gn, SYS.v_$session vs
39 WHERE vs.SID = v_sid
40 AND vs.username IN ('APPS')
41 AND UPPER (vs.osuser) = gn.no_osuser(+)
42 AND NVL (gn.yes_no, 'N') = 'N'
43 AND vs.program NOT LIKE 'ADI%'
44 AND vs.program NOT LIKE 'GLDI%'
45 /*
46 AND ( program LIKE '%SQL%'
47 OR program LIKE '%sql%'
48 OR vs.program LIKE '%TOAD%'
49 OR vs.program LIKE '%toad%'
50 OR vs.program = NULL
51 OR vs.program LIKE '%PLUS%'
52 )
53 */
54 AND status <> 'KILLED'
55 UNION
56 SELECT 1
57 FROM customschema.custom_nouser cs, SYS.v_$session vs
58 WHERE vs.SID = v_sid
59 AND vs.username IN ('APPS')
60 AND UPPER (vs.osuser) = UPPER (cs.no_osuser)
61 AND cs.yes_no = 'N'
62 AND status <> 'KILLED');
63
64 IF (v_count > 0)
65 THEN
66 --insert into customschema.killed_sessions (sid,theosuser,theuser,thedate,theprog,thestat)
67 --values (vsid,vosuser,vusername,vdate,vprogram,vstatus);
68
69 raise_application_error(-20000,'You are not an authorized user of this database.');
70
71 END IF;
72 END IF;
73 END;

73 rows selected.


The osusername for the patch instance was not present in the table. This has been inserted in the table, which has fixed the issue.


This issue is very specific to this particular OLTP instance, because of this custom trigger. You may not find this in other instances. However, it is important to understand how the problem was diagnosed and solved, which is why I have taken the pain to document it.

Wednesday, August 8, 2007

Good link for Solaris shell commands from Sun

http://www.sun.com/bigadmin/shellme/ provides most important shell commands in Solaris. It also has a link for all Solaris commands.

Here's a small sample from the link given above:

Additional Resources
1. All Solaris Commands * 2005-08-09
2. Alphabetical Directory of Linux Commands * 2005-03-22
3. Comparision of Shell commands (PDF) * 2005-11-19
4. Cool Commands * 2006-05-15
5. coolcommands.com * 2005-10-13
6. DOS to UNIX command translation * 2006-02-09
7. Korn Shell Nuances * 2006-11-07
8. Man Pages: System Administration Commands (Solaris 10) 2006-02-14
9. Man Pages: System Administration Commands (Solaris 9) 2006-02-14
10. Man Pages: User Commands (Solaris 10) 2006-02-14
11. Man Pages: User Commands (Solaris 9) 2006-02-14
12. Tips on good shell programming practices * 2006-03-10
13. Trapping Special Characters in the Korn Shell * 2005-10-13
14. Universal Command Guide for Operating Systems * 2005-01-19
15. Useful Solaris Commands (Blog) * 2006-11-16

Debugging
16. cat -v -t -e [file]
/* Show non-printing characters */ 2006-06-20
17. dumpadm -d swap
/* Configure swap device as dump device */ 2005-02-16
18. ld -l
/* Check if you have a particular library */ 2005-03-30
19. truss -f -p
/* Using multiple windows, this can be used to trace setuid/setgid programs */ 2005-03-22
20. truss executable
/* Trace doing of given command ( useful debugging ) */ 2005-03-22
Back to top

Unix command for no. of processors

In Solaris these are the commands:

/usr/sbin/psrinfo -p gives the no. of physical processors

$ /usr/sbin/psrinfo -p
8

$ /usr/sbin/psrinfo -pv
The UltraSPARC-IV physical processor has 2 virtual processors (8, 520)
The UltraSPARC-IV physical processor has 2 virtual processors (9, 521)
The UltraSPARC-IV physical processor has 2 virtual processors (10, 522)
The UltraSPARC-IV physical processor has 2 virtual processors (11, 523)
The UltraSPARC-IV physical processor has 2 virtual processors (16, 528)
The UltraSPARC-IV physical processor has 2 virtual processors (17, 529)
The UltraSPARC-IV physical processor has 2 virtual processors (18, 530)
The UltraSPARC-IV physical processor has 2 virtual processors (19, 531)


/usr/sbin/psrinfo gives the no. of cores

$ /usr/sbin/psrinfo |wc -l
16


uname -X also gives similar information

$ uname -X
System = SunOS
Node = exampleserver
Release = 5.8
KernelID = Generic_117350-35
Machine = sun4u
BusType =
Serial =
Users =
OEM# = 0
Origin# = 1
NumCPU = 16

In Linux the same information can be obtained by grepping /proc/cpuinfo:

grep -c processor /proc/cpuinfo

[root@rel12 ~]# grep -c processor /proc/cpuinfo
2

Monday, August 6, 2007

Apps 11i login and logout flow

I have tried to bisect the login flow of Apps 11i here.

First step is URL: http://www.justanexample.com:8080

Once you type or use your browser favorites button to navigate to this URL,

1. The request goes from your browser,

2. The name www.justanexample.com is resolved to an IP, first by referring to c:\windows\system32\drivers\etc\hosts file on your Windows PC, and then by querying the DNS server.

3. Once the IP address is obtained, the request travels over TCP/IP protocol on network and connects to port 8080 on which Apache Server is running.

4. Apache server then displays the Oracle Applications Rapid Install Portal page ($COMMON_TOP/portal/$CONTEXT_NAME/index.html) which has links for Rapid Install Setup home, Apps Logon links(aplogon.html), 11i online help, and machine admin scripts on the left sidebar(appdet.html).

5. When you click on Apps Logon links, it shows links for Oracle E-Business Home Page and OAM on the right pane.

6. Once you click on the Oracle E-Business Home Page link, the request is forwarded by Apache to Jserv. Jserv sends it to Appslogin servlet

7. AppsLogin servlet

The AppsLogin servlet supercedes older Release 11i login mechanisms and certain product-specific login pages. The AppsLogin servlet detects the login mode by reading the "Applications SSO Type" profile option for the current Oracle E-Business Suite Release 11i instance and transparently redirects to the appropriate login page.

  • Scenario 1: AppsLogin running on a Release 11i instance configured for Self-Service Web Applications (SSWA) will authenticate the user via the AppsLocalLogin.jsp page, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
  • Scenario 2: AppsLogin running on a Release 11i instance configured for Single Sign-On will redirect to the Single Sign-On 10g login page for user authentication, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
  • Scenario 3: AppsLogin running on a Release 11i instance configured for Single Sign-On and Portal will redirect to Single Sign-On 10g login page for user authentication, and then redirect to the default Portal home page.


/oa_servlets/Appslogin servlet
Gets WebAppsCOntext
Sets requestURL = APPSHOMEPAGE unless passed as a parameter.
Sets cancelUrl = oracle.apps.fnd.sso.SSOManager.getLoginUrl(); unless passed as a parameter.
This finds profile option APPS_SERVLET_AGENT (Apps Servlet Agent) and returns error if it cannot get this profile option.
Sets variable "url" using oracle.apps.fnd.sso.SSOManager.getLoginRedirectUrl
sets Mode using Utils.getSSOMode()
which gets profile option APPS_SSO (Applications SSO Type) SSWA is set by default if not profile option.
Depending on mode, sets url = LOCAL_LOGIN | SSO_LOGIN_REDIRECT | PORTAL
if SSWA = SSOUtil.getLocalLoginUrl()
This gets profile option APPS_LOCAL_LOGIN_URL (Applications Local Login URL) (AppsLocalLogin.jsp for example) and prepends '/OA_HTML/'
if PORTAL = SSOUtil.getPortalUrl()
This gets profile option APPS_PORTAL (Applications Portal) This is full URL normally pointing to /pls/XXX_portal30/portal30.home
Then calls "constructUrl" to determine the URL to return, which builds URL consisting of url, requestURL, cancelURL, errCode, langCode, errText and home_url parameters
If "url" is null, then returns FND-9930 error code, otherwise will redirect to the constructed URL
At this point, we have got as far as rendering the Username/PAssword entry screen... /OA_HTML/AppsLocalLogin.jsp or portal30.home

/OA_HTML/AppsLocalLogin.jsp
Gets Profile options FND_BRANDING_SIZE (FND: Branding Size) and ICX_LANGUAGE (ICX: Language)
Sets up some message text (Message.class) Uses Package FND_MESSAGE.GET_MESSAGE_INTERNAL ()
Once we click "Login" button we follow through as below:-


fndvald.jsp
Utils.getConnection() - this throws FND-9902 if cannot get DB connection.
SessionMgr.createAppsSession which itself calls wctx.getSessionManager().validateLogin followed by createSession if username.password validated OK.
There is some STATEMENT level diagnostics messages from here (for oracle.apps.fnd.sso.SessionMgr)
Gets Cookie name from dbc file ICX_COOKIE_NAME or otherwise calls package FND_SESSION_MANAGEMENT.getsessioncookiename
Creates SSO Cookie for new user session
If requestURL = APPSHOMEPAGE it calls SSOUtil.getHomePage otherwise just passes requestURL through...
This calls computeURL, which looks for profile option APPLICATIONS_HOME_PAGE (Self Service Personal Home Page mode)
Runs getEnvStore90.getEnv(ICX_PV_SESSION_MODE) which I dont understand... but seems to be 115P | 115J ?
If APPLICATIONS_HOME_PAGE = "PHP" or "PHP_FWK" then will set URL = PLSQL_AGENT/OracleNavigate.Responsibility
else (for example "FWK") will set URL = "/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE&dbc=" + AppsAgent.getDBC()


OA.jsp
oracle.apps.fnd.framework.webui.OAPageBean.class
oracle.apps.fnd.framework.webui.OAJSPHelper
Uses OAJSPHelper to set character encoding for the session, mainly by getting ICX_CLIENT_IANA_ENCODING (ICX: Client IANA Encoding) profile option
Then sets redirectURL = pageBean.preparePage
Sets flag for Portlet or not
Sets functionName from the parameters in the URL (OAHOMEPAGE for example, if URL param is ?OAFunc=OAHOMEPAGE)
Sets redirectURL = preparePage() method... this calls "redirectURL = validateUser()" but I think this should return null if I have logged in (this seems to be to do with bookmarkable URLs)
sets funcURL = createRFURL()
This does some session validation, then calls OAPageContextImpl.createRFURL
Which calls FunctionSecurity.getFunction and Function.getFunction...
This bit runs SQL to find FunctionID "select function_id from fnd_form_functions where function_name = :1"
Then "select function_name, context_dependence, maintenance_mode_support, parameters, type, web_agent_name, web_host_name, web_html_call, web_icon, form_id from fnd_form_functions where function_id = :1"
For OAHOMEPAGE the following will be set
web_html_call = "OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y&OAPB=FWK_HOMEPAGE_BRAND"
type = "JSP"
isOAFCall is set to TRUE (as the URL has OA.jsp in the URL)
funcCall = web_html_call
hasTransactionId = FALSE
so adds "&transactionid=xxx" to the funccall then returns this as the new url
This new URL is returned to OA.jsp as the redirectURL, so we call OA.jsp with some different parameters.....

By now, we should have URL something like:
OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y&OAPB=FWK_HOMEPAGE_BRAND&transactionid=xxxxxxx
Back into "redirectURL = pageBean.preparePage()"
regionCode = OAPageContextImpl.getUrlParameter(request, "page") (page=/oracle/apps/fnd/framework/navigate/webui/HomePG)
//Get the application module java class from AK.
OAApplicationModule staticAppMod = OAJSPApplicationRegistry.getStaticAKApplicationModule(mRequest);
This creates AM called "oracle.apps.fnd.framework.server.OAApplicationModule" with DB connection.
Creates MDS context, seems to have handles to MDS repository.
Gets page region, using WebBeanTypeData, from MDS repository.
Gets transaction_ID
Get profile option "FND_FORCE_PAGE_REFRESH"
Get profile option "FND_CUSTOM_OA_DEFINTION"



portal30 HOME procedure
Does a owa_util.redirect_url to wwpob_page_util.get_page_url (wrapped PLSQL)



Check versions
Java
(adident Header $OA_JAVA/)
oracle/apps/fnd/sso/AppsLogin.class
oracle/apps/fnd/sso/SSOManager.class
oracle/apps/fnd/sso/Utils.class
oracle/apps/fnd/sso/SSOUtil.class
oracle/apps/fnd/common/Message.class
oracle/apps/fnd/sso/SessionMgr.class
oracle/apps/fnd/common/WebAppsContext.class
oracle/apps/fnd/framework/webui/OAPageBean.class
oracle/apps/fnd/framework/webui/OAJSPHelper.class
oracle/apps/fnd/framework/webui/OAWebBeanConstants.class
oracle/apps/fnd/framework/webui/OAPageContextImpl.class
oracle/apps/fnd/framework/OAFwkConstants.class
oracle/apps/fnd/functionSecurity/Function.class

oracle/apps/fnd/framework/mds/OAMDSContextUtils.class


JSP
(adident Header $OA_HTML/)
AppsLocalLogin.jsp
fndvald.jsp
OA.jsp


Logout flow

Logging out from Apps 11i

Logging out starts with the logout link on the framework page. This comes from the function "ICX_LOGOUT" call from the global menu ICX_GLOBAL_MENU
/OA_HTML/OALogout.jsp

This calls SSOManager.java which sets the logoutURL = "/oa_servlets/oracle.apps.fnd.sso.AppsLogout" then OALogout.jsp redirects to this URL
AppsLogout.java

Calls "oracle.apps.fnd.sso.SSOManager.getLogoutRedirectUrl" to determine URL which it then redirects to SSOManager.java

getLogoutRedirectUrl checks the ICX_SESSIONS.MODE_CODE of the session (whether SSWA or PORTAL) 115P = SSWA
This determines whether to use
LOCAL_LOGOUT (AppsLocalLogout.jsp)
or
SSO_LOGOUT_REDIRECT (oracle.apps.fnd.sso.FNDSSOLogoutRedirect)
AppsLocalLogout.jsp

If no "returnURL" is passed in and APPS_SSO_POSTLOGOUT_HOME_URL is not set, then it calls SSOUtil.getLocalLoginUrl (However see bug Bug 6175894 below)
SSOUtil.java

getLocalLoginUrl picks up the profile option APPS_LOCAL_LOGIN_URL (AppsLocalLogin.jsp) and redirects
PHP Logout Flow

1. "GET /OA_HTML/OALogout.jsp?menu=Y HTTP/1.1" 302 271
2. "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogout HTTP/1.1" 302 296
3. "GET /OA_HTML/AppsLocalLogout.jsp?returnUrl=/OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp HTTP/1.1" 302 331
4. "GET /OA_HTML/AppsLocalLogin.jsp?cancelUrl=/OA_HTML/AppsLocalLogin.jsp&langCode=US&username=SYSADMIN HTTP/1.1" 200 8443

SSO Logout Flow

1. "GET /OA_HTML/OALogout.jsp?menu=Y HTTP/1.1" 302 267
2. "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogout HTTP/1.1" 302 345
3. "GET /oa_servlets/oracle.apps.fnd.sso.FNDSSOLogoutRedirect?returnUrl=http%3A%2F%2FmyHost.uk.oracle.com%3A80%2Fo a_servlets%2Foracle.apps.fnd.sso.AppsLogin%3FlangCode%3DUS HTTP/1.1" 302 385
4. "GET /OA_HTML/SSOAppsLogout.jsp HTTP/1.1" 200 209
5. "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 444
6. "GET /oa_servlets/oracle.apps.fnd.sso.FNDSSOLoginRedirect?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2FmyHost.uk.oracle.com%3A80%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&s=93DE6C66677A82FC0DB9AEC95EDD0A5AF37A949D37 HTTP/1.1" 302 684

Related Profile options

* Applications Login Server Logout (APPS_LS_LOGOUT) Default = null
* Applications Portal Logout (APPS_PORTAL_LOGOUT) Default = null
* Applications SSO Post Logout URL (APPS_SSO_POSTLOGOUT_HOME_URL) Default = null
* Applications Local Login URL (APPS_LOCAL_LOGIN_URL) Default at Site level = AppsLocalLogin.jsp)

How To Change UNIX Process Resource Limits Via a Bourne or Korn Shell

Which individual resources are available and their soft limit values
can be displayed by entering the ulimit command with the '-a' option:

$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 524288
stack(kbytes) 8192
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 60

In some shells on certain platforms, the '-a' option doesn't work so
you will have to specify a specific resource option:

-c Maximum size of core files.
-d Maximum size of data segment or heap.
-f Maximum size for files written by child processes.
-m Maximum size of physical memory.
-n Maximum number of file descriptors.
-s Maximum size for the stack area.
-t Maximum number of seconds to be used by each process.
-v Maximum size of virtual memory.
-w Maximum size of swap area.
-H The limits specified for the resources are hard limits.
-S The limits specified for the resources are soft limits. (the default
if neither '-H' or '-S' is specified.

So to display the soft limit for stack size:

$ ulimit -s
8192

To raise the soft limit for stack size:

$ ulimit -s 8193
sh: ulimit: The specified value exceeds the user's allowable limit.
[this indicates 8192 is also the hard limit]

To check the hard limit for stack size:

$ ulimit -Hs
8192

To increase the hard limit:

$ ulimit -Hs 8193

To see if the change took place:

$ ulimit -Hs
8193

To raise the soft limit:

$ ulimit -Ss 8193
sh: ulimit: The specified value exceeds the user's allowable limit.
[this indicates the kernel imposed limit is 8192]

To set the soft limit to 'unlimited':

$ ulimit -s unlimited

If one of the above commands doesn't work as expected, refer to the
platform specific information below.

Content taken from Metalink Note: 188149.1 How to Display and Change UNIX Process Resource Limits

Friday, August 3, 2007

Apps 11i Server sizing recommendations

This is from a presentation by John Stouffer of Solutionbeacon.

• Disk Space
- 25GB for the software and database
- 10GB for the software staging areas
- 10-25GB for patches, log/out files,archives, disk backups, temporary files, etc.
- RAID is necessary
• Typical ERP Server Configuration
- Combined Single Server
– 4CPU, 4GB RAM (Scalable)
– 50 GB disk effective

Memory
• Oracle minimum – 8MB/user
• Real World Recommendation
- 15MB for every active user
- 15MB for concurrent manager workers
- 20MB for database background processes
- 500MB SGA (pinning packages/sequences)

•CPU
• Oracle minimum – 70 users/CPU
• Real World Recommendation
- 15 Active Users/CPU (super users)
- 50 Logged Users/CPU
- 1 CPU Dedicated – Database and Application Processes (DB, Forms, Apache, Concurrent
Managers

Analyze what's happening in concurrent requests

A concurrent request is a process or set of processes which call a concurrent prgram. It is important for an Apps DBA to know what the most critical concurrent processes do on code level. That means opening the sql files or unix shell scripts and understanding what is happening in a concurrent program. If you understand the program on a high level, it will help you in troubleshooting any issues with concurrent requests which call that program. An exmaple of a high level knowledge is this: The program checks some conditions, with select queries, then it loads some data thru SQL*Loader which goes to some interface table. Then it further checks some more conditions and inserts data in staging tables. If you know this much, without actually going into further details, it should be enough to give you clues in case a request calling the program seems to have hung.

Some Metalink Notes which have scripts for analyzing pending requests and analyzing a single request:

134033.1 ANALYZEPENDING.SQL - Analyze all Pending Requests

134035.1 ANALYZEREQ.SQL - Detailed Analysis of One Concurrent Request (Release 11 and up)

Thursday, August 2, 2007

Workflow performance

Refer to these metalink notes

277124.1 FAQ on Purging Oracle Workflow Data
148678.1 Resolving Workflow Performance Issues
132254.1 Speeding Up And Purging Workflow
144806.1 A Detailed Approach To Purging Oracle Workflow Runtime Data
298550.1 Troubleshooting Workflow Data Growth Issues
165316.1 bde_wf_data.sql - Query Workflow Runtime Data That Is Eligible For