Usefull Unix/Linux commnads

1) To find a "word" or pattern in all files in a directory & subdirectories
find . -name "*" -exec grep -l {} \; -print
Example: belowsearching for word
find . -name "*" -exec grep -l dba {} \; -print

2)To find a class inside a set of Jar files
for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done

3) To unzip all the patches in 1 directory in one go
for i in *.zip
do
unzip $i
done

4)To find the symbolic links that point to the old path in your oracle_home and appl_top.
This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.
ls -al `find . -type l` | grep $OLD_PATH

5)To find all the text files that contains the old path at the UNIX level.
find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH

6) To Sort files based on Size of file in a Directory
Useful in finding out spaces issues
ls -l | sort -nrk 5 | more

7)How to check if a Port is listening for any Service
netstat -an | grep $PORTNO

Oracle Application DBA Interview Questions - III

Oracle Application DBA Interview Questions - III

Q)What is your day to day activity as an Apps DBA?
Ans:
As an Apps DBA we monitor the system for different alerts (Entreprise Manager or third party tools used for configuring the Alerts) Tablespace Issues, CPU consuption
Database blocking sessions..etc
Regular maintanance activies like cloning,patching,custom code migrations (provided by developers)
Working with user isses.

Q)How often Do you patch?
Ans: Usually for non-production the patching request comes aroung weekly 4-6 and the same patches will be applied to Production in the outage or maintanance window.
Production has weekly maintance window (Eg Sat 6PM to 9PM) where all the changes (patches) will applied on production.

Q)How often Do you clone?
Ans: Cloning happens biweekly or monthly depending on the organization requierement.

Q)What change control/management or CCB?
Ans: Every organization has change control process, Change control process is no change goes into production witout proir testing on non-production instance.
Eg: If a user encouters an issue in production instance and the fix for the issue is known, Still the fix should not be applied directly on production, as it is not tested.
The same fix need to be first applied on a non-prod instance where the similar issue is happening and test the issue and instance stability.
Once user is happy with the results the change or fix will be implemented to production with the approval from Change control Board, CCB is a managemant team who reviews all the changes being deployed to production,
Depending on the need and criticality and testing results they approve the change movement to production instance.

Q)How much time does it take to upgrade, clone ?
Ans: Clone usually takes around 48hrs to copy and configure and upgrade depends on the database size and module involved.
upgrade from 11.5.9 to 11.5.10.2 will take around 3-4 days and 11i to R12 upgrade will take around 4-5 days.

Q)What is the meaning QA,CRP,SIT,DEV,UAT,PRE-PROD,PROD Instance?
QA - Tesing Instance
CRP - Conference Room Pilot
SIT - System Integration Testing
DEV - Developement
UAT- User Acceptence Testing
STAGE - Pre-production Instance
Prod - Production/actuall instance where the business is running

Q)What do we have in FND_NODES?Ans:
FND_NODES table contains  information about node_names and services enabled on a node.
In multinode instance if you want to know which node is running what services, You can query the fnd_nodes and get that information.

Q)when do we run FND_CONC_CLONE.SETUP_CLEAN ?
Ans:
FND_NODES table contains node information, If you have cloned test instance from production still the node information of production will be present after clone in the test instance.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table in the target to clear source node information as part of cloning.
Below syntax to execute:
SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

This will delete all the entries in the fnd_nodes table, to populate it with target system node information, Run autoconfig on DB node and Applications node.

Q)How verify the sysadmin password from command line?
Ans:
This utility can be used to verify the GUEST/ORACLE password
SQL>select fnd_web_sec.validate_login('SYSADMIN','<sysadmin_password>')from dual;
If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
N
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN')
--------------------------------------------------------------------------------
Y

Q)List out few Apps related tables ?
Ans:
CONCURRENT REQUEST/PROGRAM/MANAGERS
-------------------
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND/AOL Tables
--------------
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD/Patches
-------------
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Q) How To find the latest application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

Q) How to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

Q) How to know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

Q) Howto find the base application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'

Q) How To find all available application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES

Q) How To get file version of any application file which is changed through patch application
select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME

Q) How To get information related to how many time driver file is applied for bugs
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

Q) How To find latest patchset level for module installed
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

Q) How To find what is being done by the patch
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE

Q) How To find Merged patch Information from database in Oracle Applications
select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );
Q) How toto know, what all has been done during application of PATCH
Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE

Q) How to find out Patch level of mini Pack
Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';
Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order

Q)List out Profile Options Useful for Oracle Apps DBA?
Here is the list of few profile options which Apps DBA use frequently.
**It is not necessary that you as Apps DBA must know all profile options**
Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

To Find and Delete bigger size and older files in Linux

