RAC and Pluggable Databases

In preparation of the OUGN Spring Seminar and to finally fulfill at least a part of my promise from July I was getting ready to research RAC, PDBs and services for my demos. It turned out to become a lot more interesting than I first assumed.

RAC and Multi-Tenancy

So the first attempt to really look at how this works has started with my 2 node cluster where I created a RAC database: RAC12C, administrator managed with instance RAC12C1 and RAC12C2. The database is registered in Clusterware. Clusterware and RDBMS are patched to the January PSU, i.e. 12.1.0.1.2.

The second step was to create a PDB for testing-it’s named DEMOPDB and available on both my instances by design. By the way-PDBs do not start automatically, it is your responsibility to start them when the database starts. I used a startup-trigger for this in the Consolidation Book, have a look at it to see the example. There are other ways available as I found out.

As soon as the PDBs is opened (in all Oracle deployment types, not limited to a RAC instance), a new service with the same name as the PDB is automatically started. As it turned out, using that service is the most reliable way to connect to the PDB:

SQL> connect user/password@single-client-access-name/pdbname

This works really well. Substitute your hostname with the SCAN for single instance. Now if you would like to implement some more interesting features (TAF/FAN+FCF/Application Continuity) you could create an additional service. The srvctl syntax has changed, Oracle now uses long parameter names (-service instead of -s), which doesn’t really respect the UNIX/GNU way of naming parameters (short parameter: single dash, long parameter name: double-dash) but that’s how it is.

Adding a service

So I added a new service, named TAFSRV to my PDB DEMOPDB:

srvctl add service -db RAC12C -service TAFSRV -preferred RAC12C1 -available RAC12C2 \
-tafpolicy BASIC -policy AUTOMATIC -failovertype SELECT -failovermethod BASIC \
-failoverretry 5 -pdb DEMOPDB -verbose
srvctl start service -d RAC12C -s TAFSRV

So far so good, nothing too exciting here, except maybe that I bound the service to the PDB (-pdb DEMOPDB). Notice the preferred instance is RAC12C1 with RAC12C2 as available instance.

Interesting!

This works really well if DEMOPDB is open on all instances. I can connect back and forth, no problem. Now what happens for example if I manually close the PDB on RAC12C1?

SYS@CDB$ROOT> alter pluggable database DEMOPDB close instances=('RAC12C1');

Pluggable database altered.

SYS@CDB$ROOT> @lspdbs

   INST_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         1 DEMOPDB                        MOUNTED
         2 DEMOPDB                        READ WRITE

I actually can’t connect anymore:

 sqlplus martin/secret@rac12scan.gns.example.com/tafsrv

SQL*Plus: Release 12.1.0.1.0 Production on Mon Feb 17 16:00:19 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

And why is that? The service decided to stop:

SYS@CDB$ROOT> !srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is not running.

To quote Parker Lewis: “Not a problem”. I can start the service:

SYS@CDB$ROOT> !srvctl start service -d RAC12C -s TAFSRV
SYS@CDB$ROOT> !srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C1

Well that’s interesting. I actually would have expected the service to start on the available instance, RAC12C2. But wait-if the service is now running on instance 1, everyone is locked out because I stopped that instance, didn’t I? Checking the status I can relax:

SYS@CDB$ROOT> @lspdbs

   INST_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         1 DEMOPDB                        READ WRITE
         2 DEMOPDB                        READ WRITE

But hey! I deliberately stopped the PDB on that instance…If you really want to start the service on the other instance, specify it:

SYS@CDB$ROOT> alter pluggable database DEMOPDB close instances=('RAC12C1')
  2  /

Pluggable database altered.
SYS@CDB$ROOT> !srvctl start service -d RAC12C -s TAFSRV -instance RAC12C2

SYS@CDB$ROOT> !srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C2

SYS@CDB$ROOT> @lspdbs

   INST_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         1 DEMOPDB                        MOUNTED
         2 DEMOPDB                        READ WRITE

SYS@CDB$ROOT>

The next test case is the shut down of the instance to see if the service relocates automatically:

[oracle@rac12node1 ~]$ srvctl relocate service -d RAC12C -s TAFSRV -oldinst RAC12C2 -newinst RAC12C1
[oracle@rac12node1 ~]$ srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C1
[oracle@rac12node1 ~]$ srvctl stop instance -d RAC12C -i RAC12C1
[oracle@rac12node1 ~]$ srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is not running.
[oracle@rac12node1 ~]$

So that doesn’t relocate the service either.

More interesting

After having discovered that a PDB can be started by starting the service, maybe I can use the service to start the PDB when starting the CDB? The service needs to have the AUTOMATIC management policy. Let’s try.

[oracle@rac12node1 ~]$ srvctl stop database -d RAC12C -s TAFSRV
[oracle@rac12node1 ~]$ srvctl config service -d RAC12C | grep "Preferred"
Preferred instances: RAC12C1
[oracle@rac12node1 ~]$ srvctl start database -d RAC12C

I somehow expected the service to pull up the dependent resource on instance 1-after all, instance 1 is the preferred instance. In 2 consecutive cases, this was not the case. I should probably add that this might be coincidence, and I have to investigate the observed behaviour in more detail. The facts remain though.

After the start command has completed, this is what I noticed:

[oracle@rac12node1 ~]$ srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C2

SYS@CDB$ROOT>  @lspdbs

   INST_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         1 DEMOPDB                        MOUNTED
         2 DEMOPDB                        READ WRITE

And sure enough, CRSD’s oracle agent log file showed that all services defined for RAC12C were actually started on the second instance. The agent logfile is rather verbose so it might take a little while to wade through it, and also ignore all the other resources the oracle agent process checks periodically.

The good news

Now I thought that this observation might have an impact on TAF, FCF and other HA technologies but thankfully that is not the case, at least with my tests. Here is an example. I ensured that DEMOPDB was open read/write on both instances, started service TAFSRV and killed a SQLPlus session with which I was connected to node1. It failed over as documented (and hoped for!), and the automatic relocation of the service worked too. When instance 1 came back though the DEMOPDB was _not_ started. This is the status:

SQL> @lspdbs

   INST_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 1 DEMOPDB			  MOUNTED
	 2 DEMOPDB			  READ WRITE

SQL> !srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C2

Now I was a bit anxious to see what the relocate would do, but it does well:

[oracle@rac12node1 ~]$ srvctl relocate service -d RAC12C -s TAFSRV -oldinst RAC12C2 -newinst RAC12C1
[oracle@rac12node1 ~]$ srvctl status service -d RAC12C -s TAFSRV
Service TAFSRV is running on instance(s) RAC12C1

SQL> @lspdbs

   INST_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         1 DEMOPDB                        READ WRITE
         2 DEMOPDB                        READ WRITE

Why am I looking at this?

I am currently working on TAF/FAC+FCF demos, and these rely on services quite heavily. I needed to understand how services work with 12c RAC and there are some quirks to them as I discovered.