Oracle RAC Administration - Part 10:The RAC DBA
December 14, 2006
Since my last article, quite a few developments have taken place. Oracle has not only continued its onslaught on Red Hat but also opened up its own version of Enterprise Linux for download. Larry Ellison did mention in one of his older interviews that buying Red Hat would not add much value to their stack, but at the same time, he missed that essential part of his Oracle Stack, the OS! So here you go, you have your own Enterprise Linux! I have also started building my own Oracle Linux RAC.
Actually, in a way, it might just be a bit better. I just have to look at one supplier, since the service/application stack (like RAC and other Oracle services) are crucial to me, and other OS related issues can also be taken care of by the same supplier (i.e. Oracle). It certainly means that the Oracle support staff would need to be more effective. Sure, the OS is very important but very soon an OS will be just a layer; it might even be irrelevant if you listened to Diane Greene in VMworld 2006!
You as the RAC DBA
In previous articles, we have gone through some advanced material in RAC administration (which we will certainly continue) because I want to keep you people glued to the exciting world of RAC. However, from time to time we must step back and review some of the RAC basics. In this article, we will do Just that.
What is an spfile?
Pretty simple, it is the file that your instance reads from when starting up. To check the parameters check the following:
SQL> show parameter spfile
Viewing of the SPFILE can be done with several commands, such as CAT/MORE (in Linux) or your good old notepad in Windows. Since it is a binary file, (you must have found out when you tried to open it), you have to submit an ALTER SYSTEM SET command. This is for safety and can be irritating, as you cannot edit it like your plain old pfile. In your RAC environment, all instances must share the same spfile (as seen in the step where we choose the OCFSed mount point).
Modifying the parameters:
In your RAC, the SID option is the important part of changing the parameters. There, you must do something like this:
ALTER SYSTEM SET db_cache_size = 1024m scope=spfile sid=nick01;
This would show up in your show parameter command as:
nick01.db_cache_size=1024 nick02.db_cache_size=500 *.job_queue_processes=4
As you observe, the *. applies to all nodes in the RAC cluster, so those parameters prevail universally across the RAC environment.
More RAC specific parameters:
Let's check out a few more parameters that are present in our RAC environment (there are, however, a lot more which we will get in touch with in future articles):
Many things happen in the RAC environment. You might want to install an NIC card to your environment and replace the current IPC settings to use this card. A typical example is: replacing a 1G IPC traffic related card with a 10G card. Whatever the reason, things happen in your RAC environment and you will need to address the cluster interconnection aspects should these changes have a direct impact on your RAC settings. Then you might want to do this on each node:
ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.0.0.25' scope=spfile sid='nick01'; ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.0.0.26' scope=spfile sid='nick02';
Alternatively, you can be sitting on a third node like on our brian01, which is our third node, and carry these operations out like this:
ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.0.0.25:172.22.202.25' scope=spfile sid='nick01'; ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.0.0.26:172.22.202.26' scope=spfile sid='nick02';
Obviously, if I were you (or if you were a diligent DBA), I would quickly check the alert log to see if the changes were effective. You should also have an option to generate a trace file using the ORADEBUG utility (we will also talk about it sometime later):
SQL> oradebug setmypid
SQL> oradebug ipc
Now this trace file is written in the udump directory (I would arrange the files in the list with the last modified file on top). Open the file with gedit, vi or notepad to see the socket information, ip and udp etc.
Note: You can also edit the NIC information with the oifcfg command. We will check that out in our next article.
This option needs to be set to TRUE for obvious reasons in RAC operation, BUT there are exceptions when you have to patch. Say you have to shutdown all the nodes except one, then you set the parameter for this node to FALSE and carry on with the operations of patching/upgrading the instance.
This is used in a 2-node RAC. It enables you to assign one instance in a two-instance RAC cluster as the primary instance and the other instance as the secondary instance. So obviously, it has no use in a RAC that consists of more than two nodes.
It is pretty simple. You do:
ALTER SYSTEM SET ACTIVE_INSTANCE_COUNT= 1
The first starting node becomes the primary node and the other one is the secondary node. This means that the primary node goes ahead and accepts client connections and should that fail then the secondary node takes over the failed connections from the first node, thus making the secondary node the primary node. Should the failed primary node come back online then it starts operating as the secondary node and will not accept client connections unless the (current) primary node has failed.
In the next article, we will continue our RAC DBA essentials, covering more of these processes and the oifcfg utility as well. We will also talk a bit about the test/development and staging environment and how you can use the (now currently in beta), VMwares converter utility to simple push click your production environment into a fully functional virtualized RAC environment. This is an easy and even smart thing to offer to your boss. It saves money and your DBAs stay abreast of all the wicked stuff that happens on the net and allows them to contain it on a virtualization stack before it hits production.