Monday, 25 July 2016

How To reset Password in 11GR2

Oracle 11gR2 introduces Case-sensitive passwords for database authentication.
Along with this if you wish to change the password (temporarily) and reset it back to old ,
you will find that password field in dba_users is empty.
Prior to 11g we could use following technique to change/restore password

SQL> create user sujeet identified by patna123;

User created.

SQL> grant create session to sujeet;

Grant succeeded.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select username,password from dba_users where username='sujeet';

USERNAME                       PASSWORD
------------------------------ ------------------------------
sujeet                           8DEC0D889E8E9A6B

SQL> alter user sujeet identified by patna123;

User altered.

SQL> conn amit/patna123
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user sujeet identified by values '8DEC0D889E8E9A6B';

User altered.

SQL> conn sujeet/patna123
Connected.
In 11g if you query password field, it will return NULL.

SQL> select username,password from dba_users where username='AMIT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
sujeet

Let's first see Case-sensitive password feature in 11g and then steps to change/restore passwords

SQL> create user sujeet identified by patna123;

User created.

SQL> grant connect,resource to sujeet;

Grant succeeded.

SQL> conn sujeet/Patna123
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sujeet/patna
Connected.
This behavior is controlled by "sec_case_sensitive_logon" initialization paramter.
If the value is true then it will enforce case sensitive passwords

SQL> select NAME,VALUE from V$SPPARAMETER where NAME='sec_case_sensitive_logon';

NAME                                     VALUE
---------------------------------------- --------------------
sec_case_sensitive_logon                 TRUE

SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn sujeet/PATNA123
Connected.
SQL> conn / as sysdba
Connected.

SQL> alter system set sec_case_sensitive_logon=true;

System altered.

SQL> conn sujeet/PATNA123
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sujeet/patna123
Connected.

Now to reset the password in 11g, we need to query spare4 column in user$ table

SQL> select spare4 from user$ where name='SUJEET';

SPARE4
--------------------------------------------------------------------------------
S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL> alter user sujeet identified by patna321;

User altered.

SQL> conn sujeet/patna321
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user amit identified by values 'S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB';

User altered.

SQL> conn sujeet/patna32
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.


SQL> conn sujeet/patna321
Connected.

As per Metalink Note 429465.1 , view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:

decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL)

For example:

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where rownum <5;

USERNAME                       PASSWORD
------------------------------ --------
SYS                            10G 11G
SYSTEM                         10G 11G
OUTLN                          10G 11G
DIP                            10G 11G
In this case it means both old and new-style hash values are available for the users,
 the new hash value is stored in the USER$.SPARE4 column,
as long as this remains NULL it means the password has not been
changed since the migration and the user will have the old case insensitive password.

SQL> create user test identified by test;

User created.

SQL> select USERNAME, PASSWORD_VERSIONS from dba_users where username in ('sujeet','TEST');

USERNAME                       PASSWORD
------------------------------ --------
sujeet                           11G
TEST                           10G 11G

As I had reset password using only spare4 string,
password will be case -sensitive irrespective of setting for sec_case_sensitive_logon
 parameter value. i.e why we see value of "11G"  for user sujeet

Update

When resetting the password, we need to also query password column from user$ column
if we wish to use case-insensitive feature in future. i.e
In my above example I used only spare4 column value to reset the password.
Now if I set sec_case_sensitive_logon=false , I will not be able to connect.

SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> conn sujeet/patna321
ERROR:
ORA-01017: invalid username/password; logon denied
In case we wish to use both, we need to set identified by values
 ‘S:spare4;password’. As I didnot use password field while resetting,
I find that password field in user$ is empty. To correct it,
 I had to change the password again.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
                               S:2D058976AE8FAD8ECFCDB93835ACEE94C83EDE19169209155BB81FEE7DBB

SQL>  alter system set sec_case_sensitive_logon=true;

System altered.

SQL> alter user amit identified by SUJEET;

User altered.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

So to reset the password, following needs to be used.

SQL> select password,spare4 from user$ where name='SUJEET';

PASSWORD                       SPARE4
------------------------------ ----------------------------------------------------------------------
9DEC0D889E8E9A6B               S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE

SQL> alter user sujeet identified by values 'S:F5DEBF680433864AA5A744C2368D8677A120939D083D74A2F6E99C5952AE;9DEC0D889E8E9A6B';

User altered.

Friday, 22 July 2016

ProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST


Outpost Processot has encoutered the below error.

