Oracle RAC Interview Questions (10g)

 Find Core DBA Questions in this blog -

What are Oracle Clusterware processes for 10g on Unix and Linux
Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.
Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user
Event manager daemon (evmd) —A background process that publishes events that crs creates.
Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC
•LMS—Global Cache Service Process
•LMD—Global Enqueue Service Daemon
•LMON—Global Enqueue Service Monitor
•LCK0—Instance Enqueue Process

To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

[sam id=1 codes='false']

What are Oracle Clusterware Components
Voting Disk —

• Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.
• Central reference of all nodes and keeps the heart beat information between nodes in terms of votes

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

Oracle Cluster Interconnect — In other words also called as private interconnect, with high speed gigabit channel or infiniband. which is high speed , low latency network connection attached to private switches rather the public interfaces. This will be used by cluster to provide node membership and also inter cluster communication (aka global cache services , transfer of blocks from one node to another)

How do you backup the OCR
There is an automatic backup mechanism for OCR. The default location is :

$ORA_CRS_HOMEcdata"clustername"
To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig –restore

With Oracle RAC 10g Release 2 or later, you can also use the export command:

#ocrconfig -export -s online, and use -import option to restore the contents back.

With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command:

# ocrconfig –manualbackup

How do you backup voting disk & do you need to take a backup of voting Disk.
#dd if=voting_disk_name of=backup_file_name

Voting disk backup is not required since this is non-persistent data and just records about the node (cluster membership) availability, when you start the cluster it is empty.

How do I identify the voting disk location
#crsctl query css votedisk

How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

How to identify which version of Clusterware is running & active.

#crsctl query  --activeversion

Is ssh required for normal Oracle RAC operation ?
"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.

What is the purpose of Private Interconnect ?
Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

Why do we have a Virtual IP (VIP) in Oracle RAC?
Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?
This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

How many nodes are supported in a RAC Database?
10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.

How to debug srvctl?

Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

what is the purpose of the ONS daemon?
The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners.
This in order to facilitate:
a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes.
b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.

what is the ora.rac1.GSD in crs_stat?

It is called Global Service Daemon, which is there for backward compatibility, in other words in Oracle 9i, the RAC is running with Veritas cluster and the GSD will be synchronizing the instance details to Cluster. You can add the 9i instances to Oracle 10g or 11g RAC Clusters

What is GRD?

Global Resource directory , a common memory structures across SGA’s, in other words this is the combination of GCS/GES memory structures (infact synchronizing all the times through  cluster interconnect messages). All the resources in the cluster group form a central repository called GRD. which is integrated and distributed across the nodes  memory structures. Each instance masters some of the resources (buffer) based on their weightage and accessibility) and together all formed called GRD.  Basically a combination of GES and GCS.

What is Dynamic Remastering?

Every instance (node) owns the block(buffers) called master node for that blocks when accessed first time. If the other node requires the same, it has to send a request to the owning node to get the blocks (CR/CUR modes). If the requests are more than 50 in an hour the mastership will be transferred to the other node.

What is Node Mastering?

The node which starts first in the cluster acts a master node, If this crashes the other nodes who detects first will be the master node for that cluster.

What is cache fusion, Which background process manages it?

Other words called as Global cache services, Cache fusion is to manage the buffers with in local or the global and transfers from instance to another , basically LMS process take cares of the transfers, upon  receive the message (BST/AST) it manages the lock escalation and descalation with help of Global Enqueue services (LMD/LMON/LCK )processes, process the buffer and made a copy (if required) and then transfer that buffer to requested instance via cluster interconnect.

What is cache coherency which background process manages it?

Cache coherency means that the contents of the caches in different nodes are in well defined state with respect to each other, is a technique of keeping multiple copies of buffers consistent between different Oracle instances. LMS process manages this in terms of local/global lock levels or with Message queues (AST/BST)

How does instance recovery works in RAC databases?

 [sam id=1 codes='false']

Can you change Private interconnect IP address or Ethernet interface after cluster installation?

Yes, we can, before to that we should stop the clusterware and use oifcfg tool to delete the old and set the new interfaces, for ex:

