Workflow Notification mailer issue

Issue :

Workflow Differed Job got filled so the workflow notification mailer couldn't send mail.

Solution :

I saw the particular user mail status in that all status was shows expired.

So I submitted "Move Messages from Exception to Normal Queue of Workflow 

Agent" concurrent request. It will ask parameters then we need to give 

WF component name in my case I was given WF_NOTIFICATION_OUT.

Reference:

1. Howto Move Notifications From Exception Queue To Normal Queue?ID472204.1

2. FNDWF_MOVE_MSGS_EXCEP2NORMAL "The agent is not found"[ID 760983.1]

3. Error APP-FND-1030 Is Displayed When Trying To Submit "Move messages 

from Exception to Normal Queue of Workflow Agent" FNDWF_MOVE_MSGS

_EXCEP2NORMAL [ID 1451820.1]

For Enabling De-Queue we need run below procedure,

exec dbms_aqadm.start_queue(

queue_name=>'APPLSYS.AQ$_WF_NOTIFICATION_OUT_E',dequeue=>TRUE,

enqueue=>FALSE)

Regards,

Vijay.

Steps For Cloning Process

Cloning process:

Ans :

1. Run adpreclone as applmgr and oracle user on source Perl adpreclone.pl dbTier as oracle user Perl adpreclone.pl appsTier as applmgr user

2. Take the cold/hotbackup of source database

3. Copy the five directories appl,comn,ora , db,data to target

4. Rename the directories, and change the permisssion

5. Set the inventory in oraInst.loc

6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold

7. If the backup type is hotbackup then Perl adcfgclone.pl dbTechStack.
   Create the control file on target from the control script trace file from source Recover the database   Alter database open resetlogs

8. Run autoconfig with the ports changed as per requirement in xml.

9. Run perl adcfgclone.pl appsTier as applmgr

10. Run autoconfig with the ports changed as per requirement in xml.

Location of adpreclone.pl for oracle user:

Ans : RDBMS_ORACLE_HOME/appsutil/scripts/

Location of adpreclone.pl for applmgr user:

Ans : $COMMON_TOP/admin/scripts/

Location of adcfgclone.pl for oracle user:

Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin

Location of adcfgclone.pl for applmgr user:

Ans : $COMMON_TOP/clone/bin


Reference : http://appsdba.info/index.php?module=pagemaster&PAGE_user_op=view_page&PAGE_id=58&MMN_position=53:53

Step by Step Procedure To Add a Swap File Under Linux

In Linux, as in most other Unix-like operating systems, it is common to use a whole partition of a hard disk for swapping. However, with the 2.6 Linux kernel, swap files are just as fast as swap partitions, although I recommends using a swap partition. The administrative flexibility of swap files outweighs that of partitions; since modern high capacity hard drives can remap physical sectors, no partition is guaranteed to be contiguous. You can add swap file as a dedicated partition or use following instructions to create a swap file.

Procedure To Add a Swap File Under Linux

You need to use the dd command to create swap file. The mkswap command is used to set up a Linux swap area on a device or in a file.

Step #1: Login as the root User

Open a terminal window (select Applications > Accessories > Terminal) or login to remote server using the ssh client.  Switch to the root user by typing su - and entering the root password, when prompted

Step #2: Create Storage File

Type the following command to create 512MB swap file (1024 * 512MB = 524288 block size):
# dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
Where,

1. if=/dev/zero : Read from /dev/zero file. /dev/zero is a special file in that provides as many null characters to build storage file called /swapfile1.
2. of=/swapfile1 : Read from /dev/zero write stoage file to /swapfile1.
3. bs=1024 : Read and write 1024 BYTES bytes at a time.
4. count=524288 : Copy only 523288 BLOCKS input blocks.

Step #3: Set Up a Linux Swap Area

Type the following command to set up a Linux swap area in a file:

# mkswap /swapfile1

