Using Quality of Service to manage Server Pool Policies

Today I will continue my series of blog posts about Oracle Grid Infrastructure 12.1.0.2 and the possibilities of automatic management. The previous posts can be found herehere and here. Now I will proceed and show how to setup and activate the Quality of Service Management that was introduced with 12.1.0.2.

1. Prepare Clusterware

In order to enable QoS for the cluster, we need to set a password for the QoS administrator. To do that, OC4J needs to be stopped.

[oracle@vm101 ~]$ srvctl status oc4j
OC4J is enabled
OC4J is running on node vm102

[oracle@vm101 ~]$ srvctl stop oc4j

Now we can set the password:

[oracle@vm101 ~]$ qosctl qosadmin -setpasswd qosadmin
New password:
Confirm new password:
User qosadmin modified successfully.

Optionally, we can also add more administrative users for QoS management:

[oracle@vm101 ~]$ qosctl qosadmin -adduser mmischke
QoS administrator password:
New password:
Confirm new password:
User mmischke added successfully.

Now that the password is set, OC4J can be restarted again activating the changes:

[oracle@vm101 ~]$ srvctl start oc4j
[oracle@vm101 ~]$ srvctl status oc4j
OC4J is enabled
OC4J is running on node vm101

2. Enable QoS Management at Database level

Each database running in the cluster must be enabled for QoS. This can be done using Cloud Control. Simply navigate to the home page of the cluster database target and follow these steps.

We can check the current status of the target by clicking on the information link right beside the target name:

qos-disabled

Since the current QoS Status is Disabled, we select “Enable/Disable Quality of Service Management” in the “Availability” Menu:

qos-enable-2

This brings up the following page where we specify all the required credentials for the cluster itself and the database:

qos-enable-3

Once this is done, click “Login”. Next step is to specify a password for the APPQOSSYS database user:

qos-enable-4

Clicking “OK” finishes the process and the target information now shows up with QoS Status as “Enabled”.

qos-enabled

That’s it for the database. If you plan to create several databases, you may consider scripting this process. MOS Note 2001997.1 has more information about that.

3. Create Policy Set

The last step is to create a Policy Set. Therefore navigate to the home page of the Cluster target and select “Create Policy Set” from the “Adminstration” menu.

create-policyset-1

This will start the assistant where you first have to specify the QoS administator credentials that you set up in the first step.

create-policyset-2

Maybe this results in an error like this:

create-policyset-1-fail

This is because Cloud Control uses the short hostname for whatever reason which cannot bet resolved. I worked around that by adding the cluster hosts to /etc/hosts. After that the login was successful and the assistant continues. The existing server pools will show up. Select “Manage” for all of them:

create-policyset-3a

Go through all the following steps by clicking “Next” until you reach the “Set Policy” page. Click “Set Policy” to make the selected policy the active one.

create-policyset-7a

Again click “Next” which will bring up the “Review” page where you finally click “Submit Policy Set” to finish the process.

create-policyset-8

Now the “Dashboard” page is shown where we see the QoS Status is still “Disabled”.

create-policyset-9-finish

Click on the “Disabled” link:

create-policyset-10-finish

Click on “Enable QoS Management”:

create-policyset-11-finish

Done. That’s it.

4. Create another Policy Set and edit it

You may want to have more than one policy set to match different situations. The easiest way is to create a copy of the existing policy. Select “Edit Policy Set” from the “Administration” menu of the cluster target home page.

edit-policyset-1

On the following page click “Copy Policy”:

edit-policyset-2

Now we can change all the settings. First, set a suitable name for the new policy. Then set all the other parameters to match your needs. For my example I changed the ranking. Then click “OK”.

edit-policyset-3

Now, I copied this new policy again in order to change the importance to match the night time requirements:

edit-policyset-4

So I end up with these three policies:

edit-policyset-5

Click “Next” until you reach the “Set Policy” page. Select the policy that should be used initially and click “Set Policy”.

edit-policyset-6

The change will be reflected as shown:

edit-policyset-6a

Again click “Next” to bring up the “Review” page:

edit-policyset-7

And click “Submit Policy Set” to finally enable all the changes:

edit-policyset-8

Now you can change between your policies by simply clicking “Change Active Policy”.

5. Further Information

Database Quality of Service Management User’s Guide

How to enable QoS Management functionality in a database without using EM Cloud Control (Doc ID 2001997.1)

