Database Link ORACLE to POSTGRESQL.

Luan Huynh
    Hi all.
    Nice day !
    I want to create connection from oracle to postgres.
    Here are my configs:
    1/ $oracle_home/hs/admin/initPSQL.ora
    HS_FDS_CONNECT_INFO = PSQL
    HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so
    set ODBCINI=/etc/odbc.ini

    2/ listener.ora
    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=dg4odbc)
    (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    (PROGRAM=dg4odbc)
    )
         (SID_DESC=
              (SID_NAME=PSQL)
              (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/)
              (PROGRAM=dg4odbc))
    )

    3/ tnsname.ora
    PSQL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = PSQL)
    )
         (HS=OK)
    )

    5/ Driver POSTGRESQL ODBC:
    Description=ODBC for PostgreSQL
    Driver = /usr/lib/libodbcpsql.so


    But when I create database link :

    MY QUERY:
    |
    |
    CREATE public DATABASE LINK l test
    CONNECT TO "postgres" IDENTIFIED BY "123456"
    USING 'PSQL';
    SELECT id FROM tb_abc@luanmap_test ;


    ERRORS:
    |
    |
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    ORA-02063: preceding line from LUANMAP_TEST
    28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
    *Cause:    The cause is explained in the forwarded message.
    *Action:   See the non-Oracle system's documentation of the forwarded
    message.
    Error at Line: 9 Column: 33

    Please help me or give me some advices. Thanks.
    Luan !
      • 1. Re: Database Link ORACLE to POSTGRESQL.
        asahide
        Hi,

        Did your "Create database link" Success ?
        CREATE public DATABASE LINK l test
        CONNECT TO "postgres" IDENTIFIED BY "123456"
        USING 'PSQL';
        SELECT id FROM tb_abc@luanmap_test ;
        your dblink name is named test , so you have to query follows..
        SELECT id FROM tb_abc@test;
        Regards,
        • 2. Re: Database Link ORACLE to POSTGRESQL.
          Luan Huynh
          @asahideO : thanks. sry, name "test0" (not "luanmap_test"), i fixxed it. But I still get this error.

          @asahideO : thanks. sry, name "test0" (not "luanmap_test"), i fixxed it. But I still get this error.

          In .../hs/log
          I get:
          ||
          ||
          Oracle Corporation --- FRIDAY FEB 22 2013 12:23:44.404
          Heterogeneous Agent Release
          11.2.0.1.0
          HS Gateway: NULL connection context at exit
          ||
          ||

          Edited by: 970144 on 20:10 24-02-2013
          • 3. Re: Database Link ORACLE to POSTGRESQL.
            asahide
            Hi,

            Put results of dba_db_links and tnsping PSQL, pls.

            Regards,
            • 4. Re: Database Link ORACLE to POSTGRESQL.
              Kgronau-Oracle
              Look at HS_FDS_SHAREABLE_NAME - you have configured it pointing to the ODBC Driver "HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so" but all PostGres ODBC Drivers I know don't have all ODBC functions built in - hence it requires also an ODBC Driver Manager like for example the Driver Manager from www.unixODBC.org.

              So please check out if you already have an ODBC Driver Manager installed on your machine and if the word size of the Driver Manager matches the owrd size of the ODBC Driver as well as DG4ODBC. If you don't have an ODBC Driver manager, please get it from UnixODBC and apply it to your system. Once done, change the configuration and make sure HS_FDS_SHAREABLE_NAME points to the ODBC Driver Manager instead of the ODBC driver itself.


              - Klaus
              • 5. Re: Database Link ORACLE to POSTGRESQL.
                Luan Huynh
                @kgronau : thanks.
                When I debug I get this error " Failed to load ODBC library symbol: /usr/lib/libodbcpsql.so(SQLAllocHandle) "
                So. I installed unixODBC and I try to fix all @@
                • 6. Re: Database Link ORACLE to POSTGRESQL.
                  Kgronau-Oracle
                  Root cause for the missing handle (SQLAllocHandle) is that the Postgres ODBC driver does not include generic ODBC functions and also requires a Driver Manager. So yes, please go ahead and apply the UnixODBC Driver Manager to your env.

                  - Klaus
                  • 7. Re: Database Link ORACLE to POSTGRESQL.
                    Luan Huynh
                    @kgronau : I installed unixODBC using this command "yum install unixODBC".

                    Your guide " so yes, please go ahead and apply the UnixODBC Driver Manager to your env "

                    I understand "edit ENVS=LD_LIBRARY_PATH=odbc_library_dir:oracle_home_directory/lib in listener.ora" ??? Is is true

                    PS: sry about my english skill ^_^.
                    • 8. Re: Database Link ORACLE to POSTGRESQL.
                      Kgronau-Oracle
                      Installing unixODBC this way is commonly fine but please make sure it is a 2.3.x release.

                      Regarding the LD_LIBRARY_PATH in the listener.ora file I commonly prefer to set it there as well, but in general just having all libs specified in the LD_LIBRARY_PATH for the user who starts the Oracle listener is also fine.

                      When you specify it in the listener.ora file, please use:
                      (ENV="LD_LIBRARY_PATH=<Oracle_Home>/lib:<UnixODBC>/lib:<ODBC-Driver>/lib")

                      - Klaus
                      • 9. Re: Database Link ORACLE to POSTGRESQL.
                        Luan Huynh
                        I try to do but can not.

                        hs/initpsqltest.ora
                        ||
                        HS_FDS_CONNECT_INFO = DNS_PSQL
                        HS_FDS_TRACE_LEVEL = DEBUG
                        HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so
                        set ODBCINI=/etc/odbc.ini
                        ||

                        Errors:
                        ....
                        [unixODBC][Driver Manager] Data Source name
                        ....

                        And in log file (hs/log)

                        ....
                        hgopoer, line 233: got native error 0 and sqlstate I; message follows...
                        Exiting hgopoer, rc=0 at 2013/02/22-17:41:53
                        hgocont, line 2752: calling SqlDriverConnect got sqlstate I
                        Exiting hgocont, rc=28500 at 2013/02/22-17:41:53 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization
                        Exiting hgolgon, rc=28500 at 2013/02/22-17:41:53 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont
                        Entered hgoexit at 2013/02/22-17:41:53
                        Exiting hgoexit, rc=0
                        ......

                        Edited by: 970144 on 00:19 25-02-2013
                        • 10. Re: Database Link ORACLE to POSTGRESQL.
                          Kgronau-Oracle
                          Would be good to see also the error message from SQL*Plus ... so I can just guess and would suggest to add:
                          HS_NLS_NCHAR = UCS2
                          HS_LANGUAGE=american_america.we8iso8859p1
                          to the gateway config file. Then please make sure to close SQL+plus and connect again, then test the DB link.

                          - Klaus
                          1 位用户发现它有用
                          • 11. Re: Database Link ORACLE to POSTGRESQL.
                            Luan Huynh
                            Thanks kgronau
                            I know this error: "[unixODBC][Driver Manager]Data source name not found, and no default driver specified {IM002}"
                            I try to solve it. Hope I can find soon.

                            I find it: http://www.unixodbc.org/ODBCFlow.gif
                            • 12. Re: Database Link ORACLE to POSTGRESQL.
                              Kgronau-Oracle
                              it commonly has to do with your odbc.ini file.

                              So as the basic test you should test with isql, an ODBC test utility shipped with unixODBC. This isql tool requires the environment variable ODBCINI which must be set to the odbc.ini file. According to he gateway init file you're using this odbc.ini file: /etc/odbc.ini

                              Does it contain a DSN pointing to your PostgreSQL database?
                              The DSN will also include DRIVER parameter pointing to the ODBC Driver itself (DRIVER=/usr/lib/libodbcpsql.so) as well as Database, Servername and Port. There are a couple of additional parameters which you might have to set - so best would be to check the PostgreSQl ODBC docu for the driver version you're using.
                              • 13. Re: Database Link ORACLE to POSTGRESQL.
                                Luan Huynh
                                Sry:
                                Your suggestion " (ENV="LD_LIBRARY_PATH=<Oracle_Home>/lib:<UnixODBC>/lib:<ODBC-Driver>/lib")"
                                & I do "(ENV=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/11.2.0/dbhome_1/lib)" Is is ok ?

                                In /usr/lib I copy all lib (libodbc.so, libodbcpsql.so ...) into here.

                                Here are 2 files: odbc.ini, odbcinst.ini (etc/odbc.ini ...)

                                odbc.ini
                                [DNS_PSQL]
                                Description = Test by Luan
                                Driver = PostgreSQL
                                DNS = DNS_PSQL
                                Trace = No
                                TraceFile = /tmp/odbc.log
                                Database = postgres
                                Servername = 10.1.101.161
                                UserName = postgres
                                Password = 123456
                                Port = 5432
                                Servertype = postgres
                                ReadOnly = No

                                odbcinst.ini
                                [PostgreSQL]
                                Description=ODBC for PostgreSQL
                                Driver=/usr/lib/libodbcpsql.so
                                FileUsage=1
                                Setup=/usr/lib/libodbcpsqlS.so
                                • 14. Re: Database Link ORACLE to POSTGRESQL.
                                  Kgronau-Oracle
                                  Yes, LD_LIBRARY_PATH setting is fine.
                                  When using a named Driver configuration Driver = PostgreSQL you also need to refer to the odbcinst.ini file - I commonly prefer to specify the driver and the full path in the odbc.ini file like:
                                  Driver=/usr/lib/libodbcpsql.so


                                  So once you exported ODBCINI environment variable:
                                  export ODBCINI=/etc/odbc.ini test the connection with isql (isql DNS_PSQL uid pwd)
                                  1 2 3 上一个 下一个