1월 3~4주차. Oracle Database

3주차. 윈도우에서 Oracle DB 사용하기

중소기업이나 스타트업 회사에서는 무료로 제공되는 MySQL을 선호하지만, 규모가 어느 정도 있는 회사에서는 안정성이 높은 Oracle DB를 선호한다. 대부분의 회사에서 사용되는 데이터베이스인만큼 DB 연동 모듈을 개발하기에 앞서 Oracle DB를 구성하고, 접근하는 방법에 대해 먼저 실습해보았다.

1. Oracle DB 구성

① VM(vmware사의 vSphere 이용)에 Oracle DB를 구성할 것이므로, VM에 윈도우를 설치한다.

② 아래의 링크에 접속하여 Download Oracle Database XE for Windows (ZIP) 버튼을 클릭한다.
>> Oracle Free 버전 다운로드

③ ZIP 파일을 해제한 후 Setup.exe 파일을 실행하여 오라클을 설치한다.

④ Database에 사용할 비밀번호를 입력한다. 이 비밀번호는 DB에 접속할 때 필요한 정보이므로, 반드시 기억해두어야 한다.

⑤ SQL Plus 앱을 실행한다.

⑥ 사용자명에는 sys/as sysdba를 입력하고 비밀번호에는 본인이 오라클을 설치할 때 설정한 비밀번호를 입력한다.

⑦ Oracle DB에 접속할 때 사용할 계정이 필요하므로, 아래의 명령을 입력해 계정을 생성해주자.

• 비밀번호에 특수문자는 사용할 수 없다.

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE; // 유저의 이름을 자유롭게 설정하기 위함
create user {id} identified by {pw}; // 유저 생성
grant connect, resource, dba to {id}; // 권한 부여
commit; // 변경사항 적용

2. 로컬 Oracle DB 접속

Oracle 사의 SQL Developer와 JETBRAINS 사의 Datagrip을 사용해 Oracle DB에 접속하는 방법에 대해 알아보자. 참고로 여기서는 localhost의 IP 주소로 회사에서 할당해준 정적 IP를 사용할 것이다.

1) 윈도우에서 고정 IP 사용하기

먼저는 회사에서 할당해준 고정 IP를 localhost에 적용해보자. 이 과정은 DB 접속과 무관하므로, 정적 IP를 할당하지 않을 것이라면 건너뛰어도 된다.

① 제어판 > 네트워크 및 공유 센터를 클릭한다.

• 보기 기준 > 작은 아이콘을 선택해야 해당 메뉴가 나타난다.
  

② 이더넷 3 버튼을 클릭한다.

③ 속성 버튼을 클릭한다.

④ 인터넷 프로토콜 버전 4(TCP/IPv4) 선택 후 속성 버튼을 클릭한다.

⑤ 변경하고자 하는 IP 주소, 서브넷 마스크, 기본 게이트웨이 주소, 기본 DNS, 보조 DNS 설정 후 확인 버튼을 클릭한다.

⑥ 할당이 잘 되었는지 확인하고 싶다면, cmd 창에 ipconfig 또는 ipconfig/all 명령을 입력하면 된다.

2) SQL Developer를 이용한 DB 접속

① 아래의 링크에 접속한 후 JDK가 이미 있는 경우에는 위의 것을, JDK가 없는 경우에는 아래의 것을 다운로드한다.
>> SQL Developer 다운로드

• 여기서는 VM에 설치할 것이므로, JDK를 포함하여 설치를 진행하였다.
• 다운로드에는 Oracle 계정이 필요하므로, 계정이 없다면 계정을 먼저 생성해주어야 한다.
  

② 압축을 해제한 후 sqldeveloper.exe 파일을 실행한다.

③ 좌상단에 있는 + 버튼을 누른 후, 새 데이터베이스 접속 버튼을 클릭한다.

④ 아래와 같이 입력한다.

• Name에는 원하는 값을 입력하면 된다.
• 사용자 이름에 SQL Plus에서 생성한 유저의 이름을 넣고, 비밀번호에 지정한 비밀번호를 입력한다.
• 호스트 이름에는 localhost 또는 cmd에 ipconfig 명령을 입력했을 때 나오는 IPv4 주소를 입력해야 한다. (정확히 말하면, listener.ora 파일과 tnsnames.ora 파일의 Host에 할당되어 있는 값을 넣어야 한다. 기본 값은 localhost이다.)
• 포트와 SID는 그대로 유지한 채로 테스트 버튼을 클릭하여, 테스트가 성공하는지 확인한다.

⑤ 테스트에 성공했다면, 접속 버튼을 클릭해 Oracle DB에 접속할 수 있다.

