How to make dataguard switchover seamlessly
Starting with 11.2, you do not need to create the trigger, you can define a role based service with Grid Infrastructure.

foo:

srvctl add service -db foo -service virtual_foo -role primary
srvctl start service -db foo -service virtual_foo


bar:

srvctl add service -db bar -service virtual_foo -role primary
srvctl start service -db bar -service virtual_foo


If you do not use Grid Infrastructure, well, just use the trigger.

Your virtual service was misconfigured, that is why you received ORA-01033. You should not update the value of service_names manually. After you did that and performed the switchover, virtual_foo service still ran on foo. Your client tried the first address in the lists, found that the listener was alive, and a service existed with the requested name, so it tried to log in. But the standby database was in MOUNT state, so the client could not log in --&gt; ORA-01033.

For the switchover back to foo part: do not use the same TNS entries for clients and Data Guard configuration. Clients do not need to know which site they connect to, but Data Guard Broker and database background processes need to connect exactly to foo or bar, a virtual service does not provide that functionality. Seperate the TNS entries needed for Data Guard configuration and the one used by clients.

Usually I configure DG with the below naming and TNS entries.

If the db_name is foo, then:


primary: db_unique_name = foo_site1
standby: db_unique_name = foo_site2


(or something similar, that identifies the site)

TNS:


foo_site1: TNS entry pointing to the host at site1, with service_name
defined as foo_site1 - used by DG configuration
foo_site2: TNS entry pointing to the host at site2, with service_name defined as foo_site2 - used by DG configuration
foo : TNS entry pointing to both sites, with service_name defined as foo (and that is defined as role based service) - used by clients


Finally, this part in your connection string:

(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))


Unless you have RAC databases with multiple instances per site, and foo_home and bar_home are actually SCAN names, this is unnecessary. These parameters are used to configure Transparent Application Failover (session failover between RAC instances).

By the way, this is far from a really seamless switchover, all that was achieved by this, was that clients can use a single connection string to connect to the database after a role transition.

Client sessions will fail and need to reconnect manually, that could take minutes. Or you need to stop your applications and have planned downtime for this.

Having a really seamless switchover requires far more work (like configuring ONS, FAN). More details at:

Client Failover Best Practices for Highly Available Oracle Databases

Fast Application Notification