Hi,
Based on my personal experience,Here I'm sharing interview question on Oracle
High availability topics like RAC,Apps & Other crucial questions,As a DBA and Apps DBA.Hope these questions are very much vital.
As a experience DBA and Apps DBA I have attended many interviews.Below are the questions which I have faced and find them really useful to put in this post.Many times we know the answer for the questions but unintentionally we may commit mistakes
which should be avoided to get selected for your ideol Role/Position.
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?
I tried to share my knowledge in a best way.I used
Few links and reference to get the best out of the oracle technology and understand in a best way.
Hope it helps.
Happy INTERVIEWS
Best regards,
Rafi.
Note:Check www.orasols.com for the services we offer.
Based on my personal experience,Here I'm sharing interview question on Oracle
High availability topics like RAC,Apps & Other crucial questions,As a DBA and Apps DBA.Hope these questions are very much vital.
As a experience DBA and Apps DBA I have attended many interviews.Below are the questions which I have faced and find them really useful to put in this post.Many times we know the answer for the questions but unintentionally we may commit mistakes
which should be avoided to get selected for your ideol Role/Position.
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?
I tried to share my knowledge in a best way.I used
Few links and reference to get the best out of the oracle technology and understand in a best way.
Hope it helps.
Happy INTERVIEWS
Best regards,
Rafi.
Note:Check www.orasols.com for the services we offer.
