opatch command and options

opatch is a java based utility used for applying oracle database patches.


Note: Set the path to $ORACLE_HOME/OPatch before running opatch. Also view the Readme.txt file before applying any patch


To check version:
$./opatch version


For help:
$./opatch -help


To apply a single patch
cd
$./opatch apply


To rollback a patch
$./opatch rollback -id


To apply bundle patches
$./opatch util NApply


To query the inventory for patches applied
$./opatch lsinventory
for more details:
./opatch lsinventory -detail




opatch log files
Log file for OPatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch

How to find a product version in Oracle Applications

Run the below script to find out the product version


Login to the application server using applmgr account
Go to $AD_TOP/sql
Connect to sqlplus as apps/apps
 Run "@adutconf.sql"
 Output will be in same directory in the name of adutconf.lst

How to find an AD patchset level

Run the below script to find out the AD patchset level


SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;


PATCH_LEVEL
------------------------------
R12.AD.C.5

how to fix libdb.so.2: cannot open shared object file: No such file or directory error

you can see this error in http log file while starting the apache services.

libraries: libdb.so.2: cannot open shared object file: No such file or directory

To fix this issue implement the following solution

shutdown all oracle application services and as the root user create the following link.

ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2

Startup all oracle application services.

How to fix unable to extend temp error

Sometimes you get ORA-01652: unable to extend temp error. This may be due to small temporary tablespace. you can execute the following query to find out the space a session is using in the temporary tablespace.

set linesize 1000
set pagesize 1000
col USERNAME format a20
col SPID format a10
col MODULE format a30
col PROGRAM format a30
SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
,su.tablespace
FROM v$sort_usage su
,v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
GROUP BY
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;
you will find the temp tablespace consumption. You can either resize an existing datafile or add a new one.

To resize the temporary tablespace
SQL> alter database tempfile '/u01/oradata/SID/temp01.dbf' resize 2G;

To add a datafile to the temporary tablespace
SQL> alter tablespace temp add tempfile '/u01/oradata/SID/temp02.dbf' size 2G;

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.