This error can be found in OPP logs:
----------------------------------------
[GC 14000K->8905K(20284K), 0.0074280 secs]
ProcessorException: Unable to determine SMTP server to use: set FND_SMTP_HOST
        at oracle.apps.fnd.cp.opp.EmailDeliveryProcessor.deliver(EmailDeliveryProcessor.java:66)
        at oracle.apps.fnd.cp.opp.DeliveryProcessor.process(DeliveryProcessor.java:91)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)
----------------------------------------

Solution:

1. Log into System Administrator responsibility.

2. Navigate to Profile - System.

3. Query up the %SMTP% profiles.

4. Set the following profile values to the defined host and port:

FND:smtp Host > localhost
FND:smtp Port > 25


SMTP Authentication Feature in R12.1.3

What is my SMTP?

When you need to set an email client, one of the first questions is: What is my SMTP server?
Most server names are written in the form "smtp.domain.com" or "mail.domain.com": for instance, a Gmail account will refer to smtp.gmail.com. But that's not a unified rule, so you should pay attention and get the right SMTP parameters.
How to configure SMPT?
  • Go to Workflow Manager Screen and navigate to Workflow Notification Mailer page.
  • Edit the Workflow Mailer configuration, update "SMTP user" and "SMTP Password" parameters, SAVE and bounce the Workflow Service Container.

Authentication Mechanisms

    Workflow Mailer supports PLAIN, LOGIN and CRAM-MD5 mechanisms with JavaMail version 1.4.

How to check

On a non-SSL enabled SMTP server, it can be checked easily:  
$ - telnet smtp.host.com 25
Trying 10.11.12.13...
Connected to smtp.host.com (10.11.12.13).
Escape character is '^]'.
220 smtp.host.com ESMTP Sendmail 8.13.8/8.13.8; Tue, 17 Apr 2012 10:11:36 -0400
EHLO smtp.host.com
250-smtp.host.com Hello my.desktop.com [12.13.14.15], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-8BITMIME
250-SIZE
250-DSN
250-ETRN
250-AUTH LOGIN PLAIN
250-STARTTLS
250-DELIVERBY
250 HELP
************************************************************************************************