#oifcfg –getif

#oifcfg –delif

#oifcfg –setif

Can you change the Virutal IP after you install clusterware installation?

Yes, its possible, but you have to run vipca or using srvctl, please see here for complete steps

What is the Load Balancing Advisory?
To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag.

What is hangcheck timer used for ?
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.

Is the hangcheck timer still needed with Oracle RAC 10g?
Yes

What files can I put on Linux OCFS2?
For optimal performance, you should only put the following files on Linux OCFS2:
- Datafiles
- Control Files
- Redo Logs
- Archive Logs
- Shared Configuration File (OCR)
- Voting File
- SPFILE

What are various cluster file systems

OCFS2, Veritas Cluster Filesystems, NFS, ASM, etc.

Does ASM cannot be used as Cluster filesystem.

Well yes, ASM is storage management provides storage mirroring,striping along with cluster filesystem capabilities, it also acts as volume manager eliminating the need of disk cache I/O layer from OS.

Can I change the name of my cluster after I have created it when I am using Oracle Clusterware?
No, you must properly uninstall Oracle Clusterware and then re-install.

What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)

Can you have many database versions in the same RAC?
Yes, but Clusterware version must be greater than the greater database version.

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

How do I identify the voting disk location

#crsctl query css votedisk

-- this works only when the clusterware is up in 10g, where in CRS start up is not required

What is TAF?  How does it works.

Transparent application failover is the mechansim to provide high availability of the database services to the connections happens via TNS along with RAC specific scripts called racgmon  etc.

For this we

• Configure TNS entry for that service in both nodes
• Update the remote listener parameter with the service name
• Add service name to high availability stack i.e to CRS using srvctl
  • srvctl add service –d dbname –servicename –p <preferrednode> -r<availablenode>

What is FAN? How does it works

Fast application notification is different from the TAF, which commonly used to trap the failures of the services using ONS (oracle notification services) for example JDBC connection does not failover on TAF events, where ONS can be used here.

What are RAC based services? What are difference between normal database service and RAC services?

•Is a means of grouping sessions that are doing the same kind of work

•Provides single-system image instead of multiple instances image

•Is a part of the regular administration tasks that provide dynamic service-to-instance allocation

•Is the base for high availability of connections

•Provides a new performance-tuning dimension

•Normal services not maintained in data dictionaries where the Rac services maintained in data dictionary.

What happens if one of the node is not able to access the voting disk?

The master node OCSSD in the instance verifies the votes in the voting disk periodically and ensure quorum is matched, if quorum is not matched then it posts the failure nodes OCSSD to evict the cluster.

Or Node OCSSD recognises it and evicts the node

What happens if all of the nodes not able to access the voting disk?

This can be lead to split brain syndrome, each node acts as master node, with the disktimeout setting clusterware waits until that period can be delayed using disktimeout setting to reasonable value using crsctl. all nodes reboot.

What happens if one of the node is not able to communicate via private interconnect?

Node OCSSD recognises it and evicts the node

What happens if all of the nodes not able to communicate via private interconnect?

This can be lead to split brain syndrome, each node acts as master node, with the csstimeout setting clusterware waits until that period , can be delayed using csstimeout setting to reasonable value using crsctl. all nodes reboot.

What is split brain syndrome?

Each node acts as master since there is communication or common storage access break down.

Which background daemons initiates node eviction?

ocssd

[sam id=1 codes='false']

Which background daemon starts clusterware or the resources?

crsd

Can you tell me under which user crs,cssd,oprocd, evmd, start and what is anything fails.

Work Load Balancing & Failover questions:-

What are my options for load balancing with Oracle RAC? Why do I get an uneven number of connections on my instances?

