How To Purge E-Mail workflow notifications From The EBS workflow queue.

Sometimes we need to purge the huge notification queue in ebs workflow, because of mailer services got failed and giving an error while starting or while refreshing/cloning new instance. 

As we know that old email notification not needed in cloned instance. In this scenario we have to purge all those workflow notification queue from cloned ebs instance.

Below script will verify the current status in table WF_NOTIFICATIONS that has to be sent when mailer services gets started.

SQL>select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status from wf_notifications Where Status In ('OPEN', 'CANCELED') And Mail_Status In ('MAIL', 'INVALID') --and begin_date < sysdate-30  --> List only emails older than 30 days ago order by notification_id;

Output:-

NOTIFICATION_ID BEGIN_DATE RECIPIENT_ROLE MESSAGE_TYPE

 

MESSAGE_NAME STATUS MAIL_STATUS

6273813 4/26/2016 4:01:17 PM SYSADMIN OMERROR OMERROR_MSG 

OPEN MAIL

6273814 4/26/2016 4:01:18 PM SYSADMIN OMERROR OMERROR_MSG 

OPEN MAIL

6273815 4/26/2016 4:01:18 PM SYSADMIN OMERROR OMERROR_MSG 

OPEN MAIL

6273816 4/26/2016 4:01:19 PM SYSADMIN OMERROR OMERROR_MSG 

OPEN MAIL

Update the mail_status to sent , if you have not requirement for old workflow email notification. It will not en-queue the messages again.

SQL>update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status in ('MAIL','INVALID') and Status In ('OPEN', 'CANCELED');

Purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table.

SQL> @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.01

Commit complete.

Elapsed: 00:00:00.00

**** TEMPORARY TABLES / AQs created ****

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.43

**** Messages backed up ****

Commit complete.

Elapsed: 00:00:00.00

**** wfaqback.sql completed,  executing SQLs from wfntfqup

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.37

***  invoking wfaqrenq.sql ******

*** Re-enqueing messages

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26

Commit complete.

Elapsed: 00:00:00.00

***** Objects related to wf_queue_temp_evt_table and 

wf_queue_temp_jms_table are dropped *****

***** Re-enqueue OF Alerts completed *****

Disconnected from Oracle Database 11g Enterprise Edition Release 

11.2.0.4.0 - 64 bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

[applmgr@prdsrv scripts]$

After that need to be start the workflow mailer services.

How to check database is running with spfile or pfile.

In oracle database there is couple of way to find out whether database is running with spfile or pfile.

       

Parameter File

1.     SQL>show parameter spfile;

After executing above returns blank then database was started using pfile.

2.     SQL>select name,value from v$parameter where     name=’spfile’;

If database running with spfile , it will return the result.

If database running by pfile , we can create the spfile from pfile.

Enable auto extend the datafile size in oracle database.

Before enabling the auto extend datafile size in oracle database. Make sure free space available on disk. Some time we need to enable auto increment for datafile to extend its size while data growing up frequently.


First of all we have to check whether auto extend enable or not on particular datafile.


SQL>select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,status,increment_by from dba_data_files where tablespace_name='APPS_TS_TX_IDX' order by tablespace_name;


Using below command, we can enable autoextend datafile by 200m upto 30g.


SQL>alter database datafile '/data03/db/apps_st/data/a_txn_ind23.dbf' autoextend on  next 200m maxsize 30g;

Database altered.

Difference between physical and logical backup in oracle

Physical backup are backup of the physical files such as datafiles, control files, and archived redo logs. Ultimately, every physical backup is a copy of files storing database information to some other location, whether on disk or some offline storage such as tape.

Physical backup can be done via rman utility as below.

RMAN> backup database plus archivelog all;

Logical backup are backup of logical data (for example, tables,index,schema,stored procedures) exported from a database with an Oracle export utility and stored in a binary file, for later re-importing into a database using the corresponding Oracle import utility.

Export:-

$exp USERID=scott/tiger FULL=y FILE=myfull.dmp

$exp USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp



Import:-

$imp USERID=scott/tiger FULL=y FILE=myfull.dmp