Challenging DBA and Apps DBA interview questions

Hi,
I find some time to write on the challenging Apps DBA interview questions.I found these questions challenging as these questions will surely need some time tolearn,practise along with practical experience on the tasks and concepts of Oracle Apps,RAC.

1)Explain Database migration steps in details?(DBA interview question)
Ans: Database migration or Data migration involves a team effort of DBAs,Developers,Data Modelors/Database Designers
Applications Administrator if the other applications servers like JBOSS/tomcat is involved.When we are working
in team the best way to complete this task is to prepare a plan of tasks along with time and table well in advance on an excell sheet and intimate each of the team members to communicate effectively.
From DBA's point below is an example of one such task,Check the below link for the details:

http://rafioracledba.blogspot.com/2011/04/step-by-step-migration-of-schemas-from.html

2)What are the 3 ips used in RAC configuration,explain them in brief?(DBA interview question)
Ans: 3 IPs used in RAC configuration are public,private and virtual IP.
In brief we can describe them as follows:

1.Public IP address:Public IP adress is the normal IP address typically used to manage storage,system and database.
2.private IP address:Private IP address is used only for internal clustering processing (i.e Cache Fusion or as interconnect).
3.Virtual IP address:VIP is used by database applications to enable fail over when one cluster node fails.


3)What is cache fusion?(DBA interview question)
Ans: Oracle RAC uses Cache Fusion to synchronize the data stored in the buffer cache of each database instance. Cache Fusion moves current data blocks (which reside in memory) between database instances, rather than having one database instance write the data blocks to disk and requiring another database instance to reread the data blocks from disk. When a data block located in the buffer cache of one instance is required by another instance, Cache Fusion transfers the data block directly between the instances using the interconnect, enabling the Oracle RAC database to access and modify data as if the data resided in a single buffer cache.

4)What is voting Disk,what is the major use of it?(DBA interview question)
Ans:Voting disk Manages cluster membership and arbitrates cluster ownership
between the nodes in case of network failures.The voting disk is a file that
resides on shared storage.

5)What is OCR,what is the major use of it?(DBA interview question)
Ans:Oracle Cluster Registry (OCR)–Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR contains information such as which database instances run on which nodes and which services run on which databases. The OCR also stores information about processes that Oracle Clusterware controls. The OCR
resides on shared storage that is accessible by all the nodes in your cluster.

6)In RAC 11g as we all know ASM can be used for storing OCR and voting disk,if that is the case how the clusterware start?(DBA interview question)
Ans: The clusterware starts and works properly because of the new feature added in Oracle11g release2 for ASM i.e ACFS(ASM cluster file system).ACFS is available in 11gR2 for LINUX and few unix platforms and Windows operating system
also have a similar solution available.

* ACFS will work with Single Instance Installations as well as Cluster Databases.
It is strongly integrated with the Oracle Clusterware 11g release2.
One more point to note is - Supported protocols for NAS are NFS(Network file system and CIFS(Common Internet File System).

This new feature is also named Unified Storage Management and allows for storing the following data in ASM:

1)Application File Data
2)Oracle Cluster Registry (OCR)
3)Voting Disk and
4)Oracle Binaries

Major advantages of ACFS are:

1)We will have all the trace files, alert.log,reports in ASM which makes ASM a complete storage management system .Because of this reason both database and non-database files are stored ins ASM and it completely eliminates the need for any third party cluster file systems like veritas and sun clusters.

2) ACFS uses a journaling mechanism and uses endian independent metadata to support large files and file systems to an extent up to exabytes.A special metadata log structure is maintained for integrity checks and fast recovery through a metadata transaction engine and


3)File systems in ASM can dynamically be expended and contracted with no downtime.
High availability is fully supported through mirroring and striping capabilities for all files now with ASM.


7)How you change apps password?Explain the syntax of FNDCPASS?(Apps DBA interview question)
Ans: FNDCPASS utility is used to change the password of apps/applsys and all other application products users like gl,ar.FNDCPASS utilliy is supplied with various parameters and options,details are given below along
with one task for complete understanding.

Note 1: We use FNDCPASS to change apps password,as the APPLSYS (APPS) password became corrupted using ALTER USER because an applications session was not maintained at the same time. This apps session is necessary to change the APPLSYS password in: 'Security> Oracle> Register' WHILE being in SQL*PLUS as the SYSTEM user thas why we use The supported method FNDCPASS to change the password.

Note 2:Backup FND_ORACLE_USERID and FND_USER tables before using FNDCPASS utility for safety purpose.


