Dataguard – Client failover on standby database configuration

The scenario is normal Primary/Physical Standby configuraion (without RAC)
I want to use Tnsnames.ora file and I do not want to make changes to it on all client machines(in 
thousands) after switchover/failover operations.
I configure a common service name for both the primary and standby databases.
I have two servers PRODSRVRA and PRODSRVRB.
PRODSRVRA hosts Primary database initially (Dbname - PROD , Instance Name PROD , Service Name PROD)
PRODSRVRB hosts Standby database  initially (Dbname - PROD , Instance Name PROD, Service Name PROD)
Both servers have two listeners.
Listener on Port 1522 is started on server hosting Primary database and is used for connect time 
failover.
Listeners on Port 1523 is used for communication between Primary and Standby databases for Log 
transport.

Listener.ora on PRODSRVRA

LSNRPRODA =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRA)(PORT = 1522))
    )
  )

SID_LIST_LSNRPRODA =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = c:\Oracle\Product\9.2.0.4)
      (SID_NAME = PROD)
    )
  )

LSNRPRIM =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRA)(PORT = 1523))
    )
  )

SID_LIST_LSNRPRIM =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = c:\Oracle\Product\9.2.0.4)
      (SID_NAME = PROD)
    )
  )

Listener.ora on PRODSRVRB

LSNRPRODB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRB)(PORT = 1522))
    )
  )

SID_LIST_LSNRPRODB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME =c:\Oracle\Product\9.2.0.4)
      (SID_NAME = PROD)
    )
  )

LSNRSTDBY =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PRODSRVRB)(PORT = 1523))
    )
  )

SID_LIST_LSNRSTDBY  =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME =c:\Oracle\Product\9.2.0.4)
      (SID_NAME = PROD)
    )
  )

TNSNAMES.ORA configuration on the Primary Database server - PRODSRVRA
STDBY=
  ( DESCRIPTION =
    ( ADDRESS_LIST =
      (ADDRESS= (PROTOCOL = TCP) (HOST = PRODBSRVRB) (PORT = 1523) )
    )  
    ( CONNECT_DATA = (SERVICE_NAME =PROD) (SERVER = DEDICATED) )
  )

TNSNAMES.ORA configuration on Standby Database server - PRODSRVRB

PRIM=
  ( DESCRIPTION =
    ( ADDRESS_LIST =
      (ADDRESS= (PROTOCOL = TCP) (HOST = PRODSRVRA) (PORT = 1523) )
    )  
    ( CONNECT_DATA = (SERVICE_NAME =PROD) (SERVER = DEDICATED) )
  )

Failover Configuration in Tnsnames.ora on Clients  (PCs)

PROD.WORLD =
  (DESCRIPTION_LIST =
    (FAILOVER = TRUE)
    (LOAD_BALANCE = FALSE)
    (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = PRODSRVRA)(PORT = 1522))
      (CONNECT_DATA =
        (SERVICE_NAME = PROD)
        (SERVER = DEDICATED)
      )
    )
    (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = PRODSRVRB)(PORT = 1522))
      (CONNECT_DATA =
        (SERVICE_NAME =PROD)
        (SERVER = DEDICATED)
      )
    )
  )

Primary is working - the listener on PRODSRVRA is up but the listener on PRODSRVRB is down
When the clients connect using PROD.WORLD Primary database is available they will connect to the 
Primary database.

Failover Situation :
Primary is down. The standby database on PRODSRVRB is made primary.
The listener( port1522) on Standby server PRODSRVRB is started.
The clients will connect to New Primary on PRODSRVRB using PROD.WORLD

Switchover situation
The listener ( port1522) on Old Primary is taken down. The Standby database is made Primary.
The listener( port1522) on Standby server PRODSRVRB is started.
The clients connect using PROD.WORLD to the new Primary database on PRODSRVRB.
The log transport services will continue using the other listeners working on port 1523.

Let me know will this TNSANMES.ora thing work without changing anything on Client PCs or if I am 
missing something?

Drawbacks accroding to me:
1) Global DB name cannot be used for connect time failover as mentioned in some documents - So may 
be OEM and Dataguard monitor etc. camnnot be used.
2) Connections to New Primary Database will be slow as they first look for the Old Primary database 
server.

Thanks

Followup   November 28, 2005 – 7am Central time zone:

the tnsnames.ora entries you have setup are in support of connect time failover, if we cannot 
connect to the first listed listener, we'll go for the second.  So yes (the tnsnames.ora is the 
only relevant configuration file here for this question).

Yes, there will be some hit upon connecting to the secondary site as the first will be tried first, 
found not available and then the second will be connected to.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em DATAGUARD, ORACLE 11gR2 e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s