Server Pool experiments in RAC 11.2

I spent Wednesday at UKOUG RAC & HA SIG and it was one of the best events I ever attended. Great  audience, and great feedback. One question I was particularly interested in was raised during my presentation, regarding server pools. I have now finally had the chance to experiment with this exciting new feature, my findings are in the blog post. I’ll see if the automatic assignment of nodes to pools works as advertised as well, but that’s for another post. Already this one turned out to be a monster!

Setup

I have installed a 3 node RAC cluster on Oracle Enterprise Linux 5 update 4 32bit to better understand server pools. I have read a lot about the subject, but as always, first hand experience pays off more than just reading. My environment uses GPnP, essentially it is the environment I described in part 2 of my build your own RAC 11.2 system, extended by another node.

So far, I have not yet created a database – my goal is to experiment with policy based and admin based databases. A quick check of the resources reveals:

[root@rac11gr2node3 rac11gr2node3]# crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
ora.LISTENER.lsnr
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
ora.asm
 ONLINE  ONLINE       rac11gr2node1            Started
 ONLINE  ONLINE       rac11gr2node2            Started
 ONLINE  ONLINE       rac11gr2node3            Started
ora.eons
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
ora.gsd
 OFFLINE OFFLINE      rac11gr2node1
 OFFLINE OFFLINE      rac11gr2node2
 OFFLINE OFFLINE      rac11gr2node3
ora.net1.network
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
ora.ons
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
ora.registry.acfs
 ONLINE  ONLINE       rac11gr2node1
 ONLINE  ONLINE       rac11gr2node2
 ONLINE  ONLINE       rac11gr2node3
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
 1        ONLINE  ONLINE       rac11gr2node2
ora.LISTENER_SCAN2.lsnr
 1        ONLINE  ONLINE       rac11gr2node3
ora.LISTENER_SCAN3.lsnr
 1        ONLINE  ONLINE       rac11gr2node1
ora.gns
 1        ONLINE  ONLINE       rac11gr2node1
ora.gns.vip
 1        ONLINE  ONLINE       rac11gr2node1
ora.oc4j
 1        OFFLINE OFFLINE
ora.rac11gr2node1.vip
 1        ONLINE  ONLINE       rac11gr2node1
ora.rac11gr2node2.vip
 1        ONLINE  ONLINE       rac11gr2node2
ora.rac11gr2node3.vip
 1        OFFLINE OFFLINE
ora.scan1.vip
 1        ONLINE  ONLINE       rac11gr2node2
ora.scan2.vip
 1        ONLINE  ONLINE       rac11gr2node3
ora.scan3.vip
 1        ONLINE  ONLINE       rac11gr2node1

Now which server pools do we have after a fresh installation? The crsctl command has received approx 1000 new combinations for command line options, the server pool can be queried as follows:

[root@rac11gr2node3 rac11gr2node3]# crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2 rac11gr2node3

NAME=Generic
ACTIVE_SERVERS=

This matches the $GRID_HOME/log/nodename/alertnodename.log file – each server that came up was assigned to the free pool. The crsctl status serverpool command has a number of options demonstrated below.

Static configuration:

[root@rac11gr2node3 rac11gr2node3]# crsctl status serverpool -p
NAME=Free
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r-x

NAME=Generic
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:r-x,pgrp:oinstall:r-x,other::r-x

Runtime Configuration

[root@rac11gr2node3 rac11gr2node3]# crsctl status serverpool -v
NAME=Free
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2 rac11gr2node3

NAME=Generic
ACTIVE_SERVERS=

Full configuration:

[root@rac11gr2node3 rac11gr2node3]# crsctl status serverpool -f
NAME=Free
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r-x
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2 rac11gr2node3

NAME=Generic
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:r-x,pgrp:oinstall:r-x,other::r-x
ACTIVE_SERVERS=

[root@rac11gr2node3 rac11gr2node3]#

I should probably mention the ACL, which plays into the role separated management.

1. owner -> software owner
2. pgrp  -> primary group
3. other -> other
4. rwx   -> priviliges to read, modify/delete and execute

ACL are further explained in Oracle Clusterware Administration and Deployment Guide, Appendix B. Don’t worry if they are set incorrectly, you can use crs_getperm and crs_setperm to modify them all.

