Queries to check Failed and Open status Workflow notifications in Oracle EBS

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER ,begin_date,end_date from wf_notifications 
where MAIL_STATUS!='FAILED' and end_date is not null 
order by end_date desc

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER ,begin_date,end_date from wf_notifications 
where MAIL_STATUS='FAILED' and end_date is not null 
order by end_date desc



SELECT COUNT(*), message_name,begin_date,status FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name,begin_date,status ORDER BY begin_date DESC

SELECT * FROM wf_notifications
WHERE STATUS='OPEN'

How to search for ldap user (OID User)

ldapsearch -v -h "Servername01" -p 3060  -D "cn=orcladmin" -w "Passwd" -b "" -s sub "uid=RAJESH@xyz.COM" uid orclguid orclactivestartdate orclactiveenddate orclisenabled

Note: Servername is the OID Servername in above command.

Command to Sync OID Users(LDAP) with Oracle EBS application and Vice versa

syncProfileBootstrap -host servername -port 7005 -D weblogic -profile AD_SYNC_ERP -lp 5

Note: Servername is the OID Servername in above command.

AD_SYNCERP is the OAM profile to sync users between OID and application.

How to modify OID (Ldap) User attributes

ldapmodify -p 3060 -h "servername01" -D cn=orcladmin -w "Passwd" -v -f RAJESH.ldif

Note: Servername is the OID Servername in above command.

Below are the contents of RAJESH.ldf file (RAJESH being modified ro RAJESH1)

cat RAJESH.ldif
dn: cn=RAJESH@XYZ.COM,ou=ebiz,cn=users,dc=eaa,dc=ae
changetype: moddn
newrdn: cn=RAJESH1
deleteoldrdn: 1

How to rebuild Work Flow Queue in Oracle EBS

Work Flow  Queue Rebuild Activity
=========================
1)Stop the queue as below :
rem stop queue
exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

2)Drop the queue as below :
rem drop queue
exec dbms_aqadm.drop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

3)Drop the queue table as below :
rem drop queue table
exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true);

4)Create the queue table  as below :
rem create queue table
begin
dbms_aqadm.create_queue_table
(
queue_table => 'WF_NOTIFICATION_OUT',
queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
sort_list => 'PRIORITY,ENQ_TIME',
multiple_consumers => TRUE,
comment => 'Workflow JMS Topic',
compatible => '8.1'
);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr2= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/

5)Create the queue as below :

rem create queue
begin
dbms_aqadm.create_queue
(
queue_name => 'WF_NOTIFICATION_OUT',
queue_table => 'WF_NOTIFICATION_OUT',
max_retries => 5,
retry_delay => 3600,
retention_time => 86400,
comment => 'Workflow JMS Topics'
);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr4= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/

6)Start the queue as below :
rem start queue
begin
dbms_aqadm.start_queue(queue_name => 'WF_NOTIFICATION_OUT');
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr5= '||to_char(sqlcode)||' - '||sqlerrm);
end;
/
rem start exception queue
begin
dbms_aqadm.start_queue(queue_name => 'AQ$_WF_NOTIFICATION_OUT_E',enqueue => FALSE);
exception
when others then
raise_application_error(-20000, 'Oracle Error Mkr9= '
||to_char(sqlcode)||' - '||sqlerrm);
end;
/

7)Add subscriber as below :

rem add subscriber
declare
lagent sys.aq$_agent;
subscriber_exist exception;
pragma EXCEPTION_INIT(subscriber_exist, -24034);
begin
lagent := sys.aq$_agent('WF_NOTIFICATION_OUT',null,0);
dbms_aqadm.add_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',subscriber=>lagent, rule=>'1=1');
exception
when subscriber_exist then
dbms_aqadm.alter_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',subscriber=>lagent,rule=>'1=1');
null; -- ignore if we already added this subscriber.
end;
/
commit;

Query to check or list out for Purge programs in Oracle EBS

SELECT REQUEST_ID, SUBSTR(USER_CONCURRENT_PROGRAM_NAME,1,80) "PROGRAM NAME", REQUEST_DATE
FROM APPS.FND_CONC_REQ_SUMMARY_V
WHERE USER_CONCURRENT_PROGRAM_NAME LIKE '%urge%' OR USER_CONCURRENT_PROGRAM_NAME LIKE '%elete%'
ORDER BY REQUEST_DATE,"PROGRAM NAME";



SELECT SUBSTR(B.APPLICATION_SHORT_NAME,1,11) "APPLICATION",
       SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,50) "CONCURRENT PROGRAM NAME",
       SUBSTR(A.CONCURRENT_PROGRAM_NAME,1,35) "SHORT NAME"
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPS.FND_APPLICATION_VL B
  WHERE A.APPLICATION_ID=B.APPLICATION_ID
  AND A.ENABLED_FLAG='Y'
  AND (A.USER_CONCURRENT_PROGRAM_NAME LIKE '%urge%'
  OR A.USER_CONCURRENT_PROGRAM_NAME LIKE '%elete%')
ORDER BY B.APPLICATION_SHORT_NAME;

Oracle EBS R12.2 Cloning Steps

DB Tier

Cloning Steps on the Target node after  running adpreclone.pl on source node (db Tier & AppsTier)

adpreclone.pl dbTier (on Db node)

adpreclone.pl appsTier (on Apps Node)

scp -r erpdbtuat@server01:/erpbintuat/erptuat/ /u01

or

scp -r erpdbtuat@DBserver01:/erpbintuat/erptuat/prod_apps.tar.gz  /u01

(Note : rename the base directory from erpuat to erptest i.e to instance name you are copying)

cd $ORACLE_HOME/appsutil/clone/bin

perl adcfgclone.pl dbTechStack

sqlplus "/as sysdba"

