How to purge e-mail notifications from the Workflow queue

Please Bring down the WF mailer and take proper backup before performing the below steps.

1) You need to update the notifications you do not want to sent, in the WF_NOTIFICATIONS table.
SQL> select notification_id,status,mail_status,begin_date from WF_NOTIFICATIONS where status = 'OPEN' and mail_status = 'MAIL';

2) To update a notification so that it will not get e-mailed. Set the MAIL_STATUS = 'SENT'. The mailer will think the e-mail has already been sent and it will not send it again.
SQL> update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

3) Then run the script wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer.
SQL>sqlplus apps/apps_pwd @$FND_TOP/patch/115/sql/wfntfqup APPS APPS_PWD APPLSYS

How to enable Automatic Memory Management (AMM) in 11g

check the existing value

SQL> SHOW PARAMETER target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     300
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     128
pga_aggregate_target                 big integer 6700M
sga_target                           big integer 10048M

Run the following statement

 SQL> ALTER SYSTEM SET memory_max_target=15G SCOPE=SPFILE;
 SQL> ALTER SYSTEM SET memory_target=15G SCOPE=SPFILE;
 SQL> ALTER SYSTEM SET sga_target=0 SCOPE=SPFILE;
 SQL> ALTER SYSTEM SET pga_aggregate_target=0 SCOPE=SPFILE;

Shutdown and startup

SQL> shutdown immediate;
SQL> startup

To delete oracle trace files older than 30 days

Go to the trace file location and execute the following command.

cd $ORACLE_HOME/admin/diag/rdbms//trace

find . -name "*.trc" -mtime +30 -exec rm {} \;

How to rename or move a datafile

1)shutdown immediate

2)SQL> startup mount  (bring the database in mount stage)

2a)move or rename the file to desired location/name at os level.
Example: mv PATH1/data01.dbf PATH2/data01.dbf #(for move)
         or
         mv PATH1/data01.dbf PATH1/user01.dbf #(for rename)

3)SQL> alter database rename file 'PATH1/data01.dbf' to 'PATH2/data01.dbf';
  or
  SQL> alter database rename file 'PATH1/data01.dbf' to 'PATH1/user01.dbf';

4)SQL> aler database open;

To check whether your database startup with the pfile or spfile

Connect to SQLPLUS as system or sysdba

run show parameter pfile command as below. If it returns a value like below then the database is started with spfile.

SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/product/11.2.0/dbs/spfileRAPIDS.ora
If the output is blank like below, then the database is started with pfile.

SQL> show parameter pfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

How to find services that are enabled in EBS R12

you can find out the list of services enabled in R12 application server from the context file.

Steps are given below.

Login to application server and set the environment variables.
run the following command.

java oracle.apps.ad.autoconfig.ServiceControl -e $CONTEXT_FILE -m STATUS -u apps/apps

The logfile for this session is located at $INST_TOP/logs/03031817.log
 The service group Root Service is enabled
 The service group Root Service is enabled
 The service group Web Entry Point Services is enabled
 The service group Root Service is enabled
 The service group Web Application Services is enabled
 The service group Batch Processing Services is enabled

Clearing Cache in R12

For clearing EBS middle tier cache :
Login to front end application.
Navigate to Functional Administrator responsibility
Click on (Core Services) Tab
Click on Caching Framework
Select 'Global Configuration' on left side menu
Clear All Cache
Finally Bounce the apache and oacore services.