Setting up the Listeners for Oracle Dataguard

dataguardlisteneroracleoracle-12c

I setup up a Oracle 12c Dataguard configuration. To build the standby database I used the RMAN DUPLICATE command, which requires a static listener configuration.

tnsname.ora on HOSTNAMEA and HOSTNAMEB are the same:

BR =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

BR_STBY =   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = BR)
    )   )

listener.ora HOSTNAMEA:

    SID_LIST_LISTENER =
        (SID_DESC =
          (GLOBAL_DBNAME = BR_DGMGRL)
          (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
          (SID_NAME = BR)
        )

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEA)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
  )

listener.ora HOSTNAMEB:

SID_LIST_LISTENER =
    (SID_DESC =
      (GLOBAL_DBNAME = BR_STDBY_DGMGRL)
      (ORACLE_HOME = /opt/oracle/product/12200/dbhome_1)
      (SID_NAME = BR)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAMEB)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

This configuration is using a static listener configuration. How does the application know the database has for example switched from HOSTNAMEA to HOSTNAMEB? Should I use dynamic listener registration? how should the configuration look like?

Best Answer

You should have a TNS entry, in the client machine, similar to following to connect to the data guard environment.

BR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR)
    )
  )

BR_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTA)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BR_STBY)
    )
  )

You also should have database service configured according to their role-primary or standby as shown below(if you are using Grid Infrastructure).

srvctl add service -d BR  -s BR -l PRIMARY -m BASIC -e SELECT -w 1 -z 180 
srvctl add service -d BR  -s BR_STBY -l PHYSICAL_STANDBY –m BASIC –e SELECT –w 1 –z 180
srvctl start service -s BR -s BR

If you are not using Grid Infrastructure then(AS SYSDBA on Primary)-

SQL>exec dbms_service.create_service(service_name => 'BR', network_name => 'BR', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> exec dbms_service.create_service(service_name => 'BR_STBY', network_name => 'BR_STBY', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 10, failover_delay => 1);
SQL> create or replace trigger MANAGE_SERVICES
  after startup on database
declare
  db_role varchar2(16);
  db_mode varchar2(20);
begin
  select database_role, open_mode into db_role, db_mode from v$database;
  if db_role = 'PRIMARY' then
    dbms_service.start_service('BR');
    dbms_service.stop_service('BR_STBY');
  elsif (db_role = 'PHYSICAL STANDBY') and (db_mode = 'READ ONLY') then
    dbms_service.start_service('BR_STBY');
    dbms_service.stop_service('BR');
  else
    dbms_service.stop_service('BR');
    dbms_service.stop_service('BR_STBY');
  end if;
end;
/
SQL>

Modify the attributes used in the above examples according to your need and refer to the Oracle documentation for details.

Related Question