Monthly Archives: January 2007

Creating Database Link To Transfer FSG Reports

Problem: A database link must be created in the Target instance to pull (transfer) FSG information to the production instance from the test instance.

Solution: Use Define Database Links in Oracle Applications.

To define a database link in General Ledger perform the following steps

on the Target instance (the one you are copying to):

Step 1:        Log on as sysadmin user and select a GL responsibility.

Step 2:        Navigate to Setup > System > Database Links.

There are six fields that have to be entered:

  1. Database Name
  2. Description
  3. Connect String
  4. Domain Name
  5. APPS Username
  6. APPS Password

Step 3:        In the source database, enter the following SQL query:

SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_NAME';
VALUE
--------------
PROD

This value is to be entered in the ‘Database Name’ field in Oracle Applications.

Step 4:        In the source database, enter the following SQL query:

SELECT value FROM v$parameter
WHERE UPPER(name) = 'DB_DOMAIN';
VALUE
---------------------

This value must be entered into the ‘Domain Name’ field in the form. If it is null, any value can be entered into this field (e.g. WORLD).

Step 5:        On the target database, grant privilege to apps user (log on as system user):

grant create database link to apps;

Step 6:        Ensure the two hosts are in the hosts file on each host.

127.0.0.1     localhost
128.x.x.x   oracleserver.domainname  oracleserver
128.x.x.x   oraclesrv.domainname     oraclesrv

Step 7:        Update the TNSNAMES.ORA file on each server with the entry for both instances:

PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLESERVER.domainname)
(PORT=1525))
(CONNECT_DATA=
(SID=PROD)
)
)
MFGP=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLESRV.domainname)
(PORT=1527))
(CONNECT_DATA=
(SID=MFGP)
)
)

Step 8:        Ensure that the TNSNAMES.ORA was set up correctly using tnsping to ping the remote instance.

Step 9:        Ensure that SQL Restriction is turned off

Step 10:   Bounce the 920 TNS listener on each node.

Step 11:    Ensure that you can log on the other instance using SQL*Plus.

Step 12:   Enter the following values into the form:

Field Value
Database Name PROD
Description Test Database
Connect String PROD
Domain Name WORLD (since it is null, any value can be entered)
APPS Username apps
APPS Password apps

In the SQLNET.ORA file, I had to comment out the line: sqlnet.authentication_services= (NTS)

# sqlnet.authentication_services= (NTS)

Refer to Metalink Documents:

Doc ID 161635.1: (FSG Transfer Troubleshooting Guide)

Doc ID:  Note:1057188.6 (WHERE DO YOU DEFINE DATABASE LINKS FOR FSG’S?)