An introduction to Policy Managed Databases in 11.2 RAC

I just realised this week that I haven’t really detailed anything about policy managed RAC databases. I remembered having done some research about server pools way back when 11.2.0.1 came out. I promised to spend some time looking at the new type of database that comes with server pools: policy managed databases but somehow didn’t get around to doing it. Since I’m lazy I’ll refer to these databases as PMDs from now on as it saves a fair bit of typing.

So how are PMDs different from Administrator Managed Databases?

First of all you can have PMDs with RAC only, i.e. in a multi-instance active/active configuration. Before 11.2 RAC you had to tie an Oracle instance to a cluster node. This is why you see instance prefixes in a RAC spfile. Here is an example from my lab 11.2.0.3.6 cluster:

DEMO1.__db_cache_size=1073741824
[...]
DEMO2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DEMO/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='...'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DEMO'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
DEMO2.instance_number=2
DEMO1.instance_number=1
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=310378496
*.processes=300
*.remote_listener='rac11gr2scan.example.com:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=1610612736
DEMO1.thread=1
DEMO2.thread=2
DEMO1.undo_tablespace='UNDOTBS2'
DEMO2.undo_tablespace='UNDOTBS1'

Note that the instance_number, thread and undo tablespace are manually (=administrator) managed. If these aren’t set or configured incorrectly you will run into all sorts of fun.

Enter the Policy Managed Database

In the never ending effort to make the DBA’s life easier Oracle decided to automate the process of RAC database creation and management. So instead of manually mapping instances to nodes in policy managed databases you map databases to server pools. Please refer to my earlier post about server pool management

https://martincarstenbach.wordpress.com/2010/02/12/server-pool-experiments-in-rac-11-2/

(this is the reason I blog by the way-to be able to look up things I don’t do every day!)

Basing myself on the post I checked my 2 node lab environment. Since all I ever ran on it are administrator managed databases I was not surprised to find the basic configuration:

[oracle@rac11gr2node1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Candidate server names: rac11gr2node1,rac11gr2node2
[oracle@rac11gr2node1 ~]$

As you can see there are a free and a generic pool. SRVCTL is a great utility when you are unsure how to do something, simply add a -h to it and you will see the syntax:

[oracle@rac11gr2node1 ~]$ 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>"] [-f]
-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 (Default value is -1 for unlimited maximum size)
-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
[oracle@rac11gr2node1 ~]$

Easy enough! I am now adding a new server pool across both nodes with high importance. Note that min=max=1 here. This is done on purpose to demonstrate how to extend the database to another node.

[oracle@rac11gr2node1 ~]$ srvctl add srvpool -g blog -l 1 -u 1 -n rac11gr2node1,rac11gr2node2

That was simple! But creating the pool is only half the job done. Does it have any servers assigned to it?

[oracle@rac11gr2node1 ~]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2
Server pool name: blog
Active servers count: 0

There are NO servers assigned. Remember when I said I was using admin managed databases? Their instances are using the GENERIC pool by default. So for the purpose of this blog post I had to remove their configuration. In real life you would of course not do this!

[oracle@rac11gr2node1 ~]$ srvctl config database
DEMO
RON

Both of these are admin managed. Let’s remove them (again for the sake of demonstration only)

[oracle@rac11gr2node1 ~]$ srvctl remove database -d RON
Remove the database RON? (y/[n]) y

[oracle@rac11gr2node1 ~]$ srvctl remove database -d DEMO
Remove the database DEMO? (y/[n]) y

Now it’s looking better, or so I hope:

[oracle@rac11gr2node1 ~]$ srvctl config srvpool -g blog
Server pool name: blog
Importance: 999, Min: 1, Max: 1
Candidate server names: rac11gr2node1,rac11gr2node2
[oracle@rac11gr2node1 ~]$

[oracle@rac11gr2node1 ~]$ srvctl status srvpool -g blog
Server pool name: blog
Active servers count: 1
[oracle@rac11gr2node1 ~]$

Hurray, I have my server!

Now over to dbca to create the policy managed database. This is not different at all from creating the admin managed database except for 1 step, shown in the figure below:

Note how I have chosen the new server pool with 1 active server. The database type is set to policy managed as well. After the usual series of “next”, “next”, “next” the database will eventually be ready for use:

Now let’s have a look at the resource definition for a policy managed database:

[oracle@rac11gr2node1 ~]$ srvctl config database -d poldb
Database unique name: poldb
Database name: poldb
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/poldb/spfilepoldb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: blog
Database instances:
Disk Groups: DATA,RECO
Mount point paths:
Services:
Type: RAC
Database is policy managed

In this case the notion of “server pools” makes a lot of sense. Interestingly no instance is recorded, but there is one:

[oracle@rac11gr2node1 ~]$ srvctl status database -d poldb
Instance poldb_1 is running on node rac11gr2node2

For those who used RAC One before the $ORACLE_SID will look familiar. As with RON, you get the DB_NAME plus instance number suffix.

Adding an instance

Now let’s increase the size of the pool by another node and see what happens. This in the only command I typed:

[oracle@rac11gr2node1 ~]$ srvctl modify srvpool -g blog -u 2

It takes a little while to complete though, but here’s the magic:

[oracle@rac11gr2node1 ~]$ srvctl status database -d poldb
Instance poldb_2 is running on node rac11gr2node1
Instance poldb_1 is running on node rac11gr2node2
[oracle@rac11gr2node1 ~]$

Now is that cool or isn’t it? So without me doing anything Oracle created a new online redo thread, another undo tablespace etc. I found this in the alert.log of instance 1:

2013-06-16 21:57:16.082000 +01:00
Reconfiguration started (old inc 2, new inc 4)
List of instances:
1 2 (myinst: 1)
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
alter database add logfile thread 2 SIZE 52428800 , SIZE 52428800 , SIZE 52428800
minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:4 errcnt:0
2013-06-16 21:57:57.487000 +01:00
Completed: alter database add logfile thread 2 SIZE 52428800 , SIZE 52428800 , SIZE 52428800
alter database enable public thread 2
2013-06-16 21:57:58.649000 +01:00
Completed: alter database enable public thread 2
create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 83886080 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2013-06-16 21:58:22.055000 +01:00
Completed: create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 83886080 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2013-06-16 22:00:49.614000 +01:00

Services

Services are different in policy managed databases too. Two different models exist: singleton and uniform services. The singleton is active on a single instance only (which you don’t get to choose). A uniform service is active on all instances. Forget about preferred and available nodes, that’s for admin managed databases only! Oracle is slightly misleading here:

[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s uni
PRKO-3116 : '-g' or '-r' option should be provided
[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s uni -r poldb_1
PRKO-3114 : Policy-managed database poldb can not support administrator-managed service single.
[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s single -g blog

So again-no preferred instance, but only a server pool to specify. Once the services are created, start them as usual. The status message will be similar to this one:

[oracle@rac11gr2node2 ~]$ srvctl status service -d poldb
Service single is running on nodes: rac11gr2node1
Service uni is running on nodes: rac11gr2node1,rac11gr2node2

Summary

That’s it for an introduction to policy managed databases. These are cool things, especially if you think about the possibilities to create nested server pools or automatically draw in servers from lower priority pools to those with high priority to compensate a failed node. I don’t have enough horse power on my lab server to create a 4 node cluster but I’d love to play around with these features one day.

Having said that I don’t really know of anyone who uses PMDs and server pools in production. If you happen to be one, please comment and let me know!