RE: sessions not switching to new standby after switchover (data guard)

From: Armstrong-Champ, Joseph William <Joseph.Armstrong-Champ_at_tufts.edu>
Date: Thu, 9 May 2013 21:44:39 +0000
Message-ID: <46852E87A7561E489DF15753ECB0B6D80A95B359_at_SSVMEXDAG01MB02.tufts.ad.tufts.edu>

A co-worker has discovered the issue. The services_name parm was set to the same name as the service_name listed in the tnsnames file (SVC_ADVSTG). I think that this caused the service to start whenever the database was started (as in a switchover). So even though the trigger stops the service in the standby db, the parm makes it look like the service is started. I'm not completely clear about this, because when I ran 'select * from v$active_services', the service name didn't appear. Likewise, doing a 'lsnrctl services' didn't show it either. More research needed.

Feel free to comment on the above if anyone has any has any insight.

Thanks.

-----Original Message-----
From: Armstrong-Champ, Joseph William
Sent: Thursday, May 09, 2013 3:49 PM
To: oracle-l_at_freelists.org
Subject: sessions not switching to new standby after switchover (data guard)

Oracle 11.2.0.3 on rhel 6.

Set up: primary is db1. Standby is db2.
Switchover to standby (db2) works, but active sessions don't move to the new primary (db2) until the new STANDBY (db1) is open. All new sessions get 'db in initilzation state' and can't login. We want the standbys to be in mounted state because we are not licensed for Active Data Guard.

tnsnames.ora entry:
ADVSTG.WORLD =
  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = advdb-stage-01.uit.tufts.edu)(PORT = 11003))
    (ADDRESS = (PROTOCOL = TCP)(HOST = advdb-stage-02.uit.tufts.edu)(PORT = 11003))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SVC_ADVSTG)
      (FAILOVER_MODE =


        (TYPE = SELECT)

        (METHOD = BASIC)

        (RETRIES = 180)

        (DELAY = 5)

      )

    )
  )

Users use ADVSTG as the connect string.

The switch going the other way works, (switchover to db1) i.e., the user sessions get moved correctly.

We also have a trigger that gets run on db startup which stops the svc_advstg service for the standby and starts it for the primary. This is so that new sessions get pointed to the new primary.

Hope that is clear.

Thanks for any help.

Joe

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 09 2013 - 23:44:39 CEST