Configure jobStatusRepository in Oracle Reports 12c

Create the service account

CREATE USER RW_SERVER_SVC
  IDENTIFIED BY "password"
  HTTP DIGEST DISABLE
  DEFAULT TABLESPACE SMALL_DATA
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

-- 1 Role for RW_SERVER_SVC 
GRANT RESOURCE TO RW_SERVER_SVC;
ALTER USER RW_SERVER_SVC DEFAULT ROLE ALL;

-- 1 System Privilege for RW_SERVER_SVC 
GRANT CREATE SESSION TO RW_SERVER_SVC;

-- 2 Tablespace Quotas for RW_SERVER_SVC 
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON SMALL_DATA;
ALTER USER RW_SERVER_SVC QUOTA UNLIMITED ON USERS;

On Application Server

cd E:\oracle\penfax_app\Oracle_Home\reports\admin\sql
SQL> connect RW_SERVER_SVC/<password>@DEV
SQL> @rw_server.sql
SQL> grant select on rw_server_svc.rw_server_job_queue to optrust,rpa_api;

On Application Server

Edit this file E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\servers\WLS_REPORTS\applications\reports_12.2.1\configuration\rwserver.conf

Add this XML under the <server> tag

<jobStatusRepository class="oracle.reports.server.JobRepositoryDB">
    <property name="dbpassword" value="password"/>
    <property name="dbconn" value="xxxxxxxx.yyyyyy.local:1521:DEV"/>
    <property name="dbuser" value="rw_server_svc"/>
</jobStatusRepository>

Check the output after running a report

  SELECT *
    FROM RW_SERVER_SVC.RW_SERVER_JOB_QUEUE
   WHERE server = 'rep_wls_reports_parisas01p'
ORDER BY job_id DESC;

Oracle forms and reports OHS service stop working

We are running Oracle forms and reports server since 2018 and all of a sudden the OHS service crashed on one server and then next one and next one and next one... Not good.

After some debugging execises, the problem is due to

1. Self-signed cwallet.sso expired and
2. Weblogic nodemanger default keystore SSL certficate expired

Let's start from cwallet.sso

File location: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\keystores\default\cwallet.sso
Let's check the expired date

orapki wallet display -wallet cwallet.sso
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Subject:        CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY
Trusted Certificates:
Subject:        CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY
 
orapki wallet export -wallet cwallet.sso -dn "CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY" -cert cwallet.cer

Goto Certificate Decoder and paste the cwallet.cer content into there

The current cwallet.sso is expired. We need to create a new one

orapki wallet create -wallet ./ -pwd WalletPasswd123 -auto_login
orapki wallet add -wallet ./ -pwd WalletPasswd123 -dn "CN=localhost,OU=FOR TESTING ONLY,O=FOR TESTING ONLY" -keysize 1024 -self_signed -validity 3650

Replace E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\config\fmwconfig\components\OHS\instances\ohs1\keystores\default\cwallet.sso with the new one.

Next, let's fix nodemanager SSL certificate

cd E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin\

E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin>wlst.cmd

Initializing WebLogic Scripting Tool (WLST) ...

Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> nmConnect(domainName='base_domain', username='weblogic', password='leave.me.alone')
Connecting to Node Manager ...
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090905> <Disabling the CryptoJ JCE Provider self-integrity check for better startup performance. To enable this check, specify
 -Dweblogic.security.allowCryptoJDefaultJCEVerification=true.>
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG128 to HMACDRBG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>
<Jan 17, 2023 11:23:07 AM EST> <Info> <Security> <BEA-090909> <Using the configured custom SSL Hostname Verifier implementation: weblogic.security.utils.SSLWLSHostnameVerifier$Null
HostnameVerifier.>
Traceback (innermost last):
  File "<console>", line 1, in ?
  File "<iostream>", line 111, in nmConnect
  File "<iostream>", line 553, in raiseWLSTException
WLSTException: Error occurred while performing nmConnect : Cannot connect to Node Manager. : General SSLEngine problem
Use dumpStack() to view the full stacktrace :
wls:/offline>

This doesn't tell much.

set WLST_PROPERTIES=-Djavax.net.debug=all -Dssl.debug=true
E:\oracle\penfax_app\Oracle_Home\oracle_common\common\bin>wlst.cmd

Initializing WebLogic Scripting Tool (WLST) ...

Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away.

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> nmConnect(domainName='base_domain', username='weblogic', password='leave.me.alone')


...


