Monday, November 10, 2014

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
$./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;


Monday, April 21, 2014

how to fix 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: 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/ /usr/lib/

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
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
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;

Wednesday, March 19, 2014

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

Monday, March 3, 2014

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

check the existing value


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 pga_aggregate_target=0 SCOPE=SPFILE;

Shutdown and startup

SQL> shutdown immediate;
SQL> startup