Oracle accesses GreenPlum through DBLINK

Oracle uses DBLINK to access GreenPlum for POC for integration and migration between multiple databases, and tries to use Oracle Gateway and Heterogeneous Service for transit access. It has been tested many times, it is found that the configuration can be successful only in 32-bit Oracle. The configuration environment is as follows: Windows 2003 32bit, Windows 2008 64bitOracle10G 32bit, or Oracle11G 32 bitSource and Target: Oracle, MySQL, PostgreSQL, or GreenPlum ...... my configuration method is to use an independent terminal as a jump-like machine. The actual source db and target db are on other hosts, so there are at least three involved hosts. Linux is also possible, but it is not actually used now. The only problem with the unixODBC version that requires attention is described here. 0. When installing Oracle Database, note that an instance is included. 1. Install the psqlodbc driver. 2. Configure ODBC for GreenPlum to select ANSI. If UNICODE is used, fields of character types such as char cannot be queried or displayed. 3. configure hs sidhs \ admin \ initgp. ora: HS_FDS_CONNECT_INFO = gpHS_FDS_TRACE_LEVEL = debug4. configure hs listenerlistener. ora :( SID_DESC = (PROGRAM = dg4odbc) # hsodbc for 10g (ORACLE_HOME = C: \ oracle \ product \ 11.2.0 \ dbhome_1) (SID_NAME = gp) # (ENVS = "LD_LIBRARY_PATH =/usr/lib:/u01/app/oracle/product/10.2.0/db_1/lib") # for linux) 5. configure tnstnsnames. ora: gp = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = localhost) (PORT = 15 21) (CONNECT_DATA = (SID = gp) (HS = OK) 6. problem: 6.0. ORA-01017: invalid user name/password; login denied SQL> CREATE PUBLIC DATABASE LINK postgresql CONNECT TO dba IDENTIFIED BY "isdba" USING 'gp '; DATABASE LINK created. SQL> select * from "a1" @ postgresql; select * from "a1" @ postgresql * 1st Line Error: ORA-01017: invalid username/password; login denied FATAL: no pg_cmd.conf entry for host "10.46.182.233", user "DBA", database "loglapdb", SSL off {28000, NativeErr = 210} ORA-02063: followed by 2 lines (from POSTGRESQL) this error seems very simple, that is, a Password error. The problem is that you have confirmed that the password is correct and can use psql to log on. If you do not understand why, check the gateway log as follows: Exiting hgoinit, rc = 0 at 2013/05/10-13: 31: 36 Entered hgolgon at 2013/05/10-: 36 reco: 0, name: DBA, tflag: 0 Entered hgosuec at 2013/05/10-13: 31: 36 Exiting hgosuec, rc = 0 at 2013/05/10-13: 31: 36 HOSGIP for "reported" returned "RECOVER" HOSGIP for "reported" returned "HS_TRANSACTION_LOG" HOSGIP for "reported" returned "DATE" HOSGIP for "reported" returned "DATE" HOSGIP" HS_FDS_CHARACTER_SEMANTICS "returned" FALSE "HOSGIP" HS_FDS_MAP_NCHAR "returned" TRUE "HOSGIP for" reported "returned" FALSE "HOSGIP for" reported "returned" FALSE "using DBA as default value for" HS_FDS_DEFAULT_OWNER "HOSGIP for" HS_ SQL _HANDLE_STMT_REUSE "returned" FALSE "Entered hgocont at 2013/05/10-13:31:36 HS _ FDS_CONNECT_INFO = "gp" RC =-1 from HOSGIP for "HS_FDS_CONNECT_STRING" Entered hgogenconstr at 2013/05/10-13:31:36 dsn: gp, name: DBA optn: entered hgocip at 2013/05/10-13:31:36 dsn: gpExiting hgocip, rc = 0 at 2013/05/10-13: 31: 36 Exiting hgogenconstr, rc = 0 at 2013/05/10-13:31:36 Entered hgopoer at 2013/05/10-13:31:36 later saw a similar problem with netizens. It turns out that the username should be enclosed in double quotation marks, we can also see that the transferred user name is capitalized "DBA": reco: 0, name: DBA, tflag: 0, but actually In greenplum, the real lowercase "dba", including the password defined in dblink should also use double quotation marks, and of course sourcename. SQL> drop public database link postgresql; the database link has been deleted. SQL> CREATE PUBLIC DATABASE LINK postgresql CONNECT TO "dba" IDENTIFIED BY "isdba" USING 'gp '; the DATABASE LINK has been created. SQL> select * from "a1" @ postgresql; row 6.1 is not selected. query specified field SQL> Select Name From "t1" @ postgresql; Select Name From "t1" @ postgresql * 1st Line Error: ORA-00904: "NAME ": invalid identifier must also use double quotation marks: SQL> Select Name From "t1" @ postgresql; Select Name From "t1" @ postgresql * 1st Line Error: ORA-00904: "NAME ": invalid identifier. Note that all table names and field names in greenplum are in lower case. 6.2. ORA-00600: Internal errorcode, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [] Error reported when querying, it was confirmed that it was a hs dg4odbc bug, but this bug only appeared when Using PLD. SQL * PLUS is not triggered. Currently, my version is 11.2.0.1.0. For details, see Bug 4391058, the description indicates that 11.2 has been fixed. How can I still meet it? Someone reported that the field listing is used to replace "*", but it is still invalid on my side. -EOF-