***
main, fatal error: 46: General SSLEngine problem
sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: timestamp check failed
%% Invalidated:  [Session-1, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256]
main, SEND TLSv1.2 ALERT:  fatal, description = certificate_unknown
main, WRITE: TLSv1.2 Alert, length = 2
main, fatal: engine already closed.  Rethrowing javax.net.ssl.SSLHandshakeException: General SSLEngine problem
main, called closeOutbound()
main, closeOutboundInternal()
[Raw write]: length = 7
0000: 15 03 03 00 02 02 2E                               .......
wls:/offline>

Check the output close to the bottom, it tells us there is a "timestamp check failed" problem.

Let's generate new Custom Identity and Custom Trust jks file

E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -genkey -alias base_domain -keyalg RSA -keysize 2048 -dname "CN=base_domain, OU=Digital, O=Leave Me Alone, L=Toronto, ST=Ontario, C=CA" -keypass WalletPasswd123 -keystore identity.jks -storepass WalletPasswd123
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -selfcert -v -alias base_domain -keypass WalletPasswd123 -keystore identity.jks -storepass WalletPasswd123 -storetype jks -validity 3650
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -export -v -alias base_domain -file rootCA.der -keystore identity.jks -storepass WalletPasswd123
E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -import -v -trustcacerts -alias base_domain -file rootCA.der -keystore trust.jks -storepass WalletPasswd123

Copy identity.jks and trust.jks to E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security

In Weblogic Admin Console

Custom Identity Keystore: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security\identity.jks
Custom Trust Keystore: E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\security\trust.jks

Edit E:\oracle\penfax_app\Oracle_Home\user_projects\domains\base_domain\nodemanager\nodemanager.properties, add the following to the bottom.

StartScriptEnabled=true
KeyStores=CustomIdentityAndCustomTrust
CustomIdentityKeyStoreFileName=E\:\\oracle\\penfax_app\\Oracle_Home\\user_projects\\domains\\base_domain\\security\\identity.jks
CustomIdentityAlias=base_domain
CustomIdentityPrivateKeyPassPhrase=WalletPasswd123
CustomTrustKeyStoreFileName=E\:\\oracle\\penfax_app\\Oracle_Home\\user_projects\\domains\\base_domain\\security\\trust.jks

We need to update the java Trust certificate as well

E:\oracle\penfax_app\Oracle_Home\oracle_common\jdk\bin\keytool.exe -import -alias base_domain -trustcacerts -file rootCA.der -keystore E:\oracle\temp\java\jdk1.8.0_112\jre\lib\security\cacerts -storepass changeit

Finally, everything is done. Reboot the server and OHS should start automatically.

Oracle 10g Forms and Reports Server Installation that work for Java 7 and 8

1. as_windows_x86_forms_reports_101202.zip
Use COMPATIBILITY MODE XP SP2 and run as Administrator

2. Patch #5983622
Use COMPATIBILITY MODE XP SP2 and run as Administrator

3. Patch #6640838
Use COMPATIBILITY MODE XP SP2 and run as Administrator
Use custom install, expand all nodes and ensure every node is checked.

4. Patch #6880880
unzip and overwrite the opatch directory in Oracle AS

5. Patch #9593176
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

6. Patch #11710576
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

7. Patch #13685884
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

8. Patch #13095466 (Bundle Patch on top of 10.1.2.3.1)
Run this in command window as Administrator. If opatch fails, just copy the file manually to oracle AS.

set OPATCH_PLATFORM_ID=215
set ORACLE_HOME=E:\Oracle\MiddleTier
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0_76\
E:\Oracle\MiddleTier\OPatch\opatch apply -jdk C:\ProgrA~1\Java\JDK17~1.0_7\bin\

webutil
Follow all the steps in this metalink document (Doc ID 566628.1)


formsweb.cfg

JRE 7 & 8

jpi_classid=clsid:CAFEEFAC-0017-0000-FFFF-ABCDEFFEDCBA
jpi_mimetype=application/x-java-applet;version=1.7
jpi_codebase=http://java.sun.com/update/1.7.0/jinstall-7u17-windows-i586.cab#Version=1,70,17
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads

JRE 6

jpi_classid=clsid:CAFEEFAC-0016-0000-FFFF-ABCDEFFEDCBA
jpi_mimetype=application/x-java-applet;version=1.6
jpi_codebase=http://java.sun.com/update/1.6.0/jinstall-6-windows-i586.cab
jpi_download_page=http://www.oracle.com/technetwork/java/javase/downloads