Posted on

Oracle RAC DBA Interview Questions

1) What is Oracle RAC?
Oracle RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.
2) Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database. Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
There is a single storage that is accessed by two are more of these instances
3) What is single point of failure?
SPOF stands for Single Point Of Failure.Every component in the system must have redundancy incorporated in it.Failure of a single component may lead to failure of entire system.
Components include – power supplies, hardware, storage disks, OS, database,applications.
Oracle Clustering provides solution to avoid SPOF (single Point Of Failure).
4) What is need of clustering solution?
In todays market data is considered to be the most precious asset of an organization. Database is the data repository. Customers need data on a 24×7 basis which demands the servers hosting database to be up and running all the time. As a protection against server failure due to media failure(Failure in server hardware) or instance failure(database failure) clustering solutions have been developed.
5) What is GRD? How does this help with cache fusion?
GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cached block using global resource directory.This process is referred to as cache fusion and helps in data integrity.
6) Give Details on Cache Fusion:-
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion.
7) Give Details on ACMS:-
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
8) What is clustering?
Clustering is a High availability solution.Clustering makes physically separate servers appear as a single server to the end user.Clustering provides scalability at all level – OS, Storage, database, Applications, hardware.Clustering makes the application available 24x7x365.
9) Give details on GTX0-j :-
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.GLOBAL_TXN_PROCESSES setting specifies the initial number of GTXn background processes per instance. This process is seen only in RAC environments. The range of value for GLOBAL_TXN_PROCESSES can be from 1 to 20 and there is no definite need to set this parameter. The number of processes needed is decided by oracle database automatically and is tuned on demand
10) Give details on LMON:-
This process monitors instance membership in a RAC encironment, detects isntance transitions, reconfigures GES and GCS resources as needed. This is called Global Enqueue Service Monitor Process primarily used for managing global resources
11) Give details on LMD:-
As LMON is for monitoring global enqueue services, this is global enqueue services daemon process. This process manages incoming remote resource requests within each instance. LMD0 particularly processes incoming enqueue request messages. IT controls access to global enqueues
12) Give details on LMS:-
This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
13) Give details on LCK0:-
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as library and row cache requests.
14) Give details on RMSn:-
This process is called as Oracle RAC management process.These processes perform manageability tasks for Oracle RAC. Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
15) Give details on RSMN:-
This process is called as Remote Slave Monitor.This process manages background slave process creation and communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.
16) What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
17) What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE’s, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
18) Give few examples for solutions that support cluster storage:-
ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).
19) Give details on oracle rac lkdebug utility:-
LKDEBUG is an oracle supplied utility.LKDEBUG is integrated with ORADEBUG utility.To use LKDEBUG we must login with SYSDBA system privilege.
LKDEBUG is used to obtain information about the current state GCS and GES structures in the instance.
To obtain information on LKDEBUG options issue the following command
20) What is an interconnect network?
an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
21) How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware. Windows clusters use the TCP protocol.
22) Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware interconnects.
23) What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
24) How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.
25) What is the use of a service in Oracle RAC environment?
Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.
26) What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing and failover options,and high availability characteristics.
27) Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database
28) What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
29) What is the use of VIP?
If a node fails, then the node’s VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
30) Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
31) What is the significance of VIP address failover?
When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don’t have to wait for TCP connection timeout messages.
32) What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETC
33) How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
The query gives the instance number under INST_NUMBER column, host_:instancename under INST_NAME column.
34) What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAC uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.
35) Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.
36) Why should we have separate homes for ASM instance?
It is a good practice to have ASM home separate from the database home (ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

37) What is the advantage of using ASM?
Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the/storage configuration across the disks. ASM does this by distributing the database file across all of the available storage within our cluster database environment.
38) What is rolling upgrade?
It is a new ASM feature from Database 11g. ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions
39) Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1) and upwards
40) State the initialization parameters that must have same value for every instance in an Oracle RAC database:-
Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
41) Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.
42) What two parameters must be set at the time of starting up an ASM instance in a RAC environment?
The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.
43) Mention the components of Oracle clusterware:-
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
Shared storage is needed for voting disk (quorum disk). Voting disk can be stored on a raw device or a clustered file system.Voting disk can be stored in Oracle ASM and redundancy level can be set to high
Gives details on node membership – Nodes currently available within the cluster
Ocssd uses voting disk to determine which nodes join and leave the cluster. So voting disk is known as CSS(cluster synchronization services) voting disk
In oracle 10.2 and above, 3 copies of voting disks are created by default. Voting disks can be mirrored
Odd number of voting disk needs to be maintained – recommendation from oracle
44) What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource aka cluster ready services. Some of the CRS resources are oracle RAC database, ASM instances, database services, listeners, VIP addresses, ASM diskgroups, application processes. Configuration and status of these resources that are managed by oracle clusterware is tracked by clusterware. It is interesting to note that clusterware infrastructure management is automated in the sense that the status of these resources are checked at periodic intervals, resources are restarted for fixed number of time if they fail
45) What is the use of OCR?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
46) How does a Oracle Clusterware manage CRS resources?
Oracle clusterware is a mandate software that needs to be installed in each and every server that form part of RAC cluster. They are installed in their separate oracle homes that are not shared. Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
47) Name some Oracle clusterware tools and their uses?
OIFCFG – allocating and deallocating network interfaces
OCRCONFIG – Command-line tool for managing Oracle Cluster Registry
OCRDUMP – Identify the interconnect being used
CVU – Cluster verification utility to get status of CRS resources
48) What are the modes of deleting instances from ORacle Real Application cluster Databases?
We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).
49) How do we remove ASM from a Oracle RAC environment?
We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name
50) How do we verify that an instance has been removed from OCR after deleting an instance?
Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
51) How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
52) What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type. GV$ views obtain information from individual V$ views.
53) What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.
54) What is the difference between server-side and client-side connection load balancing?
Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
55) Give details on srvm_trace:-
Oracle RAC (Real Application Cluster) SRVM_TRACE environment variable is an oracle RAC (Real Application Cluster) environment variable from Oracle
It is used in the debugging on Oracle RAC (Real Application Cluster) utility srvctl.
56) Give the usage of srvctl:-
srvctl start instance -d db_name -i “inst_name_list” [-o start_options]srvctl stop instance -d name -i “inst_name_list” [-o stop_options]srvctl stop instance -d orcl -i “orcl3,orcl4” -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount
57) What is an OCRCHECK utility?
An ocrcheck utility is a diagnostic tool used for diagnosing OC(Oracle Cluster Registry) Problems.This is used to verify the Oracle Cluster Registry(OCR) integrity.
58) What does an ocrcheck display?
The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.
59) How does ocrcheck perform integrity check?
OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.
60) Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
61) Where does an ocrcheck utility create a log file?
OCRCHECK creates a log file in the directory CRS_home/log/hostname/client.
How can we change the amount of logging?
To change amount of logging, edit the file CRS_home/srvm/admin/ocrlog.ini.
62) What is scalability?
Scalability ensures that performance should remain good irrespective of increase in workload.Infrastructure scalability starts from hardware (physical server), OS, storage, database and ends with application
Clustering solution provides scalability as it makes physically seperate servers appear as a single machine to the end user.
Scalability can be of two types:
Vertical scalability – Based on the workload the server size needs to be increased like adding mroe memory, more CPU, hard disk etc
Horizontal scalability – Instead of going for single expensive hardware, some projects can be supported with horizontal scalability wherein lots of less expensive server hardware is purchased, they are given private interconnect and to end user they appear as single server. Resources are shared and this forms basics of RAC architecture
63) What is oracle RAC hardware requirement?
A typical Oracle RAC implementation needs the following hardware:
i) Two or more physical servers – This is basics of cost cutting. Henceforth, usually low cost x-86 servers are used for the implementation
ii) Networking across these servers
iii) storage shared by these servers
iv) Linux flavors like RHEL, Oracle Linux is commonly used for RAC implementation. Still windows is supported
64) What is the difference between user accounts grid and oracle?
Starting oracle 11gR2 oracle clusterware is integrated with oracle ASM and is available as grid infrastructure. OS user grid owns the grid infrastructure that includes clusterware as well as oracle ASM instance. Oracle RAC database owner is oracle user. This is same as oracle user that is used in stand-alone installations
65) Can Cluster verification utility be Downloaded for free?
Cluster verification utility popularly called the cvu is the utility that comes as an integral part of oracle clusterware. CVU is very helpful using installation and configuration of oracle clusterware and oracle RAC (Real application cluster).It is useful during various phases of installation starting from initial hardware setup to final stages of installation and configuration.We can download and use the CVU along with the Oracle RAC 10g and 11g versions. It is available for free from the oracle website
66) Give details on oracle RAC database datafiles :0
All data belonging to the database are stored in objects like tables, indexes, data dictionary, compiled PL/SQL code
Only one copy of datafile stored on shared storage the central location can be accessed by all instances in the RAC environment same as a single-instance database
Datafiles are not mirrored. Redundancy provided at storage level to prevent loss of datafiles. In 10.1 versions and above redundancy can also be achieve using ASM
67) You have a 32-bit laptop and you want to install oracle database RAC there. Can you do so?
I invoked the Oracle Universal Installer and tried installing and creating RAC databases in my Windows 7 laptop. I’ve installed vmplayer and created virtual machines with Ubuntu OS in them. I just invoked the RAC installation option in my Windows 7 OS. I got an error. I expected the Detail to be – OCR not installed. It requires that Oracle clusterware software must be installed and configured properly for the Oracle RAC to be installed successfully. To my surprise I had a different issue which said Oracle Database RAC can be installed only in 64-bit operating system
68) What is a voting disk?
A voting disk is a file that manages information about node membership.
What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name
What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.
How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk
How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path
How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.
How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force
How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force
How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force
What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk
When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration
What does file management in oracle RAC involve?
File Management In Oracle RAC (Real Application Cluster) involves managing datafile,redo log file to name a few
Give details on datafile management in RAC :-
Datafiles store the database data and hence are the critical resources.
Datafiles are stored in a disk that is accessible by all the instances of the RAC i.e they are stored in shared storage.
Every instance in the cluster has to verify its access to datafile.Also it is equally important that they are they access the same version of the datafile. We can use the following command:
It is important to perform this integrity check after tasks like :
1) Tablespace addition
2) Tablespace deletion
3) Tablespace status change from offline/online to viceversa
4) Addition and deletion of datafiles
Whenever a datafile recovery is performed the first instance to start the cluster in RAC performs the datafile recovery.All instances start and verify their access after that.
Give details on Redo Log File Management in RAC :-
Each instance has its own online redo log groups.
To add a redo log group to a specific instance, specify the INSTANCE clause on the ALTER DATABASE ADD LOGFILE statement.
If we do not specify the instance when adding the redo log group,the redo log group is added to the instance to which you are currently connected.
Each instance must have at least two groups of redo log files. We must allocate the redo log groups before enabling a new instance with the
When the current group fills, an instance begins writing to the next log file group.If our database is in ARCHIVELOG mode,then each instance must save filled online log groups as archived redo log files that are tracked in the control file.During database recovery, all enabled instances are checked to see if recovery is
needed. If we remove an instance from your Oracle RAC database, we should disable the instance so it is does not have to be checked during database recovery
In a RAC environment how will you update the OCR with new spfile location?
Make use of srvctl utility command
srvctl modify database -db database_name -p ‘location_of_spfile’
How will you start a database in a RAC environment?
Using the srvctl command we can start the database in an Oracle RAC environment as follows :
srvctl start database -db database_name
You are making use of an Oracle restart configuration. You try to start the single instance using srvctl -i instancename but get an error. Why is that so?
It is because srvctl -i instancename option is not supported here