• 원하는 SQL문을 입력하여 테이블을 생성하거나, 쿼리를 수행할 수 있다.
  

3) Datagrip을 이용한 DB 접속

이미 SQL Developer를 이용한 데이터베이스 접속에 성공했다면, Datagrip을 이용한 방식은 생략해도 된다. 다만, Datagrip에 더 익숙한 사용자들을 위해 Datagrip을 사용해 접속하는 방법에 대해서도 소개해보기로 한다.

① Datagrip의 상단 중앙에 위치한 데이터베이스 아이콘을 클릭한 후, + 버튼 > Data Source > Oracle을 클릭한다.

② SQL Developer에서와 동일하게 입력하면 된다.

• User에 SQL Plus에서 생성한 유저의 이름을 넣고, Password에 지정한 비밀번호를 입력한다.
• 여기서도 호스트 이름에는 localhost가 아닌, cmd에 ipconfig 명령을 입력했을 때 나오는 IPv4 주소를 입력한다.
• Test Connection 버튼을 클릭하여 DB에 접속이 잘되는지 확인한다.
  

③ 테스트에 성공했다면, OK 버튼을 눌러 데이터베이스에 접속한다.

4) IP 주소 변경하기

① 정적 IP 주소를 사용해야 하는 경우, 먼저는 1)번 절차에 따라 IP 주소를 변경한다.

② listener.ora 파일과 tnsnames.ora 파일의 위치를 찾는다.

• 기본적으로는 app > {사용자 이름} > product > 21c > homes > OraDB21Home1 > network > admin에 위치한다.
  

③ listener.ora 파일을 메모장으로 열고, 아래와 같이 수정한다.

• Oracle로 들어오는 요청에 대한 설정과 관련한 파일이다.
• 아래의 색칠된 내용의 HOST만 변경된 IP 주소로 수정하면 된다.
• 정적 IP가 아니라면, localhost를 입력하면 된다.
  

④ tnsnames.ora 파일도 메모장으로 열고, 아래와 같이 수정한다.

• 마찬가지로 아래의 색칠된 내용의 HOST만 변경된 IP 주소 또는 localhost로 수정하면 된다.
  

⑤ 작업 관리자에서 OracleOraDB21Home1TNSListener와 OracleServiceXE에 우클릭 > 다시 시작을 클릭한다.

⑥ SQL Developer에 접속하여 기존 Database에 우클릭 > 속성을 클릭한다.

⑦ 호스트 이름에 변경된 IP 주소를 입력한 후 테스트 버튼을 클릭한다. 테스트에 성공했다면, 접속 버튼을 클릭한다.

3. Oracle DB 원격 접속

1) 외부 접근 허용하기

외부에서 Oracle DB에 접속하려면, 포트 포워딩(방화벽 설정)을 먼저 진행해야 하므로, 로컬 PC(VM)에서 아래와 같은 설정을 진행한다.

① Window Defender 방화벽을 실행한다.

② 고급 설정 버튼을 클릭한다.

③ 인바운드 규칙 > 새 규칙 버튼을 클릭한다.

④ 포트를 선택하고 다음 버튼을 클릭한다.

⑤ Oracle DB의 기본 포트인 1521 포트를 지정하고, 다음 버튼을 클릭한다.

⑥ 연결 허용을 선택한 후, 다음 버튼을 클릭한다.

⑦ 원하는 규칙 이름을 지정하고, 마침 버튼을 클릭한다.

⑧ 새로운 인바운드 규칙이 잘 추가된 것을 확인할 수 있다.

2) DB에 원격으로 접속하기

이제 로컬 PC의 IP 주소를 이용해 데이터베이스에 원격으로 접속해보기로 하자. 이 때, IP 주소는 내부 IP이므로, 같은 서브넷에 있는 PC에서만 해당 주소에 접속할 수 있다. 그러므로, 다른 사원의 VM에서 내 VM에 존재하는 DB에 접근하는 시나리오를 가정하여 실습해보기로 한다.

① 다른 사원의 VM에서 데이터베이스 접속 툴에 접속한다.

• 다른 사원은 현재 DB 접속 툴로 DBeaver를 사용하고 있다.

② 새 데이터베이스 연결 버튼을 클릭하고, Oracle DB를 선택한다.

③ 아래와 같이 입력한다.

• Host에는 내 VM의 IP 주소를 넣는다.
• Database에눈 ORCL이 아닌 XE를 넣어야 한다.
• Username과 Password를 알맞게 입력한 후 Test Connection 버튼을 클릭했을 때 아래와 같이 Connected 창이 나오면 성공이다.
• 테스트에 성공했다면, 완료 버튼을 클릭한다.
  

④ 성공적으로 접속되었음을 확인할 수 있다.

