In 10.2.0.4, I tested the ability of off the shelf code to automatically connect to a database that has been “switched over to”. A switchover is a graceful exchange of database responsibilities that prevents a split brain scenario where two databases are taking traffic at the same time. In a switchover, dataguard guarantees, by design, that only one database can be the primary.
My test consisted of:
• Cluster database apptest – primary on server1
• Cluster database apptest – standby on server2
I then ran a test that used an OracleDataSource and OracleConnectionCacheManager backed connection manager class that created ten connections to a URL that included both the primary and standby databases. As mentioned, a standby database *cannot* take traffic, but the OracleDataSource handles all connection retrieval request exceptions “gracefully”, with zero impact on the user. The test inserted rows and printed out the status after each insert.
After the test was running without issue for a minute or so, on the primary I issued an:
alter database commit to switchover to physical standby with session shutdown;
What this command does is, in order:
1. Push all transactions to the standby that it does not yet possess
2. Issue a block and check the standby to ensure it has everything.
3. Shuts itself down and changes its role to standby – At this point the application cannot get a connection for a brief period of time
I then issued on the standby the following commands:
alter database commit to switchover to primary;
!srvctl start service –d apptest
As soon as the service came up when the last command above was issued, connections started coming in from my test.
The following is what the user sees for a minute or so while the switchover occurs.
08-APR-2011 14:48:54 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=batch.home)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=51052)) * establish * batch.home * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
08-APR-2011 14:48:54 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=batch.home)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=51051)) * establish * batch.home * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
08-APR-2011 14:48:54 * service_update * apptest1 * 0
08-APR-2011 14:48:54 * service_update * apptest1 * 0
08-APR-2011 14:48:54 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=batch.home)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=51055)) * establish * batch.home * 0
08-APR-2011 14:48:54 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=batch.home)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=51056)) * establish * batch.home * 0
After the service was updated, you can see it begins to take connections in the last two entries of the window above.
Switching back from the new primary to the old standby is just as easy. The drawback to this is that you can’t change the standby while it is in this mode.
As such, we couldn’t have used this for tasks such as complete database upgrades, which change dictionary tables in the database. However, for activities such as storage it is perfect.