--To find out files size more than 5MB
find . -size +5000  -exec ls -ltr {} \; 
-- To **Remove** files size more than 5MB
find . -size +5000k  -exec rm -rf {} \;

--To find out files older than 30days
find . -mtime +30 -exec ls -ltr {} \;
--To find **Remove** files older than 30days
find . -mtime +30  -exec rm -rf {} \; &

CONCURRENT REQUEST MONITORING QUERIES

SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';
SQL TO FIND CONCURRENT REQUEST SID,OS PROCESS DETAILS BY REQUEST ID
*******************************************************************
select a.argument_text, a.phase_code, a.status_code, a.oracle_process_id "DB_PROCESS", a.OS_PROCESS_ID "MT_PROCESS", f.user_name
from apps.fnd_concurrent_requests a, apps.fnd_user f where request_id='&REQUEST_ID' and a.requested_by=f.user_id;
OR
==
SELECT C.SID, C.SERIAL#, A.PID
FROM   GV$PROCESS A, FND_CONCURRENT_REQUESTS B, GV$SESSION C
WHERE  B.ORACLE_PROCESS_ID = A.SPID
-- AND    B.REQUEST_ID like '%'
AND    B.REQUEST_ID =&x
AND    A.ADDR=C.PADDR;

SQL TO FIND THE CONCURRENT REQUEST TRACE FILE DETAILS (Input Request ID)
*************************************************************************
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id|
|'.trc',
--'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id ;

Blocks Corrupted in system09.dbf After Fresh Installation of E-Business Suite R12.1.1

We have identified a block corruption in the 12.1.1 Fresh Installed Instance, This block corruption identified while running RMAN.

RMAN LOG:
=========

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 09/06/2011 19:51:02
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/db/apps_st/data/system09.dbf

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
352 165657 1 0 ALL ZERO
352 165656 1 0 CORRUPT

SQL> select file_name,FILE_ID,TABLESPACE_NAME from dba_data_files where file_id=352;
FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------------------------------------ ---------- ------------------------------
/u01/db/apps_st/data/system09.dbf 352 SYSTEM

DB Verify OUTPUT
================
dbv file=/u01/db/apps_st/data/system09.dbf feedback=1000
DBVERIFY - Verification complete
Total Pages Examined : 192128
Total Pages Processed (Data) : 53193
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 27615
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2081
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 82766
Total Pages Marked Corrupt : 26473
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3723391595 (1388.3723391595)


The Segments are Free segments - No need to Panic
=====================================
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
2 , greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
4 5 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
6 7 FROM dba_extents e, v$database_block_corruption c
8 WHERE e.file_id = c.file#
9 AND e.block_id <= c.block# + c.blocks - 1
10 AND e.block_id + e.blocks - 1 >= c.block#
11 UNION
12 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
13 , header_block corr_start_block#
14 , header_block corr_end_block#
15 , 1 blocks_corrupted
16 , 'Segment Header' description
17 FROM dba_segments s, v$database_block_corruption c
18 WHERE s.header_file = c.file#
19 AND s.header_block between c.block# and c.block# + c.blocks - 1
20 UNION
21 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
22 , greatest(f.block_id, c.block#) corr_start_block#
23 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
24 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
25 - greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
26 27 FROM dba_free_space f, v$database_block_corruption c
28 WHERE f.file_id = c.file#
29 AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
30 31 order by file#, corr_start_block#;


OWNER SEGME SEGME PARTITION_NA FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- ----- ----- ------------ ---------- ----------------- --------------- ---------------- --------------
352 165656 165656 1 Free Block
352 165657 165657 1 Free Block

SOLUTION TO FIX THE ISSUE
==========================

This is known issue in R12.1.1, The installation media of R12 has block corruption in it.

Ref NoteID: ID 840963.1

Have performed below actions to fix it.

SQL> select sum(bytes/1024/1024),sum(maxbytes/1024/1024) from dba_data_files where FILE_NAME like '%system09.dbf';
SUM(BYTES/1024/1024) SUM(MAXBYTES/1024/1024)
-------------------- -----------------------
                1501                       0

SQL>alter database datafile '/u01/db/apps_st/data/system09.dbf' resize 1000M;

Check for Corruption Now: NONE :)


$dbv file=/u01/db/apps_st/data/system09.dbf feedback=1000
DBVERIFY: Release 11.1.0.7.0 - Production on Wed Sep 7 08:41:53 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/db/apps_st/data/system09.dbf
................................................................................
................................................
DBVERIFY - Verification complete
Total Pages Examined         : 128000
Total Pages Processed (Data) : 53193
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 27615
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2081
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 45111
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3723488883 (1388.3723488883)