DB link Cross platform

DB link Cross platform

Important information user have to provide:
-----------------------------------------------------------------------------------------------------------------------------------
1. Source Server Name.
2. Source DB-Name.
3. Source DB-Link Owner schema name.
4. DB-Link name
5. Target Server name.
6. Target Port number.
7. Target DB-Name.
8. Target Username/password.

Example

-----------------------------------------------------------------------------------------------------------------------------------
1. Source Server Name            -    ora1.svr.us.abc.net
2. Source DB-Name.            -    ABCD
3. Source DB-Link Owner schema name.    -    PUBLIC
4. DB-Link name                -    GLOBAL_MX_DTCC_2
5. Target Server name.            -    TARGET.uk.ABC.com
6. Target Port number.            -    1551
7. Target DB-Name.            -    GLOBAL_MX_DTCC_PHASE2
8. Target Username/password.        -    GLOBAL_MX_DTCC2_USER/"Password".


DB-Link Creation Process:

1. Create pfile as << initdg4DATABASENAME.ora >> under directory location << $ORACLE_HOME/dg4msql/admin >>:
EXAMPLE:
-------------------------
XXXXXXXX: /export/NRD/oracle/product/11.1.0/db_1/dg4msql/admin>
-rw-r--r-- 1 oracle dba   397 Aug 20 09:26 initdg4GLOBAL_MX_DTCC_PHASE2.ora

$ cat initdg4GLOBAL_MX_DTCC_PHASE2.ora
------------------------------------------------------------------------------------------------------------------------------------
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=TARGET.uk.ABC.com:1433
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


2. Update tnsnames.ora under $ORACLE_HOME?network/admin with ADDRESS/CONNECT_DATA as mentioned below in example:

Tnsnames.ora entry example:
--------------------------------------------------------------------------------------------
dg4DATABASE-Name =
    (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST= ora1.svr.us.abc.net)(PORT=1551))
        (CONNECT_DATA=(SID=dg4GLOBAL_MX_DTCC_PHASE2))
        (HS=OK)
     )


3. create cross platform DB-Link using below command on sqlplus:

DDL for Cross platform DB-Link:
---------------------------------------------------------------------------------------------
CREATE PUBLIC DATABASE LINK "GLOBAL_MX_DTCC_2" CONNECT TO "GLOBAL_MX_DTCC2_USER" IDENTIFIED BY password USING '(DESCRIPTION= (ADDRESS=PROTOCOL=tcp)(HOST= ora1.svr.us.abc.net)(PORT=1551)) (CONNECT_DATA=(SID=dg4GLOBAL_MX_DTCC_PHASE2)) (HS=OK))';


4. Restart listener associated with Database in which this DB link is created Note: restarting listener is the requirement for creating crossplatform listener.


5. Check status of restarted listener it should show entry like below:
--------------------------------------------------------------------------------------------------
Service "dg4GLOBAL_MX_DTCC_PHASE2" has 1 instance(s).
  Instance "dg4GLOBAL_MX_DTCC_PHASE2", status UNKNOWN, has 1 handler(s) for this service...


6. check if the DB-Link is working fine, using below on sqlplus:
---------------------------------------------------------------------------------------------------
SQL> select sysdate from dual@GLOBAL_MX_DTCC_2;

SYSDATE
---------
20-Aug-15