Connect to database without entering a password

When an Oracle client runs a SQL statement or a script by SQL*Plus, password is necessary to connect to the database.  But if we define a string and save it to Oracle Wallet, we can use the string to replace password to connect to the database. Here is how to use mkstore to manage cwallet.sso file.

1. Make sure string BATCH_STR is defined in tnsnames.ora file for the target instance EBSDEV.

BATCH_STR=

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=dbnode1d.domain.com)(PORT=1560))

            (CONNECT_DATA=(SID=EBSDEV))

        )

Then, "$ tnsping BATCH_STR" shall return "OK" or "$ sqlplus apps/appsPWD@BATCH_STR" shall work. But below connection does not work yet.

$ sqlplus /@BATCH_STR
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 29 11:18:03 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Optionally, put "export TWO_TASK=BATCH_STR" in .profile, then below line shall connect to database:

$ sqlplus apps/appsPWD

2. Create wallet and its files
$ mkstore -wrl $TNS_ADMIN -create
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter password: walletPWD
Enter password again:  

$ ls -al $TNS_ADMIN/*wallet.*
-rw------- 1 user1 users 3589 Dec 29 11:14 /path/to/network/admin/cwallet.sso
-rw------- 1 user1 users 3512 Dec 29 11:14 /path/to/network/admin/ewallet.p12

3. List the wallet contents. Nothing in it yet.
$ mkstore -wrl $TNS_ADMIN -listCredential
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: walletPWD
List credential (index: connect_string username)

4. Add string BATCH_STR with credential info to the wallet
$ mkstore -wrl $TNS_ADMIN -createCredential BATCH_STR apps appsPWD
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: walletPWD
Create credential oracle.security.client.connect_string1

$ mkstore -wrl $TNS_ADMIN -listCredential
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:  walletPWD
List credential (index: connect_string username)
1: BATCH_STR apps

5. Add lines to sqlnet.ora
$ cat sqlnet.ora
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/path/to/network/admin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE

6. Test the connect without entering a password
$ sqlplus /@BATCH_STR
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show user
apps

7. Optionally, an OS variable can be defined for making the db connection
$ export APPS_NO_PSWD=/@BATCH_STR
$ sqlplus $APPS_NO_PSWD
SQL> show user
apps

8.  Command to modify wallet or update APPS's password.
$ mkstore -wrl $TNS_ADMIN -modifyCredential BATCH_STR apps appsNewPWD

9. If other OS users want to use the same wallet, add permission to the Wallet file to avoid error " ORA-12578: TNS:wallet open failed ".
$ cd $TNS_ADMIN
$ chmod +r cwallet.sso

Notes: Oracle Wallet Manager (owm) can open file cwallet.sso created by mkstore. But it can not modify the contents.

Extract private key from Oracle Wallet and create Wallet from certs files

Oracle Wallet file stores X.509 certificates and private keys in PKCS (Public-Key Cryptography Standards) #12 format. Oracle Wallet Manager (OWM) can open file ewallet.p12, and create file cwallet.sso after "Auto Login" is checked and then it's Saved. Below are some notes from my testing on wallet files and certs files.

• How to extract the private key from ewallet.p12 ?

 $ openssl pkcs12 -in ewallet.p12 -nocerts -out private_key.pem
Enter Import Password:
MAC verified OK
Warning unsupported bag type: secretBag
Enter PEM pass phrase:          <= enter "welcome"
Verifying - Enter PEM pass phrase:
$ ls -al private_key.pem
-rw-r--r-- 1 user1 users 1879 Feb 11 16:57 private_key.pem

$ openssl rsa -in private_key.pem -out private.key
Enter pass phrase for private_key.pem:       <= welcome
writing RSA key
$ ls -al private.key
-rw-r--r-- 1 user1 users 1675 Feb 11 16:59 private.key

$  openssl rsa -in private_key.pem -check           <= verify private key
Enter pass phrase for private_key.pem:
RSA key ok
writing RSA key
-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAt5gCGs0BhUAAnD1FOxuq8r/JY5UalNYN+uvzMOQR5FuI1i7l
etc ... ... ...
-----END RSA PRIVATE KEY-----
Verify Oracle Wallet

 $ more private.key
-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEAt5gCGs0BhUAAnD1FOxuq8r/JY5UalNYN+uvzMOQR5FuI1i7l
etc ... ... ...
-----END RSA PRIVATE KEY-----

• Make the Wallet auto-login

Wallet has to be an auto-login for EBS to use it. But OpenSSL can not do it, as this is an Oracle specific feature. OWM can do that. Alternatively run the following to add the auto_login portion:

$ orapki wallet create -wallet <path_to_wallet> -auto_login

Seems to me this does not work, if the .p12c file never opened and Saved by OWM before. Here is the message from my R12.1.3 instance (where Oct 2015 CPU patch 21845960 was already applied to 10.1.3 Oracle Home):
$ orapki wallet create -wallet /u06/app/temp -auto_login -pwd walletPWD
Unable to load wallet at u06/app/temp

Interestingly, after OWM opens the .p12 file and click Save (even without check "Auto Login"), then orapki is able to create .sso file:
$ ls -al /path/to/wallet/*wallet.*
-rw-r--r--  1 user1 users 5989 Feb 12 10:51 ewallet.p12
$ ls -al $ORACLE_HOME/bin/orapki
-rwxr-xr-x  1 user1 users 3202 Oct 23 2012 /path/to/10.1.3/bin/orapki

$ orapki wallet create -wallet /path/to/wallet -auto_login -pwd walletPWD 

$ ls -altr /path/to/wallet/*wallet.*
-rw-r--r--  1 user1 users 5989 Feb 12 10:51 ewallet.p12
-rw-------  1 user1 users 6018 Feb 12 10:55 cwallet.sso        <= yes, "orapki" created cwallet.sso

$ orapki wallet display -wallet /path/to/wallet -pwd walletPWD       <= verify Oracle Wallet
Requested Certificates:
Subject:        CN=sitename.company.com,OU=TMS,O=company Inc.,L=New York,ST=NY,C=US
User Certificates:
Trusted Certificates:
Subject:        CN=sitename.company.com,OU=TMS,O=company Inc.,L=New York,ST=NY,C=US
Subject:        CN=GeoTrust Global CA,O=GeoTrust Inc.,C=US
Subject:        CN=company Inc. Certificate Authority,OU=GeoRoot Certification Authority,O=company Inc.,C=US

•  How to Create Java Keystore from Oracle Wallet?

JRE 1.8 needs KeyStore file for Java signing. It will be nice if the KeyStore be generated easily from the Wallet file that is used by EBS Apache server. I doubt 10gAS (FMW 10) can do that, because of below error.  It may be a FMW 11 feature, and the .jks file is a different type of keystore.
$ export PATH=$PATH:$ORACLE_HOME/??/bin/

$ orapki wallet pkcs12_to_jks -wallet ewallet.p12 -jksKeyStoreLoc ewallet.jks -jksKeyStorepwd -pwd Invalid command: pkcs12_to_jks

• Create a Wallet from cert file (to run UTL_HTTP)

Oracle wallets are used for different purpose. Doc ID 376700.1 provide a way to create one for database host when it acts as a client sending requests to secured EBS webpages. To enable HTTPS Client request from the database via UTL_HTTP, it needs to establish a truststore in wallet format. OWM can create the wallet for that by importing root CA certificate file. Where to get the root CA certificate? I verified below steps work with 12.1.0.2 database. But for EBS, I found this is not needed because file cwallet.sso on EBS apps server can be simply copied over to database server to make UTL_HTTP call work.

Steps to obtain the correct certificate from the website (Doc ID 169768.1):
a) On the IE11 browser displaying https://ebssite.domain.com:443, Select File from the menu bar, then Properties.
b) The Properties dialog box has a Certificates button. Click this.
c) The Certificate dialog box has a Certification Path tab. Click this
d) In the Certification path box. Notice that multiple certificates. Highlight the top most certificate (i.e. VerSign/RSA Secure Server CA or GeoTrust Global CA). Then notice the View Certificate button is active. Click this.

e) Another Certificate dialog box appears, which also has a Details tab. Click this.
f) The Details tab has a "Copy to File..." button. Click this.
g) The Certificate manager Export Wizard appears. Click the Next button.
h) This screen is the Certificate Export File screen. From the radio buttons, select the "Base64 encoded" option and click Next.
i) At this screen, enter the filename and click Next.
j) The "Completing the Certificate Manager Export Wizard" screen contains a summary of information. Simply click the Finish button and a dialog box should appear to say the export was completed successfully. Make a note of where this file has been saved. (Notes: need just one file!)

Configure Wallet Manager for this certificate
a) start the wallet manager owm (after export DISPLAY)
b) go to 'wallet' tab and click on NEW
c) provide a password for this wallet e.g orcl (for V901x we need password to be >7 characters and alphanumeric), when prompted on creating a client certificate choose NO
d) go to the 'operations' tab and click on 'Import Trusted Certificate', choose the option to select a file that contains the certificate
e) find the certificate that was saved from above step h) and click on OPEN. Notice that in wallet manager we can see that 'Trusted Certificates' list has been updated and that we can see the credentials of the certificate on the RHS of the screen.
f) go to the 'wallet' tab and click on 'Save As', provide folder location '/path/to/wallet' on database host.
Notes: in my test, file cwallet.sso is necessary to make below call work. So check "Auto Login" in OWM before saving it.
Now, below call shall work for database user APPS
" SELECT utl_http.request('https://ebssite.domain.com:443', NULL ,'file:/path/to/wallet', NULL) from dual; "

• " The password is incorrect. Try again? "

When I use OWM (version 10.1.0.5.0) to open ewallet.p12 file generated from a 3rd party tool to re-new the certificate for an EBS R12.1 site, owm keeps saying "The password is incorrect. Try again?". I verified file ewallet.p12 is good, because it can be opened by owm on a different server (for Dev environment).

It seems something on the host prevents owm from taking the password to open the certificate file. The fix is to apply October 2015 CPU patch 21845960 to FMW 10.1.3.5 (for Apache).

• To create a Wallet using OpenSSL for use with Oracle 10gAS (Doc ID 184701.1)

The syntax is
$ openssl pkcs12 –export –out ewallet.p12 –inkey priv_key_location –in server_cert_location –certfile root_cert_location

For example:
$ openssl pkcs12 –export –out /wallet/ewallet.p12 –inkey /wallet/priv.key –in server.crt –certfile chain.crt

Now, where to get server.crt and chain.crt? Here are from document 184701.1:
If your server certificate e.g server.crt is only signed by one Trusted Root CA certificate, then chain.crt contains the one CA certificate. If your server certificate has a chain of root CA certificates, then its necessary to create one concatenated file that contains all the root CA's.
The best way to find if your certificate has a chain of root CA's, is to move the server.crt to a Windows machine and double click on it. When the certificate window appears, click on Certification Path. This shows all the certificates in the chain. The bottom one is the actual certificate, and anything above that is/are the Root CA(s) that signed it. If there are two certificates listed, this means there is only one root CA in the path. If there are more than two certificates listed in the path, its necessary to create a single concatenated file of all the base64 certificates above the server certificate (the bottom one).
To obtain the correct root CA certificates, double click the certificate(s) above the actual certificate. This will load that certificate in a new window. Select Details -> Copy to File -> and Save this file in base64 format. Close this window and do the same for any more certificates in the chain.

Once you have these certificates, open them with a text editor and create one file with all the certificates. Make sure that the lowest CA in the chain is at the top and then the rest of the certificates up to the root are in order below it, with the root CA being the very bottom one i.e:

-----BEGIN CERTIFICATE-----
MIICPDCCAaUCEHC65B0Q2Sk0tjjKewPMur8wDQYJKoZIhvcNAQECBQAwXzELMAkG
A1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFz
etc..
-----END CERTIFICATE-----
-----BEGIN CERTIFICATE-----
MIIHPTCCBSWgAwIBAgIBADANBgkqhkiG9w0BAQQFADB5MRAwDgYDVQQKEwdSb290
IENBMR4wHAYDVQQLExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNB
etc..
-----END CERTIFICATE----

Save this file and call "chain.crt", for example:

$ cat intermediate.crt rootca.crt >chain.crt

Java KeyStore file and Java signing

After JRE (Java Runtime Environment) 1.8 patches are applied to EBS R12 by following Doc 393931.1, the next step is Java signing as described in Doc ID 1591073.1.

1. Created keystore (JKS) file $APPL_TOP/admin/adkeystore.dat

- First, backup file adkeystore.dat and verify the content of adsign.txt is correct.
- Then run below line to create a new JKS file:

$ cd $APPL_TOP/admin
$ adjkey -initialize -keysize 2048
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                    Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.

Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
adjkey will now create a signing entity for you.

Enter the Name of your Company (used for both CN and
ORGANIZATION NAME) [CN/ORGANIZATION NAME] : siteName Inc.
Enter the department or group that will use the certificate [ORGANIZATION UNIT] : siteName Inc.
Enter the full name of the city where your organization's
head office is located [LOCALITY] :  New York
Enter the full name of the State, Province or County where
your organization's head office is located [STATE] :  NY
Enter the two-letter ISO abbreviation for your country
(for example, US for the United States) [COUNTRY] : US
Enter keystore password:  Re-enter new password: Enter key password for ... ...
adjkey is complete.

You do not need to enter a new password for the keystore, as it will take the default. Use below code to see the passwords:

SQL> set serveroutput on
SQL> declare
spass varchar2(30);
kpass varchar2(30);
begin
ad_jar.get_jripasswords(spass, kpass);
dbms_output.put_line(spass);
dbms_output.put_line(kpass);
end;
/  
puneet       <== default password for keystore
myxuan     <== default password for the key

2. Create CSR (Certificate Signing Request) file

$ export JRI_DATA_LOC=$APPL_TOP/admin
$ cd  $APPL_TOP/admin

$ keytool -sigalg SHA1withRSA -certreq -keystore adkeystore.dat -file adkeystore.csr -alias EBSDEV_devserver1d
Enter keystore password:
Enter key password for <EBSDEV_devserver1d>

$ openssl req -in adkeystore.csr -text -noout | grep "Signature Algorithm"
    Signature Algorithm: sha1WithRSAEncryption
$ openssl req -in adkeystore.csr -noout -text       <== verify a CSR

Note: " $ adjkey -certreq -file adkeystore.csr " also creates a .csr file.

3. Send adkeystore.csr file to Certificate Authority of the company to sign. They shall send 3 .cer files back. They are the keys.

Use below line to see the content of cert file, e.g.
$ keytool -printcert -v -file RootCA.cer

4. Import keys (i.e. cert files) to JKS on the same server where CRS file was generated

$ echo $OA_JRE_TOP
/u05/app/EBSDEV/apps/tech_st/10.1.3/appsutil/jdk/jre
$ export SEC_PROP_LOC=$OA_JRE_TOP/lib/security   (<= No need for me as cacerts is not used)

$ keytool -import -alias ebsrootca -file RootCA.cer -trustcacerts -v -keystore adkeystore.dat  (??)
Enter keystore password:     <== puneet
Certificate already exists in system-wide CA keystore under alias <digicertassuredidrootca>
Do you still want to add it to your own keystore? [no]:  yes
Certificate was added to keystore
[Storing adkeystore.dat]

$ keytool -import -alias interCA -file siteName.cer -trustcacerts -keystore adkeystore.dat
Enter keystore password:
Owner: CN=siteName Inc., O=siteName Inc., L=New York, ST=NY, C=US
Issuer: CN=DigiCert SHA2 Assured ID Code Signing CA, OU=www.digicert.com, O=DigiCert Inc, C=US
Serial number: c123456f86c0a6a4bbe83e69e0c1ff5
Valid from: Mon Sep 21 20:00:00 EDT 2015 until: Tue Sep 26 08:00:00 EDT 2017
Certificate fingerprints:
         MD5:  34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
         SHA1: 6C:36:7D:54:9A:F6:52:1C:18:45:2B:6E:FB:D4:EF:75:EE:3E:81:E8
         Signature algorithm name: SHA256withRSA
         Version: 3
Extensions:
... ... ...
Trust this certificate? [no]:  yes
Certificate was added to keystore

$  keytool -import -alias EBSDEV -file codeSigningCA.cer -trustcacerts -keystore adkeystore.dat
Enter keystore password:
Certificate was added to keystore

5. Verify the contents of JKS file

$ keytool -list -keystore ewallet.jks -storepass keystorePWD
$ keytool -list -v -keystore adkeystore.dat    <= to the keystore detail

Below JKS file with 3 entries works well for me:

$ keytool -list -keystore adkeystore.dat
Enter keystore password:

Keystore type: JKS
Keystore provider: SUN

Your keystore contains 3 entries

ebsdev_devserver1d, Nov 4, 2015, PrivateKeyEntry,
Certificate fingerprint (MD5): 34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
interca, Sep 22, 2015, trustedCertEntry,
Certificate fingerprint (MD5): 34:3B:35:0F:6A:43:22:B3:6B:63:82:F3:B3:02:0F:74
ebsdev, Oct 27, 2015, trustedCertEntry,
Certificate fingerprint (MD5): B7:55:37:6C:3D:2A:CE:BB:A1:88:49:D6:04:36:1B:D6

6. Run the Java signing on each EBS web/Forms node

$ stop all apps services
$ adadmin
    ==> 1 Select Generate Applications Files
    ==> 4 Generate Product JAR Files    Yes.  (Do force the regeneration of all JAR files.)

  ... ... ... 
  You can safely ignore any warnings about missing metadata entries in JAR and Zip files
  ......
  Removed appsborg2.cmd.
  Successfully created new appsborg2.zip.
  Copied appsborg2.zip from AU_TOP to  AF_JLIB.

Note1: if for some reason, the keystore password saved in the database does not match the password in the JKS file, adadmin will fail. You will have to change the JKS password to make them match.
Note2: if adadmin fails or .jar files do not get signed, there is something wrong with JKS file adkeystore.dat. You may delete it and re-do it after the issue is identified. Some warnings on .zip files can be ignored during adadmin run.
Note3: if adadmin fails with error "adogjf() Unable to generate jar files under JAVA_TOP", there could be a problem with the content of $APPL_TOP/admin/adsign.txt.

7. Verify .jar files are newly signed. If it works, .jar files not only get new timestamp but also get signed with 3 certificates. For example, check one file:

$ jarsigner -verify -verbose -certs $AD_TOP/java/jar/adxlib.jar
... ... ... ...
 X.509, CN=siteName Inc., O=siteName Inc., L=New York, ST=NY, C=US
 [certificate is valid from 12/21/15 8:00 PM to 12/26/17 8:00 AM]
 X.509, CN=DigiCert SHA2 Assured ID Code Signing CA,OU=www.digicert.com,O=DigiCert Inc,C=US
 [certificate is valid from 11/20/13 8:00 AM to 11/20/28 8:00 AM]
 X.509, CN=DigiCert Assured ID Root CA, OU=www.digicert.com, O=DigiCert Inc, C=US
 [certificate is valid from 12/9/07 7:00 PM to 12/9/32 7:00 PM]

Note: In my server, 3 date ranges match with those in 3 .cer files.

8.  start apps services and launch the Forms
Now, on a client machine with JRE 1.8 installed, R12 Forms shall launch smoothly (without placing the URL in the exception list of Security tab in Java console).

If the JRE version (i.e. 1.8.0_51) on the server does not match the JRE version (i.e. 1.8.0_66) on users' machine, It will popup a confirmation before EBS Forms show up.

9. If JKS file adkeystore.dat worked on one server, it can be used in all other servers of the company after the alias is changed to the new instance info, such as from Dev to QA:

$ keytool -changealias -alias ebsdev_devserver1d -destalias EBSQA_qaserver2q -keystore adkeystore.dat
$ keytool -changealias -alias ebsdev -destalias EBSQA -keystore adkeystore.dat

10. How to change keystore password and key password

$ adjkey -storepasswd
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                 Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
alias name used is FAHPGRND_xfinapm3d

Enter the new keystore password:        <== testit1
Enter keystore password:  New keystore password: Re-enter new keystore password:
keytool -storepasswd -keystore $APPL_TOP/admin/adkeystore.dat
The above Java program completed successfully.

$ adjkey -keypasswd
                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA
                             AD Java Key Generation
                                 Version 12.0.0
NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
Enter the APPS username: apps
Enter the APPS password:

Successfully created javaVersionFile.
alias name used is EBSDEV_devserver1d

Enter the new key password:     <== myxuan2
... ... ...
keytool -keypasswd -keystore $APPL_TOP/admin/adkeystore.dat -alias EBSDEV_devserver1d
The above Java program completed successfully.

Notes: " adjkey -storepasswd" and "adjkey -keypasswd" will changes the passwords in both database and file adkeystore.dat. But, if passwords in the database and in file adkeystore.dat do not match, it will give error " keytool error: java.io.IOException: Keystore was tampered with, or password was incorrect ".  In this case, use keytool to change the passwords in file adkeystore.dat to make them match the ones in the database as the first step.

What below will do?

SQL> exec ad_jar.DEL_JRIPASSWORDS;

SQL> exec ad_jar.PUT_JRIPASSWORDS('storePWD',' keyPWD');

11. How to delete a key from keystore
$ keytool -delete -alias mykey -keystore adkeystore.dat

NOTES:
- Keytool reference:
http://docs.oracle.com/javase/6/docs/technotes/tools/solaris/keytool.html
- For Oracle Fusion Middleware 11.1.1.1.0 and later, use ORAPKI to manage Wallet. See Doc ID 1226654.1 - How to Create a Wallet via ORAPKI in FMW 11g.

APList - Payables Invoice Data Collection Test

When users open a SR with Oracle Support for data issue, they frequently ask for APList report. Really, it is a standard diagnostic test in R12 Payables (SQLAP). It gets the name maybe because its back-end xml file is called APListXml.xml. I copy steps from Doc ID 732163.1 here to skip logging into Oracle Support website each time when need it:

1. Log onto Oracle E-Business Suite
2. Connect to responsibility Application Diagnostics
3. Select the Diagnose menu option
4. Click button Select Application and select Application "Payables"
5. Scroll down to group "Invoice"
6. Select test name "Invoice Data", then click on EXECUTE on the bottom
7. Input Parameters (* required)
   • Responsibility Id (LOV) *    <== grant a super user resp. to yourself first if you do not see one on the LOV
   • Search Criteria (LOV)  
   • Supplier Name (LOV)  
   • Invoice Id (LOV) *  
   • Include GL Tables (Yes or No) (LOV)  
   • Include Related Data (Yes or No) (LOV)  
   • Max Rows Displayed

R12 concurrent manager troubleshooting

Concurrent manages may not work properly due to different reasons.  To check if any concurrent jobs are running, run script afcmrrq.sql at $FND_TOP/sql. See Doc ID 2089560.6 (How To Tell If Concurrent Managers For A Particular SID Are Running) for other scripts.

1. After instance refresh or clone, concurrent managers fail to start.
When old (or source) server info is still saved in the database tables, concurrent managers get confused and will not start properly, even after below two lines to clean source info before running adconfig on database server:
$ sqlplus apps/passwd
SQL> @cmclean.sql         ( <= only before R12.2 )
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
SQL> commit;

SQL> select * from fnd_nodes;   (shall have 0 row. But one row after DBA runs adconfig script)
SQL> select unique(node_name) from fnd_concurrent_queues;
SQL> select * from fnd_concurrent_processes;
SQL> select * from fnd_conflicts_domain;

I saw message lines in log files under $APPLCSF/log:
CONC-SM TNS FAIL
Routine AFPEIM encountered an error while starting concurrent manager FNDSCH with library $APPL_TOP/fnd/12.0.0/bin/FNDSCH.
Check that your system has enough resources to start a concurrent manager process. Contact your system ad : 06-DEC-2015 09:58:42

The best fix is to apply patch 18539575 to fix a bug in R12.1 (See Doc ID 1646026.1). After this patch, concurrent services start normally in my refreshed instances.

One time, when the CM failed to start, I see message in the log file: 
Routine AFPEIM encountered an error while starting concurrent manager STANDARD with library $APPL_TOP/fnd/12.0.0/bin/FNDLIBR.
Check that your system has enough resources to start a concurrent manager process. Contact your sys : 30-DEC-2015 15:08:42
Starting Internal Concurrent Manager Concurrent Manager : 30-DEC-2015 15:08:42
CONC-SM TNS FAIL
 : ICM failed to start for target node1Name.  Review ICM log files for additional information.
                     Process monitor session ended : 30-DEC-2015 15:08:42

I realized that node1Name is the wrong node. So, I ran sql to update the data (with caution!):

SQL> select unique(node_name) from fnd_concurrent_queues;
NODE_NAME
------------------------------
node1Name
node2Name

SQL> select count(*) from fnd_concurrent_queues;
  COUNT(*)
----------------
        45

SQL> create table fnd_concurrent_queues_BK_jan as select * from fnd_concurrent_queues;
SQL> update fnd_concurrent_queues set NODE_NAME='node2Name'
where NODE_NAME='node1Name';

3 rows updated.

SQL> select unique(node_name) from fnd_concurrent_queues;
NODE_NAME
------------------------------
node2Name
SQL> commit;

After the table was updated, all concurrent mangers started on server node2Name.

2. When all concurrent manager processes were not fully shutdown or not properly shutdown in some situation (e.g. database connections were interrupted), CM services may fail on re-start. The number of processes in "Actual" and "Target" may not match with message " System Hold, Fix Manager before resetting counters ". In this case, first try shall be to use OAM to re-start individuals. For example (in Oracle Applications Manager version 2.3.1):

Site Map => Generic Services
On radio button select Output Post Processor (or, Conflict Resolution Manager ) => View Detail => Start (or verify) in the dropdown on the top => Go
(click on Next10) Generic Service Component Container => Workflow Agent Listener Service => Start in the dropdown on the top => Go

Site Map => Request Processing Manager
On radio button select Standard Manager => View Status tab on the upper => Start => Ok. Then click on Service Instances on the top to return to manager list.

3. When try to open a Request log or output file in GUI, it gives error:

An error occurred while attempting to establish an Applications File Server connection with the
node FNDFS_NODE2NAME. There may be a network configuration problem, or the TNS listener
on node FNDFS_NODE2NAME may not be running. Please contact your system administrator.

Here, Node2NAME is the concurrent server. After you make sure the listener (tnslsnr) is started, the first try is to ping the tnsname on Node1 (web/forms server): $ tnsping FNDFS_NODE2NAME
If you can not ping it, the real problem could be in $TNS_ADMIN/tnsnames.ora file on Node1. In one case, "domain.com" was not attached to Node2NAME in entries in tnsnames.ora file on Node1 server. After I re-ran autoconfig on all nodes, the problem got fixed.

Another place to check, make sure Profile options " RRA:% " have nothing strange.

If it works, below line shall list in "Concurrent => Manager => Administrator":
NAME                                   Node      Actual    Target   Status
Service Manager: Node1    Node1     1            0          {blank}

For Service Manager, if the Status shows "Target node/queue unavailable", clicking on "Restart" or OAM tries may not help. 

UPDATE in Sept 2020: Recently, seems the R12.1 clone script on RHEL7 did catch all info during the clone (maybe due to database connection error at the begining). After a clone completion and an adautocfg.sh run on all nodes, the tnsnames.ora file on CM node missed some entries. After 2nd run of adautocfg.sh on all nodes, some entries of tnsnames.ora on CM node missed "domain.com". tnsnames.ora file on CM node matches the original tnsnames.ora prior to clone until 3rd run of adautocfg.sh completed. 

4. Sometimes you are in a hurry to stop CM services but the OS processes keep running. You may see in the GUI form the "Actual" is non-zero while the "Target" is zero, which means there are still some requests are running. If you click "Terminate" the concurrent manager on the GUI, it will NOT kill its OS process. So this will not really speed up. The best way is to Find what are the running requests and then cancel the requests.

5. One time, Output Post Processor did not start. I checked file $APPLCSF/log/FNDOPPxxxxx.txt and saw error
Exception in static block of jtf.cache.CacheManager. Stack trace is: oracle.apps.jtf.base.resources.FrameworkException:
IAS Cache initialization failed. The Distributed Caching System failed to initialize on port: 12351. The list of hosts in the distributed caching system is: 157.121.49.41 157.121.53.42 157.121.53.43 . The port 12351 should be free on each host running the JVMs.

That means port 12351 is in use. After this port became free, Output Post Processor started.

6. One way to check if CM files on file system are good or not, run below report for a test. If all work, it will generate and save Active Users report for you even when all EBS services are shutdown at OS level.

$INST_TOP/ora/10.1.2/bin/appsrwrun.sh userid=apps/appsPWD mode=character report=$FND_TOP/reports/US/FNDSCURS.rdf \
batch=yes destype=file desname=./areport.out desformat=$FND_TOP/reports/HPL pagesize=132x66 traceopts=trace_all tracefile=areport.trc tracemode=trace_replace 

7. Below error may indicate that some node name is not registered in FND_NODES table. autoconfig may not run on all nodes after a cleaning.

List of errors encountered:
.............................................................................
_ 1 _
Concurrent Manager cannot find error description for CONC-System Node
Name not Registered
Contact your support representative.
.............................................................................

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR
Review your concurrent request log file for more detailed information.
Make sure you are passing arguments in the correct format.

8. If you do not want concurrent managers to run on a node at all, use two parameters in $CONTEXT_FILE to turn off them:

<oa_service_group_status oa_var="s_batch_status">disabled</oa_service_group_status><oa_service_group_status oa_var="s_other_service_group_status">disabled</oa_service_group_status>

I noticed once below error in a manager log file, while Concurrent processing works fine. I believe the error showed up after CM services got started on a wrong node by wrong values in $CONTEXT_FILE when the node got bounced from a crash. Oracle Support said it is a database issue. But I did not do anything on tables and the error went away by itself after above two values were set to "disabled" in $CONTEXT_FILE on that node.

Routine &ROUTINE has attempted to start the internal concurrent manager. The ICM is already running. Contact you system administrator for further assistance.afpdlrq received an unsuccessful result from PL/SQL procedure or function FND_DCP.Request_Session_Lock.
Routine FND_DCP.REQUEST_SESSION_LOCK received a result code of 1 from the call to DBMS_LOCK.Request.
Possible DBMS_LOCK.Request result ORACLE error 1036 in tag_db_session

Cause: tag_db_session failed due to ORA-01036: illegal variable name/number.

The SQL statement being executed at the time of the error was: ... and was executed from the file &ERRFILE.Call to establish_icm failed
The Internal Concurrent Manager has encountered an error.

Review concurrent manager log file for more detailed information. : 26-JUL-2015 10:52:55 -
Shutting down Internal Concurrent Manager : 26-JUL-2015 10:52:55

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCSQ encountered an ORACLE error. .
Review your error messages for the cause of the error. (=<POINTER>)
.............................................................................

List of errors encountered:
.............................................................................
_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR
Review your concurrent request log file for more detailed information.
Make sure you are passing arguments in the correct format.
.............................................................................
The EBSXXXX_0726@EBSXXXX internal concurrent manager has terminated with status 1 - giving up.