Setup correct file permission for security reasons, enter:

# chown root:root /swapfile1
# chmod 0600 /swapfile1

A world-readable swap file is a huge local vulnerability. The above command make sure only root user can read/write to the file. Finally, activate /swapfile1 swap space immediately, enter:

# swapon /swapfile1

To activate /swapfile1 after Linux system reboot, add entry to /etc/fstab file. Open this file using a text editor such as vi:

# vi /etc/fstab

Append the following line:

/swapfile1 swap swap defaults 0 0

Save and close the file. Next time Linux comes up after reboot, it enables the new swap file for you automatically.

How do I Verify Swap is Activated or Not?

Simply use the free command to Check swap size Changes. 

$ free -m

Regards,

Vijay.

Compatible Initialization Parameter and Upgrade/Downgrade

Compatibility: 

The COMPATIBLE initialization parameter controls the compatibility level of your database.

This parameter controls the database behavior for example whether a feature will work for a database. For example if database version is 10.2.0.4 but compatible parameter is set to 9.2 then certain feature like RMAN compression will not work. 

When you no longer need the ability to downgrade your database back to its original version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

InCompatible :

When you upgrade to a new release of Oracle Database, certain new features might make your database incompatible with your previous release.

Conditions for Incompatible:

Your upgraded database becomes incompatible with your previous release under the following conditions: 

A new feature stores any data on disk (including data dictionary changes) that cannot be processed with your previous release. 

An existing feature behaves differently in the new environment as compared to the old environment. This type of incompatibility is classified as a language incompatibility.  

Purpose:

The COMPATIBLE initialization parameter enables or disables the use of features in the database that affect file format or disk and also you prevents from downgrading to your previous release.

After upgrading to Oracle Database new release, you can set the COMPATIBLE initialization parameter to match the release number of the new release.

COMPATIBLE Initialization Parameter and upgrade to 10g and 11g :

Oracle Database Release	Default Value	Minimum Value	Maximum Value
Oracle Database 9i Release 2 (9.2)	8.1.0	8.1.0.0.0	9.2.0.n.n
Oracle Database 10g Release 1 (10.1)	10.0.0	9.2.0.0.0	10.1.0.n.n
Oracle Database 10g Release 2 (10.2)	10.2.0	9.2.0.0.0	10.2.0.n.n
Oracle Database 11g Release 1 (11.1) Oracle Database 11g Release 2 (11.2)	11.0.0 11.2.0	10.0.0.0.0 10.0.0.0.0	11.1.0.n.n 11.2.0.n.n

You have to set the Minimum compatible value to the current database while upgrading to new release.

The COMPATIBLE Initialization Parameter and downgrade from 11g:

If, after upgrading, you want to downgrade, and you have never set the COMPATIBLE value to 11.0.0 or higher, then the COMPATIBLE initialization parameter must be left (not changed) as follows after the upgrade: 

        1. Set to 10.1.0 if you upgraded from Oracle Database 10g Release 1 (10.1) 
        2. Set to 10.2.0 or earlier if you upgraded from Oracle Database 10g Release 2 (10.2)

Checking the Current Value of the COMPATIBLE Initialization Parameter:

You can  check the current value of the COMPATIBLE initialization parameter, enter the following SQL statement: 

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

SQL> show parameter compatible;

When to Set the COMPATIBLE Initialization Parameter:

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for Oracle Database 11g Release 1 (11.1).

However, after you do this, the database cannot subsequently be downgraded

Setting the COMPATIBLE Initialization Parameter

Complete the following steps to set the COMPATIBLE initialization parameter to a higher value: 

      1) Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional). 

Raising the COMPATIBLE initialization parameter might cause your database to become incompatible with earlier releases of Oracle Database, and a backup ensures that you can return to the earlier release if necessary by restoring the backup. 

     2) If you are using a server parameter file, then complete the following steps:

           i)  Update the server parameter file to set or change the value of the COMPATIBLE initialization parameter.  

