RAC Interview Questions

1.       What is RAC?

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:

a.       ACMS—Atomic Controlfile to Memory Service (ACMS)

b.       GTX0-j—Global Transaction Process

c.       LMON—Global Enqueue Service Monitor

d.       LMD—Global Enqueue Service Daemon

e.       LMS—Global Cache Service Process

f.        LCK0—Instance Enqueue Process

g.       RMSn—Oracle RAC Management Processes (RMSn)

h.       RSMN—Remote Slave Monitor

3.       What is GRD?

GRD stands for Global Resource Directory. The GES and GCS maintain 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.

4.       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 cache fusion

5.       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.

6.       Give details on GTX0-j :-

The process provides transparent support for XA global transactions in a RAC environment. The database auto tunes the number of these processes based on the workload of XA global transactions.

7.       Give details on LMON:-

This process monitors global enques and resources across the cluster and performs global enqueue recovery operations. This is called as Global Enqueue Service Monitor.

8.       Give details on LMD:-

This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.

9.       Give details on LMS:-

This process is called as Global Cache service process. This process maintains statuses of datafiles and each cached block by recording information in a Global Resource Directory (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.

10.   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.

11.   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.

12.   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-coordinating process running in another instance.

13.   What components in RAC must reside in shared storage?

All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.

14.   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, redologs files when these files are hosted out of cluster-aware shared storage which are group of shared disks.

15.   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).

16.   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.

17.   How can we configure the cluster interconnect?

Configure User Datagram Protocol (UDP) on Gigabit Ethernet for cluster interconnects. 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.

18.   Can we use crossover cables with Oracle Clusterware interconnect?

No, crossover cables are not supported with Oracle Clusterware interconnects.

19.   What is the use of cluster interconnecting?

Cluster interconnect is used by the Cache fusion for inter instance communication.

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. 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.

20.   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.

21.   What are the characterizes controlled by Oracle services feature?

The characteristics include a unique name, workload balancing and failover options, and high availability characteristics.

22.   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.

23.   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.

24.   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.

25.   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.

26.   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.

27.   Give the usage of srvctl

a.       srvctl start instance -d db_name -i "inst_name_list" [-o start_options]

b.       srvctl stop instance -d name -i "inst_name_list" [-o stop_options]

c.       srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediate

d.       srvctl start database -d name [-o start_options]

e.       srvctl stop database -d name [-o stop_options]

f.        srvctl start database -d orcl -o mount

28.   What are the major RAC wait events?

In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs. The most common wait events related to this are gc cr request and gc buffer busy.

a.       GC CR request: the time it takes to retrieve the data from the remote cache.  Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)

b.       GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.

29.   How can I verify that RAC instances are running?

Issue the following query from any one node connecting through SQL*PLUS.

$connect sys/sys as sysdba

SQL>select * from V$ACTIVE_INSTANCES;

The query gives the instance number under INST_NUMBER column, host_: instance name under INST_NAME column.

30.   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.

31.   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.

32.   Mention the components of Oracle Clusterware.

Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry (OCR)

33.   Name some Oracle clusterware tools and their uses?

a.       OIFCFG - allocating and de-allocating network interfaces

b.       OCRCONFIG - Command-line tool for managing Oracle Cluster Registry

c.       OCRDUMP - Identify the interconnect being used

d.       CVU - Cluster verification utility to get status of CRS resources

34.   What are the types of load-balancing and difference?

There are two types of connection load-balancing: server-side load balancing and client-side 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.

35.   What are the 3 greatest benefits that RAC provide?

a.       Availability

b.       Scalability – Add CPU, disk, memory along with nodes

c.       Ability to user low cost commodity hardware.

36.   How do you backup OCR?

There is an automatic backup mechanism for OCR. The default location is: $ORA_CRS_HOME/cdata/"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 manually backup of the OCR with the command:
# ocrconfig -manualbackup

37.   How do you backup voting disk?

# dd if=voting_disk_name of=backup_file_name

38.   How do I identify the voting disk location?

# crsctl query css votedisk

39.   How do I identify the OCR file location?

Check /var/opt/oracle/ocr.loc or /etc/ocr.loc (depends upon platform)

Or
# ocrcheck

40.   What is the purpose of the ONS deamon?

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 receives 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.

41.   What is Oracle Clusterware?

It provides the basic clustering support at OS level and enables oracle software to run in clustering more. It support up to 64 nodes. Oracle Clusterware can runs as a standalone cluster service or with a vendor supplied clusterware such as SunCluster or TrueCluster

42.   What is OCR?

Is the central repository for the CRS and keeps the details of the services and status of the resources. It should be storage and should be accessible to all nodes in the clusters simultaneously. It is the integral component of RAC

43.   What is voting disk?

It is used as a central reference for all the nodes and keeps the heartbeat information between nodes. It is a shared disk that will be accessed by all the members’ nodes in the cluster during the operations. It also called ‘Quorum Device”

44.   Steps to Install RAC

a.       OS configuration for the clusterware and RAC software, each server has to have Public IP and Private IP.

                                                              i.      Configuring network files /etc/hosts

                                                            ii.      Setup groups and users

                                                          iii.      Configure Kernel parameters

                                                           iv.      Configure the hangcheck-timer module (monitor linux kernel for extended operating system hangs)

