Finally I managed to connect to the remote database using Oracle Connection Manager, after struggling for almost a week. The requirement requires the Oracle connection and firewalls. A remote Oracle client making a connection to an Oracle database can fail if there is a firewall installed between the client and the server if port redirection is taking place.The firewall will block the connection to the new port when the Oracle client connects to the database  ORA12203 or ORA-12535 or ORA-12564. The client connection failure is due to port redirection from the Database Server‘s operating system. Port redirection requires the client to connect to the database using a different port than originally configured in the configuration files. Actually, it is not difficult if you know but overall took me few days ding dong here and there trial and error where to place the CMAN and wat IP address to use. Many thanks to Prince Amin who show me the Network Structure and advice me to place the CMAN at the Remote Site. I was blur at that time. Two heads is better than One !!!! Prince Amin, I owe you dude…..
Solution: Oracle Connection Manager (CMAN)

Oracle Connection Manager is available from Oracle Net8 and requires configuration of CMAN to allow the clients to connect through firewall. CMAN is executable that allows clients to connect despite a firewall being in place between the client and the server. CMAN is similar to the listener that it reads a CMAN.ORA configuration file, which contains an address that Oracle Connection Manager listens for in comming connections, usually default port of 1610 or 1630. The oracle client must be Net8 or above.

Network Setup

SITE A SITE B(remote DB)

FW/NAT
internal IP | external IP external IP internal IP CMAN
Client(iii.iii.iii.iii) — ->(xxx.xxx.xxx.xxx) — ->| — ->(yyy.yyy.yyy.yyy) — ->(zzz.zzz.zzz.zzz)|database
|

FW/NAT

Sample Configuration:

Given the following IP address space, the following configuration is based on a working CMAN / NAT environment. This configuration assumes the NAT router is also a firewall and has the TCP port 1610 to be made available for use on the Firewall / NAT Router.

Client —> external IP Address: (xxx.xxx.xxx.xxx) outbound from the Firewall/NAT
Externally Advertised NAT IP Address: (yyy.yyy.yyy.yyy)
CMAN —> internal IP Address: (zzz.zzz.zzz.zzz)
DB Server –>internal IP Address: (zzz.zzz.zzz.zzz) CMAN is located in the same machine of the DB.

Files affected in the configuration are TNSNAMES.ORA (client), CMAN.ORA (connection manager) and LISTENER.ORA (remote site)

The TNSNAMES.ORA file on client (this is where i was stucked before Prince Amin came and save me..hehehehh)

EXAMLE_CMAN.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yyy.yyy.yyy.yyy)(PORT = 1610))
(ADDRESS = (PROTOCOL = TCP)(HOST = zzz.zzz.zzz.zzz)(PORT = 1521))
)

(CONNECT_DATA =
(SERVICE_NAME = test)
)

(SOURCE_ROUTE = YES)
)

The CMAN.ORA file. There are 3 sections in the CMAN.ORA. file that need to configure, CMAN, CMAN_ADMIN & CMAN_PROFILE.

REM
REM cman‘s listening addresses
REM

cman = (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=zzz.zzz.zzz.zzz)(PORT=1610)(QUEUESIZE=32))
)

cman_admin = (ADDRESS=(PROTOCOL=tcp)(HOST=zzz.zzz.zzz.zzz)(PORT=1830))

REM
REM cman‘s configurable params
REM
REM MAXIMUM_RELAYS defaults to 128
REM LOG_LEVEL defaults to 0
REM TRACING defaults to no
REM TRACE_DIRECTORY defaults to …/network/trace
REM RELAY_STATISTICS defaults to no
REM SHOW_TNS_INFO defaults to no
REM USE_ASYNC_CALL defaults to yes
REM AUTHENTICATION_LEVEL defaults to 0
REM Note: If AUTHENTICATION_LEVEL=1 then use SQLNET.AUTHENTICATION_SERVICES= (NTS) in SQLNET.ORA
REM MAXIMUM_CONNECT_DATA defaults to 1024
REM ANSWER_TIMEOUT defaults to 0
REM MAX_FREELIST_BUFFERS defaults to 2048
REM

cman_profile = (PARAMETER_LIST=
(MAXIMUM_RELAYS=1024)
(LOG_LEVEL=1)
(TRACING=no)
(RELAY_STATISTICS=yes)
(SHOW_TNS_INFO=yes)
(USE_ASYNC_CALL=yes)
(AUTHENTICATION_LEVEL=0)
(REMOTE_ADMIN=FALSE)
)

Startup the Connection Manager services. The command to activate is CMCTL.
Type CMCTL START CMAN|ADM. And you should be able to check the services up and running using windows services.

The LISTENER.ORA in the DB server must include a listening port for CMAN that is 1610.

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzz.zzz.zzz.zzz)(PORT = 1521))
)

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzz.zzz.zzz.zzz)(PORT = 1610))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test.world)
(ORACLE_HOME = c:\oracle\ora)
(SID_NAME = test)
)

)

With this configurations I would be able to connect to the remote DB sucessfully.