Sean kim의 생활 이야기

설치전 확인 및 고지 사항

• 오라클 클라이언트 버전 확인 후 설치 필요(필수)

• oracle fdw github에서 README 확인 필요(오라클 client 호환 버전 관련)

  https://github.com/laurenz/oracle_fdw

• fdw나 dblink는 온라인 업무에는 권장하지 않음

• 보편적인 체크 사항들 목록

오라클 클라이언트 다운로드가 필요(zip 파일) - 오라클 계정 필요
오라클 서버와 버전이 맞는 버전을 사용하는 것이 좋음(이슈 관련)
32비트 오라클 서버와 64비트 PostgreSQL 서버는 동작 불가.(동일한 bit의 프로그램 필요)
#SELECT * FROM v$version; 로 oracle 버전 정보 확인

----------Oracle client version 11.2 or better is required.---------- oracle_fdw 2.3.0버전 기준
그 외 호환 버전 정보는 Matrix in support document 207303.1 문서 참조
호환 Matrix에서는 최신 버전이 아닌 oracle 버전의 호환성에 대해 확인해주지 않는다는 점은 감안하여야 한다.
oracle_fdw 2.2.0 버전 기준 oracle client 10.2버전은 지원하지 않는다.

• 오라클 부분 확인 내용

--SID값으로 Service_name값 확인하기, 확인한 Service_name값으로 foreign server 설정값에 넣어주면 된다.
select value from v$parameter where name='<sid값>';
-------------------------
ORCLCDB.localdomain

• PostgreSQL 서버에서 필요한 파일 download 후 extension 설치하기

# Oracle Client Download
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
 instantclient-basic-linux.x64-11.2.0.4.0.zip
 instantclient-sdk-linux.x64-11.2.0.4.0.zip
 instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

# oracle fdw download
https://github.com/laurenz/oracle_fdw
 oracle_fdw-ORACLE_FDW_2_2_0.zip

#unzip all zip files
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
unzip oracle_fdw-ORACLE_FDW_2_2_0.zip

#환경변수 추가 
 #LD_LIBRARY_PATH 변수 끝에 :<오라클 클라이언트 압축 해제 경로> 추가
예) export LD_LIBRARY_PATH=$PGLIB:/home/seandb/instantclient_11_2
 #새로 추가 필요
export ORACLE_HOME=/home/seandb/instantclient_10_2
export TNS_ADMIN=/home/seandb/instantclient_10_2

#환경변수 적용 확인
source ~/.bashrc ~/.bash_profile
cd $ORACLE_HOME;pwd;cd -
cd $TNS_ADMIN;pwd;cd -
echo $LD_LIBRARY_PATH

## 라이브러리 파일 존재 여부 확인
ls -al $ORACLE_HOME/libclntsh*

# 오라클 클라이언트 디렉토리에 있는 so 파일 관련 링크 설정.
cd $ORACLE_HOME
ln -s libclntsh.so.11.1 libclntsh.so

#oracle pwd 빌드 및 설치
cd oracle_fdw-master
make&&make install

#PostgreSQL 재기동
pg_ctl stop
pg_ctl start

#oracle_fdw 익스텐션 설치
-- drop EXTENSION oracle_fdw;
CREATE EXTENSION oracle_fdw;

#외부 테이블 목록 조회
select * from pg_foreign_table;

• 서버 연결 및 확인 방법

--drop server oradb cascade;
--service name으로 외부(foreign) server 등록하기
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.56.222:1521/ORCLCDB.localdomain');

--하단의 명령어는 tnsnames.ora를 생성해준 뒤에 그 정의된 이름을 사용하는 방법이다.
vi tnsnames.ora
TESTDB =
        ( DESCRIPTION =
                ( ADDRESS_LIST =
                        ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192.168.56.222 ) ( PORT = 1521 ) )
                )
                ( CONNECT_DATA =
                        ( SID = ORCLCDB )
                )
        )
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'TESTDB');

--혹은 하단과 같이 옵션을 전부 명시해주어도 동작한다
CREATE SERVER pdspawn FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=myhost.com)(Port=1521))(CONNECT_DATA=(SID=FOREIGN_DATABASE)))');

-- 별도 유저 생성 및 권한 부여가 필요한 경우 값들 변경하여 사용
--CREATE USER oracon PASSWORD 'oracon' LOGIN ;
--GRANT usage ON FOREIGN SERVER oradb TO oracon ;
--GRANT usage ON SCHEMA public TO oracon ;

CREATE USER MAPPING FOR experdba SERVER oradb OPTIONS (user 'oracle', password 'orapass');
-- oracle_fdw 익스텐션에서 제공하는 함수로 설정이 정상적으로 되었는지 확인 가능하다.
select oracle_diag('oradb');

--drop foreign table fr_persons;
CREATE FOREIGN TABLE fr_persons(
  PERSON_ID NUMERIC NOT NULL
, FIRST_NAME VARCHAR(50)
, LAST_NAME VARCHAR(50)
) SERVER oradb OPTIONS (SCHEMA 'ORACLE', TABLE 'PERSONS');

select * from fr_persons;

• oracle_fdw 관련 함수 목록

oracle_close_connections();
oracle_diag();
oracle_execute();
oracle_fdw_handler();
oracle_fdw_validator();

