Part-1
Q 1)How to get the list of patches applied to database if lsinventory is corrupted?Ans: we have folder under $ORACLE_HOME called db_storage,we can do 'ls' to get the list of patches if our inventory is corrupted.(In case of windows you can just view by going to that path).
Note: we always have to backup the oracle home and inventory to be on safe side.
Below command will help you on it.
tar cvf – $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
I find the below link useful one:
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/
Q 2)If we want CRS(Cluster Ready Service) services to not get detected,what we should do?
Ans: $crsctl disable crs
Q 3)How to list the nodes in your cluster by running the following command on one node?
Ans: We can list the nodes in our cluster by running the following command on one node:
$ olsnodes
Q 4) How to schedule a existing concurrent request to run on specific day,all the times?
Ans : Let us say i have a gather stats concurrent request for a schema and I will it to run every weekend once(i.e every sunday),I can do it by following simple steps:
Step 1:Open the Cocurrent request in form window by specifying the specific request id(eg 4126398)
Step 2:Click on Detail Tab of the Concurrent request,we will be seeing schedule tab,click on it
Step 3:Schedule it on Sunday and Choose On specific days on the left side of the form page as shown in below screenshot
Q 5)How to recover corrupt OCR without backup?
Ans: We all know Oracle takes backups of OCR after every 4 hours,but what if we don't have backup and OCR is corrupted,we cannot go for installing whole clusterware which could be a lengthy process.
If we are on 10g R2 and later version then this can be done without re-installing Clusterware ( if you have backup of root.sh or it’s not overwritten by any
subsequent patch ) I tested this on my test machines.
We can get Current Voting Disk location
[root@racnode1 ~]# crsctl query css votedisk
0. 0 /OCFS/VOT
located 1 votedisk(s).
Current OCR Files location
[root@racnode1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 4344
Available space (kbytes) : 257800
ID : 601339441
Device/File Name : /OCFS/OCR
Device/File integrity check succeeded
Device/File Name : /OCFS/OCR2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
Output of CRS_STAT
[root@racnode1 ~]# crs_stat -t
Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd application ONLINE ONLINE racnode1
ora.racnode1.ons application ONLINE ONLINE racnode1
ora.racnode1.vip application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd application ONLINE ONLINE racnode2
ora.racnode2.ons application ONLINE ONLINE racnode2
ora.racnode2.vip application ONLINE ONLINE racnode2
ora.test.AP.cs application ONLINE ONLINE racnode1
ora….st1.srv application ONLINE ONLINE racnode1
ora.test.db application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2
I stopped clusterware on both nodes and removed OCR & Voting Disks.
[root@racnode1 ~]# ls -lrt /OCFS/*
-rw-r–r– 1 root root 399507456 Jun 29 14:05 /OCFS/OCR2
-rw-r—– 1 root oinstall 10485760 Jun 29 14:05 /OCFS/OCR
-rw-r–r– 1 oracle oinstall 10240000 Jun 29 14:05 /OCFS/VOT
[root@racnode1 ~]# rm -fr /OCFS/*
Tried again to start Cluster
[root@racnode1 ~]# crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
Clusterware could not startup.
[root@racnode1 ~]# crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
Thrown error in /tmp/crsct.* file about OCR
[root@racnode1 ~]# cat /tmp/crsc*
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Here, I lost all my OCR & Voting disk.
Below procedure can be used for recovery.
1) Execute rootdelete.sh script from All Nodes.
2) Execute rootdeinstall.sh from Primary Node.
3) Run root.sh from Primary node.
4) Run root.sh from all remaining nodes.
5) Execute remaining configurations (ONS,netca,register required resources)
1) Executing rootdelete.sh on all nodes, this script can be found under $ORA_CRS_HOME/install/
[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’
[root@racnode2 ~]# /u01/app/oracle/product/crs/install/rootdelete.sh
Shutting down Oracle Cluster Ready Services (CRS):
OCR initialization failed accessing OCR device: PROC-26: Error while accessing the physical storage Operating System error [No such file or directory] [2]
Shutdown has begun. The daemons should exit soon.
Checking to see if Oracle CRS stack is down…
Oracle CRS stack is not running.
Oracle CRS stack is down now.
Removing script for Oracle Cluster Ready services
Updating ocr file for downgrade
Cleaning up SCR settings in ‘/etc/oracle/scls_scr’
OCR initialization error can be safely ignored.
2) Execute rootdeinstall.sh on Primary Node, this script can also be found under $ORA_CRS_HOME/install
[root@racnode1 ~]# /u01/app/oracle/product/crs/install/rootdeinstall.sh
Removing contents from OCR mirror device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.031627 seconds, 332 MB/s
Removing contents from OCR device
2560+0 records in
2560+0 records out
10485760 bytes (10 MB) copied, 0.029947 seconds, 350 MB/s
3) Run root.sh on Primary node, this will create VOT & OCR files.
[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
“/OCFS/VOT” does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1
I had to touch this file to proceed
[root@racnode1 ~]# touch /OCFS/VOT
[root@racnode1 ~]# $ORA_CRS_HOME/root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Now formatting voting device: /OCFS/VOT
Format of 1 voting devices complete.
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
CSS is inactive on these nodes.
racnode2
Local node checking complete.
Run root.sh on remaining nodes to start CRS daemons.
4) Run root.sh from all remaining nodes.
[root@racnode2 crs]# ./root.sh
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
Checking to see if Oracle CRS stack is already configured
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory ‘/u01/app/oracle/product’ is not owned by root
WARNING: directory ‘/u01/app/oracle’ is not owned by root
WARNING: directory ‘/u01/app’ is not owned by root
WARNING: directory ‘/u01′ is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
assigning default hostname racnode1 for node 1.
assigning default hostname racnode2 for node 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node :
node 1: racnode1 racnode1-priv racnode1
node 2: racnode2 racnode2-priv racnode2
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 90 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
racnode1
racnode2
CSS is active on all nodes.
Waiting for the Oracle CRSD and EVMD to start
Oracle CRS stack installed and running under init(1M)
Running vipca(silent) for configuring nodeapps
Creating VIP application resource on (2) nodes…
Creating GSD application resource on (2) nodes…
Creating ONS application resource on (2) nodes…
Starting VIP application resource on (2) nodes…
Starting GSD application resource on (2) nodes…
Starting ONS application resource on (2) nodes…
Done.
Clusterware is up and running
[root@racnode2 crs]# crs_stat -t
Name Type Target State Host
————————————————————
ora.racnode1.gsd application ONLINE ONLINE racnode1
ora.racnode1.ons application ONLINE ONLINE racnode1
ora.racnode1.vip application ONLINE ONLINE racnode2
ora.racnode2.gsd application ONLINE ONLINE racnode2
ora.racnode2.ons application ONLINE ONLINE racnode2
ora.racnode2.vip application ONLINE ONLINE racnode2
5) Remaining Configuration
a) Configuring Server side ONS
[root@racnode1 crs]# $ORA_CRS_HOME/bin/racgons add_config racnode1:6200 racnode2:6200
b) Listener Configuration usint netca
we might want to remove listener.ora from both nodes as entries may exist already. Take backup or orignial listener.ora and use netca to configure &
register with OCR. Till 10g, we can not register listener using srvctl
Renaming orginal listener.ora
[oracle@racnode1 ~]$ mv $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.orig
[oracle@racnode1 ~]$ ssh racnode2 mv $ORACLE_HOME/network/admin/lstener.ora $ORACLE_HOME/network/admin/listener.ora.orig
c)Adding ASM, Instance, Database
[oracle@racnode1 ~]$ srvctl add asm -i +ASM1 -n racnode1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add asm -i +ASM2 -n racnode2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add database -d test -o /u01/app/oracle/product/10.2.0/db_1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test1 -n racnode1
[oracle@racnode1 ~]$ srvctl add instance -d test -i test2 -n racnode2
I restarted both nodes, got everything back. Yes, Services can be re-created.
[oracle@racnode1 ~]$ crs_stat -t
Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE racnode1
ora….C1.lsnr application ONLINE ONLINE racnode1
ora.racnode1.gsd application ONLINE ONLINE racnode1
ora.racnode1.ons application ONLINE ONLINE racnode1
ora.racnode1.vip application ONLINE ONLINE racnode1
ora….SM2.asm application ONLINE ONLINE racnode2
ora….C2.lsnr application ONLINE ONLINE racnode2
ora.racnode2.gsd application ONLINE ONLINE racnode2
ora.racnode2.ons application ONLINE ONLINE racnode2
ora.racnode2.vip application ONLINE ONLINE racnode2
ora.test.db application ONLINE ONLINE racnode2
ora….t1.inst application ONLINE ONLINE racnode1
ora….t2.inst application ONLINE ONLINE racnode2
Note : That’s why it’s recommended to take backup of root.sh after fresh install as subsequent patches can
overwrite root.sh script.
This is described on Metalink Note: 399482.1
Q 6)If we don't specify any location while adding ASM based datafile where it will get created?
Ans It will get created in $ORACLE_HOME/dbs path in Unix environment or $ORACLE_HOME/database in Windows environment
Q 7)If we missed the '+' sign while adding datafile to ASM what is the effect?
Ans The effect is that it creates Non-ASM based datafile in $ORACLE_HOME/dbs and database will be affected badly,in this case we need to use RMAN to convert from Non-ASM to ASM
,so never try or do this any time.this is just for information purpose.
Q 8)Name 2 scenarios where your skill were challeged as DBA?
Ans This question is often repeated in the interview,so please prepare and plan to answer this question
.Remember we need to answer 2 situations where we are tested as DBA,so if you have done some kind of restore and recovery in past for database it will ideal to express that experience,if you have done Oracle RAC installation and faced the issues make points and ellaborate it or if you have done any kind of Disaster recovery setup it will be good or If you have done upgrade from Oracle 11i to R12 and you remember the situation which made you struggle along with resolution it would be one more great choice to influence the interview.
Q 9)How many recoveries of Database have you done in your Career?
Ans This question is again to test how you react when you encounter a issue with your Database,so better to explain a scenario where you have actually involved in doing restore or recovery.
Eg: We lost one critical table of our production environment,so the steps involve while doing RMAN restore until time or SCN will be useful in this case,again make sure you provide the valide reason why you had choosen specific method for doing restore or recovery of table.So my sincere suggestion is to plan the steps for this question along with reasons,to answer point to point and help interview to pick you for that position.
Part-2
1)How to find the E-business suite login URL?
Ans:
SQL> conn apps
Enter password:
Connected.
SQL> select home_url from icx_parameters;
HOME_URL
--------------------------------------------------------------------------------
http://testnode1.comp.com:8000/OA_HTML/AppsLogin
2)How to find the release of Apps installed or version installed in our machine?
Ans:conn apps
Enter password:
Connected.
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
--------------------------------------------------
12.1.1
3)What is Yellow Bar Warning in Apps?
Ans: Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode and uses J-Initiator to run Java applets on a desktop client. If an applet is “trusted,” however, Java will extend the privileges of the applet.The Yellow Warning Bar is a warning that your applet is not running in a trusted mode.To indicate that an applet is trusted, it must be digitally signed using a digital Certificate,so Oracle Applications requires that all Java archive files must be digitally signed.
4)How to check the custom top installled?
Ans:
SQL> Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME
From fnd_application
Where application_Short_name like '%CUST_TOP_name%';
5)How to check multi-org is enabled in Oracle applications?
Ans:
SQL> select multi_org_flag from fnd_product_groups;
M
-
Y
Note:For enabling multi-org check the MY ORACLE SUPPORT notes 396351.1 and 220601.1
6)How to compile invalid objects in Oracle Applications?
Ans: Check the below link for all possible ways to compile the invalid objects in Oracle Application.Usually 'adadmin' utility provides us the option to do this task.
http://onlineappsdba.blogspot.com/2008/05/how-to-compile-invalid-objects-in-apps.html
7)Can we install Apps Tier and Database Tier on different Operating system while installing Oracle EBS 11i/R12?
Ans: Yes it is possible.We can do this by following below MY ORACLE SUPPORT notes:
Oracle Apps 11i --> Using Oracle EBS with a Split Configuration Database Tier on 11gR2 [ID 946413.1]
Oracle Apps R12 --> Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]
8)How to find the node details in Oracle Applications?
Ans: FND_NODES tables in 'apps' schema helps in finding node details after installation,clonning and migration of applications.
SQL> SELECT NODE_NAME||' '||STATUS ||' '||NODE_ID||' '||HOST
FROM FND_NODES;
9)How to see the products installed and their versions in Oracle Applications?
Ans:
SQL> SELECT APPLICATION_ID||''||ORACLE_ID||''||PRODUCT_VERSION||''||STATUS||''||PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS;
O/P looks like below:
172 172 12.0.0 I R12.CCT.B.1
191 191 12.0.0 I R12.BIS.B.1
602 602 12.0.0 I R12.XLA.B.1
805 805 12.0.0 I R12.BEN.B.1
8302 800 12.0.0 I R12.PQH.B.1
8303 800 12.0.0 I R12.PQP.B.1
809 809 12.0.0 I 11i.HXC.C
662 662 12.0.0 I R12.RLM.B.1
663 663 12.0.0 I R12.VEA.B.1
298 298 12.0.0 N R12.POM.B.1
185 185 12.0.0 I R12.XTR.B.1
10)How to see the concurrent Requests and jobs in Oracle Applications?
Ans: FND_CONCURRENT_REQUESTS can be used to see the concurrent requests and job details.These details are useful
in troubleshooting concurrent manager related issues.
SQL>SELECT REQUEST_ID||' '||REQUEST_DATE||' '||REQUESTED_BY||' '||PHASE_CODE||' '||STATUS_CODE
FROM FND_CONCURRENT_REQUESTS;
O/P will be as given below:
REQUEST_ID||''||REQUEST_DATE||''||REQUESTED_BY||''||PHASE_CODE||''||STATUS_CODE
--------------------------------------------------------------------------------------------------------
6088454 24-NOV-11 1318 P I
6088455 24-NOV-11 1318 P Q
6088403 24-NOV-11 0 C C
6088410 24-NOV-11 0 C C
Where:
PHASE_CODE column can have values:
C Completed
I Inactive
P Pending
R Running
STATUS_CODE Column can have values:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
11)What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables?
Ans: FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables are created and Dropped during the 'adadmin' and 'adpatch' sessions.
Both AD utilities (adpatch/adadmin) access the same tables to store the workers details, so both FND_INSTALL_PROCESSES and
AD_DEFERRED_JOBS tables need to be dropped from the failed adpatch session ,so that adadmin/adpatch session can run successfully next time.
Part-3:
1)what is FNDLOAD?(APPS DBA
interview)
(OR)
How we can migrate concurrent programs of one application?(Apps DBA)
Ans:FNDLOAD is a an Oracle utility to migrate data from one instance to
another.It can be used with several different
entities within Oracle 11i,R12 applications.
Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ]
]
where:
logon is username/password[@connect]
mode is either UPLOAD or DOWNLOAD
configfile is the configuration file
datafile is the data file
entity is an entity name, or - to specify all values in an upload
param is a NAME=VALUE string used for parameter substitution
FNDLOAD can be used for lot of migration/transfering task across Oracle
Applications.They are
1)FNDLOAD can be used to transfer Request Groups
2)FNDLOAD can be used for moving Concurrent Programs.
3)FNDLOAD can be used to download and upload forms personalizations.
4)FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields,
Responsibilities and
almost every other FND entity
Eg:
##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD
$FND_TOP/patch/115/import/afcpprog.lct
XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM
APPLICATION_SHORT_NAME="XXGMS"
CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"
2)what is SCAN with respect to RAC?(DBA interview)
Ans: Single Client Access Name (SCAN) is s a new Oracle Real Application
Clusters (RAC) 11g Release 2 feature that provides
a single name for clients to access Oracle Databases running in a cluster. The
benefit is that the client’s connect information does not need to change if you
add or remove nodes in the cluster. Having a single name to access the cluster
allows clients to use the EZConnect client and the simple JDBC thin URL to
access any database running in the cluster, independently of which server(s) in
the cluster the database is active. SCAN provides load balancing and failover
for client connections to the database. The SCAN works as a cluster alias for
databases in the cluster.
There are 2 options for defining the SCAN:
1. Define the SCAN in your corporate DNS (Domain Name Service)
2. Use the Grid Naming Service (GNS)
In case, a 2-node-cluster is used (for which 3 IPs are still recommended for
simplification reasons), one server in the cluster will host two sets of SCAN
resources under normal operations. If the node where a SCAN VIP is running
fails,
the SCAN VIP and its associated listener will failover to another node in the
cluster. If by means of such a failure the number of available servers in the
cluster becomes less than three, one server would again host two sets of SCAN
resources. If a node becomes available in the cluster again, the formerly
mentioned dispersion will take effect and
relocate one set accordingly.
Configuring SCAN:
#srvctl config scan_listener
#srvctl config scan
For Oracle Database 11g Release 2, SCAN is an essential part of the
configuration and therefore the
REMOTE_LISTENER parameter is set to the SCAN per default, assuming that the
database is created using standard Oracle tools (e.g. the formerly mentioned
DBCA).This allows the instances to register with the SCAN Listeners as remote
listeners to provide information on what services are being provided by the
instance, the current load, and a recommendation on how many incoming connections
should be directed to the instance.
In this context, the LOCAL_LISTENER parameter must be considered. The
LOCAL_LISTENER parameter should
be set to the node-VIP.
Eg:
local_listener=>(DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=133.
22.67.111)(PORT=1521))))
remote_listener=>sales1-scan.example.com:1521
Note: ThE example Oracle 11g Release 2 client using a default TNSNAMES.
ora will be as follows:
ORCLservice =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=sales1-scan.example.com)(PORT=1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =MYORCLservice)
))
3)How in R12 Jinitiator is placed?(APPS DBA interview)
Ans:
In App 11i, in context file we see s_sun_plugin_type set to jinit
Where as,In App R12 it is set to jdk
In Context.xml file we will find the line as given below in Apps R12
1.6.0_07
jdk
4)What are the background process in Dataguard physical standby setup and
Logical standby setup?(DBA interview)
Ans: In additional to Mandatory Oracle background processes,the background
processes playing key role in Dataguard physical standby and Logical Standby
Setup are:
1)ARC0 Archiver Process on Local Server
2)ARCH Archiver
3)ASYNC Asynchronous
4)DMON Data Guard Monitor
5)FAL Fetch Archive Log =>For Pysical Standby setup
6)LCR Logical Change Record =>For Logical Standby setup
7)LGWR Logwriter
8)LNS Network Server Process
9)LSP Logical Standby Process (SQL Apply for Logical DG)=>For Logical
Standby setup
10)MRP Managed Recovery Process (Redo Apply for Physical DG)=>For Pysical
Standby setup
11)RFS Remote File Server
12)SYNC Synchronous
5)Which background process is responsible for recovery processes in
physical standby database?(DBA interview)
Ans: MRP(Managed recovery process) is responsible for recovery process
in physical standby Database in a Dataguard Configuration.
6)which background process is responsible for recovery in logical standby
database?(DBA interview)
Ans:LSP(Logical standby process) is responsible for recovery in logical
standby database in a Dataguard configuration.
7)What we should do if when applying patch the adpatch utility fails and
Now you want to use adadmin/adpatch utility again? (Apps DBA)
Ans: Both AD utilities (adpatch/adadmin) access the same tables to store
the workers details, so both FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS
tables need to be dropped from the failed adpatch session ,so that
adadmin/adpatch session can run successfully next time.
Part-4:
1)Explain the installation and configuration of Oracle SOA?
What steps you followed for building Oracle SOA setup?
What were the major issues faced by you during Oracle SOA installation and configuration?
(Oracle Fusion DBA interview question)Ans: Check the below link for the installation and configuration process:
http://rafioracledba.blogspot.com/search/label/Oracle%20SOA%20tasks
When installing and configuring SOA.The Repository creation utility(RCU) and SOA version should be same.
Eg: If we are using Oracle RCU version 11.1.1.5.0 than we have to SOA version also 11.1.1.5.0.
I Installed the Oracle RCU version 11.1.1.5.0 and SOA version 11.1.1.2.0 so was facing the issue like SOA infra was not coming up so I upgraded the Oracle SOA version to 11.1.1.5.0 by using upgrade option,while using Oracle SOA version 11.1.1.5.0 than after upgradation,I started SOA managed server it worked.
For Starting the SOA(Service-Oriented Architecture) managed server:Set the SERVER_NAME and ADMIN_URL environment variables, then run the script with no arguments.
[oracle@nodefmw bin]$ pwd
/Middleware/SOASuite11gR1/wlserver_10.3/common/bin
Usage: startManagedWebLogic [SERVER_NAME] [ADMIN_URL]
for example:
startManagedWebLogic managedserver1 http://localhost:7001
./startManagedWegLogic nodefmw.apac.abc.com http:// nodefmw.apac.abc.com:7001
(OR)
#
./startManagedWeblogic.sh soa_server1 User:weblogic
Pass:welcome123
For Starting the BAM(Business Authentication Module) managed server:
#
./startManagedWeblogic.sh bam_server1User:weblogic
Pass:welcome123
2)Have you done DMZ setup?What are the steps followed in doing DMZ setup?
(Oracle Apps DBA interview question)Ans: Thanks Navdeep for explaing the things beautifully and clearly.For detail steps please
check the Navdeep's link.
Reference:
http://practicalappsdba.wordpress.com/2007/03/26/dmz-configuration-for-irecruitment/
For implementing DMS check the MY ORACLE SUPPORT note:
Oracle E-Business Suite R12 Configuration in a DMZ [ID 380490.1]
A DMZ (De-Militarized Zone) is a separate part of an organization’s network which is a shielded and
'cut off' from the main corporate network and its systems.The DMZ contains technical equipment to prevent access from external parties(say on the Internet) from gaining access to your main systems.
In the IT industry,a demilitarized zone is a single or multi-segment perimeter network that demarks the portion of the corporate network that lies between the intranet and outside networks.Corporate DMZ borders are enforced by firewalls and other dedicated networking devices.
When configuring Oracle E-Business Suite in a DMZ configuration,
firewalls are deployed at various levels to ensure that only the traffic that the architecture expects is allowed to cross the firewall boundaries.
The firewalls ensure that if intrusion attempts against machines in the DMZ are successful,the intrusion is contained within the DMZ and the machines in the intranet are not affected.
The steps followed in doing DMZ setup are as follows:To make Oracle E-Business Suite modules as secure as possible, the following tasks may need to be performed.
* Use of separate web node for external usage
* Setting of server level profile values
* Associate trust levels to application middle tier nodes
* Mark a subset of responsibilities as available on an external web node
* Deploy a Reverse proxy in front of the external web node
* Configuring a URL firewall and mod security in the reverse proxy
* Run only the required Oracle E-Business Suite Application services on the external web tier
3)What are the background processes in Oracle ASM explain?(oracle DBA interview question)Ans: The following background processes are an integral part of Automatic Storage Management:
1)ARBn(Actual Rebalance) * ARBn(Actual Rebalance) performs the actual rebalance data extent movements in an Automatic Storage Management instance.
* There can be many of these processes running at a time, named ARB0, ARB1, and so on.
2)ASMB(ASM Balance) * ASMB(ASM Balance) runs in a database instance that is using an ASM disk group.
* ASMB communicates with the ASM instance, managing storage and providing statistics.
* ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or
* when the database instance first starts if the SPFILE is stored in ASM.
3)GMON(Global monitor) * GMON(Global monitor) maintains disk membership in ASM disk groups.
4)MARK(Marking) * MARK marks ASM allocation units as stale following a missed write to an offline disk.
* This essentially tracks which extents require resync for offline disks and
5)RBAL(Rebalance) * RBAL runs in both database and ASM instances.
* In the database instance,it does a global open of ASM disks.
* In an ASM instance, it also coordinates rebalance activity for disk groups.
4)What are the parameters specific to Oracle ASM explain?(oracle DBA interview question)Ans: The following parameters for specific to ASM instance They are:
1)ASM_DISKGROUPS
2)ASM_DISKSTRING
3)ASM_POWER_LIMIT
4)ASM_PREFERRED_READ_FAILURE_GROUPS
5)INSTANCE_TYPE
Ofcourse,there are other parameters also like:
DB_CACHE_SIZE,DIAGNOSTIC_DEST,LARGE_POOL_SIZE,PROCESSES,REMOTE_LOGIN_PASSWORDFILE,SHARED_POOL_SIZE
Let us briefly discuss them,
1)ASM_DISKGROUPS:The ASM_DISKGROUPS initialization parameter specifies a list of the names of disk groups that an ASM instance mounts at startup. Oracle ignores the value that you set for ASM_DISKGROUPS when you specify the NOMOUNT option at startup or when you issue the ALTER DISKGROUP ALL MOUNT statement. The default value of the ASM_DISKGROUPS parameter is a NULL string. If the parameter value is NULL or is not specified, then ASM does not mount any disk groups.
The ASM_DISKGROUPS parameter is dynamic. If you are using a server parameter file (SPFILE), then you should not need to manually alter the value of ASM_DISKGROUPS. ASM automatically adds a disk group to this parameter when the disk group is successfully created or mounted. ASM also automatically removes a disk group from this parameter when the disk group is dropped or dismounted. The following is an example of setting the ASM_DISKGROUPS parameter dynamically:
SQL> ALTER SYSTEM SET ASM_DISKGROUPS = 'CONTROLFILE, DATAFILE, LOGFILE, STANDBY'
2)ASM_DISKSTRING:The ASM_DISKSTRING initialization parameter specifies a comma-delimited list of strings that limits the set of disks that an ASM instance discovers. The discovery strings can include wildcard characters. Only disks that match one of the strings are discovered. The same disk cannot be discovered twice.
The discovery string format depends on the ASM library and the operating system that are in use. Pattern matching is supported; refer to your operating system-specific installation guide for information about the default pattern matching. For example, on a Linux server that does not use ASMLIB, to limit the discovery process to only include disks that are in the /dev/rdsk/ directory, set ASM_DISKSTRING to:
/dev/rdsk/*
The asterisk is required. To limit the discovery process to only include disks that have a name that ends in disk3 or disk4, set ASM_DISKSTRING to:
/dev/rdsk/*disk3, /dev/rdsk/*disk4
3)ASM_POWER_LIMIT:The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing.
The default value is 1 and the range of allowable values is 0 to 11 inclusive. A value of 0 disables rebalancing.
Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead.
4)ASM_PREFERRED_READ_FAILURE_GROUPS:The ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter value is a comma-delimited list of strings that specifies the failure groups that should be preferentially read by the given instance. This parameter is generally used only for clustered ASM instances and its value can be different on different nodes. For example:
diskgroup_name1.failure_group_name1, ...
The ASM_PREFERRED_READ_FAILURE_GROUPS parameter setting is instance specific.
This parameter is only valid for clustered ASM instances and the default value is NULL.
Note:
The ASM_PREFERRED_READ_FAILURE_GROUPS parameter is valid only in Oracle RAC environments.
5)INSTANCE_TYPE:The INSTANCE_TYPE initialization parameter must be set to ASM for an ASM instance.
This is a required parameter and cannot be modified. The following is an example of the INSTANCE_TYPE parameter
in the initialization file:
INSTANCE_TYPE=ASM
6)DB_CACHE_SIZE
You do not need to set a value for the DB_CACHE_SIZE initialization parameter if you use automatic memory management.
The setting for the DB_CACHE_SIZE parameter determines the size of the buffer cache.
This buffer cache is used to store metadata blocks. The default value for this parameter is suitable for most environments.
7)DIAGNOSTIC_DEST
The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located.
The value for an ASM instance is of the form:
diagnostic_dest/diag/asm/db_name/instance_name
For an ASM instance, db_name defaults to +asm.
8)LARGE_POOL_SIZE
You do not need to set a value for the LARGE_POOL_SIZE initialization parameter if you use automatic memory management.
The setting for the LARGE_POOL_SIZE parameter is used for large allocations.
The default value for this parameter is suitable for most environments.
5)What all the possible ways to convert/copy/transfer non-asm filesystem to asm or viceversa?What all the conversions possible in asm?(oracle DBA interview question)Ans: In 10g the possible ways to migrate - DBMS_FILE_TRANSFER, rman (copy vs.
backup), or XMLDB FTP
In 11g, we introduced the asmcmd copy command. Key point here is that copy files
out is great for:
1. archive logs
2. Controlfiles
3. Datafiles for debugging
4. Dumpsets (can be done across platforms)
Copying files in:
TTS
Copy in only supported files.
28
ASMCMD Copy
ASMCMD> ls
+fra/dumpsets/expdp_5_5.dat
ASMCMD> cp expdp_5_5.dat sys@rac1.orcl1:+DATA/dumpsets/ex
pdp_5_5.dat
source +fra/dumpsets/expdp_5_5.dat
target +DATA/dumpsets/expdp_5_5.dat
copying file(s)...
file, +DATA/dumpsets/expdp_5_5.dat,
copy committed.
6)What is ACFS?what are the advantages of ACFS?(oracle DBA interview question)Ans: The new feature added in Oracle11g release2 for ASM is 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 is 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)what is interconnect(Cache fusion) in Oracle rac?(oracle DBA interview question)Ans: Starting with Oracle 9i, RAC can transfer blocks from one instance to another this can be called as
interconnect (cache fusion).
8)Have you done workflow configuration?(oracle Apps DBA interview question)Ans: Best link with explaination I found:
http://onlineappsdba.com/index.php/2008/08/01/workflow-notification-mailer-configuration-using-autoconfig-for-outbound-processing/
9)Explain the importance of autoconfig file?(oracle Apps DBA interview question)Ans: AutoConfig uses a file called context file, which is used to configure changes.Context file is a XML file having all the parameters of application system.
These parameters are part of some configuration file for some services or it may be part of some environment file.When we create an application system (using Rapid install),context file gets created automatically will all the parameters
and there values. Most of the parameters take default value,where as some parameters takes the value that is supplied
at the time of installation.
All the information required to configure Oracle Apps 11i/R12 are stored in files called as Context file.So there are two context file, one for Apps Tier(Middle Tier) and one for DB Tier.
Oracle apps 11i Context file location is as follows:Apps Tier:$APPL_TOP/admin/$TWO_TASK.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK.xml
Oracle apps R12 Context file location is as follows:Apps Tier:$INST_TOP/appl/admin/$TWO_TASK_
.xml
DB Tier:$ORACLE_HOME/appsutil/$TWO_TASK_.xml
Where: TWO_TASK is the SID set for EBS Database.
More details about autoconfig can be extracted from the below mentioned link with nice explaination:
http://onlineappsdba.com/index.php/2008/01/28/autoconfig-in-oracle-apps-11i-r12-12i/
Context file is repository for configuration stored in xml format.
Its xml file and file name format is _.xml in Oracle Apps 11i
10)What all the techniques to improve oracle application patching mechanism?
(oracle Apps DBA interview question)
Ans:
To improve the oracle application patching mechanism in terms of performance and other criteria we can apply
patches as given below:
1)Merge Patches using admrgpch
admrgpch utility is used to merge two or more patches in oracle applications.
The advantage of merging patches is that it reduces downtime and thus very much useful.
Note 1: The syntax for using admrgpch utility is as given below:
admrgpch -s -d -merge_name
For eg:
admrgpch -s -d -merge_name
We have to make sure the the merge path log file "admrgpch.log" does not contain any error,once the two patches
are merged.
Note 2:'admrgpch' utility will not merge patches of different Oracle applications releases and operating systems platforms and with different parallel modes and we cannot use 'admrgpch' utility to merge AD and Non-AD patches.
2)To improve the performance while applying patch using 'adpatch'(auto patch utility) has lot of options like:
options=nocopyportion noautoconfig nogenerateportion hotpatch ... We can use as per our convenience.
Eg:
$adpatch options=noautoconfig
$
3)If you cannot afford downtime than you can apply patch using options=hotpatch,very much useful for production environment here it is not required to keep the application in maintainance mode.
Eg:
$adpatch options=hotpatch
11)How we register new products in Oracle applications?(oracle Apps DBA interview question)
Ans:'adsplice' is an ad utility which is used to add off-cycle/new product to oracle apps.
i.e. products released between maintenance packs.
Download the product patch which contains information about the new product,'adsplice' control file and other associated files. Go through the readme and apply pre-reqs if any.Now for using 'adsplice' there is a control file newprods.txt. A typical newprods.txt looks like as given below:
product=zsa
base_product_top=*APPL_TOP*
oracle_schema=zsa
sizing_factor=100
main_tspace=ZSAD
index_tspace=ZSAX
temp_tspace=TEMP
default_tspace=ZSAD
If we are using OFA tablespace model then we need to change the last 4 parameters else for OATM(Oracle Application tablespace Model) it is optional.
We have to copy the new-product in $APPL_TOP/admin location and run 'adsplice' utility from 'applmgr' OS user and follow the prompts.Then we can verify that product is correctly spliced or not using adutconf.sql (AD Configuration report).
We need to logout and login again so that the new environment is set.
We can then verify that new _TOP environment variable is set and need to apply the patches for newly added product.
12)What is SHARED APPL_TOP?What are the major advantages and disadvantages of SHARED APPL_TOP?
(oracle Apps DBA interview question)
Ans: Basically Shared APPL_TOP's means sharing APPL_TOP and
COMMON_TOP across the Application Servers.
Benefits:
1)Administration tasks only need to be performed on any one node.
2)Application patches only need to be applied to any one node in the system.
3)Allows the use of "Distributed AD" to reduce downtime (check MetaLink 236469.1).
4)Reduces overall disk space requirements.
5)Makes it easier to add additional nodes to our Applications system.
6)Create single node clones from a shared APPL_TOP multi node system and
7)Merge APPL_TOP(Merge TOPs) from different application tiers into a single application tier APPL_TOP.
Disadvantages:
1)Single point of failure.
2)Performance might be an issue,if shared APPL_TOP's filesystem will be a 'nfs'(network file system) mount.
13)Explain the architecture of Oracle Golden Gate?(Oracle DBA interview question)
Ans: The Oracle GoldenGate software architecture is comprised of three primary components:
1)Capture,
2)Trail Files and
3)Delivery.
As shown in the figure,
This modular approach allows each component to perform its tasks independently of the others,
accelerating data replication and ensuring data integrity.
1)Capture:
Oracle GoldenGate’s Capture module resides on the source database and looks for new transactional activity.The Capture module reads the result of insert, update, and delete operations by directly accessing the database transaction (redo) logs, and then immediately captures new and changed data for distribution.
The Capture module only moves committed transactions—filtering out intermediate activities and rolled-back operations—which not only reduces infrastructure load but also eliminates potential data inconsistencies.Further optimization is achieved through transaction grouping and optional compression features.
2)Trail Files:
Oracle GoldenGate’s Trail Files contain the database operations for the changed data in a transportable,platform-independent data format.
Trail Files are a critical component within Oracle GoldenGate’s optimized queuing mechanism.
They reside on the source and/or target server but exist outside of the database to ensure heterogeneity,improved reliability, and minimal data loss.
This architecture minimizes impact to the source system because no additional tables or queries to the database are required to support the data capture process. The Capture module reads once, and then immediately moves the captured data to the external Trail File for delivery to the target(s).
In the event of an outage at the source and/or target, the Trail Files contain the most-recent
changed data up to the point of the outage, and the data is applied once the systems are back online again.
3)Delivery:
Oracle GoldenGate’s Delivery module takes the changed data from the latest Trail File and applies it to the target database using native SQL for the appropriate relational database management system.Delivery can be made to any open database connectivity–compliant database.
The Delivery module applies each transaction in the same order as it was committed and within the same transactional context as at the source, enabling consistency and referential integrity at the target.
To enhance IT flexibility, captured data can also be delivered to a Java Message Service destination or as a flat file using Oracle GoldenGate Application Adapters.
14)What is difference between Socket & Servlet Mode in Apps Forms?(oracle Apps DBA interview question)
Ans: When forms run SOCKET Mode these are dedicated connection between Client Machine & Form Server (Started by adfrmctl.sh).When Forms run in servlet mode the forms requests are fulfilled by Jserv in Apache .
There will be additional JVM for Forms Request in that case and we won't start form via adfrmctl.sh.
15)How to compile Forms in 11i and R12?(oracle Apps DBA interview question)
Ans: We can Compile Forms in 11i using 'f60gen' utility as follows:
$f60gen module=.fmb userid=apps/ output_file=$XX_TOP/forms/US/.fmx
Where as,In R12 we can Compile Forms using 'frmcmp_batch' utility as follows:
$frmcmp_batch .fmb userid=apps/ output_file=$XX_TOP/12.0.0/form/US/.fmx module_type=form compile_all=special
16)If a patch fail how to proceed in Oracle Application?
Ans: I like the below link for this:
http://oracleappsdba1.blogspot.com/2009/02/oracle-apps-patch-fails.html
17)Describe the steps involved in the upgradation from Oracle 11i to R12 Appplications?
(oracle Apps DBA interview question)
Ans: Atul again described these upgradation steps beautifully in the below mentioned link:
http://onlineappsdba.com/index.php/2008/07/09/upgrade-oracle-apps-11i-to-r1212i-1204-key-points/
http://onlineappsdba.com/index.php/2007/10/13/upgrade-oracle-applications-to-release-1202/
http://onlineappsdba.com/index.php/2008/03/26/upgrade-oracle-applications-to-1204/
We usually prepare the below documents for doing upgrade of Oracle Applications:
1)xls document for preparing a plan and to record the steps of upgrade.
2)Word document for the actual implemented steps in details the steps.
Part-5:
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:
Part-6:
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
Part-7:
1) Tell me about yourself?
Ans: Looks to be a simple question as there is no thinking require.But,This is the question which may decides your selection sometimes.Here You have speak about your experience and why you are the best person for this particular position and what you have done in past,what you are doing now and what you will be
doing in future for your growth,Team growth and Organization growth.
In brief about myself: This is Rafi here.I'm working as Oracle DBA and Apps DBAs since 4 yrs. I've been involved in Handling Database and Applications.As a part of DBA and Apps DBA team I have done Oracle RAC implentation on Linux and Solaris platform using ASM,OCFS and RAW DEVICE Storage mechanism,I've involved in migration of Database and Upgradation from Oracle 9i to Oracle 11g rel2 and Upgradation of Oracle Applications from Oracle 11i to Oracle R12 .I have worked on 4 Unix based platformsi.e Linux,Solaris,HP-UX and IBM-AIX including windows server. I've handled Oracle Database 9i,10g & 11g and I've Handled Oracle Applications 11i & R12 Administration.I've been involved in Clonning & Patching of Oracle Applications.
I've completed Oracle 9i OCP,10g OCP,11g OCP,RAC Expert,Oracle E-businness Suite R12 OCP and ITIL v3 foundation Certification.
Right now I'm focussing on Oracle SOA,Oracle Data Integrator,Oracle JDeveloper,Weblogic and Fusion middleware components.
Note:Please take about your skillsets and experience while answering the above question so that interview can ask questions
sometime based on your experience and skillsets and not necessary what skillset he wants.
2)How you stop Your RAC Database configue with ASM?
Ans: This is a bit tricky.But a simple logic is Database depends on storage and storage depends on node.Hence We can tell like this,Important is to remember that node application and cluster is started and stopped only with 'root'
user or the user having sudo privilege to do this in /etc/sudoers file.One more point to remember always asm instance
should be started first.
There are actually 2 commands to do it:
1) crsctl stop crs: This command will stop all the node applications(i.e gsd, VIP, ons, listener) and crs daemons(crsd,evmd,ocsd)
However /etc/init.d/init.crs {start|stop|enable|disable} can be used to start,stop,enable and disable these crs daemons
or services.
Login as root or a user with sudo permission to run the crsctl command:
#cd ORA_CRS_HOME/bin
#crsctl start crs
=> Run as root or you should have SUDO permissing in /etc/sudoers file.
#ssh testnode_1n1 /home/app/crs/bin/crsctl check crs
(or)
#ssh testnode_1n1 /home/app/crs/bin/crsctl stop crs
Note:This command will prevent CRS from starting on a reboot.There is no return output from the command.
[root@TEST_NODE1 oracle]# crsctl disable crs
For checking this use 'crsstat' command:
[root@TEST_NODE1 oracle]# crsstat
HA Resource Target State
----------- ------ -----
ora.TEST_NODE1.ASM1.asm OFFLINE OFFLINE
ora.TEST_NODE1.LISTENER_TEST_NODE1.lsnr OFFLINE OFFLINE
ora.TEST_NODE1.gsd OFFLINE OFFLINE
ora.TEST_NODE1.ons OFFLINE OFFLINE
ora.TEST_NODE1.vip OFFLINE OFFLINE
ora.orcl.RAC.cs OFFLINE OFFLINE
ora.orcl.RAC.orcl1.srv OFFLINE OFFLINE
ora.orcl.RAC.orcl2.srv OFFLINE OFFLINE
ora.orcl.db OFFLINE OFFLINE
ora.orcl.orcl1.inst OFFLINE OFFLINE
ora.orcl.orcl2.inst OFFLINE OFFLINE
ora.TEST_NODE2.ASM2.asm OFFLINE OFFLINE
ora.TEST_NODE2.LISTENER_TEST_NODE2.lsnr OFFLINE OFFLINE
ora.TEST_NODE2.gsd OFFLINE OFFLINE
ora.TEST_NODE2.ons OFFLINE OFFLINE
ora.TEST_NODE2.vip OFFLINE OFFLINE
Let’s not forget to enable CRS on reboot:
[root@TEST_NODE1 oracle]# crsctl enable crs
2)srvctl stop:
--------------
To stop a RAC Database configure system with ASM step by step:
--------------------------------------------------------------------------
srvctl stop service -d test =>To stop services like TAF(Transparent Application failover
srvctl stop database -d test =>To stop Database which may include 'n' number of instances
srvctl stop asm -n node1-pub1 =>To stop asm instance on node1
srvctl stop asm -n node2-pub2 =>To stop asm instance on node2
srvctl stop nodeapps -n node1-pub1,node2-pub2 =>To stop all node apps(listeners,GSD,ONS,VIP)
(or)
To start a RAC Database configure with ASM:
---------------------------------------------------------------------------
We have to first start the nodeapps,then the ASM instances, followed by the database instances, and lastly the services for TAF and load balancing.
srvctl start nodeapps -n node1-pub
srvctl start nodeapps -n node2-pub
srvctl start asm -n node1-pub
srvctl start asm -n node2-pub
srvctl start database -d test
Note 1: When we use crsctl to stop clusterware,It will stop nodeapps,asm instances and Database instance.
Note 2:Starting and Stopping of Cluster in Oracle 11g is changed because of 1)Separate home for clusterware
i.e Grid home.
2) OCR is in diskgroup inside ASM, then no way to stop ASM using srvctl, you have to shutdown the cluster to stop ASM.
We can start or start cluster or other services as follows:
Starting / Stopping the Cluster in Oracle 11g rel2:
--------------------------------------------------------------------
Stopping Cluster:
-----------------------
We can Use the "crsctl stop cluster" command on node1 to stop the Oracle Clusterware stack:
root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster
Starting Cluster:
---------------------------------
[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster
We can start/stop all the clusterware nodes by using below command:
[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl start cluster all
[root@node1 ~]# /u01/app/11.2.0/grid/bin/crsctl stop cluster all
3)How you check the health of Your RAC Database?
Ans: 'crsctl' command from root or oracle user can be used to check the clusterware health But for starting or stopping we have to use root user or any privilege user.
[oracle@TEST_NODE1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
4)How you check the services in RAC Node?
Ans: We can check the service or start the services with 'srvctl' command.load balanced/TAF service named RAC online.
[oracle@TEST_NODE1 ~]$ srvctl start service -d orcl -s RAC
[oracle@TEST_NODE1 ~]$ crsstat
HA Resource Target State
----------- ------ -----
ora.TEST_NODE1.ASM1.asm ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.LISTENER_TEST_NODE1.lsnr ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.gsd ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.ons ONLINE ONLINE on TEST_NODE1
ora.TEST_NODE1.vip ONLINE ONLINE on TEST_NODE1
ora.orcl.RAC.cs ONLINE ONLINE on TEST_NODE2
ora.orcl.RAC.orcl1.srv ONLINE ONLINE on TEST_NODE1
ora.orcl.RAC.orcl2.srv ONLINE ONLINE on TEST_NODE2
ora.orcl.db ONLINE ONLINE on TEST_NODE2
ora.orcl.orcl1.inst ONLINE ONLINE on TEST_NODE1
ora.orcl.orcl2.inst ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.ASM2.asm ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.LISTENER_TEST_NODE2.lsnr ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.gsd ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.ons ONLINE ONLINE on TEST_NODE2
ora.TEST_NODE2.vip ONLINE ONLINE on TEST_NODE2
[oracle@TEST_NODE1 ~]$
4)If there is some issue with virtual IP how will you troubleshoot it?How will you change virtual ip?
Ans: To change the VIP (virtual IP) on a RAC node, use the command
[oracle@testnode oracle]$ srvctl modify nodeapps -A new_address
5)What kind of backup stratergy you follow for your Databases?
Ans:We follow different backup strategy for our Databases depends on type of Database.We use different kind of Backup stratergy for Production,Test,Performance,Demo,Development Databases.But the main
aim is to recover the Database with minimal or no Data loss:
1)Production Databases:
-----------------------------
Backup stratergy for Production Database is as follows:
RMAN BACKUP:
---------------------
incremental level 0 =>Weekly Basis at 6am -- Full backup of Database with archive logs and copy of Current control file
incremental level 1 =>Mon,Tues,Thurs,Friday at 6am --Changes from recent back to a particular day
cummulative backup =>Wed,Saturday at 6am -- changes from the lowest level i.e mon-wed and Thur-Sat.
While deciding the backup stratergy for our production system of 300GB we had in our mind the following points:
1)Backup should be schedule at less peak hours.
2)With no loss we should recover Database in case of any Disaster.
For scripts and scheduling details refer my below link:
http://rafioracledba.blogspot.com/search/label/RMAN
expdp Backup:
----------------
Export Datapump backup on daily basis at 9pm.
We should have one datapump backup which should be most recent to recover the lost of Table or any Data.Below points are same:
1)Backup should be schedule at less peak hours.
2)With no loss we should recover Database in case of any Disaster.
For scripts and scheduling details refer my below link:
http://rafioracledba.blogspot.com/search/label/expdp%2Fimpdp
2)Test Databases:
---------------------
Usually Test Database is almost same as production in terms of Data.However whenever we want to test some
patch or any script before applying to production we can apply in test and than apply in production.I usually
prefer to have same backup stratergy as production for Test Databases.
3)Development Database:
---------------------------
In a development Database.We can go for below backup statergy,However if you have space and enough infrastructure you can repeat the same backup stratergy as above.
Expdp full backup:
In a Development environment,We should have full Database logical
Backup up to date and should be schedule on daily basis,so that whenever there
Is some table drop or table backup is requested by developer you can restore
That table from your Logical backup.
COLD RMAN BACKUP:
-------------------
We can schedule cold rman backup on every sundays at 9am(any time which you feel is convinient without affecting much to developers and end users).Below is the link for implementing RMAN cold Backup:
http://rafioracledba.blogspot.com/2011/06/scheduling-rman-cold-backup-and-copy.html
6)How you will backup your RAC Database?
Ans: Backup strategy of RAC Database:
An RAC Database consists of
1)OCR
2)Voting disk &
3)Database files,controlfiles,redolog files & Archive log files
1)OCR BACKUP:
-------------
Oracle performs physical backup of OCR devices every 4 hours under the default backup direcory $ORA_CRS_HOME/cdata/
and then it rolls that forward to Daily, weekly and monthly backup. You can get the backup information by executing below command.
Login with help of 'root' user and fire the below command.
ocrconfig -showbackup
[root@testnode -pub ~]# ocrconfig -showbackup
testnode-pub 2011/05/03 17:50:47 /u01/app/crs/cdata/test-crs/backup00.ocr
testnode-pub 2011/05/03 13:46:45 /u01/app/crs/cdata/test-crs/backup01.ocr
Manually backing up the OCR we can do with the help of same command 'ocrconfig
ocrconfig -manualbackup -->Physical Backup of OCR
The above command backs up OCR under the default Backup directory.We can export the contents of the OCR using below command (Logical backup).
ocrconfig -export /tmp/ocr_exp.dat -s online <<-- data-blogger-escaped-backup="" data-blogger-escaped-br="" data-blogger-escaped-logical="" data-blogger-escaped-ocr="" data-blogger-escaped-of="">
2)Voting Disk Backup:
----------------------------
Backup the existing votedisks as below as oracle:
dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
41024+0 records in
41024+0 records out
[oracle@node1-pub ~]$
Note: If we want to add a voting disk we can do with the help of 'crsctl' as given below:
[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ touch /u02/ocfs2/vote/VDFile_3
[oracle@node1-pub ~]$ crsctl add css votedisk /u02/ocfs2/vote/VDFile_3
Now formatting voting disk: /u02/ocfs2/vote/VDFile_3.
Successful addition of voting disk /u02/ocfs2/vote/VDFile_3.
Note:Please ask the interview about which version ,this is because,In Oracle 11g rel2,The way we take backup of Voting Disk and OCR is totally different,Because now we can use ASM for storing OCR and Voting Disk.Another point to keep in mind is Voting File is no longer supported to take a manual backup of it with 'dd' command
Instead,the Voting File gets backed up automatically into the OCR. As a New Feature,we can now do a manual backup of the OCR any time you like, without having to wait until that is done automatically – which is also still done.
Now the manual backup:
[root@node1-pub ~]# /u01/app/11.2.0/grid/bin/ocrconfig -manualbackup
We can take backup on a separate shared location also:
[root@node1-pub ~]#/u01/app/11.2.0/grid/bin/ocrconfig -backuploc /u02/ocr_backup
To see the above backup we can use the same command 'ocrconfig':
[root@node1-pub ~]# /u01/app/11.2.0/grid/bin/ocrconfig -showbackup
Note:Oracle’s solution to this problem is to combines the clusterware and ASM into a single Grid Infrastructure home and comes up a procedure with a complex start up sequence which mixes the different components of clusterware and ASM instance in order
3)Datafiles Backup:
--------------------
For Backing up your Database files,Redolog files,control files,Archive log files and other Database files,Please use 'rman' as it has many advantages over other
user managed backup.RMAN gives you the flexibility of backing up Database in archive log mode(hot backup)
and unarchive log mode(Cold backup).
http://forums.oracle.com/forums/thread.jspa?messageID=9624568#9624568
7)How to recover your RAC Database?
Ans: If you have valid backup of OCR,Voting Disk and Database files than RAC Database recovery can be done without much lost or Damages.Below are some steps to do these:
1)OCR can be restore and recover using 'ocrconfig' command if you have a valid export backup of ocr.
2)Voting disk can be restore and recovered using 'dd' command if you have got the backup which you took from 'dd' command.
Detail process we can get from below link:
http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_65.shtml#Recover the Voting Disk
3)Restore and Recover the Database
First, take the database out of cluster mode,This is very important step, via:
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:02:17 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Then restore the database via RMAN:
# rman target=/
Recovery Manager: Release 10.2.0.3.0 - Production on Sat May 24 17:04:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 251658508 bytes
Database Buffers 8388608 bytes
Redo Buffers 7127040 bytes
RMAN> restore database;
Starting restore at 24-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oracle/ora1/TESTDB/systTESTDB01.dbf
restoring datafile 00002 to /u02/oracle/ora1/TESTDB/undotbs01.dbf
restoring datafile 00003 to /u02/oracle/ora1/TESTDB/sysaux01.dbf
restoring datafile 00004 to /u02/oracle/ora1/TESTDB/users01.dbf
restoring datafile 00005 to /u02/oracle/ora1/TESTDB/example01.dbf
restoring datafile 00006 to /u02/oracle/ora1/TESTDB/undotbs02.dbf
restoring datafile 00007 to /u02/oracle/ora2/TESTDB/mgmt.dbf
restoring datafile 00008 to /u02/oracle/ora1/TESTDB/mgmt_ecm_depot1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oracle/ora3/TESTDB/backups/0ijh6j4t_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oracle/ora3/TESTDB/backups/0ijh6j4t_1_1 tag=FHB_EM1200805241630
channel ORA_DISK_1: restore complete, elapsed time: 00:06:36
Finished restore at 24-MAY-08
Since my database is so small, I will simply restore the entire thing, however, since you know which datafile is missing, you could simply restore it and then recover the database as necessary.
Recover the Database
RMAN> recover database;
Starting recover at 24-MAY-08
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-MAY-08
RMAN> alter database open;
database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Afterwards, place the database back into cluster mode and startup both instances:
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sat May 24 17:16:36 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
# srvctl start database -d TESTDB
[oracle@rac1 bdump]$ srvctl status database -d TESTDB
Instance TESTDB1 is running on node rac1
Instance TESTDB2 is running on node rac2
8)What kind of backup strategy you are following for application server?
Ans;Complete Oracle Application Server Environment Backup can be done using the below techniques,
A complete Oracle Application Server environment backup includes:
* A full backup of all files in the middle-tier Oracle homes (this includes Oracle software files and configuration files)
* A full backup of all files in the Infrastructure Oracle home (this includes Oracle software files and configuration files)
* A complete cold backup of the Metadata Repository
* A full backup of the Oracle system files on each host in your environment.
OracleAS Backup and Recovery Tool can be used for taking Oracle Application server backup.For more details and configuration of OracleAS Backup and Recovery tool refer the below link:
http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/br_tool.htm
9)How your Add node to your RAC Database?
Ans: To add a new node(server) to your RAC Database use the command 'srvctl' with the option given below.
[oracle@testnode oracle]$ srvctl add nodeapps -n newserver_name -o $ORACLE_HOME -A 149.181.220.1/255.255.255.0/eth1
Note: The -A flag precedes an address specification.
Howerver there is one more method:
Run the addNode.sh script
On an existing node from the Oracle_home/oui/bin directory,run the addNode.sh script
Note:For above question if it is asked in detail than there are lot of steps,Which is
explained in Q11.
10)For a Database created with ASM on RAC How you would add one more asm configuration?
Ans: We can use DBCA in Silent Mode to Add ASM and Database Instances to Target Nodes
We can use the DBCA in silent mode to add instances to nodes on which you have extended an Oracle Clusterware home and an Oracle Database home. Use the following syntax where password is the password as given below:
$dbca -silent -addInstance -nodeList node -gdbName gdbname [-instanceName instname]
-sysDBAUserName sysdba -sysDBAPassword password
Note:We can use Oracle Enterprise Manager grid control also to do the same task.
11)How you add node for a RAC cluster?Step by step?
Ans:Below are the v steps for adding non-rac node to an RAC Database:
I)Prerequisite Steps for Extending Oracle RAC to Target Nodes:
The following steps describe how to set up target nodes to be part of your cluster:
Step 1, "Make physical connections"
Step 2, "Install the operating system"
Step 3, "Create Oracle users"
Step 4, "Verify the installation" =>use cluvfy ustility for verification of clusterware installation
Eg: cluvfy stage -post hwos -n node_list|all [-verbose]
II)Extend Oracle Clusterware to Target Nodes
In the above step you have to stop clusterware services with 'crsctl' and create a clone environment by copying file and making identical copy of clusterware hope.
III)Configure Shared Storage on Target Nodes
Depending on the environment existing whether it is having asm,ocfs2,raw or any vendor shared storage make the environment same as the source.If the ASM HOME and ORACLE RAC database HOME exists in oracle than you don't need to do any thing as ASM home to a node will happen implicitly if it is not so the case,you must first extend the Oracle Clusterware home (CRS_home),ASM home, and then the Oracle home (in that order), in order to add the new node to the cluster.
IV)Add the Oracle Real Application Clusters Database Homes to Target Nodes
We can add the Oracle RAC database home to target nodes using either of the following methods:
1)Extending the Database Home to Target Nodes Using Oracle Universal Installer in Interactive Mode
(OR)
2)Extending the Database Home to Target Nodes Using Oracle Universal Installer in Silent Mode
Let us see the 2nd method which doesn't involve user interaction:
We can optionally run addNode.sh in silent mode,
replacing steps 1 through 6, as follows where nodeI, nodeI+1,
and so on are the target nodes to which you are adding the Oracle RAC database home.
*Ensure that you have successfully installed the Oracle Database with the Oracle RAC software on at least one node
in your cluster environment.
*Ensure that the $ORACLE_HOME environment variable identifies the successfully installed Oracle home.
Go to Oracle_home/oui/bin and run the addNode.sh script.
In the following example, nodeI, nodeI+1 (and so on) are the nodes that you are adding:
addNode.sh -silent "CLUSTER_NEW_NODES={nodeI, nodeI+1, … nodeI+n}"
You can also specify the variable=value entries in a response file, known as filename, and you can run the addNode script as follows:
addNode.sh -silent -responseFile filename
Command-line values always override response file values.
v)Add ASM and Oracle RAC Database Instances to Target Nodes
We can add ASM and RAC Database Instances with the help of DBCA.
After you terminate your DBCA session, run the following command to verify the administrative privileges on the target node and obtain detailed information about these privileges where nodelist consists of the target nodes:
cluvfy comp admprv -o db_config -d oracle_home -n nodelist [-verbose]
Above the steps in brief so that we can crack the interview.The actual steps might be in detailed which we have to plan and do to avoid issues.
12)Have you done upgrade/migration from Oracle 11i Application to Oracle R12 Applications and What are the steps followed in brief?
Ans: Upgradation/migration tasks from Oracle 11i to R12 can be divided into 3 steps in broad:
1: Prerequisites: Must be completed prior to using the migration utility.
2: Migration Tasks : Tasks to migrate an application tier to a new platform.
3: Finishing Tasks: Tasks that need to be performed after the migration.
Application Tier Platform Migration with Oracle E-Business Suite Release 12 [ID 438086.1] explain these steps in best way.Other
metalink notes from My oracle support which can be really helpful in performing upgradation/migration are:
Export/import process for R12 using 11gR1 or 11gR2 [ID 741818.1]
Using Transportable Database to migrate E-Business Suite R12 Using Oracle Database 10gR2 or 11g [ID 734763.1]
Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]
13)What are the difference between Oracle 12.0.4 and 12.1.1 applications?
Ans: The metalink note 566521.1 Oracle Application Object Library Release Notes, Release 12.1.1 explain the difference
in the best way.Link is given below:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=README&id=566521.1
14)What is the difference in java for Oracle 11i and Oracle R12 Application?
Ans: There are 3 key technical differences when we compare Oracle 11i and R12,They are:
1) Jserv in apps 11i(mod_jserv) is replaced by OC4J (mod_oc4j) in apps R12.
2) JDBC version is changed from version 9 in apps 11i to version 10.2.0 in Apps R12 and
3) Java processes use JDK version 1.3.1 or 1.4.2 in Apps 11i,where as R12 uses JDK/JRE version 1.5.0.
15)What are the difference between Oracle 11i and R12?
Ans:
Database:
Database Version in 11i (11.5.9 & 11.5.10) was 9i Rel 2 where as in Release 12 its 10g R2 (10.2.0.2)
Application Tier:
Tech Stack in Application Tier consist of iAS(1.0.2.2.2) & Developer 6i (Forms & Reports 8.0.6) but in Applications R12 it is build on Fusion Middleware (10g Web Server and 10g Forms & Reports)
Sub component in Application Tier
A) HTTP Server or Web Server in R12 is Version 10.1.3 which is built on Apache version 1.3.34. In apps 11i it is Version 1.0.2.2.2 built on Apache Version 1.3.19
B) Jserv in apps 11i is replaced by OC4J (mod_jserv is replaced by mod_oc4j)
C) Forms Version 6i in Apps 11i is replaced by Forms 10.1.2.0.2 in R12
D) Reports Version 6i in Apps 11i is replaced by Reports 10.1.2.0.2 in R12
E) JDBC version is changed from version 9 in apps 11i to version 10.2.0 in Apps R12
F) modplsql or mod_pls is removed from Apps R12 (What will happen to my mod plsql applications- coming soon* )
G) Java processes use JDK/JRE version 1.5.0 in R12 against JDK version 1.3.1 or 1.4.2 in Apps 11i
H) For various environment variable changes check below picture
I) New top INSTANCE_TOP added in Release 12 for configuration and log files
16)What is the major use of INST_TOP?
Ans:New TOP $INST_TOP(INSTANCE_TOP) in Application Tier.If you are familiar with Oracle Apps 11i ,The file system for 11i with various TOP's consist of APPL_TOP, ORA_TOP, COMMON_TOP, DB_TOP and DATA_TOP.
With Multiple Middle Tier configuration came shared APPL_TOP (only APPL_TOP used to be shared ).
After shared APPL_TOP came shared Application Tier (All three TOP's on Application Tier, APPL_TOP, ORA_TOP and COMMON_TOP were shared across servers).
With this came challenge of configuration files which are specific to a server mainly iAS & 806 configuration file.
If we have configured Shared Application Tier (sharing ORA_TOP as well) in 11i then this problem was overcome by creating conf directory in COMMON_TOP.
In Oracle Apps R12,We can see new TOP called INSTANCE_TOP($INST_TOP) in Application Tier.
This TOP will now hold any configuration files (specific to that server) ,
Certificates (If SSL is enabled SSL Certificates & any other certificates specific to that server) and related log files or pid files (for Apache or any other process).
In diagram tops with <>ST can be shared across servers (Shared Tier) and
INSTANCE_TOP will be specific to Server.
17)What is OID?
Ans:OID which stands for Oracle Internet Directory is part of Identity Management in Infrastructure Tier of Fusion Middleware.
OID is part of infrastructure tier in 10g Application Server (Identity Mangement from 10.1.4 onwards).
Oracle Internet Directory (OID) is Oracle’s Implementation of LDAP (Light weight Directory Access Protocol)which is ldap version 3 compliant. OID is special kind of database repository in which information is stored in Tree structure also called DIT (Directory Information Tree).
18)How to do Oracle Application single-signon configuration?
Ans:We can use OID (Oracle Internet Directory) For configuring Single-Signon configuration.For implementation details the best link is the below one:
http://www.appsdba.info/docs/oracle_apps/advance/Integrate_AppsR12_with_OID_SSO.pdf
(OR)
The below MY ORACLE SUPPORT ID can be useful in doing single-signon configuration using Progress Reporter.
Configuring Oracle Application Server Single Sign-On (OSSO) with P6 [ID 1110098.1]
We can configure Oracle Application server single sign-on(OSSO) with P6(progress reporter 6).
Configuring Oracle Application Server Single Sign-On (OSSO) with P6
Part 1 - Configure P6 Authentication Scheme
In the P6 v7.0 Client Applications media download, there is a tool called LDAPCfgWiz.exe (\P6_70_Client_Applications\install\database\ldap-config). This tool configures the authentication mode and specifies the LDAP store information as well as steps for provisioning users from the LDAP store to the database.
In the Admin Guide, located in the Documentation medial download (P6_70_Documentation\language\Product Manuals) or http://download.oracle.com/docs/cd/E16281_01/index.htm, follow pages 313-323 for detailed steps to setting authentication mode, specifying LDAP store information, and provisioning users.
Note:
For Web Authentication you will be setting Web Single Sign On.Provisioning the users to the database is a required step. When the users are provisioned, these users still require licensing to Primavera modules. This is accomplished by logging into the P6 Project Management application.
Part 2 - Configure P6 Web Access
The P6 Web Administration Application has to be configured for authentication to Oracle Application Server Single Sign-On.
Launch the Administration Application by going to the P6 Web Home directory and launching. adminpv.cmd (windows) or adminpv.sh (unix). You can also launch the Administration Application through the browser (http://servername:port/primaveraweb/admin.jsp).
We will need to log into the P6 Web Administration Application using your privileged database user account (ex. PRIVUSER).
Under Custom, select the plus-sign (+) next to the configuration you are using for P6 Web Access (ex. Primavera Configuration)
Expand Authentication and set the Mode equal to WebSSO
Under Web Single Sign-On, set the following values:
Setting
Value
User Name Header Key
The name of the http header you specified in the policy server
Context Path Override
The path used to pass web requests from the Single Sign-On
Web server to the server of P6 Web Access.
Server and Port Override
The fully qualified domain name and port for the Web server
that the Single Sign-On server is controlling.
Save your configuration and restart the P6 Web Application server
Part 3 - Configure P6 Progress Reporter
The P6 Progress Reporter Configuration Application has to be configured for authentication to Oracle Application Server Single Sign-On
Launch the Configuration Application through the browser by going to http://servername:port/pr/admin/configuration
Click the Authentication link at the top of the screen
Enter the User Header being used with your SSO configuration (ex. Proxy-Remote-User)
Save your configuration and restart the P6 Progress Reporter server
19)Do you have any idea of load balancing in application?How load balancing is done?
Ans: I will not be doing justice to myself and others if I don't share this link with you all.Navdeep had explained the load balancing in application in a really beautiful way.Check the below link:
http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
If we want more basic stuffs we can get from below MY ORACLE SUPPORT ID(metalink note 380489.1):
Using Load-Balancers with Oracle E-Business Suite Release 12
The most current version of this document can be obtained in Oracle Metalink Note 380489.1
20)What is demililitarized setup?
Ans:DMZ stands for Demilitarized Zone. It is a network segment that is separate from your internal network, where publicly accessible servers reside, (ie: an FTP server). This separation provides an additional layer of security for your internal network. If one of these DMZ servers is compromised, intruders will not be allowed direct access to your internal network as well.To provide segregation from your internal network, the DMZ should be created on an interface of the SonicWall that is different from the one used for your internal LAN.
The above question is basically dealing with how you protect your network and security of the Network.Ofcourse as a DBA networking awareness and security details related to networking is very crucial so that we can guide network team to maintain security at network layer also.
21)Do you have any questions?
Ans: In this question you have to express your eagerness to know about company,team size and the role you will be assigned after getting this postition.Be carefull don't show your eagerness to get this job at any cost by begging,remember one thing
"If the company needs you they will take you by paying any cost but,if they don't need than you don't get that job even after reducing
your cost.
Below are few questions we can ask:
1)Gather some information about company if you come to know before giving interview or else ask For which kind of sector
the company is progressing.
2)How many memembers are going to work on this project?
3)What will be my role if I get selected?
