How to configure Client Failover after Data Guard Switchover or Failover

Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.1 Information in this document applies to any platform.
Goal
This Document provides a Method to let Clients connect to the new Primary Database after Data Guard Switchover or Failover.
Solution
On the Primary and Standby Servers, configure the Listener and start them.
listener.ora on Server one (Primary): ========================= listener=
(description= (address=(protocol = TCP)(host=one.world.com)(port=1521)) )
listener.ora on Server two (Standby): =========================== listener=
(description= (address=(protocol = TCP)(host=two.world.com)(port=1521)) )
NOTE: Do not use SID_LIST_ on the listener.ora File to let the Database to register itself with the correct Service Names.
(S)PFILE on Primary (one) =================== service_names=(db_prod.world.com, db_stby.world.com) log_archive_dest_2=’SERVICE=dbtwo LGWR reopen=60 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)’
(S)PFILE on Standby (two) =================== service_names=db_stby.world.com log_archive_dest_2=’SERVICE=dbone LGWR reopen=60 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)’
REMARK: The valid_for-Attribute was introduced in 10.x, so not possible in 9.x, use the log_archive_dest_state_2 instead to toggle enable/defer depending on the Role
Listener Status on Primary Server will show both the Primary Service, i.e. db_prod.world.com, and PhysicalSstandby Service, i.e. db_stby.world.com. Listener Status on Standby Server will show Physical Standby Service, i.e. db_stby.world.com only.

tnsnames.ora on Primary & Standby: =========================
dbone.world.com =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=one.world.com)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = db_stby.world.com)) )
dbtwo.world.com= (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=two.world.com)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = db_stby.world.com)) )
db.world.com =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=one.world.com)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=two.world.com)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME= db_prod.world.com)) )
*** so you’ll always use db_prod.world.com Service to reach the current Primary Database.
NOTES:
1. After Switchover or Failover, reset the service_names Parameter, this can be achieved by creating an “On Database Open” Trigger, or by executing the following ‘alter system’ Commands (when using SPFILE): – New Primary (the ‘old Standby’ on Node two): SQL> alter system set service_names = ‘db_prod.world.com, db_stby.world.com’ scope=both; – New Standby (the ‘old Primary’ on Node one) SQL> alter system set service_names=’db_stby.world.com’ scope = both;
So if you want to explicity connect to one of the Instances you’d use dbone or dbtwo depending to which Instance you want to connect, but if you want to connect to the Primary Database no matter in which Server the Primary Database is runing you’d use the TNS-Alias db.
2. If you setup the local_listener and remote_listener on the Primary and Standby Database, all Listeners will know which Instances provide which Service(s).
3. If you are using the DataGuard Broker and DGMGRL, you have to configure a static Listener Entry for each Database in the SID_LIST_LISTENER (in Contrast to what is mentioned above). This Entry must contain a Line using the following Syntax:
GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain (See DataGuard Broker Guide for Details)
This can still work with this Example as long as the SERVICE_NAME called by the Client-side tnsnames.ora matches the GLOBAL_DBNAME-Value configured in the SID_LIST_LISTENER Section.
4. Change the Names of the Servers, Domain and TNS-Aliases to meet your Standards and this Example should be enough.
You can also configure Automatic Client Failover. Please see fallowing Note for more info:

References
Note 405120.1 – Prerequisites Required For Automatic Client Failover Configurations

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