• 관련 오브젝트 목록 조회

seandb=> select * from pg_foreign_data_wrapper ;
  oid  |  fdwname   | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
-------+------------+----------+------------+--------------+--------+------------
 16495 | dblink_fdw |    16386 |          0 |        16494 |        |
 16601 | oracle_fdw |       10 |      16596 |        16597 |        |
(2 rows)

seandb=> select * from pg_foreign_server
seandb-> ;
  oid  | srvname | srvowner | srvfdw | srvtype | srvversion |                 srvacl
            |                srvoptions
-------+---------+----------+--------+---------+------------+-----------------------------
------------+------------------------------------------
 16602 | oradb   |       10 |  16601 |         |            | {seandba=U/seandba,oracon=
U/seandba} | {dbserver=//192.168.56.222:1521/orclcdb}
(1 row)

seandb=> select * from pg_foreign_table ;
 ftrelid | ftserver |            ftoptions
---------+----------+----------------------------------
   16604 |    16602 | {schema=SYSTEM,table=TEST_TABLE}
   16607 |    16602 | {schema=ORACLE,table=PERSONS}
(2 rows)

• 외부 테이블 생성 후 테이블에 대한 정보 조회방법

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

 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into
| is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------
+----------+---------------
 seandb       | seandba     | fr_persons | FOREIGN    |                              |                      |                           |                          |                        | YES
| NO       |
(1 row)

reference list:

https://github.com/laurenz/oracle_fdw/wiki

https://www.postgresql.org/docs/12/sql-createserver.html

Docker 환경에서 Oracle 사용하기

• Migrator나 DBLINK 등의 연결방식의 환경 테스트가 필요한 경우에 유용할듯

• Migration Test 진행할 때에 개인적으로 사용 가능할듯.

• oracle docker 이용을 위해서는 최소 30기가 정도의 디스크 공간이 필요하다.

docker image pull을 위한 작업들

1. Docker Hub에서 login 후 진행해야 한다
2. https://hub.docker.com/_/oracle-database-enterprise-edition (2020.03)
3. 접속하여 Proceed to checkout 버튼 클릭 후 정보 입력
4. 사용하고자 하는 Docker 환경에 터미널 연결
5. docker login 명령어로 docker hub 아이디/암호 입력
6. docker pull store/oracle/database-enterprise:12.2.0.1 명령으로 image 다운로드

Oracle Docker Container 실행

1. oracle.env 파일 생성(유저,암호 등 설정파일)

    TZ=Asia/Seoul
    ORACLE_SID=ORCL
    ORACLE_PDB=MYORCL
    ORACLE_PWD=oracle
    ORACLE_CHARACTERSET=UTF8

2. env 파일 load 하는 방식으로 컨테이너 생성.

   docker run -d --name sean_ora12c --env-file ./oracle.env -p 1521:1521 -it store/oracle/database-enterprise:12.2.0.1 
   ## volume 사용시
   ##docker run -d --name sean_ora12c --env-file ./oracle.env -p 1521:1521 -it -name sean_ora12c -v /root/:/ORCL store/oracle/database-enterprise:12.1.0.1
   ## slim 버전
   ##docker run -d --env-file ./oracle.env -p 1521:1521 -it -name sean_ora12c -v /root/:/ORCL store/oracle/database-enterprise:12.2.0.1-slim
   
   ## 컨테이너 내려갔을때 올리기
   docker start sean_ora12c
   # 별도 작업 필요없음

3. docker logs -f sean_ora12c 명령어로 진행상황 확인

    The command completed successfully
   
    DONE!
    Remove password info
    Docker DB configuration is complete !
    configDB.sh is done at 297 sec
   
    Done ! The database is ready for use .

User 생성 및 권한 설정

• 도커 환경에서 exec로 컨테이너 접근

$ docker exec -it sean_ora12c bash -c "source /home/oracle/.bashrc; sqlplus sys/Oradoc_db1@ORCLCDB as sysdba"

• 유저 생성 및 암호 설정, 권한 설정

alter session set "_ORACLE_SCRIPT"=true;

## 유저 oracle, 암호 orapass
create user oracle identified by orapass;

grant connect, resource, dba to oracle;

• sysdba로 접근 후 DB, SID 확인

docker exec -it sean_ora12c bash -c "source /home/oracle/.bashrc; sqlplus sys/Oradoc_db1@ORCLCDB as sysdba"

SELECT NAME, DB_UNIQUE_NAME FROM v$database;
SELECT instance FROM v$thread;

DBeaver Oracle 접속

1. 필요한 드라이버 다운로드 후 DBeaver에 위치 등록.
   • https://mvnrepository.com/artifact/com.oracle.jdbc/ojdbc8/12.2.0.1
   • 등록(Add File)

2. 접속 정보 입력 후 접속 테스트(SID = ORCLCDB, 아이디, 암호는 위에서 생성한 대로)

https://hub.docker.com/u/dosroad/content/sub-79223df5-6a1f-4c40-a3b2-96d36ff84172

http://1004lucifer.blogspot.com/2019/11/docker-oracle-12c-oracle.html

oracle xe 18c 설치

https://emflant.tistory.com/237