주식회사 누리아이티

정보자산의 보안강화를 위한 2차인증 보안SW 및 지문인식 OTP/출입/보안카드 전문기업

▶ BaroSolution/가이드

Oracle External Procedure 사용법(C모듈)

누리아이티 2017. 12. 21. 09:01

1. External Procedure ?

 

복잡한 수식계산을 Oracle에서 제공하는 기능으로만 충분하지 않을 경우가 있는데, 이럴 경우 C JAVA 같은 언어로 복잡한 기능을 작성한 후 Oracle 에서는 파라미터를 넘겨서 해당 결과를 받으면 수행속도의 개선을 가져올 수 있는데, 간단히 말하자면 C언어나 VB, JAVA 등의 언어를 사용하여 SQL에서 구현하기 어렵거나 복잡한 것을 구현한 뒤 SQL에서 호출해서 사용하는 것을 말한다.

 

2. 장점 및 단점

 

External Procedure을 사용하여 얻는 장점으로는 Java C의 재 활용성이 우수하다. 반면 External Procedure을 사용으로 발생하는 단점은 Session이 종료되지 않으면 extProc Oracle에서 메모리를 관리하는 영역이 아니라 O/S영역이기 때문에 한번 호출 될 때마다 해당 Session이 종료되지 않으면 끝까지 살아남게 되어 지속적인 메모리에 남아 있게 되어 O/S의 메모리 부하가 생길 수 밖에 없다.

 

그래서, O/S의 메모리를 최대한 줄일 수 있는 방법은 다음과 같다.

 

Session의 수를 제한하여 O/S의 메모리 한계치를 벗어나지 않도록 조정한다.

O/S에서 extProc로 생성된 것 중 오래된 Process Kill한다. Process Kill하더라도 없으면 재생성 되므로 큰 문제는 발생되지 않는다.

③ 어플리케이션에서 불필요하게 External Procedure를 호출하는 Function의 사용을 제거하고, 사용 완료 후 Session를 종료하여 메모리의 부하를 최소화 하게 한다.

 

3. C 모듈(libbarocrypt.so)

 

C 모듈은 Java 모듈과 달리 External Procedure를 사용하는 것이 번거롭지만 External Procedure의 동작 순서는 다음과 같다.

 

① 사용자가 SQL에서 External Procedure에서 작성한 Function DB에 요청을 한다.

 

Shared SQL Area에서 해당 문장을 Parsing하면서 External Procedure을 알고 NET8 Listener에서 사용자 SQL External Procedure를 호출 했으니 해석해 달라고 요청한다.

 

Listener는 다시 extProc 프로세스를 생성하면서 O/S에 있는 External Procedure가 있는 DLL, Procedure Name, Parameter를 넘겨준다.

 

extProc O/S에 있는 DLL 파일을 찾아서 O/S의 메모리에 Load하여 요청 받은 Function의 결과를 처리한다.

 

⑤ 처리된 결과를 SQL return해 준다.

 

Session이 종료되면 extproc도 자동으로 종료된다.

 

3.1 C 모듈 library 생성

 

C 모듈을 Oracle 내에 암복호화 Library(libcrypt_encrypts-암호화 Library, libcrypt_decrypts-복호화 Library)를 다음과 같이 각각 생성한다. (Oracle 사용자가 Library를 생성하는데 필요한 권한 필요)

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom
 
SQL*Plus: Release 11.2.0.1.0 Production on 9 12 14:13:40 2017
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> CREATE OR REPLACE LIBRARY libcrypt_encrypts AS '/data/icom/nurit/crypt/libusercrypt.so' ;
  2  /
 
Library created.
 
SQL> CREATE OR REPLACE LIBRARY libcrypt_decrypts AS '/data/icom/nurit/crypt/libusercrypt.so' ;
  2  /
 
Library created.
 
SQL> commit;
 
Commit complete.

 

3.2 암복화 Stored Function 생성 및 확인

 

Oracle sqlplus로 접속한 뒤 TO_ENCRYPT(암호화 함수), TO_DECRYPT(복호화 함수)를 다음과 같이 생성 및 확인한다.

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom
 
SQL*Plus: Release 11.2.0.1.0 Production on 9 12 14:16:06 2017
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> CREATE OR REPLACE FUNCTION TO_ENCRYPTS (input VARCHAR2) return VARCHAR2
      as external
      language C
      library libcrypt_encrypts
      name "baro_encrypts"
      parameters  (input  STRING);
  2    3    4    5    6    7  /
 
Function created.
 
SQL> CREATE OR REPLACE FUNCTION TO_DECRYPTS (input VARCHAR2) return VARCHAR2
      as external
      language C
      library libcrypt_decrypts
      name "baro_decrypts"
      parameters  (input  STRING);
  2    3    4    5    6    7  /
 
Function created.
 
SQL> commit;


 
Commit complete.

 

3.3 listener 구성

 

C 모듈을 사용하기 전에 원하는 Shared Library에 대한 경로를 포함하도록 listener를 구성해야 한다. EXTPROC_DLLS 환경 변수를 설정하여 수행하는데, Oracle 계정에 로그인 후 다음과 같이 listener.ora 파일에 설정한다.

 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
      (PROGRAM = extproc)
      (ENVS="EXTPROC_DLLS=ANY")
    )
    (SID_DESC =
      (SID_NAME = ODB)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = project)(PORT = 1521))
    )
  )
 
ADR_BASE_LISTENER = /oracle/app/oracle

 

3.4 tnsnames.ora 구성

 

C 모듈을 사용하기 전에 listener.ors 파일의 설정한 Key, SID와 동일한 값으로 Oracle 계정에 로그인 후 EXTPROC_CONNECTION_DATA을 다음과 같이 tnsnames.ora 파일에 설정한다.

 

ODB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = project)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ODB)
    )
  )
 
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(HOST = project)(KEY = EXTPROC1521))
        (CONNECT_DATA = (SID = PLSExtProc))
)

 

tnsnames.ora 파일을 설정한 후 반드시 Oracle 계정에 로그인 후 listener를 재기동 해야 한다.

 

> lsnrctl start | stop | status

 

3.5 암복화 함수(TO_ENCRYPTS, TO_DECRYPTS) 테스트

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom
 
SQL*Plus: Release 11.2.0.1.0 Production on 9 12 14:16:06 2017
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> SELECT TO_ENCRYPTS('qwerqwerqwer이종일qwerqwer') FROM DUAL;
 
TO_ENCRYPTS('QWERQWERQWER이종일QWERQWER')
--------------------------------------------------------------------------------
LkrLD7uCXBnPZreic9NgsHgsDjWQG1QQL0w9UHndzy8=
 
SQL> SELECT TO_DECRYPTS('LkrLD7uCXBnPZreic9NgsHgsDjWQG1QQL0w9UHndzy8=') FROM DUAL;
 
TO_DECRYPTS('LKRLD7UCXBNPZREIC9NGSHGSDJWQG1QQL0W9UHNDZY8=')
--------------------------------------------------------------------------------
qwerqwerqwer이종일qwerqwer