1 2 Назад Вперед 28 Ответы Последний ответ: 20.09.2015 8:04, автор: Bashar.

    Create DBlink from APPS database to normal Oracle DB

    Dr.Anty

      Hi,

      Could you please help me out to make dblink from Apss DB to Oracle DB. (Core database).

       

      Regards,

        • 1. Re: Create DBlink from APPS database to normal Oracle DB
          Pravin Takpire

          Use below command

           

          CREATE DATABASE LINK CORE_DB -- CORE_DB is db link name

             CONNECT TO scott IDENTIFIED BY tiger

             USING 'sales'; -- sales is tns details of core database added in tnsnames.ora for apps database.

           

          regards

          Pravin

          • 2. Re: Re: Create DBlink from APPS database to normal Oracle DB
            Dr.Anty

            CREATE DATABASE LINK CORE_DB -- CORE_DB is db link name

              CONNECT TO scott IDENTIFIED BY tiger

              USING 'sales'; -- sales is tns details of core database added in tnsnames.ora for apps database.

            اHello

            I run this command on CRM database to create db link.

            SQL> CREATE DATABASE LINK crm_db

              2  CONNECT TO GULF IDENTIFIED BY oracle123

              3  using 'gndb';

            Database link created.

             

            I have DB CRM is enterprise edition database.

             

            GNDB is a Stranded edition database.

             

            crm_db  =====DB link name

            GULF IDENTIFIED BY oracle123  =====Schema name  and password on GNDB database.

            gndb ============  tns details of GNDB.   I added on in tnsnames.ora for CRM


            tnsnames.ora on CRM db.

            [oracle@ora_crm admin]$ more tnsnames.ora

             

             

            CRM=

              (DESCRIPTION =

                (ADDRESS_LIST =

                  (ADDRESS = (PROTOCOL = TCP)(HOST =ora_crm)(PORT = 1521))

                (CONNECT_DATA =

                  (SERVICE_NAME = CRM)

                )

              )

             

             

            GNDB=

              (DESCRIPTION =

                (ADDRESS_LIST =

                  (ADDRESS = (PROTOCOL = TCP)(HOST =gndb)(PORT = 1521))

                (CONNECT_DATA =

                  (SERVICE_NAME = gndb)

                )

              )

             

            needs when connect on GNDB  i can select from CRM DB objects.


            select * from ACCOUNTBASE@crm_db

            ORA-02019: connection description for remote database not found


            Regrads,

            Ahmed.

            • 3. Re: Create DBlink from APPS database to normal Oracle DB
              Pravin Takpire

              give me output of below

               

              tnsping gndb

               

              telnet gndb 1521

               

              regards

              Pravin

              • 4. Re: Re: Create DBlink from APPS database to normal Oracle DB
                Dr.Anty

                Pravin Takpire wrote:

                 

                give me output of below

                 

                tnsping gndb

                 

                [oracle@gndb ~]$ tnsping gndb

                 

                TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-SEP-2015 14:09:02

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

                Used parameter files:

                Used TNSNAMES adapter to resolve the alias

                Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =gndb)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = gndb)))

                TNS-12533: TNS:illegal ADDRESS parameters.

                 

                telnet gndb 1521

                 

                [oracle@gndb ~]$ telnet gndb 1521

                Trying 192.168.40.25...

                Connected to gndb.

                Escape character is '^]'.

                 

                regards

                Ahmed

                • 5. Re: Create DBlink from APPS database to normal Oracle DB
                  Pravin Takpire

                  Looks like there is one missing parenthesis in GNDB details

                   

                  Use below

                   

                  GNDB=

                    (DESCRIPTION =

                      (ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST =gndb)(PORT = 1521)))

                      (CONNECT_DATA =(SERVICE_NAME = gndb))

                    )

                   

                  regards

                  Pravin

                  • 6. Re: Re: Create DBlink from APPS database to normal Oracle DB
                    Dr.Anty

                    Hello,

                    tnsping gndb  working fine now.


                    but still can not perform the query.

                     

                    select * from ACCOUNTBASE@crm_db

                    ORA-02019: connection description for remote database not found


                    oracle@gndb ~]$ tnsping gndb

                    TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-SEP-2015 14:54                                              :3

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

                    Used parameter files:

                    Used TNSNAMES adapter to resolve the alias

                    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)                                              (HOST =gndb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gndb)))

                    OK (20 msec)

                     

                    regards,

                    Ahmed.

                    • 7. Re: Create DBlink from APPS database to normal Oracle DB
                      Bashar.

                      Hi,

                       

                      Use this statement to create the database link:

                       

                      CREATE DATABASE LINK crm_db

                      CONNECT TO GULF IDENTIFIED BY oracle123

                      using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =gndb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gndb)))';

                       

                      Regards,

                      Bashar

                      • 8. Re: Re: Create DBlink from APPS database to normal Oracle DB
                        Dr.Anty

                        Bashar. wrote:

                         

                        Hi,

                         

                        Use this statement to create the database link:

                         

                        CREATE DATABASE LINK crm_db

                        CONNECT TO GULF IDENTIFIED BY oracle123

                        using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =gndb)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = gndb)))';

                         

                        Regards,

                        Bashar

                        SQL> CREATE DATABASE LINK crm_db

                          2  CONNECT TO GULF IDENTIFIED BY oracle123

                          3  using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =gndb)(PORT = 1521)))

                          4  (CONNECT_DATA = (SERVICE_NAME = gndb)))';

                        Database link created.

                         

                        select * from ACCOUNTBASE@crm_db

                        ORA-02019: connection description for remote database not found

                         

                        Hi Bshar,

                         

                        I create the dblink on CRM DB. ( run the above command on CRM DB)

                        gulf is a schema on GNDB.

                        needs the select data from CRM DB on GNDB.

                         

                        By the way CRM is EE database

                              GNDB is SE database

                         

                        regards,

                        Ahmed.

                        • 9. Re: Re: Create DBlink from APPS database to normal Oracle DB
                          Bashar.

                          I create the dblink on CRM DB. ( run the above command on CRM DB)

                          gulf is a schema on GNDB.

                          needs the select data from CRM DB on GNDB.

                           

                          Please explain the requirement again.

                          • 10. Re: Re: Create DBlink from APPS database to normal Oracle DB
                            Dr.Anty

                            I have tow database

                            1) CRM

                            2)GNDB

                             

                            needs to make dblink Between them to get information from CRM on GNDB.

                             

                            I mean i want issue select on GNDB such as ( select * from ABC), ABC table on CRM DB..

                             

                            when make dblink for the db, it will be on database level or schema level?

                            CONNECT TO GULF IDENTIFIED BY oracle123 ===== GULF is a schema on GNDB database.


                            I hope it's clear to you

                            thanks in advance for your help Bashar, you solved before to me  critical issue.

                             

                             

                            regards,

                            Ahme.

                             

                            • 11. Re: Create DBlink from APPS database to normal Oracle DB
                              Pravin Takpire

                              Post contents of listener.ora and sqlnet.ora (if available) of GNDB instance

                              regards

                              Pravin

                              • 12. Re: Re: Create DBlink from APPS database to normal Oracle DB
                                Dr.Anty

                                LISTENER =

                                  (DESCRIPTION_LIST =

                                    (DESCRIPTION =

                                      (ADDRESS = (PROTOCOL = TCP)(HOST = gndb)(PORT = 1521))

                                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                                    )

                                  )

                                 

                                 

                                ADR_BASE_LISTENER = /oracle

                                • 13. Re: Create DBlink from APPS database to normal Oracle DB
                                  Bashar.

                                  Now I see that you want to connect from GNDB to CRM DB.

                                  At first I thought you wanted to do the contrary.

                                   

                                  The database link you created actually tries to connect to GNDB (loop-back).

                                  This is not what you intend.

                                  Therefore, you should create it as follows:

                                   

                                  CREATE DATABASE LINK crm_db

                                  CONNECT TO <user owning the ACCOUNTBASE table> IDENTIFIED BY <password>

                                  using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =<CRM db hostname or IP address>)(PORT = <port>))) (CONNECT_DATA = (SERVICE_NAME = <database SID>)))';

                                   

                                  Substitute the appropriate values for the text enclosed with <>.

                                   

                                  The database link is created in the schema you used to create it unless you specify the "public" keyword which makes it available for all db users.

                                   

                                  Regards,

                                  Bashar

                                  • 14. Re: Re: Create DBlink from APPS database to normal Oracle DB
                                    Dr.Anty

                                    SQL> CREATE DATABASE LINK crm_db

                                      2  CONNECT TO GN_MSCRM IDENTIFIED BY GN_MSCRM

                                      3  using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST =ora_crm)(PORT =1521))) (CONNECT_DATA = (SERVICE_NAME = crm)))';

                                     

                                    I issue this sql on CRM database.

                                    still facing the same error.

                                     

                                    I run the below command on GNDB.

                                    SELECT * from contractbase@crm_db;

                                    ORA-02019: connection description for remote database not found


                                    Regards,


                                    1 2 Назад Вперед