Sunday, January 31, 2016

How to Check Patch Applied Status in Oracle Applications?


From Oracle Applications Release 11i to 12.1.x, Patch application Status can be queried from ad_bugs or ad_applied_patches tables

Query to check whether patch is applied on a instance.

SQL>select bug_number,creation_date,last_update_date from apps.ad_bugs where bug_number='&patchnum';

SQL> select PATCH_NAME,CREATION_DATE from apps.ad_applied_patches where PATCH_NAME='&patchnum';

What is difference between above two Queries?

Suppose, we applied a patch which has multiple bugfixes.The details of all bugs fixed by the patch are listed in ad_bugs table.
In Contrast,ad_applied_patches table give info only about the patches which are applied through adpatch utility.


From Release 12.2, Online Patching utility(adop) is used for Patching application Filesystem.

Querying the data from ad_bugs or ad_applied_patches tables may not give correct information.Because, online patching can be aborted anytime prior to Cutover Phase.
ad_bugs or ad_applied_patches tables may contain entries for patches which are started through adop and later aborted.

The below query gives accurate Information about Patch Applied Status in R12.2

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') from dual;

example sql:
SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (22498647);

or for single app tier installations:
select ad_patch.is_patch_applied('R12',-1,22498647) from dual;

Sample Output:

EXPLICIT = applied
NOT APPLIED = not applied / aborted


Login to Database node as the database os user

Set environment using commands below

$export ORACLE_HOME=/u01/app/oracle/product/112

$export PATH=$PATH:$ORACLE_HOME/Opatch

$which opatch
==>It should give output like /u01/app/oracle/product/112/Opatch

$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

== This gives list of all patches applied to the ORACLE_HOME

$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc|grep 988765

==> To check if a particular patch is applied to the ORACLE_HOME

R12-Password Change Utility

Oracle has introduced a new utility to change the Oracle E-Business Suite passwords in R12.1.2.AFPASSWD is an enhanced version of FNDCPASS utility.
Differances between AFPASSWD and FNDCPASS

1) AFPASSWD only prompts for passwords required for the current operation. allowing separation of duties between applications administrators and database administrators.
Changing sysadmin Password using AFPASSWD prompts only for APPS Password. Changing APPLSYS password prompts for APPS and SYSTEM password.
Whereas, FNDCPASS requires SYSTEM and APPS password to change password for application and database users.

2) AFPASSWD can be run from the database tier as well as the application tier. Whereas, FNDCPASS can be run only on Concurrent Manager Node

3) When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

Changing SYSADMIN Password  

Output:Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.  Working…
Enter new password for user [SYSADMIN]:
Verify new password for user [SYSADMIN]:
Password is changed successfully for user SYSADMIN.
Password is changed successfully for user SYSADMIN.
AFPASSWD completed successfully.

Changing APPLSYS Password

Syntax: $AFPASSWD -c apps@PROD -s APPLSYS
Output: Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.  Working…
Enter new password for user:
Verify new password for user:
AFPASSWD completed successfully.


AFPASSWD [-c [@]] -f
AFPASSWD [-c [@]] -o
AFPASSWD [-c [@]] -a
AFPASSWD [-c [@]] -s

-c [@]
Specify the connection string to be use, parameters

Application-manager user and/or TWO_TASK value,
this option can be use in combination with the others.
(If not provided, AFPASSWD will try to default the values
from the environment)
is the APPS schema owner.

Change the password for an Application user.

Change the password for an Oracle Applications Database user.

-a Modify ALLORACLE users.

Lock|Unlock account for a single Oracle Applications database user.

Lock|Unlock accounts for non-essential Oracle Applications database users

Modify APPLSYS user. This requires the execution
of autoconfig (in ALL-TIERS) to distribute the
changes on your instance ie DB-TIER and ADMIN-TIER.

Display this help.

- AFPASSWD will prompt for the required passwords.
- AFPASSWD will prompt for new passwords twice for confirmation.
- Data entered for which contains spaces must be enclosed
in double quotes. For example, AFPASSWD -f "SCOTT TIGER"
- The only option permitted in combination with other options is the -c option.
Otherwise, there should only be one option per command (see usage above).
If additional options are provided on the command line, only the first valid
option after -c (if -c is present) will be executed.

Script to Check History of a concurrent Program

