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 22.214.171.124 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 126.96.36.199.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='188.8.131.52.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
(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 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
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!