Admin 실습 03: DB 수동 생성 & 네트워크 구성, DB 링크

실습 스크립트 전체 보기: https://github.com/nsylove97/Seongryeol-OracleDB-Portfolio

실습 환경

OS	Oracle Linux 7.9 (VMware Virtual Machine)
DB	Oracle Database 19c
접속 툴	SQL*Plus, MobaXterm(SSH)

---

1. 환경 변수 설정 & 사전 준비

-- STEP 1: 새 OS 계정 생성 (root 계정으로 실행)
-- 새 OS 유저 생성 및  dba 그룹에 추가
useradd -G dba produser
passwd produser
-- 비밀번호 입력

-- oracle 계정의 환경변수 파일을 produser 홈으로 복사
cp /home/oracle/.bash_profile /home/produser/.bash_profile
chown produser:produser /home/produser/.bash_profile

produser 생성 및 dba 그룹에 추가

oracle 계정의 .bash_profile을 prod 홈 디렉토리로 복사 후 소유권 변경

-- STEP 2: produser 계정으로 전환 후 환경변수 설정
su - produser
vi ~/.bash_profile

-- .bash_profile 에 아래 내용 추가/수정
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome
export ORACLE_SID=PROD                      # 새로 만들 DB 이름으로 설정
export PATH=$ORACLE_HOME/bin:$PATH

-- tnsnames.ora 경로 자동 인식 설정
export TNS_ADMIN=$ORACLE_HOME/network/admin
alias tns='cd $ORACLE_HOME/network/admin'   # tns 명령어로 바로 이동

produser 계정으로 전환 후 환경변수 설정

.bash_profile 수정

-- 환경변수 즉시 반영
. ~/.bash_profile

-- 반영됐는지 확인
echo $ORACLE_SID     # PROD 출력 확인
echo $ORACLE_HOME    # Oracle Home 경로 확인
whoami               # produser 출력 확인

-- 현재 사용 가능한 SID 목록 확인
cat /etc/oratab

 

환경변수 적용 및 확인 후 사용가능한 SID 목록 확인

---

 DB 수동 생성 (PROD)

STEP 1. 데이터 파일 저장 디렉토리 생성

-- PROD DB 데이터파일 저장 디렉토리 생성
mkdir -p /u02/oradata/PROD

-- 소유자 및 권한 설정
-- DB 파일 생성·수정·삭제가 가능하도록 oracle:dba 소유, 775 권한 부여
chown -R oracle:dba /u02/oradata/PROD
chmod -R 775 /u02/oradata/PROD

-- 디렉토리 생성 확인
ls -l /u02/oradata/PROD

PROD 데이터 파일 저장 디렉토리 생성 후 소유자 및 권한 설정

 

STEP 2.  패스워드 파일 생성

-- SYSDBA 인증을 위한 패스워드 파일 생성 (oracle 계정에서)
-- 19c에서는 format=12, force=y 추가 필수
orapwd file=$ORACLE_HOME/dbs/orapwPROD \
        password=oracle \
        entries=10 \
        format=12 \
        force=y

-- 생성 확인
ls -l $ORACLE_HOME/dbs/orapwPROD

패스워드 파일 생성 후 확

 

STEP 3. pfile (initPROD.ora) 작성

-- DB가 없는 상태에서는 spfile을 못 쓰므로 텍스트 기반 pfile 필수
vi $ORACLE_HOME/dbs/initPROD.ora

-- initPROD.ora 내용
db_name          = PROD # 필수 01
db_block_size    = 8192 # 필수 02
memory_target    = 800M
processes        = 150
undo_management  = AUTO
undo_tablespace  = UNDOTBS1
control_files    = ('/u02/oradata/PROD/control01.ctl',
                    '/u02/oradata/PROD/control02.ctl') # 필수 03
db_recovery_file_dest      = '/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size = 5G
diagnostic_dest  = /u01/app/oracle

# pfile 생성 확인
ls -l $ORACLE_HOME/dbs/initPROD.ora
cat $ORACLE_HOME/dbs/initPROD.ora

initPROD.ora 생성

pfile 생성 확인

 

STEP 4. NOMOUNT 단계 진입

-- SYSDBA로 접속
sqlplus / as sysdba

-- pfile로 NOMOUNT 단계 진입
STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initPROD.ora';

/*
 [결과]
   ORACLE instance started.
   Total System Global Area  838860800 bytes
   ...
   → 인스턴스(메모리+프로세스)만 뜬 상태, DB 파일은 아직 없음
*/

 

STEP 5. CREATE DATABASE 실행

-- PROD 데이터베이스 수동 생성 스크립트 편집 (OS에서)
vi createPROD.sql