Sometimes, While troubleshooting Performance issues related to Concurrent Requests, we need to know the duration for request completion in the past. The script below gives the start time,end time and arguments for a given Concurrent Program


set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99

select request_id,
       to_char(actual_start_date,'DD/MON HH24:MI:SS') START_TIME,
       (actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
and user_concurrent_program_name like '%&%'
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id
and actual_start_date >(sysdate-1)
order by actual_start_date desc,ACTUAL_COMPLETION_DATE desc;


Query to Find Concurrent Requests Run on a specific Date

The Script below gives the details of all Concurrent Programs submitted on a given date

f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
to_char(f.actual_start_date,'DD-MON-YY HH24:MI:SS') start_on,
to_char(f.actual_completion_date,'DD-MON-YY HH24:MI:SS') end_on,
|| ' HOURS ' ||
*24*60*60) -
|| ' MINUTES ' ||
*24*60*60) -
*24*60*60)/3600)*3600 -
*24*60*60) -
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
from  apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date like '24-JUN-15%'
order by
f.actual_start_date desc;

Friday, January 29, 2016

Useful Linux Commands for Apps DBA's

Check Used Space on  a Disk/ Mount Point
The du command summarizes your disk usage. The sort command sorts the files by size. The command shown below sorts from smallest to largest all files in the current directory and all directories contained below the starting directory. Because the sort starts with the smallest file, the end of the list shows the largest files.

Deleting a large file is usually more efficient than deleting many small files. Often the largest files are core files or cache files, both of which you can delete.

du -h --max-depth=1
du -a | sort -k 1n,1|tail -30
du -sk * | sort -n | tail

Files more than 5mb

find . -mount -size +50000000c -print | ls -lh
find . -size +100000000c -print
find . -size +50000000c -print | xargs ls -l
find . -size +10000000c -print
find . -size +10000 -print

Files more than 30 day old.

find . -name "*.aud" -mtime +7

find . -name *.req -mtime +30|xargs gzip

find . -name "TEST01_*.out" -mtime +2 |xargs ls -l

find . -name "*.log" -mtime -1|xargs ls -l

Files modified in the last 6hrs ago
find . -name "*.log" -mmin 360|xargs ls -l
find . -mmin 360|xargs ls -l

find . -mtime +1|xargs ls -l

find . -size +30000000c -xdev -exec ls -l {} \;


Database Links in Oracle

A database link is a schema object in one database that enables you to access objects on another database.

SQL> desc dba_db_links
 Nombre                                                                                     ¨Nulo?   Tipo
 ------------------------------------------------------------------------------------------ -------- -------------------------------------------------------------
 OWNER                                                                                      NOT NULL VARCHAR2(30)
 DB_LINK                                                                                    NOT NULL VARCHAR2(128)
 USERNAME                                                                                            VARCHAR2(30)
 HOST                                                                                                VARCHAR2(2000)
 CREATED                                                                                    NOT NULL DATE

Use the below query to find the db links available for a database:

set lines 152
col owner for a15
col db_link for a30
col username for a15
col host for a30

select owner,db_link,username,host,CREATED
from dba_db_links;

spool db_link_test.sql

select 'select count(*) from tab@'||db_link||';'
from dba_db_links;

spool off

spool db_link_output.log


spool off


How to Test if a DB link is working fine?

select ' connect '||owner||'/'||owner||'1'||chr(10)||
'select count(*) from tab@'||db_link||';'
from dba_db_links;

select distinct owner
from dba_db_links;

Monday, January 18, 2016

R12.2 Filesystem Layout

  • File System 1 (FS1)--Stores a complete copy of all  Applications and Middle Tier code
  • File System 2 (FS2)--Stores a complete copy of all Applications and Middle Tier code 
  • File System Non-Editioned (FS_ NE)--Stores data that is written or read from the file system,Report Outputs,Log Files etc.
FS1 and FS2 Rotate Run and Patch Designation

How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.
This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.

1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';
2) select count(*) from apps.conc_req_on_hold
3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes


Sunday, January 17, 2016

ONE NODE RAC Features in Oracle Database 11g Edition

Available from 11gr2, Express Edition.
High availability feature.
Provides Cold failover Solution.
Automates instance relocation incase of instance failure or fault in the first node.
Facilitates rolling upgrade for single instance database.
Live migration of instances across servers.

Uses omotion utility to migrate the instance.

Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)