Useful apps DBA scripts

• Who is connected, when

sql> select user_name,to_char(last_logon_date,’DD-MON-YYYY HH24:MI:SS’)
from apps.fnd_user
where to_char(last_logon_date,’DD-MON-YYYY’)=to_char(sysdate,’DD-MON-YYYY’);

• Which resposibility is used

sql> select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility from fnd_user fu,
fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag=’N’ AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;

• Number of user connected to EBS

sql> SELECT COUNT (DISTINCT d.user_name)
FROM apps.fnd_logins a, v$session b, v$process c, apps.fnd_user d
WHERE     b.paddr = c.addr
AND a.pid = c.pid
AND a.spid = b.process
AND d.user_id = a.user_id
AND (d.user_name = ‘USER_NAME’ OR 1 = 1)

• EBS gather stats

sql> execute fnd_stats.gather_table_stats(‘AR’,'RA_CUSTOMER_TRX_ALL’);

• CUSTOM.pll compile: (copy CUSTOM pll under $AU_TOP/resource and compile)

f60gen CUSTOM.pll apps/apps_password module_type=LIBRARY compile_all=YES

• How to change apps password on 11i

• Shutdown all apps services on all nodes.

• Backup these tables                             

                         sql>    create table xxx_fnd_user as select * from FND_USER

                         sql>    create table xxx_FND_ORACLE_USERID asselect * from FND_ORACLE_USERID

• Connect as apps user on sqlplus and run:

                          $ FNDCPASS apps/apps_old 0 Y system/password SYSTEM APPLSYS apps_new

• Edit passwords in these files on all nodes:

                         $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app

                        $ORACLE_HOME/reports60/server/CGIcmd.dat

• How to change module passwords on 11i

$ FNDCPASS apps/apps_password 0 Y system/system_password ORACLE AR new_password

Useful Unix-Linux commands for apps DBA’s

• Search a word on a text(s)

find . -type f -name “*.txt” -exec grep XXWW {} \; -print

• Number of files in a directory:

ls -l | wc -l

• Kill all processes of a user (be careful !)

kill `ps -fu username | awk ‘NR != 1 {print $2}’`

• Find a file under current directory and subdirectories

find . -name “*.trc”

• Find files older than XX days (60 days)

find . -name “*.trc” -mtime +60

• Find files older than XX days and move them to another directory.

find . -name “*.trc” -mtime +60 -exec mv {} /data1/test \

• Find files older than XX days and delete them all (be careful)

find . -name “*.trc” -mtime +60 -exec rm {} \;

• Runing Apache services

ps -ef|grep apache

• Running Concurrent processes

ps -ef|grep FNDLIBR*|grep user

• Running forms services

ps -ef|grep f60*|grep user