Oracle RAC Administration - Part 6: Administering the Database Instances and Cluster Databases

October 5, 2006

Brief intro

Oracle has finally released its Oracle 10G Release 2 database for Solaris 10. I have been waiting for it for quite some time and I hope that they were able to fix the clusterware problems, which had had some issues. Anyway, I will test it on my ESX box, which has three ready baked Solaris 10 U1 Operating Systems.

OK moving ahead with our administration series, we will look at administering our database instances and cluster databases.

Tools needed for administering our instances

For every administration task, you need a steady and reliable set of tools. I have seen many tools (and I mean third party tools) but when it comes to taming the Oracle database, you should have a look at the tools discussed (briefly) below. It really doesn’t matter if you are administering a multi-node RAC farm or a single Oracle instance:

• Using SQL*PLUS
• Using Oracle Enterprise Manager
• Using SRVCTL (our powerful utility)

Managing Oracle Real Application Clusters with SQL*PLUS

We can all tell tales about the famed SQL*Plus tool, but check into it in the manual labeled Utilities. It's a great utility and is readily available with the installed Oracle base. Try to connect using the proper parameters, such as naming the instance, because by default, the SQL*Plus prompt does not identify the current instance. So you would typically do something like this if you need to connect to a different instance in SQL*PLUS:

CONNECT username/password@my_service_name. 

If you want to change the SQL*Plus prompt, so that it automatically includes the name of the current instance, you will have to type the following commands in your SQL*Plus:

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

Now, what this command will do is, it replaces the SQL string before the > symbol with the user variable _CONNECT_IDENTIFIER that should ideally display the current instance name for the time while you will be logged into your instance. We have also seen Tom Kyte’s script, which did that automagically since versions 8.x. All you have to do is enter the following text into your glogin.sql; this file can be found in your SQL*Plus directory:

SET SQLPROMPT '_CONNECT_IDENTIFIER> '

You can also add any other needed text or even a SQL*Plus user variable between the single quotes in the command. We all know that we can have several SQL*Plus sessions to the same instance. To perform any important tasks, you can log on as SYSOPER or SYSDBA. Administrative tasks such as instance shutdown, startup can be carried out with these admin privileges.

Managing Oracle Real Application Clusters with Enterprise Manager

This web-based tool has come of age and offers an enterprise class administration and RAC control, to manage a single Oracle RAC database. Enterprise Manager is the central tool that focuses on all possible aspects of our RAC service, providing a central point of control for the Oracle environment through a graphical user interface (GUI).

Oracle Enterprise Manager can be used to start, stop and monitor databases, RAC instances and the associated Listeners. There are schedule jobs, alert thresholds, altering schema, storage features and much more. We will dedicate more time to the Enterprise Manager, since it is a very useful tool, even for Grid control, meaning when administering a GRID.  In addition, Enterprise Manager can perform tasks on the fly, on multiple RAC databases.

Managing Oracle Real Application Clusters with SRVCTL

In a previous article, we did some work with the SRVCTL tool, which we touted as a powerful utility, and it is powerful, for many reasons. SRVCTL is the source to all of the feeds that go into the Enterprise Manager. The configurations, for instance, are all picked up from a list that SRVCTL generates when discovering and monitoring the nodes in our RAC. These configurations are all stored in our Oracle Cluster registry (OCR) file. In addition, Enterprise Manager uses SQL*PLUS to stop and start the instances. Of course, the Enterprise Manager uses many more tools (which we as DBAs and Developers use day to day) than just the above-mentioned tools, to arm you with a robust GUI.
So now it's time to move on to the more robust, enhanced and feature rich tools found in Enterprise Manager. As you can see, these tools are all independent and yet interdependent.

An Example : Starting/Stopping using various tools

We can use all the tools to perform the start/stop tasks, whether it Enterprise Manager, SQL*Plus or SRVCTL. Assuming that we are using the SPFILE, which is advisable anyway, lets do the following:

• Starting Up and Shutting Down with Enterprise Manager
• Starting Up and Shutting Down with SQL*Plus
• Starting Up and Shutting Down with SRVCTL

Make sure that your Clusterware is running. You will not be able to start your RAC (after you have brought it down for this exercise) if your CRS stack is not running properly. All of the processes must be running in place. The procedure for shutting down RAC instances is pretty much the same as performing that kind of exercise on a single-instance Oracle database. There are however some important points to note. Refer to the Admin manual for more information on shutting down the databases. The key differences are:

• Bringing down one of the many nodes will not affect the performance or the availability of the whole RAC service. These operations are very common when performing maintenance and upgrade tasks while the RAC instance and the core business application continue to run despite all shutdowns and restarts in the back-end.
• In order to shut down a RAC database completely or the RAC service completely you will have to perform that operation on each single node. The nodes will have the database in mounted or in open state.
• You do not have to worry about instance recovery, as you would in your single-instance Oracle database, after having done a NORMAL or IMMEDIATE shutdown. You must, however, perform recovery when you issue the SHUTDOWN ABORT command or if the instance or the applications terminated abnormally. The good part is that the node that is still up will perform the instance recovery for the nodes that were brought down abnormally. Moreover, it happens automatically when none are up, the first one automatically detects and recovers the ailing nodes.
• Doing SHUTDOWN TRANSACTIONAL LOCAL helps bringing down the local node after its transactions are all committed or rolled back. It does this in addition to the SHUTDOWN IMMEDIATE command. There is no interference of other nodes.

Conclusion:

In our next article, we will continue to perform the shut down and start up task using SQL*PLUS, Oracle Enterprise Manager and SRVCTL.

» See All Articles by Columnist Tarry Singh