Tuesday, March 8, 2016

Name a URL for EBS website (and F5)

After EBS is installed and configured on host webHost1d, the default URL is http://webHost1d.domain.com:s_webport. Most times, we want to replace it with more meaningful name, such as finance.domain.com. Or if multiple nodes for the web/form tier, a DNS name has to be used for the EBS site.

1. Request a DNS ip address for finance.domain.com

DNS ip address registration will map server webHost1d.domain.com (or additional server) to finance.domain.com. Before the mapping, you can not ping finance.domain.com or nslookup finance.domain.com.

$ nslookup finance.domain.com
** server can't find finance: SERVFAIL

$ ping finance.domain.com
ping: unknown host finance.domain.com

2. After the DNS ip is registered, nslookup will work like something below. Here, 123.45.67.987 is the ip address for finance.domain.com

$ nslookup finance.domain.com
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com       canonical name = webHost1d.domain.com.
Name:   webHost1d.domain.com
Address: 123.45.67.987

If finance.domain.com is monitored by F5, its DNS ip address will tied to multiple pool members inside F5, depending on the number of EBS web/forms nodes. In this case, nslookup will return slightly different:

$ nslookup finance.domain.com
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com        canonical name = finance.dev.vip.domain.com.
Name:   finance.dev.vip.domain.com
Address: 123.45.67.987

The DNS entry for finance.domain.com points to F5 CNAME (canonical name)  finance.dev.vip.domain.com

3. Also, ping on it shall also work

$ ping finance.domain.com
PING finance.domain.com (123.45.67.987) 56(84) bytes of data.
64 bytes from finance.domain.com (123.45.67.987): icmp_seq=1 ttl=63 time=0.280 ms
64 bytes from finance.domain.com (123.45.67.987): icmp_seq=2 ttl=63 time=0.559 ms

4. Make 3 changes in $CONTEXT_FILE to use finance.domain.com as the URL.

<externURL oa_var="s_external_url">https://webhost1d.domain.com:4453</externURL>  =>
<externURL oa_var="s_external_url">https://finance.domain.com:4453</externURL>

<webentryhost oa_var="s_webentryhost">webhost1d</webentryhost>  =>
<webentryhost oa_var="s_webentryhost">finance</webentryhost>

<login_page oa_var="s_login_page">https://webhost1d.domain.com:4453/OA_HTML/AppsLogin</login_page>   =>
<login_page oa_var="s_login_page">https://finance.domain.com:4453/OA_HTML/AppsLogin</login_page>

After AutoConfig, the EBS site can be accessed by https://finance.domain.com:4453

By the way, when 4453 is the ssl port number, it will be the value for three $CONTEXT_FILE variables (and also in $INST_TOP/ora/10.1.3/Apache/Apache/conf/ssl.conf file as the Listen port):

<httpslistenparameter oa_var="s_https_listen_parameter">4453</httpslistenparameter>
<web_ssl_port oa_var="s_webssl_port" oa_type="PORT" base="4443" step="1" range="-1" label="Web SSL Port">4453</web_ssl_port>
<activewebport oa_var="s_active_webport" oa_type="DUP_PORT" base="8000" step="1" range="-1" label="Active Web Port">4453</activewebport>

TROUBLESHHOTING

After an EBS R12.1.3 instance was cloned / moved to new nodes (webHost1d and webHost2d), EBS site page https://finance.domain.com:4453 did not work. 

Page using node name http://webHost1d.domain.com:s_webport worked but failed on re-directing to login page (Note: if company network does not allow http, this page may not load up in browser. Then, on OS level, try $ wget webHost1d.domain.com 8021 ). So, Apache services worked fine. Furthermore, TELNET to the node link works (if telnet is not available, try "wget"): 

 $ telnet webHost1d.domain.com 8021
Trying 177.99.88.66...
Connected to webHost1d.
Escape character is '^]'.
GET       <== type in/Enter
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<HTML><HEAD>
<TITLE>400 Bad Request</TITLE>
</HEAD><BODY>
<H1>Bad Request</H1>
Your browser sent a request that this server could not understand.<P>
invalid request-URI <P>
</BODY></HTML>
Connection closed by foreign host.

While using NSLOOKUP to check the site, it returns two IPs:

