모두의 지식 저장고

Oracle NLS_LANGUAGE 변경 Scripts

• 데이터 베이스 NLS_LANGUAGE 변경 방법에 대해 공유 합니다. 
• 독일어로 변경하는 방법에 대해 알아 보겠습니다. 

-- dba 접속
sqlplus sys/oracle as sysdba

-- 언어셋 변경 
update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';  
update sys.props$ set value$='AL16UTF16' where  name='NLS_NCHAR_CHARACTERSET';
update sys.props$ set value$='GERMAN_GERMANY.WE8ISO8859P1  ' where name='NLS_LANGUAGE';
commit;

- db 재기동
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;

SHUTDOWN IMMEDIATE;

STARTUP;

NLS_LANGUAGE 변경 진행 과정

변경된 NLS 정보 확인 방법

set linesize 400
set pagesize 100
col PARAMETER for a30
col VALUE for a100

-- 현재 nls 구성정보
SELECT * FROM V$NLS_PARAMETERS;
 
--설치시 nls 정보
SELECT * FROM NLS_DATABASE_PARAMETERS;
 
--인스턴스 nls 정보
SELECT * FROM NLS_INSTANCE_PARAMETERS;​

ODBC 란? 

ODBC 란 Open DataBase Connectivity  약어로 데이터 베이스에 연결하기 위한 공개형 응용 프로그램 인터페이스를 말합니다.  간단히 데이터베이스와 어플리케이션을 연결해주는 인터페이스라고 보면 되겠습니다.   

최초 마이크로 소프트에서 데이터베이스에 접근하기 위해 개발된 소프트웨어 규약으로서 DBMS 어플리케이션 인터 페이스(API) 이며 , Unix 계열 운영체제에서도 사용할수있도록 개발한 것이 unixODBC,iodbc 입니다. 

Tibero 와 ODBC 연동 방법에 대한 설명 자료 입니다. 

Tibero iodbc 연동 

1. iodbc 설치 바이너리 다운로드

idobc 홈페이지에서 사용할 버전을 확인후 드라이버를 다운로드를 받습니다.

https://github.com/openlink/iODBC/releases

2. Install

• 압축 해제  및 설치 진행

tar xvzf libiodbc-3.52.15.tar.gz

./configure --prefix=/usr/local --sysconfdir=/etc --disable-gui

make

make install

3.환경 파일 작성 

• odbc.ini
  • 접속할 DBMS DNS 정보를 설정하는 파일 입니다. 
• .odbc 파일 읽는 순서   : $HOME 아래에 .odbc.ini를 만들면 해당 경로를 먼저 읽음
• 순서 : $HOME/.odbc.ini -> /etc/odbc.ini

vi .odbc.ini 
[ODBC Data Sources]

Tibero7 = Tibero7 ODBC driver

[ODBC]

Trace = 1

TraceFile = /home/django/unixODBC/unixODBC-2.3.9/trace/odbc.trace

[Tibero7]

Driver = /home/django/tibero7/client/lib/libtbodbc.so

Description = Tibero ODBC driver for Tibero

server = 192.168.xxx.xxxx


#server는(ip,hostname 둘다가능)

port = 17000

#port 는 tibero port

database = tibero

#database 는  DB_NAME 입력 

User =tibero

Password = tmax

4.접속 테스트 

Tibero unixODBC 연동 

Centos7 기준으로 설명 합니다. 

1.unixODBC 다운로드 

아래 경로에서 다운로드 받습니다. 

http://www.unixodbc.org/download.html

2. Install

• 환경변수를 설정 합니다.

#ODBC 환경변수 설정 
export UNIXODBC_HOME=/home/django/unixODBC-2.3.9
export LD_LIBRARY_PATH=$UNIXODBC_HOME/lib:$LD_LIBRARY_PATH
export PATH=$UNIXODBC_HOME/bin:$PATH

• 설치를 진행 합니다.

./configure --prefix=$UNIXODBC_HOME --sysconfdir=$UNIXODBC_HOME/etc --with-iodbc-inidir=$UNIXODBC_HOME/etc --disable-gui
&& make
&& make install

3.환경 파일 작성

iodbc 설정 사항과 동일 합니다. 

4.테스트

오라클 샘플 스키마 생성 방법에 대해 알아보겠습니다. 

오라클 설치 시  오라클 스키마는 생성되지 않습니다. 

테스트하기 위한 Sample Schema 생성 방법에 대해 알아보겠습니다. 

Oracle Database Sample Schemas 다운로드

먼저 오라클에서 제공하는 샘플 스키마를 다운로드 받습니다. 

https://github.com/oracle/db-sample-schemas/tree/v19c

git clone https://github.com/oracle/db-sample-schemas.git

다운로드 파일 :db-sample-schemas

sample-schemas scripts 수행 설명 (mksample.sql)

sqlplus sys/oracle@orclcdb as sysdba

