251125 TIL

2025. 11. 25. 18:26ㆍCourses/아이티윌 오라클 DBA 과정

오라클 19C 설치

가상 머신 생성

리눅스 설정

DATE & TIME : Asia/Seoul
SOFTWARE SELECTION : Server with GUI
INSTALLATION DESTINATION : 파티션 선택
NETWORK & HOST NAME :
- General : Automatically connect to this network when it is available 체크
- enp0S8 IPv4 설정
  - Method : Manual
  - IP : 192.168.56.150
  - Subnet Mask : 255.255.255.0
  - Host name : ora19c

KDUMP : Enable kdump 체크 해제
SECURITY POLICY : Apply security policy OFF

root : 1234
user : itwill/1234

Reboot

LICENSE 동의 후 FINISH CONFIGURATION

접속

login as: root
root@192.168.56.150's password:
Last login: Tue Nov 25 10:55:16 2025 from 192.168.56.1
[root@ora19c ~]# hostname
ora19c
[root@ora19c ~]# ifconfig
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.2.15  netmask 255.255.255.0  broadcast 10.0.2.255
        inet6 fe80::adfa:7f26:55e7:1893  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:55:fd:33  txqueuelen 1000  (Ethernet)
        RX packets 173  bytes 222097 (216.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 82  bytes 8762 (8.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.150  netmask 255.255.255.0  broadcast 192.168.56.255
        inet6 fe80::65df:6c6d:53ae:78b7  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:0b:df:68  txqueuelen 1000  (Ethernet)
        RX packets 139  bytes 18097 (17.6 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 140  bytes 21214 (20.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 60  bytes 5100 (4.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 60  bytes 5100 (4.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

virbr0: flags=4099<UP,BROADCAST,MULTICAST>  mtu 1500
        inet 192.168.122.1  netmask 255.255.255.0  broadcast 192.168.122.255
        ether 52:54:00:a9:9d:14  txqueuelen 1000  (Ethernet)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

hosts 설정

[root@ora19c ~]# vi /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.150 ora19c

preinstall 패키지를 이용한 오라클 환경 설정

OS 커널 파라미터 설정
ORACLE 유저 생성
ORACLE에서 필요한 OS 그룹 생성(oinstall, dba, …)
ORACLE에서 필요한 패키지들 설치

yum -y  install oracle-database-preinstall-19c

충돌이 발생하면 해당 파일 지우고 다시 설치

# 오라클 유저 생성됨
[root@ora19c ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)

[root@ora19c ~]# tail /etc/passwd
nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
gnome-initial-setup:x:989:983::/run/gnome-initial-setup/:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
tcpdump:x:72:72::/:/sbin/nologin
itwill:x:1000:1000:itwill:/home/itwill:/bin/bash
vboxadd:x:988:1::/var/run/vboxadd:/bin/false
oracle:x:54321:54321::/home/oracle:/bin/bash

# 오라클에 필요한 OS 그룹 생성됨
[root@ora19c ~]# tail /etc/group
itwill:x:1000:itwill
vboxsf:x:982:
vboxdrmipc:x:981:
oinstall:x:54321:oracle
dba:x:54322:oracle
oper:x:54323:oracle
backupdba:x:54324:oracle
dgdba:x:54325:oracle
kmdba:x:54326:oracle
racdba:x:54330:oracle

[root@ora19c ~]# sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

[root@ora19c ~]# grep -v ^# /etc/security/limits.d/oracle-database-preinstall-19c.conf

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

패스워드 설정

[root@ora19c ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.

오라클 설치 디렉터리 생성 및 권한 설정

[root@ora19c ~]# mkdir -p /u01/app/oracle
[root@ora19c ~]# mkdir -p /u01/app/oraInventory
[root@ora19c ~]# mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1
[root@ora19c ~]# chown -R oracle:oinstall /u01
[root@ora19c ~]# chmod -R 775 /u01

[root@ora19c ~]# ls -lR /u01
/u01:
total 0
drwxrwxr-x. 4 oracle oinstall 40 Nov 25 11:16 app

/u01/app:
total 0
drwxrwxr-x. 3 oracle oinstall 21 Nov 25 11:16 oracle
drwxrwxr-x. 2 oracle oinstall  6 Nov 25 11:16 oraInventory

/u01/app/oracle:
total 0
drwxrwxr-x. 3 oracle oinstall 20 Nov 25 11:16 product

/u01/app/oracle/product:
total 0
drwxrwxr-x. 3 oracle oinstall 22 Nov 25 11:16 19.3.0

/u01/app/oracle/product/19.3.0:
total 0
drwxrwxr-x. 2 oracle oinstall 6 Nov 25 11:16 dbhome_1

/u01/app/oracle/product/19.3.0/dbhome_1:
total 0

/u01/app/oraInventory:
total 0

오라클 유저 환경 설정

# .bash_profile 수정
[oracle@ora19c ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
# 아래 부분 추가
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=ora19c
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
############################################################################

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

# 변경 사항 적용
[oracle@ora19c ~]$ source .bash_profile

# ORA를 가지는 환경변수 출력
[oracle@ora19c ~]$ env | grep ORA
ORACLE_SID=ora19c
ORACLE_BASE=/u01/app/oracle
ORA_INVENTORY=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

오라클 설치 파일 전송

[oracle@ora19c ~]$ ls
LINUX.X64_193000_db_home.zip

오라클 설치 파일 압축 풀기

# $ORACLE_HOME으로 이동
[oracle@ora19c ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ora19c ~]$ cd $ORACLE_HOME
[oracle@ora19c dbhome_1]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1

# oracle 홈디렉터리에 있는 오라클 압축 파일을 현재 디렉터리에 압축 해제
[oracle@ora19c dbhome_1]$ ls ~/
LINUX.X64_193000_db_home.zip
[oracle@ora19c dbhome_1]$ unzip -q ~/LINUX.X64_193000_db_home.zip
[oracle@ora19c dbhome_1]$ ls
addnode     deinstall      javavm   OPatch   R              sqlj
apex        demo           jdbc     opmn     racg           sqlpatch
assistants  diagnostics    jdk      oracore  rdbms          sqlplus
bin         dmu            jlib     ord      relnotes       srvm
clone       drdaas         ldap     ords     root.sh        suptools
crs         dv             lib      oss      root.sh.old    ucp
css         env.ora        md       oui      root.sh.old.1  usm
ctx         has            mgw      owm      runInstaller   utl
cv          hs             network  perl     schagent.conf  wwg
data        install        nls      plsql    sdk            xdk
dbjava      instantclient  odbc     precomp  slax
dbs         inventory      olap     QOpatch  sqldeveloper

오라클 설치

Putty에서는 불가능 → MobaXterm 이용

oracle@192.168.56.150's password:
    ┌──────────────────────────────────────────────────────────────────────┐
    │                 • MobaXterm Personal Edition v24.0 •                 │
    │               (SSH client, X server and network tools)               │
    │                                                                      │
    │ ⮞ SSH session to oracle@192.168.56.150                               │
    │   • Direct SSH      :  ✓                                             │
    │   • SSH compression :  ✓                                             │
    │   • SSH-browser     :  ✓                                             │
    │   • X11-forwarding  :  ✓  (remote display is forwarded through SSH)  │
    │                                                                      │
    │ ⮞ For more info, ctrl+click on help or visit our website.            │
    └──────────────────────────────────────────────────────────────────────┘

Last login: Tue Nov 25 11:19:38 2025
/usr/bin/xauth:  file /home/oracle/.Xauthority does not exist
[oracle@ora19c ~]$ env | grep ORA
ORACLE_SID=ora19c
ORACLE_BASE=/u01/app/oracle
ORA_INVENTORY=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

# $ORACLE_HOME으로 이동
[oracle@ora19c ~]$ cd $ORACLE_HOME
[oracle@ora19c dbhome_1]$ ls
addnode     clone  cv      deinstall    drdaas   hs             javavm  ldap  network  OPatch   ords  perl     R         root.sh        schagent.conf  sqlj      suptools  wwg
apex        crs    data    demo         dv       install        jdbc    lib   nls      opmn     oss   plsql    racg      root.sh.old    sdk            sqlpatch  ucp       xdk
assistants  css    dbjava  diagnostics  env.ora  instantclient  jdk     md    odbc     oracore  oui   precomp  rdbms     root.sh.old.1  slax           sqlplus   usm
bin         ctx    dbs     dmu          has      inventory      jlib    mgw   olap     ord      owm   QOpatch  relnotes  runInstaller   sqldeveloper   srvm      utl
[oracle@ora19c dbhome_1]$ ls -l

# runInstaller 실행
[oracle@ora19c dbhome_1]$ ./runInstaller
Launching Oracle Database Setup Wizard...

앞부분에서 오라클 환경 설정을 해줬기 때문에 자동으로 잡힘

root 유저 비밀번호 설정 : 1234

Listener 설정

[oracle@ora19c dbhome_1]$ netca

Oracle Net Services Configuration:

[oracle@ora19c dbhome_1]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 13:49:53

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2025 13:49:03
Uptime                    0 days 0 hr. 0 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

DB 생성

[oracle@ora19c dbhome_1]$ dbca

default language와 territory는 OS에 종속됨

Add sample schemas to the database를 체크해줘야 hr 이 생성됨

ORACLE 백그라운드 프로세스 조회

[oracle@ora19c ~]$ ps -ef | grep oracle
oracle    4990     1  0 13:49 ?        00:00:00 /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle    8857     1  0 14:22 ?        00:00:00 ora_pmon_ora19c
oracle    8859     1  0 14:22 ?        00:00:00 ora_clmn_ora19c
oracle    8861     1  0 14:22 ?        00:00:00 ora_psp0_ora19c
oracle    8863     1  6 14:22 ?        00:00:50 ora_vktm_ora19c
oracle    8868     1  0 14:22 ?        00:00:00 ora_gen0_ora19c
oracle    8870     1  0 14:22 ?        00:00:00 ora_mman_ora19c
oracle    8874     1  0 14:22 ?        00:00:00 ora_gen1_ora19c
oracle    8877     1  0 14:22 ?        00:00:00 ora_diag_ora19c
oracle    8879     1  0 14:22 ?        00:00:00 ora_ofsd_ora19c
oracle    8882     1  0 14:22 ?        00:00:00 ora_dbrm_ora19c
oracle    8884     1  0 14:22 ?        00:00:00 ora_vkrm_ora19c
oracle    8886     1  0 14:22 ?        00:00:00 ora_svcb_ora19c
oracle    8888     1  0 14:22 ?        00:00:00 ora_pman_ora19c
oracle    8890     1  0 14:22 ?        00:00:00 ora_dia0_ora19c
oracle    8892     1  0 14:22 ?        00:00:00 ora_dbw0_ora19c
oracle    8894     1  0 14:22 ?        00:00:00 ora_lgwr_ora19c
oracle    8896     1  0 14:22 ?        00:00:00 ora_lg00_ora19c
oracle    8898     1  0 14:22 ?        00:00:00 ora_ckpt_ora19c
oracle    8900     1  0 14:22 ?        00:00:00 ora_lg01_ora19c
oracle    8902     1  0 14:22 ?        00:00:00 ora_smon_ora19c
oracle    8904     1  0 14:22 ?        00:00:00 ora_smco_ora19c
oracle    8906     1  0 14:22 ?        00:00:00 ora_reco_ora19c
oracle    8908     1  0 14:22 ?        00:00:00 ora_w000_ora19c
oracle    8910     1  0 14:22 ?        00:00:00 ora_lreg_ora19c
oracle    8912     1  0 14:22 ?        00:00:00 ora_w001_ora19c
oracle    8914     1  0 14:22 ?        00:00:00 ora_pxmn_ora19c
oracle    8918     1  0 14:22 ?        00:00:02 ora_mmon_ora19c
oracle    8920     1  0 14:22 ?        00:00:00 ora_mmnl_ora19c
oracle    8922     1  0 14:22 ?        00:00:00 ora_d000_ora19c
oracle    8924     1  0 14:22 ?        00:00:00 ora_s000_ora19c
oracle    8926     1  0 14:22 ?        00:00:00 ora_tmon_ora19c
oracle    8929     1  0 14:22 ?        00:00:01 ora_m000_ora19c
oracle    8938     1  0 14:22 ?        00:00:00 ora_tt00_ora19c
oracle    8940     1  0 14:22 ?        00:00:00 ora_tt01_ora19c
oracle    8942     1  0 14:22 ?        00:00:00 ora_tt02_ora19c
oracle    8945     1  0 14:22 ?        00:00:00 ora_aqpc_ora19c
oracle    8947     1  0 14:22 ?        00:00:01 ora_cjq0_ora19c
oracle    8949     1  0 14:22 ?        00:00:00 ora_w002_ora19c
oracle    8954     1  0 14:22 ?        00:00:00 ora_p000_ora19c
oracle    8956     1  0 14:22 ?        00:00:00 ora_p001_ora19c
oracle    8958     1  0 14:22 ?        00:00:00 ora_p002_ora19c
oracle    8960     1  0 14:22 ?        00:00:00 ora_p003_ora19c
oracle    9117     1  0 14:22 ?        00:00:00 ora_w003_ora19c
oracle    9150     1  0 14:22 ?        00:00:00 ora_w004_ora19c
oracle    9154     1  0 14:22 ?        00:00:00 ora_m001_ora19c
oracle    9156     1  0 14:22 ?        00:00:00 ora_m002_ora19c
oracle    9158     1  0 14:22 ?        00:00:02 ora_m003_ora19c
oracle    9185     1  0 14:22 ?        00:00:00 ora_qm02_ora19c
oracle    9189     1  0 14:22 ?        00:00:00 ora_q002_ora19c
oracle    9191     1  0 14:22 ?        00:00:00 ora_q003_ora19c
oracle    9765     1  0 14:32 ?        00:00:00 ora_w005_ora19c
oracle    9769     1  0 14:32 ?        00:00:00 ora_w006_ora19c
oracle    9773     1  0 14:32 ?        00:00:00 ora_m004_ora19c
oracle    9776     1  0 14:32 ?        00:00:00 ora_w007_ora19c
oracle    9992 23911  0 14:35 pts/0    00:00:00 ps -ef
oracle    9993 23911  0 14:35 pts/0    00:00:00 grep --color=auto oracle
root     23910  3278  0 11:19 pts/0    00:00:00 su - oracle
oracle   23911 23910  0 11:19 pts/0    00:00:00 -bash
root     24021  1331  0 11:26 ?        00:00:00 sshd: oracle [priv]
oracle   24025 24021  0 11:27 ?        00:00:41 sshd: oracle@notty
oracle   24026 24025  0 11:27 ?        00:00:17 /usr/libexec/openssh/sftp-server
root     24252  1331  0 11:49 ?        00:00:00 sshd: oracle [priv]
root     24264  1331  0 11:49 ?        00:00:00 sshd: oracle [priv]
oracle   24266 24252  0 11:49 ?        00:00:22 sshd: oracle@pts/1
oracle   24269 24264  0 11:49 ?        00:00:00 sshd: oracle@notty
oracle   24270 24269  0 11:49 ?        00:00:00 /usr/libexec/openssh/sftp-server
oracle   24282 24266  0 11:49 pts/1    00:00:00 -bash

리스너 상태 조회

[oracle@ora19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 14:38:06

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2025 13:49:03
Uptime                    0 days 0 hr. 49 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora19c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora19c" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully

DB 접속

login as: oracle
oracle@192.168.56.150's password:
Last login: Tue Nov 25 12:02:12 2025
[oracle@ora19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 25 14:43:36 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user
USER is "SYS"

SQL*Plus 접속 상태에서 리눅스 명령어 사용법

! 뒤에 리눅스 명령어 사용

SQL> ! env | grep ORA
ORACLE_SID=ora19c
ORACLE_BASE=/u01/app/oracle
ORA_INVENTORY=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1

SQL> ! lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:18:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.
...

! 엔터 → 리눅스 명령어 사용 → exit

SYS@ora19c> !
[oracle@ora19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:50:41

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
...

[oracle@ora19c ~]$ exit
exit

클라이언트 환경에서 접속

방화벽이 막혀있기 때문에 접속 오류 발생

[root@ora19c ~]# whoami
root
[root@ora19c ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2025-11-25 10:53:31 KST; 3h 57min ago
     Docs: man:firewalld(1)
 Main PID: 868 (firewalld)
    Tasks: 2
   CGroup: /system.slice/firewalld.service
           └─868 /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid

Nov 25 10:53:31 ora19c systemd[1]: Starting firewalld - dynamic firewall da.....
Nov 25 10:53:31 ora19c systemd[1]: Started firewalld - dynamic firewall daemon.
Nov 25 10:53:31 ora19c firewalld[868]: WARNING: AllowZoneDrifting is enable...w.
Hint: Some lines were ellipsized, use -l to show in full.

[root@ora19c ~]# systemctl stop firewalld
[root@ora19c ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@ora19c ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

Nov 25 10:53:31 ora19c systemd[1]: Starting firewalld - dynamic firewall da.....
Nov 25 10:53:31 ora19c systemd[1]: Started firewalld - dynamic firewall daemon.
Nov 25 10:53:31 ora19c firewalld[868]: WARNING: AllowZoneDrifting is enable...w.
Nov 25 14:50:59 ora19c systemd[1]: Stopping firewalld - dynamic firewall da.....
Nov 25 14:51:02 ora19c systemd[1]: Stopped firewalld - dynamic firewall daemon.
Hint: Some lines were ellipsized, use -l to show in full.

-- 인스턴스 정보
SELECT * FROM v$instance;

-- 데이터베이스 정보
SELECT * FROM v$database;

리스너가 중지된 경우

# 리스너 중지
[oracle@ora19c ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:09:17

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
The command completed successfully

# 리스너 상태 조회 -> no listener
[oracle@ora19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:09:35

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

# 리스너 실행
[oracle@ora19c ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:12:15

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2025 15:12:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

# 리스너 상태 조회
[oracle@ora19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:12:18

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2025 15:12:15
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/ora19c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ora19c" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL*Plus 프롬프트에 접속 정보 표시

[oracle@ora19c ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@ora19c admin]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/sqlplus/admin
[oracle@ora19c admin]$ ls
glogin.sql  help  libsqlplus.def  plustrce.sql  pupbld.sql  pupdel.sql
[oracle@ora19c admin]$ vi glogin.sql

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set sqlprompt "_user'@'_connect_identifier> "

[oracle@ora19c admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 25 15:23:05 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@ora19c>

SQL*Plus 프롬프트에서 사용할 수 있는 변수 목록

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/DEFINE.html#GUID-D6727C11-E4D9-4AB5-A7B8-9C6527A44A50

User's Guide and Reference

docs.oracle.com

Variable Name	Contains
_CONNECT_IDENTIFIER	Connection identifier used to make connection, where available.
_DATE	Current date, or a user defined fixed string.
_EDITOR	Specifies the editor used by the EDIT command.
OVERSION	Current version of the installed Oracle Database.
ORELEASE	Full release number of the installed Oracle Database.
_PRIVILEGE	Privilege level of the current connection.
_SQLPLUS_RELEASE	Full release number of installed SQL*Plus component.
_USER	User name used to make connection.
_SQL_ID	sql_id of the SQL statement executed.

Shutdown 상태

리눅스 서버 종료 후 재실행

login as: oracle
oracle@192.168.56.150's password:
Last login: Tue Nov 25 14:43:26 2025 from 192.168.56.1
[oracle@ora19c ~]$ ps -ef | grep oracle
root      2530  1262  0 15:43 ?        00:00:00 sshd: oracle [priv]
oracle    2542  2530  0 15:43 ?        00:00:00 sshd: oracle@pts/0
oracle    2549  2542  0 15:43 pts/0    00:00:00 -bash
oracle    2606  2549  0 15:43 pts/0    00:00:00 ps -ef
oracle    2607  2549  0 15:43 pts/0    00:00:00 grep --color=auto oracle
[oracle@ora19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 25 15:44:19 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance. # idle instance => 데이터베이스가 shutdown 된 상태

SYS@ora19c> exit
Disconnected

# 리스너도 중지됨
[oracle@ora19c ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:46:02

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

리스너 실행

# 리스너 실행
[oracle@ora19c ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-NOV-2025 15:46:46

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-NOV-2025 15:46:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

데이터베이스 실행

[oracle@ora19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 25 15:48:08 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

# 오라클 데이터베이스 시작
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

STARTUP 단계

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-B3A8DB74-211A-453C-8B73-B61824DC56F6

Database Concepts

This chapter explains the nature of an Oracle database instance, the parameter and diagnostic files associated with an instance, and what occurs during instance creation and the opening and closing of a database.

docs.oracle.com

1. nomount

instance 시작 : SGA(System Global Area) 영역, Backgroud Process 생성
1. 서버 파라미터 파일 조회 → 없으면 텍스트 파라미터 파일 조회 → 없으면 오류 발생
2. 초기 파라미터 값을 바탕으로 SGA 영역 할당
3. 오라클 백그라운드 프로세스 실행
4. alert log 및 trace 파일에 기록
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-189ADDB5-1E71-4924-8371-F5B2EFB5B304

Database Concepts

docs.oracle.com

초기 파라미터 파일 열기

위치 : $ORACLE_HOME/dbs
spfile(서버 파라미터 파일, 바이너리 파일) : spfile<SID>.ora

pfile(텍스트 초기 파라미터 파일) : init<SID>.ora

[oracle@ora19c ~]$ cd $ORACLE_HOME/dbs
[oracle@ora19c dbs]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1/dbs
[oracle@ora19c dbs]$ ls
hc_ora19c.dat  init.ora  lkORA19C  orapwora19c  spfileora19c.ora

초기 파라미터 파일이 없을 경우

# spfile 이름 변경
[oracle@ora19c dbs]$ mv spfileora19c.ora spfileora19c.bak
[oracle@ora19c dbs]$ ls
hc_ora19c.dat  init.ora  lkORA19C  orapwora19c  spfileora19c.bak

# 데이터베이스 셧다운
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# 데이터베이스 실행 -> 오류 발생
# spfile 이름 변경으로 인해 데이터베이스 실행 시 필요한 초기 파라미터 조회 불가
SYS@ora19c> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initora19c.ora'

startup 시 어떤 초기 파라미터 파일을 이용했는지 조회

SYS@ora19c> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/dbs/spfileora19c.ora

spfile을 이용해서 pfile 생성

initora19c.ora 파일 생성

SYS@ora19c> create pfile from spfile;

File created.

SYS@ora19c> !
[oracle@ora19c dbs]$ ls
hc_ora19c.dat  initora19c.ora  orapwora19c
init.ora       lkORA19C        spfileora19c.ora

pfile(initora19c.ora)로 데이터베이스 실행

# spfile 이름 변경
[oracle@ora19c dbs]$ mv spfileora19c.ora spfileora19c.bak
[oracle@ora19c dbs]$ ls
hc_ora19c.dat  initora19c.ora  orapwora19c
init.ora       lkORA19C        spfileora19c.bak

# db 접속
[oracle@ora19c dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 25 16:30:26 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

# 데이터베이스 실행
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# startup 시 사용한 초기 파일 조회 -> initora19c.ora로 실행
SYS@ora19c> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

원상복귀

# 데이터베이스 종료
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# OS 프롬프트로 전환
SYS@ora19c> !
[oracle@ora19c dbs]$ ls
hc_ora19c.dat  initora19c.ora  orapwora19c
init.ora       lkORA19C        spfileora19c.bak

# initora19c.ora 삭제
[oracle@ora19c dbs]$ rm -f initora19c.ora

# spfile 이름 변경
[oracle@ora19c dbs]$ mv spfileora19c.bak spfileora19c.ora

[oracle@ora19c dbs]$ ls
hc_ora19c.dat  init.ora  lkORA19C  orapwora19c  spfileora19c.ora

# SQL*Plus 프롬프트로 복귀
[oracle@ora19c dbs]$ exit
exit

# 데이터베이스 실행
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# startup 시 사용한 초기 파일 조회 -> spfileora19c.ora로 실행
SYS@ora19c> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/dbs/spfileora19c.ora

alert log

alert_<SID>.log
위치 : $ORACLE_BASE/diag/rdbms/{DB_NAME}/{INSTANCE_NAME}/trace

# $ORACLE_BASE
[oracle@ora19c ~]$ cd $ORACLE_BASE
[oracle@ora19c oracle]$ pwd
/u01/app/oracle
[oracle@ora19c oracle]$ ls
admin  cfgtoollogs  diag                oradata
audit  checkpoints  fast_recovery_area  product

# diag
[oracle@ora19c oracle]$ cd diag
[oracle@ora19c diag]$ ls
afdboot  asmcmd   clients   dps  ios      netcman  plsqlapp
apx      asmtool  crs       em   kfod     ofm      rdbms
asm      bdsql    diagtool  gsm  lsnrctl  plsql    tnslsnr

# rdbms
[oracle@ora19c diag]$ cd rdbms
[oracle@ora19c rdbms]$ ls
ora19c

# ora19c
[oracle@ora19c rdbms]$ cd ora19c
[oracle@ora19c ora19c]$ ls
i_1.mif  ora19c

# ora19c
[oracle@ora19c ora19c]$ cd ora19c
[oracle@ora19c ora19c]$ ls
alert  hm        incpkg  lck  metadata       metadata_pv  sweep
cdump  incident  ir      log  metadata_dgif  stage        trace

# trace
[oracle@ora19c ora19c]$ cd trace
[oracle@ora19c trace]$ pwd
/u01/app/oracle/diag/rdbms/ora19c/ora19c/trace
[oracle@ora19c trace]$ ls
alert_ora19c.log   ...

# alert_ora19c.log
[oracle@ora19c trace]$ vi alert_ora19c.log

실시간으로 alert log 보기

[oracle@ora19c trace]$ tail -F alert_ora19c.log
29294753,29299830,29307090,29307109,29311336,29329675,29330791,29339299,
29357821,29360467,29360775,29367971,29368725,29379299,29379381,29380527,
29381000,29382296,29391301,29393649,29402110,29411931,29413360,29457319,
29465047
===========================================================
2025-11-25T16:35:57.607331+09:00
db_recovery_file_dest_size of 8256 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.

트레이스 파일 제거

[oracle@ora19c trace]$ rm -f *.{trc,trm}
[oracle@ora19c trace]$ ls
alert_ora19c.log

alert log 파일은 보통 연 단위로 관리
로그 파일이 너무 커지면 백업하고 삭제 작업

2. mount

3. open

SHUTDOWN 단계

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-ADFF9AAC-EDA9-4F3C-8AD7-156BB620E812

Database Concepts

docs.oracle.com

SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

close
dismount
shutdown

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251126 TIL (0)	2025.11.26
251121 TIL (0)	2025.11.21
251120 TIL (0)	2025.11.20
251119 TIL (0)	2025.11.19
251118 TIL (0)	2025.11.18

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

오라클 19C 설치

가상 머신 생성

리눅스 설정

접속

hosts 설정

preinstall 패키지를 이용한 오라클 환경 설정

패스워드 설정

오라클 설치 디렉터리 생성 및 권한 설정

오라클 유저 환경 설정

오라클 설치 파일 전송

오라클 설치 파일 압축 풀기

오라클 설치

Listener 설정

DB 생성

ORACLE 백그라운드 프로세스 조회

리스너 상태 조회

DB 접속

SQL*Plus 접속 상태에서 리눅스 명령어 사용법

클라이언트 환경에서 접속

리스너가 중지된 경우

SQL*Plus 프롬프트에 접속 정보 표시

SQL*Plus 프롬프트에서 사용할 수 있는 변수 목록

Shutdown 상태

리스너 실행

데이터베이스 실행

STARTUP 단계

1. nomount

초기 파라미터 파일 열기

초기 파라미터 파일이 없을 경우

startup 시 어떤 초기 파라미터 파일을 이용했는지 조회

spfile을 이용해서 pfile 생성

alert log

실시간으로 alert log 보기

트레이스 파일 제거

2. mount

3. open

SHUTDOWN 단계

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바