startup nomount pfile='$ORACLE_HOME/dbs/initERPCLONE.ora'

copy the the rman backups to the destination filesystem (if its in accessible shared location then no need to copy)

rman auxiliary /
run {
allocate AUXILIARY channel c0 type disk;
allocate AUXILIARY channel c1 type disk;
allocate AUXILIARY channel c2 type disk;
allocate AUXILIARY channel c3 type disk;
allocate AUXILIARY channel c4 type disk;
allocate AUXILIARY channel c5 type disk;
allocate AUXILIARY channel c6 type disk;
allocate AUXILIARY channel c7 type disk;
duplicate target database to "ERPCLONE" BACKUP LOCATION '/stage/backup/' NOFILENAMECHECK;
RELEASE CHANNEL c0;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
RELEASE CHANNEL c5;
RELEASE CHANNEL c6;
RELEASE CHANNEL c7;
}

Take care of post db activities like password resets ,re-pointing db links and purging tables can be done now else these can also be done after Apps Tier Cloning Completes

Run Autoconfig $ORACLE_HOME/appsutil/scripts/instance_name/adautocfg.sh


=======================================================================
Apps Tier

adpreclone.pl appsTier

 (remove the Apps Tier Dir Structure under  /u01)

1) Copy the Apps Tar file from PROD to CLONE
rm  /u01/applprod/prod_apps_tar/prod_apps.tar.gz
scp applprod@appsserver01: /u01/backup/prod_apps.tar.gz  /u01/

scp -r applprod@appsserver01:/erpbintuat/erptuat/ /u01

2) Delete the Apps Directories

cd /u01/ERPCLONE/fs1

(Remove the below directories that will be replaced by the source copy)

rm -rf EBSapps FMW_Home_* inst_*
mv FMW_Home FMW_Home_orig
mv inst inst_orig

3) Untar the Apps tar file
cd /u01/ERPCLONE/fs1

 tar -zxvf /prod_apps.tar.gz .
(do not miss the dot at the end )

4) Run adcfgclone

cd /u01/CLONE/backup

cp ERPCLONE_server01.xml_bkup ERPCLONE_server01.xml

Here you have two ways/options of cloning

1) perl adcfgclone.pl appsTier 

 (this will only create/clone the run filesystem in case you dont want to maintain patch fs in test environment)

2)adcfgclone.pl  appsTier dualfs

(this will create both Run fs and Patch fs )

perl '/u01/ERPCLONE/fs1/EBSapps/comn/clone/bin/adcfgclone.pl' appsTier /u01/backup/ERPCLONE_server01.xml

Enter the APPS user password:
Enter the WebLogic AdminServer password:

5) After adcfgclone , enable SSL

Modify the Oracle HTTP Server wallet

cd  /u01/ERPCLONE/fs1/FMW_Home/webtier/instances/EBS_web_ERPCLONE_OHS1/config/OHS/EBS_web_ERPCLONE/keystores/default/

mv cwallet.sso cwallet.sso_bkp
cp/u01/ERPCLONE/fs_ne/inst/ERPCLONE_server01/certs/Apache/cwallet.sso .

6) Update the Context File and Configuration Files

7)Change the permission of “.apachectl” file using sudo privilege on both the Run and Patch File Systems as the root user.

sudo chown root $FMW_HOME/webtier/ohs/bin/.apachectl
sudo chmod 6750 $FMW_HOME/webtier/ohs/bin/.apachectl


8) Take backup of ssl.conf
/u01/ERPCLONE/fs1/FMW_Home/webtier/instances/EBS_web_ERPCLONE_OHS1/config/OHS/EBS_web_ERPCLONE/ssl.conf and update the below parameter values

•         Start the node manager and admin server on the run file system after sourcing the Run environment.
•         Execute below command on all application tier nodes to propagate the changes made in ssl/httpd configuration to the context file variables:
perl $AD_TOP/bin/adSyncContext.pl contextfile=$CONTEXT_FILE
Enter the APPS user password:
Enter the WebLogic AdminServer password:

•  Stop the node manager and admin server

Edit run file system Context file and update the values to reflect as below

Variable Expected Value
s_url_protocol https
s_local_url_protocol https
s_webentryurlprotocol https
s_active_webport 443
s_webssl_port 443
s_https_listen_parameter 443
s_login_page https://server01.com:443/OA_HTML/AppsLogin

s_external_url https://server01.com:443

s_webentryhost   server01
s_webentrydomain   .com


Run AutoConfig using the adautocfg.sh script in the application tier $ADMIN_SCRIPTS_HOME directory


====================================================
Known Issues

1)  During EBS Cloning Instance is already registered in inventory so de-register it as below :

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

cd /ptchapps/ERPCLONE/fs1/EBSapps/10.1.2/oui/bin

./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/ERPCLONE/fs1/FMW_Home/Oracle_EBS-app1"}

./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/ERPCLONE/fs1/FMW_Home/webtier"}

./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/ERPCLONE/fs1/FMW_Home/oracle_common"}

./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/ERPCLONE/fs1/FMW_Home/Oracle_OAMWebGate1"}

then Re-run adcfgclone.pl

2) EBS Login Page is not loading


perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2
logfile set: /u01/ERPCLONE/fs1/inst/apps/ERPCLONE_server01/logs/appl/rgf/ojsp/ojspc_error.log
starting...(compiling all)
using 10i internal ojsp ver: 10.3.6.0
synchronizing dependency file:
  loading deplist...7831
  enumerating jsps...7836
  updating dependency...5153
  parsing jsp...5153
  writing deplist...7831
initializing compilation:
  eliminating children...5963 (-1873)
translating and compiling:
  translating jsps...5963/5963 in 17m17s
  compiling jsps...5963/5963 in 15m10s
Finished!