Oracle RAC SIG – Ensuring Your Oracle RAC Databases Meet Your Business Objectives at Runtime

Advertisements

Changing Serverpool Configuration with Policy Sets

Preface

Today I want to continue my series of posts about Oracle GridInfrastructure and server pool management. It started a while ago with this post. It describes the general functionality of serverpools. Since 12.1 there is a new feature called policy sets which allows us to have more than only one configuration of server pools. I will now guide you through the basic steps to create and use policies.

Basic Setup

First, this is my configuration. Two RAC databases and two serverpools named “gold” and “silver” to match their importance. A total of four servers in the cluster.
Database one:

[oracle@vm101 ~]$ srvctl config database -db rac01
Database unique name: rac01
Database name: rac01
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/RAC01/PARAMETERFILE/spfile.274.884701253
Password file: +DATA/RAC01/PASSWORD/pwdrac01.256.884699023
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: gold
Disk Groups: DATA,RECO
Mount point paths:
Services: gold_svc
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed

And it’s service:

[oracle@vm101 ~]$ srvctl config service -db rac01 -service gold_svc
Service name: gold_svc
Server pool: gold
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Service is individually enabled on nodes:
Service is individually disabled on nodes:

And the second database pretty similar:

[oracle@vm101 ~]$ srvctl config database -db slv01
Database unique name: slv01
Database name: slv01
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/SLV01/PARAMETERFILE/spfile.297.899123185
Password file: +DATA/SLV01/PASSWORD/pwdslv01.282.899122825
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: silver
Disk Groups: RECO,DATA
Mount point paths:
Services: silver_svc
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed

And the service for the second database:

[oracle@vm101 ~]$ srvctl config service -db slv01 -service silver_svc
Service name: silver_svc
Server pool: silver
Cardinality: UNIFORM
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Service is individually enabled on nodes:
Service is individually disabled on nodes:

Creating a Policy Set

In order to create a policy set we basically generate a configuration file from the current setup.

[oracle@vm101 ~]$ crsctl create policyset -file ./default.policyset

[oracle@vm101 ~]$ cat default.policyset
SERVER_POOL_NAMES=Free ora.gold ora.silver
POLICY
  NAME=Default
  SERVERPOOL
    NAME=ora.gold
    IMPORTANCE=10
    MAX_SIZE=3
    MIN_SIZE=1
    SERVER_CATEGORY=ora.hub.category
  SERVERPOOL
    NAME=ora.silver
    IMPORTANCE=8
    MAX_SIZE=3
    MIN_SIZE=1
    SERVER_CATEGORY=ora.hub.category

As you can see, there are the two serverpools, gold and silver. Both are configured to have at least one server and at most three servers. Because of the higher IMPORTANCE of “gold” it has three active servers while “silver” has only the minimum of one active server.

[oracle@vm101 ~]$ srvctl status serverpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: gold
Active servers count: 3
Server pool name: silver
Active servers count: 1

We now can modify the configuration file according to our requirements and add new policies to it. In my case I have two policies, one for the week and one for the weekend. During week time I will keep my configuration. But on weekends I want to have the servers equally distributed, two servers per server pool. Let’s go:

[oracle@vm101 ~]$ cp default.policyset myown.policyset
[oracle@vm101 ~]$ vi myown.policyset
[oracle@vm101 ~]$ cat myown.policyset
SERVER_POOL_NAMES=Free ora.gold ora.silver
POLICY
  NAME=Week
  SERVERPOOL
    NAME=ora.gold
    IMPORTANCE=10
    MAX_SIZE=3
    MIN_SIZE=3
    SERVER_CATEGORY=ora.hub.category
  SERVERPOOL
    NAME=ora.silver
    IMPORTANCE=8
    MAX_SIZE=3
    MIN_SIZE=1
    SERVER_CATEGORY=ora.hub.category
POLICY
  NAME=WeekEnd
  SERVERPOOL
    NAME=ora.gold
    IMPORTANCE=10
    MAX_SIZE=2
    MIN_SIZE=2
    SERVER_CATEGORY=ora.hub.category
  SERVERPOOL
    NAME=ora.silver
    IMPORTANCE=8
    MAX_SIZE=2
    MIN_SIZE=2
    SERVER_CATEGORY=ora.hub.category
[oracle@vm101 ~]$ crsctl modify policyset -file myown.policyset

The last command loads the updated configuration.
You can achieve the same using several “crsctl” commands like this:

