Change application passwords in E-Business suit 11i & 12i

FNDCPASS Tool:

to change the password in 11i and 12i we need to use FNDCPASS tool to do so.

To test the FNDCPASS just type the word in the command line as shown below:

========================================================$FNDCPASS

Usage: FNDCPASS logon 0 Y system/password mode username new_password

where logon is username/password[@connect]

system/password is password of the system account of that database

mode is SYSTEM/USER/ORACLE

username is the username where you want to change its password

new_password is the new password in unencrypted format

example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager ORACLE GL      GL1

FNDCPASS apps/apps 0 Y system/manager USER   VISION  WELCOME

========================================================

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS new_password

The above steps should change the user apps and applsys password.Oracle advice never to change the application users password using the "alter  user " command line tool.

Apps and applsys are the database schema of the oracle application.

When we try to change the password, internally the following will take place. 

Validations of current applsys & system password

Re-encrypt all password in FND_USER & FND_ORACLE_USERID

Update apps & applsys password in FND_ORACLE_USERID table.

Update DBA_USERS table as well.

FNDCPASS apps/apps 0 Y system/manager USER VISION new_password

The above command is used for changing the application level passwords like sysadmin etc. front end applications user password.

When we try to change the password, internally the following will take place. 

Validations of current applsys & system password and update VISION NEW password in FND_USER table.

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL_New_Password

The above command is used for changing password for any other product schema like AP,AR,GL etc.

When we try to change the password, internally the following will take place. 

Validations of current applsys & system password ,update GL new password in FND_ORACLE_USERID table andUpdate DBA_USERS table as well.

Notes to take into consideration:

1-Always backup the above 3 tables before using FND_USER

2-The tables that FNDCPASS uses are FND_USER and FND_ORACLE_USERID.

All the application passwords and schema passwords are stored in these two tables along with DBA_USERS.

3-Database core schema's like sys,system passwords can be changed using ALTER command in database.

4-Never update apps, applsys and other product schema password directly from database using the alter command.

5-Applsyspub is a public database schema used in Oracle Applications , This account password can be changed using FNDCPASS.

6-GUEST/ORACLE schema password can't be changed using FNDCPASS , This application user password can only be changed by java API.

