Sorry Folks. I was not able to update the blog. I was busy in an upgradation from 11.5.10.2 to 12.1.3 on IBM-AIX.

As I have successfully done the Go-Live. I will update the blog with upgradae details.

Some Useful Unix commands and sql queries

Killing stubborn Unix Processes



===============================


cat /dev/null > /tmp/ kill -9


+++++++++++++++++++++++++++



Get the Total number of files


=============================


select count(*) from dba_data_files union


select count(*) from dba_temp_files union


select count(*) from v$logfile union


select count(*) from v$controlfile;


+++++++++++++++++++++++++



Get Mountpoint hosting the DBFs


-------------------------------


select distinct substr(name,1,instr(NAME,'/',-1,1)-9) fsname from v$datafile;
 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

To delete files based on date and file extensions

----------------------------------------------

ls -l *.dat grep "Jul" tr -s " " cut -d " " -f9 > zzjul.sh


ps -ef|grep "" sort grep -v grep awk '{print $2'}


Generate Compression script for cold backup on source database


====================================================

sqlplus /nolog

conn /as sysdba

set lines 132

set head off

set pages 0

select '/usr/bin/compress < ' FILE_NAME 
' > (/DEST_BACKUP_DIR/)' 
substr(FILE_NAME,30)
'.Z'
from dba_data_files
order by substr(FILE_NAME,30)
spool ColdBackup..ksh
/
spool off
 
+++++++++++++++++++++++++++++++++++++++++++++
 
Generate Uncompression script for cold backup on source database


======================================================

select '/usr/bin/uncompress < /mnt2/oradata/backup/gpses117'
substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
'.Z > '
FILE_NAME
from dba_data_files
order by substr(FILE_NAME, instr(FILE_NAME,'/',-1,1)+1)
spool RestoreBackup_gpser117.ksh
/
spool off

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

All Scheduled Jobs


==================

set wrap on
set lines 140
set veri off
col PROGRAM for a20
col UnitCode for a8
col TypeCode for a8

select b.program, a.request_id, resubmit_interval "Interval", resubmit_interval_unit_code "UnitCode"

,resubmit_interval_type_code "TypeCode", resubmit_time "ResubTime", resubmit_end_date "EndDate"
,resubmitted, a.request_type, request_limit ,increment_dates ,resub_count,
exp_date, root_request_id "RootReqID", origin
from fnd_concurrent_requests a ,fnd_conc_req_summary_v b
where root_request_id is not null
and a.request_id = b.request_id;
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
Get the SID for a given SPID


========================

col program for a30
col username for a10

col spid for a9

select A.PID, A.SPID, A.USERNAME, A.SERIAL#,
B.SID, B.COMMAND, B.PROGRAM
from v$process A, v$session B

where A.spid = &spid

and B.PADDR = A.ADDR

/
 
+++++++++++++++++++++++++++++++++++++++++
 
List of Users with SYSADMIN Responsibility


===============================

set pages 45
set lines 132
col RESPONSIBILITY_NAME for a30
col SECURITY_GROUP_NAME for a30
col APPLICATION_NAME for a30
col USER_NAME for a15

select user_name, application_name, responsibility_name, u.END_DATE, ur.END_DATE
from apps.fnd_user u, apps.fnd_user_resp_groups ur,
apps.fnd_responsibility_vl r, apps.fnd_application_vl a,
apps.fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and r.RESPONSIBILITY_NAME = 'System Administrator'
and ur.security_group_id = s.security_group_id
order by 1
/

Oracle 11gR2 RAC implementation video

RMAN duplicate information

If your PRODUCTION database is in archivelog mode and you are doing refresh the TEST database which is not in archivelog mode with the help of RMAN new feature (duplicate database). Then archive log will be enabled automatically in TEST database. You have to disable the archivelog manually.

RMAN Duplicate

Suppose you have to create duplicate database from PROD and you cannot create recovery catalog and connect to target instance also. Then no need to worry in 11GR2 it is possible to create duplicate database without connecting to recovery catalog and target instance.

RMAN>duplicate target database to "ORCL" nofilenamecheck
until time "to_date('31-03-2011 11:44:05','dd-mm-yyyy h24:mi:ss')"
backup location '/software/backup';

For getting the date and time details run below command on PROD.

sql>select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;

Oracle Apps Interview Questions for Freshers and 1 year experience

1. What is R12 architecture?

2. What are the various tops available in oracle apps?

3. What are the new features of R12?

4. How to do multi node installation?

5. How to do single node installation?

6. What is autoconfig?

7. How to apply a patch in oracle application?

8. How to change the HTTP port in R12?

9. What ad utilities are available in R12 tell few names?

10. What is the role of adadmin utility?

11. How to register a new product in oracle application?

12. How to compile apps invalid objects?

13. What is difference between apps, applsys and applsyspub?

