如何在Oracle中配置remote_listener？实现DB2请求重定向至DB1

Hey there! Let's tackle your two Oracle configuration needs one by one—redirecting DB2 requests to DB1, and setting up remote_listener.

1. Redirecting All Requests to DB2 to DB1 #

Depending on your environment, there are a few straightforward ways to achieve this:

Option 1: Update Client TNS Names (Simplest for Client-Side Requests) #

If most requests come from client machines, modify the tnsnames.ora file on each client to point the DB2 alias directly to DB1's database.

For example, replace your existing DB2 entry:

DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db2-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2_service)
    )
  )

With this (pointing to DB1):

DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1-server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db1_service)
    )
  )

Any client using the DB2 alias will now connect to DB1 automatically.

Option 2: Configure Listener Redirection on DB2's Server #

If you can't modify every client's config, set up DB2's listener to forward incoming requests to DB1. Here's how:

1. Edit DB2's listener.ora file (usually in $ORACLE_HOME/network/admin):

   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (GLOBAL_DBNAME = db2_service)
         (SID_NAME = db2_sid)
         (ORACLE_HOME = /path/to/db2/oracle/home)
         # Redirect to DB1's listener address
         (PROGRAM = tnslsnr)
         (ARGUMENTS = "(ADDRESS=(PROTOCOL=TCP)(HOST=db1-server)(PORT=1521))")
       )
     )
   

2. Restart DB2's listener to apply changes:

   lsnrctl stop
   lsnrctl start
   

Note: This works best for basic redirects—complex routing may require additional tuning.

Option 3: Use Oracle Connection Manager (CMAN) for Advanced Routing #

For granular control (like filtering by source IP or service name), deploy Oracle Connection Manager. Add a rule to cman.ora that redirects DB2-bound traffic to DB1:

CMAN =
  (CONFIGURATION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cman-server)(PORT = 1521))
    )
    (RULE_LIST =
      (RULE =
        (SOURCE = *)
        (DESTINATION = db2-server)
        (SERVICE_NAME = db2_service)
        (ACTION = REDIRECT)
        (REDIRECT_DESTINATION = db1-server)
      )
    )
  )

Restart the CMAN service after updating the config.

2. Configuring remote_listener in Oracle #

The remote_listener parameter lets an Oracle instance register itself with a remote listener (e.g., DB2 registering with DB1's listener). Follow these steps:

Step 1: Check Current Parameter Value #

First, confirm the existing remote_listener setting in your database:

SHOW PARAMETER remote_listener;

If the value is blank, it’s not configured yet.

Step 2: Set remote_listener (Dynamic or Static) #

Dynamic Setting (Takes Effect Immediately)

Use ALTER SYSTEM to set the parameter without restarting the instance. For example, to register DB2 with DB1's listener:

-- Use direct address
ALTER SYSTEM SET remote_listener = 'db1-server:1521/db1_service' SCOPE=BOTH;

-- Or use a TNS alias (if defined in tnsnames.ora)
ALTER SYSTEM SET remote_listener = 'DB1_LISTENER' SCOPE=BOTH;

SCOPE=BOTH applies the change to both memory and the persistent parameter file (SPFILE). If you’re using a text-based PFILE, skip this and edit the file directly.

Static Setting (For PFILE Users)

Open your init<sid>.ora file and add/update the line:

remote_listener = 'db1-server:1521/db1_service'

Restart the Oracle instance to apply the change.

Step 3: Verify Registration #

On DB1's server, check if DB2 has registered with the listener:

lsnrctl services

You should see DB2's service listed under the registered services for DB1's listener.

Key Notes #

• Ensure network connectivity between DB1 and DB2 (port 1521 must be open).
• Match the SERVICE_NAME in your config to the actual service name of the target database.
• If using TNS aliases, make sure the TNS_ADMIN environment variable points to the correct directory containing tnsnames.ora.

内容的提问来源于stack exchange，提问作者FIROZ K A