Hi DBA-Mates,
As per experiences some real time questions and answers are found, which may useful to all specially for Oracle Apps DBA.
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:
Regards,
ora-data Team
