http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [195]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2917]
PostgreSQL [103]
기타정보 [151]
운영체제 [584]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

psql dblink to oracle [ odbc test ] 한글
최길호 [LIST]   2021-04-19 23:25:03, 조회 : 2,498

추가
한글 조치
http://iotn.co.kr/power/zboard.php?id=9_1_bbs&no=2981

=============================== PostgreSQL Server
로그인 가능 하도록 config, DB, 계정 생성

su - postgres
vi $PGDATA/postgresql.conf
## 서버 모든 IP 로그인 가능
listen_addresses = '*'
#listen_addresses = 'localhost'

telnet x.x.x.x 5432 포트 열리는지 확인 [ pg_ctl reload는 안된다. stop/start ]

vi $PGDATA/pg_hba.conf
## 모든 Client 로그인 가능
host    all             all             0.0.0.0/0           md5

# 방화벽 서비스 추가
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload

## 재시작
pg_ctl stop
pg_ctl start
pg_ctl reload

## DB,계정 생성
psql
postgres=# create user gilho password 'gilho';
CREATE ROLE
postgres=# create database gilho owner gilho;
CREATE DATABASE
postgres=# \c gilho gilho
Password for user gilho:
You are now connected to database "gilho" as user "gilho".
gilho=> create table a ( a int);
CREATE TABLE
gilho=> \q


=============================== ODBC [ Oracle DB Server ]
ODBC 사용해서 PostgreSQL 서버 로그인 확인

yum install postgresql-odbc
or
Linux 7 OS DVD mount rpm 설치
# mount /dev/sr0 /mnt
# yum install postgresql-odbc-09.03.0100-2.el7.x86_64.rpm
....
Installing : postgresql-libs-9.2.24-4.el7_8.x86_64  
Installing : postgresql-odbc-09.03.0100-2.el7.x86_64

cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

cat >> /etc/odbc.ini
[psql]
Driver=PostgreSQL
Description=ODBC for PostgreSQL
Servername=node4
Port=5432
Protocol=9.2
FetchBufferSize=99
Username=gilho
Password=pwd
Database=gilho
ReadOnly=no
Debug=1
Trace = yes
CommLog=1
UseDeclareFetch=0
TraceFile=/tmp/sql.log
UseServerSidePrepare=1
dbms_name=PostgreSQL

isql -v psql
SQL> select * from a;
+------------+
| a          |
+------------+
+------------+
SQLRowCount returns 0
SQL> quit

=============================== Oracle DB
cat > $ORACLE_HOME/hs/admin/initpsql.ora
HS_FDS_CONNECT_INFO = psql
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini

cat >> $ORACLE_HOME/network/admin/listener.ora
PSQL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 5432))
    )
  )
LOGGING_PSQL = OFF
SID_LIST_PSQL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = psql )
      (ORACLE_HOME = /oracle/db )
      (PROGRAM = dg4odbc )
    )
  )

lsnrctl start psql
Service "psql" has 1 instance(s).
  Instance "psql", status UNKNOWN, has 1 handler(s) for this service...

cat >> $ORACLE_HOME/network/admin/tnsnames.ora
psql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 5432))
    (CONNECT_DATA =
      (SID = psql)
    )
    (HS = OK)
  )

tnsping psql
OK (0 msec)

drop public database link psql;
create public database link psql connect to "gilho" identified by "pwd" using 'psql';
select * from "a"@psql;
no rows selected



에러 조치
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC]Could not connect to the server;
Connection refused [40.0.0.28:5432] {08001,NativeErr = 101}
=> telnet x.x.x.x 5432 확인

ORA-02063: preceding line from PSQL
=> HS_FDS_SHAREABLE_NAME =/usr/lib64/libodbc.so [ 파일이 정확한지 확인 ]
   ls -al /usr/lib64/libodbc.so
   lrwxrwxrwx. 1 root root 16 Dec 10 21:58 /usr/lib64/libodbc.so -> libodbc.so.2.0.0

ORA-01017: invalid username/password; logon denied
[unixODBC]FATAL:  password authentication failed for user "GILHO"
=> gilho를 대문자 인식 "gilho"로 변경

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ERROR:  relation "A" does not exist at character 48;
=> a를 대문자 A로 인식 "a"로 변경
3.239.112.140


  LIST

제목 작성자 작성일 조회
Admin  odbc_fdw test    최길호 2021/11/22 138
Admin  ERROR: cross-database references are not implemented / postgres_fdw / psql dblink    최길호 2021/11/15 185
Admin  CREATE EXTENSION postgres_fdw; [ source install ]    최길호 2021/11/15 292
Admin  psql [ 8.4.20 , 8.4.22 ] 여러 Version 운영, upgrade 8.4.20 to 8.4.22 [ 뒤 Version 다르면 그냥 시작 ]    최길호 2021/11/12 116
Admin  source make install [ 8 ~ 14 ]    최길호 2021/11/11 171
Admin  psql odbc make install    최길호 2021/11/09 134
Admin  oracle to psql dblink    최길호 2021/11/09 155
Admin  /usr/bin/initdb -D /var/lib/pgsql/test [ Instance 추가 ]    최길호 2021/11/05 106
Troubleshoot  connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?    최길호 2021/11/04 168
Troubleshoot  ERROR: current transaction is aborted, commands ignored until end of transaction block; [ postgresql-odbc upgrade ]    최길호 2021/11/04 183
Troubleshoot  pg_ctl start $PGDATA/pg_log/ 한글/영어 메세지 [ $PGDATA/postgresql.conf lc_messages ]    최길호 2021/10/29 205
Admin  pg_dumpall pg_upgrade    최길호 2021/10/21 149
Admin  통계정보    최길호 2021/10/21 124
Admin  pg_dump / pg_restore[ psql (8.4.20) to psql (14.0) ]    최길호 2021/10/20 170
Admin  PostgreSQL 14 / pgadmin Install [ lc_collate 'ko_KR.UTF-8' order by 정상 ]    최길호 2021/10/17 235
Admin  psql dblink to oracle [ odbc test ] 한글    최길호 2021/04/19 2498
Admin  postgres , enterprisedb(EDB) / [ ERROR: type "number" does not exist ]    최길호 2020/09/01 272
Admin  install postgresql on docker centos:7    최길호 2020/08/31 381
Troubleshoot  psql: server closed the connection unexpectedly [ vi $PGDATA/postgresql.conf / listen_addresses='*' ]    최길호 2020/08/31 412

    목록보기   다음페이지 1 [2][3][4][5][6]
       

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
11/23
[ORACLE]
acfsutil size: ACFS-03006: sma....
by 최길호
11/07
[운영체제]
Job for sshd.service failed be....
by 최길호
11/04
[ORACLE]
jsp error 위치 확인 [ debug ].
by 최길호