Thoughts on Adding a Single Instance To Oracle Clusterware (Grid Infra).

The Oracle database can easily be configured to use the Cluster ware framework for high availability.

Using Grid Infrastructure to protect a database resource is a very cost-effective way of setting up an Active/passive cluster. As an added advantage, using only one vendor’s software stack to implement the cluster can make troubleshooting easier. Staff already familiar with RAC will easily be able to set up and run this configuration because it uses a same software stack: all commands and log files are in familiar locations, and troubleshooting does not rely on the input from other teams.

To set up an active/passive cluster with 11g Release 2, you need to initially install Grid Infrastructure on all nodes of the cluster. Grid Infrastructure will give the user with a cluster logical volume manager: ASM. If for some reason another file system is required, you can choose the supported cluster file systems, including ACFS. Using a cluster file system that is mounted concurrently to both cluster nodes offers the advantage of not having to remount the database files and binaries in case of a node failure. Some configurations we saw suffered from extended fail over periods caused by required file system checks before the file system could be remounted.

On top of the Grid Infrastructure build, you do a local installation of the RDBMS. It is important that you do not choose a cluster installation when prompted so; otherwise, you risk violating your license agreement with Oracle.

After the binaries are installed and patched according to your standards, you need to create an ASM disk group or OCFS2/GFS mount point to store the database files. Next, start-up the database configuration assistant from the first node to create a database. Please make sure that you store all the data files in ASM or the clustered file system. The same applies for the Fast Recovery Area: it should be on shared storage, as well.

TIP: After the database is created by dbca, it is automatically registered in the OLR. The profile of the resource is a good reference for the resource profile to be created in the next step. It is a good idea to save the resource profile in a safe place. You can extract the profile with the crsctl status resource ora.databaseName.db -p command. This will create a text file containing valuable information needed for setting up a configuration in the Clusterware that will have to be tailored to your specific needs.

This Note was made with the given situation that there was a full Rac environment in place that needed to be replaced by the solution we are discussing in this note. And we were not allowed to use Rac_One at that time. Today i would recommend to check if Rac_One is an option to you ( check your Licenses !) before continue.

Summary of things needed:

In order to make things work you might need to take care of this shopping list first before starting:

Needed

For

Root access

In this solution we also automate the fail over of the HP Open view monitoring. Stopping and starting that agent needs root access. And I suggest it to be password less.

Cooperation with HP Openview expert

If they are not involved, Open view will still look at wrong node after failover. They can help automate the failover of monitoring!

Extra IP addresses

If a node dies, or has maintenance, ALL resources will fail over. This means that schedules in maestro could go wrong, connecting to the wrong node. I have requested extra IPs per database to have max. Flexibility.

Action script

To be implemented in same place on each server participating in this concept.

Identical Infra structure

All links / subdirectory in place in same place on all nodes where a specific database could fall over to; Tnsnames equal.

Rac Clusterware 11.2

This Solution has been implemented and tested in a 11GR2 environment. If you need setup under 11.1 crs you will have to use different approach as is documented in SingleInstance DB Fileover in 11gr1 Clsuterware.pdf.

Which Method to use?

Frankly there are three scenarios that can be followed and there is not a single solution fit all situation.