All the types of load balancing available currently (9i-10g) occur at connect time.
This means that it is very important how one balances connections and what these connections do on a long term basis.
Since establishing connections can be very expensive for your application, it is good programming practice to connect once and stay connected. This means one needs to be careful as to what option one uses. Oracle Net Services provides load balancing or you can use external methods such as hardware based or clusterware solutions.
The following options exist prior to Oracle RAC 10g Release 2 (for 10g Release 2 see Load Balancing Advisory):
Random
Either client side load balancing or hardware based methods will randomize the connections to the instances.
On the negative side this method is unaware of load on the connections or even if they are up meaning they might cause waits on TCP/IP timeouts.
Load Based
Server side load balancing (by the listener) redirects connections by default depending on the RunQ length of each of the instances. This is great for short lived connections. Terrible for persistent connections or login storms. Do not use this method for connections from connection pools or applicaton servers
Session Based
Server side load balancing can also be used to balance the number of connections to each instance. Session count balancing is method used when you set a listener parameter, prefer_least_loaded_node_listener-name=off. Note listener name is the actual name of the listener which is different on each node in your cluster and by default is listener_nodename.
Session based load balancing takes into account the number of sessions connected to each node and then distributes the connections to balance the number of sessions across the different nodes.

How can a customer mask the change in their clustered database configuration from their client or application? (I.E. So I do not have to change the connection string when I add a node to the Oracle RAC database)

The combination of Server Side load balancing and Services allows you to easily mask cluster database configuration changes. As long as all instances register with all listeners (use the LOCAL_LISTENER and REMOTE_LISTENER parameters), server side load balancing will allow clients to connect to the service on currently available instances at connect time.
The load balancing advisory (setting a goal on the service) will give advice as to how many connections to send to each instance currently providing a service. When a service is enabled on an instance, as long as the instance registers with the listeners, the clients can start getting connections to the service and the load balancing advisory will include that instance is its advice.
With Oracle RAC 11g Release 2, the Single Client Access Name (SCAN) provides a single name to be put in the client connection string (as the address). Clients using SCAN never have to change even if the cluster configuration changes such as adding nodes.

What is the Load Balancing Advisory?

To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag. The feedback is provided as an entry in the Automatic Workload Repository and a FAN event is published. The easiest way for an application to take advantage of the load balancing advisory, is to enable Runtime Connection Load Balancing with an integrated client.

How do I enable the load balancing advisory?

The load balancing advisory requires the use of services and Oracle Net connection load balancing.
To enable it, on the server: set a goal (service_time or throughput, and set CLB_GOAL=SHORT ) on your service.
For client, you must be using the connection pool.
For JDBC, enable the datasource parameter FastConnectionFailoverEnabled.
For ODP.NET enable the datasource parameter Load Balancing=true.

Why do we have a Virtual IP (VIP) in Oracle RAC 10g or 11g? Why does it just return a dead connection when its primary node fails?

The goal is application availability.
When a node fails, the VIP associated with it is automatically failed over to some other node. When this occurs, the following things happen.
(1) VIP detects public network failure which generates a FAN event.
(2) the new node re-arps the world indicating a new MAC address for the IP.
(3) connected clients subscribing to FAN immediately receive ORA-3113 error or equivalent. Those not subscribing to FAN will eventually time out.
(4) New connection requests rapidly traverse the tnsnames.ora address list skipping over the dead nodes, instead of having to wait on TCP-IP timeouts
Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error.
As a result, you don't really have a good HA solution without using VIPs and FAN. The easiest way to use FAN is to use an integrated client with Fast Connection Failover (FCF) such as JDBC, OCI, or ODP.NET.

What are my options for setting the Load Balancing Advisory GOAL on a Service?

The load balancing advisory is enabled by setting the GOAL on your service either through PL/SQL DBMS_SERVICE package or EM DBControl Clustered Database Services page. There are 3 options for GOAL:
None - Default setting, turn off advisory
THROUGHPUT - Work requests are directed based on throughput. This should be used when the work in a service completes at homogenous rates. An example is a trading system where work requests are similar lengths.
SERVICE_TIME - Work requests are directed based on response time. This should be used when the work in a service completes at various rates. An example is as internet shopping system where work requests are various lengths
Note: If using GOAL, you should set CLB_GOAL=SHORT

 Core DBA Questions - Here in this blog , very nice and neatly framed

[sam id=1 codes='false']