[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / 幼い女性を性の奴隷にした帝國主義日本軍の?制性を公式に認め、謝罪しなければならない. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [71]
02.File Book
File 자료실 [96]
Site Link [14]
개인폴더 [201]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [43]
DB2 [79]
ORACLE [3054]
PSQL [135]
CUBRID [5]
기타정보 [158]
운영체제 [607]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

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

 회원가입

psql 10.7 oracle dblink [ oracle_fdw db link ]
최길호 [LIST]   2023-07-25 17:20:41, 조회 : 28


Oracle DB 10.1 === Client 12.2 안됨. ORA-03134
Oracle DB 10.1 === Client 11.2 가능

postgresql Install
===========================
https://www.postgresql.org/ftp/source/v10.7/
./configure --prefix=/home/postgres/10.7
make
more config.log
make install


export PATH=/home/postgres/10.7/bin:$PATH
export PGDATA=/home/postgres/10.7/data
export ORACLE_HOME=/oracle/product/12.2
export LANG=ko_KR.eucKR
export NLS_LANG=.KO16MSWIN949
export LD_LIBRARY_PATH=/home/postgres/10.7/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

initdb -D /home/postgres/10.7/data
pg_ctl -D /home/postgres/10.7/data start
psql


oracle_fdw Install
===========================
https://github.com/laurenz/oracle_fdw/releases
README : https://github.com/laurenz/oracle_fdw
psql : 10.4(포함) 이상
oracle : 11.2(포함) 이상

ldd $ORACLE_HOME/lib/libclntsh.so
        linux-vdso.so.1 =>  (0x00007fff4b3da000)
        libmql1.so => /oracle/product/12.2/lib/libmql1.so (0x00007f39b3751000)
        libipc1.so => /oracle/product/12.2/lib/libipc1.so (0x00007f39b331e000)
        libnnz12.so => /oracle/product/12.2/lib/libnnz12.so (0x00007f39b2bd5000)
        libons.so => /oracle/product/12.2/lib/libons.so (0x00007f39b2987000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f39b2783000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f39b2481000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f39b2265000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f39b204b000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f39b1e43000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f39b1c41000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f39b1a27000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f39b1659000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f39b746f000)
        libclntshcore.so.12.1 => /oracle/product/12.2/lib/libclntshcore.so.12.1 (0x00007f39b108b000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f39b0e75000)

cd /home/postgres/10.7/oracle_fdw-ORACLE_FDW_2_5_0
make
make install

pg_ctl stop
pg_ctl start

ldd lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffce11b9000)
        libclntsh.so.12.1 => /oracle/product/12.2/lib/libclntsh.so.12.1 (0x00007f7ec5336000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7ec4f68000)
        libmql1.so => /oracle/product/12.2/lib/libmql1.so (0x00007f7ec4cf1000)
        libipc1.so => /oracle/product/12.2/lib/libipc1.so (0x00007f7ec48be000)
        libnnz12.so => /oracle/product/12.2/lib/libnnz12.so (0x00007f7ec4175000)
        libons.so => /oracle/product/12.2/lib/libons.so (0x00007f7ec3f27000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7ec3d23000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f7ec3a21000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7ec3805000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f7ec35eb000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f7ec33e3000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f7ec31e1000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7ec2fc7000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f7ec9001000)
        libclntshcore.so.12.1 => /oracle/product/12.2/lib/libclntshcore.so.12.1 (0x00007f7ec29f9000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7ec27e3000)

-- drop
drop server oradb cascade;
revoke USAGE ON FOREIGN DATA WRAPPER oracle_fdw from orauser;
drop user orauser;

-- create
DROP EXTENSION oracle_fdw;
CREATE EXTENSION oracle_fdw;
CREATE USER orauser PASSWORD 'orauserpwd' LOGIN ;
GRANT usage ON SCHEMA public TO orauser ;
GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw to orauser;

-- 필요시 다른계정에게 사용 가능하게 권한 부여도 가능
GRANT usage ON FOREIGN SERVER oradb TO orauser ;


