How to Mount or unmount Filesystem on Unix server

1)Create the appropriate entry in /etc/fstab to ensure that the file system is mounted at boot time.: 


cat /etc/fstab

UUID=2eb83701-0f05-41bc-bb0d-755922dadf8d /                       ext4    defaults        1 1
UUID=d3fceab3-da77-4766-afd3-50374e8ab323 /boot                   ext4    defaults        1 2
UUID=47e9f773-c63a-4b29-94ea-4e83304eeeb7 swap                    swap    defaults        0 0
UUID=db3dce5c-21db-4027-9cf4-7305c4b19d4f  /oracle                ext4    defaults        1 0
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
//192.6.0.100/rmanbackup        /extdrive       cifs    username=Administrator,password=welcome123 ,uid=root,gid=root,_netdev,sec=ntlm,auto     0 0

Mounted file systems  information is in the proc file system 

cat /proc/mounts

2) Login as root user and issue below command to mount 

mount /dev/xvdf1 /test

mount shared file system :

sudo mount -t cifs -o username=Administrator //192.6.0.100/rmanbackup     /extdrive

3) Login as root user and issue below command to unmount 

sudo umount -l  /extdrive

How to Debug WORKFLOW Mailer failures in Oracle Apps (EBS) and How can I change Workflow Notification Mailer parameters without login to OAM?

WORKFLOW Mailer Debugging for Notification Failures
==============================================

First take a look at the current Workflow mailer log which is located in the below location:

cd $APPLCSF/$APPLLOG

ls -ltr FNDCPGSC*.txt

AFJVAPRG is a utility/command which will help you debug

Issue below command on Oracle Apps Tier  to check connectivity and routing of IMAP server like port connectivity and notification delivery processing status and path where its failing and why its failing

Note: Mention the correct port number used for IMAP/SMTP server and the IMAP/SMTP server names correctly in the below commands

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=ebusiness@gmail.com -Dpassword=welcome123 -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer


Issue below command on Oracle Apps Tier  to check connectivity and routing of SMTP server like port connectivity and notification delivery processing status and path where its failing and why its failing

$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=ebusiness@gmail.com -Dpassword=welcome123  -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer


================================================
Additionally :
How can I change Workflow Notification Mailer parameters without login to OAM? 

sqlplus apps/apps   @$FND_TOP/sql/wfmlpcln.sql
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


PL/SQL procedure successfully completed.

Commit complete.

cd $FND_TOP/sql
@afsvcpup.sql

Enter Component Id: 10006
Enter the Comp Param Id to update : 10093

Enter a value for the parameter  : xyz@abc.com

How Oracle Homes are unregistered from the global inventory

While  Cloning of Oracle Apps you might decide to initiate the cloning when errors are encountered
In such cases since the Oracle Inventory is already registered in the previous  attempt it errors out as it cannot create a new home which is already existent in the inventory and hence need to Unregister the Oracle Home and re-initiate the clone.

Below are the steps for the same


"Ensure that the above Oracle Homes are unregistered from the global inventory "/oracle/TEST/oraInventory/ContentsXML/inventory.xml" and re-run adcfgclone.pl script"

Below Oracle Homes are already registered in the global inventory:
/oracle/TEST/apps/fs2/FMW_Home/Oracle_EBS-app1
/oracle/TEST/apps/fs2/FMW_Home/webtier
/oracle/TEST/apps/fs2/FMW_Home/oracle_common

First go to below location to initiate the oui : 

cd /oracle/TEST/apps/fs2/EBSapps/10.1.2/oui/bin


./runInstaller -silent -deinstall REMOVE_HOMES={"/oracle/DOSC/apps/fs2/FMW_Home/Oracle_EBS-app1"}
./runInstaller -silent -deinstall REMOVE_HOMES={"/oracle/DOSC/apps/fs2/FMW_Home/webtier"}
./runInstaller -silent -deinstall REMOVE_HOMES={"/oracle/DOSC/apps/fs2/FMW_Home/oracle_common"}

How To Configure Email Delivery Option in Oracle Apps R12.2.4 (EBS)

In Oracle EBS Home Page Click on Setting ---> Preferences 

In the below section of manage preferences

Enter the smtp email address of your Instance (your workflow email address )  and its password  and select the email text style to your preference as in below screenshot and click on apply button 

