Joords Oracle DBA blog: FORWARD_LISTENER : the offsite listener forwards the connection to the new Oracle listener

The parameter FORWARD_LISTENER is useful when it is difficult to change an existing client connect string, for example, after an offsite database has been moved into the Oracle Cloud. With the FORWARD_LISTENER parameter, clients can continue to connect to their offsite listener, and the offsite listener forwards the connection to the Oracle Cloud listener.

Once a forward listener has been configured through the FORWARD_LISTENER parameter, the LOCAL_LISTENER parameter can be cleared by setting its value to "-oracle-none-" so that all the connections coming to an existing set of remote listeners configured through the REMOTE_LISTENER parameter are forwarded only to listeners configured through FORWARD_LISTENER.

The FORWARD_LISTENER parameter can also be specified in the LISTENER_NETWORKS parameter.

Lets play and test it, information of the environment

Listener overview service endpoint

[oracle@hol ~]$ lsnrctl status listener
=======================================
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hol)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                13-SEP-2022 11:25:32
Uptime                    0 days 0 hr. 7 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hol/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=1521)))
Services Summary...
...
...
Service "relocate" has 1 instance(s).
  Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@hol ~]$ lsnrctl status listener_cdb2
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hol)(PORT=1525)))
STATUS of the LISTENER
------------------------
Alias                     listener_cdb2
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                13-SEP-2022 11:25:36
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hol/listener_cdb2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=1525)))
Services Summary...
...
...
Service "relocate" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

Databases

[oracle@hol ~]$ ps -ef|grep smon
oracle   14537     1  0 11:25 ?        00:00:00 ora_smon_CDB1
oracle   15399     1  0 11:30 ?        00:00:00 ora_smon_cdb2

Listeners

[oracle@hol ~]$ ps -ef|grep lsnr
oracle   14439     1  0 11:25 ?        00:00:00 /u01/app/oracle/product/19/bin/tnslsnr listener -inherit
oracle   14446     1  0 11:25 ?        00:00:00 /u01/app/oracle/product/19/bin/tnslsnr listener_cdb2 -inherit

We have an environment existing of the following construction :

CDB1 instance with a pluggable database PDB1 using default listener on port 1521
CDB2 instance with a pluggable database relocate using the listener_cdb2 on port 1525

The relocate service on the CDB1 is created within the PDB1 using the commands

below:

CDB1_pdb1> BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'relocate',
    network_name => 'relocate'
  );
END;
/

CDB1_pdb1> BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'relocate'
  );
END;
/

CDB1_pdb1> BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'relocate'
  );
END;
/

Start setup the connection forwarding for the application which is using the listener_cdb2 on port 1525 to connect to the new migrated pluggable database listening on a other port an another CDB without changing the application connection string [CDB1 database using relocate service for PDB1 and listener port 1521]

To get this on a my environment operational I have to trick this by using the listener_cdb2 string 'hol:1525' settings for the remote-listener

Validation of the connection to listener_cdb2 which listen on port 1525
No changed are executed on the databases and pdbs

sqlplus c##relocate/oracle@hol:1525/relocate

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 12 13:30:54 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Mon Sep 12 2022 12:02:51 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

CDB2_relocate> col CDB_NAME for a10
CDB2_relocate> col PDB_NAME for a10
CDB2_relocate> SELECT SYS_CONTEXT ('USERENV','CDB_NAME') as CDB_NAME,  SYS_CONTEXT ('USERENV','DB_NAME') as PDB_NAME from dual;

CDB_NAME   PDB_NAME
---------- ----------
cdb2       RELOCATE

On the relocate PDB within the CDB2 we will activate the forward_listener parameter.

CDB2_relocate> alter system set forward_listener='hol:1521' scope=memory
                --- no change the remote listener is not available to reconnect the request to the forward_listener address
 CDB2_relocate> alter system set remote_listener='hol:1525' scope=memory
                -- the trick when working without ASM/grid configuration

The last statement will show in the lsnrctl services listener_cdb2 command the following rules in the listener_cdb2 a entry for forwarding is found for the service relocate

