5 Replies Latest reply on Feb 19, 2016 5:41 AM by Kj - Kiran Jadhav

    database link

    2907350

      Oracle Database 11gR2

      EBSR12.2.4

      Oracle Linux 6.4

       

      Hello,

       

      I want to create a database link between 2 separate machines. What is the best way to do it?

       

      Rgds

      Roshan

        • 1. Re: database link
          VishnuVinnakota

          Hi,

           

          http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205

           

          Make sure you have TNS entries defined on both the database tnsnames.ora

          Confirm with tnsping <SID>

           

          CREATE DATABASE LINK <DBLINKNAME> CONNECT TO hr IDENTIFIED BY password USING '<TNSNAME(SID)>';

          • 2. Re: database link
            2907350

            My target machine is server name PREPROD(SID)

             

            What do I need to modify in my listener.ora? Do I need to modify somthing in tnsnames.ora? My source machine(where i am creating the database link) has service name BITEST, IP: 172.25.43.147

             

            my listener .ora is

             

             

            PREPROD=

              (DESCRIPTION=

                (ADDRESS=

                  (PROTOCOL=TCP)

                  (HOST=172.25.43.145)

                  (PORT=1523)

                )

                (CONNECT_DATA=

                  (INSTANCE_NAME=PREPROD)

                  (SERVICE_NAME=PREPROD)

                )

              )

             

            SID_LIST_PREPROD =

              (SID_LIST =

                (SID_DESC =

                  (ORACLE_HOME= /u01/oracle/PREPROD/db/11.2.0)

                  (SID_NAME = PREPROD)

                )

              )

             

             

            STARTUP_WAIT_TIME_PREPROD = 0

            CONNECT_TIMEOUT_PREPROD = 10

            TRACE_LEVEL_PREPROD = OFF

            • 3. Re: database link
              Kj - Kiran Jadhav

              If you are trying to create DB link from BITEST to PREPROD, then -

               

              1. Add TNS entry of PREPROD in BITEST tnsnames.ora file.

              2. tnsping PREPROD, if its OK then

              3. CREATE DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';

              4. Verify the DB link - select * from dual@link_name;


              Regards,

              Kiran


              • 4. Re: database link
                2907350

                When i connect as sysdba user it works. I do select * from tab@bi_apps;

                 

                But when I connect as XX_OCEAN/XX_OCEAN and connect it says:

                 

                ORA-02019: connection description for remote database not found

                • 5. Re: database link
                  Kj - Kiran Jadhav

                  It seems that you have created the DB link with SYS user.

                   

                  Use below query to check owner of DB link :

                   

                  SQL> select owner,DB_LINK from dba_db_links;

                   

                  There are 2 types of DB link Public and Private:

                   

                  Public : You can access the DB link with any DB user

                  Private : You can access the DB link with DB link owner only.

                   

                  If you want to access DB link from any schema then create public database link else create private DB link by connecting to XX_OCEAN schema.

                   

                  Syntax for Public DB link creation :

                   

                  CREATE PUBLIC DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';


                  Syntax for Private DB link creation :


                  CREATE DATABASE LINK link_name CONNECT TO target_db_user IDENTIFIED BY target_db_user_paswd USING 'target_tns_alias';



                  Regards,

                  Kiran

                  1 person found this helpful