1 2 Previous Next 23 Replies Latest reply on Oct 29, 2018 1:11 AM by Beauty_and_dBest

    Db Link for FSG report transfer

    Beauty_and_dBest

      EBS 12.2.7

      12c

      OL6

       

      I am creating a database link for FSG transfer, but I got error below

       

      APP-RG-09518: An error occurred while creating a database link. Please ensure that all entered parameters are correct

       

       

       

      But I can create successfully the database link at database backend using:

       

      SQL> create database link fit_trn  connect to apps identified by appsusing 'fit_trn';

       

       

      Please help how to troubleshoot dblink error in forms.

       

       

      Kind regards,

      jc

        • 1. Re: Db Link for FSG report transfer
          John_K

          Your connect string needs to be the TNS entry.

           

           

          i.e.

           

          (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp)(PROTOCOL = TCP)(Host = xxxxxxx)(Port = 1521)))(CONNECT_DATA = (SID = xxxxx)))

          • 2. Re: Db Link for FSG report transfer
            Beauty_and_dBest

            Thanks John,

             

            Are the following description correct?

             

            1. Database Name  =  SID of the remote database to connect to

            2. Domain Name  <<< what do I put in here? I do not have value of "DOMAIN_NAME" is v$parameter    Do I just copy the /etc/hosts domain?

             

             

            Kind regards,

            • 3. Re: Db Link for FSG report transfer
              John_K

              Unless you have a different domain setup, use "WORLD".

              Database Name doesn't nee to be the SID - that's contained in the connect string. The database name will be what your resulting db link is called on the database; so whilst it's probably good practice, it's not mandatory.

              It just creates a DB Link on the Database using the fields as follows:

              • 4. Re: Db Link for FSG report transfer
                Beauty_and_dBest

                Hi John,

                 

                I tried it many different combinations but still to no avail

                Why does oracle not able to simply show what error is it looking for?

                So depressing to use this form.

                 

                 

                Kind regards,

                • 5. Re: Db Link for FSG report transfer
                  John_K

                  Check your domain is WORLD then - do "select * from dba_db_links" and see what links already exist.

                   

                  Failing that, construct the create statement using the graphic I showed above, and run it on the database - that might give you a bit of a better indication of where the issue lies. I'd imagine it to be the connect identifier or the domain.

                  • 6. Re: Db Link for FSG report transfer
                    Beauty_and_dBest

                    Thanks John,

                     

                    As I have mentioned above, creating database link on the database is successful :

                     

                    SQL> create database link fit_trn  connect to apps identified by appsusing 'fit_trn';

                     

                     

                     

                    TNS_NAMES

                    FIT_TRN=

                            (DESCRIPTION=

                                    (ADDRESS=(PROTOCOL=tcp)(HOST=trnsrvr.oraclecloud.com)(PORT=1521))

                                (CONNECT_DATA=

                                    (SERVICE_NAME=FIT_TRN)

                                    (INSTANCE_NAME=FIT_TRN)

                                )

                            )

                     

                     

                    I also subtituted above tns in the in connect_string field, but still the same error

                     

                    Kind regards,

                    • 7. Re: Db Link for FSG report transfer
                      lmu

                      Yes, it is totally annoying.  I did this the other day.

                      If you use the tns_names entry for the connect string. Test it creating the link manually.  When you get that to work, then try the same line in the form.

                       

                      Make sure you dropped the link you created manually. it won't create two links with the same name.

                      • 8. Re: Db Link for FSG report transfer
                        John_K

                        I meant create your database link using the text from the tns entry, not using the tns name.

                        • 9. Re: Db Link for FSG report transfer
                          Beauty_and_dBest

                          Hi Imu,

                           

                          Yes,  I have created it manually, and it worked. I can select tables from the remote database.

                          That is why I named the 2nd link in the form as FIT_TRN2.

                           

                          Hi John,

                           

                          Yes I used the text only as :

                          (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=trnsrvr.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FIT_TRN)(INSTANCE_NAME=FIT_TRN)))

                           

                           

                           

                          Kind regards,

                          • 10. Re: Db Link for FSG report transfer
                            John_K

                            Ok, so when you did this on the database:

                             

                            create database link blah connect to apps identified by fnd using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=trnsrvr.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FIT_TRN)(INSTANCE_NAME=FIT_TRN)))';

                             

                            then

                             

                            select * from dual@blah;

                             

                            Did that work?

                            • 11. Re: Db Link for FSG report transfer
                              John_K

                              Also, the form just calls "RG_XFER_UTILS_PKG.create_link", which does this:

                               

                                CursorId := DBMS_SQL.open_cursor;

                                DBMS_SQL.parse(CursorId,

                                               'CREATE DATABASE LINK '|| LinkName ||

                                               ' CONNECT TO '|| Username ||' IDENTIFIED BY '|| Password ||

                                               ' USING '''|| ConnectString ||'''',

                                               DBMS_SQL.v7);

                                ExecuteValue := DBMS_SQL.execute(CursorId);

                                DBMS_SQL.close_cursor(CursorId);

                               

                              and it calls that like this:

                               

                                  CreateRetVal := RG_XFER_UTILS_PKG.create_link(

                                            :CREATE_LINK.name || '.' || :CREATE_LINK.domain_name,

                                            :CREATE_LINK.apps_username,

                                            :CREATE_LINK.apps_password,

                                            :CREATE_LINK.connect_string);

                               

                              so you could check that replacing those values works on the db.

                              • 12. Re: Db Link for FSG report transfer
                                Kanda-Oracle

                                Hi JC

                                 

                                  You may mask the server names and other details.

                                 

                                To verify that credentials are correct,

                                • Can you add them in any 'tnsnames.ora' file
                                • Test it out using "tnsping".
                                • This confirms , credentials (host,port..) are correct
                                  • It helps to rule out the doubt,whether you have any bad entries.

                                 

                                Thanks


                                N Kandasamy

                                • 13. Re: Db Link for FSG report transfer
                                  Kanda-Oracle

                                  This note is on older version but you may still refer those debug steps.

                                   

                                   

                                   

                                  Hope this helps!

                                  • 14. Re: Db Link for FSG report transfer
                                    Beauty_and_dBest

                                    Hi John, Kanda, Imu, and ALL,

                                     

                                    SQL>  create database link blah connect to apps identified by fit_trn using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=trnsrvr.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FIT_TRN)(INSTANCE_NAME=FIT_TRN)))';

                                     

                                    Database link created.

                                     

                                    SQL> select * from dual@blah;

                                     

                                    D

                                    -

                                    X

                                     

                                     

                                    $ tnsping fit_trn

                                     

                                    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 26-OCT-2018 15:14:57

                                     

                                    Copyright (c) 1997, 2014, Oracle.  All rights reserved.

                                     

                                    Used parameter files:

                                    /u02/orauat/UAT/12.1.0/network/admin/UAT_trnsrvr/sqlnet_ifile.ora

                                     

                                     

                                    Used TNSNAMES adapter to resolve the alias

                                    Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=trnsrvr.oraclecloud.com)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=FIT_TRN) (INSTANCE_NAME=FIT_TRN)))

                                    OK (0 msec)

                                    [orauat@trnsrvr ~]$

                                     

                                     

                                     

                                     

                                     

                                     

                                    Kind regards,

                                    1 2 Previous Next