For example, to set the COMPATIBLE initialization parameter to 11.0.0, enter the following statement:
              SQL> ALTER SYSTEM SET COMPATIBLE = '11.0.0' SCOPE=SPFILE; 

          ii) Shut down and restart the instance. 

   3) If you are using an initialization parameter file, then complete the following steps: 

          i) Shut down the instance if it is running:                 

               SQL> SHUTDOWN IMMEDIATE 

         ii)Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter. 

For example, to set the COMPATIBLE initialization parameter to 11.0.0, enter the following in the initialization parameter file: 
           COMPATIBLE = 11.0.0 

        iii) Start the instance using STARTUP.

Checking the Current Value of the COMPATIBLE Initialization Parameter:

You can  check the current value of the COMPATIBLE initialization parameter, enter the following SQL statement: 

SQL> SELECT name, value, description FROM v$parameter WHERE name = 'compatible';

SQL> show parameter compatible;

Downgrade the Oracle Database :

Make sure your database is compatible with the release to which you are downgrading before you perform the downgrade steps in this section.

Complete the following steps to downgrade your release 10.1 database to the previous Oracle Database release:

Log in to the system as the owner of the release 10.1 Oracle home directory.
 

1. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
2. Start SQL*Plus.
3. Connect to the database instance as a user with SYSDBA privileges.
4. Start up the instance in DOWNGRADE mode:

SQL> STARTUP DOWNGRADE

You may need to use the PFILE option to specify the location of your initialization parameter file.

Set the system to spool results to a log file for later verification of success:

SQL> SPOOL downgrade.log

Run dold_release.sql, where old_release refers to the release to which you are downgrading. See Table 7-1 to choose the correct script. Each script provides a direct downgrade to the release specified in the "Downgrading To" column.

To run a script, enter the following:

SQL> @dold_release.sql

Reference :

1.      COMPATIBLE Initialization Parameter and Upgrade/Downgrade in 11g R1 or 11gR2 (Doc ID 444709.1)

2.      11g Compatible Initialization Parameter Setting When Upgrading Agile (Doc ID 1324734.1)

3.      How To Change The COMPATIBLE Parameter And What Is The Significance? (Doc ID 733987.1)

4.      How to Downgrade from Oracle RDBMS 10gR2? (Doc ID 398372.1)

Regards, 

Vijay.

Find Application User Password in Oracle E-Business Suite 11i & R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.

We can use different sqls to find apps password and application user passwords.

Connect as an Apps User to do the following,

Create Package

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END XXARTO_GET_PWD;

/

 Package created.

Create Package Body
 

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2

AS

LANGUAGE JAVA

NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END XXARTO_GET_PWD;

/

Package body created.

Find apps user password in 11i

SELECT (SELECT XXARTO_GET_PWD.Decrypt (

UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))

FROM DUAL)),

Usertable.Encrypted_Foundation_Password)

FROM DUAL)

AS Apps_Password

FROM applsys.Fnd_User Usertable

WHERE Usertable.User_Name LIKE

UPPER( (SELECT SUBSTR (

Fnd_Profile.VALUE ('Guest_User_Pwd'),

1,

INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')

- 1)

FROM DUAL));

APPS_PASSWORD
--------------------------------------------------------------------------------
APPS

Find other application user password in 11i

