Month: April 2016

What are the daily activities of Oracle Applications DBA?

  1. Maintaining Oracle Application File System and Database using AD Utilities.
  2. Starting, stopping and troubleshooting issues related to Application server components.
  3. Using adadmin to relink executables, generate forms/reports,generate jar files, compile/validate APPS schema.
  4. Analyzing patch pre-requisites and Applying the Patches using AD Patch Utility on Application file System and Technology Stack.
  5. Applying Mini Packs, Family Packs, Maintenance Pack and Rollup patches.
  6. Applying database patches using opatch utility and troubleshooting issues related to OPatch.
  7. System administration activities like creating users, assigning Responsibilities, defining custom managers.
  8. Changing configuration files as per the requirement.
  9. Changing APPS password and Application database users’ password for security.
  10. Performing Healthchecks.
  11. Splicing off-cycle products using adsplice utility.
  12. Cloning of Oracle Applications 11i from Production to Test Instance.
  13. Taking backups- physical, logical, RMAN and performing Restore, Recovery.
  14. Troubleshooting issues related to concurrent managers.
  15. Managing System Configuration using Autoconfig.
  16. Modifying initialization parameters as per the requirement-PFILE AND SPFILE.
  17. Reorganization of tables that are fragmented.
  18. Creating users and assigning appropriate roles, privileges.
  19. Managing tablespaces and data files.
  20. Monitoring alert log file.
  21. Troubleshooting ORA- errors.
  22. Analyzing AWR reports.

How to Recover lost APPS Password in Oracle Applications R12?





Have you ever forgot or lost apps password in R12. In Oracle Applications 11i, It is easy to recover apps password as it is stored in application files appsweb.cfg and wdbsvr.app.
Apps password is not available in the application files in R12. We can still recover the lost password by creating a decrypt function.


Steps to retreive apps password in R12:


1.Create function Using below syntax


SQL> create or replace FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2
AS
LANGUAGE JAVA NAME ‘oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String’;
4
5 /


Function created.




2. Find the encrypted password using below query


SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME=’GUEST’;


ENCRYPTED_FOUNDATION_PASSWORD
——————————————————————————–
JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A




3.Run the below query to decrypt the password




SQL> SELECT apps.decrypt_pin_func(‘GUEST/ORACLE’,’JH89E045BEA551FD01A8D71029ACA879F209U8BD898F451EBDB987C818E7608CE0CC0167BDCAF9D1D04D9C9CEE418CFE615A’) from dual;


APPS.DECRYPT_PIN_FUNC(‘GUEST/ORACLE’,’ZG761B2FB6A2C49BC802F57E55AC26637388B4EA99
——————————————————————————–
PASSWORD




4. Verify Database Connection using the password retreived




SQL> conn apps/PASSWORD
Connected.

How to enable Trace for Application User in Oracle Applications 11i/R12?



Enabling Tracing for a Application User makes diagnostics easier to analyze.Since only the sql operations performed by a specific User are Traced.





Steps to Enable Trace for Application User:


1.Login to Oracle Applications and select the System Administrator responsibility. 
Navigate to  Profile –>  System 


2.Search for Profile Option ‘Initialization SQL Statement – Custom’ .Also Specify the username whom you wish to enable tracing during Search.


3.
Under username column Update the below line


BEGIN FND_CTL.FND_SESS_CTL(”,”, ”, ‘TRUE’,”,’ALTER SESSION SET TRACEFILE_IDENTIFIER=’||””||’USER_TRACE_IDENTIFER’ ||””||’ EVENTS =’||””||’ 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ‘||””); END;


Note : Level cane be 4,8,12 depends on your requirement 




Save the Changes done in Profile Option


4. Be careful while updating the Profile Option, Any incorrect Syntax may Prevent user from Logging in


5. Inform User to Re-login and Perform the Activity which needs to be traced.


6. Trace Files can be found in the directory specified by “user_dump_directory” parameter on the database server.
Use the identifier given in Profile Option to search the trace file in udump directory.


7. After the issue is reproduced and required trace files are collected for analysis, disable Tracing from Front End.
Its recommended to disable the trace immediately after the diagnostics are collected, as it will generate huge logfiles.


8. Navigate to system=> Profile form under System Administrator Responsibility.


Query for the username  and Profile Option name “Initialization SQL Statement – Custom”


Remove the values and make it blank.


Save the changes.