Purge Obsolete oracle workflow Logs and keeping the logs for one or two years.

I will discuss today how i purged successfully fixed asset workflow logs and kept the latest 2 years logs only.to do this you have to run the concurrent request "Purge Obsolete Workflow Runtime Data”. The concurrent request takes some parameters in order to delete the fixed assets logs.

To proceed further enter the parameters below and leave all others blank:

Item Type FA Account Generator
Item Key - leave blank
Age -730 -- which is the age in day

persistence type Permanent

To determine the persistence type use the below query:

SQL> select name,persistence_type,persistence_days from wf_item_types where name ='FAFLEXWF';

NAME     PERSISTE PERSISTENCE_DAYS

-------- -------- ----------------

FAFLEXWF PERM

We can see that this type is permanent so we choose permanent not temporary.

I choose commit every 5000 transaction because i want not to overload the undo segment.

Then submit the request as shown in the attached screen.

What if you want to purge all the permanent of temporary workflow and keep only the past 1 year logs then you will change the parameters to be as shown in the  below screen:

The following tables are affected by Purge Obsolete Workflow Runtime Data
FNDWFPR concurrent request:

WF_ITEM_ACTIVITY_STATUSES
WF_COMMENTS
WF_ITEMS
WF_ITEM_ACTIVITY_STATUSES_H 
WF_ITEM_ATTRIBUTE_VALUES
WF_ACTIVITY_TRANSITIONS 
WF_ACTIVITY_ATTR_VALUES
WF_PROCESS_ACTIVITIES
WF_ACTIVITY_ATTRIBUTES_TL
WF_ACTIVITY_ATTRIBUTES
WF_NOTIFICATIONS 
WF_ACTIVITIES
WF_ACTIVITIES_TL
WF_NOTIFICATION_ATTRIBUTES
WF_LOCAL_USER_ROLES
WF_LOCAL_ROLES
WF_USER_ROLE_ASSIGNMENTS

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.