Service "relocate" has 1 instance(s).
  Instance "cdb2", status READY, has 4 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: hol.localdomain, pid: 15427>
         (ADDRESS=(PROTOCOL=tcp)(HOST=hol)(PORT=33773))
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
      "DEDICATED" established:0 refused:0 state:ready
         REMOTE SERVER   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=1525)))
      "COMMON" established:0 refused:0 state:ready
         FORWARD SERVER   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=1521)))

Still the connections are not forwarded to the new database, here for we have to execute the change for the parameter LOCAL_LISTENER

CDB2_relocate> alter system set local_listener='-oracle-none-' scope=memory;

In the listener_cdb2 listener log the service is died for the CDB2

==> listener_cdb2/trace/listener_cdb2.log <==
13-SEP-2022 12:10:29 * service_died * cdb2 * 12537

This will result into a redirect of the new connections to the new environment CDB1 with PDB1

CDB2_relocate> show parameter list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
forward_listener                     string      hol:1521
listener_networks                    string
local_listener                       string      -oracle-none-
remote_listener                      string      hol:1525

Do the test of connecting again

[oracle@hol ~]$ sqlplus c##relocate/oracle@hol:1525/relocate

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 13 12:11:02 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Mon Sep 12 2022 13:30:54 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> col CDB_NAME for a10
SQL> col PDB_NAME for a10
SQL> SELECT SYS_CONTEXT ('USERENV','CDB_NAME') as CDB_NAME,  SYS_CONTEXT ('USERENV','DB_NAME') as PDB_NAME from dual;

CDB_NAME   PDB_NAME
---------- ----------
CDB1	   PDB1

The connection request will show two logging events, one in every listener log

=> listener_cdb2/trace/listener_cdb2.log <==

2022-09-13T12:11:02.316515+02:00
13-SEP-2022 12:11:02 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=19496)) * establish * relocate * 0

==> listener/trace/listener.log <==
2022-09-13T12:11:02.330769+02:00
13-SEP-2022 12:11:02 * (CONNECT_DATA=(SERVICE_NAME=relocate)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=::1)(PORT=40100)) * establish * relocate * 0

The connection is forwarded to the an new Oracle Database located somewhere else without changing the application connection string. Now we can easy change this when we have time to do it.

DBMS_SERVICE: drain the existing service connections on the database

Using the dbms_service package we can drain the existing session, so that they will disconnect and when new request are made they will go to the new database environment

The DISCONNECT_SESSION procedure disconnects all sessions currently connected to the service. The disconnection can take one of three forms, indicated by package constants.

POST_TRANSACTION : Sessions disconnect once their current transaction ends with a commit or rollback. This is the default value (0).
IMMEDIATE : Sessions disconnect immediately. Value (1).
NOREPLAY : Sessions disconnect immediately, and are flagged not to be replayed by application continuity. Value (2).

CDB2_relocate> BEGIN
  DBMS_SERVICE.disconnect_session(
   service_name      => 'RELOCATE',
   disconnect_option => DBMS_SERVICE.post_transaction
  );
END;
/

Joords Oracle DBA blog

Pages

Tuesday, September 20, 2022

FORWARD_LISTENER : the offsite listener forwards the connection to the new Oracle listener

Lets play and test it, information of the environment

Validation of the connection to listener_cdb2 which listen on port 1525
No changed are executed on the databases and pdbs

On the relocate PDB within the CDB2 we will activate the forward_listener parameter.

DBMS_SERVICE: drain the existing service connections on the database

Blog Archive

Pages

Tuesday, September 20, 2022

FORWARD_LISTENER : the offsite listener forwards the connection to the new Oracle listener

Lets play and test it, information of the environment

Validation of the connection to listener_cdb2 which listen on port 1525No changed are executed on the databases and pdbs

On the relocate PDB within the CDB2 we will activate the forward_listener parameter.

DBMS_SERVICE: drain the existing service connections on the database

Validation of the connection to listener_cdb2 which listen on port 1525
No changed are executed on the databases and pdbs