Roaming Service for Primary Oracle Instance (ADG)

In a configuration with a Standby base, when switching to a backup database, a situation arises that clients, as before, try to connect to the old industrial server.

To eliminate this problem, you can use roaming services, for this you need to:Specify the value of the local_listener parameter

Example:

alter system set local_listener='(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))))' scope=both;

re-register an instance in the listener

alter system register;

Create roaming services in the industrial database

begin DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'PRIMARY',NETWORK_NAME=>'PRIMARY'); 
end;
begin DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'ADG',NETWORK_NAME=>'ADG');  end;
begin DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'SNAP',NETWORK_NAME=>'SNAP'); end;
begin DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'LOGICAL',NETWORK_NAME=>'LOGICAL'); 
end;

Service names can be anything you want.

Create a trigger that, when the database starts, will start a particular service

CREATE OR REPLACE TRIGGER START_SERVICE AFTER STARTUP ON DATABASE
DECLARE
  DBROLE VARCHAR(30);
  OPEN_MODE VARCHAR(30);
BEGIN

  SELECT DATABASE_ROLE INTO DBROLE FROM V$DATABASE;
  SELECT OPEN_MODE INTO OPEN_MODE FROM V$DATABASE;
  IF DBROLE = 'PRIMARY' THEN
     DBMS_SERVICE.START_SERVICE ('PRIMARY');
  ELSIF DBROLE = 'PHYSICAL STANDBY' THEN
        IF OPEN_MODE LIKE 'READ ONLY%' THEN
           DBMS_SERVICE.START_SERVICE ('ADG');
        END IF;

  ELSIF DBROLE = 'LOGICAL STANDBY' THEN
        DBMS_SERVICE.START_SERVICE ('LOGICAL');
  ELSIF DBROLE = 'SNAPSHOT STANDBY' THEN
        DBMS_SERVICE.START_SERVICE ('SNAP');

  END IF;

END;
/  

Restart the database, check if the service is registered

Add both addresses to the clients to the block in the tnsnames.ora file and specify the name of the industrial service

PROMDB =
 (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.46)(PORT = 1522))
       )
       (CONNECT_DATA =
       (SERVICE_NAME = PRIMARY)
    )
 )