crsctl also replaces the crs_stat executable in many respects. To display a resource’s profile, you use the crsctl status syntax, optionally with the -p or -v option for static/runtime information. The above “crsctl status resource -t” replaces crs_stat -t and is much more readable.

[root@rac11gr2node3 rac11gr2node3]# crsctl status resource ora.scan2.vip -p
NAME=ora.scan2.vip
TYPE=ora.scan_vip.type
ACL=owner:root:rwx,pgrp:root:r-x,other::r--,group:oinstall:r-x,user:oracle:r-x
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=scan_vip)
DEGREE=1
DESCRIPTION=Oracle SCAN VIP resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=
LOAD=1
LOGGING_LEVEL=1
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=balanced
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=0
SCAN_NAME=rac-scan.rac.the-playground.de
SCRIPT_TIMEOUT=60
SERVER_POOLS=*
START_DEPENDENCIES=hard(ora.net1.network) dispersion:active(type:ora.scan_vip.type) pullup(ora.net1.network)
START_TIMEOUT=0
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(ora.net1.network)
STOP_TIMEOUT=0
UPTIME_THRESHOLD=1h
USR_ORA_ENV=
USR_ORA_VIP=192.168.1.252
VERSION=11.2.0.1.0

Now let’s try and add a server pool. The crsctl command seems to be the correct choice for this (spoiler: it’s not! You can skip to the srvctl add srvpool command or read on how I learnt the hard way). First of all, the crsctl options are explained by executing crsctl operation object -h, i.e.

[root@rac11gr2node3 rac11gr2node3]# crsctl add serverpool -h
Usage:
 crsctl add serverpool <spName> [[-file <filePath>] | [-attr "<attrName>=<value>[,...]"]] [-i]
where
 spName          Add named server pool
 filePath        Attribute file
 attrName        Attribute name
 value           Attribute value
 -i              Fail if request cannot be processed immediately
 -f              Force option

Interesting, but not self explanatory so I still needed the documentation. The clusterware Administration and Deployment Guide has all the information I needed in appendix E “CRSCTL Utility Reference”. So I decided to create the server pool from a parameter file:

[root@rac11gr2node3 rac11gr2node3]# cat /tmp/sp_martin
IMPORTANCE=999
MIN_SIZE=1
MAX_SIZE=2
SERVER_NAMES=rac11gr2node1,rac11gr2node2
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:r-x,pgrp:oinstall:r-x,other::r-x

I initially omitted the ACL line but ended up having the resource owned by root which I didn’t want. So to check if that was successful, I ran the status command again:

[root@rac11gr2node3 rac11gr2node3]# crsctl stat serverpool -p
NAME=Free
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r-x

NAME=Generic
IMPORTANCE=0
MIN_SIZE=0
MAX_SIZE=-1
SERVER_NAMES=
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:r-x,pgrp:oinstall:r-x,other::r-x

NAME=martin
IMPORTANCE=999
MIN_SIZE=1
MAX_SIZE=2
SERVER_NAMES=rac11gr2node1,rac11gr2node2
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:r-x,pgrp:oinstall:r-x,other::r-x
[root@rac11gr2node3 rac11gr2node3]#

Look ma, I’ve got a server pool! Now off then to create a database. But – what? dbca didn’t detect a server pool when trying to create a policy managed database.

Bug? Something wrong with my configuration? Don’t shout “bug” too quickly, let’s first ensure I didn’t do anything stupid. Is the server pool active and registered?

[root@rac11gr2node3 ~]# srvctl status srvpool
Server pool name: Free
Active servers count: 3
Server pool name: Generic
Active servers count: 0

Well I don’t have any servers assigned. But crsctl doesn’t seem to allow you to manually assign servers, that’s happening under the covers. Hmm, I was really stumped, none of the logs provided any clue. Maybe there’s something wrong with my approach? Start from scratch, as so often:

[root@rac11gr2node3 ~]# crsctl delete serverpool martin

Maybe that’s not what I want… Now I was annoyed and cheated-ran dbca and let it generate scripts. And sure enough, the master SQL script called srvctl to create the server pool… I can do that, too! Again I got the syntax first:

[root@rac11gr2node3 ~]# srvctl add srvpool -h

Adds a server pool to the Oracle Clusterware.