Go to the SRS window and select active users concurrent program

Then Click on the Delivery option of the above window and goto email tab as shown below
Enter the recipients email address 

As can be seen in the request log email has been delivered to intended user .
This option can be used for scheduled requests for sending alerts or notifications for some jobs you might want to be notified of :

How to create ODBC for MS Excel Pivot reports to extract or pull data from Oracle Database

1)Pre-requisite for setting up this interface is the Installation of Oracle Client software in the machine/PC where this Interface/report will be used.

2)Creation of ODBC Connectivity navigation as given below in the screen shot:
Start by clicking on Control panel and follow below steps

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

Next Click on the above ADD button (you will be prompted for below values)

3)Below configuration Parameters need to given

4)Test the newly created ODBC (POSC_EXCEL) connectivity by clicking on TEST connection

Enter the password of the Oracle database user in the above screen which will confirm the successfull conectivity of ODBC to Oracle database (ODBC which will be used by MS excel to pull data from Oracle ) 

5)So far the ODBC has been created in the machine at the Windows level now we need to call the same ODBC from the MS EXCEL Sheet where we would be generation the PIVOT Report along with the query embedded with it . Below are the Navigation for the same 

First click on from Other sources 

and then 

Click on Data Connection Wizard option 

Next Screen will appear to choose the ODBC which we have created earlier 

Choose  ODBC DSN in the above screen and click next

Screen with list ODBC connection will appear choose the ODBC you would like to connect and provide the password (password for Oracle User for which OSBC was configured) 

Here you will get to choose the Oracle view name for the query developed 

Here this query will along with the connection will save a local copy for this file so you dont need to repeat the process but just click on refresh whenever you open the file to get the latest data . 

Finally Data Import will happen in the below screen :
================================

How to add Add Unix firewall exceptions for Oracle Apps or EBS URL

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

iptables -I INPUT -p tcp --dport 8020 --syn -j ACCEPT     ( This is stored in  /etc/iptables.conf)

service iptables save

iptables-save > /etc/iptables.conf

Add the following command in /etc/rc.local to reload the rules in every reboot.
iptables-restore < /etc/iptables.conf

[root@oracletest ~]# chkconfig --list telnet
telnet          on

[root@oracletest ~]# service xinetd restart
Stopping xinetd:                                           [  OK  ]
Starting xinetd:                                             [  OK  ]

[root@oracletest ~]# service network  restart


Curl command from unix server can tell whether EBS/Oracle Apps Url is accessible or not
(without going to browser to check )

==============================================================
 cat  /etc/rc.local

#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

iptables-restore < /etc/iptables.conf

Script to Disable Multiple EBS Users from backend (Oracle Apps )

Script to Disable Multiple Oracle Apps Users from backend  mostly in EBS Clone environments

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

DECLARE

  -- get user list which should be disabled

  cursor c1 is

  select user_name

     from fnd_user

    where (end_date is null or end_date > sysdate) 

      and user_name not in ('SYSADMIN', 'VJAY', 'KIRAN','ARUN','SACHIN'); 

BEGIN

  for c in c1 loop

    -- disable user

    fnd_user_pkg.disableuser(c.user_name);

  end loop;

  --commit changes

  commit;

END;

Create or Assign New Temporary tablespace in Oracle Database

1.Create Temporary Tablespace TempNew

2.Make the default tablespace to TemNew

3.Drop temp tablespace TEMP

First Query To Check current temporary tablespace 

SELECT * FROM database_properties WHERE property_name like '%TABLESPACE'

1) Create another Temporary Tablespace TEMP2

 -CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/ebs/TOSC/db/data/temp2_new.dbf' SIZE 3g TABLESPACE GROUP TEMP;

2) Make Default Database temp tablespace  TEMP2

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

3) drop tablespace temp1 INCLUDING CONTENTS AND DATAFILES;

How to Cancel Concurrent Requests from Backend Oracle Apps ( EBS )

select status_code,phase_code from fnd_concurrent_requests where  request_id=&1;

select fcr.CONCURRENT_PROGRAM_ID,fcp.CONCURRENT_PROGRAM_NAME from
fnd_concurrent_requests fcr , fnd_concurrent_programs fcp
where
1=1
and fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID
and fcp.CONCURRENT_PROGRAM_NAME like '%OSC%AR%'

