2007.07.20
OPatch 10.2.0.3 note: add %ORACLE_HOME%\jdk to the PATH before running OPatch.
JACoulter's Oracle Notes
DISCLAIMER: The information and links provided on this site are my personal notes, based on my limited experience installing, using, and deinstalling Oracle databases and application servers on a variety of computers using Windows NT/2000/XP operating systems. The installation and deinstallation procedures found here can cause irreversable loss of data and may damage your operating system. However, you are welcome to use them AT YOUR OWN RISK. I am in no way responsible for data you lose or operating systems you damage or destroy.
OPatch 10.2.0.3 note: add %ORACLE_HOME%\jdk to the PATH before running OPatch.
Spent the better part of the morning trying to remmber how to load the DBMS_LDAP package into a database. It is a script called catldap.sql, located in the ORACLE_HOME\rdbms\admin\ directory.
Had problems with the SQLNET.AUTHENTICATION_SERVICES= (NTS) directive in sqlnet.ora again, this time during a re-install of an OAS 10g 10.1.2.0.2 Infrastructure on our Windows 2003 Server development box.
The Win2003 box is acting as an Active Directory domain controller, so local user accounts cannot be created. During the installation of the metadata repository database, we were got a ORA-12152: TNS:unable to send break message error. This error can be caused by inability to communicate with a database instance. After trying the install a couple of times, I waited until the error appeared and then opened the sqlnet.ora file and commented out the SQLNET.AUTHENTICATION_SERVICES= (NTS) directive. I retried the configuration assistant, and the installation continued without a problem.
I don't know why this error suddenly began to appear. I installed OAS on this box several times before using the domain user account without any problems. The only thing that changed was I installed a newer version of Service Pack 1 (needed this for the ktpass.exe utility - different problem).
I posted the question of why this would problem would appear on OTN forums. Doubt I'll get a response, but at least I found a work-around.
While attempting to run a perl script that required logging into an OAS 10g metadata repository database (10.1.4.0.2), I kept getting the following error:
ORA-28547: connection to server failed, probable Net8 admin error
After several hours of reconfiguring the listener.ora, tnsnames.ora, and sqlnet.ora and searching the internet, I finally fixed it by deleting this line from the sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES= (NTS)
I have no idea why this fixed it, except that I am logged in as a domain user and apparently this causes problems. This line has caused other problems before; maybe I should just delete it on principle whenever I do a new installation on Windows boxen.
Working on configuring Windows Native Authentication for the Oracle Single Sign-on server. Had issues getting the Windows ktpass utility to issue a usable keytab file. After lots of meandering around the internet and a rebuff from Oracle Support, finally got something to work. Here's the command syntax I used:
C:\oracle\infra1012>ktpass -princ HTTP/hw-05-0193@EMTS.TYBRIN.COM -pass passwd01 -mapuser hw-05-0193 -out sso.keytab -ptype KRB5_NT_PRINCIPAL
Here's the kinit command that worked:
C:\oracle\infra1012>kinit HTTP/hw-05-0193.emts.tybrin.com@EMTS.TYBRIN.COM
Password for HTTP/hw-05-0193.emts.tybrin.com@EMTS.TYBRIN.COM:passwd01
New ticket is stored in cache file C:\Documents and Settings\Administrator\krb5cc_Administrator
What finally got it to work was deleting and then recreating the hw-05-0193 user account from the Windows server. I found this out from this posting on the comp.protocols.kerberos newsgroup.
But the SSO server still isn't getting a ticket from AD.
The ball is back in Oracle Support's court. . .
Sent on a wild goose chase by an Oracle Wallet - again.
A wallet is really a pkcs#12 file, with all that Oracle goodness added in.
To be usable, a wallet needs it's auto-login feature enabled. A pkcs#12 file that does not have auto-login featured will prompt the user to provide a password. An Oracle Wallet just gives errors.
When you save an Oracle Wallet with the Oracle Wallet Manger and auto-login is enabled, the Wallet Manager creates a second file, called cwallet.sso.
Guess what? cwallet.sso is what all Oracle Application Server components use - not the Oracle Wallet itself!
So if you delete an old wallet, and replace it with a new wallet, but don't delete the old cwallet.sso file, all Oracle Application Server components will continue to serve the old wallet!
Yay!
And not a word in the HTTP Server, SSO admin, or Application Server admin guides about cwallet.sso.
Boo!
Working with Oracle Support on the sso/auth problem. Seems IE 6.0 has a bug that causes the redirect to fail. Using IE 7.0 and Mozilla Firefox 1.5 I was able login to a SSO-protected test directory via HTTPS. However, when I changed the httpd.conf DocumentRoot directive back to our application's root directory, I got the sso/auth error in IE 7.0. Firefox sends me the Forms app, but now I'm having a problem with the Sun JAVA plug-in. . .
Ran into another problem: unable to access our application through the webcache via HTTPS. Worked okay when webcache bypassed, but got a Web Application Server Unable to Respond error when going through webcache. Opened a TAR, and the solution was to change the orgin server wallet location in the webcache from "System defined default" to the actual path of the webcache wallet.
Problem accessing SSO still exists, but I have found that if I hit the back button in the browser to the static page that calls our forms app after logging into sso, I can access the application. Apparently the login is successful and a cookie is set even though I'm getting the error. Will try a few more things before reopening the TAR.
The problem noted below has reappeared again overnight. To the best of my knowledge nothing has changed since yesterday afternoon when SSO was working when connecting via HTTPS. Tried restarting the OC4J_BI_Forms, middle-tier HTTP server, and infrastructure OC4J_SECURITY, but no change. Shutdown the middle-tier and and infrastructure entirely, and no change. The HTTP error logs reveal the same error: MOD_OC4J_0376: Request initial processing failed in ac worker with HTTP status code 1. The OC4J logs don't reveal anything (to my limited knowledge) that looks out of the ordinary. Looks like I'll be re-opening that TAR
Returned from the weekend, installed the latest Windows patches on our SSO development server, rebooted, and promptly got the same problem I had before, HTTP 404 errors after authenticiation by the SSO server.
Tried restarting the OC4J_BI_Forms instance, but that didn't fix the problem. Restarted the OC4J_SECURITY instance on the infrastructure, the middle-tier HTTP Server, and for good measure, OC4J_BI_Forms again, and it worked.
First it was ghost wallets, and now this. Oracle drives me crazy.
Setting up Single Sign-on for Oracle Application Server 10g Rel 2 (10.1.2.0.2) with the ultimate goal of enabling Windows Native Authentication. Had serious problems getting SSL to work. Followed instructions in Chapter 7 and Chapter 4 of the Single Sign-on Admin's Guide, but as ususal, the language is impercise and not all the required steps are included. After many reconfigurations, one re-install, and hours of searching the Oracle Forums and MetaLink, I finally figured out what my problem was - after making changes to the Oracle HTTP server files, not only is are dcmctl updateconfig -v -d and opmnctl stop/startproc ias-component=HTTP_Server commands required on the middle-tier, but it is also necessary to restart the OC4J_BI_Forms container. I have put a short list of steps required to enable SSL for SSO in the howto section.
With that problem resolved, I discovered another issue: I can connect to my Forms app via HTTPS with SSO enabled directly through the middle-tier HTTP server, but when I try to go through the Web Cache, I get a "No Response From Application Web Server" response. Searched MetaLink and (surprisingly) found the problem addressed in Note 253887.1, Internet Explorer shows 'No Response from Application Web Server' in Request Redirection through Web Cache and after logging in through SSO. The fix is to use different hostnames for the infrastructure and the middle-tier. That's going to mean a deinstall and a reinstall. Oh joy!
Have spent several frustrating days trying to configure the 10.1.2.0.2 Single Sign-On server for SSL connections. Have been through the SSO Admin guide and several Metalink notes and almost got there, getting our Forms application to request SSO authorization via HTTPS, but after entering the username and password getting first a "Forbidden" error and now a "Page Not Found" error. Looking at the Apache error logs for the SSL virtual host on the infrastructure's OHS, it looks like the loopback adapter is requesting the connection as 192.168.1.1 and the virtual host is not recoginizing the IP address. Will try adding an IP-based virtual host for 192.168.1.1 when I return from TDY next week.
Had problems configuring the Oracle HTTP server to accept HTTPS connections on a 9.0.4.2 middle-tier running on Windows 2000 SP4.
The error message in the virtual host error log indicated a problem with the virtual host configuration. This configuration is the one supplied by Oracle. According to the Oracle HTTP Server Admin's Guide, all that is required to enable SSL for the HTTP server is changing a single attribute in the opmn.xml file and placing a copy of the wallet in the HTTP server's default wallet location.
This didn't work. I tried using a known good wallet (it was used for SSL with the WebCache component) as well as the test wallet supplied by Oracle. I examined the ssl.conf file, where Oracle defines the virtual host that handles HTTPS connections, and could find nothing wrong there. The ssl.conf file is loaded with an include statement in the httpd.conf file.
After banging my head against the wall all day, I decided to disable the ssl.conf file by commenting out the include statement and added a "Listen 443" statement as well as a <VirtualHost> directive directly to the httpd.conf file. The <VirtualHost> directive looks like this:
<VirtualHost server.domain:443>
SSLEngine on
SSLWallet file:d:\oracle\ora904\Apache\Apache\conf\ssl.wlt\default
</VirtualHost>
Now SSL works and I am once again wondering why Oracle has to make this more difficult than it needs to be.
While working on configuration of a new Oracle Application Server 10g (10.1.2.0.2) installation on a Windows 2003 Server Standard edition discovered the JInitiator 1.3.1.22 plug-in was not working in HTTPS mode with the new Internet Explorer 7.0. HTTP mode was fine, and there were no problems with JInitiator in HTTPS mode using IE 6.0 SP1, only IE 7.0.
Knowing our customers will have to upgrade to IE 7.0 within the next few months, and that use of HTTPS is mandatory for them, I posted the problem on the Oracle Forums and received two very informative responses from an Oracle Forms product engineer and another Oracle Forms user. The Forms engineer stated that JIntiator has not been certified for use with IE 7.0, although Oracle is "discussing" doing so. Both suggested replacing the Oracle JInitiator with the Sun JRE plug-in, specifically JRE version 1.4.2_09 or higher.
I began researching the Oracle Forms documentation for instructions how to configure Forms to use the Sun JRE plug-in, but still needed the correct parameter values. I googled jpi_classid and the first link returned led me to this entry in Wilfred van der Deijl's OraTransplant blog. Wilfred provides the correct parameter values to use in the formsweb.cfg file. He also notes that to use the Sun JRE plug-in with Forms 9.0.4.x the Oracle WebCache must be disabled (WebCache works correctly with the Sun JRE plug-in and Forms 10.1.2).
I tried this on both 9.0.4.2 and 10.1.2 set-ups. One thing I noted was I had to uninstall all Sun 1.5 JDKs, SDKs and JREs and allow the browser to download the latest 1.4.2_x version or else the browser would use the 1.5 version (which doesn't work correctly with Forms 9.0.4.2). I haven't tried using 1.5 with Forms 10.1.2, so it may work. But right now our customers are using Forms 9.0.4.2, so I'll have to keep them at the latest 1.4.2_x version of the Sun JRE plug-in.
I am posting a thank-you to Wilfred's blog for providing this information. I have also posted the formsweb.cfg parameters and values in the How-to section here.
UPDATE 2006.11.17 This morning my Java automatic update indicated it wanted to download a new version of Java 2 (most likely because I uninstalled all versions newer than 1.4.2_x yesterday). I allowed it to download and install version 1.5.0_09 and tested my Forms 10.1.2 and 9.0.4.2 applications. Both work normally, although when running the JRE with Forms 9.0.4.2 the console shows the following error:
java.lang.NoSuchMethodException: sun.java2d.SunGraphicsEnvironment.setFallbackFont(java.lang.String)
at java.lang.Class.getMethod(Unknown Source)
at oracle.forms.engine.Main.initDesktop(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
java.lang.NoSuchMethodException: sun.java2d.SunGraphicsEnvironment.preferLocaleSpecificFonts()
at java.lang.Class.getMethod(Unknown Source)
at oracle.forms.engine.Main.initDesktop(Unknown Source)
at oracle.forms.engine.Main.start(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
I'm not sure what this means, but the Forms application loads in the JRE normally and runs normally. Users won't see the above error message unless they open the Java console, so I'm going to let it ride. The error does not appear when running Forms 10.1.2
Spent three days talking a customer through installation of CPU October 2006 on his Oracle 9i (9.2.0.6) database servers and Oracle 10g (9.0.4.2) application servers. Had issues with the cscript //nologo install.js command and envrionment variables. Although I was able to successfully set these on my Windows XP box using the instructions in Setting Environment Variables for Critical Patch Updates (Windows) we couldn't get it to work on his Windows 2003 Server Standard boxes. No matter what we tried we got that frustrating
"Please set the jdk home in the PATH variable and rerun the script
or
Please check if you have set the variables ORACLE_HOME, path for java.exe
in PATH correctly and path for oraInstaller.dll is set in the PATH
variable and rerun the script"
I searched for "cscript" on the Oracle MetaLink forums and found several posts from users who had the same problem. Their solution was to change to the DB_9.0.1.5FIPS_PLUS subdirectory and run cscript //nologo patch.js, bypassing the install.js script. Gave it a shot and it worked. Apparently all install.js does is check the envrionment variables before calling patch.js. Why Oracle makes this process more difficult than it has to be is beyond all of us.
Applied the Oct 2006 Critical Patch Update to an Oracle Application Server 10g (9.0.4.2) infrastructure and middle-tier yesterday with no problems. The Database 9i 9.2.0.6 patch has been delayed until the end of October. Refined the setting of Windows envrionment variables; should be able to copy from my how-to and paste into a command prompt window.
Browsing the Oracle Forums and found an interesting post describing using the iff2xml90.bat utility to convert a Forms .fmb file to xml format. This allows a Forms developer to perform diffs on different versions of a .fmb. Tried it out and this is what it looks like:
C:\>cd \oracle\dev904\bin
C:\oracle\dev904\bin>iff2xml90 c:\oracle\dev904\forms90\test.fmb
Oracle Forms 10g (9.0.4) Forms to XML Tool
Copyright(c) 2001, 2004, Oracle. All rights reserved.
Processing module c:\oracle\dev904\forms90\test.fmb
Graphics IMAGE40 image saved as TEST_CANVAS1_IMAGE40.tif.
XML Module saved as c:\oracle\dev904\forms90\test_fmb.xml
iff2xml90.bat is included with Oracle Developer 10g
Returned from two weeks' of installing and upgrading our Forms application at several locations. One of the questions raised by our customers was how to set a default database SID so users would not have enter the SID when logging into the application. Oracle 9iAS required a Windows registry edit; OracleAS 10g requires only the addition of a new envrionment variable named LOCAL with the tns alias of the database to the Forms default.env file. This can be done easily using Enterprise Manager. More explanation here.
Attempted to apply Oracle Application Server Patchset 9.0.4.3 to a 9.0.4.2 installation but ran into the following issues:
I had to wipe the installation and start over from scratch. No problems noted. Only had to apply the Software Update portion of the patchset; the metadata repository changes affect only Portal and Wireless installations, which we do not use.
Browswing the OTN forums, found a link leading to instructions for creating a second Forms server on Oracle Application Server 10g.
A customer location was getting Visual C++ Runtime errors when attempting to log-in to our Forms application at a remote location. This error is usually caused when a user attempts to load conflicting versions of JInititator or the Sun JRE in the same browser. In this instance the error was apparently caused by the proxy server. At several of our customer's locations, the proxy server information must be entered into the JInitiator control panel. The default is for JInitiator to read the proxy information from the browser. According to our customer a Windows security patch prevents JInitiator from reading the browser's proxy settings.
Downloaded and applied CPU July 2006 to a 9.2.0.6 database server and a 9.0.4.2 infrastructure and middle tier. No problems with the database server patch and only the usual problems running the cscript install.js script on the application server's Homes. Solved this by clearing the PATH envrionment variable of all extraneous information and added only those directories required for the patch. Our Forms application works fine, no known issues at this time.
Our customer called asking why our Forms application wasn't enforcing the 10-day minimum wait before a password change.
We use a modified version of Oracle's verify_password function to enforce password rules. The offending section of code looked like this:
/* Handled by Profile. Change no sooner than every 10 days.
If expdate is 80 days away or less then OK, else error.
Secondary check */
BEGIN
SELECT 'X' INTO dummy
FROM DBA_USERS
WHERE username = username_var
AND (expiry_date - sysdate) < 81;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20002,
'Password cannot be changed sooner than 10 days after last change');
END
The problem was caused by a change in the profile's password expiration (expiry_date) from 90 days to 45 days. We determined that it would be better to write the function to use the values stored in the database to determine when the 10 day time limit was up. This was the solution we came up with:
/* V3.1 must handle < 10 day check based on any expiration time defined
in the profile. Changed < 10 days ago means
(Expiry_Date-GracePrd) - Sysdate %lt; (Life Span + Grace Period) - 9
*/
life_limit_var := 0;
grace_limit_var := 0;
BEGIN
SELECT limit
INTO life_limit_var
FROM dba_profiles
WHERE Profile = 'JLIM_USER_PROFILE'
AND resource_name = 'PASSWORD_LIFE_TIME';
EXCEPTION
WHEN OTHERS THEN
Null;
END;
BEGIN
SELECT limit
INTO grace_limit_var
FROM dba_profiles
WHERE Profile = 'JLIM_USER_PROFILE'
AND resource_name = 'PASSWORD_GRACE_TIME';
EXCEPTION
WHEN OTHERS THEN
Null;
END;
IF life_limit_var > 0 THEN
BEGIN
SELECT 'X' INTO dummy
FROM DBA_USERS
WHERE username = username_var
AND (expiry_date+grace_limit_var) - sysdate <
(life_limit_var+grace_limit_var)-9;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20002,
'Password cannot be changed sooner than 10 days after last change');
END;
END IF;
This code pulls the expiry_date and grace_period values for the profile and loads them into local variables. The two are added together and the current date (sysdate) is subtracted from it. The resulting value is evaluated against the total of the expiry_date and grace_period values minus nine days. If the condition returns false (indicating that it has been less than 10 days since the password was changed) no rows are returned and the exception raises an error. If the condition is true, one row is returned and the exception is ignored.
Several customers have had problems with their Reports servers after applying the latest Windows security patches.
Apparently the patches update security policy on users' My Documents folders.
Unfortunately, Oracle's default temp location for Reports is in the installing user's My Documents folder. I'm not sure which user Oracle runs as, but after the security policy change, the Reports server is no longer able to write to the temp location.
The fix is to change the temp location in the Windows registry. The REPORTS_TMP key is found at HKEY_LOCAL MACHINE>SOFTWARE>ORACLE>HOME1. We changed the string value to C:\TEMP, rebooted the server, and Reports works fine.
Talked a customer through applying CPUApr2006 to his 9i 9.2.0.6 database servers and 10g 9.2.0.4 application servers yesterday. Ran into problems with the cscript //nologo install.js again. The problems are caused by ORACLE_HOME, JDK_HOME, and PATH envrionment variables not being set-up correctly. Here's the proper way to set the variables in a command prompt window:
set ORACLE_HOME=[path to oracle home, e.g d:\oracle\infra904]
set JDK_HOME=%ORACLE_HOME%\jdk
set PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin;C:\Program Files\Oracle\oui\bin\win32; %SystemRoot%\system32;%PATH%
set CLASSPATH=.;jlib\OraInstaller.jar;jlib\xmlparserv2.jar;jlib\jewt4.jar;jlib\share.jar; jlib\srvm.jar;%CLASSPATH%
It's important to remember the install.js script is case-sensitive and the ORACLE_HOME envrionment variable must match exactly the path found in the \Program Files\Oracle\Inventory\ContentsXML\inventory.xml file.
Oracle Support called today to work on the OID plug-in TAR. We went through the debugging messages and ldap log files and discovered that if we placed the ### modify plugin ### section of the pluginreg.dat file before the ### add plugin ### section, the pre_add trigger worked. Why? Only the engineer who designed the Oracle ldap server knows. I wrote a short how-to.
Next is working on the PL/SQL code for the triggers to enforce our application's password policies.
Got the code working in Forms to update a user's RAD, and then it stopped working again. The error message states it can't find the GUID of the user when it tries to delete the existing RAD. I am having URL issues with the OAS 10g installation on my machine; sometimes it builds a URL with the fully-qualified hostname of the machine, and other times it builds the URL with only the hostname, truncating the domain. I don't know why this would affect my Form code, but you never know with Oracle.
Requested a status update on the TAR I opened on 2006.04.11. This is the response I got:
I have not made sufficient progress on this to provide an action plan. From the information you have already provided, the action plan for the known is sues where the pre modification triggers do fire and pre add triggers do not fir e has already been actioned.
I hate Oracle support, but somedays I hate them more than others. This is one of those days.
Got the sample code working and began adapting it for use with our change password .fmx. Had some issues, but solved them by granting execute privileges to the user's role and creating a public synonym for the sample package. Still not working though. Placing debug messages in the .fmb code because I don't want to spend a half-day configuring Forms Builder to run an OC4J instance on my machine - it's always a hassle.
No reponse to the TAR opened yesterday, except my boss got a phone call from Oracle wanting to know who we worked for and what we were working on - guess that's because we use our customer's support identifier.
Worked on getting the sample RAD creation and deletion packages for use with Forms working. Finally got everything to compile, executed, and it's not working. No surprises there.
So tomorrow we figure out why the procedure excecutes but doesn't create a RAD for the user in OID.
Found the TAR I opened last year, followed the steps, and the add user plugin still does not fire. The only difference between then and now is last year I was using OracleAS 10g 9.0.4.0 and now I am using OracleAS 10g 9.0.4.2. So whatever was broke seems to be broke even worse now.
In the meantime I am working with the modify user plug-in to see what needs to be done to synchronize password changes between the database and OID. So far I have already discovered that database password changes do not update the user's Resource Access Descriptor in their OID account. So that's the first problem to figure out. I'm sure there will be more.
Adapted a PL/SQL script to create OID user accounts using the DBMS_LDAP API by querying a database table containing usernames. Works great on our Sun development machine, but crashes after loading 1100+ users on our Win2K machines. While waiting on direction from management I began working with the custom password policy plug-in sample and am at the same place I was at almost a year ago - modify user works but add user does not. Reviewed my notes on this subject, made the corrections I found last year, but the add user plug in is not enforcing the minimum password length of 8 characters. It doesn't help that OID DAS on my development server is mal-forming the URL when I login, requiring a hand-edit of the URL. I don't know if this is contributing the problem or not, but you can never discount anything when dealing with Oracle.
Back to working on an OID/SSO solution to implement case-sensitive passwords for our Forms app users.
Configured a test server to for SSO and created two user accounts in OID that use Resource Access Descriptors (RAD) to login to the Oracle 9i backend database. Discovered the Oracle 9i database will automatically update the RAD when a user has an expired password.
Have to figure out how to do the following:
Explanation of and example custom password policy plug-in for Oracle Internet Directory is contained in Chapter 46 of the Oracle Internet Directory Administrator's Guide.
Chapter 47 of the OID Admin Guide explains how to use an usernames and passwords in an application for external authentication
Customer requires custom password policies, so I'm back to SSO/OID for our Forms and Reports applciation. I have enabled SSO for the ap on a development server and now need to determine the following:
Looked into the protocol.ora file and discovered it is no longer used in Oracle9iR2. Instead, the parameters specified in the old protocol.ora file are now placed in the sqlnet.ora file. The parameters to include/exclude specific IP/hostnames are:
This is how a database server sqlnet.ora file looks configured to accept connections from one IP address (the application/web server):
# SQLNET.ORA Network Configuration File: D:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools. NAMES.DEFAULT_DOMAIN = world #SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) TCP.VALIDNODE_CHECKING=YES TCP.INVITED_NODES=(192.168.1.101)
All other IP addresses are excluded by default.
Still researching database security. Found a page on OTN with white papers discussing database security: http://www.oracle.com/technology/deploy/security/oracle9ir2/index.html
This page contains a security "best practices" paper that contains information on using a protocol.ora file to specify which IP addresses can connect to the listener: Secure Configuration Guide for Oracle9iR2
Continuing my research into preventing unauthorized direct connections to a database server via ODBC or any other client.
Found the Oracle9i Net Services Administration Guide, which provides a very clear and simple explanation of how web clients, application servers, and clients connect and communicate with a database server.
I have been tasked to find a way to prevent unauthorized ODBC connections to our application's 9i database. I will post links to useful articles here:
Some notes:
Figured out how to change the version of JInititator installed the first time a user runs a Forms app:
Reading Frank Nimphius' Blogbuster and found a link to an OTN Forum thread about using javascript to run a Forms app in a browser without a navigation bar.
Investigating moving an OracleAS 10g (9.0.4.2) middle-tier from one metadata repository to another, but quickly learned from reading Section 8.6 of the OracleAS 10g (9.0.4) Administrator's Guide that it is not possible to switch to a pre-existing metadata repository; instead a new one must be created and the contents of the original metadata repository imported into it. So it looks like I'll be de-installing some middle-tiers and recreating them - that appears to be the only way to add a middle-tier to a pre-existing metadata repository.
Found a question and answer about resetting a forgotten password for the Oracle Enterprise Manager 10g (9.0.4) ias_admin superuser account on the OTN forums yesterday. Apparently the procedure provided in the Administrator's Guide is incorrect (why am I not suprised?). The correct procedure is included in the release notes (which is why I should read the release notes more often). I abbreviated the procedure and put it in the How-To section. You can find it here.
Wrote a lessons-learned on how to fix the problem with an upgraded database not autostarting with its Windows service and a how-to on building an Oracle wallet using OpenSSL-generated root and server certificates.
Cruising the OTN Forums, I found a reference to moving a middle-tier application server to a new infrastructure. The instructions are contained in Chapter 8, Changing Infrastructure Services of the OracleAS 10g Administrator's Guide. Specific instructions for changing the metadata repository used by a middle-tier instance are contained in Section 8.6.
Spent the past several days talking a customer through application of CPUOct2005 on two 10g (9.0.4) application servers. Ran into problems running cscript //nologo install.js again but discovered that the case-sensitivity is determined by the inventory.xml located at \Program Files\Oracle\Inventory\ContentsXML\. Also had problems running the 9.0.4.2 patchset; it was complaining that files were in use even though all Oracle services had been shut down. Searching through an OTN Forum I found several different solutions, but the one that worked was stopping the Distributed Transaction Cooridinator service. I wrote a very short how-to, Applying CPUOct2005, and placed in the How-Tos section.
Pulled off the CPU job to evaluate a solution for our Reports load-balancing problem. Solution requires creation of a global variable that is assigned the name of the middle-tier's in-process reports server. This value is used by the calling .fmb when it creates the URL passed to the rwservlet; don't understand it fully yet but it does work. I will write a full how-to when I've got it figured out. That may a while; testing of a new release is taking priority.
Working with a customer to install CPU October 2005 on his 9i database servers and 10g (9.0.4.) application servers. Ran into the following problems:
Started renaming the files, but after five were reported "in use" by the Univeral Installer, went with the second option. Didn't try closing Windows Explorer, but if it happens again that will be the next step. Waiting on the customer to call back to see if second work-around fixed the problem.
The answer to the metadata repository upgrade question was yes. I have listed the Oracle Note with instructions in the Reference section.
It took most of the day, but I talked the customer through the process, installing a 9.2.0.5 database and then upgrading the metadata repository on his two 10 (9.0.4) application servers. We also had to install the 9.0.4.2 patchset before we could upgrade the metadata repository.
Now the same customer needs to install the Critical Patch Update October 2005 on his database and application servers. I am currently running testing that process on a development server here. The basic steps are:
I have already encountered one problem. Oracle returned a ORA-00904: "SYS"."DBMS_EXPORT_EXTENSION"."FUNC_INDEX_DEFAULT": invalid identifier error when I attempted to export the metadata repository database with the 9.2.0.7 export utility. The workaround provided by Oracle was to use a previous version of the export utility; I changed directories to the infrastructure home and used the 9.0.1.5 export utility installed with the baseline 9.0.4 application server. Worked like a charm.
BTW: Still no response from Oracle Support on the clustered Reports servers. We have advised our customer to proceed with only one stand-alone Reports server until we get a fix from Oracle.
Still nothing from Oracle Support on the Reports server issue. One thing I forgot to mention was that I was able to create comma delimited text files in the Reports server cache directories that worked just like .pdf files except they have .txt file extensions instead of .csv. Unfortunately, our application's System Requirements Specification states it will produce certain reports with a .csv extension. So,unless the SRS changes, we're stuck with either re-coding all the forms that call .csv reports, or waiting until we get Reports servers that hold a job through completion, like sticky sessions for HTTP.
Another issue arose today - the customer who I installed OracleAS 10g (9.0.4.2) for says his network security scanner is complaining the Oracle 9i 9.0.1.5.1 database used by the metadata repository has a security vulnerability that must be patched. I took a look on OTN and MetaLink and discovered that the 9.0.1.5 database is no longer supported EXCEPT for OracleAS 10g (9.0.4) metadata repositories - but the only patches issued will be through OracleAS 10g Critical Patch Updates. I applied the most current CPU (July 2005) when I installed the application servers, so obviously this vulnerability was never patched at the 9.0.1 version level. I opened a TAR asking if I can upgrade/migrate the metadata repository into a 9.2.0.5 database. I'm looking at it this way - the upgrade/migrate will be another Oracle learning experience.
Worked a kludge for the .csv reports. As designed, the reports are created in the c:\temp directory on the Reports server's host machine. However, we call our reports by a URL that is stored in a parameter table in the application's database. This URL points to the primary server. The report exectuable references the URL parameter when it calls the built-in web.show_document. Therefore the primary server must be able to find the .csv file in its c:\temp directory. I dug into the .fmb for one of our reports with the .csv option and figured out how the report got its name and where it was created ( set_report_object_property(report_id, report_desname, 'c:\temp\'||file_id||'.csv');). I shared the c:\temp directory on the primary server and then mapped the drive on the secondary server. Because of windows permission rules, I had to go into the property settings for the Oracle Process Manager and Application Server controls and set them to log on as the same user that mapped the share. Now, when the secondary is called upon to create a .csv report, it creates it in the c:\temp directory of the primary server. When the URL is passed to the primary server, it can find the .csv file in its c:\temp. One problem remains however. To provide consistency in the forms executables that call the reports, I attempted to map c:\temp on the primary server to itself, but for some reason the primary server's Oracle HTTP server will not recognize the shared drive. This means the .fmbs for the primary server have to use:
set_report_object_property(report_id, report_desname, 'c:\temp\'||file_id||'.csv');
while the secondary server's .fmbs have to use:set_report_object_property(report_id, report_desname, 'z:\'||file_id||'.csv');
I don't know if this is acceptable to configuration management or not.
Renamed the in-process Reports servers and everything seemed to work great at first - job_ids and caches were in sync and I could run reports from either either mid-tier, but there was one critical factor that did not work: if I shut down the primary middle-tier (the one specified in the URL that executes the report) then I can't run a report because the Report server on the middle-tier is down . . .
Indeed, I think that with identically named in-process servers, they act as if clustered (even though OEM 10g reports the servers are not clustered). Attempts to restart the Reports server on the secondary server result in an error message complaining OPMN cannot find the Reports server, even though it's status is shown as "up." When I shut down the primary middle-tier, no status is available for the Reports server on the secondary middle-tier.
So, I'm still stuck waiting for Oracle Support to come up with a solution - or no solution - for using stand-alone clustered Reports servers.
Decided to re-try using the in-process Reports servers. Turned off the stand-alone Reports servers and renamed the in-process servers to "repserv." It works beautifully for pdf reports - both servers automatically sync jobnumbers and the reports queues are identical. But .csv reports do not work (the reason why we attempted to use stand-alone clustered Reports servers). Our application sends the report request to the URL of the primary server. For pdf reports, the in-process servers act like they are clustered (even though they are not - I think), but the csv reports are placed in the c:\temp directory instead of the reports cache on the machine that is assigned the job. Because a specific URL is used in the Reports command line, it looks in that host's c:\temp directory for the .csv file. If the .csv file was created on the secondary server, an HTTP 404 File Not Found error is generated because the HTTP server is trying to serve a file that does not exist in it's c:\temp directory.
I'm going to read the Publishing Reports to the Web guide and see what the difference is between how we call the pdf reports and the csv reports.
I also discovered some strange behavior with the middle tiers - if I shut the primary middle-tier down, I cannot get Forms to run on the secondary, even when I go directly to the secondary. As soon as I restart the primary middle-tier, the secondary's forms start working again. However, if I shut down the secondary middle-tier, the primary is not affected at all. This occurs even with the site-to-server mapping from the primary's web cache to the secondary removed. Weird.
No word back from Oracle Support today. But, I got to thinking, what if I rename the in-process Reports servers to "repserv" (the name required by our Forms application)? Can two in-process Reports servers operate on the same subnet? We'll find out tomorrow.
No progress with the Reports servers. Uploaded configuration files to Oracle Support; they say this is a "known problem" and they are checking on the status of a fix. . .
In the meantime I exported the metadata repository database and attempted to backup the Oracle Internet Directory LDAP server data, but the script keeps telling me I need to provide the correct database password for the OID user. The OID Admin guide claims that the password is the same as the password created for the Application Server Adminstrator, but that password does not work. Risked ruin by running the oidpasswd utility, which ran sucessfully, but the back-up ommand (ldifwrite) still asks for the password. I'm sure it's something like the CLASSPATH environment variable not being set correctly or some other blasted obscure non-intuitive setting. . .
One of our IT guys kindly explained how to get a Windows service to log-on as a user so mapped drives could be accessed and the correct way of specifying the mapped drive in the configuration file. So both Reports servers now use the same cache, but the problem of syncing the job_ids still exists. I tried to map the secondary Reports server to use the primary Reports server's persistent file, but it does not work. Although the server gives the indication it has started, it cannot be connected to.
Oracle Support requested additional information on patchsets and patches applied, so I sent them the full set of trace files from both servers too. Waiting for guidance.
Began testing the Reports servers again this morning. Attempted to direct the secondary middle-tier's Reports server to use the cache and persist file on the primary middle-tier, but the secondary Reports server dies on start-up. A look at the trace file showed this error:
[2005/10/3 9:44:57:104] Exception 54005 (): Unable to create cache directory Z:\cache
exception oracle.reports.RWException {
oracle.reports.RWError[] errorChain={struct oracle.reports.RWError {
int errorCode=54005,
java.lang.String errorString="Unable to create cache directory Z:\cache",
java.lang.String moduleName="REP"
}}
}
Just in case the Reports server was really trying to create the cache directory on the primary server I entered the value "test" and got the same error. I don't know if this is a permissions issue or if it can't read the mapped drive for some reason.
Reports testing was halted by a hardware crash on the main development server. Spent a day and half rebuilding it from re-formatting the drives to reinstalling the OracleAS 10g Infrastructure and Middle-Tier application servers. In the process learned a couple of things:
Hope to pick-up where I left on troubleshooting the clustered Reports servers on Monday. Oracle Support reports they are receiving "conflicting information" internally and are still researching the issue. . .
Working Reports server load-balancing issues again. The last configuration we attempted used two identically named stand-alone servers on each middle-tier, however, we have since learned that identically named servers cannot exist on the same network subnet. So, back to the drawinging board.
Trying to get clustered Reports servers to work right now. This was the first configuration we tried, but we kept getting intermittent errors, or the server would serve up the wrong report. Found a note on Metalink describing a bug in the servers causing them to fail if one in the cluster was shut down. Applied Patchset 2 (9.0.4.2) and the bug patch. Servers seem to stay up now, but still having issues getting the servers to consistently serve the correct reports. They seem to hand jobs off mid-way through, so the job-ids of the reports created do not exist in the other server's cache.
Found a note describing setting up clustered Reports servers for 9iAS Rel 2 which states all servers must use the same cache. This is not mentioned in the 10g (9.0.4) Reports publishing guide. But it makes sense. The trick is to also get the servers to use the same "persistent file" (report_server.dat, stored in [ORACLE_HOME]\reports\server) which is where information on previous jobs is stored and where it seems to get its info for setting job ids.
Verified that I can empty the cache directory and rename or delete the persistent file to reset the job ids used by the reports servers. Next step is to set up a central cache and reconfigure one of the servers to store reports in it and to use the same persistent file.
Applied CPU July 2005 to one our development 9.2.0.5 databases. No problems (other than the usual operator errors) noted.
Customer reports a problem with a new computer not loading JInitiator in Windows IE. This customer is off-site. They say it worked fine on Friday, but not today. Loaded some between Friday and now, but it shouldn't be the problem - other machines have the same software loaded and run JInitiator fine. Told them to try holding the Ctrl key down and to make sure they're using a fresh instance of IE to make sure there are no conflicts with other plug-ins.
UPDATE: Discovered the last data admin created some hack batch file to update the certdb.txt file instead of DOING THE JOB RIGHT and creating a new self-extracting zip file for the users to download from the app's home page. So, what happens is the users download an outdated certdb.txt and then they run the hack batch file to load the correct certdb.txt.
Why? Who knows?
Who knows. . .
Busy with lots of non-Oracle tasks the past few weeks.
Tried to set up a virtual host for a forms development server and learned in the process that although you can direct the initial form to open from a second directory, all subsequent forms will open from the first directory it is found in. Adding the second directory to the FORMS90_PATH environment variable does not ensure all subsequent forms requests will actually come from the directory they're called from. In retrospect, this is pretty damned obvious, but as usual I had to learn it the hard way. It would be possible to set up two identical development directories and keep them seperate using different configurations in formsweb.cfg, but our app does not use formsweb.cfg - it uses a hacked html file with the formsweb.cfg parameters in it instead.
Have been writing software design documents (SDDs) for a couple of simple modules we'll use in the next release of our Forms and Reports app.
Have also been working with Oracle Support on the custom password policy plug-in problem (see 2005.06.16 entry). Solution was to add "orclpluginisreplace: 0" to the policy module definitions. If this line is not added, the default value is 0 (disabled), but for some reason known only to the Oracle Gods, it had to be explicitly set in the definition for the pre_add policy to work correctly.
Tried a fix for an old problem - jpegs in a html report not displayed on a secondary middle-tier unless the secondary middle-tier's web cache was enabled. I opened a TAR for this problem and although it took a while (my contact had to put the question out on an mailing list internal to Oracle), the suggested fix - setting ServerName in the secondary middle tier's httpd.conf to the main Web Cache server - seems to work.
Yay!
Otherwise I have been busy writing a software design document for a module in our latest development project. Soon I'll get to write some code! Real code! That will be used again and again and again!
Yay!
Browsing the OTN forums this morning and found a link to the OracleŽ Application Server Containers for J2EE Standalone User's Guide 10g Release 2 (10.1.2). It has the following definition of the OC4J standalone instance used in the 10g Developer Suite:
OC4J Standalone is for use by development and small-medium scale production deployments. Specifically, OC4J Standalone supports HTTP and HTTPS natively without the use of Oracle HTTP Server. It does not have support for load balancing, clustering, or management through Oracle Enterprise Manager 10g. To use those features, customers must install one of the Oracle Application Server installation types, such as J2EE + WebCache. The standalone version is supported in a single instance, single JVM, and single machine configuration.
Good stuff to know.
Spent the day in a peer review of software design documents and trying to figure out why I couldn't run a form on my desktop machine using Forms Builder 10g.
My desktop runs WinXP SP2 - when I tried to run the form, an IE window opened, but all I got was the contents of a temp file that looked like this:
<html> <head> ORACLE FORMS.</head>
<body onload="document.pform.submit();" >
<form name="pform" action="http://foo.bar.com:8889/forms90/f90servlet" method="POST">
<input type="hidden" name="form" value="C:\code_10g\code_GRANITE.fmx">
<input type="hidden" name="userid" value="TEST005/TEST005@tdev1">
<input type="hidden" name="obr" value="yes">
<input type="hidden" name="array" value="YES">
</form> </body></html>
It took some time digging through OTN's Developer Forum, but I found a post from someone experiencing the same problem. The solution was to hit the F5 key, which would cause IE to execute the html. When I did this, I got a warning message about active content. So I went to Tools > Internet Options > Advanced, and checked the "Allow active content to run in files in My Computer" in the Security section.
Voila. Forms now run without a hitch.
Lots of good info on creating custom plug-ins for Oracle Internet Directory in chapter 5 of the Oracle Internet Directory Application Developer's Guide
Got Oracle's custom password policy plug-in installed and half of it works. The custom policy is used for password modification but not password creation. I had to turn off password syntax checking using the Oracle Directory Manager to get the password modification to work (not in the instructions!) but haven't been able to figure out what needs to be turned off/on for the password creation policy to work.
The good news is, SSO/OID will solve our password policy enforcement problem - if we let it. . .
A few notes on what I did to enable SSO logins for our Forms app:
Today I'll try implementing a custom password verification plug-in that ensures SSO passwords meet our customer's min/max length and numerical and special characters requirements.
Got OID/SSO working. Now need to figure out how to sync OID and RAD password creation/expiration and extend OID password policy to meet customer's password rules. Info on developing OID plug-ins contained here in the OID Application Developer's Guide and a how-to example here in the OID Admin Guide.
Also found an example for calling an external password verification program.
Continued researching using OID/SSO with our Forms and Reports application. Found this informative demo.
Configured a virtual host on our OracleAS 10g development server. I did this once before, but the second time around was like re-inventing the wheel, so I wrote a how-to.
An interesting and useful piece of Oracle sysadmin knowledge I found in section 4 of the OracleAS 10g (9.0.4) Security Guide:
Load balancers are often used with or contain HTTPS-to-HTTP protocol-converting appliances. These devices can be purchased from a number of vendors and can achieve rates of thousands of SSL key exchange sessions per second or higher. (By comparison, 500MHz Intel/UNIX systems can achieve only 20-30 SSL key exchanges per second, 60-90 exchanges if cryptography accelerator boards are used.) We strongly recommend HTTPS-to-HTTP protocol converting devices. Without these devices, as much as two-thirds of the CPU of a site's HTTP CPU cycles can be consumed by SSL operations--see the results of the SPECweb99_SSL benchmarks.
In short - SSL eats CPU cycles. Good to know tuning info.
Continued researching SSO/OID today. Read the OracleAS 10g (9.0.4) Security Guide, which provides a good overview of the roles of SSO and OID, but little how-to information. Tomorrow I dive into the OID Admin Guide.
Load-balancing Reports servers has been pushed to the side for the moment. New task is evaluating Oracle Internet Directory and Single Sign-On as a possible solution to implementing case-sensitive passwords for our Forms application (currently users log directly into the back end Oracle 9i database when they connect to Forms.)
Took a while searching through Oracle's 10g Documentation Library and the OTN Forums, but I found a couple of promising leads, including this white paper: OracleAS 10g (9.0.4) Forms Single Sign-On.
Two questions:
1. How to sync user/passwords in Oracle Internet Directory and the back-end database?
2. Single Sign-On passwords are case-sensitive (good) and must conform to OID rules. What are OID password rules and can they be changed?
Didn't really "solve" the ping crash problem - just worked around it. If it were solved the in-process reports server would respond correctly to opmn's pings. . .
Visited customer who was attempting to install and configure a second middle-tier. Their Reports servers were not working correctly. After several reconfigures of in-process and stand-alone Reports servers, we observed that only one of the Reports servers was serving reports regardless of which middle-tier initiated the report. We also observed that we could not get two Reports servers running at the same time - if we started the Reports server on the primary middle-tier and then tried to start the Reports server on the secondary middle-tier, the secondary middle-tier Reports server would die immediately after starting. This also happened if the secondary middle-tier's Reports server was started first - the primary middle-tier's Reports server would die immediately after starting.
We contacted the local Oracle Support office at the customer's site, but they were unable to help. They didn't seem to know much about 10g Forms and Reports and didn't really seem to care to learn.
The best guess I could make was the infrastructure's metadata repository had been corrupted. I researched the Oracle Distributed Configuration Management Reference Guide and learned the metadata repository contains all the configuration information for the middle-tier's components - the HTTP server, OC4J instances, Web Cache, and of course, Reports servers. That's why it's so important to issue the dcmctl updateconfig command after hand-editing any of the components' configuration files. Somewhere during the many reconfigurations we went through trying to get Reports to work correctly, we corrupted the metadata causing it to only recoginize one report server (I think).
We decided to tear the installation down and rebuild it from scratch, but the infrastructure install hung during the automatic database configuration agent (the customer was installing on VMWare virtual servers running Windows 2000 server). We'll see what happens when the customer gets it re-installed. Meanwhile I'm still working on the secondary middle-tier's Web Cache issue via TAR.
Solved the ping crash problem and the JPEG problem.
Ping crash: Solved ping crashes by disabling the in-process reports servers on both middle-tiers by modifying opmn.xml (deleted rwservlet ping property) and targets.xml (removed reports server ias-component section). Installed stand-alone reports servers named repserv on both middle-tiers. Ping crashes solved.
JPEG: The Web Cache on the secondary middle-tier was disabled for security reasons (to ensure users could only access the application through the primary server's Web Cache). I enabled the secondary server's Web Cache and now the JPEGs display correctly. Don't know why, but they do. Opened yet another TAR to look into it because we really don't want users to access the secondary middle-tier directly.
The server's working again but I'm not sure I know why.
Found Note 261867.1 "Slow Response of Reports 10g Ping URL Causes OPMN To Restart OC4J_BI_FORMS" on MetaLink this morning. It refered me to the ipm.log in \ORACLE_HOME\opmn\logs where I found errors relating to the in-process reports server not responding to OPMN's pings. OPMN was restarting the OC4J_BI_Forms process because of the failed pings. Tried the Note's fix of adding a ping timeout property to opmn.xml, but that hosed up the whole shebang, causing something to eat up all the CPU cycles and system memory. I updated the TAR I opened on Friday with this info, and Oracle's response mentioned osagent.exe crashing, preventing communication with the Reports servlet. Osagent.exe does crash intermittently on the server, so I tried their fix which was adding a section to opmn.xml instructing opmn to poll osagent.exe and re-start if necessary. Too bad it didn't work - opmnctl kept complaining it couldn't find the directory or file for osagent.exe. I tried every possible path combination I could think of, but it still wouldn't work.
So, I put everything back the way it was, re-started opmn and all processes, updated the metadata repository for both opmn and oc4j, and restarted OEM. And now it works again. This leads me to believe that the start-up sequence is important - if OEM starts before OPMN and the OC4J instances have finished their intialization routines it causes problems. I went into Windows Services and changed the OracleASProcessManger (opmn) and OracleASControl (OEM) to manual start so the next time I reboot I can start opmn manually and then OEM. MAYBE that's the key. . .
Arrrrrrrrrrrrrrrrgggggggggggggggggggghhhhhhhhhhh!
OC4J_BI_Forms is crashing again after a re-boot. Spent the entire day messing with it. Opened a TAR - got a call from Oracle within 10 minutes. They're looking into it.
ARRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGHHHHHHHHHHH!
Don't know why the OC4J_BI_Forms instance was crashing on the second middle-tier. I reconfigured the in-process Reports server to it's default configuration, confirmed it worked, then reconfigured it for our application. It's been working fine for almost twenty-four hours now, and the Web Cache is load-balancing between the middle-tiers. This looks like a good configuration - allowing each OC4J_BI_Forms instance to run its own in-process Reports server independently - and unless something goes horribly wrong I'm leaving it alone.
Only one minor cosmetic issue remains - OEM is not reporting the correct number of OC4J virtual machines on the primary middle-tier. It is configured for four machines. opmnctl status reports the correct number, but OEM's drill-down page for the OC4J_BI_Forms shows only two. A quick search of MetaLink revealed no clues, but then quick-searching MetaLink rarely does. This is something I'll work on when I have nothing else to do.
Renamed the Reports servers to remove the cluster defintion (server.cluster to server) and Reports works fine on both servers. But now I'm getting random Form disconnects on my second middle-tier. . .
It's always "But" with Oracle.
Spent some time this morning searching through MetaLink and found a Reports server cluster configuration question. Oracle's response was to NOT cluster the in-process Reports servers for load-balancing, instead to rely on other High Availability features such as the Web Cache to distribute the load. This makes sense - let the OC4J's in-process server handle any Reports requests from the users connected to it. And this should also solve the configuration questions - each in-process server will act independently.
I think this should work, since the only time I will require additional Reports servers is when I install additional middle-tiers. When I get a moment I'll rename the in-process servers and give it a go.
The osagent.exe doesn't seem to be related to my reports problems. . . Turned on tracing for the Reports servers, ran reports until a FRM-41217 error was generated and it looks like the one Reports server was assigned the task of preparing the report and the other was assigned the task of presenting it (used the jobID sequence to identify which Reports server did what - the jobIDs are autoincremented but are generated by the server assigned the task - the sequences are not identical so I can tell which server is which). Of course, the jobIDs don't match. Need to figure out how to sync these jobIDs - where do they come from and where are they stored?
Tied-up working another project. . . Got the Note from Oracle, took a quick read, noticed a reference to osagent.exe, which suprisingly enough, was crashing on the smaller of my two development servers. Osagent.exe is a service that brokers communication between programs. Searched MetaLink for "osagent" and the first hit was for a patchset with reference to osagent and reports servers and FRM-41213 errors. Hope this isn't another dead-end path.
Changed the minEngine property to "1" in the servers' .conf files, but still getting a hang on the initial request followed by a couple of good reports followed by our old friend FRM-41217 "Unable to get job status" errors. I also got a REP-52251 error.
Found Note:218835.1 "FRM-41213 When Calling Report from Forms Using Reports In-process Server", which indicates the in-process server's must be started first with an independent call via HTTP to the rwservlet before it will respond to a RUN_REPORT_OBJECT call from Forms, to which all I can say is "crap." Actually, I can say alot more than that, but I won't. I will say that I am very frustrated and am ready to abandon the in-process Reports server for a stand-alone server.
I updated the TAR yesterday and got a note back referring me to Note 291955.1, which MetaLink says it cannot find. When I find out what's in that Note I'll make the decision whether or not to give-up on using the in-process Reports server.
Looks like the FRM-52251 and FRM-41217 errors are fixed, but now I'm getting a lot of FRM-41213 "Unable to contact report server" errors after restarting the OC4J_BI_Forms processes that contain the Reports servers. I think this may be a result of the minEngine property being set by default to 0 in the servers' .conf files. I'll look into it tomorrow.
Still working on getting the in-process Reports servers to operate correctly.
Seached Metalink for info on the REP-52251, REP-51002, and FRM-41217 errors. Found Note 289666.1 (REP-52251: Cannot Get Output Of Job Id) which indicates the problem is caused by Oracle's Single Sign-On (SSO) feature. Reports servers are configured for SSO by default. When a Form requests a Report, it is supposed to provide user credentials. Apparently, if you request the same report serveral times, the user credentials get passed to the Reports server or it recoginizes them, or something, and it will present the report. This is what is happening in my case - I get a FRM-41217 error followed by a REP-52251 error, followed by the report. If I request a different report, sometimes I will get it and sometimes I have to go through the errors again. The REP-51002 error seems to have disappeared.
Our application does not use SSO, and I have modified the rwservlet.properties and repserv.conf files to turn SSO off in Reports, but the errors continue. Note 289666.1 also indicates that SSO must be turned-off for both Forms and Reports, but of course it gives no instructions for turning SSO off for Forms. . . I glanced at a couple of configuration files in the Forms90 directory, but didn't find anything obvious.
UPDATE: I made one more tweak to the rwservlet.properties file by commenting out a line providing an OID identity. This seems to have done the trick. I ran several reports from several different sessions and no errors were generated. However, the initial report request hung, and I beleive that may have been caused by the Reports server having to initialize an engine. The minEngine parameter in rwservlet.properties is "0." I changed it to "1". This appears to have solved that problem.
Spent the last three days with a customer and returned today to continue working the Reports issue.
The SSO issue was caused by not stripping the <security></security> section from a server.cluster.conf file.
But I'm still getting intermittent REP-51002, REP-52251, and FRM-41217 errors. Most of them seem to come from one middle-tier. I also noticed that although OEM reports the OC4J_BI_Forms servlet's status correctly, it reports the detailed metrics such as CPU and Memory usage are unavailable. The other middle-tier in the farm does not have this problem and it's reports cluster seems to work fine when a report is passed to it. I spent some time searching MetaLink for information on OC4J servlets and OEM, but found very little (whereas there is a LOT of information on troubleshooting Reports servers, both stand-alone and in-process.
My hunch is if I can get the OC4J_BI_Forms process to properly report its status to OEM I may get rid of some of the problems with the in-process Reports servers. I think the information that is displayed in OEM for the OC4J_BI_Forms process is stored in the infrastructure's metadata repository. That's what I'll work on Monday.
Also - no response from Oracle to my Monday TAR update.
Spent the day working on the Reports server issue. Oracle Support sugggested increasing the number of OC4J_BI_Forms instances from 1 to 4, which I did, but the stand alone reports servers were still having problems.
I found Oracle Note:242610.1 "How to Rename Default in-process Reports Server Name" on MetaLink and decided to give that a try after deinstalling and deregistering the stand-alone Reports servers on the two test machines. I renamed the in-process servers and got them to work a couple of times, but OEM still wasn't reporting their status correctly. Changed a few more parameters in the targets.xml files to show the new name, ran the dcmctl update config -ct opmn -v -d and dcmctl resyncinstance -v -d commands to resync the infrastructure, and now OEM reports the status of the renamed in-process servers correctly, but (BUT - it's always BUT) now I'm getting the SSO log-in page when I try to run a report from our application. . .
For what it's worth, the Reports server status page is reporting the jobs completing successfully. I checked the respective Reports server's .conf files and made sure the <security> tags had not been reinsterted (removing them was how I originally turned off the SSO log-in page). I updated the TAR, but since I'm headed up to a customer's site for the next three days I won't be able to work on this until Friday. I'm sure it's something simple in a configuration file. Obscure, but simple.
When I get this solved I'll write a how-to and then tell the boss I need to update the customer's install guide with new configuration.
Something I learned about OracleAS 10g today:
If a mid-tier component's (like the Oracle HTTP server) configuration is manually updated, the dcmctl -updateConfig -ct <component> command must be run to update the metadata repository in the infrastructure.
The Reports issue has dropped to the wayside. I have been busy updating our application's implementation guide for our customer as well as doing some minor re-design of the application's three html files. I have been passing info back and forth to Oracle via the TAR I opened, but no real progress as of yet.
In other news, I bought a used copy of Oracle Application Server 10g Administration Handbook and it is chock-full of useful information, even if most of it is geared towards *NIX installations. It's nice having the info provided in a sequential manner rather than randomly picking up bits here and there through Oracle white papers and notes.
Got most of the Reports issue solved. Had to log a TAR to find the final key that allowed me to de-register the Reports servers with OEM and OPMN (it's a dcmctl command - I put the Oracle Note in the reference section). Everything works great UNLESS I register the Reports servers with OEM and OPMN. If I do that, they have a bad habit of crashing the OC4J_BI_Forms instance. I'm guessing this is buggy behavior and I don't really care - I de-registered them and if a Reports server needs to be restarted, it can be done from the Windows Services panel.
Tomorrow I start writing the customer's how-to guide. Joy.
Still working on Reports. Got it to run on the individual machines, but I need to load balance it as well as Forms. Working on setting up a Reports cluster, which is working on the primary machine, but not working on the secondary. When I attempt to run a report on the secondary machine it crashes the OC4J instance, taking Forms with it. It may have something to do with a stand-alone Reports server I added to the Oracle Enterprise Manager (OEM) and Oracle Process Notification and Managment Server (OPMN) and then de-installed while I was configuring the reports cluster ("cluster" - what an apt name for all this). Of course, the Reports documentation provides excellent instructions on how to add a Reports server to OEM and OPMN, but absolutely nothing is to be found on how to remove it when you no longer need it. Posted a question on the MetaLink Reports forum; we'll see what the boys and girls in India have to say about it tomorrow.
Spent the day installing an OracleAS 10g middle tier application server and configuring Web Cache for load balancing two OC4J instances. Also captured screen shots of everything for the customer's installation guide. Got most of it working (and I only had to read three Oracle notes after following the installation guide!), but I can't run reports and it's too late to troubleshoot. Our Forms application works however, and the web cache appears to be balancing the load. I'll pick up where I left off in the morning.
Upgrade was successful. Of course there was more work required (patching the database server up to the most current security patch) and a couple of long nights. Also encountered two problems not encountered before: ORA-12638 Credential Retrieveal Failed when attemping a SQLNet connection across Windows Active Directory and JInitiator requiring both the server certificate and DOD Root CA certificate in its certdb.txt file. I created a new Lessons Learned section where I put the full details and solutions for future reference.
I also added a how-to for creating and installing a Reports 60 server as an NT service and a link to the Oracle Application Server Forms Services 10g (9.0.4) Capacity Planning Guide white paper.
Currently preparing to upgrade a customer's installation. Practicing removing an 8i database server, installing and configuring a 9i database server, importing the 8i data into the 9i database and then running a series of SQL scripts to upgrade the data for the latest release of our application. It's a nice change from reading about load-balancing and a good SQL refresher.
Played around with JMeter in between performing other non-sys admin related duties (peer review). Even running 100 threads from a remote server (an unused desktop I enlisted for the task) slows my desktop machine way down. . .
Still haven't gotten the Oracle (read: Apache) HTTP server to roll over and cry uncle. It slows down, but my average response time stays below 4 seconds. I guess that's good.
I also looked at the Mercury LoadRunner. It apparently has the ablility to test Oracle Forms applications. But I bet it costs too much. . .
If I can figure out how to get JMeter to fire-up JInititator and fill out a form, I'll have it made.
What have I learned about JMeter today?
That I can crash my pc quicker than I can crash an Oracle HTTP (read Apache) Server.
JMeter spawns threads that act as virtual users, each generating whatever request you configure it for. Each thread requires resources, namely memory, on the JMeter server machine. For my desktop with 256Mb, I can spawn about 50 threads before my pc sinks down into a page-swapping quagmire that takes over five minutes to recover from.
Fortunately, you can setup JMeter servers on any number of machines and start them from one JMeter client GUI. This allows you to sidestep the very finite boundry of your own system's resources to simulate concurrent users generating HTTP requests to the HTTP server.
I set up a JMeter server on another desktop and simulated 100 concurrent users making HTTP requests, but have to crash the HTTP server. It gets slow, but doesn't crash. Tomorrow I will enlist a couple of other machines as JMeter servers and we'll see if we can't make some smoke. . .
Still playing around with JMeter and load-testing. Found a very good white paper at OTN on capacity planning for Forms 6i. Even though the technology is outdated, the paper explains load-testing methodology in detail using real-world examples. I've added a link to it in the right box under the Forms 6i section.
Spent the morning in a meeting with customers and this afternoon exploring the use of JMeter for load-testing our Forms application. I can set it up to hit the HTML, but not sure if I can get it to work with JInitiator. More research required. . .
Spent the rest of the day researching JInitiator/proxy server problems in the OTN forums. All I've been able to find out is sometimes JInitiator has problems with proxies, and sometimes it doesn't. It appears the type and purpose of the proxy is the root cause. I suspect the proxy is changing something in the IP packets and JInitiator doesn't like it. In some cases the solution was to bypass the proxy (which our customer does not want to do and Oracle claims you don't have to do if using the Forms listener servlet, which we are) and in others it was to contact Oracle Support. . . I sent an e-mail to the customer requesting information about his proxy and its purpose; that info will help me narrow the problem down (I hope).
Working a table promoting our product at a local conference tomorrow. It's going to be nice to get out of the cube for a day!
Confirmed we are not using port 9000 to establish connections to the Forms server.
The issue was the firewall not allowing packets containing some non-standard HTML info through. The firewall is configured to forward all traffic on HTTP (port 80) through to the 9iAS server without inspecting the packets at the application level, i.e. the firewall has been turned-off for port 80 for the 9iAS server.
So now the question is: does the customer's inside proxy server also inspect packets at the application level for non-standard (compliant?) HTML? If it does, then will they configure the proxy to allow all traffic on port 80 through to these servers?
Trying to confirm if we are still sending connection requests to the Forms Listener servlet on port 9000. Asked our network guys if we can log the traffic between the server in our DMZ and a client to reveal the ports used. We'll see what the logs reveal. . .
My brain hurts.
It appears we are using both the Forms listener and Forms listener servlet? The code in our modified basejini.html file calls the servlet, but the formsweb.cfg file calls the listener on port 9000. Having a hard time getting my head around the flow of the requests and the order the parameters are read in. If formsweb.cfg is ignored (which it may be since we call the modified basejini.html directly) then the listener is not involved. But why do we have to open port 9000 in the firewall to complete a connection? What the heck?
I'm going to construct a block diagram of the program flow. Drawing these things out usually helps me understand them better.
I found Oracle Note 206247.1 which confirms my guess that port 9000 must be opened in a firewall to allow connections to a 9iAS Rel 1 (1.0.2.2) server running a Forms6i server.
One solution presented in the Note is to install 9iAS with the Oracle HTTP server on one machine and the Forms6i Server, configured for port 80, on another machine.
Don't think this a viable solution for our customer however.
However, I keep finding hints that by using the Forms listener servlet all communications can be conducted via HTTP on port 80.
What's frustrating is, I still haven't found any instructions for using/configuring the Forms listener servlet.
Posted a question on the OTN Forms forum; we'll see if anyone responds.
Network guys confirmed port 9000 opened in the firewall to allow server to accept forms requests.
Drew a quick diagram of how the Forms6i connection process works as I understand it.
Still reading though - the documenation alludes to a "forms listener servlet" that uses HTTP instead of port 9000.
Of course, none of this is an issue in 9iAS Rel 2 or OracleAS 10g. But our customer has 9iAS Rel 1, so that's what I have to make work.
What I've learned so far about Oracle Forms6i and JInitiator:
What I'm still not sure about:
I suspect the answer to these questions are a) yes and b) yes.
I have running netstat -a from the client and checking for which ports are being used. When I contacted a 9iAS server running the Oracle load balancing cgi, I saw port 9000 being used. When I contacted a single 9iAS server, all I saw was HTTP connections.
I am pretty sure of one thing: the initial request for a form is made to the Forms listener on port 9000. If that isn't open in the firewall, the listener will never respond.
We have one 9iAS server in our DMZ that allows connections from outside the firewall. I'll check with the network folks and see if the port 9000 was opened for it. . .
The Oracle JInitiator - firewall/proxy server issue continues.
Reading a howto on testing Forms6i through a firewall on Linux installations, I read that the port to the Forms server must be opened in the firewall. I'll ask our network guys to try this tomorrow morning.
I also configured natd to redirect remote desktop connections to my home WinXP Pro machine so I can test configurations.
Investigating problems using JInitiator through proxy servers. I copied a few Oracle Notes relating to the subject in the Reference section.
Added a short how-to for using opmnctl with Oracle Application Server 10g
I added a short how-to on removing and installing an Oracle HTTP Server.
Still working on that install guide. Bleh.
Waiting to hear if I need to write a how-to for applying CPU January 2005 to a 9i database server and a 9iAS application server. . .
In the meantime, I'm reviewing an install guide for 9i 9.2.0.5. Fun stuff!
The CPU's Installation Note does say all forms and reports executables must be recompiled, so it looks like I have to patch a Developer6i so I can recompile the forms and reports. . .
Or I may just need to patch the main server.
Completed the CPU for Oracle 9iAS Rel 1 1.0.2.2. Here's a quick summary of Notes and patches required to get a baseline install up to speed:
Using Note 179240.1, Recommended Oracle HTTP Server Post-Install Steps for 9iAS Release 1 (1.0.2.2.x)
Install CPU (patch 4005880):
Installed Patchset 16 for Developer6i and everything works except I can't make a connection to the server through the dev60cgi.exe that's load-balancing the servers. I can make direct connections from a remote client, but going through the load-balancer produces a java.lang.ClassNotFoundException error. The CPU's Installation Note does say all forms and reports executables must be recompiled, so it looks like I have to patch a Developer6i so I can recompile the forms and reports. . .
Still working on CPU January 2005 for 9iAS Rel 1 (1.0.2.2)
Made a WAG today that maybe the 8.1.7 files were in the iSuites Oracle Home. Set up the Oracle Universal Installer, and viola!
It loaded.
Was it that difficult for Oracle to mention this in the CPU Note, that they were referring to the iSuites Home that's created with a Developer6i install?
Of course it was. How else are they going to get people to open TARs and call Support?
The server tested good with the 8.1.7 patch applied. Still don't know about the OPROCMGR patch; it fixes a problem with JServ processes, but we don't use JServ. However, if we apply the patch JInitiator can no longer contact the Forms Server. Of course how the patch affects JInitiator and the Forms Server aren't mentioned anywhere in the patch's README.
Pressing ahead with the CPU. Waiting on Patchset 16 for Developer6i to download so I can upgrade the Forms and Reports servers. May have another problem with JInitiator: the Oracle Universal Installer is reporting a way old version of JInit installed, not the current 1.3.1.9. I don't know if that will cause a problem or not; we'll find out shortly.
Began the process of applying Critical Update Patch (CPU) January 2005 to a 9iAS Release 1 (1.0.2.2) application server.
What a nightmare.
According to the Oracle Note for the CPU, four prerequisite steps must be accomplished on a 9iAS Release 1 (1.0.2.2) application server before the CPU can be applied.
I only got one of them to work, and that's for a component (Oracle Web Cache) our application does not use.
The first step requires installing a 8.1.7.4 patchset (9iAS uses 8i database version files for its infrastructure - I think), because (according to the Note) "this home installed with 8.1.7.0 files), but our baseline installation of the application server apparently installs 8.0.6 files.
So far I haven't been able to verify this or find another patchset that would upgrade the 8.0.6 files to 8.1.7.0. I did however discover that 9iAS Release 1 is "desupported." When I searched MetaLink for 9iAS Release 1 patchsets, all I got were patchsets for 9iAS Release 2. . .
I would open a TAR, but unfortunately my boss's name is on the MetaLink account and regardless of instructions to reply to me, Oracle Support always sends every TAR response to her e-mail.
And she's away at a conference all next week.
So I posted the question on the OTN Application Server - General forum. Hopefully somebody will take pity and put me out of my misery.
Success.
Next is applying the Critical Update Patch 2005 to a Oracle 9iAS 1.0.2.2 application server.
Joy.
After spending twenty minutes yesterday going through the overly-complicated process of creating a TAR, I discovered the patch that was declared obsolete had been restored. Yippee.
But today I got to open a TAR for real, this one because the OPatch utility, which is required for installing the Critical Patch Update, does not want to play nice. It is complaining that it cannot find the oracle home, even though I set the ORACLE_HOME environment variable per the README's instructions. The correct oracle home is even displayed when I run the opatch lsinventory -all command. But when I attempt to run opatch apply, the batch file program complains the oracle home is not registered in the "Central Inventory" (whatever that is). So, after some reading of the opatch user's guide, I found opatch attach -name [ORACLE_HOME]. This runs correctly, reporting it has successfully registered the oracle home in the "Central Inventory." But opatch apply still doesn't work.
So I opened a TAR and I suppose it won't be long before someone tells me to check my PATH, etc., which I have already done. The machine does have multiple Oracle homes (it also has Oracle 9iAS installed for Forms and Reports) however, so I suspect I will have to jiggle something around in the registry to get OPatch to work.
No wonder experienced Oracle DBAs get paid so much. . .
ADDENDUM: Oracle responded to the TAR refering me to a note for this very same problem (opatch error 100). c:\winnt\system32 was missing from the PATH envrionment variable. This solved the problem and opatch ran fine.
But this leads me to ask the question: why doesn't Microsoft make this a default since so almost all the basic system utilities are located at c:\winnt\system32? That's Redmond doing our thinking for us again, I guess. . .
Dear Oracle:
I have spent two days upgrading a 9i database from 9.2.0.1 to 9.2.0.5. I did this so I could apply your "Oracle Critical Patch Update January 2005" to evaluate it against our Forms application.
Getting from 9.2.0.1 to 9.2.0.5 was simple enough, by Oracle standards. But now that I am ready to apply your "Oracle Critical Patch Update January 2005" I find that I have to apply two more patches (Alert 45 and Alert 62) and that your "Oracle Critical Patch Update January 2005" actually consists of two patchsets, one of which you have pulled from your website.
Reading an Oracle Note is a labor in and of itself, but after spending two days deciphering the poor excuse for technicial writing you inflict upon your paying customers, I have a few suggestions:
Sincerely,
Jim Coulter
Oracle DBA Wanna-be
Spent the day teaching myself how to patch 9i from 9.2.0.1 to 9.2.0.5 so I can apply the January 2005 Critical Patch Update. Used the down time between downloading patches and running scripts to redesign this web page, using css from The Layout Resevoir and Owen Brigg's Box Lessons. So far I have made it from 9.2.0.1 to 9.2.0.2; tomorrow I hope to get to 9.2.0.4.
The links in the left box are not working yet. I stil have to move the content into the new layouts.
Installed OracleAS 10g BI Enterprise edition. Configured Web Cache for SSL. Learned how to generate my own signed root and server SSL certificates using OpenSSL. Captured screen shots of every step. Converted, cropped and saved screenshots and pasted them into a Word doc with instructions.
De-installed OracleAS and then re-installed using the instruction manual. Worked the FIRST TIME.
Made minor changes to install guide and added section on testing installation and configuring for SSL-only connections. 247 pages. I'm bushed.
Will add a short how-to on using OpenSSL to create root and server certificates and importing them into Oracle Wallet. Still unable to create a wallet using OpenSSL. Maybe if I have some down time I can mess around with it some more.
Spent the day installing OracleAS 10g BI Enterprise Edition. Everything was going great until it came time to run our forms. Can't find the menu .mmx. Checked all the places we normally set the path to our application directory but it still can't find the .mmx. Tried configuring formsweb.cfg and jserv.properties like we do for a 9iAS Rel 1 installation, but it can't find the .mmx.
Crap.
Began the installation of a full OracleAS 10g app server. Spent most of the day reviewing Oracle documentation. Found an almost complete set of instructions for de-installing Oracle products here.
I am capturing screenshots of all the installation screens for the implementation guide. I have two more days to get a complete draft together. Nothing like a deadline to get me going.
During the de-install I had to re-boot the machine several times. Since I am using a Remote Desktop Connection from my desk to the server, I remembered a handy trick to see if the server was up after the reboot. At a command prompt, type ping -t foo.bar.com. This will constantly ping the re-booting machine. When it begins to respond you can re-establish your connection. Of course, this applies to Microsoft operating systems only. A UNIX ping doesn't require the -t switch - it keeps pinging until you tell it to stop.
Limited success! Or at least some progress. . .
Created a new wallet with OWM and generated a certificate request. Used the sign-server-cert.sh script to generate a signed server cert. OWM allowed this cert to be imported into the wallet. But still need to figure out how to generate a complete wallet with OpenSSL tools.
Boss just said customer wants a full OracleAS 10g install, so creating complete wallets with OpenSSL is now unnecessary. However, I can still use the self-signed root certificate and the sign-server-cert.sh script to generate a test certificate. . .
Found the tar.gz file ssl.ca-0.1.tar.gz with shell scripts for generating self-signed CA certificates, server certificates, and pkcs#12 files here.
Installed them on my home unix box, and after going through the usual three-hour re-training on how to get an executable to execute on a unix box, ran them per the procedure linked below.
OWM still does not like the wallet. Haven't tried using the wallet on the server yet, but that's next.
But if OWM won't open it, I doubt the server will like it either.
2005.02.09 0930
Trying to create an Oracle Wallet with OpenSSL. Found a very brief howto here and an older version here.
Not having much luck using this procedure. Oracle is rejecting the pkcs#12 files I'm creating using the test CA certificate from Thawte.
So far I have tried these combinations:
I have also tried using Oracle Wallet Manager to import the Thawte test CA into an Oracle Wallet and then importing the Thawte-generated site certificate, but the OWM complains that the CA is not recognized.
Next step: use the OpenSSL CA.pl script to generate my own CA and then try to create a site certifcate with an OWM-generated certificate request.
The HTML and CSS for this page was adapted from:
The Layout Reservoir 3 Column Layout
Owen Brigg's Box Lessons