In Oracle Apps 11i (Not needed in R12( when changing the password for APPS it is important to manually change the APPS password in the following:

$IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file

$FND_TOP/resource/wfmail.cfg

$AD_TOP/admin/template/CGIcmd.dat

Trigger to catch database failed login attemts

Sometimes in the database you get a failed login attempts and you need to catch who is doing so.I have created a login trigger which you can use to determine who is doing this attempts.The trigger will throw messages in the alert log with all the information needed to do so.
=========
Code:          |
==========================================================

-- sample trigger to write diagnostic info to alert.log
-- for failed login attempts (ora-1017)

create or replace trigger logon_denied_to_alert
after servererror on database
 declare
  message varchar2(120);
  IP varchar2(15);
  v_db_user varchar2(30);
  v_os_user varchar2(80);
  v_module varchar2(50);
  v_action varchar2(50);
  v_pid varchar2(10);
  v_sid number;
  v_machine varchar2(100);
begin
   IF (ora_is_servererror(1017)) THEN

   -- get IP for remote or ospid for local connections:
      if sys_context('userenv','network_protocol') = 'TCP' then
        IP := sys_context('userenv','ip_address');
      else
        select distinct sid into v_sid from sys.v_$mystat;
        SELECT p.SPID into v_pid FROM V$PROCESS p, V$SESSION v
             WHERE p.ADDR = v.PADDR AND v.sid = v_sid;
        select machine into v_machine from v$session where sid = v_sid;
      end if;    

      v_db_user := sys_context('userenv','AUTHENTICATED_IDENTITY');
      v_os_user := sys_context('userenv','os_user');
     
      dbms_application_info.READ_MODULE(v_module,v_action);
       
         message:= to_char(sysdate,'Dy Mon dd HH24:MI:SS YYYY');
         sys.dbms_system.ksdwrt(2,message);

         message:= 'logon denied connecting as ' || v_db_user || ' from '|| v_machine || ' '||nvl(IP,v_pid)||' '||v_os_user||
         ' with '||v_module||' '||v_action;

         sys.dbms_system.ksdwrt(2,message);

-- remove comments from next line to let it hang for 5 minutes
-- to be able to do more diagnostics on the operating system:
--         sys.dbms_lock.sleep(300);
    end if;
end;
/
-- end trigger
===============================================================

Single Client Access Name "SCAN" for Oracle Real application cluster 11g

I will discuss today a new feature that came up with Oracle 11g R 1.this new feature is known as single client access name(SCAN).prior to oracle 11g real application cluster the client connection will use each node virtual IP. When you add any new node or remove a node then the client tnsnames.ora needs to be updated with the new IP addition or removal.
Oracle 11G introduced the SCAN to handle this problem.The SCAN provide a single  name from clients  to access the database running in the cluster.
This is also very beneficial for client connecting using JDBC thin URLs and EZCONNECT to cluster with single hostname.

The components of the SCAN a cluster :
1-SCAN Name.
2-SCAN IP,Oracle recommends 3 IP addresses for SCAN for cluster.
3-SCAN Listener.

The SCAN Name is the cluster representative in the network .The SCAN Name has to be registered in the DNS and resolved using round-robin  algorithm.
To test the round-robin algorithm configuration just nslookup on the SCAN Name.The DNS server should return those 3 set of IP  addresses in different order each time.

E-Business suit 11i and 12i good notes

Abstract:

This document was created to help the Apps DBA with good documents exist on oracle support website http://metalink.oracle.com.

This document should help the Apps DBA with different topics like upgrade,cloning and health check. This is the first version and I will try to add more input to it.

Version Control:

This article was created 26-MAR-2013.I will try to update it when possible with new good documents I meet in day to day activities.

The Document was updated 28-MAR-2013.

E-Business Suit 11i and 12i Notes:

Health Check:

bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)

Concurrent manager:

Concurrent Requests Remain in Status Pending Normal For a Long Period Before Running [ID 749176.1]

Password:

Changing APPS Password Using Fndcpass Gives 'not able to decrypt password' Message [ID 733427.1]

Tried To Change Apps Password And A "Not Able To Decrypt" Message Came Up [ID 459601.1]

FNDCPASS Was Not Able To Decrypt Password For Applsyspub When Changing Apps Password [ID 454299.1]

Profile Options:

Note: 470102.1 - How To Check If a Profile Option Is Set In Oracle Application

Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus

How To Set A System Profile Value Without Logging In To The Applications [ID 364503.1]

How to Change Profile Option Value Without Forms [ID 943710.1]

Script To List The Values Of A Profile Option At All Levels [ID 803587.1]

Note: 282382.1 - How to Search all of the Profile Options for a Specific Value

Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?

Note: 282382.1 - How to Search all of the Profile Options for a Specific Value

Account Payable:

 11i: Payment Batch in Built Status Errors with APP-SQLAP-10771: Could not reserve record (Doc ID 358910.1)
APXINWKB - Unable to Update or Cancel Invoice APP-SQLAP-10771 Could Not Reserve Record (Doc ID 1200053.1)

Sysadmin and workflow:

How To Remove Error Notifications From The Worklist (Doc ID 357904.1)

SYSADMIN User Gets Thousands of WFERROR Notifications [ID 1299167.1]

Cloning:

Oracle E-business suite (11i & R12) Cloning Master Note

Master Note: Rapid Clone Documentation Resources For Release 11i and 12 [ID 799735.1]

 Rapid Clone of E-Business Suite Release 11i:

Note 230672.1 - 'Cloning Oracle Applications Release 11i with Rapid Clone'
Note 216664.1 - 'FAQ: Cloning Oracle Applications Release 11i'
Note 398619.1 - 'Clone Oracle Applications 11i using Oracle Application Manager (OAM Clone)'
Note 165195.1 - 'Using AutoConfig to Manage System Configurations with Oracle Applications 11i'
Note 364565.1 - 'Troubleshooting RapidClone issues with Oracle Applications 11i'
Note 760637.1 - 'Manually Cloning Oracle Applications Release 11i with 10g or 11g RAC'
Note 362473.1 - 'Cloning E-Business Suite Using Hot Backup for Minimal Downtime of Source Environment'
Note 233428.1 - 'Sharing the Application Tier File System in Oracle Applications 11i'
Note 243880.1 - 'Shared APPL_TOP FAQ'
Note 238276.1 - 'Migrating to Linux with Oracle Applications Release 11i'
Note 218089.1 - 'Autoconfig FAQ'
Note 270519.1 - 'Customizing an AutoConfig Environment'
Note 316806.1 - 'Oracle Applications Installation Update Notes, Release 11i (11.5.10.2)'

Rapid Clone of E-Business Suite Release R12:

 Note 406982.1 - 'Cloning Oracle Applications Release 12 with Rapid Clone'
Note 603104.1 - 'Troubleshooting RapidClone issues with Oracle Applications R12'
Note 559518.1 - 'Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone'
Note 387859.1 - 'Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12'
Note 549389.1 - 'Oracle Applications Release Notes, Release 12.0.4'
Note 394692.1 - 'Oracle Applications Documentation Resources, Release 12'

Generic - Rapid Clone of E-Business Suite Release 11i or R12:

Note 783188.1 - 'Certified RAC Scenarios for E-Business Suite Cloning'
Note 375650.1 - 'How To Run Rapid Clone (adcfgclone.pl) Non-Interactively'
Note 760772.1 - 'Cloning Oracle Application 11i /R12 with Rapid Clone - Database (9i/10g/11g) Using Hot Backup on Open Database'
Note 343917.1 - 'Frequently Asked Questions: Oracle E-Business Suite Support on x86-64'
Note 311717.1 - 'Frequently Asked Questions: Oracle E-Business Suite Support on Itanium'
Note 419475.1- 'Removing Credentials from a Cloned EBS Production Database'

Cloning via the E-Business Suite Plugin 4.0 (Grid Manager Plugin)

Note 1224313.1 - 'Getting Started with Oracle E-Business Suite Plug-in, Release 4.0'
Note 812294.1 - 'Troubleshooting Guide and Known Issues List for the Oracle Application Management Pack for Oracle E-Business Suite'
Note 250262.1 - 'RDA 4 - HCVE Users' Guide and Available Rule Sets' = OS pre-check diagnostics for EBS 11i & 12

(Doc ID 230672.1) Cloning Oracle Applications Release 11i with Rapid Clone  ==> Section 4: Advanced Cloning Methods >> 6. Cloning a RAC System=(Doc ID 760637.1)Manually Cloning Oracle Applications Release 11i with 10g or 11g RAC.

Clean Nonexistent Nodes or IP Addresses From FND_NODES [ID 260887.1]

Upgrade:

Additional Information: See Oracle 10gR2 Database Preparation  Guidelines for Oracle E-Business Suite Release 12 Upgrade (Doc ID:  403339.1) for more information.

11i Upgrade Document:

Note 881505.1 Oracle Applications Release 11i with Oracle 11g Release 2

Oracle AMP for grid control:

Certified RAC Scenarios for E-Business Suite Cloning (Doc ID 783188.1) 

Getting Started with Oracle E-Business Suite Plug-in, Release 4.0 (Doc ID 1224313.1)

 Step by Step to Install New AMP Plugin - Oracle E-Business Suite Plug-in 12.1.0.1.0 on top of Grid Control 12c (Doc ID 1463040.1)

https://blogs.oracle.com/stevenChan/entry/additional_platforms_certified_for_oracle

11i Browsers:
Note:285218.1-Recommended Browsers for Oracle E-Business Suite 11i
Note:290807.1-Upgrading Sun JRE (Native Plugin) with Oracle Applications 11i for Windows Clients

Security:
Note 189367.1 (Best Practices for Securing your E-Business Suite)