$ nslookup finance.domain.com   <== It returns two entries
Server:         123.45.67.89
Address:      123.45.67.89#53

finance.domain.com        canonical name = finance.dev.vip.domain.com.
Name:   finance.dev.vip.domain.com
Address: 123.45.67.987
Name:   finance.dev.vip.domain.com
Address: 123.45.66.987

My understanding is that if it returns two IPs, either the web services are not fully working on web server or there is an issue in F5 / network. In above case, the problem was in F5. After F5 admin removed and re-entered two pool members' IPs, and also re-entered port number (s_webssl_port 4453) in F5 console, the problem got fixed. F5 admin said the problem was on the port number (s_webport 8021??). If s_webport is listened in F5, webpage https://finance.domain.com:4453 may give ERR_CONNECTION_RESET error

Also, need to confirm with Network team that finance.dev.vip.domain.com is the right cname (canonical name).

If 443 is used as s_active_webport (vs. 4453) in $CONTEXT_FILE, 443 shall be also entered in F5 to make site https://finance.domain.com work.

By the way, if EBS site https://finance.domain.com works, link to single node  https://webHost1d.domain.com:web_ssl_port shall say "This site is not secure" or "Your connection is not private" (or ERR_CERT_COMMON_NAME_INVALID, maybe because the ssl cert is not for webHost1d.domain.com).

In a VPN network, such as Zscaler, nslookup may show a different IP or show finance.domain.com is blocked on laptop, while both "nslookup finance.domain.com" and "wget https://finance.domain.com" works on web server (Linux level). That makes the website is not accessible on the laptop (with misleading error in browsers). The cause could be that Zscaler configured the site in a wrong segment.
C:\> nslookup finance.domain.com
... ...
*** Unknown can't find finance.domain.com: Non-existent domain

If reverse lookup is enabled, another common issue in network is IP address is not mapping to the DNS name, for example: 

$ nslookup  123.45.67.987 
** server can't find 987.67.45.123.in-addr.arpa: NXDOMAIN



Posted by at 1 comment:
Labels:

Thursday, March 3, 2016

Get active session info in EBS database

When an EBS job or session runs too long, everyone wants to know what it is doing in the database (11g & 12c) and how far away from its finish. Depending on the session stage, if the job is actively processing sql statements, two scripts here will provide similar Output:
... ... ...
Progress in DB (SID: 3258)
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:00:54
Elapsed in min .... : 0:11
Remaining ......... : 0:3
Complete_pct .... : 79.59
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_PAYMENT_SCHEDULES_ALL
Message ......... : Index Fast Full Scan:  AR.AR_PAYMENT_SCHEDULES_ALL: 58776 out of 73853 Blocks done
----------------------------------------------------------
START TIME ...... : 08-FEB-2016 15:01:08
Elapsed in min .... : 69:35
Remaining ......... : 39:24
Complete_pct .... : 63.51
SQL ID .......... : f8h3xq0c5sqb6
SQL hash value .. : 408705382
db Object ....... : AR.AR_CASH_RECEIPTS_ALL
Message ......... : Table Scan:  AR.AR_CASH_RECEIPTS_ALL: 528906 out of 832791 Blocks done

  • Script finds database session info by entering a concurrent Request ID:
When the concurrent Request is still in Running status and if column ORACLE_SESSION_ID in table fnd_concurrent_requests is populated, below query shall return accurate information. Seem to me when the concurrent Request is near to finish, sometimes that column could get erased to NULL.
X~~~~~~~~~~~~~~~~~~~~~ enter EBS Request ID ~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000

DECLARE
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w, fnd_concurrent_requests f
where
           s.paddr = p.addr
and     s.sid = w.sid
and     f.ORACLE_SESSION_ID = s.AUDSID
and     f.request_id='&Request_id';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' CM Request: database session details associated with Request id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;

  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/
  • Script finds database session info by entering OS process ID on Apps node 
X~~~~~~~~~~~~~~~~~~~~~ enter OS process ID ~~~~~~~~~~~~~~~~~~~~~~~X
set echo off
set linesize 150
set verify off
set feedback off
set serveroutput on size 1000000
DECLARE
v_sql_id  v$sql.sql_id%type;
cursor sess_cursor is
select
        p.spid,
        s.process,
        s.sid,
        s.serial#,
        s.username,
        s.status,
        s.machine,
        s.terminal,
        s.program,
        s.module,
        s.action,
        s.sql_hash_value,
        s.sql_address,
        to_char(s.logon_time, 'DD-Mon-YYYY HH24:MI:SS') logontime,
        round((s.last_call_et/60),2) last_call_et,
        s.seconds_in_wait,
        s.client_identifier,
        s.sql_id,
        w.event,
        w.state