select CONCURRENT_PROGRAM_NAME from fnd_concurrent_programs  where CONCURRENT_PROGRAM_NAME like '%OSC%AR%H%'
select * from fnd_concurrent_programs_tl where USER_CONCURRENT_PROGRAM_NAME like '%OSC%Dynomax%On%'

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=90353

select request_id,phase_code , status_code from fnd_concurrent_requests
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID=90353;

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER in Oracle Apps (EBS)

HOW TO SET THE OVERRIDE ADDRESS in WORKFLOW MAILER
------------------------------------------------------------------------------------------------
How can I change Workflow Notification Mailer parameters without login to OAM?
Solution
You can use the following script to change any Workflow Notification Mailer parameter or even any workflow agent listener parameter without login to the application

Do the following

1.Run the following script $FND_TOP/sql/afsvcpup.sql from sqlplus as apps user
2.For prompt Enter Component Id: enter id corresponds to Workflow Notification Mailer
3.For prompt Enter the Comp Param Id to update enter id corresponds to parameter you want to change
4.For prompt Enter a value for the parameter enter your parameter value

Provide steps to allow the Workflow Mailer to Override an Address.

To resolve the issue test the following steps in a development instance and then migrate accordingly:
1. Use the script $FND_TOP/sql/afsvcpup.sql.

This will enable to change any and all or your Mailer components.
A. Run the script
B. It will show you the components - pick the comp id for the Workflow Mailer - default is 10006
C. then it will show you the parameters and their values for the mailer
D. Pick the comp param id for the Override address (it will say Test Address ) - default is 10093

2. The script will display the following:
You have selected parameter : Test Address
Current value of parameter : NONE

Enter a value for the parameter : <Here you will put the email address you want >
3. Retest the Workflow mailer and confirm the override now works as expected.
--Check the workflow services using the below query.


select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

--
To Check What is the Current Email Address Set

SQL> select fscpv.parameter_value
    from fnd_svc_comp_params_tl fscpt
    ,fnd_svc_comp_param_vals fscpv
    where fscpt.display_name = 'Test Address'
    and fscpt.parameter_id = fscpv.parameter_id;  2    3    4    5

PARAMETER_VALUE
--------------------------------------------------------------------------------
xyz.abc@domain.com

Option 2 or  Second approach  to update over ride address
======================
Also one alternate is there to set it from back-end using the below query
------------------------------------------------------------------------------------------
update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address');

How to create a trigger to send Email notifications in Oracle Database

 CREATE OR REPLACE TRIGGER APPS.xx_send_mail_trigger

AFTER insert

ON dept

REFERENCING NEW AS NEW OLD AS OLD

   FOR EACH ROW

  6  DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

tmpVar NUMBER;

   v_sqlerrm varchar2(500);



      var1 varchar2(250);

      var2 varchar2(250);

   BEGIN

      tmpVar := 0;

      v_sqlerrm :=NULL;

   UTL_MAIL.send(sender     =>  'xyz@gmail.com',

               recipients =>  'xyz@gmail.com',

                   cc         =>  'xyz@gmail.com',

                   bcc        =>  'xyz@gmail.com',

               subject    => 'UTL_MAIL Test',

                   message    => 'If you get this message it worked!');

       COMMIT;



   END xx_send_mail_trigger;

   /



Trigger created.



CREATE or REPLACE TRIGGER TR_TEST

AFTER INSERT ON dept_test

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

tmpVar NUMBER;

a NUMBER;

v_sqlerrm varchar2(500);



   var1 varchar2(250);

   var2 varchar2(250);

BEGIN

   tmpVar := 0;

   v_sqlerrm :=NULL;

SELECT deptno INTO a FROM dept_test WHERE deptno=:NEW.deptno;

DBMS_OUTPUT.PUT_LINE('This is to inform that following deliveries has been shipped in Oracle' || ',' || a);



UTL_MAIL.send(sender     =>  'xyz@gmail.com',

                recipients =>  'xyz@gmail.com',

                cc         =>  'xyz@gmail.com',

                bcc        =>  'xyz@gmail.com',

                subject    => 'This is to inform that following deliveries has been shipped in Oracle',

                message    => 'This is to inform that following deliveries has been shipped in Oracle');

    COMMIT;

END TR_TEST;

/


Trigger created.