Monday, March 14, 2016

Enable Forms Runtime Diagnostics (FRD)

1. R12.1 Forms can be implemented in servlet mode or socket mode. Check file $FORMS_WEB_CONFIG_FILE (or $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg) to find which mode forms runs:

In Servlet mode:
serverURL=/forms/lservlet
connectMode=servlet    

In Socket mode:
serverURL=(should be blank)
connectMode=Socket

This can be also verified by Profile option "ICX: Forms Launcher" value https://hostname.domain:port/forms/frmservlet on site level.

2. Doc ID 438652.1 ( R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications) gives ways to capture all events that occur in a form session. Unfortunately if the forms does launch, no trace log will be generated. I tested three ways in R12.1.

1) To enable FRD on Site level:
In appsweb.cfg, set "record=collect" as shown below (under ENVIRONMENT SPECIFIC PARAMETERS section)

# Sub argument for other params
record=collect

also can specify the log name
log=site1.log 

2) To enable FRD on User level:
Change Profile option "ICX: Forms Launcher" on user level to https://hostname.domain:port/forms/frmservlet?record=collect

Then, launch forms after logging onto EBS (usually without any services downtime, but may need to bounce Apache or clear cache). Forms shall popup a note "Forms Runtime Diagnostics is enabled, Please note this can affect performance." before forms shows up.

By default, trace file collect_<pid> gets written in folder $FORMS_TRACE_DIR, where <pid> is the process identifier.  "grep" the pid to find which os process created it.

Optionally, in appsweb.cfgs, specify the log name
log=user1.log
(Note: Log file site1.log or user1.log will be saved in folder $FORMS_TRACE_DIR. This folder may need manually cleaning from time to time.)

3) Enabling FRD (in R12.1) by URL https://hostname.domain:port/forms/frmservlet?record=collect ( or https://hostname.domain:port/forms/frmservlet?record=collect+log=user1.log )
Enter EBS userID/password to access forms directly. But it may give error:
APP-FND-01542: This Applications Server is not authorized to access this database.

To get this working, modify current context file by changing “s_appserverid_authentication” value from SECURE to OFF. Then shutdown apps and run Autoconfig.

3. When QA uses Vugen 12 for scripting and uses HP Performance Center (PC) 12 to run the scripts to test EBS R12.1.3 site performance, I was asked to change Profile option "ICX: Forms Launcher" on user level from https://hostname.domain:port/forms/frmservlet to
https://hostname.domain:port/forms/frmservlet?play=&record=names

I did not know what that profile value really does. But after the change, performance testing worked by PC 12 for our QA.

NOTES:  For JWS, do NOT use Method 2 of Option 1 in Doc ID 438652.1 or follow Doc ID 373548.1 (How To Collect And Use Forms Trace (FRD) in Oracle Applications Release 12) to enable FRD by setting up profile option Forms Runtime Parameters to "record=forms tracegroup=0-97". It will give error "FRM-90926: Duplicate Parameter on Command Line" without launching forms.
Posted by at No comments:
Labels:

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:
Subscribe to: Comments (Atom)