Usage: srvctl add srvpool -g <pool_name> [-l <min>] [-u <max>] [-i <importance>] [-n "<server_list>"]
 -g <pool_name>           Server pool name
 -l <min>                 Minimum size of the server pool (Default value is 0)
 -u <max>                 Maximum size of the server pool, -1 for unlimited maximum size (Default value is 0)
 -i <importance>          Importance of the server pool (Default value is 0)
 -n "<server_list>"       Comma separated list of candidate server names
 -f                       Force the operation even though some resource(s) will be stopped
 -h                       Print usage

That’s simple enough – with what I know, I easily created a server pool for my first 2 nodes and gave it a max importance – 1.

[root@rac11gr2node3 ~]# srvctl add srvpool -g martin -l 1 -u 2 -i 999 -n rac11gr2node1,rac11gr2node2
[root@rac11gr2node3 ~]# echo $?
0

Did it work?

[root@rac11gr2node3 ~]# srvctl status srvpool
Server pool name: Free
Active servers count: 1
Server pool name: Generic
Active servers count: 0
Server pool name: martin
Active servers count: 2
[root@rac11gr2node3 ~]#

Ah, that’s better! I have 2 servers allocated. I can get even more information with the “-a” flag.

[root@rac11gr2node3 ~]# srvctl status srvpool -g martin -a
Server pool name: martin
Active servers count: 2
Active server names: rac11gr2node1,rac11gr2node2
NAME=rac11gr2node1 STATE=ONLINE
NAME=rac11gr2node2 STATE=ONLINE
[root@rac11gr2node3 ~]#

The crsctl output is similar:

[root@rac11gr2node3 ~]# crsctl stat serverpool
NAME=Free
ACTIVE_SERVERS=rac11gr2node3

NAME=Generic
ACTIVE_SERVERS=

NAME=ora.martin
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2

[root@rac11gr2node3 ~]#

Spot the difference: it’s now “ora.martin” as resource name! BTW, that prefix is reserved for Oracle’s own resources. The full configuration can be viewed as always, and I spotted a problem with the ACL:

[root@rac11gr2node3 ~]# crsctl stat serverpool ora.martin -f
NAME=ora.martin
IMPORTANCE=999
MIN_SIZE=1
MAX_SIZE=2
SERVER_NAMES=rac11gr2node1 rac11gr2node2
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:root:rwx,pgrp:root:r-x,other::r--
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2

DBCA, executed as oracle doesn’t recognise the server pool because oracle doesn’t have the necessary permissions.

Instead of dropping the server pool I opted to change the permissions using crs_setperm and crs_getperm:

[root@rac11gr2node3 ~]# crsctl getperm serverpool ora.martin
Name: ora.martin
owner:root:rwx,pgrp:root:r-x,other::r--
[root@rac11gr2node3 ~]#

Get the syntax:

[root@rac11gr2node3 ~]# crsctl setperm serverpool -h
Usage:
 crsctl setperm serverpool <spName> {-u <aclstring>|-x <aclstring>|-o <user_name>|-g <group_name>}
where
 -u           Update server pool ACL
 -x           Delete server pool ACL
 -o           Change server pool owner
 -g           Change server pool primary pool

 ACL (Access Control List) string:

 { user:<user_name>[:<readPerm><writePerm><execPerm>]   |
 group:<group_name>[:<readPerm><writePerm><execPerm>] |
 other[::<readPerm><writePerm><execPerm>] }
where
 user         User ACL
 group        Group ACL
 other        Other ACL
 readPerm     Read permission ("r" grants, "-" forbids)
 writePerm    Write permission ("w" grants, "-" forbids)
 execPerm     Execute permission ("x" grants, "-" forbids)

[root@rac11gr2node3 ~]#

With that it’s easy enough to change the own to oracle.

[root@rac11gr2node3 ~]#crsctl setperm serverpool ora.martin -o oracle
[root@rac11gr2node3 ~#]crsctl setperm serverpool ora.martin -g oinstall

[root@rac11gr2node3 ~]# crsctl stat serverpool ora.martin -f
NAME=ora.martin
IMPORTANCE=999
MIN_SIZE=1
MAX_SIZE=2
SERVER_NAMES=rac11gr2node1 rac11gr2node2
PARENT_POOLS=
EXCLUSIVE_POOLS=
ACL=owner:oracle:rwx,pgrp:oinstall:r-x,other::r--
ACTIVE_SERVERS=rac11gr2node1 rac11gr2node2

With that done dbca now recognises the pool:

The next part of the series will detail the steps how to create a policy managed database and uniform/singleton services.