4주차. 리눅스에서 Oracle DB 접속

1. Oracle 설치

① 아래의 링크에 접속하여 리눅스 전용 Oracle Database 11gR2 파일을 다운로드 받는다.
>> Oracle Download

② 윈도우에 다운로드 받은 rpm 파일을 리눅스로 이동하기 위해 WinSCP를 사용하기로 한다.

• 여기서는 다운로드 받은 rpm 파일을, /home/nubo/oracle 디렉토리를 만든 후 그 하위로 넣어주었다.

mkdir oracle

③ putty에 접속한 후, 아래의 명령을 입력하여 Oracle을 설치한다.

sudo apt-get update
sudo apt-get install -y alien unixodbc unzip libaio1
cd oracle
sudo unzip -x oracle-xe-11.2.0-1.0.x86_64.rpm.zip
cd Disk1
sudo alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm // rpm(redhat 계열에서 사용) 패키지를 deb(debian 계열에서 사용) 패키지로 변환
ls // dev 파일이 잘 생성되었는지 확인
sudo dpkg --install oracle*.deb // Oracle DB 설치

④ 계속해서 putty에 아래의 명령을 입력하여, 설정을 진행하자.

• 사용할 포트 번호와 비밀번호를 설정하면 된다.

sudo /etc/init.d/oracle-xe configure

⑤ 아래의 명령을 입력하여, 오라클이 잘 실행되는지 확인하자.

sudo systemctl start oracle-xe // 오라클 실행
systemctl status oracle-xe

⑥ sudo vi ~/.bashrc를 입력한 후 아래의 내용을 추가한다.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

⑦ putty를 duplicate session으로 다시 열고, 아래의 명령을 입력하여 리스너의 상태를 확인하자.

lsnrctl status

⑧ sqlplus 명령을 입력하여 Oracle DB에 접속할 수 있다.

• 최초 로그인 시에는 username에 system을, password에 본인이 설정한 비밀번호를 입력해야 한다.

sqlplus

2. DB 원격 접속

① 아래의 명령을 입력하여 데이터베이스에 원격으로 접속할 수 있다.

sqlplus {유저 이름}/{비밀번호}@{원격 접속할 IP 주소}:{포트 번호}/SID

② Oracle에서는 MySQL의 show databases와 show tables 명령 대신 아래와 같은 명령으로 사용한다.

// show databases
select distinct owner from all_tables;

// show tables
select table_name owner from user_tables;

③ Employee 테이블에 데이터를 추가해보자.

desc employee;
insert into Employee values(1, 'Hyunseop', 'M', 5000);
insert into Employee values(2, 'Chrome', 'M', 4500);
select * from Employee;

④ Database에 변경 사항이 생겼을 때에는, 반드시 Commit을 진행해야 변경 사항이 적용된다.

Commit

3. Oracle Query 사용법

Oracle Query는 MySQL Query와 거의 비슷하므로, 간단히 쿼리가 실행되는 것만 확인해보기로 하자.

① Select, Insert, Update, Delete, Drop 쿼리

select * from Employee;
insert into Employee (emp_id, name, gender, salary) values(1, 'Hyunseop', 'M', 5000);
update employee set name='Chrome' where emp_id=1;
delete from employee where emp_id=1;
drop table Employee;

② 외래키 제약 조건 적용

• 테이블 생성과 동시에 외래키 제약 조건 적용

// Company 테이블 생성
create table Company (
  company_id int primary key,
  name varchar(40)
);

// Employee 테이블 생성
create table Employee (
  employee_id int primary key,
  name varchar(40),
  gender varchar(1),
  salary int,
  company_id int,
  foreign key (company_id)
  references Company(company_id)
);

• 이미 생성된 테이블에 외래키 제약 조건을 추가

// 기존 Employee 테이블 삭제
drop table Employee;

// 외래키 제약 조건을 적용하지 않은 Employee 테이블 생성
create table Employee (
  employee_id int primary key,
  name varchar(40),
  gender varchar(1),
  salary int,
  company_id int
);

// 외래키 제약 조건 적용
alter table Employee
add foreign key (company_id)
references Company(company_id);

③ Primary Key에 Auto Increment 적용하기

• 테이블 생성과 동시에 Auto Increment 적용하기

create table Department (
  department_id int generated by default as identity primary key,
  name varchar(40),
  location varchar(45)
);

// ID 없이도 삽입 가능 
insert into Department (name, location) values('Technology', 'Seoul'); 

• Sequence를 생성하여 값을 가져오는 방식으로 Auto Increment 적용하기

create sequence employee_seq;
select employee_seq.nextval from dual; // 다음 Seq의 값
insert into employee VALUES (employee_seq.nextval, 'Hyunseop', 'M', 5000, 3);