from
        v$session s, v$process p, v$session_wait w
where
           s.paddr = p.addr
and     s.sid = w.sid
and     s.process = '&EBS_OS_pid';

begin
dbms_output.put_line('------------------------------------------------------------------------------');
dbms_output.put_line(' Database session details associated with Process id ');
dbms_output.put_line('------------------------------------------------------------------------------');

for x in sess_cursor
loop
        -- select distinct sql_id into v_sql_id from v$sqltext v
        --  where v.hash_value = x.sql_hash_value
        --   and v.address = x.sql_address;
        dbms_output.put_line('DB PID ......... : ' || x.spid);
        dbms_output.put_line('EBS PID ........ : ' || x.process);
        dbms_output.put_line('SID ............ : ' || x.sid);
        dbms_output.put_line('Serial# ........ : ' || x.serial#);
        dbms_output.put_line('Username ....... : ' || x.username);
        dbms_output.put_line('Status ......... : ' || x.status);
        dbms_output.put_line('Machine ........ : ' || x.machine);
        dbms_output.put_line('Terminal ....... : ' || x.terminal);
        dbms_output.put_line('Program ........ : ' || x.program);
        dbms_output.put_line('Module ......... : ' || x.module);
        dbms_output.put_line('Action ......... : ' || x.action);
        dbms_output.put_line('SQL Hash Value . : ' || x.sql_hash_value);
        dbms_output.put_line('Logon Time ..... : ' || x.logontime);
        dbms_output.put_line('Last Call Et ... : ' || x.last_call_et || ' ' || 'min');
        dbms_output.put_line('Seconds in Wait. : ' || x.seconds_in_wait);
        dbms_output.put_line('User ID ........ : ' || x.client_identifier);
        dbms_output.put_line('Session State .. : ' || x.state);
        dbms_output.put_line('Wait Event ..... : ' || x.event);
        dbms_output.put_line('SQL ID ... ..... : ' || x.sql_id);
        dbms_output.put_line('SQL Text ....... : ');

        for y in (
                select sql_text
                from v$sqltext v
                where
                v.hash_value = x.sql_hash_value
                and v.address = x.sql_address
                order by v.piece)
        loop
                dbms_output.put_line(' ' || y.sql_text);
        end loop;
        dbms_output.put_line('Progress in DB (SID: '|| x.sid ||')');
        for z in (
               SELECT
               ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
               ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
               ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
               sl.sql_id, sl.message, sl.sql_hash_value,
               opname operation,
               target object,
               to_char(start_time, 'DD-MON-YYYY HH24:MI:SS') start_time
               FROM   v$session_longops sl
               WHERE  sl.sid = x.sid AND sl.serial# = x.serial#
                      AND totalwork > 0
              order by progress_pct desc, start_time asc)
        loop
                 dbms_output.put_line('----------------------------------------------------------');
                 dbms_output.put_line('  START TIME ...... : ' || z.start_time);
                 dbms_output.put_line('  Elapsed in min .. : ' || z.elapsed);
                 dbms_output.put_line('  Remaining ....... : ' || z.remaining);
                 dbms_output.put_line('  Complete_pct .... : ' || z.progress_pct);
                 dbms_output.put_line('  SQL ID .......... : ' || z.sql_id);
                 dbms_output.put_line('  SQL hash value .. : ' || z.sql_hash_value);
                 dbms_output.put_line('  db Object ....... : ' || z.object);
                 dbms_output.put_line('  Message ......... : ' || z.message);
       end loop;
  dbms_output.put_line('--------------------------------------------------------------------');
end loop;
end;
/

Posted by at No comments:
Labels:

Thursday, February 18, 2016

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.


Posted by at No comments:
Labels:

Saturday, February 13, 2016

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
Posted by at No comments:
Labels:

Saturday, February 6, 2016

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 EBSDEV_devserver1d

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.
Posted by at No comments:
Labels:
Subscribe to: Comments (Atom)