Interviews Questions And Answers for Oracle Apps

Hi DBA-Mates,
As per experiences some real time questions and answers are found, which may useful to all specially for Oracle Apps DBA.

Below are some important and useful Oracle Application Questions and Answers:

Q) How to check if your system is SSL enabled?  

Answer: 
For quick check: If your context file contains the values of
s_url_protocol / s_local_url_protocol set to https, then
your environments has to be SSL-enabled. If the rest of config is
absent but these are set to https, then URL does not resolve
- which is an indirect check of incomplete config. 

And also you can use this query to check 
SELECT PROFILE_OPTION_VALUE 
FROM applsys.fnd_profile_option_values 
WHERE PROFILE_OPTION_VALUE like 'http%';

Q) Query to find who and when update an Oracle Application user's 
profile?
Answer:
SELECT t.user_profile_option_name, profile_option_value,v.creation_date,
v.last_update_date, v.creation_date - v.last_update_date "Change Date", 
(SELECT UNIQUE user_name 
FROM fnd_user 
WHERE user_id = v.created_by) "Created By", 
(SELECT user_name 
FROM fnd_user 
WHERE user_id = v.last_updated_by) "Last Update By" 
FROM fnd_profile_options o, 

fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

Q)How to check product installation Information Version of Apps ?
Answer:
Select * from fnd_product_groups; 

Q) Query for Information about concurrent requests?
Answer:
Select * from fnd_concurrent_requests; 

Q)How to check Information about particular concurrent request ?
Answer:
Select logfile_name, logfile_node_name, outfile_name, outfile_node_name 
from fnd_concurrent_requests where request_id =<request id> ;

Q) How to fetch Stores values for various profile options?
Answer:
Select * from FND_PROFILE_OPTION_VALUES; 

Q) How to get Information about various profile options?
Answer:
Select * from FND_PROFILE_OPTIONS; 

Q) How to Find database version?
Answer:
Select * from v$version; 

Q) How to find Oracle Workflow Cartridge Release Version? 
Answer:
Select wf_core.translate ('WF_VERSION') from dual; 

Q)How to Find out Invalid number of Objects  ?
Answer:
Select count (*) from dba_objects where status ='INVALID' ;

Q) How to Check which node is running what service? 
Answer:
Select * from fnd_nodes; 

Q) How to find out Package Header?
Answer:
Select name, text from dba_source where text like ‘%Header: %’ and owner = ‘APPS’
and name =‘INVALID_OBJECT_NAME’;

Q)How to get Information about the bugs fixed in Installation ?
Answer:
Select * from ad_bugs; 

Q) How to backup the definition of a View before dropping a view?
Answer:
Select dbms_metadata.get_ddl (’VIEW’,'RG_View’,'APPS’) from dual; 

Q) Fetching Information about the applied patches ?
Answer:
Select * from ad_applied_patches;

Q)How to check if the partitions of a table are set to LOGGING?
Answer:

Select partition_name, logging
from dba_tab_partitions where table_name=’WF_LOCAL_ROLES’;

Q)How to find out version of a package?
Answer:

select text from dba_source where line=2 and name=’AP_IMPORT_INVOICES_PKG’;

Q)How to check whether the product is install, shared and Not installed in Apps?
Answer:

select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I',’Fully Installed’,
‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;

Q)How to check which object is corrupted?

Answer:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents WHERE file_id = 64 and 1 between block_id AND block_id + blocks-1;

Q)Which users logged in to the system longer than 6 months ago ?Answer:

select a.user_id,a.user_name,b.user_id,b.start_time
from FND_USER a, FND_LOGINS b
where a.user_id = b.user_id
and b.start_time = (select max(start_time) from FND_LOGINS where user_id = b.user_id)
and START_TIME < SYSDATE - 180;

Q)To see how many distinct users are connected to my system, at particular time ?
Answer:

select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY
Responsibility,fu.LAST_LOGON_DATE from fnd_user fu,
fnd_responsibility fr, icx_sessions ic;

Q) If a database is running in NOARCHIVELOG mode which type of backups you can take?
Answer: In no archive log mode, you have to take cold backup only, it means your database should be down and then take backup.

For this, you can right shell script in order:
(a) shutdown the database
(b) copy all the files
(c) startup the database.

Q) How to see DB size at OS level command?
Answer: By Issuing the following command in Unix: 

$ ipcs -pmb
ipcs: invalid option -- b
usage : ipcs -asmq -tclup
ipcs [-s -m -q] -i id
ipcs -h for help.

Q) What are the Different Shutdown options in database?
Answer: 

1) shut (for normal shutdown) -à It will wait until all the users to logout from database.
2) Shut immediate-à It will roll forwards the committed data and rollback the uncommitted data.
3) shut transactional -à It will wait until all the transactions to be complete by a commit or
rollback.
4) Shut abort-à It will not check for users, transations etc... Just it will abort from database by shutting down the instance.

Some more useful links:

Important SQL Scripts for DBA

Interview Q & A

R12.1 Vs R12.2

Real Time/Life Experiences

Career Planning Tips

For any query or suggestions please write to our comment box or contact us @ora-data.blogspot.com.

Regards,
ora-data Team