@/opt/oracle/oradata/db-sample-schemas/mksample.sql oracle oracle hr oe pm ix sh bi users temp /opt/oracle/oradata/db-sample-schemas/logs/ ora19c

mksample.sql 에 서 입력 받는 인자 값들입니다. 

입력 변수는 볼드체로 표기했습니다. 

mksample.sql 파일 수정 

스크립트를 수행하면 샘플 유저를 생성하는 과정에서 ORA-65096: invalid common user or role name 에러가 발생합니다 .

에러 발생을 막기 위해 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 적용하겠습니다. 

mksample.sql 스크립트중 CONNECT system/&&password_system@&&connect_string 구문 밑에 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 를 추가합니다. 

mksample.sql 실행 

정상적으로 수행이 완료되면 log 디렉토리에 수행 스크립트 별로 로그가 남게 되고 

마지막으로 검증 결과가 mkverify_v3.log 와 같은 형태로 남게 됩니다. 

The Customer Orders(CO) schema 스크립트 수행 

mksample 에서는 co 생성 스크립트가 포함되어 있지 않습니다. 

별도 스크립트로 수행해야 테스트 샘플 스키마가 생성 되게 됩니다. 

수행 파일은  db-sample-schemas/customer_orders/co_main.sql 입니다. 

Scott 계정 생성 

오라클 12c? 이후 부터는 scott 계정이 기본 적으로 생성되지 않습니다. 

livesql 사이트에서 테이블 정보와 데이터 확인후 샘플데이터를 생성하면 됩니다.

https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

Scott 계정 스크립트

create user scott identified by tiger;
grant dba to scott;

conn scott/tiger

DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
	
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
	
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

샘플 스키마 ERD Diagrams 

자세한 자료는 오라클 자료를 참고하시기 바랍니다. 

https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/schema-diagrams.html#GUID-D268A4DE-BA8D-428E-B47F-80519DC6EE6E

메일 전송을 위한 프로 시져 작성 

DROP DIRECTORY ATTACH_FILE;
CREATE DIRECTORY ATTACH_FILE AS '/data1/attach_dir';
GRANT READ ,WRITE ON DIRECTORY ATTACH_FILE TO TIBERO;

CREATE OR REPLACE PROCEDURE send_mail (p_to   IN VARCHAR2,
             p_from  IN VARCHAR2,
             p_subject  IN VARCHAR2,
             p_text_msg IN VARCHAR2 DEFAULT NULL,
             p_attach_dir IN VARCHAR2 DEFAULT NULL,
             p_attach_name IN VARCHAR2 DEFAULT NULL,
             p_attach_mime IN VARCHAR2 DEFAULT NULL,
             p_smtp_host IN VARCHAR2,
             p_smtp_port IN NUMBER DEFAULT 25)
AS
    l_mail_conn UTL_SMTP.connection;
    l_boundary VARCHAR2(50) := '----=*#tmaxSendmailBoundary#*=';
      

    vf_buffer RAW(32767);
    vf_raw    RAW(32767); --반환할 파일
    vf_type  UTL_FILE.FILE_TYPE;
      
BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
    UTL_SMTP.helo(l_mail_conn, p_smtp_host);
    UTL_SMTP.mail(l_mail_conn, p_from);
    UTL_SMTP.rcpt(l_mail_conn, p_to);

    UTL_SMTP.open_data(l_mail_conn);

    UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="euc-kr"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END IF;

    IF p_attach_name IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);


      -- 파일을 바이트모드로 읽는다.
      -- p_dir : 디렉토리명, p_file : 파일명, rb: 바이트모드로 읽기
      vf_type := UTL_FILE.FOPEN ( p_attach_dir, p_attach_name, 'rb');

      -- 파일이 오픈됐는지 IS_OPEN 함수를 이용해 확인.
      IF UTL_FILE.IS_OPEN ( vf_type ) THEN
         -- 루프를 돌며 파일을 읽는다.
         LOOP
         BEGIN
           -- GET_RAW 프로시저로 파일을 읽어 vf_buffer 변수에 담는다.
           UTL_FILE.GET_RAW(vf_type, vf_buffer, 32767);
           -- 반환할 RAW 타입 변수에 vf_buffer를 할당.
           vf_raw := vf_raw || vf_buffer;
            -- BASE64 인코딩을 한 후 파일내용 첨부
            UTL_SMTP.WRITE_RAW_DATA(l_mail_conn, UTL_ENCODE.BASE64_ENCODE ( vf_buffer));
            UTL_SMTP.WRITE_DATA(l_mail_conn,  UTL_TCP.CRLF );
         EXCEPTION
             -- 더 이상 가져올 데이터가 없으면 루프를 빠져나간다.
               WHEN NO_DATA_FOUND THEN
               EXIT;
         END;
         END LOOP;
      END IF;

      -- 파일을 닫는다.
      UTL_FILE.FCLOSE(vf_type);


    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END IF;

    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
    UTL_SMTP.close_data(l_mail_conn);

    UTL_SMTP.quit(l_mail_conn);
