In 10.2.0.4, I tested the ability of off the shelf code to automatically connect to a database that has been \u201cswitched over to\u201d. 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.<\/p>\n
My test consisted of:<\/p>\n
\u2022\tCluster database apptest \u2013 primary on server1
\n\u2022\tCluster database apptest \u2013 standby on server2<\/p>\n
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 \u201cgracefully”, with zero impact on the user. The test inserted rows and printed out the status after each insert.<\/p>\n
After the test was running without issue for a minute or so, on the primary I issued an:<\/p>\n
\r\nalter database commit to switchover to physical standby with session shutdown;\r\n<\/pre>\nWhat this command does is, in order:<\/p>\n
1.\tPush all transactions to the standby that it does not yet possess
\n2.\tIssue a block and check the standby to ensure it has everything.
\n3.\tShuts itself down and changes its role to standby \u2013 At this point the application cannot get a connection for a brief period of time<\/p>\n
I then issued on the standby the following commands:<\/p>\n
\r\nalter database commit to switchover to primary;\r\n!srvctl start service \u2013d apptest\r\n<\/pre>\nAs soon as the service came up when the last command above was issued, connections started coming in from my test.<\/p>\n
The following is what the user sees for a minute or so while the switchover occurs.<\/p>\n
\r\n08-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\r\nTNS-12514: TNS:listener does not currently know of service requested in connect descriptor\r\n08-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\r\nTNS-12514: TNS:listener does not currently know of service requested in connect descriptor\r\n\r\n\r\n08-APR-2011 14:48:54 * service_update * apptest1 * 0\r\n08-APR-2011 14:48:54 * service_update * apptest1 * 0\r\n\r\n\r\n08-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\r\n08-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\r\n<\/pre>\nAfter the service was updated, you can see it begins to take connections in the last two entries of the window above.<\/p>\n
Switching back from the new primary to the old standby is just as easy. The drawback to this is that you can\u2019t change the standby while it is in this mode. <\/p>\n
As such, we couldn\u2019t 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
In 10.2.0.4, I tested the ability of off the shelf code to automatically connect to a database that has been \u201cswitched over to\u201d. A switchover is a graceful exchange of database responsibilities that prevents a split brain scenario where two…<\/p>\n