\c postgres orauser
DROP SERVER oradb;
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.0.1:1521/orcl');
ERROR:  permission denied for foreign-data wrapper oracle_fdw
-- GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw to orauser;
select * from pg_foreign_server;
DROP USER MAPPING FOR orauser SERVER oradb;
CREATE USER MAPPING FOR orauser SERVER oradb OPTIONS (user 'gilho', password 'pwd');
select oracle_diag('oradb');
select oracle_diag();
---------------------------------------------------------------------------------------
oracle_fdw 2.5.0, PostgreSQL 10.7, Oracle client 12.2.0.1.0, Oracle server 10.2.0.5.0

drop FOREIGN TABLE tab ;
CREATE FOREIGN TABLE tab
( TNAME     VARCHAR(30),
  TABTYPE   VARCHAR(7),
  CLUSTERID numeric )
SERVER oradb OPTIONS (SCHEMA 'SCOTT', TABLE 'TAB2');

select * from pg_foreign_table ;
select * from information_schema.tables where lower(table_type) like 'foreign%';


Error
===========================
postgres=# create extension oracle_fdw;
ERROR:  could not load library "/home/postgres/10.7/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory
조치중 하나
ln -s /oracle/product/12.2/lib/libclntsh.so.12.1 /lib64/libclntsh.so.12.1
또는
https://github.com/laurenz/oracle_fdw/issues/440
root#
cat >> /etc/ld.so.conf
include /oracle/product/12.2/lib/
root# ldconfig
pg_ctl stop
pg_ctl start
postgres=# CREATE EXTENSION oracle_fdw;

ERROR:  permission denied for foreign server oradb
\c postgres postgres
GRANT usage ON FOREIGN SERVER oradb TO orauser ;
GRANT usage ON SCHEMA public TO orauser ;
3.239.9.151


  LIST

제목 작성자 작성일 조회
Admin  pg_backup_start / pg_backup_stop / psql 세션 유지 / WARNING: aborting backup due to backend exiting before pg_backup_stop was called    최길호 2023/12/21 25
Admin  psql 10.7 oracle dblink [ oracle_fdw db link ]    최길호 2023/07/25 28
Admin  HA 복구 test    최길호 2023/03/03 45
Admin  log: COPY public.xxxx (xxx, xxx, xxx) TO stdout; / pg_dump 백업시 발생    최길호 2023/03/03 23
Admin  requested timeline x is not a child of this server's history    최길호 2023/03/02 21
Admin  recovery_target_action / archive log 받아서 계속 복구    최길호 2023/02/28 34
Admin  recovery_target_action = 'pause' / select pg_wal_replay_pause(); / select pg_get_wal_replay_pause_state();    최길호 2023/02/27 13
Admin  memory buffer cache    최길호 2023/02/06 19
Admin  active session, pid, lock sql , kill pid / pg_terminate_backend    최길호 2023/02/06 77
Admin  psql HA    최길호 2023/02/05 62
Admin  pg_ctl start -D . | postmaster.pid | /tmp/.s.PGSQL.5432 |    최길호 2023/02/03 39
Admin  pg_waldump    최길호 2023/02/03 18
Troubleshoot  $PGDATA/pg_wal/00000002.history 없는경우 [ ERROR: could not open file "pg_wal/00000002.history": ENOENT ]    최길호 2023/02/02 33
Admin  shmem_exit(0): 0 before_shmem_exit callbacks to make [ 로그 기록용 ]    최길호 2023/02/01 58
Troubleshoot  LOG: invalid resource manager ID in primary checkpoint record    최길호 2023/02/01 33
Admin  pg_restore [ 다른 계정, 다른 DB, sql 파일로 저장 ]    최길호 2023/01/30 26
Admin  pg_basebackup pg_dump pg_dumpall backup script    최길호 2023/01/30 122
Admin  session 정보    최길호 2023/01/27 30

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

Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
03/04
[기타정보]
아두이노 wifi web test.
by 최길호
03/01
[기타정보]
아두이노 스마트하우스 V2.
by 최길호
02/28
[ORACLE]
plsql run sql from column / 테....
by 최길호