0

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?

1

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.

  • What about the listener.ora configuration? dynamic listener registration? – r0tt Nov 17 '17 at 7:55
  • For listener dynamic registration make sure you have set local_listener parameter appropriately for both primary and standby server. For example :alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=HOSTB)(PORT=1521))';. Instead of full address, you can use listener alias defined in the tnsnames.ora file. – JSapkota Nov 18 '17 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.