To implement the solution, please execute the following steps:

1. Restore the FND_ORACLE_USERID and FND_USER tables from a backup.

2. Then run FNDCPASS to change the APPLSYS password. Ex.

FNDCPASS apps/ 0 Y system/ SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps


Eg:
-bash-3.2$ FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS apps
Log filename : L6021164.log


Report filename : O6021164.out
-bash-3.2$ less L6021164.log


+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:
+---------------------------------------------------------------------------+

Current system time is 23-SEP-2011 03:53:32

+---------------------------------------------------------------------------+

Working...


+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 23-SEP-2011 03:53:38

+---------------------------------------------------------------------------+


For example,the following command changes the GL user password to 'gl'.

FNDCPASS apps/apps 0 Y system/manager ORACLE GL gl


-bash-3.2$ FNDCPASS apps/apps 0 Y system/manager ORACLE GL gl
Log filename : L6021170.log


Report filename : O6021170.out

-bash-3.2$ less L6021164.log

+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

module:
+---------------------------------------------------------------------------+

Current system time is 23-SEP-2011 03:59:01

+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 23-SEP-2011 03:59:01

+---------------------------------------------------------------------------+

Note 1: Passwords for APPLSYS and the APPS schemas -- including the MRC schema -- must be the same.
If you change the password for one, FNDCPASS automatically changes the others

Note 2:After changing the password as the golden rule for apps dba check the log file if it gives
error than password is unchaged.Error details in log file can be as give below:

ERRORs:
APP-FND-01496 Results From FNDCPASS Chaning The APPLSYS password
APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password

Note 3:When changing 'apps' password it is important to run autoconfig to propagate the change across the application or else we will need change the 'apps' password in following files manually which can be a tedious task.

1. $IAS_ORACLE_HOME\Apache\modplsql\cfg\wdbsvr.app file
2. $FND_TOP/resource/wfmail.cfg
3. $COMMON_TOP/admin/scripts//adcmctl.ch
4. $OA_HTML/bin/appsweb.cfg
5. $AD_TOP/admin/template/CGIcmd.dat

Q 8) What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?(Apps DBA inteview question)
Ans: If we lost the 'apps' user password we can see this password in wdbsvr.app file.
The file wdbsvr.app file is located under $IAS_ORACLE_HOME/Apache/modplsql/cfg

Based on file location I am sure you can say this is related to Apache, & looking into modplsql/cfg , I am sure you will say its related to mod_pls (mod plsql component of Apache/Oracle 11i WebServer) configuration file. This file is used by mod_plsql component of Apache to connect to database. So when you type url http://hostname:port/pls/SID , whenever Apache(11i Web Server) finds that request is for /pls/ then Apache delegates this request to mod_pls component which in turn pick this file & check if there is any DAD with name SID (in our example its VISION11I) &
Sample entry in wdbsvr.app
[DAD_VISION11I]
connect_string = VISION11I
password = apps
username = APPS
default_page = fnd_web.ping

On typing http://hostname:port/pls/VISION11I ,
it will connect to database using apps schema & will return you page fnd_web.ping ( where fnd_web is package & ping is procedure or vise versa).So story about this file doesn't stop here,this URL which I mentioned about is quite useful in troubleshooting so you can check if database connection is working fine
or not. Another thing you want to check about this file is since it stores APPS password you need to change here whenever you change apps password .

Note:In Apps 11i we have apps password without encrypted form in the below mentioned locations:

• iAS_TOP/Apache/modplsql/cfg/wdbsvr.app
• ORACLE_HOME/reports60/server/CGIcmd.dat

In R12 you won't see these files anymore because Modplsql module got removed and we won't be seeing the files with apps password.

9)Explain the process when we login with 'sysadmin' what happens at the backend?(Apps DBA interview question)
Which table is involved?
Ans: When we login using 'sysadmin' user and password there is a validation takes place,once the user gets validated than E-business suite home page is displayed.

8)How you troubleshoot apps login issue?(Apps DBA interview question)
Ans:Atul's link explain this in best way,please go through it even for troubleshooting at our work environment also.

http://becomeappsdba.blogspot.com/2006/10/troubleshoot-oracle-apps-web-server.html

9)Explain the major troubling which you had done in Oracle Apps?
Ans:We have to explain the recent troubeshooting which we had done recently.Whenever we are doing troubleshooting in Oracle Applications,it is very important to get familiar with the log file locations,depending
on release of Apps.In Apps 11i and R12 the log file locations have changed,so best link I found to remember this locations is given below:

