Recently, in one of the ongoing projects, I had to install and configure multiple Oracle Grid Infrastructure 11gR2 clusters for ensuring Database and applications high availability using Cold Failover solution. As you have probably guessed – databases were Single Instances (not RAC’s). When the cluster setup was done, I had to add databases as resources and create action scripts for controlling them (start, stop, relocate). So, as I started thinking about the scripts, I have pointed out some main requirements for them:
1) Scripts should be unified, simple and flexible – no hardcoded environment values there – as I have multiple databases running on some clusters, I want to use one script to maintain them all rather then allocate separate script per each db;
2) Database resources are controlled by root – that means only root will be allowed to start, stop and relocate them.
Basically, what I wanted to do – is to find a way for clusterware agent (a special entity for resource management) to pass Oracle common variables such as ORACLE_HOME, ORACLE_SID, etc. to the Action script.
By default there are three resource types in Grid Infrastructure 11gR2, which can be used for custom resource definition – local_resource, cluster_resource and application (I don’t count default ora.% types here, as these are reserved by clusterware stack ones). The first one is self explanatory and doesn’t suite in our case as we want to setup a failover solution. The second one looks more suitable per application, at least from the name (is that really true? we’ll see). Well, I had no more options left except for cluster_resource, so I started looking at the resource attributes. We can easily check them by adding a dummy resource without defining any attributes and then print out the status with “-p” option:
[root@gridnode01 ~]# crsctl add resource db.TEST -type cluster_resource [root@gridnode01 ~]# crsctl status resource db.TEST -p NAME=db.TEST TYPE=cluster_resource ACL=owner:root:rwx,pgrp:root:r-x,other::r-- ACTION_FAILURE_TEMPLATE= ACTION_SCRIPT= ACTIVE_PLACEMENT=0 AGENT_FILENAME=%CRS_HOME%/bin/scriptagent AUTO_START=restore CARDINALITY=1 CHECK_INTERVAL=60 DEFAULT_TEMPLATE= DEGREE=1 DESCRIPTION= ENABLED=1 FAILOVER_DELAY=0 FAILURE_INTERVAL=0 FAILURE_THRESHOLD=0 HOSTING_MEMBERS= LOAD=1 LOGGING_LEVEL=1 NOT_RESTARTING_TEMPLATE= OFFLINE_CHECK_INTERVAL=0 PLACEMENT=balanced PROFILE_CHANGE_TEMPLATE= RESTART_ATTEMPTS=1 SCRIPT_TIMEOUT=60 SERVER_POOLS= START_DEPENDENCIES= START_TIMEOUT=0 STATE_CHANGE_TEMPLATE= STOP_DEPENDENCIES= STOP_TIMEOUT=0 UPTIME_THRESHOLD=1h [root@gridnode01 ~]#
Nice, but I don’t see any attributes here that we could use as a variables for Database resource. Let’s get back to the application resource type now and check which attributes are used there.
[root@gridnode01 ~]# crsctl add resource db.TEST1 -type application [root@gridnode01 ~]# crsctl status resource db.TEST1 -p NAME=db.TEST1 TYPE=application ACL=owner:root:rwx,pgrp:root:r-x,other::r-- ACTION_FAILURE_TEMPLATE= ACTION_SCRIPT= ACTIVE_PLACEMENT=0 AGENT_FILENAME=%CRS_HOME%/bin/appagent AUTO_START=restore CARDINALITY=1 CHECK_INTERVAL=60 DEFAULT_TEMPLATE= DEGREE=1 DESCRIPTION= ENABLED=1 FAILOVER_DELAY=0 FAILURE_INTERVAL=0 FAILURE_THRESHOLD=0 HOSTING_MEMBERS= LOAD=1 LOGGING_LEVEL=1 NOT_RESTARTING_TEMPLATE= OFFLINE_CHECK_INTERVAL=0 OPTIONAL_RESOURCES= PLACEMENT=balanced PROFILE_CHANGE_TEMPLATE= REQUIRED_RESOURCES= RESTART_ATTEMPTS=1 SCRIPT_TIMEOUT=60 SERVER_POOLS= START_DEPENDENCIES= START_TIMEOUT=0 STATE_CHANGE_TEMPLATE= STOP_DEPENDENCIES= STOP_TIMEOUT=0 UPTIME_THRESHOLD=1h USR_ORATST_BIN= USR_ORATST_DIR= USR_ORATST_PARM= USR_ORATST_TEST= USR_ORA_ALERT_NAME= USR_ORA_CHECK_TIMEOUT= USR_ORA_CONNECT_STR= USR_ORA_DEBUG= USR_ORA_DISCONNECT= USR_ORA_FLAGS= USR_ORA_IF= USR_ORA_INST_NOT_SHUTDOWN= USR_ORA_LANG= USR_ORA_NETMASK= USR_ORA_OPEN_MODE= USR_ORA_OPI= USR_ORA_PFILE= USR_ORA_PRECONNECT= USR_ORA_SRV= USR_ORA_START_TIMEOUT= USR_ORA_STOP_MODE= USR_ORA_STOP_TIMEOUT= USR_ORA_VIP= [root@gridnode01 ~]#
The first part is absolutely identical, but the second part USR_ORA% is something new and looks like Oracle related attributes. All these attribute description could be found in application resource type template file:
[root@gridnode01 ~]# cd $CRS_HOME/crs/template [root@gridnode01 template]# tail -25f application.tdf # # interface names for VIP applications # # #!=========================== attribute: USR_ORA_IF type: string switch: -o oi default: required: no # # Services that supported by the instance resource # it is a space separated list # #!=========================== attribute: USR_ORA_SRV type: string switch: -o osrv default: required: no #
I went through all of these attributes, but unfortunately, I didn’t find the ones I was looking for. So I decided to find out if there is a possibility to add them. It turned out that we may add custom attributes only for application resource type. In cluster resource type this is not supported. I have even tried to build my custom resource type which was based on cluster resource type but this also didn’t worked there. What you will get upon trying to use any of the USR_ORA% or custom attributes there is CRS-0160: The attribute ‘USR_ORA_HOME’ is not supported in this resource type.
That’s why I have added three new attributes in the application template file as follows (It can be done by using same syntax for existing attributes):
[root@gridnode01 template]# tail -20f application.tdf #!=========================== attribute: USR_ORA_HOME type: string switch: -o ohome default: required: no #!=========================== attribute: USR_ORA_SID type: string switch: -o osid default: required: no #!=========================== attribute: USR_ORA_OWNER type: string switch: -o oown default: required: no
So what’s next? I am going to add Database resource defining these new custom attributes at creation time:
[root@gridnode01 ~]# crsctl add resource db.TEST -type application \ -attr "PLACEMENT=restricted, \ ACTION_SCRIPT=/u01/app/oragrid/126.96.36.199/crs/script/coldfailover/act_db.sh, \ HOSTING_MEMBERS=gridnode01 gridnode02, \ CHECK_INTERVAL=30, \ CARDINALITY=1, \ ACTIVE_PLACEMENT=0, \ AUTO_START=restore, \ DEGREE=1, \ RESTART_ATTEMPTS=1, \ USR_ORA_OWNER=oracle, \ USR_ORA_SID=TEST, \ USR_ORA_HOME=/u01/app/oracle/product/188.8.131.52" [root@gridnode01 ~]# crsctl status resource db.TEST NAME=db.TEST TYPE=application TARGET=OFFLINE STATE=OFFILE on gridnode01
The resource has been added successfully with custom attributes. Now we need to pass those to the action script. Luckily, we can now reference them using $_USR_attributename in scripts. So the only thing I should do is to export Oracle variables in the start of the script taking values from these custom attributes and sequentially use them in action commands:
#!/bin/bash export ORACLE_HOME=$_USR_ORA_HOME export ORACLE_SID=$_USR_ORA_SID export ORACLE_USER=$_USR_ORA_OWNER case $1 in "start") su - $ORACLE_USER ...
That fully did the trick I was aiming for. No matter how many database resources I make, I can use one script to operate them all. All environment specific variables are now “built-in” resource definition. This also makes possible to operate resources using root user and not specific database user. There is also a lot of other USR_ORA% default attributes which could ease our life and make scripts even better (It is worth reviewing them). Also, you can make as much custom attributes as you want for your specific application needs or resource specifications. The only bad thing I have found about this solution is that as per Documentation, Oracle does not recommend using application type resource. It is stated that this was the only resource type in earlier Clusterware releases and here, in 11gR2, it still exists only for backward compatibility. Please consider using cluster_resource instead. But in my opinion it has really limited set of attributes available. Maybe some changes will be in place in next release. Let’s hope for the best