Become a Certified ExpertBy Harald van Breederode and Joel Goodman
Oracle University offers Oracle Expert certification in Oracle Real Application Clusters.
The new Oracle Certified Expert program provides opportunities for Oracle professionals to earn certifications in advanced, niche, and specialty technology areas. The program's first advanced offering for DBAs is the Oracle Database 10g: Administering Oracle Real Application Clusters exam. To earn the Oracle Real Application Clusters Administrator Certified Expert credential, you must pass this new exam and meet one of the following additional requirements: earn the Oracle Database 10g DBA Oracle Certified Professional (OCP) credential or attend either the Oracle Database 10g: Real Application Clusters course or the Oracle Database 10g: Real Application Clusters for Administrators Release 2 course.
This column discusses concepts relating to sequences, parallel execution, virtual IP addresses (VIPs), and Oracle Clusterware file management. It also provides samples of the type of questions that appear in the Oracle Database 10g: Administering Oracle Real Application Clusters exam. The sample questions and code formats have been adjusted for publication in this column.
Sequences and Oracle RAC
The use of sequences in Oracle Real Application Clusters (Oracle RAC) is much the same as it is for single-instance Oracle databases, but there are certain Oracle RAC-specific issues. With two or more database instances, sequences may require coordination to guarantee that the sequence numbers are allocated in the correct order.
Which of the following are always true regarding the use of sequences in an Oracle 10g RAC database? (Pick two.)
A. It is not possible to use the CACHE option, because each database has a row cache.
The correct answers are B and D.
Answer B is correct because if the CACHE option is used without the ORDER option, each instance caches a separate range of numbers and sequence numbers may be assigned out of order by the different instances. Answer D is correct because the CACHE option causes each instance to cache its own range of numbers, thus reducing I/O to the Oracle Data Dictionary, and the NOORDER option eliminates message traffic over the interconnect to coordinate the sequential allocation of numbers across all instances of the database.
Answer A is incorrect because even though each instance has a row cache, it is still possible to use the CACHE option with sequences. In such a case, each instance may cache numbers for that sequence in its row cache. Answer C is incorrect because if the CACHE and ORDER options are used together, all instances must allocate numbers in order by coordinating the assignment of the next value, using messages over the interconnect, thereby increasing interconnect traffic in proportion to the frequency of new-number assignment.
Adding the ORDER option requires that sequence numbers be returned in order. How does Oracle Database coordinate sequences with the CACHE and ORDER options so that numbers are cached in each instance's row cache but are still allocated in the correct order?
A. One instance acts as the mastering instance for the cached values.
The correct answer is D.
Answer D is correct because all database instances know their own NEXTVALUE, based on the last cached value used in that instance. So the NEXTVALUE used for a request from any instance must be the highest NEXTVALUE from any instance.
Answer A is incorrect because no database instance masters the sequence metadata for a specific sequence. Each instance has row cache metadata for the same sequence, provided that sequence has been used in that instance. Answer B is incorrect because instances do not send this metadata regularly; they send it only upon request from the instance currently allocating the next number in the sequence. Answer C is incorrect because the cache metadata is not stored in the controlfile. It is stored in the row cache of all instances that have used the sequence since that instance started.
Parallel Execution and Oracle RAC
Oracle RAC databases support parallel execution of queries, data manipulation language (DML), and data definition language (DDL) in much the same way as a single database instance, but there are some special considerations regarding performance and administration issues that are unique to Oracle RAC. A two-instance Oracle RAC database has the settings PARALLEL_MAX_SERVERS = 100 and PARALLEL_MIN_PERCENT = 0 on each instance. The DBA has also set PARALLEL_ADAPTIVE_MULTIUSER to false on both instances. The DBA then logs in to instance A and attempts to create a large index in parallel, using the following statement:
SQL> create index sh.sales_prod_cust on SH.sales (prod_id, cust_id) parallel 10;
How are the parallel execution slave processes allocated to build this index?
A. Five slaves are allocated from each instance, and if either instance has fewer than five slaves available, an error is returned.
The correct answer is D.
Oracle Database attempts to allocate all slaves on the instance where the coordinator process is running, in this case on instance A. If all slaves required are available, they are allocated from this instance, which reduces interconnect overhead. If the coordinator instance is unable to provide enough slaves because some are already allocated or because the parallel request exceeded PARALLEL_MAX_SERVER for the requesting instance, slaves are requested from other instances. If all the instances together are unable to provide enough slaves, the statement will execute with a reduced set of slaves, as would be the case in a single-instance Oracle Database.
Another important performance issue related to parallel execution is instance recovery. A four-node Oracle RAC cluster has an instance on each node. Instance C fails due to node failure on node C. All instances have the PARALLEL_MAX_SERVER = 300 setting. How does the DBA ensure that recovery for instance C is done in parallel, thereby speeding up the recovery?
A. No action is required, because PARALLEL_MAX_SERVER is already set on all instances.
The correct answer is C.
In Oracle Database 10g, the RECOVERY_PARALLELISM parameter defaults to CPU_COUNT -1, but the DBA must assure that it has a nonzero value and that the degree of parallelism used by the recovery allows instance recovery to complete within the required service-level agreement for recovery. If RECOVERY_PARALLELISM is set to a value less than 2, recovery will be done serially, not in parallel. Note that other factors affecting recovery speedsuch as default buffer cache size, which is outside the scope of this articleare neither Oracle RAC-specific nor related to parallelism.
Answer A is incorrect because setting PARALLEL_MAX_SERVER to a nonzero value is not enough to guarantee that recovery will be done in parallel. If RECOVERY_PARALLELISM either defaults to or is set to 0 or 1, recovery will be done serially. Answer B is incorrect because an Oracle RAC recovery is performed by one of the surviving instances automatically and not by the DBAs issuing a recover command. Answer D is incorrect because RECOVERY_PARALLELISM defaults to CPU_COUNT - 1. On a machine with only one or two CPUs, this would mean that the default is for recovery to be serial, not parallel.
Virtual IP Addresses
Database clients use TNS descriptors to contact a TNS listener on one of the cluster nodes from a list of the listeners on all cluster nodes. TNS descriptors for Oracle RAC contain host names or IP addresses of the public network interfaces on all cluster nodes.
When database clients attempt connections to cluster databases, one of the available host names in the TNS descriptor is selected and a connection request is made. If the selected instance or listener on that host is unavailable, clients select another host name and try again until they succeed in connecting. Although this technique improves availability when instances or listeners are down, a network timeout is required to enable clients to detect unavailable nodes. Clients are therefore delayed by the TCP timeout period before they attempt to connect to another host name, resulting in slower network connection establishment and lower availability of connections.
To circumvent these network timeouts, Oracle Database 10g clusters use VIPs, which respond in one of two ways to connection requests made over the public network interfaces. While a cluster node is available, its associated VIP is active on that node and inbound connection requests are accepted by the listener. If a node becomes unavailable, its associated VIP is activated on one of the remaining cluster nodes by Oracle Clusterware, thereby enabling this other node to reject connection requests originally sent to the failed node. This rejection of connection requests results in immediate notification to the requesting client, which immediately selects another host name from the TNS descriptor. This results in faster network connection establishment and higher availability of connections. Which statements are true about VIPs?
A. VIPs always accept connection requests.
The correct answers are C and D. Answer C is correct because connecting through VIPs prevents delays from network timeouts. Answer D is correct because clients must resolve IP addresses of VIPs.
Answer A is incorrect because VIPs accept connections only when the VIP is used on its own node. Answer B is incorrect because Oracle Clusterware automatically relocates VIPs as required. Answer E is incorrect because VIPs are used for client connections made over public network interfaces, not over the interconnect, which is a private network for the cluster. Your company plans to switch from one internet service provider (ISP) to another ISP, which will result in new IP addresses for your Oracle RAC VIPs. What must you do to implement these new VIP addresses for your Oracle RAC cluster?
A. You don't need to do anything. Oracle Clusterware will discover the new VIPs automatically.
The correct answer is D.
A crucial element of the Oracle RAC high-availability architecture is support for most maintenance activities in a rolling fashion. This allows software on one node to be shut down for maintenance while other nodes continue to operate normally. Upon completion of the maintenance action, the node and the software are restarted and the same steps are then carried out serially on the remaining nodes.
Answer A is incorrect because Oracle Clusterware cannot discover the new VIPs automatically. Answer B is incorrect because Oracle Clusterware stores VIPs in its own metadata to avoid dependency on DNS. Answers C and E are incorrect because planned downtime would compromise the Oracle RAC high-availability architecture.
Oracle Clusterware File Management
The two important Oracle Clusterware file types are the Oracle Cluster Registry (OCR) and the voting disk. The OCR contains cluster configuration data such as public and private node names, database and instance names, IP and VIP addresses, node applications, and voting disk locations. The voting disk is a disk device or file that plays an important role during cluster reconfiguration activities such as nodes joining or leaving the cluster and public or private network failures. Multiplexing both the OCR and the voting disks can protect them against media failures, but making backups for disaster recovery is still an important activity for DBAs. While evaluating your company's backup-and-recovery strategy, your IT manager asks you to recommend a backup strategy for the Oracle Clusterware voting disk. When would you recommend that backups of the voting disk be done? (Choose all that apply.)
A. Never, because there is nothing inside the voting disk.
The correct answers are B and C. Answer B is correct because a new heartbeat area is created on the voting disk when a new node is added to the cluster. Answer C is correct because a node heartbeat area is removed from the voting disk whenever a node is removed from the cluster. Answer A is incorrect because each node has a heartbeat area on the voting disk. Answer D is incorrect because although Oracle Clusterware creates OCR backups automatically, it does not do so for the voting disk. Your system administrator informs you of a disk failure in your Oracle RAC cluster that necessitates a replacement disk. An OCR mirror was located on the failed disk and requires recovery. What is the best way to resolve this issue?
A. Copy the remaining OCR to the mirror location while the cluster is still running.
The correct answer is D. OCRCONFIG -REPLACE is the command for replacing the OCR mirror.
Answer A is incorrect because Oracle Clusterware prevents mirrors from being copied while they are in use. Answer B is incorrect because the backup OCR will be out of sync with the remaining OCR, and no logs exist to facilitate a roll forward. Answer C is possible but conflicts with high-availability objectives, because the cluster would need to be shut down, and is therefore not the best way to resolve the issue. Answer E is incorrect because the -REPAIR argument is used to repair a broken /etc/oracle/ocr.loc file that points to the OCR device location(s).
Harald van Breederode (email@example.com) is the Linux technical advisor to the Oracle Certification Exam development team and has worked for Oracle University Netherlands since 1999. Joel Goodman (firstname.lastname@example.org) is the Oracle Database technical advisor to the Oracle Certification Exam development team and has worked for Oracle University U.K. since 1997.