-- createPROD.sql 내용
CREATE DATABASE PROD
    USER SYS    IDENTIFIED BY oracle
    USER SYSTEM IDENTIFIED BY oracle
    LOGFILE
        GROUP 1 ('/u02/oradata/PROD/redo01a.log') SIZE 50M,
        GROUP 2 ('/u02/oradata/PROD/redo02a.log') SIZE 50M
    CHARACTER SET     AL32UTF8       -- 한글 포함 유니코드
    NATIONAL CHARACTER SET AL16UTF16
    DATAFILE        '/u02/oradata/PROD/system01.dbf'  SIZE 700M REUSE
    SYSAUX DATAFILE '/u02/oradata/PROD/sysaux01.dbf'  SIZE 600M REUSE
    DEFAULT TABLESPACE users
        DATAFILE '/u02/oradata/PROD/users01.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    DEFAULT TEMPORARY TABLESPACE temp
        TEMPFILE '/u02/oradata/PROD/temp01.dbf' SIZE 100M REUSE
    UNDO TABLESPACE UNDOTBS1
        DATAFILE '/u02/oradata/PROD/undotbs01.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

-- 저장 후 실행 (sqlplus에서)
@createPROD.sql

/*
 [결과]
   Database created.
   → control file, redo log, 핵심 datafile 모두 생성 완료
*/

createPROD.sql 내용

sqlplus에서 실행, DB 생성 완료

 

STEP 6.  데이터 딕셔너리 & SQL*Plus 환경 초기화

-- 데이터 딕셔너리 뷰 생성 (catalog.sql)
-- DBA_TABLES, V$SESSION 같은 뷰를 사용하려면 반드시 실행
@?/rdbms/admin/catalog.sql

-- PL/SQL 패키지 생성 (catproc.sql)
@?/rdbms/admin/catproc.sql

-- SQL*Plus 전용 환경 초기화 (PRODUCT_USER_PROFILE 테이블 생성)
-- SYSTEM 계정으로 실행해야 함
CONN system/oracle
@?/sqlplus/admin/pupbld.sql

/*
 [결과]
   여러 줄의 생성 메시지 출력됨 — 에러가 없으면 정상
   ? 는 $ORACLE_HOME 환경변수를 의미
*/

catalog.sql 실행 완료

catproc.sql 생성 완료

pupbld.sql 실행 완료

 

STEP 7. 생성 결과 확인

CONN / AS SYSDBA

-- DB 이름 및 상태 확인
SELECT NAME, OPEN_MODE, DB_UNIQUE_NAME FROM V$DATABASE;

/*
 [결과]
   NAME    OPEN_MODE    DB_UNIQUE_NAME
   ------- ------------ --------------
   PROD    READ WRITE   PROD
*/

sys계정에서 DB 이름 및 상태 확인

-- 테이블스페이스 생성 확인
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

-- 데이터파일 생성 확인
SELECT NAME FROM V$DATAFILE;

-- 리스너에서 PROD 서비스 등록 확인 (터미널에서 실행)
lsnrctl services

dba_tablespaces 뷰로 테이블스페이스 생성 확인

데이터 파일 생성 확인

리스너에 PROD 서비스 등록 확인

STEP 8. 새 DB에 클라이언트 방식으로 접속

-- hostname 방식으로 PROD DB에 접속
sqlplus hr/hr@localhost:1521/PROD

hostname 방식으로 PROD DB의 hr 계정 접속

---

3. Oracle Net 설정 파일 직접 편집

tnsnames.ora — 로컬 네이밍 등록

-- 파일 위치로 이동 (alias 설정 시 tns 명령어로 바로 이동 가능)
cd $ORACLE_HOME/network/admin
vi tnsnames.ora

tnsnames.ora 파일이 없으니 새로 생성

-- tnsnames.ora 설정 예시

-- 기존 orcl DB 별칭
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7vr)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

-- 새로 만든 PROD DB 별칭
PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel7vr)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

tnsnames.ora에 orcl과 prod DB 별칭 입력

-- 별칭으로 접속 테스트
tnsping ORCL
tnsping PROD
# OK (xx msec) 확인

-- 별칭으로 DB 접속
sqlplus hr/hr@PROD

 

별칭으로 접속 테스트 성공

별칭으로 접속 성공

---

4. 리스너 기본 관리 실습

-- 리스너 상태 확인
lsnrctl status

-- 리스너가 관리 중인 서비스 목록 확인
lsnrctl services

-- 리스너 사용 가능한 명령어 목록 확인
lsnrctl help

-- 리스너 Trace 레벨 설정 (실시간 적용)
lsnrctl set trc_level off      # 기록 안 함 (기본값)
lsnrctl set trc_level user     # 일반 수준
lsnrctl set trc_level admin    # 관리자 수준
lsnrctl set trc_level support  # 가장 자세함 (Oracle 지원팀용)

lsnrctl show trc_level         # 현재 설정 확인

 

리스너 상태 확인

리스너가 관리 중인 서비스 목록 확인

 

리스너가 사용 가능한 명령어 목록 확인

리스너 현재 trace 레벨 설정 확인 (off)

---

5. 다중 리스너 구성 실습

listener.ora 직접 편집

-- 리스너 설정 파일 생성 및 편집
vi $ORACLE_HOME/network/admin/listener.ora

-- 기본 리스너 (1521 포트)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel7vr)(PORT = 1521))
    )
  )

-- 추가 리스너 lsnr1 (1522 포트)
LSNR1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel7vr)(PORT = 1522))
    )
  )