14. How applsyspub work in oracle apps?

15. How to stop and start apps services?

16. How many Oracle Homes are available in oracle apps? Explain.

17. Adpatch, Adadmin and adworker log file path?

18.  Concurrent Manager Log and output files path?

19.  How to do cloning in oracle apps? Explain Steps.

20.  What is the use adctrl utility?

21.  How we can skip a worker?

22.  How to check whether Concurrent Manager services are up and running or not?

23.  Which apps services are main and which are optional?

24.  What is the path of apache log files?

25.  What is the path of opmn log files?

26.  Which files include in INST_TOP?

27.  Which configuration files are related to Apache?

28.  How to create a user in Oracle Application?

29.  How to check the concurrent Manager log from frontend and backend?

30.  What is the use of xml file?

31.  What is the version of Application Oracle_home and IAS_ORACLE_HOME?

32.   How to compile jsps?

33.   Which top contains all the AD utilities?

34.   Which top contains FMX and FMB files?

35.   What is the meaning of 12.0.0?

36.   What is the meaning of US folder which created under the forms and reports?

37.   How to check whether concurrent manager are up and running or not from frontend?

38.   How to check that how many processes are assign to a manager?

39.   What is responsibility and how to create?

40.   How to define a new concurrent manager?

41.   What is profile option?

42.    How to find form, report and apache version?

43.   Suppose you joined a company as a apps dba. You got one project and you do not know anything about the server, file system, etc... Suddenly your manager asking Concurrent Manager log file. How do you find out the concurrent manager log file?

44.   Suppose you are applying a patch and suddenly patch got failed. You are having only 5 hrs downtime and 3 hrs downtime you already used. You checked and found that you missed one prereq of main patch. what you will do in this case?

46.  What is c, d, g and u driver?

47.   How you will troubleshoot an issue which you get while applying a patch?

48.   What is the use of .rf9 files?

49.   How will you generate a form and report?

50.   What various levels are available with profile options?

51.   You have done one cloning from PROD to TEST and cloning completed successfully. But concurrent manager are not coming up. How you will solve this issue?

52.   What is cmclean.sql and when we need to run?

53.   Which view provides node information?

54.   What is workflow and how to configure the workflow?

55.    How do you check whether workflow services are up and running or not?

56.    What is the procedure for applying a patch? Explain step-by-step.

57.    How will you check that a patch is already applied or not?

58.    What is the difference between ad_bugs and ad_applied_patches?

59.    What two tables are created when you apply a patch and what happen with those tables when patch is finished?

60.    What is the meaning of Context_Name?

61.     How to change the Apps and oracle user Password?

62.     Why applsys and apps password are same?

63.     What is sun java applet in R12?

64.    How will you troubleshoot Apache related issues?

65.     What is developer patch and why we need to apply?

66.     How you will register a user in the application?

67.     How you will register a new top in the application?

68.     What is FNDSM?

69.   what is CONTEXT_FILE and where is situated in R12?

70.   How to relink the application through adadmin?

71.   What happened when you enable the maintenance mode?

72.   How to enable and disable the maintenance mode?

73.  Is we can apply a patch without enable the maintenance mode? If yes how?

74.   What are the options we can use with adpatch utility?

75.   How to reduce the patching time?

76.    How autoconfig work?

77.    how we can monitor the application?

78.   What is dbc file and why it is important for application?

79.   Suppose users are complaining that login page is not coming. What could be the reason for this and how you will troubleshoot this?

80.   What is adpreclone.pl and adcfgclone.pl?  When we need to run both?

81. What information adpreclone.pl collects?

82.  from where adcfgclone.pl takes the information for the cloning?

83.  Is application should be down for running the adpreclone.pl?

84.  If one company do the cloning every weekend. So you will run the adpreclon.pl everytime. If yes why and if no why?

85.  Suppose users are complaining that concurrent manager are showing inactive no manager. What could be the reason and how will you troubleshoot?

86.  How will you set a concurrent request which should be run everynight at 10:00 PM?

87.  What concurrent managers are available in apps and what is the role of them?

88.  Suppose internal manager is not coming up then how will you troubleshoot this?

89.  If internal manager is down then other concurrent manger will be up and running or not?

90.  How to merger the patches? Which patches you can merge?

91.  How to hide the password while applying a patch?

92. What are the types of patches?

93. Suppose you are applying a patch and you forgot to enable the maintenance mode then patch will be going on or erroring out?

94.  Suppose users are complaining that forms are not opening. How will you troubleshoot?

95.  How Application login works?

96.   How to do single node to multi node cloning and vice versa?

97. Can I run the autoconfig parallel in R12?

98.  What information contains by ad_deferred_jobs and fnd_install_processes?

99. In R12 we are using form service or form server?

100. What components are managed by OC4J in R12?