http://easyoracleapps.wordpress.com/category/r12/

10)What do we have in FND_NODES?when do we run FND_CONC_CLONE.SETUP_CLEAN ?(Apps DBA interview question)

Ans:FND_NODES have the information about node_name,node_mode, support_cp.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table to clear corrupted setup after performing
clonning of Applications.

##
## before cleanup
##

SQL>select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
HOST01 O Y N Y N
MIDTIER01 O N Y N Y
APPSERVER21 N Y N Y <-- bogust host
APPSERVER22 N Y N Y <-- bogust host
APPSERVER23 N Y N Y <-- bogust host
AUTHENTICATION O N N N N

6 rows selected.



##
## Cleanup
##
login as 'applmgr'(application filesystem owner),set the application environment and run
FND_CONC_CLONE script to remove bogust host entries.Now login with 'apps' user in Database.

SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


##
## After cleanup
##

SQL> select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES
/


##
## Run AutoConfig
##
Note:Context file location
In Apps 11i:$APPL_TOP/admin/$TWO_TASK.xml
In Apps R12:$INST_TOP/appl/admin/TWO_TASK_.xml

## Host01
In Apps 11i:
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/ appspass=

In Apps R12:
$AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/TWO_TASK_.xml
appspass=apps


## Host
$AD_TOP/bin/adconfig.sh contextfile=$APPL_TOP/admin/XTPR_host02.xml appspass=


##
## After AutoConfig runs
##


select node_name, node_mode, support_cp,
support_web, support_admin, support_forms
from FND_NODES

NODE_NAME N S S S S
------------------------------ - - - - -
MIDTIER01 O N Y N Y
AUTHENTICATION O N N N N
HOST01 O Y N Y N


11)Where do we find the details of all the ports in Oracle Apps?
Ans: Details of all the ports for various severs are present in portlist.ini file.
This file is located inside technology stack in the below mentioned path:

/oraAPP/apps/tech_st/10.1.2/install

The portlist.ini file have the details of all the ports information of all the servers like HTTP,Java,Oracle Notification
Server,Oracle Developer suite,DATABASE LISTENER,Web Cache,Reports Services,DCM Discovery,Log Loader which can be
seen below.

-bash-3.2$ view portlist.ini

;OracleAS Components reserve the following ports at install time.
;As a post-installation step, you can reconfigure a component to use a different port.
;Those changes will not be visible in this file.

[System]
Host Name = TESTNODEebs.in.apps.com

[Ports]
Oracle HTTP Server port = 7778
Oracle HTTP Server Listen port = 7779
Oracle HTTP Server SSL port = 8250
Oracle HTTP Server Listen (SSL) port = 4444
Java Object Cache port = 7000
Oracle Notification Server Request port = 6003
Oracle Notification Server Local port = 6102
Oracle Notification Server Remote port = 6201
Oracle Developer Suite HTTP port = 8888
Oracle Developer Suite JMS port = 9240
Oracle Developer Suite RMI port = 23910
ASG port = 7891
Oracle HTTP Server Diagnostic port = 7200
Log Loader port = 44000
Application Server Control RMI port = 1850
DCM Discovery port = 7100
Oracle Net Listener = 1521
Oracle Management Agent Port = 1157
Application Server Control port = 1156
Web Cache HTTP Listen port = 7778
Web Cache HTTP Listen (SSL) port = 8250
Web Cache Administration port = 9400
Web Cache Invalidation port = 9401
Web Cache Statistics port = 9402
Reports Services bridge port = 14011
Reports Services discoveryService port = 14021
Reports Services SQL*Net port = 1950

If you don't know port number of your web server you can check it in file
$IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf with directive Listen & Port

12)How we check the sysadmin password which we are using to login Oracle E-business suite home page is correct?(Apps DBA interview question)
Ans:We Use Following query to check whether 'sysadmin' password that we are
using is correct or incorrect.

select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
is wrong. We have to change sysadmin password using 'FNDCPASS' utility.

Eg:

SQL> select fnd_web_sec.validate_login('SYSADMIN','Passord_for_sysadmin')
from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','sysadmin')
--------------------------------------------------------------------------------
N

SQL> select fnd_web_sec.validate_login('SYSADMIN','welcome')
from dual; 2

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','WELCOME')
--------------------------------------------------------------------------------
Y

13)How to check the Standby Database is in sync with production Database?(Apps DBA interview question)
Ans: Check the below link for useful details:

http://www.oraclemasters.in/?p=1255