Removing database from the Grid infrastructure (and add application resources ( database will be one of them) back to the cluster again. Pro: naming convention in the resources. Con: More maintenance needed cause resources like database need b removed before you can add again ,

Add the vip address and a dedicated listener to the clusterware in 11.2 environment (aka the Italian way cause that is how it is performed there). Pro: Register new resources ( vip per db and dedicated local listener to cluster and alter the db resources ). Con. Due to naming convention you still use ora. Resource for the database which might be confusing because it is not a Rac.

Add the vip address and a dedicated listener to the clusterware in 11.1 creating 5 resources among them listener , vip , database , start and stop resource and use crs_register command to add them to the cluster. Pro: this has been done on several clusters already and is working both under crs 11.1 and 11.2.

Note When I first implemented it I have used the method “removing databases from the grid infra and that worked well, given the fact that this was a 11.2 environment with a scan listener in place. However this method was not completed ( since I do not have a failover vip address and a local listener in place).

Happy Reading ,

Mathijs

Details:

1. Removing Databases from the Grid Infra structure (Cluster ware)

IF the database(s) in scope are part of a Rac at the moment, In order to remove the database resource called (TEST) in this document from the OLR, as shown in the following example (this example assumes the database is named TEST):

[oracle@london1 ~]$ srvctl remove database -d TEST

Setting up an action script

Action Script location:

Next, we to agree where we will put a Mandatory Script and of course what we want to be part of this script. As A default on all boxes, I have used: oracle@MYSRVR04hr:/opt/crs/product/112_ee_64/crs/crs/public. Indeed buried deep in the heart of the oracle cluster ware software.

Action Script contents:

As a mandatory component of the solution we need an action script that allows the framework to start, stop, and check the database resource. Since every database becomes an “Application resource” every database will have an Action script of its own!

If the clusterware relocates a database (performs a failover), internally this script is running with a parameter:

stop in this action script that will perform following steps:

sudo su – -c “/var/opt/OV/bin/instrumentation/dbspicol OFF $ORACLE_SID; rm /var/opt/OV/log/OpC/PMON_oracle_${ORACLE_SID}.flag” Note: as root this will stop the openview agent, and it will remove an agreed touch file that is being used to show that the database is active on this node.

Then using sqlplus it will stop the database.

Start in this action script that will perform following steps:

mv /opt/oracle/ MYDB1/diag/rdbms/mydb1/MYDB1/trace/alert_$ORACLE_SID.log /opt/oracle/$ORACLE_SID/admin/Arch/alert_${ORACLE_SID}.log.${CURRENT_TIMESTAMP}. Note: This will move away the old alertfile on the Node we are failing over to (in other words on the server where the database was NOT active).

$ORACLE_HOME/bin/sqlplus /nolog. Note: Using Sqlplus this is used to start the database on the new node (server)

sudo su – -c “/var/opt/OV/bin/instrumentation/dbspicol ON $ORACLE_SID;touch /var/opt/OV/log/OpC/PMON_oracle_${ORACLE_SID}.flag” Note: As the root user this will start the Openview agent, and create a touch file as proof that this is the node where the database is alive.

Note: Before adding this Action script to the cluster ware you could (should test it). Simply run it with <scriptname> stop or <scriptname> start.

In my project we made the script (per Database) look very much like this Every action script is named like this (ora.<Dbname>.active) :

Note: This action script defines environment variables in the header section, setting the Oracle owner, the Oracle SID, and the Oracle home. It then implements the required start, stop, clean, and check entry points. The check could be more elaborate—for example, it could check for a hung instance—however, this example was kept short and simple for the sake of clarity.

The action script needs to be deployed to the other cluster node as well, and it must be made executable.

Whenever there is a change to the script, the action script needs to be synchronized with the other cluster nodes!

Defining the Cluster Resource:

After you finished defining the mandatory action script, you now need to create a new kind (type) of cluster resource which will be added to the cluster ware (or Grid infra-structure if you like). Securing an Oracle database instance with Clusterware is simplified by the availability of the SCAN listener in this case because users of the database do not need to worry about which node the database is currently started on because the SCAN abstracts this information from them. The communication of the SCAN with the local listener also makes a floating virtual IP (which other cluster stacks require) unnecessary.

Using some values of the resource profile saved earlier (remember You can extract the profile with the crsctl status resource ora.databaseName.db -p command. This will create a textfile containing valuable information needed for setting up a configuration in the Clusterware that will have to be tailored to your specific needs), you need to configure the resource profile for the cluster resource next. Note: It is easier to use a configuration file than to supply all the resource parameters on the command line in name-value pairs. To recreate the database cluster resource, you could use the following configuration file, which is saved as TEST.db.config:

The preceding configuration file can be read as follows. Placement and hosting members go handin-hand; the restricted policy only allows executing the resource on the hosting members MYSRVR05HR and MYSRVR04HR london2. The check interval of 10 seconds determines the frequency of checks, and setting active placement 0 prevents Oracle from relocating the resource to a failed node; a failed node could indicate a second outage in the system, and it would be better to let the DBAs perform the switch back to the primary node. The cardinality specifies that there will always be one instance of this resource in the cluster (never more or less); similarly, the degree of 1 indicates that there cannot be more than one instance of the resource on the same node. The parameters restart attempts and action script is self-explanatory in this context. Please note that the directory /opt/crs/product/112_ee_64/crs/crs/public/was used to store the action script.

Note: ACL=owner:oracle:rwx,pgrp:dba:rwx,other::r– sets the priviliges in order to make sure that the oracle user is able to perform the actions to ( relocating ) a database.

Adding the resource to the cluster:

Next, use the following command to register the resource in Grid Infrastructure:

If you get a CRS-2518 (invalid directory path) error while executing this command, you most likely forgot to deploy the action script to the other node.

Note: You might be tempted to use the ora.database.type resource type here. Unfortunately, using this resource type repeatedly caused core dumps of the agent process monitoring the resource. These were in $GRID_HOME/log/hostname/agent/crsd/oraagent_oracle.

Tip: The permissions on the resource at this moment might be too strict (in our configuration we took care of that , but if you forget to do that it could happen that at this moment, only root could effectively modify the resource at this time. Trying to start the resource as the oracle account results in a failure, as in this example:

[oracle@london1 ~]$ crsctl start resource TEST.db

CRS-0245: User doesn’t have enough privilege to perform the operation

CRS-4000: Command Start failed, or completed with errors.

You can confirm the cause of this failure checking the permissions:

[root@london1 ~]# crsctl getperm resource TEST.db

Name: TEST.db

owner:root:rwx,pgrp:root:r-x,other::r–

You would like the oracle user to also be able to start and stop the resource; you can enable this

level of permission using the crsctl setperm command, as in the following example:

[root@london1 ~]# crsctl setperm resource TEST.db -o oracle

[root@london1 ~]# crsctl getperm resource TEST.db

Name: TEST.db

owner:oracle:rwx,pgrp:root:r-x,other::r–

The preceding snippet allows users logging in as oracle (or using sudo su – oracle) to start and stop the database, effectively transferring ownership of the resource to the oracle account. You need to ensure that the oracle account can execute the action script; otherwise, you will get an error when trying to start the resource. Members of the oinstall group have the same rights. All other users defined on the operating system level with privileges to execute the binaries in $GRID_HOME can only read the resource status.

Final Tips:

TIP: The final preparation steps are to copy the password file and pfile pointing to the ASM spfile into the passive node’s $ORACLE_HOME/dbs directory. You are in 11.2, so the ADR will take care of all your diagnostic files. You need to create the directory for your audit files however, which normally is in $ORACLE_BASE/admin/$ORACLE_SID/adump. Make sure links, directories, tnsnames.ora are also the same on the nodes.

BIGGEST PossibleTIP:. YOU need to keep Action Scripts in sync and with same privs on all nodes where you want to fail over to… So changing the action script means TEST it and if it works: Copy it across to the new Environment.

Once you have implemented the Solution:

Relocating a resource:

From this point on, you need to use crsctl {start|stop|relocate} to manipulate the database.

Tip:

Always set your environment to the Grid infra-structure before starting to work!

Always check the naming convention of the specific resource you want to work with :

crsctl status resource -t

When you have identified the resource ( for example mydb1) : app.mydb1.db

Check the resource

crsctl status resource app.mydb1.db

Then you can relocate the mydb1 with:

crsctl relocate resource app.mydb1.db

and check it with :

crsctl status resource app.mydb1.db

2. Add the vip address & dedicated listener to the clusterware(11.2)

In this scenario most of the resources in the clusterware (GI) remain in place, only new resources are added for a dedicated listener and a vip address. AS mentioned in the requirements you will need to have a dedicated VIP address for the database, that vip needs to be used in the listener configuration. SSH in order to backups or to perform schedules should be using the VIP !

3. Add the vip address and a dedicated listener to the clusterware in 11.1

This Scenario has been implemented on various servers already and it is working both under 11.1 Crs and 11.2 GI which makes it a good candidate as a default. It consists of 5 .Cap files which contain settings for the cluster. IN those 5 cap files 3 . pl scripts are being used. All activities to register have been copied to a .txt file which we will add here.