crsctl modify policyset 
crsctl add policy 
crsctl modify serverpool ... -policy ...

I prefer the configuration file, it is more straight forward and easier to document.

Changing the active policy

At first, I activate the “week” policy, this changes nothing since it matches the current situation.

[oracle@vm101 ~]$ crsctl modify policyset -attr "LAST_ACTIVATED_POLICY=Week" -f
[oracle@vm101 ~]$ srvctl status serverpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: gold
Active servers count: 3
Server pool name: silver
Active servers count: 1
[oracle@vm101 ~]$ crsctl stat res -t -w "(( TYPE = ora.service.type ) OR ( TYPE = ora.database.type ))"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.rac01.db
      1        ONLINE  ONLINE       vm103                    Open,STABLE
      2        ONLINE  ONLINE       vm101                    Open,STABLE
      3        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      4        ONLINE  ONLINE       vm104                    Open,STABLE
ora.rac01.gold_svc.svc
      1        ONLINE  ONLINE       vm104                    STABLE
      2        ONLINE  ONLINE       vm103                    STABLE
      3        ONLINE  ONLINE       vm101                    STABLE
ora.slv01.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        ONLINE  ONLINE       vm102                    Open,STABLE
ora.slv01.silver_svc.svc
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  ONLINE       vm102                    STABLE
--------------------------------------------------------------------------------

Now comes the interesting part you waited for all the time, changing the policy and in turn changing the server pool setup.

[oracle@vm101 ~]$ crsctl modify policyset -attr "LAST_ACTIVATED_POLICY=WeekEnd"
CRS-2736: The operation requires stopping resource 'ora.rac01.db' on server 'vm102'
CRS-2736: The operation requires stopping resource 'ora.rac01.gold_svc.svc' on server 'vm102'
CRS-2920: Unable to activate policy 'WeekEnd' because this will affect running resources, but the force option was not specified.
CRS-4000: Command Modify failed, or completed with errors.

[oracle@vm101 ~]$ crsctl modify policyset -attr "LAST_ACTIVATED_POLICY=WeekEnd" -f
CRS-2673: Attempting to stop 'ora.rac01.gold_svc.svc' on 'vm102'
CRS-2677: Stop of 'ora.rac01.gold_svc.svc' on 'vm102' succeeded
CRS-2673: Attempting to stop 'ora.rac01.db' on 'vm102'
CRS-2677: Stop of 'ora.rac01.db' on 'vm102' succeeded
CRS-2672: Attempting to start 'ora.slv01.db' on 'vm102'
CRS-2676: Start of 'ora.slv01.db' on 'vm102' succeeded
CRS-2672: Attempting to start 'ora.slv01.silver_svc.svc' on 'vm102'
CRS-2676: Start of 'ora.slv01.silver_svc.svc' on 'vm102' succeeded

As you can see, we have to use the “-f” force option to get this done. Now the cluster looks like this:

[oracle@vm101 ~]$ srvctl status serverpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 0
Server pool name: gold
Active servers count: 2
Server pool name: silver
Active servers count: 2
[oracle@vm101 ~]$ crsctl stat res -t -w "(( TYPE = ora.service.type ) OR ( TYPE = ora.database.type ))"
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.rac01.db
      1        ONLINE  ONLINE       vm103                    Open,STABLE
      2        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      3        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      4        ONLINE  ONLINE       vm104                    Open,STABLE
ora.rac01.gold_svc.svc
      1        ONLINE  ONLINE       vm104                    STABLE
      2        ONLINE  ONLINE       vm103                    STABLE
      3        ONLINE  OFFLINE                               STABLE
ora.slv01.db
      1        ONLINE  ONLINE       vm101                    Open,STABLE
      2        ONLINE  ONLINE       vm102                    Open,STABLE
ora.slv01.silver_svc.svc
      1        ONLINE  ONLINE       vm101                    STABLE
      2        ONLINE  ONLINE       vm102                    STABLE
--------------------------------------------------------------------------------

You see, now my “rac01” database and it’s “gold_svc” service now has only two running instances since one of the servers got moved to the “silver” server pool allowing the “slv01” database and it’s “silver_svc” to start a second instance.

Conclusion

We now can switch between these two configurations whenever we want to. Do this using a cron job or whatever you like. All the handling will be done by GridInfrastructure.
All in all, I like this feature since it allows easy change of setups according to workload that needs to be handled.
A future blog post will then describe the usage of the “Quality of Service” infrastructue to manage the cluster resources.