END;
/

첨부 테스트 파일 생성 

메일전송 테스트

메일 서버는 리눅스 sendmail 서버를 사용 하여 테스트 하였습니다. 

리눅스 Sendmail 설치는 https://growupcoding.tistory.com/47 글을 참고 하세요 

begin
tibero.send_mail( 'example@test.com'
,'example@test.com'
,'첨부파일 테스트'
,'테스트 메일입니다. '
,'ATTACH_FILE'
,'test.txt'
,'application/octet-stream'
,'centos7',25);
end;
/

Gmail 메일 확인

Oralce 환경설정 방법

앞서 docker-compose 를 이용한 Oracle 19c 설치 글에서는 단순히  설치 방법에 대해서 알아보았습니다. 

이번에는 Docker 설치후 테스트를 좀더 효율적으로 진행하기 위한 방법에 대해 소개하겠습니다. 

_ORACLE_SCRIPT 파라미터 적용 

19c 에서는 테이블이나 유저를 생성하게 되면 ORA-65096 에러가 발생 합니다. 

_ORACLE_SCRIPT  파라미터를 변경함으로서 에러발생을 해결 할 수 있습니다. 

외부 장비 접속 설정 (네트워크 설정)

tnsnames.ora 설정

ORCLCDB=localhost:1521/ORCLCDB

ora19c= 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLCDB)
  )
)
ORCLPDB1= 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

listener.ora 설정

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = ORCLCDB)
        (SID_NAME = ORCLCDB)
    )     
    (SID_DESC =
        (GLOBAL_DBNAME = ORCLPDB1)
        (SID_NAME =ORCLPDB1)                
    )
)

리스너 재시작 

리스너 상태 확인 

sqlplus 접속 확인

MyOra  툴은 무료로 오라클 sql 작업이나 DB 자원 모니터링을 할수 있는 툴입니다. 

해당 툴을 다운로드 해서 사용하는 방법에 대해 소개하겠습니다. 

MyOra 다운로드

아래 경로에서 해당 툴 다운로드 받습니다. 

https://www.myorasql.com/download.html

현재는 9.4 버전 까지 Release 되어있습니다. (2022.03.21) 

MyOra 실행

다운로드 받은 앞출 파일을 해제 후 프로그램을 실행합니다. 

설치된 java 버전에 따라 실행 파일이 다릅니다. 

제환경은 1.8 jdk 가 설치 되어 있어 MyOra-9.4\Java1_8andBelow 디렉토리에있는 MyOra 파일을 실행 하겠습니다. 

* 주의 : 접속하로자 하는 오라클 jdbc 라이브러리가 실행 파일 경로에 위치해야 합니다. 

https://growupcoding.tistory.com/27 글 에서 설치한 오라클 jdbc 파일을 MyOra-9.4\Java1_8andBelow 로 복사하겠습니다. 

오라클 자원 사용률 모니터링 

Top Query 조회 

Instance 정보

SQL Editor

기본 SQL Editor 도 지원 합니다. 쿼리 실행 단축키는 F9 번입니다. 

1.Docker Image 다운로드 

우선 Docker 환경에서 오라클을 설치 하려면 오라클 이미지가 준비 되어있어야 겠죠? 

아래 GitHub 에서 Oracle19c 이미지를 다운로드 받습니다. 

https://github.com/oracle/

git clone https://github.com/oracle/docker-images.git

2.Oracle19c 바이너리 다운로드 

Docker 이미지를 다운로드를 받았다면 이제 실제로 설치할 오라클 바이너리를 준비해야 겠습니다. 

아래 경로에서 오라클 19c 바이너리(LINUX.ZSERIES64_193000_db_home.zip)를 다운로드 받습니다. 

https://www.oracle.com/kr/database/technologies/oracle19c-linux-downloads.html

3. 설치 

설치를 위한 바이너리가 모두 준비되었습니다. 

-rw-r--r--  1 oracle-docker oracle-docker 3059705302  2월 22 19:36 LINUX.X64_193000_db_home.zip
drwxr-xr-x 32 oracle-docker oracle-docker       4096  2월 22 15:40 docker-images

이제 본격적인 설치 작업을 진행하도록 하겠습니다. 

Oracle 19c 바이너리 Docker 디렉토리 복사 

 cp LINUX.X64_193000_db_home.zip docker-images/OracleDatabase/SingleInstance/dockerfiles/19.3.0

Docker Image build

cd docker-images/OracleDatabase/SingleInstance/dockerfiles
./buildContainerImage.sh -e -v 19.3.0

Docker 실행 

$ docker run --name oracle -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=oracle -v oracle19c:/opt/oracle/
oradata oracle/database:19.3.0-ee

Oracle 접속 및 테스트 

$ docker exec -it oracle sqlplus sys/oracle@//localhost:1521/ORCLCDB as sysdba