b.       Setup the shared storage

                                                              i.      Configure raw devices /etc/systemconfig/rawdevices

c.       Establish user equivalence between nodes

d.        

                                                              i.       

45.    

a.        

 

Component	Description	Share Storage
SGA	Each instance has its own SGA	N/A
Background process	Each instance has its own BP	N/A
Data files	Shared by all instances, so must be placed in shared storage.	YES
Control files	Shared by all instances, so must be placed in shared storage.	YES
Online Redologs	Only one instance can write, but others instances can read during the recovery and archiving. If an instance is shutdown log switches by others instances can force the idle instance redologs to be archived.	YES
Archived Redologs	Private to the instance, but others instances will need access to all required archive logs during media recovery	NO
Flash Recovery Logs	Shared by all instances, so must be placed and shared storage	YES
Alert log and Trace	Private to each instance, other instances never read/write those files	NO
ORACLE_HOME	Same as single instance plus can be placed on shared storage file system, allowing a common ORACLE_HOME for all instances in an RAC environment.	YES/NO

 

Data Guard Interview Questions

1. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?

 

Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

 

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.

 

2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

 

Active dataguard is mostly about the physical standby.

 

Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write - do some destructive things in it (drop tables, change data, whatever - run a test - perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails - you are covered. Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes - and logical standby does not (11g added broader support, but not 100%) - there are times when logical standby isn’t sufficient. It also permits fast incremental backups when offloading backups to a physical standby database.

 

3. What is a Dataguard?

 

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.

 

4. What are the uses of Oracle Data Guard?

 

a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

 

b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.

 

c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

 

5. What is Redo Transport Services?

 

It control the automated transfer of redo data from the production database to one or more archival destinations.

Redo transport services perform the following tasks:

 

a) Transmit redo data from the primary system to the standby systems in the configuration.

b) Manage the process of resolving any gaps in the archived redo log files due to a network failure.

c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the

primary database or another standby database.

 

6. What is apply services?

 

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

 

7. What is difference between physical and standby databases?

 

The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data:

 

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database.

 

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.

 

8. What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:

 

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services

b) Manage an entire Data Guard configuration from any system in the configuration

c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases

d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface.

e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

 

9. What are the Data guard Protection modes and summarize each?

 

Maximum availability :

 

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

SQL> alter database set standby to maximize availability;

 

Maximum performance :

 

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

SQL> alter database set standby to maximize performance;

 

Maximum protection :

 

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather thanu continue processing transactions.

SQL> alter database set standby to maximize protection;

 

 

 

 

 

DATA GUARD

 

Standby Setup: Overview

 

1.      Backup Primary DB

2.      Ship backup to Standby Site

3.      Add Log Transport on Primary

4.      Add Net*8 settings on Primary

5.      Setup Standby database

6.      Setup Net*8 on Standby Site

7.      Start Recovery

 

 

Checklist

 

Steps	Description	Site
1	Enforce Logging of All operations to ensure data consistence. Make Sure Primary Database is in ARCHIVE Log mode and is set to Auto-Archive SQL> alter database force logging;	Primary
2	Create a Backup of Primary Database datafiles Offline (consistent) or an online (inconsistent)	Primary
3	Create a Standby Control file SQL> alter database create standby controlfile as '/..../...';	Primary
4	Create a Copy of init.ora file for Standby database.	Primary
5	Ship backup data files, standby control file and standby init.ora file to standby site	Primary
6	Setup Net*8 on Primary site to access Standby site	Primary
7	Setup Net*8 on Standby site to access Primary site	Standby
8	Make Primary and Standby init.ora changes (parameters) Db_file_name_convert Standby_archive_dest ......	Standby
9	Startup Standby Database instance in recovery mode SQL> startup mount pfile='.....'	Standby
10	Create Standby redo log files, if applicable SQL> alter database add standby logfile (/..','/....');	Standby
11	Setup instance parameters to ship redo log/archive log to standby site SQL> alter system archive log current;	Primary
12	Verify shipping and application of archive log	Primary/Standby

 

Switchover

 

a. identified what role the db plays

SQL> select database_role from v$database;

 

b. Initiate the switchover on the primary database

SQL> alter database commit to switchover to physical standby with session shutdown wait;

 

c. On the primary database shutdown and restart the instance.

SQL> shutdown immediate;

SQL> startup nomount;

SQL> alter database mount standby database;

 

d. Start the MRP process (Managed Recovery Process)

SQL> alter database recover managed standby database disconnect;

 

e.  Verify the switchover status in the v$database view. it should be SWITCHOVER PENDING status.

 SQL> select database_role from v$database;

 

f. Switch physical standby database role to the primary role.

SQL> alter database commit to switchover to primary with session shutdown wait;

 

g. Shutdown and restart the new primary database

SQL> shutdown

SQL> startup

 

h. Begin archiving logs to the physical standby database.

SQL> alter system archive log start;

SQL> alter system switch logfile;

 

FAILOVER

 

a. Initiate the failover operation on the standby database

SQL> alter database recover managed standby database finish;

 

b. Convert the physical standby database to the primary role

SQL> alter database commit to switchover to primary;

 

c. Register the missing redo logs.

SQL> alter database register logfile '/standby/arch_dest/arch_1_101.arc';