Category: EBSO

What is the difference between dbtechstack,dbtier and dbconfig modes in Cloning?

While Cloning Oracle Applications, adcgclone can be executed in 3 Modes on Database node.




1)perl adcfgclone.pl dbTier

 It configures the ORACLE_HOME on the target database node and  recreate the controlfiles.
 This is used in case of standby database/hot backups.   


2)perl adcfgclone.pl dbTechStack 

It configures the ORACLE_HOME on the target database tier node only. Relink the oracle home.

The below steps has to be performed manually
1. Create the Target Database control files.
2. Start the Target System Database in open mode
3. Run the library update script against the Database
cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME]
sqlplus “/ as sysdba” @adupdlib.sql [libext]
 Where [libext] should be set to ‘sl’ for HP-UX, ‘so’ for any other UNIX platform, 
or ‘dll’ for Windows.

3)perl adcfgclone.pl dbconfig

It configures the database with context file. Database must be open while running adcfgclone in dbconfig mode.
e.g 
cd $RDBMS_ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig <Context_file>


Where Target Context File is:
$RDBMS_ORACLE_HOME/appsutil/<SID_HOSTNAME>.xml



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.



Useful adop options and syntax

1.Using analytics parameter in adop apply phase




$ adop phase=apply analytics=yes


Specifying this option will cause adop to run the following scripts and generate the associated output files (reports):


ADZDCMPED.sql – This script is used to display the differences between the run and patch editions, including new and changed objects. The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop/<adop_sessionID>/<apply_directory>/<context_name>/adzdcmped.out.


ADZDSHOWED.sql – This script is used to display the editions in the system. The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop/<adop_sessionID>/<apply_directory>/<context_name>adzdshowed.out.


ADZDSHOWOBJS.sql – This script is used to display the summary of editioned objects per edition. The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop/<adop_sessionID>/<apply_directory>/<context_name>adzdshowobjs.out


ADZDSHOWSM.sql – This script is used to display the status report for the seed data manager. The output file location is: /u01/R122_EBS/fs_ne/EBSapps/log/adop/<adop_sessionID>/<apply_directory>/<context_name>adzdshowsm.out


Note: The analytics parameter should only be used when required, because of the extra processing needed.






2. flags=autoskip 

 e.g adop phase=apply patches=12345678  flags=autoskip 

 This option is an alternative for  “Continue as if it were successful”?  in adpatch, very useful in cases where patch failed to compile forms/reports and exiting.
 We need not restart the adop session just to compile a single forms (.fmb,.fmx) file.
 Make sure to review the autoskip.log logfile and fix the issues in autoskip log whenever you use autoskip flag in adop cycle.


3.  skipsyncerror=(yes|no)  [default: no]


 Specifies whether to ignore errors that may occur during incremental file system synchronization.  This might happen if you applied
 a patch in the previous patching cycle that had errors but decided to continue with the cutover.  When the patch is synchronized on
 the next patching cycle, the apply errors may occur again, but can be ignored.


4. wait_on_failed_job=(yes|no)  [default: no]


 Controls whether adop apply command exits when all workers have failed.  Instead of exiting, you can force adop to wait, and use the “adctrl” to retry failed jobs.

e.g adop phase=apply patches=7777 wait_on_failed_job=yes



5. Abort


The Online Patching Cycle can be aborted at any time prior to Cutover


e.g adop phase=abort


This is needed  if unrecoverable error happened or the user decides that patch is not needed.
If adop phase=apply failed, user should try abandon=yes first.
The abort command drops the database patch edition and  returns the system to normal runtime state.  Immediately following abort, you must also run a full cleanup and
fs_clone operation to fully remove effects of the failed online patching cycle.



            

What Happens in ADOP apply Phase

Apply Phase is executed in PATCH Filesystem
During Apply phase of an ADOP cycle, adop execute patch drivers to update Patch Edition
We can have multiple apply phases in an adop cycle, Multiple patches including customizations can be installed in this phase.


The production application is online and accessible to users. RUN filesystem is not affected by the changes.
Patches are applied to the copy (Patch Edition)
 Changes are made in the isolation of an Edition 
The running application is unaffected by these changes




workers 
specifies number of parallel workers, automatically calculated if not specified  
abandon 
abandons a patching session (if set to “yes”) and must have an opposite specification to that of parameter “restart”.
restart
 restarts a patching session (if set to “yes”) and must have an opposite specification to that of parameter “abandon”.

Example: $ adop phase=apply workers=4 abandon=yes restart=no patches=1234567,8909228






For more adop options and their usage Click here

Autopatch error: The worker should not have status ‘Running’ or ‘Restarted’ at this point

If you are restarting a failed patch session in oracle applications, sometimes you may encounter the error


AutoPatch error:
The worker should not have status ‘Running’ or ‘Restarted’ at this point.


Telling workers to quit…


All workers have quit.


Connecting to APPS……Connected successfully.


AutoPatch error:


Error running SQL and EXEC commands in parallel


Cause:


1. adpatch or adop process was killed from OS level while patch is being applied


2. Database shutdown or terminated






Solution:


Using adctrl utility, use option 4 to change the worker status to Failed




Review the messages above, then press [Return] to continue.


                    AD Controller Menu
     —————————————————


     1.    Show worker status


     2.    Tell worker to restart a failed job


     3.    Tell worker to quit


     4.    Tell manager that a worker failed its job


     5.    Tell manager that a worker acknowledges quit


     6.    Restart a worker on the current machine


     7.    Exit




Enter your choice [1] : 4


Enter the worker number(s)/range(s) or ‘all’ for all workers,
or press [Return] to go back to the menu : all


Status changed to ‘Failed’ for worker 1.
Status changed to ‘Failed’ for worker 2.
Status changed to ‘Failed’ for worker 3.
Status changed to ‘Failed’ for worker 4.
Review the messages above, then press [Return] to continue.






select option “1. Show Worker Status” , The worker status will be “Failed” 


Now select option 2. “2. Tell worker to restart a failed job” 




Enter the worker number(s)/range(s) or ‘all’ for all workers,
or press [Return] to go back to the menu : all


Status changed to ‘Fixed, restart’ for worker 1.
Status changed to ‘Fixed, restart’ for worker 2.
Status changed to ‘Fixed, restart’ for worker 3.
Status changed to ‘Fixed, restart’ for worker 4.




Restart adpatch 


In 11i, R12.1.X version of oracle applications


when applying patch using adpatch , Select option Yes When it prompts for


‘Do you wish to Continue with Previous adpatch Session’


In R12.2.x, Use restart and abandon parameters




adop phase=apply patches=123456 restart=yes abandon=no 


To restart the patch session from where it failed.