listener.ora 파일 생성 후 기본/추가 리스너 정보 입력

-- 두 리스너 모두 시작
lsnrctl start
lsnrctl start lsnr1

-- 각 리스너 상태 확인
lsnrctl status
lsnrctl status lsnr1

-- 서비스 등록 확인
lsnrctl services lsnr1

기본 리스너는 이미 실행 중이니 추가 리스너 시작

각 리스너 상태 확인 (모두 실행 중)

추가 리스너에는 등록된 서비스가 아직 없음

non-default 리스너에 인스턴스 수동 등록

-- 인스턴스가 non-default 리스너를 못 읽을 때 수동으로 등록
ALTER SYSTEM SET local_listener =
    '(ADDRESS=(PROTOCOL=TCP)(HOST=oel7vr)(PORT=1522))'
    SCOPE=BOTH;

ALTER SYSTEM REGISTER;

-- 등록 확인
lsnrctl services lsnr1

orcl DB에서 non-default 리스너 수동 등록

orcl 서비스 등록 확인

포트별 접속 테스트

-- 기본 리스너 정지 후 1522 포트만으로 접속 테스트
lsnrctl stop
sqlplus hr/hr@oel7vr:1521/orcl
-- ORA-12541: TNS:no listener ← 기본 리스너 없으면 에러

sqlplus hr/hr@oel7vr:1522/orcl
-- 접속 성공 ← lsnr1은 살아있으므로

-- 기본 리스너 복구
lsnrctl start

기본 리스너 정지 후 1521 포트로는 접속 불가

1522포트로는 접속 성공

---

6. Naming Methods 비교 실습

① Easy Connect

-- 설정 파일 없이 바로 접속
-- 형식: sqlplus 계정/패스워드@호스트:포트/서비스명
sqlplus hr/hr@oel7vr:1521/orcl
sqlplus hr/hr@oel7vr:1521/PROD

Easy Connect 방식으로 orcl과 prod DB 접속 성공

② Local Naming (tnsnames.ora)

-- tnsping으로 연결 가능 여부 사전 확인
tnsping orcl
tnsping PROD

-- 별칭으로 간단하게 접속
sqlplus hr/hr@orcl
sqlplus hr/hr@PROD

tnsping으로 연결 테스트 후 Local Naming 방식으로 별칭 입력해서 orcl과 prod DB 접속 성공

 

Easy Connect vs Local Naming 비교

항목	Easy Connect	Local Naming
설정 파일	불필요	tnsnames.ora 필요
접속 형식	@호스트:포트/서비스명	@별칭
Failover/LB	미지원	지원
보안성	낮음	높음
실무 사용	빠른 테스트용	운영 환경 표준

---

7. Database Link 실습

-- PROD DB의 SYS 계정에서 hr에게 DB Link 생성 권한 부여
GRANT CREATE DATABASE LINK TO hr;

-- hr 계정으로 접속
CONN hr/hr

-- DB Link 생성
-- 형식: CREATE DATABASE LINK 링크명
--         CONNECT TO 계정 IDENTIFIED BY 비번
--         USING 'net_service_name';
CREATE DATABASE LINK remote_orcl
    CONNECT TO hr IDENTIFIED BY hr
    USING 'ORCL';            -- tnsnames.ora에 등록된 서비스명

-- PROD DB에서 DB Link를 통한 원격 테이블 조회 (테이블명@링크명) 
SELECT COUNT(*) FROM employees@remote_orcl;

/*
 [결과]
   COUNT(*)
   --------
   107       ← 원격 DB 테이블 조회 성공
*/

hr에게 권한 부여 후 DB 링크 생성 및 원격 테이블 조회

-- Synonym 생성 — @링크명 없이 바로 접근
CREATE SYNONYM remote_emp FOR employees@remote_orcl;

SELECT COUNT(*) FROM remote_emp;     -- Synonym으로 간단하게 조회

-- DB Link 목록 확인
SELECT DB_LINK, USERNAME, HOST FROM USER_DB_LINKS;

/*
 [결과]
   DB_LINK        USERNAME    HOST
   -------------- ----------- ----
   REMOTE_ORCL    HR          ORCL
*/

-- 실습 후 정리
DROP SYNONYM remote_emp;
DROP DATABASE LINK remote_orcl;

시노님 등록 후 시노님으로 바로 접근 가능

user_db_links 뷰에서도 확인 가능

---

실습 핵심 요약

주제	핵심 포인트
DB 수동 생성	pfile 작성 → STARTUP NOMOUNT → CREATE DATABASE 순서 필수
비밀번호 파일	orapwd 로 생성, SYSDBA 인증에 사용
tnsnames.ora	별칭 등록 → @별칭으로 접속, tnsping으로 연결 테스트
다중 리스너	listener.ora에 포트 추가 → lsnrctl start 리스너명
non-default 리스너	ALTER SYSTEM SET local_listener + ALTER SYSTEM REGISTER
DB Link	CREATE DATABASE LINK → 테이블@링크명으로 원격 조회
Synonym	@링크명 없이 접근 가능하도록 별칭 생성

---