Select Usertable.User_Name , (Select XXARTO_GET_PWD.Decrypt (Upper ((Select
(Select XXARTO_GET_PWD.Decrypt (Upper ((Select Upper (Fnd_Profile.Value ('Guest_User_Pwd'))
From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password
From Fnd_User Usertable
Where Usertable.User_Name Like Upper ((Select Substr (Fnd_Profile.Value ('Guest_User_Pwd'),
1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual)))) ,Usertable.Encrypted_User_Password)
From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username');

Enter value for username: vijay
old   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username')
new   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('vijay')

USER_NAME                  ENCRYPTED_USER_PASSWORD
-------------------------------------------------------------
VIJAY                               ORACLE

Find other application user password in R12

ALTER SESSION SET current_schema = apps;

Select Usr.User_Name, Usr.Description,

       Get_User_Pwd.Decrypt((Select (Select Get_User_Pwd.Decrypt

       (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password)From Dual) As Apps_Password

       From Fnd_User Usertable  Where Usertable.User_Name = (Select Substr(Fnd_Web_Sec.Get_Guest_Username_Pwd,1,

       Instr(Fnd_Web_Sec.Get_Guest_Username_Pwd,'/') - 1) From Dual)), Usr.Encrypted_User_Password) Password

  From Fnd_User Usr

 Where Usr.User_Name = '&User_Name';

Find  application user password (for example SYSADMIN) in 11i
 

SELECT Usertable.User_Name,
(SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))
FROM DUAL)),
Usertable.Encrypted_Foundation_Password)
FROM DUAL)
AS Apps_Password
FROM applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE
UPPER( (SELECT SUBSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),1,
INSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),
'/')
- 1)
FROM DUAL)))),
Usertable.Encrypted_User_Password)
FROM DUAL)
AS Encrypted_User_Password
FROM Applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE UPPER ('&Username');

Enter value for username: sysadmin
old  24: WHERE Usertable.User_Name LIKE UPPER ('&Username')
new  24: WHERE Usertable.User_Name LIKE UPPER ('sysadmin')

USER_NAME                     ENCRYPTED_USER_PASSWORD
--------------------------------------------------------------------------------
SYSADMIN                              SYSADMIN

Regards,
Vijay.

RC-50004: Fatal: Error occurred in ApplyAppsTier

Cause :

When i ran the adcfgclone.pl in R12 application tier i got the RC-50004: Fatal Error.

RC-50004: Fatal: Error occurred in ApplyAppsTier
 

StackTrace -
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.NoSuchMethodError: oracle.apps.ad.autoconfig.InstantiateFile.writeReport(Ljava/util/Vector;Loracle/apps/ad/util/LogFile;I)V
at oracle.apps.ad.clone.ApplyApplTop.checkAutoConfigErr(ApplyApplTop.java:590)
at oracle.apps.ad.clone.ApplyApplTop.runAutoConfig(ApplyApplTop.java:558)
at oracle.apps.ad.clone.ApplyApplTop.doConf(ApplyApplTop.java:339)
at oracle.apps.ad.clone.ApplyApplTop.doApply(ApplyApplTop.java:382)
at oracle.apps.ad.clone.ApplyApplTop.<init>(ApplyApplTop.java:267)
at oracle.apps.ad.clone.ApplyAppsTier.<init>(ApplyAppsTier.java:105)
... 6 more
　
ERROR while running Apply...
Fri Apr 26 09:10:48 2013
ERROR: Failed to execute /oracle2/TEST/apps/apps_st/comn/clone/bin/adclone.pl
Please check logfile.

Solution :

Check the 10.1.2 and 10.1.3 home entry in the oraInst.loc file and change the  Production orainventry location to test orainventry location and try again to run the adcfgclone.pl script.

Step 1:

bash-cd /oracle2/TEST/apps/tech_st/10.1.3
bash-3.00$ cat oraInst.loc
inventory_loc=/oracle2/TEST/apps/tech_st/10.1.3/admin/oraInventory

change the oraInventory location in 10.1.3 home oraInst.loc file using the vi editor.

Step 2 :

bash- cd /oracle2/TEST/apps/tech_st/10.1.2
bash-3.00$ pwd
/oracle2/TEST/apps/tech_st/10.1.2
bash-3.00$ cat oraInst.loc
inventory_loc=/oracle2/TEST/apps/tech_st/10.1.2/admin/oraInventory