Verify the SMTP and IMAP Server details are correct and these components are working.
Make sure customer has set up a Mail Client that connects to the same IMAP account that the Java Mailer is using, and it is configured to send outbound emails using the same SMTP Server. Verify that you can send an email from this account and have it be received by the receiver. Verify that you can send an email to this IMAP account and that the email is received by this IMAP account.
· Following are the manual steps to verify SMTP and IMAP server configuration. The lines marked with ‘%%’ are the commands to enter. Please exclude ‘%%’ when entering the commands, ‘%%’ is there for clarification purposes. The following examples were run on a Sun Solaris platform, on other UNIX platforms, the return result may look slightly different, in any case, you will get the general idea.
Important: These tests must be run from the concurrent processing tier where the Java Mailer is running.
a) Verify SMTP Server: In this example, crmops02.us.oracle.com is the SMTP server running on the default port 25, andap102ses.us.oracle.com is the server where the Java Mailer(concurrent processing tier) runs. Wfuser is the mail account used by the Java Mailer. myemail@oracle.com is a valid email address to receive email from the Java Mailer.
%% telnet crmops02.us.oracle.com 25
Trying 144.25.76.117...
Connected to crmops02.us.oracle.com.
Escape character is '^]'.
220 crmops02.us.oracle.com ESMTP Sendmail 8.9.3 (PHNE_18546)/8.7.1; Fri, 31 Jan 2003 05:56:18 -0800 (PST)
%% EHLO ap102ses.us.oracle.com
250-crmops02.us.oracle.com Hello ap102ses.us.oracle.com [144.25.76.44] (may be forged), pleased to meet you
250-EXPN
250-VERB
250-8BITMIME
250-SIZE
250-DSN
250-ONEX
250-ETRN
250-XUSR
250 HELP
%% MAIL FROM: wfuser@crmops02.us.oracle.com
250 wfuser@crmops02.us.oracle.com... Sender ok
%% RCPT TO: myemail@oracle.com
250 myemail@oracle.com... Recipient ok
%% DATA
354 Enter mail, end with "." on a line by itself
%% Subject: Test message via CRMOPS2
%%
%% Test message body
%% .
250 FAA17833 Message accepted for delivery
%% quit
221 crmops02.us.oracle.com closing connection
Connection closed by foreign host.
Verify that myemail@oracle.com receives an email from wfuser01@crmops02.us.oracle.com. If not, then the SMTP server is not configured properly. Please contact the SMTP System Administrator to correct the problem.
b) Verify IMAP server: In this example, ap700lts.us.oracle.com is the IMAP server running on the default port 143. Each IMAP4 command must start with a number/character, and it does not matter if you use the same number/character for all the commands. Spaces are important and multiple spaces/tabs are not allowed. For each successful command completion the server ends the response by "n OK ....". If a command fails then the failure reason is printed.
%% telnet ap700lts.us.oracle.com 143
Trying 130.35.81.214...
Connected to ap700lts.us.oracle.com.
Escape character is '^]'.
* OK Domino IMAP4 Server Release 5.0.11 ready Sun, 23 Nov 2003 10:26:49 -0800
%% 1 login wfuser welcome
1 OK LOGIN completed
%% 1 select "INBOX"
* 23 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 0] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 select "DISCARD"
* 54 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 6] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 select "PROCESSED"
* 58 EXISTS
* 0 RECENT
* OK [UIDVALIDITY 4] UIDs valid
* FLAGS (\Flagged \Seen \Answered \Deleted \Draft)
* OK [PERMANENTFLAGS (\Flagged \Seen \Answered \Deleted \Draft)] Permanent flags
1 OK [READ-WRITE] SELECT completed
%% 1 logout
* BYE ap700lts.us.oracle.com IMAP4rev1 server terminating connection
1 OK LOGOUT completed
Connection closed by foreign host.
c) Patch 3265133 (included in Rollup 5.1 - patch 3409889) provides a simple commandline interface that uses native JavaMail APIs. This interface can be used to validate IMAP / SMTP connectivity from the concurrent-tier where mailer runs. Syntax can be found by running the following on the concurrent-tier after initializing the APPS environment:
Values in <...> are the values defined (or to be defined) for mailer configuration using OAM.
IMAP Server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver= -Dport=143
-Daccount= -Dpassword= -Dconnect_timeout=120
-Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer
Example output:
----------------
bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Dserver=ap700lts.us.oracle.com -Dport=143
-Daccount=notesuser2@ap700lts.us.oracle.com -Dpassword=XXXXX -Dconnect_timeout=120 -Dfolder=INBOX oracle.apps.fnd.wf.mailer.Mailer
Server ap700lts.us.oracle.com at port 143 is reachable
Successfully connected to the IMAP account
Testing existense of folder:INBOX
Folder [INBOX] exists
Note: Special folders like Inbox/Trash may not get listed on some IMAP servers
Folders defined are:
INBOX
Drafts
Sent
PROCESS
DISCARD
------------------------------------------
Note: Email Processing (PROCESS and DISCARD Folder) should get listed like "PROCESS" and "DISCARD" in output above. If not, these folders are required to be created by connecting to the IMAP / Inbound Email account with any email client (e.g. Netscape Messenger or Microsoft Outlook). These folders should be created before they are specified in the mailer configuration within OAM. The notification mailer may not be able to access folders that were created using command line tools outside the e-mail client.
SMTP Server:
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=
-Dport=25 -Daccount= -Dconnect_timeout=120
oracle.apps.fnd.wf.mailer.Mailer
Example Output:
-----------------
bash-2.05$ $AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Dserver=ap6120rt.us.oracle.com -Dport=25
-Daccount=notesuser6@ap700lts.us.oracle.com -Dconnect_timeout=120 oracle.apps.fnd.wf.mailer.Mailer
Server ap6120rt.us.oracle.com at port 25 is reachable
Successfully connected to the SMTP account
------------------------------------------
If above tests are successful from commandline, mailer should also be able to connect to IMAP / SMTP server. If these tests are not successful then mailer will also not be able to connect. Then it is IMAP / SMTP server issue that administrators need to take a look at.

How to find version in oracle EBSR12.1.3


How to check my jre version R12.1.3 EBS?

[applmgr@sujeet ~]$ cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version
sun_plugin_version=1.7.0_021

How to check my java version in R12.1.3 EBS?
[applmgr@sujeet ~]$ $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_14-b05"

How to check my forms version in R12.1.3 ebs?

[applmgr@sujeet ~]$ $ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

How to check my Apache version in R12.1.3 EBS?

[applmgr@sujeet ~]$ $IAS_ORACLE_HOME/Apache/Apache/bin/httpd -version
Server version: Oracle-Application-Server-10g/10.1.3.4.0 Oracle-HTTP-Server
Server built:   Jul  7 2008 14:58:00


How to check my pl/sql version in R12.1.3 EBS?

[applmgr@sujeet ~]$ $ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version
PL/SQL Version 10.1.0.5.0 (Production)

How to find workflow version in oracle apps R12.1.3?

SQL> select TEXT from WF_RESOURCES where
NAME='WF_VERSION';  2

TEXT
--------------------------------------------------------------------------------
2.6.0