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 上一个 下一个