change the oraInventory location in 10.1.2 home oraInst.loc file using the vi editor.

Regards,
Vijay.

Process of Adpreclone.pl script in DB and Apps

adpreclone.pl : This is the preparation phase, will collects information about the source system, creates a cloning stage area, and generates templates and drivers. All of these are to reconfigure the instance on a Target machine.
 

Preclone will do the following:

Convert Symbolic links
All symbolic links pointing to a static path will be converted into relative paths

Create templates
Any files under the $ORACLE_HOME that contain system specific information, will be replicated and converted into a template. These templates are placed into the $ORACLE_HOME/appsutil/template directory.

Create driver(s)
A driver file, relating to these new templates is created called instconf.drv. This contains a list of all the templates and their locations, and the destination configuration files that these templates will create.
This driver file is called instconf.drv and is placed into directory
$ORACLE_HOME/appsutil/driver

Create Stage area
A clone stage is created containing the required java code and scripts to reconfigure the instace on the Target machine

Rapid Clone stage area:

dbTier : $ORACLE_HOME/appsutil/clone
appsTier(s) - $COMMON_TOP/clone

The stage area(s) consist of the following directories:-
 jre used to run the java code on the Target machine.
 bin contains the RapidClone scripts that can be run on the Target machine

·                        adclone.pl is the main cloning script

·                        adcfgclone.pl  is used to configure the Target system, this calls adclone.pl

·                        adclonectx.pl is used to clone a Source XML file manually

·                        adaddnode.pl is used to add a new node to the Patch History tables

·                        adchkutl.sh checks for existence of require O/S utils, cc, make, ar and ld
  

 jlib contains all the Rapid Clone java code, jdbc libraries etc
 context contains templates used for a Target XML file
 data (Database Tier only) contains the driver file, and templates used to generate the control file SQL script
 adcrdb.zip contains the template and list of data files on the Source
 addbhomsrc.xml contains information on the datafile mount points of the Source
 appl (Applications Tier only) this is used when merging appltops, i.e Multi-node to Single node cloning
Executing adpreclone.pl will create a log file:-
 

Rapid Clone:

dbTier : $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/StageDBTier_xxxxxx.log
appsTier : $APPL_TOP/admin/$CONTEXT_NAME/log/StageAppsTier_xxxxxx.log
Once this adpreclone.pl step has been completed successfully, all the java .class files under the following directories should be identical to those under $JAVA_TOP/oracle :

RDBMS $ORACLE_HOME/appsutil/java/oracle
RDBMS $ORACLE_HOME/appsutil/clone/jlib/java/oracle
$COMMON_TOP/clone/jlib/java/oracle 

Adpreclone Process in Detail :

When running adpreclone.pl dbTier :

This will run in two steps Techstack and database.
 
Techstack:

It will create following directories in the ORACLE_HOME/appsutil/clone
Jlib, db, data where "Jlib" relates to libraries "db" will contain the techstack information, "data" will contain the information related to datafiles and required for cloning.
Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv
Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml
 
Prepare database for cloning:

This includes creating datbase control file script and datafile location information file at
$ORACLE_HOME/appsutil/template
adcrdbclone.sql, dbfinfo.lst
Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driver
data.drv
Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsoui
 
When Running adpreclone appsTier :

This will create stage directory at $COMMON_TOP/clone. This also run in two steps.
 
Techstack:
 
Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template

Creates Techstack driver files for
 
IAS_ORACLE_HOME/appsutil/driver/instconf.drv
806_ORACLE_HOME/appsutil/driver/instconf.drv
 
APPL_TOP preparation:

-   It will create application top driver   file$COMMON_TOP/clone/appl/driver/appl.drv
-   Copy JDBC libraries$COMMON_TOP/clone/jlib/classes111.zip
 
Now Shutdown all the services of Application and database for Copy the file System to target location

Configuring the target system
Once it is done .

Regards,

Vijay.