④ Join 문

select e.name, c.name 
from Employee e, Company c 
where c.company_id=e.conpany_id

⑤ Index 생성하기

create index idx_employee_name on Employee(name);

⑥ 집계 함수 사용하기

select sum(salary) from Employee;
select avg(salary) from Employee;
select min(salary) from Employee;
select max(salary) from Employee;
select count(*) from Employee;

⑦ Group By 절 사용하기

select company_id, avg(salary) as avg_salary
from Employee
group by company_id
having avg(salary) >= 5000;

4. Oracle Instant Client

1) 필요성

지금까지는 Oracle을 패키지 형태로 다운로드 받는 방법을 소개하였는데, 사실 이 방법은 DB에 원격 접속만 하면 되는 환경에서는 적절하지 않다. 그 이유는 패키지로 설치하는 방법은 설치가 쉽고, DB를 로컬 환경과 원격 환경에서 모두 접속할 수 있다는 장점은 있으나, 불필요한 내용까지 다운로드 받게 된다는 점에서 서버 리소스가 낭비되는 문제가 있다.

일반적으로, 서버를 배포할 환경(Linux)과 DB가 구축된 환경이 다를 때에는, SQLPLUS만 실행되도록 만들어 서버 환경을 가볍게 만들어주는 편이 좋다. 이 때, 사용할 수 있는 것이 바로 Oracle Instant Client이다.

Oracle Instant Client는 Oracle Database에 접근하기 위해 사용하는, 간편한 클라이언트 소프트웨어 패키지이다. Oracle Instant Client에는 애플리케이션을 개발하고 배포하는 데에 필요한 최소한의 파일만 포함되어 있다. 주로 Oracle 데이터베이스로의 접속을 위해 사용되므로, 애플리케이션을 실행하는 서버 환경에서 유용하다.

2) 설치 방법

그러면 지금부터 Oracle Instant Client를 설치하는 방법에 대해 알아보기로 하자.

① 아래의 사이트에 접속한다.
>> Oracle Instant Client 다운로드

② Ubuntu에서는 Instant Client 11.2. 버전과 12.1 버전만 지원된다.

• 여기서는 11.2 버전을 사용하기로 한다.
  

③ 아래에 표시된 basic, sqlplus 파일을 다운로드 받는다.

④ 다운로드 받은 Zip 파일의 압축을 해제한 후, 두 디렉토리에 존재하는 파일들을 하나의 디렉토리로 합친다.

• 여기서는 sqlplus 디렉토리 하위의 instantclient_11_2에 합쳤다.

⑤ WinSCP를 이용하여 instantclient_11_2 디렉토리를 리눅스로 옮겨주자.

• 여기서는 ~/oracle 디렉토리 하위로 옮겨주었다.

⑥ 윈도우에서 압축을 해제한 후 WinSCP로 옮길 경우, 실행 권한이 사라진다. chmod 명령을 이용해 실행 권한을 부여해주자.

cd instantclient_11_2
ls -l // 실행 권한 확인
sudo chmod 775 lib* adrci genezi sqlplus uidrvci glogin.sql // basic 관련 권한 부여
sudo chmod 555 glogin.sql libsqlplus* libocijdbc11.so sqlplus // sqlplus 관련 권한 부여

⑦ bashrc 파일을 열어 환경변수 설정을 진행하자.

sudo vi ~/.bashrc

# bashrc 파일의 환경변수 설정을 아래와 같이 변경한다.
export ORACLE_HOME=/home/nubo/oracle/instantclient_11_2
export ORACLE_SID=XE
export NLS_LANG=KOREAN_KOREA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

⑧ PATH 와 LD_LIBRARY_PATH의 값을 통해 Oracle의 경로를 찾을 때, lib, bin 폴더를 기준으로 찾게 되므로, lib, bin 폴더로 파일을 옮겨주어야 한다.

• rpm 파일을 설치할 때와 달리, Zip 파일을 압축 해제하는 방식에서는 파일이 lib, bin 폴더 안에 자동으로 정리되지 않기 때문이다.
• 아래의 명령을 통해 bin 디렉토리를 생성하자.

sudo mkdir -p ./bin
sudo mkdir -p ./lib

⑨ adrci genezi, sqlplus 파일을 bin 디렉토리 하위로, 나머지 모든 파일은 lib 디렉토리 하위로 옮긴다.

sudo mv adrci genezi sqlplus ./bin
sudo mv * ./lib

⑩ sqlplus가 정상적으로 동작하는 것을 확인할 수 있다.

sqlplus {유저 이름}/{비밀번호}@{원격 접속할 IP 주소}:{포트 번호}/SID