[제목]
[2025-10-03] VMware Workstation 환경에서 Oracle Linux 8.10 기반하에 Oracle 19c 2 Node RAC 설치
[테스트 개요]
가상 머신 :
VMware Workstation
가상 머신 디스크 : NVMe
Pulic IP Network 방식 : NAT
Private
IP Network 방식 : Host-Only
OS : Oracle Linux 8.10
Oracle Version : Oracle
19c RU28
공유 디스크 : udev
[테스트 환경]
--> 설치 완료 후 정보 확인
OS : Oracle
Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d
'"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname -r)
Oracle
Version : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
(echo -e "set pages 0 lines 200 feedback off heading off\nselect banner from
v\$version where banner like 'Oracle Database%';" | sqlplus -s / as
sysdba)
Oracle Configuration
DB명 : ORA19RF (echo -e
'set pages 0 feedback off heading off verify off\nselect '\''DB명 : '\''||name
from v$database;' | sqlplus -s / as sysdba) '
RAC Node
1
Hostname : ol8ora19rf1
(hostname)
Public IP : 192.168.240.31 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RF1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname : ol8ora19rf2
(hostname)
Public IP : 192.168.240.32 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RF2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0
(38124772)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37962938;ACFS RELEASE UPDATE 19.28.0.0.0
(37962938)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0
(36758186)
Oracle (opatch lspatches) (oracle os
user)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
[내용]
1. VM 생성
1-1. VM 생성 준비
--필요한 디렉토리
생성
D:\VMware\OL8_ORA19RF\
D:\VMware\OL8_ORA19RF\ol8ora19rf1
D:\VMware\OL8_ORA19RF\ol8ora19rf2
D:\VMware\OL8_ORA19RF\Storage
--해당 RAC 시스템이 사용할 HOST-ONLY
NETWORK 추가
상단 메뉴 Edit -> Virual Network Editor
-> Change Settings -> Add Network -> Select a network to add :
VMnet3
Subnet IP : 10.0.3.0, Subnet mask :
255.255.255.0
Use local DHCP service to disribute IP address to VMs :
체크해제
-> Apply -> OK
1-2. VM 생성
상단 메뉴 File
-> New Virtual Machine -> Custom -> Next
Hardware compatibility :
Workstation 17.5 or later -> Next
Installer disk image file (iso) : OracleLinux-R8-U10-x86_64-dvd.iso -> Next
Virtual
machine name : ol8ora19rf1
Location : D:\VMware\OL8_ORA19RF\ol8ora19rf1 ->
Next
Number of
processors : 1
Number of cores per processor : 8 -> Next
Virtualize
Intel VT-x/EPT or AMD-V/RVI : 체크
Memory for
this virtual machine : 16384 MB -> Next
NAT -> Next
LSI Logic ->
Next
NVMe -> Next
Create a new virtual disk ->
Next
Maximum
disk size (GB) : 128
Allocate all disk space now : 체크
Store virtual disk
as a single file : 선택 -> Next
Disk file : D:\VMware\OL8_ORA19RF\ol8ora19rf1\ol8ora19rf1.vmdk -> Next
Customize
Hardware 클릭 -> Add -> Network Adapter -> Finish
생성되면 선택 후
Custom
: Specific virtual network -> VMnet3 (host-only)
Sound Card
선택 후 -> Connect at power on 체크해제
Close
Finish
2. 생성된
VM에 Oracle Linux 8.10 설치
부팅 시
Install Oracle Linux 8.10.0 선택
English -> English (United States) ->
continue
Keyboard Layout -> Korean 추가
Language Support -> 한국어
추가
Time & Date -> Asia/Seoul
Software Selection -> Minimal
Install -> Standard
KDUMP : 비활성화
Installation Destination -> Manual
Partitioning -> 추가 -> /boot (500MB), swap (16384MB), / (나머지용량)
Security
Policy : OFF
Network & Host Name
Hostname :
ol8ora19rf1
ens160 설정 (어댑터1 : Bridged)
ON
General :
Connect Automatically With Priority
IPv4 Setting
Method : Manual
Address : 192.168.240.31
NetMask
: 255.255.255.0
Gateway : 192.168.240.2
DNS :
127.0.0.1,8.8.8.8
IPv6 Setting
Method :
Ignore
ens224 설정 (어댑터2 : host-only)
ON
General :
Connect Automatically With Priority
IPv4 Setting
Method : Manual
Address : 10.0.3.31
NetMask :
255.255.255.0
Gateway : -
DNS : -
IPv6
Setting
Method : Ignore
Root Password : 1234
CREATE USER
-- optional
Make This user Administrator
full name :
admin
password : 1234
Begin Installation
192.168.240.31:22 로 접속 root/1234
┌──────────────────────────────────────────────────────────────────────┐
│
? MobaXterm Professional Edition v22.2
?
│
│
(SSH client, X server and network
tools)
│
│
│
│ ? SSH session to root@192.168.240.31
│
│ ? Direct SSH
:
?
│
│ ? SSH compression :
?
│
│ ? SSH-browser
:
?
│
│ ? X11-forwarding : ?
(disabled or not supported by server)
│
│
│
│ ? For more info, ctrl+click on help or visit our
website.
│
└──────────────────────────────────────────────────────────────────────┘
Activate the web console with: systemctl enable --now cockpit.socket
[root@ol8ora19rf1 ~]# poweroff
--여기서 VM 백업
3. OS
설정
<반드시 필요한
유틸, 없으면 설치하고 넘어가야함>
ifconfig (dnf -y install
net-tools)
wget (dnf -y install wget)
lsof (dnf -y install lsof)
vim
(dnf -y install vim)
unzip (dnf -y install unzip)
traceroute (dnf -y
install traceroute)
rlwrap (dnf -y install epel-release rlwrap)
nmcli (dnf
-y install NetworkManager)
3-1. OS 설치 후 정보 확인
[root@ol8ora19rf1 ~]# cat
/etc/oracle-release
Oracle Linux Server release
8.10
[root@ol8ora19rf1 ~]# uname -r
5.15.0-206.153.7.1.el8uek.x86_64
--UEK R6 = 리눅스 5.4 기반(OL7/OL8에서 제공).
--UEK R7 = 리눅스 5.15
기반(OL8/OL9에서 제공).
--> 해당 버전은 --> 해당 버전은 5.15.0 이므로 UEK R7 버전임을 알 수
있음
3-2. root 사용자의 .bash_profile 설정
[root@ol8ora19rf1 ~]# vi ~root/.bash_profile
[root@ol8ora19rf1 ~]# cat
>> ~root/.bash_profile << 'EOF'
set -o vi
stty erase
^H
stty erase ^?
export PS1='[\u@\h][$PWD]$ '
export
ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/19c/grid
export
DB_HOME=$ORACLE_BASE/product/19c/db_1
export
BASE_PATH=/usr/sbin:$PATH
export PATH=$GRID_HOME/bin:$BASE_PATH
alias
csrt='crsctl stat res -t'
EOF
--root user로 ssh
재접속
[root@ol8ora19rf1][/root]$
3-3. 인터넷 연결 확인
[root@ol8ora19rf1][/root]$ ping 8.8.8.8
PING 8.8.8.8
(8.8.8.8) 56(84) bytes of data.
64 bytes from 8.8.8.8:
icmp_seq=1 ttl=128 time=37.0 ms
64 bytes from 8.8.8.8:
icmp_seq=2 ttl=128 time=38.2 ms
64 bytes from 8.8.8.8:
icmp_seq=3 ttl=128 time=37.4 ms
3-4. ifconfig 정보 확인
[root@ol8ora19rf1][/root]$ ifconfig
ens160:
flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet
192.168.240.31 netmask 255.255.255.0 broadcast 192.168.240.255
inet6
fe80::20c:29ff:feb6:b1eb prefixlen 64 scopeid 0x20<link>
ether
00:0c:29:b6:b1:eb txqueuelen 1000 (Ethernet)
RX packets
870 bytes 70022 (68.3 KiB)
RX errors
0 dropped 0 overruns 0 frame 0
TX packets
1524 bytes 186886 (182.5 KiB)
TX errors
0 dropped 0 overruns 0 carrier 0 collisions
0
ens224:
flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet
10.0.3.31 netmask 255.255.255.0 broadcast 10.0.3.255
inet6
fe80::20c:29ff:feb6:b1f5 prefixlen 64 scopeid 0x20<link>
ether
00:0c:29:b6:b1:f5 txqueuelen 1000 (Ethernet)
RX packets
0 bytes 0 (0.0 B)
RX errors
0 dropped 0 overruns 0 frame 0
TX packets
14 bytes 992 (992.0 B)
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
68 bytes 5500 (5.3 KiB)
RX errors
0 dropped 0 overruns 0 frame 0
TX packets
68 bytes 5500 (5.3 KiB)
TX errors
0 dropped 0 overruns 0 carrier 0 collisions
0
3-5. traceroute 설치
[root@ol8ora19rf1][/root]$ dnf -y install traceroute
[root@ol8ora19rf1][/root]$ traceroute
google.com
traceroute to google.com (142.250.206.206), 30 hops max, 60
byte packets
1 _gateway
(192.168.240.2) 0.452 ms 0.400 ms 0.321 ms
2 * * *
3 *
* *
4 * * *
5 * * *
6 *
* *
7 * * *
8 * * *
9 *
* *
10 * * *
...생략
3-6. /etc/hosts 설정
[root@ol8ora19rf1][/root]$ cat >> /etc/hosts <<
'EOF'
### Public IP
192.168.240.31 ol8ora19rf1
192.168.240.32 ol8ora19rf2
### Private IP
10.0.3.31 ol8ora19rf1-priv
10.0.3.32 ol8ora19rf2-priv
### Virtual IP
192.168.240.34 ol8ora19rf1-vip
192.168.240.35 ol8ora19rf2-vip
### DNS
192.168.240.37 OL8ORA19RF-scan
192.168.240.38 OL8ORA19RF-scan
192.168.240.39 OL8ORA19RF-scan
EOF
[root@ol8ora19rf1][/root]$ cat
/etc/hosts
127.0.0.1 localhost localhost.localdomain
localhost4 localhost4.localdomain4
::1
localhost localhost.localdomain localhost6 localhost6.localdomain6
### Public IP
192.168.240.31
ol8ora19rf1
192.168.240.32
ol8ora19rf2
### Private IP
10.0.3.31 ol8ora19rf1-priv
10.0.3.32 ol8ora19rf2-priv
### Virtual IP
192.168.240.34 ol8ora19rf1-vip
192.168.240.35 ol8ora19rf2-vip
### DNS
192.168.240.37 ol8ora19rf-scan
192.168.240.38 ol8ora19rf-scan
192.168.240.39 ol8ora19rf-scan
3-7. rlwrap 설치
[root@ol88ca1][/root]$ dnf -y install epel-release
[root@ol88ca1][/root]$ dnf -y install rlwrap
[root@ol8ora19rf1][/root]$ rlwrap -v
rlwrap 0.46.2
3-8. selinux 설정
[root@ol8ora19rf1][/root]$ vi /etc/selinux/config
[root@ol8ora19rf1][/root]$ cat
/etc/selinux/config
# This file
controls the state of SELinux on the system.
# SELINUX=
can take one of these three values:
# enforcing - SELinux security
policy is enforced.
# permissive
- SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is
loaded.
#SELINUX=enforcing
SELINUX=permissive
#
SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are
protected,
# minimum -
Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security
protection.
SELINUXTYPE=targeted
[root@ol8ora19rf1][/root]$ setenforce permissive
3-9. fstab 설정
[root@ol8ora19rf1][/root]$ vi /etc/fstab
[root@ol8ora19rf1][/root]$ cat >> /etc/fstab <<
'EOF'
tmpfs
/dev/shm
tmpfs size=16g 00
EOF
[root@ol8ora19rf1][/root]$ mount -o remount /dev/shm
[root@ol8ora19rf1][/root]$ systemctl daemon-reload
[root@ol8ora19rf1][/root]$ df -h
Filesystem
Size Used Avail Use% Mounted on
devtmpfs
7.8G 0 7.8G 0% /dev
tmpfs
16G 0 16G 0% /dev/shm
tmpfs
7.9G 8.8M 7.8G 1% /run
tmpfs
7.9G 0 7.9G 0% /sys/fs/cgroup
/dev/mapper/ol-root 112G 4.0G
108G 4% /
/dev/NVMe0n1p1
495M 254M 241M 52% /boot
tmpfs
1.6G 0 1.6G 0%
/run/user/0
3-10. 필수 패키지 검증 작업
--Oracle Linux 8 버전에서 Oracle 19c 설치 시 필수 패키지 목록
[root@ol8ora19rf1][/root]$ cat > ~root/check_pakage.sh
<< 'EOF'
rpm -q bc
rpm -q
binutils
rpm -q elfutils-libelf
rpm -q elfutils-libelf-devel
rpm -q
fontconfig-devel
rpm -q glibc
rpm -q glibc-devel
rpm -q ksh
rpm -q libaio
rpm -q
libaio-devel
rpm -q libXrender
rpm -q libX11
rpm -q libXau
rpm -q libXi
rpm -q libXtst
rpm -q libgcc
rpm -q libnsl
rpm -q librdmacm
rpm -q
libstdc++
rpm -q libstdc++-devel
rpm -q libxcb
rpm -q libibverbs
rpm -q make
rpm -q
policycoreutils
rpm -q policycoreutils-python-utils
rpm -q smartmontools
rpm -q
sysstat
EOF
[root@ol8ora19rf1][/root]$ chmod 700
~root/check_pakage.sh
[root@ol8ora19rf1][/root]$ sh
~root/check_pakage.sh | grep "not installed"
package binutils
is not installed
package elfutils-libelf-devel is not
installed
package fontconfig-devel is not installed
package glibc-devel is not installed
package ksh is not installed
package libaio-devel is not installed
package libXi is not installed
package libXtst is not installed
package libnsl is not installed
package librdmacm is not installed
package libstdc++-devel is not installed
package make is not installed
package sysstat is not installed
[root@ol8ora19rf1][/root]$ dnf -y install \
binutils \
elfutils-libelf-devel
\
fontconfig-devel \
glibc-devel
\
ksh \
libaio-devel \
libXi \
libXtst \
libnsl \
librdmacm \
libstdc++-devel \
make \
sysstat
[root@ol8ora19rf1][/root]$ sh ~root/check_pakage.sh | grep "not installed"
3-11. Oracle Preinstall 설치
--파일 저장 위치 :
https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/
[root@ol8ora19rf1][/root]$ wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ rpm -ivh
oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
error: Failed dependencies:
nfs-utils is
needed by oracle-database-preinstall-19c-1.0-2.el8.x86_64
xorg-x11-utils
is needed by oracle-database-preinstall-19c-1.0-2.el8.x86_64
xorg-x11-xauth
is needed by oracle-database-preinstall-19c-1.0-2.el8.x86_64
--> 필요한
패키지가 없다고 나옴
[root@ol8ora19rf1][/root]$ dnf install -y nfs-utils \
xorg-x11-utils \
xorg-x11-xauth
[root@ol8ora19rf1][/root]$ rpm -ivh
oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm
Verifying...
################################# [100%]
Preparing...
################################# [100%]
Updating /
installing...
1:oracle-database-preinstall-19c-1.#################################
[100%]
cd
/var/log/oracle-database-preinstall-19c/backup/
--> 해당 위치에서 Preinstall 작업 로그를 확인 가능함
3-12. /etc/group 확인
[root@ol8ora19rf1][/root]$ cat /etc/group | grep
oracle
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
3-13. limits.d 설정
[root@ol8ora19rf1][/root]$ cat /etc/security/limits.d/oracle-database-preinstall-19c.conf
#
oracle-database-preinstall-19c setting for nofile soft limit is 1024
oracle soft
nofile 1024
#
oracle-database-preinstall-19c setting for nofile hard limit is 65536
oracle hard
nofile 65536
#
oracle-database-preinstall-19c setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oracle soft nproc
16384
#
oracle-database-preinstall-19c setting for nproc hard limit is 16384
oracle hard nproc
16384
#
oracle-database-preinstall-19c setting for stack soft limit is 10240KB
oracle soft stack
10240
#
oracle-database-preinstall-19c setting for stack hard limit is 32768KB
oracle hard stack
32768
#
oracle-database-preinstall-19c setting for memlock hard limit is maximum of
128GB on x86_64 or 3GB on x86 OR 90 % of RAM
oracle hard
memlock 134217728
#
oracle-database-preinstall-19c setting for memlock soft limit is maximum of
128GB on x86_64 or 3GB on x86 OR 90% of RAM
oracle soft
memlock 134217728
#
oracle-database-preinstall-19c setting for data soft limit is 'unlimited'
oracle soft data
unlimited
#
oracle-database-preinstall-19c setting for data hard limit is 'unlimited'
oracle hard data
unlimited
--> memlock 값은 KB 단위임
oracle 계정의 ulimit
설정 파일 :
/etc/security/limits.d/oracle-database-preinstall-19c.conf
-> pam_limits가 해당 파일을 인식하여 oracle 계정의 ulimit 설정을
해줌
[root@ol8ora19rf1][/root]$ vi
/etc/security/limits.d/oracle-database-preinstall-19c.conf
[root@ol8ora19rf1][/root]$ cat
/etc/security/limits.d/oracle-database-preinstall-19c.conf
#
oracle-database-preinstall-19c setting for nofile soft limit is 1024
oracle soft
nofile 1024
#
oracle-database-preinstall-19c setting for nofile hard limit is 65536
oracle hard
nofile 65536
#
oracle-database-preinstall-19c setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oracle soft nproc
16384
#
oracle-database-preinstall-19c setting for nproc hard limit is 16384
oracle hard nproc
16384
#
oracle-database-preinstall-19c setting for stack soft limit is 10240KB
oracle soft stack
10240
#
oracle-database-preinstall-19c setting for stack hard limit is 32768KB
oracle hard stack
32768
# oracle-database-preinstall-19c setting for memlock hard
limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM
#oracle hard
memlock 134217728
oracle
hard memlock 15099494
--> KB
단위임 물리메모리가 16기가인 경우 -> (1*1024*1024*1024*16)/1024*0.9 =
15099494.4
# oracle-database-preinstall-19c setting for memlock soft
limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90% of RAM
#oracle soft
memlock 134217728
oracle
soft memlock 15099494
--> KB
단위임 물리메모리가 16기가인 경우 -> (1*1024*1024*1024*16)/1024*0.9 =
15099494.4
#
oracle-database-preinstall-19c setting for data soft limit is 'unlimited'
oracle soft data
unlimited
#
oracle-database-preinstall-19c setting for data hard limit is 'unlimited'
oracle hard data
unlimited
-> memlock 설정은 huge page
설정을 위해 물리 메모리의 90%로 함
-> memlock을 물리메모리의 90%로 설정해줌
(15099494 KB임, KB단위임)
3-14. /etc/sysctl.conf설정
[root@ol8ora19rf1][/root]$ getconf
PAGESIZE
4096
--> 해당
OS의 페이지 단위는 4096 바이트임
[root@ol8ora19rf1][/root]$ vi /etc/sysctl.conf
[root@ol8ora19rf1][/root]$ cat /etc/sysctl.conf
# sysctl settings
are defined through files in
# /usr/lib/sysctl.d/,
/run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same
in
# /etc/sysctl.d/ and put new settings there. To
override
# only specific settings, add a file with a
lexically later
# name in /etc/sysctl.d/ and put new
settings there.
#
# For more
information, see sysctl.conf(5) and sysctl.d(5).
#
oracle-database-preinstall-19c setting for fs.file-max is 6815744
fs.file-max = 6815744
#
oracle-database-preinstall-19c setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128
#
oracle-database-preinstall-19c setting for kernel.shmmni is 4096
kernel.shmmni = 4096
# oracle-database-preinstall-19c setting for kernel.shmall
is 1073741824 on x86_64
#kernel.shmall =
1073741824
kernel.shmall = 2097152
--> 페이지 단위임 (getconf PAGESIZE) ->
1*1024*1024*1024*16/4096/2 = 2097152 (물리메모리의
50%)
# oracle-database-preinstall-19c setting for kernel.shmmax
is 4398046511104 on x86_64
#kernel.shmmax =
4398046511104
kernel.shmmax = 8589934592
--> 바이트 단위임 (getconf PAGESIZE) ->
1*1024*1024*1024*16/2 = 8589934592 (물리메모리의
50%)
#
oracle-database-preinstall-19c setting for kernel.panic_on_oops is 1 per Orabug
19212317
kernel.panic_on_oops = 1
#
oracle-database-preinstall-19c setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
#
oracle-database-preinstall-19c setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
#
oracle-database-preinstall-19c setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
#
oracle-database-preinstall-19c setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
#
oracle-database-preinstall-19c setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2
#
oracle-database-preinstall-19c setting for net.ipv4.conf.default.rp_filter is
2
net.ipv4.conf.default.rp_filter =
2
#
oracle-database-preinstall-19c setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
#
oracle-database-preinstall-19c setting for net.ipv4.ip_local_port_range is 9000
65500
net.ipv4.ip_local_port_range = 9000
65500
# Added by
KOLEE
kernel.pid_max = 10239
vm.min_free_kbytes = 524288
vm.swappiness=1
kernel.randomize_va_space = 0
kernel.sysrq = 1
[root@ol8ora19rf1][/root]$ sysctl -p
fs.file-max =
6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall
= 2097152
kernel.shmmax = 8589934592
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
kernel.pid_max = 10239
vm.min_free_kbytes = 524288
vm.swappiness = 1
kernel.randomize_va_space = 0
kernel.sysrq = 1
3-15. 각종 불필요한 프로세스 중지 및 비활성화
[root@ol8ora19rf1][/root]$ systemctl stop firewalld
[root@ol8ora19rf1][/root]$ 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@ol8ora19rf1][/root]$ systemctl status
firewalld
● firewalld.service - firewalld - dynamic firewall
daemon
Loaded: loaded
(/usr/lib/systemd/system/firewalld.service; disabled; vendor >
Active: inactive (dead)
Docs:
man:firewalld(1)
Sep 23 11:09:05
nnnnnnnn systemd[1]: Starting firewalld - dynamic firewall daem>
Sep 23 11:09:05 nnnnnnnn systemd[1]: Started firewalld -
dynamic firewall daemo>
Sep 23 11:09:05 nnnnnnnn
firewalld[915]: WARNING: AllowZoneDrifting is enabled.>
Sep 23 14:11:21 ol8ora19rf1 systemd[1]: Stopping firewalld
- dynamic firewall d>
Sep 23 14:11:22 ol8ora19rf1
systemd[1]: firewalld.service: Succeeded.
Sep 23
14:11:22 ol8ora19rf1 systemd[1]: Stopped firewalld - dynamic firewall
da>
[root@ol8ora19rf1][/root]$ systemctl stop
bluetooth
Failed to stop bluetooth.service: Unit bluetooth.service
not loaded.
[root@ol8ora19rf1][/root]$ systemctl stop
ntpdate
Failed to stop ntpdate.service: Unit ntpdate.service not
loaded.
[root@ol8ora19rf1][/root]$ systemctl stop
avahi-daemon.socket
Failed to stop avahi-daemon.socket: Unit
avahi-daemon.socket not loaded.
[root@ol8ora19rf1][/root]$ systemctl stop
avahi-daemon
Failed to stop avahi-daemon.service: Unit
avahi-daemon.service not loaded.
[root@ol8ora19rf1][/root]$ systemctl stop
libvirtd
Failed to stop libvirtd.service: Unit libvirtd.service not
loaded.
[root@ol8ora19rf1][/root]$ virsh net-destroy
default
-bash: virsh: command not found
3-16. chrony 설치
[root@ol8ora19rf1][/root]$ dnf -y install chrony
[root@ol8ora19rf1][/root]$ vi /etc/chrony.conf
[root@ol8ora19rf1][/root]$ cat /etc/chrony.conf
# Use public
servers from the pool.ntp.org project.
# Please consider
joining the pool (http://www.pool.ntp.org/join.html).
#pool
2.pool.ntp.org iburst
#added
server 0.kr.pool.ntp.org iburst
server 1.asia.pool.ntp.org iburst
server 2.asia.pool.ntp.org iburst
# Record the rate
at which the system clock gains/losses time.
driftfile
/var/lib/chrony/drift
# Allow the system
clock to be stepped in the first three updates
# if its
offset is larger than 1 second.
makestep 1.0
3
# Enable kernel
synchronization of the real-time clock (RTC).
rtcsync
# Enable hardware
timestamping on all interfaces that support it.
#hwtimestamp *
# Increase the
minimum number of selectable sources required to adjust
# the system clock.
#minsources
2
# Allow NTP client
access from local network.
#allow
192.168.0.0/16
# Serve time even
if not synchronized to a time source.
#local stratum
10
# Specify file
containing keys for NTP authentication.
keyfile
/etc/chrony.keys
# Get TAI-UTC
offset and leap seconds from the system tz database.
leapsectz right/UTC
# Specify
directory for log files.
logdir
/var/log/chrony
# Select which
information is logged.
#log measurements statistics
tracking
[root@ol8ora19rf1][/root]$ systemctl start chronyd
[root@ol8ora19rf1][/root]$ systemctl enable chronyd
[root@ol8ora19rf1][/root]$ chronyc tracking
Reference
ID : 00000000 ()
Stratum :
0
Ref time (UTC) : Thu Jan 01 00:00:00 1970
System time : 0.000000000 seconds
fast of NTP time
Last offset :
+0.000000000 seconds
RMS
offset : 0.000000000 seconds
Frequency : 0.000 ppm
slow
Residual freq : +0.000 ppm
Skew
: 0.000 ppm
Root delay :
1.000000000 seconds
Root dispersion : 1.000000000
seconds
Update interval : 0.0 seconds
Leap status : Not
synchronised
[root@ol8ora19rf1][/root]$ chronyc sources
-v
.-- Source
mode '^' = server, '=' = peer, '#' = local clock.
/ .- Source state '*' = current best, '+' = combined,
'-' = not combined,
|
/ 'x' =
may be in error, '~' = too variable, '?' = unusable.
||
.- xxxx [ yyyy ] +/- zzzz
|| Reachability register
(octal) -. |
xxxx = adjusted offset,
||
Log2(Polling interval) --.
| | yyyy = measured
offset,
||
\
| | zzzz = estimated
error.
||
|
| \
MS Name/IP
address Stratum Poll Reach
LastRx Last sample
===============================================================================
^+
203.32.26.46
2 6 7
1 +57ms[ +44ms] +/- 130ms
^+
103.186.118.217
2 6 7 0
-9645us[ -23ms] +/- 34ms
^*
103.186.118.215
2 6 7
0 -21ms[ -34ms] +/- 43ms
3-17. dnsmasq 설치 및 설정
[root@ol8ora19rf1][/root]$ vi /etc/resolv.conf
[root@ol8ora19rf1][/root]$ cat /etc/resolv.conf
# Generated by NetworkManager
nameserver 127.0.0.1
nameserver
8.8.8.8
--> 기재 (위/아래) 순서를 주의 할 것 (127.0.0.1이 위로
올라와야함)
[root@ol8ora19rf1][/root]$ dnf -y install dnsmasq
[root@ol8ora19rf1][/root]$ vi
/etc/dnsmasq.conf
--> "local=" 로 검색한 후 해당 부분 추가
[root@ol8ora19rf1][/root]$ cat /etc/dnsmasq.conf | grep
localdomain
local=/localdomain/
[root@ol8ora19rf1][/root]$ systemctl start dnsmasq
[root@ol8ora19rf1][/root]$ systemctl enable dnsmasq
Created symlink
/etc/systemd/system/multi-user.target.wants/dnsmasq.service →
/usr/lib/systemd/system/dnsmasq.service.
[root@ol8ora19rf1][/root]$ nslookup
ol8ora19rf-scan
Server:
127.0.0.1
Address:
127.0.0.1#53
Name: OL8ORA19RF-scan
Address: 192.168.240.29
Name: OL8ORA19RF-scan
Address: 192.168.240.27
Name: OL8ORA19RF-scan
Address: 192.168.240.28
--> 제대로
출력이 안될 경우 reboot 후 재시도 해볼 것
3-18. 사용자 그룹 및 사용자 설정
[root@ol8ora19rf1][/root]$ cat /etc/group | grep -i
oracle
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@ol8ora19rf1][/root]$ groupadd asmadmin
[root@ol8ora19rf1][/root]$ groupadd asmdba
[root@ol8ora19rf1][/root]$ groupadd asmoper
[root@ol8ora19rf1][/root]$ useradd -g oinstall -G
dba,asmadmin,asmdba,asmoper grid
[root@ol8ora19rf1][/root]$ usermod -g oinstall -G
dba,oper,backupdba,dgdba,kmdba,racdba,asmdba oracle
[root@ol8ora19rf1][/root]$ id oracle
uid=54321(oracle) gid=54321(oinstall)
groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54332(asmdba)
[root@ol8ora19rf1][/root]$ id grid
uid=54322(grid)
gid=54321(oinstall)
groups=54321(oinstall),54322(dba),54331(asmadmin),54332(asmdba),54333(asmoper)
[root@ol8ora19rf1][/root]$ passwd grid
Changing password
for user grid.
New password:
BAD
PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all
authentication tokens updated successfully.
[root@ol8ora19rf1][/root]$ 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.
3-19. 설치 디렉토리 생성
[root@ol8ora19rf1][/root]$ mkdir -p /u01/app/19c/grid
[root@ol8ora19rf1][/root]$ mkdir -p
/u01/app/oracle/product/19c/db_1
[root@ol8ora19rf1][/root]$ chown -R grid:oinstall /u01
[root@ol8ora19rf1][/root]$ chown -R oracle:oinstall
/u01/app/oracle/product
[root@ol8ora19rf1][/root]$ chmod
-R 775 /u01
3-20. grid user를 위한 limit 조건 추가
--oracle 유저의 설정을 조회한 후 grid
유저로 바꿔서 설정하기
[root@ol8ora19rf1][/root]$ awk 'NF && $1 !~ /^#/'
/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 15099494
oracle
soft memlock 15099494
oracle soft data
unlimited
oracle hard
data unlimited
--> 여기서 oracle만 grid로 바꿔서 텍스트로 가지고 있어야함
[root@ol8ora19rf1][/root]$ cat >>
/etc/security/limits.conf << 'EOF'
grid soft nofile
1024
grid hard
nofile 65536
grid
soft nproc 16384
grid hard nproc
16384
grid soft
stack 10240
grid
hard stack 32768
grid hard memlock
15099494
grid soft
memlock 15099494
grid
soft data unlimited
grid hard data
unlimited
EOF
[root@ol8ora19rf1][/root]$ cat /etc/security/limits.conf |
grep grid
grid soft nofile
1024
grid hard
nofile 65536
grid
soft nproc 16384
grid hard nproc
16384
grid soft
stack 10240
grid
hard stack 32768
grid hard memlock
15099494
grid soft
memlock 15099494
grid
soft data unlimited
grid hard data
unlimited
3-21. root, grid, oracle 사용자의 bash 설정
[root@ol8ora19rf1][/root]$ vi ~grid/.bash_profile
[root@ol8ora19rf1][/root]$ cat >> ~grid/.bash_profile
<< 'EOF'
export TMP=/tmp
export TMPDIR=$TMP
export
EDITOR=vi
export ORACLE_HOSTNAME=`hostname`
export ORACLE_UNQNAME=+ASM
export
ORACLE_UNQNAME_LOWER=+asm
export
ORACLE_BASE=/u01/app/oracle
export
GRID_HOME=/u01/app/19c/grid
export
DB_HOME=$ORACLE_BASE/product/19c/db_1
export
ORACLE_HOME=$GRID_HOME
export ORACLE_SID=+ASM1
export ORACLE_SID_LOWER=+asm1
export ORACLE_USER_ORACLE_SID=ORA19RF1
export ORACLE_USER_ORACLE_SID_LOWEWR=ora19rf1
export ORACLE_TERM=xterm
export
BASE_PATH=/usr/sbin:$PATH
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_KOREA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'
export PS1='[$ORACLE_SID:\u@\h][$PWD]$ '
alias ss='rlwrap sqlplus "/as sysdba"'
alias sa='rlwrap sqlplus "/as sysasm"'
alias cdt='cd $ORACLE_HOME/network/admin'
alias cdb='cd $ORACLE_BASE'
alias
cdo='cd $ORACLE_HOME'
alias cdod='cd
$ORACLE_HOME/dbs'
alias alog='tail -f
$ORACLE_BASE/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias valog='vi -R
$ORACLE_BASE/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias llog='tail -f
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
alias vllog='vi -R
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
alias crslog='tail -f
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/alert.log'
alias vcrslog='vi -R
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/alert.log'
alias crsdlog='tail -f
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/crsd.trc'
alias vcrsdlog='vi -R
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/crsd.trc'
alias ocssdlog='tail -f
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/ocssd.trc'
alias vocssdlog='vi -R
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/ocssd.trc'
alias ohasdlog='tail -f
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/ohasd.trc'
alias vohasdlog='vi -R
$ORACLE_BASE/diag/crs/`hostname`/crs/trace/ohasd.trc'
#alias cdord='cd $ORACLE_BASE/oradata/$ORACLE_SID' #for
FileSystem
alias cdoss='cd /home/grid/os_scripts'
alias csrt='crsctl stat res -t'
set
-o vi
stty erase ^H
stty erase
^?
EOF
[root@ol8ora19rf1][/root]$ cat >>
~oracle/.bash_profile << 'EOF'
export TMP=/tmp
export TMPDIR=$TMP
export
EDITOR=vi
export ORACLE_HOSTNAME=`hostname`
export ORACLE_UNQNAME=ORA19RF
export ORACLE_UNQNAME_LOWER=ORA19RF
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/19c/grid
export DB_HOME=$ORACLE_BASE/product/19c/db_1
export ORACLE_HOME=$DB_HOME
export
ORACLE_DBNAME=ORA19RF
export
ORACLE_DBNAME_LOWER=ora19rf
export
ORACLE_SID=ORA19RF1
export ORACLE_SID_LOWER=ora19rf1
export ORACLE_TERM=xterm
export
BASE_PATH=/usr/sbin:$PATH
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_KOREA.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export PS1='[$ORACLE_SID:\u@\h][$PWD]$ '
alias ss='rlwrap sqlplus "/as sysdba"'
alias st='rlwrap sqlplus tuner/oracle'
alias cdt='cd $ORACLE_HOME/network/admin'
alias cdb='cd $ORACLE_BASE'
alias
cdo='cd $ORACLE_HOME'
alias cdod='cd
$ORACLE_HOME/dbs'
alias alog='tail -f
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias valog='vi -R
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
alias llog='tail -f
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
alias vllog='vi -R
$ORACLE_BASE/diag/tnslsnr/`hostname`/listener/trace/listener.log'
#alias cdord='cd $ORACLE_BASE/oradata/$ORACLE_SID' #for
FileSystem
alias csrt='crsctl stat res -t'
alias rt='rman target /'
alias
cdrb='cd /home/oracle/rman_backup/$ORACLE_DBNAME'
alias
cdrba='cd /home/oracle/rman_backup/$ORACLE_DBNAME/ADG'
alias cdrbx='cd
/home/oracle/rman_backup/$ORACLE_DBNAME/XTTS'
alias
cdx='cd /home/oracle/xtts'
alias cdos='cd
/home/oracle/oracle_scripts'
alias cdoss='cd
/home/oracle/os_scripts'
alias cdrbl='cd
/home/oracle/os_scripts/rman_log'
set -o vi
stty erase ^H
stty erase ^?
EOF
--os 설치 후 ssh 접속하자마자 설정한 상태임
(할 필요 없음)
[root@ol8ora19rf1][/root]$ cat >> ~root/.bash_profile
<< 'EOF'
set -o vi
stty
erase ^H
stty erase ^?
export
PS1='[\u@\h][$PWD]$ '
export
ORACLE_BASE=/u01/app/oracle
export
GRID_HOME=/u01/app/19c/grid
export
DB_HOME=$ORACLE_BASE/product/19c/db_1
export
BASE_PATH=/usr/sbin:$PATH
export
PATH=$GRID_HOME/bin:$BASE_PATH
alias csrt='crsctl stat
res -t'
EOF
[root@ol8ora19rf1][/root]$ cat >> ~oracle/.bashrc
<< 'EOF'
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias
adrci='rlwrap adrci'
alias dgmgrl='rlwrap dgmgrl'
alias oraenv='rlwrap oraenv'
alias
lsnrctl='rlwrap lsnrctl'
EOF
[root@ol8ora19rf1][/root]$ cat >> ~grid/.bashrc
<< 'EOF'
alias sqlplus='rlwrap sqlplus'
alias adrci='rlwrap adrci'
alias
asmcmd='rlwrap asmcmd -p'
alias lsnrctl='rlwrap
lsnrctl'
EOF
3-22. transparent_hugepage disable 및 disable 확인
[root@ol8ora19rf1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@ol8ora19rf1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/defrag
always defer defer+madvise madvise
[never]
--만약 defrag의 값이 never가 아니라면 아래의 작업 수행 시작
[root@ol8ora19rf1][/root]$ cat >
/etc/systemd/system/disable-thp.service << 'EOF'
[Unit]
Description=Disable
Transparent Huge Pages (THP)
[Service]
Type=simple
ExecStart=/bin/sh -c
"echo 'never' >/sys/kernel/mm/transparent_hugepage/enabled && echo
'never' >/sys/kernel/mm/transparent_hugepage/defrag"
[Install]
WantedBy=multi-user.target
EOF
[root@ol8ora19rf1][/root]$ systemctl daemon-reload
[root@ol8ora19rf1][/root]$ systemctl start disable-thp
[root@ol8ora19rf1][/root]$ systemctl enable disable-thp
--만약
defrag가 never가 아니라면 아래의 작업 수행 종료
[root@ol8ora19rf1][/root]$ cat
/etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g'
/etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="resume=/dev/mapper/ol-swap
rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"
GRUB_ENABLE_BLSCFG=true
--------------------transparent_hugepage=never가 아니라면 아래의 작업
수행 시작--------------------
--transparent_hugepage=never로 고친 후 아래의 명령을
실행
grub2-mkconfig -o
/boot/grub2/grub.cfg
--> 실행 후
재부팅후 다시 확인해야함
--> 재부팅 후 확인
grep AnonHugePages
/proc/meminfo
AnonHugePages:
0 kB
--> 0
kB여야함
--> huge
page 설정은 인스턴스 설치까지 완료한 후 할것임
--------------------transparent_hugepage=never가 아니라면 아래의 작업 수행 종료--------------------
3-23. systemd-logind 설정
[root@ol8ora19rf1][/root]$ cat /etc/systemd/logind.conf |
grep -i RemoveIPC
#RemoveIPC=no
RemoveIPC=no
--만약 해당 설정이 no가 아니라면 no로 바꾸고
아래의 명령 수행으로 적용해줌
systemctl restart systemd-logind
systemctl status systemd-logind
3-24. CPU 속도가 동일한지 확인
[root@ol8ora19rf1][/root]$ grep -E '^model name|^cpu MHz'
/proc/cpuinfo
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz : 3071.999
model name : Intel(R)
Core(TM) Ultra 9 275HX
cpu
MHz :
3071.999
--> cpuspeed 서비스가 있다면 종료
시켜야함
ps -ef | grep
[c]puspeed
kill -9 <pid>
3-25. tuned-adm
서비스 확인 및 설정
[root@ol8ora19rf1][/root]$ tuned-adm
active
Current active profile: virtual-guest
[root@ol8ora19rf1][/root]$ tuned-adm
list
Available
profiles:
-
accelerator-performance - Throughput performance based
tuning with disabled higher latency STOP states
-
aws
- Optimize for aws ec2 instances
-
balanced
- General non-specialized tuned profile
-
desktop
- Optimize for the desktop use-case
-
epyc-eda
- Optimize for EDA compute workloads on AMD EPYC CPUs
-
hpc-compute
- Optimize for HPC compute workloads
-
intel-sst
- Configure for Intel Speed Select Base Frequency
-
latency-performance - Optimize
for deterministic performance at the cost of increased power consumption
-
network-latency
- Optimize for deterministic performance at the cost of increased power
consumption, focused on low latency network performance
-
network-throughput -
Optimize for streaming network throughput, generally only necessary on older
CPUs or 40G+ networks
-
optimize-serial-console - Optimize for serial console
use.
-
powersave
- Optimize for low power consumption
-
throughput-performance - Broadly applicable tuning
that provides excellent performance across a variety of common server
workloads
-
virtual-guest
- Optimize for running inside a virtual guest
-
virtual-host
- Optimize for running KVM guests
Current active
profile: virtual-guest
[root@ol8ora19rf1][/root]$ tuned-adm profile
throughput-performance
[root@ol8ora19rf1][/root]$
tuned-adm active
Current active profile:
throughput-performance
--> 해당
값이 throughput-perfomance 또는 tuned-profile-oracle이어야함
3-26. cio 옵션이 설정되어 있는지 확인
--> 오라클홈과 그리드홈이 위치하는 디스크에는 cio 설정이 없어야함
[root@ol8ora19rf1][/root]$ cat /etc/fstab
#
# /etc/fstab
# Created by anaconda
on Tue Sep 23 01:56:25 2025
#
#
Accessible filesystems, by reference, are maintained under '/dev/disk/'.
# See man pages fstab(5), findfs(8), mount(8) and/or
blkid(8) for more info.
#
#
After editing this file, run 'systemctl daemon-reload' to update systemd
# units generated from this file.
#
/dev/mapper/ol-root
/
xfs defaults 0
0
UUID=96adcbef-3299-4fff-a047-733f31804ccc
/boot
xfs defaults 0
0
/dev/mapper/ol-swap
none
swap defaults 0 0
tmpfs
/dev/shm
tmpfs size=16g
00
--추가적인 검증
mount | grep
cio
findmnt -o
TARGET,OPTIONS | grep cio
--만약 cio 옵션이
있다면,
/etc/fstab 에서 cio 옵션 설정 제거
후
다시 마운트 시키고 --> 마운트 방법 :
"mount -o remount 마운트위치"
리부트
시킴
3-27. pam_limits.so 설정
[root@ol8ora19rf1][/root]$ cat >> /etc/pam.d/login
<< 'EOF'
session
required pam_limits.so
EOF
[root@ol8ora19rf1][/root]$ cat /etc/pam.d/login | grep
pam_limits.so
session required
pam_limits.so
3-28. NOZEROCONF=yes 확인
[root@ol8ora19rf1][/root]$ cat
/etc/sysconfig/network
# Created by anaconda
#
oracle-database-preinstall-19c : Add NOZEROCONF=yes
NOZEROCONF=yes
3-29. java jdk 1.8 설치
[root@ol8ora19rf1][/root]$ dnf -y install
java-1.8.0-openjdk-devel
[root@ol8ora19rf1][/root]$ java
-version
openjdk version
"1.8.0_462"
OpenJDK Runtime Environment (build
1.8.0_462-b08)
OpenJDK 64-Bit Server VM (build
25.462-b08, mixed mode)
--자바 기본 버전 확인
[root@ol8ora19rf1][/root]$ alternatives --config
java
3-30. MTU 사이즈를
9000으로 변경 (root os user) (테스트 VM환경에서는 설정하지 않음)
cat
/etc/sysconfig/network-scripts/ifcfg-??????
.
.
.
MTU=9000
sudo systemctl restart network
ip a | grep mtu
ifconfig | grep -i mtu
--추후 2번째 노드를 만든 후 아래와 같이 검증해볼
필요 있음
ping -M do -s 8972
<ip address>
--> MTU가 9000으로 통신이 되고 있는지 확인하는 것임
3-31. OSW 설치
OSWatcher (Doc ID 301137.1) 문서 참조할 것
--oswbb840.tar 파일 다운 로드 후 /home/oralce 디렉토리에
저장
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ tar xvf
oswbb840.tar
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cp
/home/oracle/oswbb/Exampleprivate.net
/home/oracle/oswbb/private.net
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/oswbb/private.net << 'EOF'
######################################################################
# This file contains examples of how to monitor private
networks. To
# monitor your private networks create an
executable file in this same
# directory named
private.net. Use the example for your host os below.
#
Make sure not to remove the last line in this file. Your file
# private.net MUST contain the rm lock.file line.
######################################################################
#Solaris Example
######################################################################
#echo "zzz ***"`date`
#traceroute
-r -F <node1-priv>
#traceroute -I -r -F
<node1-priv>
#traceroute -r -F
<node2-priv>
#traceroute -I -r -F
<node2-priv>
######################################################################
#HP Example
######################################################################
#echo "zzz ***"`date`
#traceroute
-r -F <node1-priv>
#traceroute -r -F
<node2-priv>
######################################################################
#Linux Example
######################################################################
echo "zzz ***"`date`
traceroute -r -F 10.0.3.31
traceroute -r -F 10.0.3.32
######################################################################
#Tru64 Example
######################################################################
#echo "zzz ***"`date`
#traceroute
-r -f <node1-priv>
#traceroute -r -f
<node2-priv>
#######################################################################
#AIX Example
######################################################################
#echo "zzz ***"`date`
#traceroute
-r <node1-priv>
#traceroute -r
<node2-priv>
######################################################################
# DO NOT DELETE THE FOLLOWING LINE!!!!!!!!!!!!!!!!!!!!!
######################################################################
rm locks/lock.file
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cd
/home/oracle/oswbb
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oswbb]$ nohup
./startOSWbb.sh 5 960 gzip
--> 5초 간격으로 데이터를 수집하고 960시간(2주) 동안의 데이터를 보관한뒤 파일을 자동으로
압축 저장
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oswbb]$ cat
nohup.out
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oswbb]$ ps -ef |
grep -i osw
oracle 6079
1 0 18:26 pts/5 00:00:00 /bin/sh ./OSWatcher.sh 5 960
gzip
oracle 6275 6079 0
18:26 pts/5 00:00:00 /bin/sh ./OSWatcherFM.sh 960
/home/oracle/oswbb/archive
oracle
6689 4851 0 18:27 pts/5 00:00:00 grep --color=auto
-i osw
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oswbb]$ cd
/home/oracle/oswbb
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oswbb]$ java
-jar oswbba.jar -i /home/oracle/oswbb/archive
-->
OSW의 결과를 시각화 그래프로 볼 수 있음
3-32. OSW 자동 기동
--root os user
[root@ol8ora19rf1][/root]$ cat >
/etc/systemd/system/oswbb.service << 'EOF'
[Unit]
Description=Start OSWbb
script at boot
After=network.target
[Service]
Type=forking
User=oracle
WorkingDirectory=/home/oracle/oswbb
ExecStart=/home/oracle/oswbb/startOSWbb.sh 5 960 gzip
PIDFile=/home/oracle/oswbb/oswbb.pid
Restart=on-failure
StandardOutput=append:/home/oracle/oswbb/oswbb.log
StandardError=append:/home/oracle/oswbb/oswbb_error.log
[Install]
WantedBy=multi-user.target
EOF
[root@ol8ora19rf1][/root]$ vi /home/oracle/oswbb/startOSWbb.sh
--맨 아래 부분을 아래와 같이
수정
######################################################################
# Start OSW
######################################################################
#./OSWatcher.sh $1 $2 $3 $4 & ##주석처리
nohup ./OSWatcher.sh $1 $2 $3 $4 &
echo $! >
/home/oracle/oswbb/oswbb.pid
[root@ol8ora19rf1][/home/oracle/oswbb]$ systemctl
daemon-reload
[root@ol8ora19rf1][/home/oracle/oswbb]$
systemctl enable oswbb.service
Created symlink from
/etc/systemd/system/multi-user.target.wants/oswbb.service to
/etc/systemd/system/oswbb.service.
[root@ol8ora19rf1][/home/oracle/oswbb]$ systemctl restart
oswbb.service
--> os reboot후 os watcher가
자동 기동 되는 지 확인
[root@ol8ora19rf1][/root]$ ps -ef | grep -i
osw
oracle 7582
1 0 01:03 ? 00:00:00 /bin/sh
./OSWatcher.sh 5 960 gzip
root 7645 7248 0
01:03 pts/6 00:00:00 grep --color=auto -i
osw
[root@ol8ora19rf1][/root]$ poweroff
4. ASM 환경 구성
4-1. 공유 스토리지 생성
--VM 종료 후
--디렉토리가 존재하는지
확인
D:\VMware\OL8_ORA19RF\Storage
--> 없으면 생성할 것
VM 선택 -> 마우스
오른쪽 버튼 -> Virtual Machine Settings
Add -> Hard
Disk -> NVMe -> Create a new virtual disk
Maxium
Disk Size (GB) : 2
Allocate all disk space noew : 체크
Store virtual disk as a single file : 선택 -> Next
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS1.vmdk ->
finish
Advanced -> NVMe 1:0 -> Independent ->
Persistent -> OK
VM 선택 -> 마우스
오른쪽 버튼 -> Virtual Machine Settings
Add -> Hard
Disk -> NVMe -> Create a new virtual disk
Maxium
Disk Size (GB) : 2
Allocate all disk space noew : 체크
Store virtual disk as a single file : 선택 -> Next
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS2.vmdk ->
finish
Advanced -> NVMe 1:1 -> Independent ->
Persistent -> OK
VM 선택 -> 마우스
오른쪽 버튼 -> Virtual Machine Settings
Add -> Hard
Disk -> NVMe -> Create a new virtual disk
Maxium
Disk Size (GB) : 2
Allocate all disk space noew : 체크
Store virtual disk as a single file : 선택 -> Next
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS3.vmdk ->
finish
Advanced -> NVMe 1:2 -> Independent ->
Persistent -> OK
VM 선택 -> 마우스
오른쪽 버튼 -> Virtual Machine Settings
Add -> Hard
Disk -> NVMe -> Create a new virtual disk
Maxium
Disk Size (GB) : 64
Allocate all disk space noew : 체크
Store virtual disk as a single file : 선택 -> Next
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_DATA1_1.vmdk ->
finish
Advanced -> NVMe 1:3 -> Independent ->
Persistent -> OK
VM 선택 -> 마우스
오른쪽 버튼 -> Virtual Machine Settings
Add -> Hard
Disk -> NVMe -> Create a new virtual disk
Maxium
Disk Size (GB) : 64
Allocate all disk space noew : 체크
Store virtual disk as a single file : 선택 -> Next
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_FRA1_1.vmdk ->
finish
Advanced -> NVMe 1:4 -> Independent ->
Persistent -> OK
--------------------UDEV로 구성하는 경우
시작--------------------
--D:\VMware\OL8_ORA19RF\ol8ora19rf1\ol8ora19rf1.vmx 파일에 아래의
한줄 추가
disk.EnableUUID =
"TRUE"
--------------------UDEV로 구성하는 경우
종료--------------------
--VM 시작 (부팅)
[root@ol8ora19rf1][/root]$ ls -l
/dev/nvme1n*
brw-rw----. 1 root disk 259, 3 Sep 29 00:24 /dev/nvme1n1
brw-rw----. 1 root disk 259, 4 Sep 29 00:24 /dev/nvme1n2
brw-rw----. 1 root disk 259, 5 Sep 29 00:24 /dev/nvme1n3
brw-rw----. 1 root disk 259, 6 Sep 29 00:24 /dev/nvme1n4
brw-rw----. 1 root disk 259, 7 Sep 29 00:24
/dev/nvme1n5
4-2. UDEV로 구성하는 경우
----------------------ID_PATH로 하는 경우 시작----------------------
[root@ol8ora19rf1][/root]$ ls -l
/dev/disk/by-path
total 0
lrwxrwxrwx. 1 root
root 9 Sep 29 00:24 pci-0000:00:07.1-ata-2 -> ../../sr0
lrwxrwxrwx. 1 root root 13 Sep 29 00:24
pci-0000:0b:00.0-nvme-1 -> ../../nvme0n1
lrwxrwxrwx.
1 root root 15 Sep 29 00:24 pci-0000:0b:00.0-nvme-1-part1 ->
../../nvme0n1p1
lrwxrwxrwx. 1 root root 15 Sep 29 00:24
pci-0000:0b:00.0-nvme-1-part2 -> ../../nvme0n1p2
lrwxrwxrwx. 1 root root 13 Sep 29 00:24
pci-0000:1b:00.0-nvme-1 -> ../../nvme1n1
lrwxrwxrwx.
1 root root 13 Sep 29 00:24 pci-0000:1b:00.0-nvme-2 -> ../../nvme1n2
lrwxrwxrwx. 1 root root 13 Sep 29 00:24
pci-0000:1b:00.0-nvme-3 -> ../../nvme1n3
lrwxrwxrwx.
1 root root 13 Sep 29 00:24 pci-0000:1b:00.0-nvme-4 -> ../../nvme1n4
lrwxrwxrwx. 1 root root 13 Sep 29 00:24
pci-0000:1b:00.0-nvme-5 -> ../../nvme1n5
[root@ol8ora19rf1][/root]$ cat >
/etc/udev/rules.d/99-oracle-asmdevices.rules << 'EOF'
KERNEL=="nvme1n*", ENV{ID_PATH}=="pci-0000:1b:00.0-nvme-1",
SYMLINK+="oracleasm/disks/CRS1", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="nvme1n*", ENV{ID_PATH}=="pci-0000:1b:00.0-nvme-2",
SYMLINK+="oracleasm/disks/CRS2", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="nvme1n*", ENV{ID_PATH}=="pci-0000:1b:00.0-nvme-3",
SYMLINK+="oracleasm/disks/CRS3", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="nvme1n*", ENV{ID_PATH}=="pci-0000:1b:00.0-nvme-4",
SYMLINK+="oracleasm/disks/DATA1_1", OWNER="grid", GROUP="asmadmin",
MODE="0660"
KERNEL=="nvme1n*",
ENV{ID_PATH}=="pci-0000:1b:00.0-nvme-5", SYMLINK+="oracleasm/disks/FRA1_1",
OWNER="grid", GROUP="asmadmin", MODE="0660"
EOF
-->
ID_PATH로 하는것이 가상 머신 환경에서 더욱 더 안정적임
--------------------ID_PATH로 하는 경우
종료--------------------------
--------------------ID_WWN 으로 하는 경우
시작------------------------
[root@ol8ora19rf1][/root]$ ls -l
/dev/disk/by-path
total 0
lrwxrwxrwx. 1 root
root 9 Sep 29 21:28 pci-0000:00:07.1-ata-2 -> ../../sr0
lrwxrwxrwx. 1 root root 13 Sep 29 21:28
pci-0000:0b:00.0-nvme-1 -> ../../nvme0n1
lrwxrwxrwx.
1 root root 15 Sep 29 21:28 pci-0000:0b:00.0-nvme-1-part1 ->
../../nvme0n1p1
lrwxrwxrwx. 1 root root 15 Sep 29 21:28
pci-0000:0b:00.0-nvme-1-part2 -> ../../nvme0n1p2
lrwxrwxrwx. 1 root root 13 Sep 29 21:28
pci-0000:1b:00.0-nvme-1 -> ../../nvme1n1
lrwxrwxrwx.
1 root root 13 Sep 29 21:28 pci-0000:1b:00.0-nvme-2 -> ../../nvme1n2
lrwxrwxrwx. 1 root root 13 Sep 29 21:28
pci-0000:1b:00.0-nvme-3 -> ../../nvme1n3
lrwxrwxrwx.
1 root root 13 Sep 29 21:28 pci-0000:1b:00.0-nvme-4 -> ../../nvme1n4
lrwxrwxrwx. 1 root root 13 Sep 29 21:28
pci-0000:1b:00.0-nvme-5 -> ../../nvme1n5
[root@ol8ora19rf1][/root]$ udevadm info --query=all
--name=/dev/nvme1n1 | egrep 'ID_WWN' | grep -v
ID_WWN_WITH_EXTENSION
E:
ID_WWN=eui.e2435832145baa09000c2967de858abb
[root@ol8ora19rf1][/root]$ udevadm info --query=all
--name=/dev/nvme1n2 | egrep 'ID_WWN' | grep -v
ID_WWN_WITH_EXTENSION
E:
ID_WWN=eui.c3a548c3873ae0c7000c2965ab39653f
[root@ol8ora19rf1][/root]$ udevadm info --query=all
--name=/dev/nvme1n3 | egrep 'ID_WWN' | grep -v
ID_WWN_WITH_EXTENSION
E:
ID_WWN=eui.48da7d9ea258e854000c296562f8ea0c
[root@ol8ora19rf1][/root]$ udevadm info --query=all
--name=/dev/nvme1n4 | egrep 'ID_WWN' | grep -v
ID_WWN_WITH_EXTENSION
E:
ID_WWN=eui.2842decf9178154f000c296a89ba33d7
[root@ol8ora19rf1][/root]$ udevadm info --query=all
--name=/dev/nvme1n5 | egrep 'ID_WWN' | grep -v
ID_WWN_WITH_EXTENSION
E:
ID_WWN=eui.4850cc785836d52d000c296eb7379425
[root@ol8ora19rf1][/root]$ cat >
/etc/udev/rules.d/99-oracle-asmdevices.rules << 'EOF'
ACTION=="add|change", SUBSYSTEM=="block",
ENV{DEVTYPE}=="disk", KERNEL=="nvme1n*",
ENV{ID_WWN}=="eui.e2435832145baa09000c2967de858abb",
SYMLINK+="oracleasm/disks/CRS1", OWNER="grid", GROUP="asmadmin", MODE="0660"
ACTION=="add|change", SUBSYSTEM=="block",
ENV{DEVTYPE}=="disk", KERNEL=="nvme1n*",
ENV{ID_WWN}=="eui.c3a548c3873ae0c7000c2965ab39653f",
SYMLINK+="oracleasm/disks/CRS2", OWNER="grid", GROUP="asmadmin", MODE="0660"
ACTION=="add|change", SUBSYSTEM=="block",
ENV{DEVTYPE}=="disk", KERNEL=="nvme1n*",
ENV{ID_WWN}=="eui.48da7d9ea258e854000c296562f8ea0c",
SYMLINK+="oracleasm/disks/CRS3", OWNER="grid", GROUP="asmadmin", MODE="0660"
ACTION=="add|change", SUBSYSTEM=="block",
ENV{DEVTYPE}=="disk", KERNEL=="nvme1n*",
ENV{ID_WWN}=="eui.2842decf9178154f000c296a89ba33d7",
SYMLINK+="oracleasm/disks/DATA1_1", OWNER="grid", GROUP="asmadmin",
MODE="0660"
ACTION=="add|change", SUBSYSTEM=="block",
ENV{DEVTYPE}=="disk", KERNEL=="nvme1n*",
ENV{ID_WWN}=="eui.4850cc785836d52d000c296eb7379425",
SYMLINK+="oracleasm/disks/FRA1_1", OWNER="grid", GROUP="asmadmin",
MODE="0660"
EOF
-----------------ID_WWN 으로 하는 경우
종료-------------------------
--재부팅 없이 즉시
적용
udevadm control
--reload-rules
udevadm trigger
--> 재부팅 후 다시 확인 (반드시 확인하고 넘어갈 것)
[root@ol8ora19rf1][/root]$ ls -l
/dev/oracleasm/disks/*
lrwxrwxrwx. 1 root root 13 Sep 29 00:33
/dev/oracleasm/disks/CRS1 -> ../../nvme1n1
lrwxrwxrwx. 1 root root 13 Sep 29 00:33
/dev/oracleasm/disks/CRS2 -> ../../nvme1n2
lrwxrwxrwx. 1 root root 13 Sep 29 00:33
/dev/oracleasm/disks/CRS3 -> ../../nvme1n3
lrwxrwxrwx. 1 root root 13 Sep 29 00:33
/dev/oracleasm/disks/DATA1_1 -> ../../nvme1n4
lrwxrwxrwx. 1 root root 13 Sep 29 00:33
/dev/oracleasm/disks/FRA1_1 -> ../../nvme1n5
4-2. ASMLIB로 설정하는 경우 (실제 해보지는 않음 udev로 구성했음, 추후 해볼 것)
--> OS부팅 시 디폴트 커널 변경
(Oracle Linux 8 버전에서 asmlib 2.x 버전을 사용하려면 레드헷 커널을 사용해야함)
[root@ol8ora19rf1][/root]$ uname -a
Linux ol88ca1
5.15.0-101.103.2.1.el8uek.x86_64 #2 SMP Mon May 1 20:11:30 PDT 2023 x86_64
x86_64 x86_64 GNU/Linux
[root@ol8ora19rf1][/root]$ ls -l
/boot/vmlinuz-*
-rwxr-xr-x. 1 root root
13484336 Jul 8 23:48
/boot/vmlinuz-0-rescue-765d97a7dd954fe7a938515195f77a4c
-rwxr-xr-x. 1 root root 10843648 May 17 2023
/boot/vmlinuz-4.18.0-477.10.1.el8_8.x86_64
-rwxr-xr-x. 1
root root 13484336 May 2 2023
/boot/vmlinuz-5.15.0-101.103.2.1.el8uek.x86_64
[root@ol8ora19rf1][/root]$ grubby --set-default
/boot/vmlinuz-4.18.0-477.10.1.el8_8.x86_64
The default is
/boot/loader/entries/765d97a7dd954fe7a938515195f77a4c-4.18.0-477.10.1.el8_8.x86_64.conf
with index 1 and kernel
/boot/vmlinuz-4.18.0-477.10.1.el8_8.x86_64
[root@ol8ora19rf1][/root]$ shutdown -Fr
now
--> 재부팅 후 uname -a 로 부팅
커널(4.18로)이 바뀐 것을 확인하고 넘어갈 것
4-2-1. fdisk 작업
[root@ol8ora19rf1][/root]$ ls -l
/dev/nvme1n*
brw-rw----. 1 root disk 259, 3 Sep 29 00:24 /dev/nvme1n1
brw-rw----. 1 root disk 259, 4 Sep 29 00:24 /dev/nvme1n2
brw-rw----. 1 root disk 259, 5 Sep 29 00:24 /dev/nvme1n3
brw-rw----. 1 root disk 259, 6 Sep 29 00:24 /dev/nvme1n4
brw-rw----. 1 root disk 259, 7 Sep 29 00:24
/dev/nvme1n5
n -> 엔터 -> 엔터
-> 엔터 -> w (모두 이렇게 작업하면됨)
[root@ol8ora19rf1][/root]$ fdisk /dev/nvme1n1
[root@ol8ora19rf1][/root]$ fdisk /dev/nvme1n2
[root@ol8ora19rf1][/root]$ fdisk /dev/nvme1n3
[root@ol8ora19rf1][/root]$ fdisk /dev/nvme1n4
[root@ol8ora19rf1][/root]$ fdisk /dev/nvme1n5
n -> 엔터 -> 엔터 -> 엔터 -> w (모두 이렇게
작업하면됨)
4-2-2. asmlib 설치
[root@ol8ora19rf1][/root]$ cd ~
[root@ol8ora19rf1][/root]$ wget https://yum.oracle.com/repo/OracleLinux/OL8/10/baseos/base/x86_64/getPackage/kmod-redhat-oracleasm-2.0.8-18.0.1.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ wget https://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.17-1.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ wget https://yum.oracle.com/repo/OracleLinux/OL8/addons/x86_64/getPackage/oracleasm-support-2.1.12-1.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ rpm -ivh
kmod-redhat-oracleasm-2.0.8-18.0.1.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ rpm -ivh
oracleasmlib-2.0.17-1.el8.x86_64.rpm
[root@ol8ora19rf1][/root]$ rpm -ivh
oracleasm-support-2.1.12-1.el8.x86_64.rpm
4-2-3. asmlib 설치
[root@ol8ora19rf1][/root]$ oracleasm configure
-i
Configuring the
Oracle ASM library driver.
This will
configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine
whether the driver is
loaded on boot and what
permissions it will have. The current values
will
be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will
abort.
Default user to
own the driver interface [grid]: grid
Default group to own
the driver interface [asmadmin]: asmadmin
Start Oracle ASM
library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks
on boot (y/n) [n]: y
Writing Oracle ASM library
driver configuration: done
[root@ol8ora19rf1][/root]$ oracleasm init
[root@ol8ora19rf1][/root]$ oracleasm status
--> 모두
yes인 것을 확인
[root@ol8ora19rf1][/root]$ oracleasm configure
4-2-4. asm 디스크 생성
[root@ol8ora19rf1][/root]$ ls -l /dev/nvme1n*
--디스크 정보 확인하면서 작업할
것
[root@ol8ora19rf1][/root]$ lsblk -o
NAME,SIZE,FSTYPE,MOUNTPOINT /dev/nvme1n*
[root@ol8ora19rf1][/root]$ fdisk -l
/dev/sd*
[root@ol8ora19rf1][/root]$ oracleasm createdisk CRS1
/dev/nvme1np1
[root@ol8ora19rf1][/root]$ oracleasm
createdisk CRS2 /dev/nvme1np2
[root@ol8ora19rf1][/root]$
oracleasm createdisk CRS3 /dev/nvme1np3
[root@ol8ora19rf1][/root]$ oracleasm createdisk DATA1_1
/dev/nvme1np4
[root@ol8ora19rf1][/root]$ oracleasm
createdisk FRA1_1 /dev/nvme1np5
[root@ol8ora19rf1][/root]$ oracleasm scandisks
[root@ol8ora19rf1][/root]$ oracleasm
listdisks
[root@ol8ora19rf1][/root]$ poweroff
-- VMware
Workstation 환경에서 udev 사용을 권장함
5. 두번째 노드 생성
--ol8ora19rf1
[root@ol8ora19rf1][/run/media/Packages]$
poweroff
--한후
--ol8ora19rf1 VM에서 ASM DISK를 모두 연결 제거해야함
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS1.vmdk
-> Remove
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS1.vmdk
-> Remove
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS2.vmdk
-> Remove
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS3.vmdk
-> Remove
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_DATA1_1.vmdk
-> Remove
Virtual Machine
Settings ->
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_FRA1_1.vmdk
-> Remove
VM 선택 -> Manage
-> Clone -> Next
The current state in the virtual
Machine -> Next
Create a full clone -> Next
Virtual machine name : ol8ora19rf2
Location :
D:\VMware\OL8_ORA19RF\ol8ora19rf2
ol8ora19rf1과
ol8ora19rf2 모두 디스크를 추가시킴 (모두 동일하게)
VM 선택 -> Virtual
Machine Setting -> Add -> Hard Disk -> NVMe -> Use an Existing
virtual disk
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS1.vmdk
(Advanced에서 NVMe 1:0, Independent, Persistent)
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS2.vmdk
(Advanced에서 NVMe 1:1, Independent, Persistent)
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_CRS3.vmdk
(Advanced에서 NVMe 1:2, Independent, Persistent)
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_DATA1_1.vmdk
(Advanced에서 NVMe 1:3, Independent, Persistent)
D:\VMware\OL8_ORA19RF\Storage\ORA19RF_FRA1_1.vmdk
(Advanced에서 NVMe 1:4, Independent, Persistent)
ol8ora19rf2 VM설정으로
들어가서
Network Adapter NAT -> Advanced -> MAC Address ->
Generate
Network Adapter 2 host-only -> Advanced
-> MAC Address -> Generate
--> MAC주소를 변경함
이상태에서 ol8ora19rf2 노드만!!! 부팅 시킴
(기존에 존재하는 ol8ora19rf1의 주소로 접속하면 ol8ora19rf2로 붙게 되는 것임)
root 계정으로 로그인 후
(root/1234)
vi /etc/sysconfig/network-scripts/ifcfg-ens160 로 들어간
후
IPADDR=192.168.240.32
로
변경
vi
/etc/sysconfig/network-scripts/ifcfg-ens224 로 들어간 후
IPADDR=10.0.3.32
로
변경
[root@ol8ora19rf1][/root]$ hostnamectl set-hostname
ol8ora19rf2
[root@ol8ora19rf1][/root]$ hostname
ol8ora19rf2
--그 다음
vi
~oracle/.bash_profile --로 들어간 후
export
ORACLE_SID=ORA19RF2
export ORACLE_SID_LOWER=ORA19RF2
--> 로 변경
--그 다음
vi ~grid/.bash_profile --로 들어간
후
export ORACLE_SID=+ASM2
export
ORACLE_SID_LOWER=+asm2
export
ORACLE_USER_ORACLE_SID=ORA19RF2
export
ORACLE_USER_ORACLE_SID_LOWEWR=ORA19RF2
--> 로 변경
--> ol8ora19rf2 VM 종료
--D:\VMware\ORA19RF\ol8ora19rf1\ol8ora19rf1.vmx 파일에 추가
--D:\VMware\ORA19RF\ol8ora19rf2\ol8ora19rf2.vmx 파일에 추가
disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
nvme1.sharedBus = "virtual"
nvme1:0.deviceType = "disk"
nvme1:1.deviceType = "disk"
nvme1:2.deviceType = "disk"
nvme1:3.deviceType = "disk"
nvme1:4.deviceType = "disk"
--> 위의
설정은 반드시 2개의 VM을 모두 poweroff 한 상태에서 수정해야함
--> 수정이 완료되었으면 2개의 노드(서버) 모두 동시에 기동(부팅)
------------------------udev로 구성한 경우 확인
시작--------------------------
[root@ol8ora19rf1][/root]$ ls -l
/dev/oracleasm/disks
total 0
lrwxrwxrwx. 1 root root 13
Sep 29 21:54 CRS1 -> ../../nvme1n1
lrwxrwxrwx. 1 root
root 13 Sep 29 21:54 CRS2 -> ../../nvme1n2
lrwxrwxrwx. 1 root root 13 Sep 29 21:54 CRS3 ->
../../nvme1n3
lrwxrwxrwx. 1 root root 13 Sep 29 21:54
DATA1_1 -> ../../nvme1n4
lrwxrwxrwx. 1 root root 13
Sep 29 21:54 FRA1_1 -> ../../nvme1n5
[root@ol8ora19rf2][/root]$ ls -l
/dev/oracleasm/disks
total 0
lrwxrwxrwx. 1 root root 13 Sep 29 21:54 CRS1 ->
../../nvme1n1
lrwxrwxrwx. 1 root root 13 Sep 29 21:54
CRS2 -> ../../nvme1n2
lrwxrwxrwx. 1 root root 13 Sep
29 21:54 CRS3 -> ../../nvme1n3
lrwxrwxrwx. 1 root
root 13 Sep 29 21:54 DATA1_1 -> ../../nvme1n4
lrwxrwxrwx. 1 root root 13 Sep 29 21:54 FRA1_1 ->
../../nvme1n5
----------------------udev로 구성한 경우 확인
종료---------------------------
----------------------asmlib 로 구성한 경우 확인
시작------------------------
[root@ol8ora19rf1][/root]$ ls
-l /dev/oracleasm/disks
[root@ol8ora19rf1][/root]$ oracleasm listdisks
[root@ol8ora19rf2][/root]$ ls -l
/dev/oracleasm/disks
[root@ol8ora19rf2][/root]$
oracleasm listdisks
----------------------asmlib 로 구성한 경우 확인
종료-----------------------
6. Grid 설치
6-1. Grid 설치 준비
--grid os
user
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cd ~
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
$GRID_HOME
--> 이미 존재한다면 만들어지지 않을 것임
[+ASM1:grid@ol8ora19rf1][/home/grid]$ ls -l $GRID_HOME
--LINUX.X64_193000_grid_home.zip 파일을 /home/grid 위치에 복사시킴
--해당 파일은 https://www.oracle.com/database/technologies/oracle-database-software-downloads.html 에서 다운로드
하면됨
[+ASM1:grid@ol8ora19rf1][/home/grid]$ unzip -q /home/grid/LINUX.X64_193000_grid_home.zip -d $GRID_HOME
--root os
user
[root@ol8ora19rf1][/root]$ rpm -ivh
/u01/app/19c/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm
Verifying...
################################# [100%]
Preparing...
################################# [100%]
Using default
group oinstall to install package
Updating /
installing...
1:cvuqdisk-1.0.10-1
################################# [100%]
--ol8ora19rf2 서버에 파일
전송
[root@ol8ora19rf1][/u01/app/19c/grid/cv/rpm]$ scp
/u01/app/19c/grid/cv/rpm/cvuqdisk-1.0.10-1.rpm
ol8ora19rf2:/tmp
The authenticity of host 'ol8ora19rf2 (192.168.240.22)'
can't be established.
ECDSA key fingerprint is
SHA256:dgyv76H+VXxcJ9Yv28imNT+wFslitGn5S5VATMVeXEA.
Are
you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'ol8ora19rf2,192.168.240.22'
(ECDSA) to the list of known hosts.
root@ol8ora19rf2's password:
cvuqdisk-1.0.10-1.rpm
100% 11KB 10.1MB/s 00:00
--> ol8ora19rf2 서버에 접속해서
설치함
[root@ol8ora19rf2][/root]$ rpm -ivh
/tmp/cvuqdisk-1.0.10-1.rpm
Verifying...
################################# [100%]
Preparing...
################################# [100%]
Using default
group oinstall to install package
Updating /
installing...
1:cvuqdisk-1.0.10-1
################################# [100%]
--Oracle Linux 8 버전에서 Oracle 19c 설치 시 반드시 ApplyRU 방식으로 해야함
-------------------------> Apply PSU를 하고자 한다면 START
<------------------------------
--root os
user 임
[root@ol8ora19rf1][/root]$ mkdir -pv
/tmp/patch
mkdir: created directory '/tmp/patch'
[root@ol8ora19rf1][/root]$ cd /tmp/patch
[root@ol8ora19rf1][/tmp/patch]$
--> /tmp/patch 위치에
p6880880_190000_Linux-x86-64.zip 파일 업로드 (해당 파일은 반드시 19.28 용 OPatch여야함)
--> /tmp/patch 위치에 p37957391_190000_Linux-x86-64.zip 파일
업로드 (19.28 GI UPDATE 파일임)
[root@ol8ora19rf1][/tmp/patch]$ unzip -q
/tmp/patch/p37957391_190000_Linux-x86-64.zip -d /tmp/patch
[root@ol8ora19rf1][/tmp/patch]$ chmod 777 -R
/tmp/patch
--grid os user
임
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mv $GRID_HOME/OPatch
$GRID_HOME/OPatch.bak
[+ASM1:grid@ol8ora19rf1][/home/grid]$ unzip -q
/tmp/patch/p6880880_190000_Linux-x86-64.zip -d $GRID_HOME
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/OPatch/opatch version -oh $GRID_HOME
OPatch Version: 12.2.0.1.47
OPatch succeeded.
[+ASM1:grid@ol8ora19rf1][/home/grid]$ which
opatch
/u01/app/19c/grid/OPatch/opatch
[+ASM1:grid@ol8ora19rf1][/home/grid]$ opatch
version
OPatch Version: 12.2.0.1.47
OPatch
succeeded.
--------------------------> Apply PSU를 하고자 한다면 END
<---------------------------------
6-2. ol8ora19rf1 및 ol8ora19rf2 노드간 ssh 접속 환경 설정 (ol8ora19rf1 노드, grid os user)
--grid os user
임
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/oui/prov/resources/scripts/sshUserSetup.sh -user oracle -hosts
"ol8ora19rf1 ol8ora19rf2" -noPromptPassphrase -advanced
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/oui/prov/resources/scripts/sshUserSetup.sh -user grid -hosts
"ol8ora19rf1 ol8ora19rf2" -noPromptPassphrase -advanced
--비밀번호 없이 다른 노드에 ssh접속이 가능한지
확인
[+ASM1:grid@ol8ora19rf1][/home/grid]$ ssh
ol8ora19rf2
Activate the web console with: systemctl enable --now
cockpit.socket
Last login: Tue Sep 23 18:53:11 2025 from 192.168.240.1
[+ASM2:grid@ol8ora19rf2][/home/grid]$
exit
logout
Connection to ol8ora19rf2 closed.
[+ASM1:grid@ol8ora19rf1][/home/grid]$
6-3. grid 설치 전 요구사항 검증 (ol8ora19rf1 노드, grid os user)
--grid os user
임
--아래 환경 변수를 반드시
세팅해줘야함!!!!!!!!!!!!!!!!!!!!!
[+ASM1:grid@ol8ora19rf1][/home/grid]$ export
CV_ASSUME_DISTID=OL7
[+ASM1:grid@ol8ora19rf1][/home/grid]$ $GRID_HOME/runcluvfy.sh stage -pre crsinst -n ol8ora19rf1,ol8ora19rf2 -fixup -verbose
Verifying Package:
compat-libcap1-1.10 ...FAILED
ol8ora19rf2: PRVF-7532 :
Package "compat-libcap1" is missing on node
"ol8ora19rf2"
ol8ora19rf1: PRVF-7532 : Package "compat-libcap1" is
missing on node
"ol8ora19rf1"
--> 위 에러는 무시해도 됨 (OL8: CLUVFY Reports PRVF-7532 :
Package "compat-libcap1" is missing on node (Doc ID
2801988.1))
Verifying
resolv.conf Integrity ...FAILED
ol8ora19rf2: PRVG-2002 :
Encountered error in copying file "/etc/resolv.conf"
from node "ol8ora19rf2" to node "ol8ora19rf1"
protocol error: filename does not match request
Verifying DNS/NIS name service ...FAILED
ol8ora19rf2: PRVG-2002 : Encountered error in copying file
"/etc/nsswitch.conf"
from node "ol8ora19rf2" to node "ol8ora19rf1"
protocol error: filename does not match
request
[root@ol8ora19rf1][/root]$ cp -p /usr/bin/scp /usr/bin/scp-original
[root@ol8ora19rf1][/root]$ echo "/usr/bin/scp-original -T \$*" > /usr/bin/scp
[root@ol8ora19rf1][/root]$ cat /usr/bin/scp
/usr/bin/scp-original -T $*
[root@ol8ora19rf2][/root]$ cp -p /usr/bin/scp
/usr/bin/scp-original
[root@ol8ora19rf2][/root]$ echo
"/usr/bin/scp-original -T \$*" > /usr/bin/scp
[root@ol8ora19rf2][/root]$ cat /usr/bin/scp
/usr/bin/scp-original -T $*
--조치를 취했으니 다시 검증해봄
--아래 환경 변수를 반드시 세팅해줘야함
[+ASM1:grid@ol8ora19rf1][/home/grid]$ export
CV_ASSUME_DISTID=OL7
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/runcluvfy.sh stage -pre crsinst -n ol8ora19rf1,ol8ora19rf2 -fixup
-verbose
--이제 두번째 요구 사항 검증을 할
것임
--아래 환경 변수를 반드시
세팅해줘야함
[+ASM1:grid@ol8ora19rf1][/home/grid]$ export
CV_ASSUME_DISTID=OL7
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/runcluvfy.sh stage -pre crsinst -n ol8ora19rf1,ol8ora19rf2 -osdba dba
-orainv oinstall -fixup -method root -networks ens160/ens224
-verbose
6-4. Grid 설치
[root@ol8ora19rf1][/root]$ dnf install -y
policycoreutils
[root@ol8ora19rf2][/root]$ dnf install
-y policycoreutils
--grid os user
임
--1번 노드
[+ASM1:grid@ol8ora19rf1][/home/grid]$ export
CV_ASSUME_DISTID=OL7
[+ASM1:grid@ol8ora19rf1][/home/grid]$
$GRID_HOME/gridSetup.sh -applyRU /tmp/patch/37957391/
Preparing the home
to patch...
Applying the patch
/tmp/patch/37957391/...
--아래 위치에 applyRU 작업 로그
남음
/u01/app/19c/grid/cfgtoollogs/
Configure Oracle Grid Infrastructure for a New Cluster 선택 후
Next
Configure an Oracle Standalone Cluster 선택 후 Next
Create Local SCAN 선택
Cluster Name :
ol8ora19rf
SCAN Name : ol8ora19rf-scan
SCAN Port : 1521
Configure GNS 체크
해제
Next
Add
Public Hostname : ol8ora19rf2
Virtual Hostname : ol8ora19rf2-vip
SSH connectivitiy -> grid의 패스워드 입력 (1234) -> TEST
-> Setup
Next
ens33,
192.168.0.0, public
ens34, 10.0.2.0 ASM&Private
인 것을 확인 후 Next
Use Oracle Flex ASM
for Storage 선택 후 Next
Yes (GIMR Yes) -> NO
Change Discovery Path -> /dev/oracleasm/disks/*
Disk Group name : CRS
Redundancy : Normal
Allocation Unit
Size : 4MB
/dev/oracleasm/disks/CRS1,
/dev/oracleasm/disks/CRS2, /dev/oracleasm/disks/CRS3 선택
Configure Oralce ASM Filter Drivier 체크 해제
Next
Use same Passwords for these
accounts 선택 후 패스워드 입력 (패스워드는 oracle로 함) -> Next
IPMI
: Do not use로 선택 후 Next
EM 체크 해제 후 Next
순서대로 asmadmin, asmdba, asmoper로 지정 후 Next
Oracle base : /u01/app/oracle로 한 후 Next
Inventory Directory : /u01/app/oraInventory 로 한 후 Next
Automatically run configuration scritps -> 루트 패스워드
입력
Package: policycoreutils-python-2.5-17 - This is a
prerequisite condition to test whether the package
"policycoreutils-python-2.5-17" is available on the system.
Check Failed on Nodes: [ol8ora19rf2,
ol8ora19rf1]
Verification result of failed node:
ol8ora19rf2
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf2"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
Verification result of
failed node: ol8ora19rf1
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf1"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
--> 위 error가
나왔지만 ignore 시킴
DNS/NIS Name service 관련
--> 위 error가 나왔지만 ignore 시킴
Save Response File
클릭 후 RSP 파일을 저장 시켜 놔야함 (저장 위치는 /home/grid/grid.rsp)
Install 버튼 클릭 (드디어
설치 시작)
--crsconfig_params 파일 확인
(root.sh가 어떻게 실행 될 것인지 확인)
[+ASM1:grid@ol8ora19rf1][/home/grid]$ awk -F= 'NF
&& $1 !~ /^#/ && $2!=""'
/u01/app/19c/grid/crs/install/crsconfig_params
SILENT=false
ORACLE_OWNER=grid
ORA_DBA_GROUP=oinstall
ORA_ASM_GROUP=asmadmin
LANGUAGE_ID=AMERICAN_AMERICA.AL32UTF8
TZ=Asia/Seoul
ISROLLING=true
REUSEDG=false
USER_IGNORED_PREREQ=false
INSTALL_NODE=ol8ora19rf1
LISTENER_USERNAME=grid
MGMT_DB=false
BIG_CLUSTER=true
HUB_SIZE=32
HUB_NODE_LIST=ol8ora19rf1,ol8ora19rf2
HUB_NODE_VIPS=ol8ora19rf1-vip,ol8ora19rf2-vip
ORACLE_HOME=/u01/app/19c/grid
ORACLE_BASE=/u01/app/oracle
JREDIR=/u01/app/19c/grid/jdk/jre/
JLIBDIR=/u01/app/19c/grid/jlib
CLUSTER_TYPE=DB
VNDR_CLUSTER=false
CLUSTER_NAME=ol8ora19rf
NODE_NAME_LIST=ol8ora19rf1,ol8ora19rf2
ASM_UPGRADE=false
ASM_DISCOVERY_STRING=/dev/oracleasm/disks/*
ASM_CONFIG=near
CRS_STORAGE_OPTION=1
CSS_LEASEDURATION=400
CRS_NODEVIPS='ol8ora19rf1-vip/255.255.255.0/ens160,ol8ora19rf2-vip/255.255.255.0/ens160'
NETWORKS="ens160"/192.168.240.0:public,"ens224"/10.0.3.0:asm,"ens224"/10.0.3.0:cluster_interconnect
SCAN_NAME=ol8ora19rf-scan
SCAN_PORT=1521
SCAN_TYPE=LOCAL
AFD_CONF=false
RHP_CONF=false
GNS_CONF=false
NEW_NODEVIPS='ol8ora19rf1-vip/255.255.255.0/ens160,ol8ora19rf2-vip/255.255.255.0/ens160'
GPNPCONFIGDIR=$ORACLE_HOME
GPNPGCONFIGDIR=$ORACLE_HOME
CDATA_DISK_GROUP=CRS
CDATA_DISKS=/dev/oracleasm/disks/CRS1,/dev/oracleasm/disks/CRS2,/dev/oracleasm/disks/CRS3
CDATA_REDUNDANCY=NORMAL
CDATA_AUSIZE=4
CDATA_SIZE=0
CDATA_BACKUP_REDUNDANCY=
CDATA_BACKUP_AUSIZE=1
CDATA_BACKUP_SIZE=0
EXTENDED_CLUSTER=false
EXTENDED_CLUSTER_SITES=ol8ora19rf
SCAN_IPS=%oracle_install_crs_OPC_SCAN_IPs%
CLUSTER_CLASS=STANDALONE
-----------------------------만약 silent 모드로 설치한다면
시작-----------------------------------
--grid.rsp 파일 내용
[+ASM1:grid@ol8ora19rf1][/home/grid]$ awk -F= 'NF
&& $1 !~ /^#/ && $2!=""' /home/grid/grid.rsp >
/home/grid/grid_new.rsp
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat
/home/grid/grid_new.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v19.0.0
INVENTORY_LOCATION=/u01/app/oraInventory
oracle.install.option=CRS_CONFIG
ORACLE_BASE=/u01/app/oracle
oracle.install.asm.OSDBA=asmdba
oracle.install.asm.OSOPER=asmoper
oracle.install.asm.OSASM=asmadmin
oracle.install.crs.config.scanType=LOCAL_SCAN
oracle.install.crs.config.gpnp.scanName=ol8ora19rf-scan
oracle.install.crs.config.gpnp.scanPort=1521
oracle.install.crs.config.ClusterConfiguration=STANDALONE
oracle.install.crs.config.configureAsExtendedCluster=false
oracle.install.crs.config.clusterName=ol8ora19rf
oracle.install.crs.config.gpnp.configureGNS=false
oracle.install.crs.config.autoConfigureClusterNodeVIP=false
oracle.install.crs.config.clusterNodes=ol8ora19rf1:ol8ora19rf1-vip,ol8ora19rf2:ol8ora19rf2-vip
oracle.install.crs.config.networkInterfaceList=ens160:192.168.240.0:1,ens224:10.0.3.0:5
oracle.install.crs.configureGIMR=false
oracle.install.asm.configureGIMRDataDG=false
oracle.install.crs.config.storageOption=FLEX_ASM_STORAGE
oracle.install.crs.config.sharedFileSystemStorage.ocrLocations=
oracle.install.crs.config.useIPMI=false
oracle.install.asm.diskGroup.name=CRS
oracle.install.asm.diskGroup.redundancy=NORMAL
oracle.install.asm.diskGroup.AUSize=4
oracle.install.asm.diskGroup.disksWithFailureGroupNames=/dev/oracleasm/disks/CRS1,,/dev/oracleasm/disks/CRS2,,/dev/oracleasm/disks/CRS3,
oracle.install.asm.diskGroup.disks=/dev/oracleasm/disks/CRS1,/dev/oracleasm/disks/CRS2,/dev/oracleasm/disks/CRS3
oracle.install.asm.diskGroup.diskDiscoveryString=/dev/oracleasm/disks/*
oracle.install.asm.gimrDG.AUSize=1
oracle.install.asm.configureAFD=false
oracle.install.crs.configureRHPS=false
oracle.install.crs.config.ignoreDownNodes=false
oracle.install.config.managementOption=NONE
oracle.install.config.omsPort=0
oracle.install.crs.rootconfig.executeRootScript=true
oracle.install.crs.rootconfig.configMethod=ROOT
$GRID_HOME/gridSetup.sh -silent -ignorePrereqFailure
-responseFile /home/grid/grid.rsp -waitForCompletion -applyRU
/tmp/patch/37957391
--> 이렇게 설치하면됨
-----------------------------만약 silent 모드로 설치한다면
종료-----------------------------------
[+ASM1:grid@ol8ora19rf1][/home/grid]$ alias
csrt
alias csrt='crsctl
stat res -t'
[+ASM1:grid@ol8ora19rf1][/home/grid]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE
ol8ora19rf1
STABLE
ONLINE ONLINE
ol8ora19rf2
STABLE
ora.chad
ONLINE ONLINE
ol8ora19rf1
STABLE
ONLINE ONLINE
ol8ora19rf2
STABLE
ora.net1.network
ONLINE ONLINE
ol8ora19rf1
STABLE
ONLINE ONLINE
ol8ora19rf2
STABLE
ora.ons
ONLINE ONLINE
ol8ora19rf1
STABLE
ONLINE ONLINE
ol8ora19rf2
STABLE
ora.proxy_advm
OFFLINE OFFLINE
ol8ora19rf1
STABLE
OFFLINE OFFLINE
ol8ora19rf2
STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
2 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.CRS.dg(ora.asmgroup)
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
2 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.asm(ora.asmgroup)
1 ONLINE
ONLINE
ol8ora19rf1
Started,STABLE
2 ONLINE
ONLINE
ol8ora19rf2
Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
2 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.cvu
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.ol8ora19rf1.vip
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.ol8ora19rf2.vip
1 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.qosmserver
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.scan1.vip
1 ONLINE
ONLINE
ol8ora19rf2
STABLE
ora.scan2.vip
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
ora.scan3.vip
1 ONLINE
ONLINE
ol8ora19rf1
STABLE
--------------------------------------------------------------------------------
--설치가 완료되면 반드시 모든 노드를 reboot 시킨 후 crs가 잘올라오는지 확인해봐야함
--잘올라오지 않는다면 파일의 권한이 아래와 같은지 확인해볼 것
[+ASM1:grid@ol8ora19rf1][/u01/app/oracle]$ ls -l
$ORACLE_BASE
total 4
drwxr-x---. 3 grid oinstall
18 Sep 29 22:48 admin
drwxrwxr-x. 7
grid oinstall 74 Sep 29 22:52 cfgtoollogs
drwxr-xr-x. 2 grid
oinstall 6 Sep 29 22:53 checkpoints
drwxrwxr-x. 6 grid oinstall
68 Sep 29 22:46 crsdata
drwxrwx---. 23 grid
oinstall 4096 Sep 29 22:45 diag
drwxr-x--x. 3
root root 18 Sep 29 22:47
oracle.ahf
drwxrwxr-x. 3 oracle
oinstall 17 Sep 29 00:06 product
[+ASM1:grid@ol8ora19rf1][/u01/app/oracle]$ ls -l
$ORACLE_BASE/diag
total 0
drwxrwx---. 2 grid
oinstall 6 Sep 29 22:45 afdboot
drwxrwx---. 2 grid
oinstall 6 Sep 29 22:45 apx
drwxrwx---. 3 grid
oinstall 18 Sep 29 22:48 asm
drwxrwxr-x. 4 grid oinstall
40 Sep 29 22:57 asmcmd
drwxrwx---. 4 grid oinstall 40
Sep 29 22:46 asmtool
drwxrwx---. 2 grid oinstall 6
Sep 29 22:45 bdsql
drwxrwx---. 2 grid oinstall 6
Sep 29 22:45 clients
drwxrwxrwt. 3 grid oinstall 25 Sep
29 22:46 crs
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 diagtool
drwxrwx---. 2 grid oinstall 6 Sep
29 22:45 dps
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 em
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 gsm
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 ios
drwxrwxrwt. 3 grid oinstall 25 Sep 29 22:46
kfod
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
lsnrctl
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
netcman
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
ofm
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
plsql
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
plsqlapp
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 rdbms
drwxrwx---. 3 grid oinstall 25 Sep 29 22:49
tnslsnr
[+ASM2:grid@ol8ora19rf2][/home/grid]$ ls -l
$ORACLE_BASE
total 4
drwxr-x---. 3 grid oinstall
18 Sep 29 22:52 admin
drwxrwxr-x. 5
grid oinstall 48 Sep 29 22:50 cfgtoollogs
drwxrwxr-x. 6 grid oinstall
68 Sep 29 22:50 crsdata
drwxrwx---. 23 grid
oinstall 4096 Sep 29 22:45 diag
drwxr-x--x. 3
root root 18 Sep 29 22:51
oracle.ahf
drwxrwxr-x. 3 oracle
oinstall 17 Sep 29 00:06 product
[+ASM2:grid@ol8ora19rf2][/home/grid]$ ls -l
$ORACLE_BASE/diag
total 0
drwxrwx---. 2 grid
oinstall 6 Sep 29 22:45 afdboot
drwxrwx---. 2 grid
oinstall 6 Sep 29 22:45 apx
drwxrwx---. 3 grid
oinstall 18 Sep 29 22:51 asm
drwxrwxr-x. 4 grid oinstall
40 Sep 29 22:57 asmcmd
drwxrwx---. 4 grid oinstall 40
Sep 29 22:51 asmtool
drwxrwx---. 2 grid oinstall 6
Sep 29 22:45 bdsql
drwxrwx---. 3 grid oinstall 23 Sep 29
22:51 clients
drwxrwxrwt. 3 grid oinstall 25 Sep 29
22:50 crs
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 diagtool
drwxrwx---. 2 grid oinstall 6 Sep
29 22:45 dps
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 em
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 gsm
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 ios
drwxrwxrwt. 3 grid oinstall 25 Sep 29 22:50
kfod
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
lsnrctl
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
netcman
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
ofm
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
plsql
drwxrwx---. 2 grid oinstall 6 Sep 29 22:45
plsqlapp
drwxrwx---. 2 grid oinstall 6 Sep 29
22:45 rdbms
drwxrwx---. 3 grid oinstall 25 Sep 29 22:51
tnslsnr
--grid 설치가 모두 완료되면 아래 절차에 따라서
ASM 디스크를 구성해야함
[+ASM1:grid@ol8ora19rf1][/home/grid]$
asmca
ASM -> Disk
Groups -> Create
Disk Group name : DATA1
Redundancy : External
/dev/oracleasm/disks/DATA1_1 선택 후 OK
ASM -> Disk
Groups -> Create
Disk Group name : FRA1
Redundancy : External
/dev/oracleasm/disks/FRA1 선택 후 OK
Exit
-----------------------------DATA1 및 FRA1 디스크 그룹을 수동으로
생성한다면 시작---------------------------------
sqlplus "/as
sysasm"
SQL>
set lines 200
col group_number for 99
col path
for a30
col name for a14
select
group_number, disk_number, name, mount_status, path, total_mb, free_mb
from v$asm_disk
order by
1,3;
GROUP_NUMBER
DISK_NUMBER NAME
MOUNT_STATUS
PATH
TOTAL_MB FREE_MB
------------
----------- -------------- --------------------- ------------------------------
---------- ----------
0
0
CLOSED
/dev/oracleasm/disks/DATA1_1
0 0
0
1
CLOSED
/dev/oracleasm/disks/FRA1_1
0 0
1 0
CRS_0000
CACHED
/dev/oracleasm/disks/CRS1
2044 1716
1 1
CRS_0001
CACHED
/dev/oracleasm/disks/CRS2
2044 1712
1 2
CRS_0002
CACHED
/dev/oracleasm/disks/CRS3
2044 1716
SQL>
col COMPATIBILITY
form a10
col DATABASE_COMPATIBILITY form a30
col NAME form a20
select
group_number, name, compatibility, database_compatibility
from v$asm_diskgroup;
GROUP_NUMBER
NAME
COMPATIBIL DATABASE_COMPATIBILITY
------------
-------------------- ---------- ------------------------------
1
CRS
19.0.0.0.0 10.1.0.0.0
SQL> create
diskgroup DATA1 external redundancy disk '/dev/oracleasm/disks/DATA1_1'
ATTRIBUTE 'au_size'='4M', 'compatible.asm'='19.5', 'compatible.rdbms'='19.0',
'compatible.advm'='19.5';
SQL> create diskgroup FRA1
external redundancy disk '/dev/oracleasm/disks/FRA1_1' ATTRIBUTE 'au_size'='4M',
'compatible.asm'='19.5', 'compatible.rdbms'='19.0',
'compatible.advm'='19.5';
SQL>
SET LINE 200
COL DISK_GROUP FOR A10
COL LABEL
FOR A10
COL STATE FOR A10
SELECT
A.NAME AS DISK_GROUP, D.NAME "LABEL", A.STATE
FROM
V$ASM_DISK D, V$ASM_DISKGROUP A
WHERE
D.GROUP_NUMBER=A.GROUP_NUMBER
ORDER BY
2;
DISK_GROUP
LABEL STATE
----------
---------- ----------
CRS
CRS_0000 MOUNTED
CRS
CRS_0001 MOUNTED
CRS
CRS_0002 MOUNTED
DATA1 DATA1_0000 MOUNTED
FRA1 FRA1_0000
MOUNTED
SQL>
col COMPATIBILITY
form a10
col DATABASE_COMPATIBILITY form a30
col NAME form a20
select
group_number, name, compatibility, database_compatibility
from v$asm_diskgroup;
GROUP_NUMBER
NAME
COMPATIBIL DATABASE_COMPATIBILITY
------------
-------------------- ---------- ------------------------------
1
CRS
19.0.0.0.0 10.1.0.0.0
2
DATA1
19.5.0.0.0 19.0.0.0.0
3
FRA1
19.5.0.0.0 19.0.0.0.0
--수동으로 고친다면 아래와 같은 방법으로
하면됨
SQL> alter
diskgroup DATA1 SET ATTRIBUTE 'compatible.asm' = '19.5';
SQL>
col name for a50
col value for
a30
select b.name, a.name, a.value from v$asm_attribute
a, v$asm_diskgroup b where a.GROUP_NUMBER = b.group_number and a.name like
'%compatible%';
NAME
NAME
VALUE
------ -------------------------------------
------------
CRS
compatible.advm
19.0.0.0.0
CRS
compatible.rdbms
10.1.0.0.0
CRS
compatible.asm
19.0.0.0.0
DATA1
compatible.advm
19.5.0.0.0
DATA1
compatible.rdbms
19.0.0.0.0
DATA1
compatible.asm
19.5.0.0.0
DATA1
compatible.patch.asm.doubleparity ENABLED
DATA1 compatible.patch.asm.containerparity
ENABLED
FRA1
compatible.asm
19.5.0.0.0
FRA1
compatible.rdbms
19.0.0.0.0
FRA1
compatible.advm
19.5.0.0.0
FRA1
compatible.patch.asm.doubleparity ENABLED
FRA1 compatible.patch.asm.containerparity
ENABLED
-----------------------------DATA1 및 FRA1 디스크 그룹을 수동으로
생성한다면 종료---------------------------------
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl stat res -t |
grep -i dg
ora.CRS.dg(ora.asmgroup)
ora.DATA1.dg(ora.asmgroup)
ora.FRA1.dg(ora.asmgroup)
7. Oracle 엔진 설치 (UI 모드) (ol8ora19rf1 노드, oracle os user)
7-1. 오라클 엔진 파일 복사 및 압축 해제
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv
$ORACLE_HOME
--/home/oracle 위치에
LINUX.X64_193000_db_home.zip 파일 복사
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ unzip -q
LINUX.X64_193000_db_home.zip -d $ORACLE_HOME
--ol8ora19rf2 서버에 접속해서 아래와 같이
권한을 줌
--root os
user
[root@ol8ora19rf2][/root]$ chmod -R 775 /u01/app/oracle
[root@ol8ora19rf2][/root]$ mkdir -p
/u01/app/oracle/product
[root@ol8ora19rf2][/root]$ chown
-R oracle:oinstall /u01/app/oracle/product
[root@ol8ora19rf2][/root]$ mkdir -p
/u01/app/oracle/product/19c/db_1
[root@ol8ora19rf2][/root]$ chmod -R 775
/u01/app/oracle/product/19c/db_1
[root@ol8ora19rf2][/root]$ chown -R oracle:oinstall
/u01/app/oracle/product/19c/db_1
7-2. 오라클 엔진 설치
--Oracle Linux 8에서 Oracle 19c 설치 시 반드시 ApplyRU로 설치해야함
--------------------------Apply RU로 설치한다면
START---------------------------
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mv
$ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.bak
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ unzip -q
/tmp/patch/p6880880_190000_Linux-x86-64 -d $ORACLE_HOME
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ which
opatch
/u01/app/oracle/product/19c/db_1/OPatch/opatch
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ opatch version
-oh $ORACLE_HOME
OPatch Version: 12.2.0.1.47
OPatch succeeded.
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export
CV_ASSUME_DISTID=OL7
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
$ORACLE_HOME/runInstaller -applyRU /tmp/patch/37957391/
--------------------------Apply RU로 설치한다면
END---------------------------
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export
CV_ASSUME_DISTID=OL7
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
$ORACLE_HOME/runInstaller
Install database
software only -> Next
Oracle Real Application
Clusters database installation -> Next
SSH
Connectivity -> oracle os 유저의 패스워드 입력 -> Test -> Next
Enterprise Edition -> Next
Oracle base :
/u01/app/oracle
Software location :
/u01/app/oracle/product/19c/db_1
Next
dba, oper, backupdba, dgdba, kmdba, racdba
Next
Automatically Run
configuration scripts : 패스워드 1234
Package:
policycoreutils-python-2.5-17 - This is a prerequisite condition to test whether
the package "policycoreutils-python-2.5-17" is available on the system.
Check Failed on Nodes: [ol8ora19rf2,
ol8ora19rf1]
Verification result of failed node: ol8ora19rf2
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf2"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
Verification result of
failed node: ol8ora19rf1
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf1"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
--> 위와 같은
fail이 났지만 ignore All 체크 후 Next
Save Response File
(저장 경로 : /home/oracle/db.rsp)
Install 시작
----------------------------------------------Silent 모드로
설치한다면 시작-----------------------------------------------------
--db.rsp 파일 내용
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ awk -F= 'NF
&& $1 !~ /^#/ && $2!=""' /home/oracle/db.rsp >
/home/oracle/db_new.rsp
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat
/home/oracle/db_new.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT
oracle.install.db.CLUSTER_NODES=ol8ora19rf1,ol8ora19rf2
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.ConfigureAsContainerDB=false
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.omsPort=0
oracle.install.db.config.starterdb.enableRecovery=false
cd
$ORACLE_HOME/database
./runInstaller -silent -ignorePrereqFailure
-ignoreSysPrereqs -showProgress -responseFile /home/oracle/db.rsp
-executePrereqs --> 이걸로 일단 검사해봄
--------------------------Apply RU로 설치한다면
START----------------------------------------------------------------------------------------------------------
./runInstaller
-silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -responseFile
/home/oracle/db.rsp -waitForCompletion -applyRU
/tmp/patch/37957391
--------------------------Apply RU로 설치한다면
END------------------------------------------------------------------------------------------------------------
./runInstaller
-silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -responseFile
/home/oracle/db.rsp -waitForCompletion
----------------------------------------------Silent 모드로
설치한다면
종료-----------------------------------------------------
--2025.09.28
--오라클 엔진 설치 후 양쪽 노드를 reoot하여 CRS가 정상 구동되는지 확인해봐야함
--실제로 재기동 후 노드 2번이 CRS 기동이 안되어 원인을 찾던 중 아래의 권한문제인 것을 알고
해결함
[root@ol8ora19rf2][/root]$ ls -l
/u01/app/oracle
total 4
drwxrwxr-x. 3
oracle oinstall 18 Sep 28 17:18 admin
drwxrwxr-x. 5 oracle oinstall
48 Sep 28 17:16 cfgtoollogs
drwxrwxr-x. 6
oracle oinstall 68 Sep 28 17:16 crsdata
drwxrwxr-x. 23 oracle oinstall 4096 Sep 28
17:08 diag
drwxrwxr-x. 3 oracle
oinstall 18 Sep 28 17:17
oracle.ahf
drwxrwxr-x. 3
oracle oinstall 17 Sep 28 16:26 product
--> 이렇게 되어
있었음
--정상적으로 올라오는 노드 1번에서 같은 경로를 확인
결과
[root@ol8ora19rf1][/root]$ ls -l
/u01/app/oracle
total 4
drwxr-x---. 3 grid oinstall
18 Sep 28 17:13 admin
drwxrwxr-x. 7
grid oinstall 74 Sep 28 17:19 cfgtoollogs
drwxr-xr-x. 2 grid
oinstall 6 Sep 28 17:20 checkpoints
drwxrwxr-x. 6 grid oinstall
68 Sep 28 17:11 crsdata
drwxrwxr-x. 23 grid
oinstall 4096 Sep 28 17:08 diag
drwxr-x--x. 3
root root 18 Sep 28 17:12
oracle.ahf
drwxrwxr-x. 3 oracle
oinstall 17 Sep 28 16:26 product
--> 이렇게
되어 있었음
--> 노드 2번에서 아래의 명령으로 권한을
맞춰줌
[root@ol8ora19rf2][/u01/app/oracle]$ chown -R grid:oinstall
admin
[root@ol8ora19rf2][/u01/app/oracle]$ chown -R
grid:oinstall cfgtoollogs
[root@ol8ora19rf2][/u01/app/oracle]$ chown -R grid:oinstall
crsdata
[root@ol8ora19rf2][/u01/app/oracle]$ chown -R
grid:oinstall diag
[root@ol8ora19rf2][/u01/app/oracle]$
chown root:root oracle.ahf
--> 위의 조치 노드 2번을 재부팅하면 CRS가 제대로 구동되는 것을
확인함
8. oracle 인스턴스 설치 (UI 모드)
8-1. 설치 전 디렉토리 생성 (ol8ora19rf1, ol8ora19rf2 노드 모두, grid os user)
--ol8ora19rf1
--grid os user
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/u01/app/oracle/audit
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/u01/app/oracle/admin
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/u01/app/oracle/cfgtoollogs/dbca
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/u01/app/oracle/cfgtoollogs/sqlpatch
--ol8ora19rf2
--grid os user
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/u01/app/oracle/audit
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/u01/app/oracle/admin
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/u01/app/oracle/cfgtoollogs/dbca
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/u01/app/oracle/cfgtoollogs/sqlpatch
8-2. 디렉토리 권한 변경 (ol8ora19rf1, ol8ora19rf2 노드 모두, root os user)
--ol8ora19rf1
--root os user
[root@ol8ora19rf1][/root]$ chmod 770
/u01/app/oracle/audit
[root@ol8ora19rf1][/root]$ chmod
770 /u01/app/oracle/admin
[root@ol8ora19rf1][/root]$
chmod 770 /u01/app/oracle/cfgtoollogs/dbca
[root@ol8ora19rf1][/root]$ chmod 775
/u01/app/oracle/cfgtoollogs/sqlpatch
--ol8ora19rf2
--root os user
[root@ol8ora19rf2][/root]$ chmod 770
/u01/app/oracle/audit
[root@ol8ora19rf2][/root]$ chmod
770 /u01/app/oracle/admin
[root@ol8ora19rf2][/root]$
chmod 770 /u01/app/oracle/cfgtoollogs/dbca
[root@ol8ora19rf2][/root]$ chmod 775
/u01/app/oracle/cfgtoollogs/sqlpatch
--> 디렉토리 소유자를 grid user로 하고 해당 디렉토리에 대해 oracle user도 읽기/쓰기/실행 권한을 주기 위해 root user로 권한을 주는 것임
8-3. 인스턴스 생성 (ol8ora19rf1, oracle os user)
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ export
CV_ASSUME_DISTID=OL7
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
dbca
Create a database -> Next
Advanced configuration -> Next
Database Type : RAC
Configuration
type : Admin Managed
General Purpose or Transaction
Processing
Next
ol8ora19rf1,
ol8ora19rf2 모두 선택 후 Next
Global database name :
ORA19RF
SID Prefix : ORA19RF
Create as Container Database : 체크 해제
Next
Database files Storage Type :
ASM
Database files location : +DATA1/{DB_UNIQUE_NAME}
Use Oracle-Managed Files (OMF): 체크
Multiplex redo logs and control files : +DATA1, +FRA1 ->
ok
Next
Specifiy Fast
Recovery Area -> 체크해제
--Recovery files Storage type : ASM
--Fast Recovery Area : +FRA1
--Fast Recovery Area Size : 45874MB (65535*0.7)
Enable archivning : 체크 -> Edit archive mode
parmaters -> Archive log file format : %t_%s_%r.ARC -> OK, +FRA1
Next
Oracle Database Vault 구성 체크
해제
Oracle Label Security 구성 체크 해제
Next
Memory 설정
Use Automatic Shared Memory Managent 선택 시
--------------------------노드 별 물리메모리가 10기가인 경우
시작----------------------------
물리 메모리의 40%를 SGA와
PGA할당함
물리 메모리의 40%는 10240*0.4 = 4096MB
SGA Size : 3072MB (4096MB * 0.75)
PGA Size : 1024MB (4096MB * 0.25)
Use Manual Shared Memory Management를 선택 시
총 4096 MB로 맞춤
Shared Pool Size :
1024 MB
Buffer Cache Size : 1216 MB
Java Pool Size : 160 MB
Large Pool
Size : 512 MB
PGA Size : 1024 MB
Stream Pool Size : 160 MB --> 이건 입력항목이 없으므로 추후 설정
--------------------------노드 별 물리메모리가 10기가인 경우
종료----------------------------
--------------------------노드 별 물리메모리가 16기가인 경우
시작----------------------------
물리 메모리의 40%를 SGA와
PGA할당함
물리 메모리의 40%는 16384*0.4 = 6553 MB
SGA Size : 4915MB (4096MB * 0.75)
PGA Size : 1638MB (4096MB * 0.25)
Use Manual Shared Memory Management를 선택 시
총 6553 MB로 맞춤
Shared Pool Size :
1024 MB
Buffer Cache Size : 3673 MB
Java Pool Size : 160 MB
Large Pool
Size : 512 MB
PGA Size : 1024 MB
Stream Pool Size : 160 MB --> 이건 입력항목이 없으므로 추후 설정
--------------------------노드 별 물리메모리가 16기가인 경우
종료----------------------------
Sizing
설정
Processes : 1432
(oracle ulimit의 max user processes설정보다 작아야함. 2047일경우 : 2047*0.7=1432)
Chracter sets 설정
Choose from the
list of character sets 선택 : KO16MSWIN949
National
character set : AL16UTF16
Default Language : American
Default territory : Korea
Connection Mode : Dedicated Server Mode
Sample schemas : Add Sample schemas to the database 체크
Next
Run Cluster Verification
Utility Checks Periodically : 체크 해제
EM 체크 해제
Next
Use the same administrative
password for all accounts 선택 -> 1234
Next
Create Database 체크
Generate
database creation scripts 체크 ->
{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/scripts
Customize
Storage Locations 클릭
Control Files
Maximum Datafiles : 1000
Redo Log
Groups
1, 2, 3 : Thread 1으로, 204800 KB로
4, 5, 6 : Thread 2으로, 204800 KB로
All Initialization Parameters... -> Show advanced
parameters 체크
optimizer_adaptive_plans : false, include
in spfile 체크
optimizer_adaptive_reporting_only : true,
include in spfile 체크
optimizer_dynamic_sampling : 0,
include in spfile 체크
parallel_force_local : true,
include in spfile 체크
parallel_min_servers : 0, include
in spfile 체크
pga_aggregate_limit : 0, include in spfile
체크
undo_retention : 3600, include in spfile 체크
close
Next
Package:
policycoreutils-python-2.5-17 - This is a prerequisite condition to test whether
the package "policycoreutils-python-2.5-17" is available on the system.
Check Failed on Nodes: [ol8ora19rf2,
ol8ora19rf1]
Verification result of failed node:
ol8ora19rf2
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf2"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
Verification result of
failed node: ol8ora19rf1
Expected Value
: policycoreutils-python-2.5-17
Actual Value
: missing
Details:
-
PRVF-7532 : Package "policycoreutils-python" is missing on
node "ol8ora19rf1"
- Cause: A required
package is either not installed or, if the package is a kernel module, is not
loaded on the specified node.
- Action:
Ensure that the required package is installed and available.
Back to Top
-- 위와 같은 Failed가
났지만 ignore all로 하고 Next
Save Response File -> /home/oracle/dbca.rsp
Finish하면 설치 시작!
Pawssword
Management 에서 HR, OUTLN은 Lock Account 해제하고 비밀번호 1234로 세팅
-------------------------------------Silent 모드로 설치 한다면
시작---------------------------------------------
--dbca.rsp 내용
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ awk -F= 'NF
&& $1 !~ /^#/ && $2!=""' /home/oracle/dbca.rsp >
/home/oracle/dbca_new.rsp
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat
/home/oracle/dbca_new.rsp
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=ORA19RF
sid=ORA19RF
databaseConfigType=RAC
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
numberOfPDBs=0
useLocalUndoForPDBs=true
nodelist=ol8ora19rf1,ol8ora19rf2
templateName=/u01/app/oracle/product/19c/db_1/assistants/dbca/templates/General_Purpose.dbc
systemPassword=
emExpressPort=5500
runCVUChecks=FALSE
omsPort=0
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=+DATA1/{DB_UNIQUE_NAME}/
storageType=ASM
diskGroupName=+DATA1/{DB_UNIQUE_NAME}/
characterSet=KO16MSWIN949
nationalCharacterSet=AL16UTF16
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/19c/db_1,DB_UNIQUE_NAME=ORA19RF,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=ORA19RF,ORACLE_HOME=/u01/app/oracle/product/19c/db_1,SID=ORA19RF
initParams=ORA19RF1.undo_tablespace=UNDOTBS1,ORA19RF2.undo_tablespace=UNDOTBS2,optimizer_adaptive_reporting_only=TRUE,sga_target=4794MB,parallel_min_servers=0,db_block_size=8192BYTES,cluster_database=true,optimizer_dynamic_sampling=0,family:dw_helper.instance_mode=read-only,log_archive_dest_1='LOCATION=+FRA1',nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP)
(SERVICE=ORA19RFXDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=exclusive,pga_aggregate_limit=0MB,db_create_file_dest=+DATA1/{DB_UNIQUE_NAME}/,db_create_online_log_dest_2=+FRA1,db_create_online_log_dest_1=+DATA1,parallel_force_local=TRUE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=1432,pga_aggregate_target=1599MB,ORA19RF1.thread=1,ORA19RF2.thread=2,nls_territory=KOREA,undo_retention=3600,local_listener=-oraagent-dummy-,optimizer_adaptive_plans=FALSE,open_cursors=300,log_archive_format=%t_%s_%r.ARC,compatible=19.0.0,db_name=ORA19RF,ORA19RF1.instance_number=1,ORA19RF2.instance_number=2,audit_trail=db
sampleSchema=true
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0
--oracle os user
dbca -silent
-ignorePrereqFailure -createDatabase -responseFile
/home/oracle/dbca.rsp
-------------------------------------Silent 모드로 설치 한다면
시작---------------------------------------------
--설치가 끝나면
[root@ol8ora19rf1][/root]$ crsctl stop crs
[root@ol8ora19rf2][/root]$ crsctl stop crs
[root@ol8ora19rf1][/root]$ reboot
[root@ol8ora19rf2][/root]$ reboot
--재기동 후
grid, oracle 모두 정상인지 확인 할 것
--재기동 후 ASM DISK 인식을 못하는 경우
[+ASM2:grid@ol8ora19rf2][/home/grid]$
/u01/app/19c/grid/bin/kfed read /dev/oracleasm/disks/CRS1 | egrep
'grpname|type:|dsknum|vfstart|au'
[+ASM2:grid@ol8ora19rf2][/home/grid]$
/u01/app/19c/grid/bin/kfed read /dev/oracleasm/disks/CRS2 | egrep
'grpname|type:|dsknum|vfstart|au'
[+ASM2:grid@ol8ora19rf2][/home/grid]$
/u01/app/19c/grid/bin/kfed read /dev/oracleasm/disks/CRS3 | egrep
'grpname|type:|dsknum|vfstart|au'
[+ASM2:grid@ol8ora19rf2][/home/grid]$
/u01/app/19c/grid/bin/kfed read /dev/oracleasm/disks/DATA1_1 | egrep
'grpname|type:|dsknum|vfstart|au'
[+ASM2:grid@ol8ora19rf2][/home/grid]$
/u01/app/19c/grid/bin/kfed read /dev/oracleasm/disks/FRA1_1 | egrep
'grpname|type:|dsknum|vfstart|au'
--> 위의 명령어로 공유 디스크의 메타 데이터를 확인해 볼것 (정확한
디스크와 연결되어 있는지 확인해 볼것)
--> 여기서 VM 자체를 압축파일로 갖고 있는 것도 좋음
9. 설치 후 필수 설정
9-1. 리스너 추가 (리스너 추가 시 아래의 절차를 따름)
9-1-1. 기존 리스너 중지 및 비활성화
--grid os
user
srvctl stop
listener -listener LISTENER
srvctl disable listener
-listener LISTENER
9-1-2. 신규 리스너 추가
--grid os
user
srvctl add
listener -listener LISTENER_ORA19RF -p 1721
9-1-3. ol8ora19rf1 node의 +ASM1 인스턴스에서 local_listener 파라미터 설정 (grid os user)
sqlplus "/as
sysasm"
SQL> alter system set
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.31)(PORT=1721))'
scope=both sid='+ASM1';
SQL> alter system
register;
9-1-4. ol8ora19rf2 node의 +ASM2 인스턴스에서 local_listener 파라미터 설정 (grid os user)
sqlplus "/as
sysasm"
SQL> alter system set
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.32)(PORT=1721))'
scope=both sid='+ASM2';
SQL> alter system
register;
9-1-5. ol8ora19rf1 node의 ORA19RF1 인스턴스에서 local_listener 파라미터 설정
sqlplus "/as
sysdba"
SQL> alter system set
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.31)(PORT=1721))'
scope=both sid='ORA19RF1';
SQL> alter system
register;
9-1-6. ol8ora19rf2 node의 ORA19RF2 인스턴스에서 local_listener 파라미터 설정
sqlplus "/as
sysdba"
SQL> alter system set
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.240.32)(PORT=1721))'
scope=both sid='ORA19RF2';
SQL> alter system
register;
9-1-7. LISTENER_ORA19RF 올리기 및 활성화
srvctl start
listener -listener LISTENER_ORA19RF
srvctl enable
listener -listener LISTENER_ORA19RF
9-2. SQLNET 설정
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >>
$GRID_HOME/network/admin/sqlnet.ora << 'EOF'
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
#EXPIRE_TIME=10
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat
$GRID_HOME/network/admin/sqlnet.ora
#
sqlnet.ora.ol8ora19rf1 Network Configuration File:
/u01/app/19c/grid/network/admin/sqlnet.ora.ol8ora19rf1
#
Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
#EXPIRE_TIME=10
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >>
$GRID_HOME/network/admin/sqlnet.ora << 'EOF'
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
#EXPIRE_TIME=10
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat
$GRID_HOME/network/admin/sqlnet.ora
#
sqlnet.ora.ol8ora19rf2 Network Configuration File:
/u01/app/19c/grid/network/admin/sqlnet.ora.ol8ora19rf2
#
Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
#EXPIRE_TIME=10
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl stop listener
-listener LISTENER -node ol8ora19rf1
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl start listener
-listener LISTENER -node ol8ora19rf1
[+ASM2:grid@ol8ora19rf2][/home/grid]$ srvctl stop listener
-listener LISTENER -node ol8ora19rf1
[+ASM2:grid@ol8ora19rf2][/home/grid]$ srvctl start listener
-listener LISTENER -node ol8ora19rf1
9-3.프로세스 우선 순위 확인 및 설정
--이건 연습임, 테스트 환경에서는 설정하지말것, 운영환경에서도 철저한 검증이 필요
9-3-1. CSS 프로세스 우선 순위 확인 및 설정
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl get css
priority
CRS-4269: Successful get priority 4 (real-time) for Cluster
Synchronization Services.
[+ASM2:grid@ol8ora19rf2][/home/grid]$ crsctl get css
priority
CRS-4269: Successful get priority 4 (real-time) for Cluster
Synchronization Services.
--우선 순위가 4가 아닌
경우
crsctl set css
priority 4
--> 명령으로 세팅
해줌
9-3-2. chrt 명령을 사용한 real-time 우선 순위 확인 및 변경
[root@ol8ora19rf1][/root]$ chrt --max
SCHED_OTHER
min/max priority : 0/0
SCHED_FIFO
min/max priority : 1/99
SCHED_RR
min/max priority : 1/99
SCHED_BATCH min/max priority : 0/0
SCHED_IDLE min/max priority :
0/0
SCHED_DEADLINE min/max priority :
0/0
[root@ol8ora19rf1][/root]$ ps -eo
"pid,user,pri,nice,sched,command,args" | egrep 'lms|lgw|vktm' | grep -v egrep |
grep -v asm | grep -v grep | sort
8773
oracle 41 - 2
ora_vktm_ORA19RF1
ora_vktm_ORA19RF1
8824 oracle
19 0 0
ora_lms0_ORA19RF1
ora_lms0_ORA19RF1
8826 oracle
19 0 0
ora_lms1_ORA19RF1
ora_lms1_ORA19RF1
8862 oracle
19 0 0
ora_lgwr_ORA19RF1
ora_lgwr_ORA19RF1
--> 우선순위 변경
시
chrt -r -p 39
6450
--> chrt -r -p 우선순위
프로세스번호
--> 우선순위 확인
chrt -p 6450
9-3-3. ocssd.bin 프로세스 우선 순위 확인 및 설정 (AIX Only) (AIX 인 경우를 정리해 놓은 것임)
--ocssd.bin 프로세스 우선 순위
확인
ps -eo
"pid,user,pri,nice,sched,command,args" | egrep 'ocssd.bin' | grep -v egrep |
grep -v asm | grep -v grep | sort
2308
grid 139 - 2
/u01/app/19c/grid/bin/ocssd /u01/app/19c/grid/bin/ocssd.bin
ocssd.bin 프로세스의 우선순위를 0으로
변경
renice 0 -p <pid>
9-4. 파일 권한 확인
--> 모든 노드에서 확인해야함!
9-4-1. oracle 유저 기준 (아래와 동일하면 정상)
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ ls -al
$ORACLE_HOME/bin | egrep "extjob$|jssu$|oracle$|oradism"
-rwsr-x---.
1 root oinstall 2241432 Oct 24 21:57 extjob
-rwsr-x---. 1 root oinstall
2252256 Oct 22 00:14 jssu
-rwsr-s--x. 1 oracle
asmadmin 408794912 Oct 24 21:57 oracle
-rwsr-x---.
1 root oinstall 95844 Jan 26 2017
oradism
9-4-2. grid 유저 기준
(아래와 동일하면 정상)
[+ASM1:grid@ol8ora19rf1][/home/grid]$ ls -al $GRID_HOME/bin
| egrep "extjob$|jssu$|oracle$|oradism"
-rwsr-x---.
1 root oinstall 2241432 Oct 24 21:50 extjob
-rwsr-x---. 1 root oinstall 2252256 Oct
20 23:21 jssu
-rwsr-s--x. 1 grid oinstall
373530032 Oct 24 21:50 oracle
-rwsr-x---. 1 root
oinstall 95844 Jan 26 2017
oradism
9-5. Cluster Health Monitor 끄기
--root os user로
돌려야함
--1번 노드
[root@ol8ora19rf1][/root]$ crsctl stat res ora.crf -init -p
| grep ENABLED
ENABLED=1
[root@ol8ora19rf1][/root]$ crsctl modify resource ora.crf
-attr "ENABLED=0" -init
[root@ol8ora19rf1][/root]$
crsctl stat res ora.crf -init -p | grep ENABLED
ENABLED=0
--root os user로
돌려야함
--2번 노드
[root@ol8ora19rf2][/root]$ crsctl stat res ora.crf -init -p
| grep ENABLED
ENABLED=1
[root@ol8ora19rf2][/root]$ crsctl modify resource ora.crf
-attr "ENABLED=0" -init
[root@ol8ora19rf2][/root]$
crsctl stat res ora.crf -init -p | grep ENABLED
ENABLED=0
9-6. Trace File Analyzer 끄기
[root@ol8ora19rf1][/root]$ tfactl print
status
.---------------------------------------------------------------------------------------------------.
| Host | Status
of TFA | PID | Port | Version | Build
ID
| Inventory Status |
+-------------+---------------+------+------+------------+-----------------------+------------------+
| ol8ora19rf2 | RUNNING
| 1821 | 5000 | 25.5.1.0.0 | 250510020250626113556 |
COMPLETE |
| ol8ora19rf1 | RUNNING
| 1890 | 5000 | 25.5.1.0.0 | 250510020250626113556 |
COMPLETE |
'-------------+---------------+------+------+------------+-----------------------+------------------'
[root@ol8ora19rf1][/root]$ tfactl stop
[root@ol8ora19rf1][/root]$ tfactl disable
[root@ol8ora19rf1][/root]$ /etc/init.d/init.tfa stop
[root@ol8ora19rf1][/root]$ tfactl print status
TFA-00002 Oracle
Trace File Analyzer (TFA) is not running
TFA-00106 TFA
Shutdown/Stopped by user
[root@ol8ora19rf2][/root]$ tfactl print
status
.---------------------------------------------------------------------------------------------------.
| Host | Status
of TFA | PID | Port | Version | Build
ID
| Inventory Status |
+-------------+---------------+------+------+------------+-----------------------+------------------+
| ol8ora19rf1 | NOT RUNNING |
- |
|
|
|
|
| ol8ora19rf2 |
RUNNING | 1890 | 5000 | 25.5.1.0.0 |
250510020250626113556 | COMPLETE
|
'-------------+---------------+------+------+------------+-----------------------+------------------'
[root@ol8ora19rf2][/root]$ tfactl stop
[root@ol8ora19rf2][/root]$ tfactl disable
[root@ol8ora19rf2][/root]$ /etc/init.d/init.tfa stop
[root@ol8ora19rf2][/root]$ tfactl print status
TFA-00002 Oracle
Trace File Analyzer (TFA) is not running
TFA-00106 TFA
Shutdown/Stopped by user
9-7. MGMT 리스너 및 MGMT DB 끄기
--19c에서는 mgmt가 필수가 아니므로 처음부터
mgmt를 설치 안했으면 생략가능
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl disable mgmtdb
-node ol8ora19rf1
[+ASM1:grid@ol8ora19rf1][/home/grid]$
srvctl disable mgmtlsnr -node ol8ora19rf1
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl stop mgmtdb
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl stop
mgmtlsnr
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl
stop diskgroup -diskgroup MGMT
9-8. 스캔 및 스캔 리스너 끄기
--root os user 로 돌려야함
--1번 노드
[root@ol8ora19rf1][/root]$ srvctl disable scan -scannumber
1
[root@ol8ora19rf1][/root]$ srvctl disable scan
-scannumber 2
[root@ol8ora19rf1][/root]$ srvctl disable
scan -scannumber 3
[root@ol8ora19rf1][/root]$ srvctl
disable scan_listener -scannumber 1
[root@ol8ora19rf1][/root]$ srvctl disable scan_listener
-scannumber 2
[root@ol8ora19rf1][/root]$ srvctl disable
scan_listener -scannumber 3
--2번 노드
[root@ol8ora19rf2][/root]$ srvctl stop scan_listener
-scannumber 1
[root@ol8ora19rf2][/root]$ srvctl stop
scan_listener -scannumber 2
[root@ol8ora19rf2][/root]$
srvctl stop scan_listener -scannumber 3
[root@ol8ora19rf2][/root]$ srvctl stop scan -scannumber
1
[root@ol8ora19rf2][/root]$ srvctl stop scan
-scannumber 2
[root@ol8ora19rf2][/root]$ srvctl stop
scan -scannumber 3
9-9. CVU 끄기
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl stop cvu
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl disable
cvu
9-10. qosmserver 끄기
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl stop
qosmserver
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl
disable qosmserver
9-11. misscount=30 및 disktimeout=200 확인
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl get css
misscount
CRS-4678: Successful get misscount 30 for Cluster
Synchronization Services.
-->
30이 아니라면 아래의 명령으로 30으로 세팅
crsctl set css misscount 30
[+ASM2:grid@ol8ora19rf2][/home/grid]$ crsctl get css
misscount
CRS-4678: Successful get
misscount 30 for Cluster Synchronization Services.
--> 200이 아니라면 아래의 명령으로 200으로 세팅
crsctl set css
disktimeout 200
9-12. ora.cssd 프로세스에서 CRASHDUMP 설정
--1번 노드
[root@ol8ora19rf1][/root]$ crsctl stat res ora.cssd -init
-p | grep REBOOT_OPTS
REBOOT_OPTS=
[root@ol8ora19rf1][/root]$ crsctl modify res ora.cssd -attr
"REBOOT_OPTS=CRASHDUMP" -init
CRS-5134:
Crashdump from CSS was not enabled as Linux kdump is not active, enable the
Linux kdump and retry the command. See Action message for more
details.
--> CSSD (Cluster
Synchronization Service Daemon): RAC에서 노드 간 하트비트와 클러스터 멤버십을 관리하는 핵심 프로세스임
--> Oracle Clusterware는 노드가 비정상 종료되거나 split-brain 상황이
발생할 경우 crashdump (core dump) 를 남길 수 있는데, 이를 위해 OS 레벨의 kdump 기능이 필요함
--> 즉, Linux kdump(커널 crash dump) 이 활성화되어 있지 않아서
crashdump 옵션이 무효화된 상태라는 의미
--> 추후 kdump 켜고 테스트
해볼것
[root@ol8ora19rf1][/root]$ crsctl stat res ora.cssd -init
-p | grep REBOOT_OPTS
REBOOT_OPTS=CRASHDUMP
--> Oracle Clusterware 쪽에서는 crashdump를 하겠다고 설정했지만, OS
레벨에서 kdump가 비활성화되어 있으면 실제로는 dump가 남지 않음
[root@ol8ora19rf1][/root]$ crsctl stat res ora.cssdmonitor
-init -p | grep REBOOT_OPTS
REBOOT_OPTS=
[root@ol8ora19rf1][/root]$ crsctl modify res
ora.cssdmonitor -attr "REBOOT_OPTS=CRASHDUMP" -init
CRS-5134: Crashdump from CSS was not enabled as Linux kdump
is not active, enable the Linux kdump and retry the command. See Action message
for more details.
--> 위와 마찬가지의
상황임
[root@ol8ora19rf1][/root]$ crsctl stat res ora.cssdmonitor
-init -p | grep REBOOT_OPTS
REBOOT_OPTS=CRASHDUMP
--> 위와 마찬가지의 상황임
--2번 노드
[root@ol8ora19rf2][/root]$ crsctl stat res ora.cssd -init
-p | grep REBOOT_OPTS
REBOOT_OPTS=
[root@ol8ora19rf2][/root]$ crsctl modify res ora.cssd -attr
"REBOOT_OPTS=CRASHDUMP" -init
CRS-5134:
Crashdump from CSS was not enabled as Linux kdump is not active, enable the
Linux kdump and retry the command. See Action message for more
details.
--> 위와 마찬가지의
상황임
[root@ol8ora19rf2][/root]$ crsctl stat res ora.cssd -init
-p | grep REBOOT_OPTS
REBOOT_OPTS=CRASHDUMP
--> 위와 마찬가지의 상황임
[root@ol8ora19rf2][/root]$ crsctl stat res ora.cssdmonitor
-init -p | grep REBOOT_OPTS
REBOOT_OPTS=
[root@ol8ora19rf2][/root]$ crsctl modify res
ora.cssdmonitor -attr "REBOOT_OPTS=CRASHDUMP" -init
CRS-5134: Crashdump from CSS was not enabled as Linux kdump
is not active, enable the Linux kdump and retry the command. See Action message
for more details.
--> 위와
마찬가지의 상황임
[root@ol8ora19rf2][/root]$ crsctl stat res ora.cssdmonitor
-init -p | grep REBOOT_OPTS
REBOOT_OPTS=CRASHDUMP
--> 위와 마찬가지의 상황임
9-13. VIP 및 스캔 리소스 의존성 확인 (의존성 설정 방법은 추후 알아봐야함)
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl status
resource ora.ol8ora19rf1.vip -p | egrep
"^NAME|STOP_DEPENDENCIES=|START_DEPENDENCIES="
NAME=ora.ol8ora19rf1.vip
START_DEPENDENCIES=hard(ora.net1.network)
weak(global:ora.gns) pullup(ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
--VIP는 반드시
net1 네트워크가 살아 있어야 올라올 수 있고, 네트워크가 내려갈 때 VIP를 먼저 내림.
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl status
resource ora.ol8ora19rf2.vip -p | egrep
"^NAME|STOP_DEPENDENCIES=|START_DEPENDENCIES="
NAME=ora.ol8ora19rf2.vip
START_DEPENDENCIES=hard(ora.net1.network)
weak(global:ora.gns) pullup(ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
--VIP는 반드시
net1 네트워크가 살아 있어야 올라올 수 있고, 네트워크가 내려갈 때 VIP를 먼저 내림.
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl status
resource ora.scan1.vip -p | egrep
"^NAME|STOP_DEPENDENCIES=|START_DEPENDENCIES="
NAME=ora.scan1.vip
START_DEPENDENCIES=hard(ora.net1.network)
weak(global:ora.gns) dispersion:active(type:ora.scan_vip.type)
pullup:active(global:ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
--SCAN VIP도 마찬가지로 net1이 반드시 필요.
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl status
resource ora.scan2.vip -p | egrep
"^NAME|STOP_DEPENDENCIES=|START_DEPENDENCIES="
NAME=ora.scan2.vip
START_DEPENDENCIES=hard(ora.net1.network)
weak(global:ora.gns) dispersion:active(type:ora.scan_vip.type)
pullup:active(global:ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
--SCAN VIP도
마찬가지로 net1이 반드시 필요.
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crsctl status
resource ora.scan3.vip -p | egrep
"^NAME|STOP_DEPENDENCIES=|START_DEPENDENCIES="
NAME=ora.scan3.vip
START_DEPENDENCIES=hard(ora.net1.network)
weak(global:ora.gns) dispersion:active(type:ora.scan_vip.type)
pullup:active(global:ora.net1.network)
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
--SCAN VIP도
마찬가지로 net1이 반드시 필요.
9-14. chmdiag 및 nfsstat 비활성 (ol8ora19rf1, grid os user)
[+ASM1:grid@ol8ora19rf1][/home/grid]$ oclumon manage
-disable chmdiag
CHMDiag option is successfully Disabled on ol8ora19rf1
CHMDiag option is successfully Disabled on ol8ora19rf2
[+ASM1:grid@ol8ora19rf1][/home/grid]$ oclumon manage
-disable nfsstat
NFS stat collection is successfully Disabled on
ol8ora19rf1
NFS stat collection is successfully Disabled
on ol8ora19rf2
9-15. ASM 환경 필수 설정
9-15-1. ASM 인스턴스를 오렌지로 접속
--클라이언트의 tnsnames 설정을
해줌
ORA19RF1_+ASM1
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.240.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= +ASM)
)
)
ORA19RF2_+ASM2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = 192.168.240.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
--> 이렇게
하면 ASM 인스턴스에 오렌지로 붙을 수 있음
9-15-2. ASM 인스턴스 sqlplus로 붙을 때 glogin.sql 설정 (ol8ora19rf1 및
ol8ora19rf2, grid os user)
--1번 노드
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat
$GRID_HOME/sqlplus/admin/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
--
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >>
$GRID_HOME/sqlplus/admin/glogin.sql <<'EOF'
SET
TIMING OFF
SET HEADING OFF
SET
FEEDBACK OFF
SET TIMING OFF
SET
TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf1'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat
$GRID_HOME/sqlplus/admin/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
TIMING OFF
SET HEADING OFF
SET
FEEDBACK OFF
SET TIMING OFF
SET
TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf1'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
--2번 노드
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat
$GRID_HOME/sqlplus/admin/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
--
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >>
$GRID_HOME/sqlplus/admin/glogin.sql <<'EOF'
SET
TIMING OFF
SET HEADING OFF
SET
FEEDBACK OFF
SET TIMING OFF
SET
TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf2'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat
$GRID_HOME/sqlplus/admin/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
TIMING OFF
SET HEADING OFF
SET
FEEDBACK OFF
SET TIMING OFF
SET
TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf1'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
9-15-3. _rebalance_compact 설정을 FALSE로 설정 (ol8ora19rf1, grid os user)
--설정 확인
[+ASM1:grid@ol8ora19rf1][/home/grid]$ alias
sa
alias sa='rlwrap
sqlplus "/as sysasm"'
[+ASM1:grid@ol8ora19rf1][/home/grid]$
sa
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Oct 1 22:32:18 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@+ASM1>$
COL GROUP_KFENV
FOR 99999
COL NAME_KFENV FOR A20
COL VALUE_KFENV FOR A10
COL
DISK_GROUP_NAME FOR A20
SELECT dg.name AS
DISK_GROUP_NAME,
x.GROUP_KFENV,
x.NAME_KFENV,
x.VALUE_KFENV
FROM X$KFENV x
JOIN V$ASM_DISKGROUP dg
ON (x.GROUP_KFENV = dg.GROUP_NUMBER)
WHERE x.NAME_KFENV LIKE '%compact%'
;
DISK_GROUP_NAME GROUP_KFENV
NAME_KFENV
VALUE_KFEN
-------------------- -----------
-------------------- ----------
CRS
1 _rebalance_compact TRUE
DATA1
2 _rebalance_compact TRUE
FRA1
3 _rebalance_compact TRUE
[ol8ora19rf1]<SYS@+ASM1>$ ALTER DISKGROUP CRS SET ATTRIBUTE '_REBALANCE_COMPACT'='FALSE';
Diskgroup altered.
Elapsed:
00:00:00.01
[ol8ora19rf1]<SYS@+ASM1>$ ALTER DISKGROUP DATA1 SET ATTRIBUTE '_REBALANCE_COMPACT'='FALSE';
Diskgroup altered.
Elapsed: 00:00:00.00
[ol8ora19rf1]<SYS@+ASM1>$ ALTER DISKGROUP FRA1 SET ATTRIBUTE '_REBALANCE_COMPACT'='FALSE';
Diskgroup altered.
Elapsed: 00:00:00.00
[ol8ora19rf1]<SYS@+ASM1>$
COL GROUP_KFENV
FOR 99999
COL NAME_KFENV FOR A20
COL VALUE_KFENV FOR A10
COL
DISK_GROUP_NAME FOR A20
SELECT dg.name AS
DISK_GROUP_NAME
,
x.GROUP_KFENV
, x.NAME_KFENV
, x.VALUE_KFENV
FROM X$KFENV x
JOIN
V$ASM_DISKGROUP dg
ON (x.GROUP_KFENV
= dg.GROUP_NUMBER)
WHERE x.NAME_KFENV LIKE
'%compact%';
DISK_GROUP_NAME GROUP_KFENV
NAME_KFENV
VALUE_KFEN
-------------------- -----------
-------------------- ----------
CRS
1 _rebalance_compact FALSE
DATA1
2 _rebalance_compact FALSE
FRA1
3 _rebalance_compact FALSE
9-15-4. ASM 인스턴스 필수 파라미터 설정 (ol8ora19rf1, grid os user)
[ol8ora19rf1]<SYS@+ASM1>$ ALTER SYSTEM SET
"_enable_shared_pool_durations"=false scope=spfile sid='*';
[ol8ora19rf1]<SYS@+ASM1>$ ALTER SYSTEM SET "audit_sys_operations"=false
scope=spfile sid='*';
9-15-5. ASM 2개만
보이게 하기 (ol8ora19rf1, grid os user)
--만약 crsctl stat res -t에서 asm 인스턴스가 3개가 보인다면 아래의 작업을 수행
[+ASM1:grid@ol8ora19rf1][/home/grid]$ srvctl modify asm
-count ALL
--> 이제 2개만
보임
ora.asm(ora.asmgroup)
1 ONLINE
ONLINE
ol8ora19rf1
Started,STABLE
2 ONLINE
ONLINE
ol8ora19rf2
Started,STABLE
9-16. 오라클 인스턴스 필수 환경 설정
9-16-1. oracle 인스턴스 gloin 설정(ol8ora19rf1 및 ol8ora19rf2, oracle os user)
--1번 노드
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >>
$ORACLE_HOME/sqlplus/admin/glogin.sql <<'EOF'
SET TIMING OFF
SET HEADING OFF
SET FEEDBACK OFF
SET TIMING OFF
SET TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf1'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
EOF
--2번 노드
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ cat >>
$ORACLE_HOME/sqlplus/admin/glogin.sql <<'EOF'
SET
TIMING OFF
SET HEADING OFF
SET
FEEDBACK OFF
SET TIMING OFF
SET
TERM OFF
COLUMN HOSTNAME NEW_VALUE _HOSTNAME
#SELECT HOST_NAME AS HOSTNAME FROM V;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
DEFINE _HOSTNAME =
'ol8ora19rf2'
SET SQLPROMPT
'[&_HOSTNAME]<&_USER.@&_CONNECT_IDENTIFIER>$ '
SET TIMING ON
SET TERMOUT ON
SET SERVEROUTPUT
ON
SET NULL "(NULL)"
SET FEED
ON
SET LINES 200
SET PAGES
100
SET HEADING ON
SET FEEDBACK
ON
SET TERM ON
DEFINE_EDITOR=vi
EOF
9-16-2. Autotask OFF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Oct 1 22:40:38 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$
col client_name
for a50
col status for a15
SELECT CLIENT_NAME
, STATUS
FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME
STATUS
--------------------------------------------------
---------------
sql tuning
advisor
ENABLED
auto optimizer stats
collection
ENABLED
auto space
advisor
ENABLED
exec dbms_auto_task_admin.disable(client_name => 'auto
space advisor', operation => null, window_name => null);
exec dbms_auto_task_admin.disable(client_name => 'sql
tuning advisor', operation => null, window_name => null);
exec dbms_auto_task_admin.disable(client_name => 'auto
optimizer stats collection', operation => null, window_name =>
null);
col client_name
for a50
col status for a15
SELECT CLIENT_NAME
, STATUS
FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME
STATUS
----------------------------------------------------------------
--------
sql tuning
advisor
DISABLED
auto optimizer stats
collection
DISABLED
auto space
advisor
DISABLED
9-16-3. RESOURCE_MANAGER_PLAN "FORCE:" 로 설정
[ol8ora19rf1]<SYS@ORA19RF1>$ ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='FORCE:' SCOPE=BOTH SID ='*';
9-16-4. 스케줄러 세팅
exec
dbms_scheduler.set_Attribute('MONDAY_WINDOW', 'RESOURCE_PLAN', '');
exec dbms_scheduler.set_Attribute('TUESDAY_WINDOW',
'RESOURCE_PLAN', '');
exec
dbms_scheduler.set_Attribute('WEDNESDAY_WINDOW', 'RESOURCE_PLAN', '');
exec dbms_scheduler.set_Attribute('THURSDAY_WINDOW',
'RESOURCE_PLAN', '');
exec
dbms_scheduler.set_Attribute('FRIDAY_WINDOW', 'RESOURCE_PLAN', '');
exec dbms_scheduler.set_Attribute('SATURDAY_WINDOW',
'RESOURCE_PLAN', '');
exec
dbms_scheduler.set_Attribute('SUNDAY_WINDOW', 'RESOURCE_PLAN', '');
exec dbms_scheduler.set_Attribute('WEEKNIGHT_WINDOW',
'RESOURCE_PLAN', '');
exec
dbms_scheduler.set_Attribute('WEEKEND_WINDOW', 'RESOURCE_PLAN',
'');
exec
dbms_scheduler.disable('sys.BSLN_MAINTAIN_STATS_JOB');
exec
dbms_scheduler.disable('sys.DRA_REEVALUATE_OPEN_FAILURES');
exec
dbms_scheduler.disable('sys.RSE$CLEAN_RECOVERABLE_SCRIPT');
exec
dbms_scheduler.disable('sys.SM$CLEAN_AUTO_SPLIT_MERGE');
exec dbms_scheduler.disable('sys.FGR$AUTOPURGE_JOB');
exec dbms_scheduler.disable('sys.FILE_WATCHER');
exec
dbms_scheduler.disable('sys.HM_CREATE_OFFLINE_DICTIONARY');
exec
dbms_scheduler.disable('sys.XMLDB_NFS_CLEANUP_JOB');
9-16-5. HEAT_MAP 명시적 OFF
CREATE OR REPLACE
TRIGGER SYS.HEAT_MAP_OFF
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE
IMMEDIATE 'ALTER SYSTEM SET HEAT_MAP = OFF';
END;
/
9-16-6. AWR 수집 및 보관 주기 설정
[ol8ora19rf1]<SYS@ORA19RF1>$ exec dbms_workload_repository.modify_snapshot_settings(interval => 10, retention => 40*24*60);
--SYSAUX 사이즈를 세팅하는
방법
[ol8ora19rf1]<SYS@ORA19RF1>$ @?/rdbms/admin/utlsyxsz.sql
--> 해당 스크립트 실행 후 아래와 같이 SYSAUX 테이블 스페이스 사이즈 변경할 수
있음
[ol8ora19rf1]<SYS@ORA19RF1>$
col name for a50
SELECT NAME FROM
V$DATAFILE WHERE NAME LIKE '%sysaux%';
+DATA1/ORA19RF/DATAFILE/sysaux.258.1213140481
ALTER DATABASE DATAFILE '+DATA1/ORA19RF/DATAFILE/sysaux.258.1213140481' RESIZE 2G;
9-16-7. alert log 표기 방식 변경
[ol8ora19rf1]<SYS@ORA19RF1>$ alter system set uniform_log_timestamp_format=false scope=both SID='*';
9-16-8. dbms statistics 수집
exec
dbms_stats.gather_fixed_objects_stats;
exec
dbms_stats.gather_dictionary_stats;
9-16-9. LOCAL_TEMP_TABLESPACE 설정 확인
col USERNAME for
a30
col LOCAL_TEMP_TABLESPACE for a20
SELECT USERNAME, LOCAL_TEMP_TABLESPACE FROM
DBA_USERS;
USERNAME
LOCAL_TEMP_TABLESPAC
------------------------------
--------------------
SYS
TEMP
SYSTEM
TEMP
XS$NULL
TEMP
OJVMSYS
TEMP
LBACSYS
TEMP
OUTLN
TEMP
SYS$UMF
TEMP
DBSNMP
TEMP
APPQOSSYS
TEMP
DBSFWUSER
TEMP
GGSYS
TEMP
ANONYMOUS
TEMP
HR
TEMP
CTXSYS
TEMP
DVSYS
TEMP
DVF
TEMP
GSMADMIN_INTERNAL
TEMP
MDSYS
TEMP
OLAPSYS
TEMP
XDB
TEMP
WMSYS
TEMP
GSMCATUSER
TEMP
MDDATA
TEMP
SYSBACKUP
TEMP
REMOTE_SCHEDULER_AGENT
TEMP
GSMUSER
TEMP
SYSRAC
TEMP
GSMROOTUSER
TEMP
SI_INFORMTN_SCHEMA
TEMP
AUDSYS
TEMP
DIP
TEMP
ORDPLUGINS
TEMP
SYSKM
TEMP
ORDDATA
TEMP
ORACLE_OCM
TEMP
SYSDG
TEMP
ORDSYS
TEMP
37 rows selected.
Elapsed: 00:00:00.01
--> 이것이 TEMP가 아닌 경우 아래와 같이
설정함
ALTER USER <USER_NAME> LOCAL TEMPORARY TABLESPACE
TEMP;
9-16-10.
SUPPLEMENTAL_LOG 설정
<SUPPLEMENTAL_LOG 설정이 필요한 상황>
논리적 복제(Oracle GoldenGate, Streams,
CDC) 같은 기능을 쓰려면 추가 정보가 필요
Logical Standby는 SQL로 변환해 적용하기
때문에 변경된 row를 식별할 수 있어야 함
일부 감사/로그
추적
[ol8ora19rf1]<SYS@ORA19RF1>$ SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM
V$DATABASE;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
NO
1 row selected.
Elapsed:
00:00:00.01
[ol8ora19rf1]<SYS@ORA19RF1>$ alter database add supplemental log
data;
--> 최소 보조 로깅(minimal
supplemental logging)
--> UPDATE/DELETE 시 row를 고유하게
식별할 수 있도록 PK/UK 컬럼 또는 ROWID 정보를 추가로 기록
--> 변경된 행을 식별할
최소 정보만 로깅 (가볍지만 PK 없는 테이블은 문제)
Database altered.
Elapsed:
00:00:00.33
[ol8ora19rf1]<SYS@ORA19RF1>$ SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEMENTAL_LOG_DATA_MI
------------------------
YES
1 row selected.
9-16-11. DBMS PROFILE 설정
alter profile default limit
failed_login_attempts unlimited
password_lock_time 1
password_life_time 7300
password_grace_time 30
password_reuse_time 90
password_reuse_max 3
--password_verify_function ora12c_stig_verify_function
;
--> 테스트용이라 설정하지 않음
9-16-12. UTL_SMTP, UTP_HTTP에서 권한 회수
select * from DBA_TAB_PRIVS where grantee = 'PUBLIC' AND TABLE_NAME IN ('UTL_SMTP', 'UTL_HTTP');
REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;
-->
테스트용이라 설정하지 않음
select * from DBA_TAB_PRIVS
where grantee = 'PUBLIC' AND TABLE_NAME IN ('UTL_SMTP', 'UTL_HTTP');
--> 출력행
없어야함
9-16-13. INVALID OBJECT COMPILE
--아래의 sql문을 실행 시켜 invalid
object 컴파일
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('PACKAGE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER PACKAGE'|| ' ' || OWNER || '.' || '"'|| OBJECT_NAME || '"' || '
COMPILE BODY;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('PACKAGE BODY')
AND STATUS
= 'INVALID'
)
UNION ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER PUBLIC ' || OBJECT_TYPE || ' ' || '"' || OBJECT_NAME || '"'
|| ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE 1=1
AND OBJECT_TYPE IN ('SYNONYM') AND OWNER = 'PUBLIC'
AND STATUS = 'INVALID'
)
UNION ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('TYPE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER TYPE' || ' ' || OWNER || '.' || '"'|| OBJECT_NAME || '"' ||
' COMPILE BODY;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('TYPE BODY')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('VIEW')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('PROCEDURE')
AND STATUS
= 'INVALID'
)
UNION
ALL
SELECT *
FROM
(
SELECT
OBJECT_NAME
, STATUS
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || '"'|| OBJECT_NAME ||
'"' || ' COMPILE;' AS DDL
FROM
DBA_OBJECTS
WHERE
1=1
AND OBJECT_TYPE IN ('TRIGGER')
AND STATUS
= 'INVALID'
)
ORDER
BY 3
;
9-16-14. Audit_trail 및 Audit_sys_operation 파라미터 설정
[ol8ora19rf1]<SYS@ORA19RF1>$ ALTER SYSTEM SET audit_trail = os scope=spfile sid = '*';
System altered.
Elapsed: 00:00:00.03
System altered.
Elapsed: 00:00:00.01
9-16-15. disk i/o 및 파일 시스템 i/o 관련 파라미터 설정
[ol8ora19rf1]<SYS@ORA19RF1>$ ALTER SYSTEM SET disk_asynch_io = true scope=spfile sid = '*';
System altered.
Elapsed: 00:00:00.01
[ol8ora19rf1]<SYS@ORA19RF1>$ ALTER SYSTEM SET filesystemio_options = setall
scope=spfile sid = '*';
System altered.
Elapsed: 00:00:00.01
9-16-16. 권고 파라미터 설정
--아래 설정은 메모리 10기가를 기준으로 SGA영역을
static으로 지정한 것임
ALTER SYSTEM SET
sga_max_size = 3221225472
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
db_cache_size = 1275068416
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
java_pool_size = 167772160
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
large_pool_size = 536870912
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
shared_pool_size = 1073741824 SCOPE=SPFILE SID =
'*';
ALTER SYSTEM SET
streams_pool_size = 167772160 SCOPE=SPFILE SID =
'*';
ALTER SYSTEM SET pga_aggregate_target =
1073741824 SCOPE=SPFILE SID = '*';
--아래 설정은 메모리 16기가를 기준으로 SGA영역을
static으로 지정한 것임
--large_pool_size를 2GB로
한것을 기준으로 한 것임
ALTER SYSTEM SET
sga_max_size = 5153960755
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
db_cache_size = 1597190963
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
java_pool_size = 167772160
SCOPE=SPFILE SID = '*';
ALTER SYSTEM SET
large_pool_size = 2147483648 SCOPE=SPFILE
SID = '*';
ALTER SYSTEM SET
shared_pool_size = 1073741824 SCOPE=SPFILE SID =
'*';
ALTER SYSTEM SET
streams_pool_size = 167772160 SCOPE=SPFILE SID =
'*';
ALTER SYSTEM SET pga_aggregate_target =
1717986918 SCOPE=SPFILE SID = '*';
--19c 표준 파라미터
설정
SELECT *
FROM
(
SELECT
ROW_NUMBER()
OVER(ORDER BY CASE WHEN SUBSTR(B.PARAMETER, 1, 1) = '_' THEN 9 ELSE 0 END,
A.GENERAL_HIDDEN_SE, B.PARAMETER) AS RNUM
, A."GENERAL_HIDDEN_SE"
, B.PARAMETER
, A.DEFAULT_VALUE
, B.STANDARD_VALUE
, NVL(B.NOTE, ' ') AS NOTE
, A."세션변경"
, A."시스템변경"
, A."설명"
, CASE WHEN (
B.STANDARD_VALUE NOT IN ('user_define', 'default')
AND B.PARAMETER NOT IN ('db_block_size', 'event')
)
THEN
CASE WHEN A."GENERAL_HIDDEN_SE" = 'GENERAL'
THEN 'ALTER SYSTEM SET ' || A.PARAMETER || '=' || ''||B.STANDARD_VALUE ||'' || '
SCOPE=SPFILE' || ' SID=''*''' ||';'
WHEN A."GENERAL_HIDDEN_SE" = 'HIDDEN'
THEN 'ALTER SYSTEM SET ' || '"'||A.PARAMETER||'"' || '=' || ''||B.STANDARD_VALUE
||'' || ' SCOPE=SPFILE' || ' SID=''*''' ||';'
END
ELSE '' END
AS SCRIPT
FROM
(
SELECT 'GENERAL'
AS "GENERAL_HIDDEN_SE"
, A.NAME AS "PARAMETER"
, A.VALUE AS "VALUE"
, A.DISPLAY_VALUE AS "DISPLAY_VALUE"
, A.DEFAULT_VALUE AS "DEFAULT_VALUE"
, A.ISDEFAULT AS "IS_DEFALUT"
, A.ISSES_MODIFIABLE AS "세션변경"
, A.ISSYS_MODIFIABLE AS "시스템변경"
, A.ISINSTANCE_MODIFIABLE AS "인스턴스변경"
, A.ISPDB_MODIFIABLE AS "PDB변경"
, A.DESCRIPTION AS "설명"
, A.UPDATE_COMMENT AS "UPDATE_COMMENT"
FROM
V$PARAMETER A
WHERE NOT EXISTS (SELECT 1 FROM X$KSPPI K WHERE K.KSPPINM = A.NAME AND K.KSPPINM
LIKE '\_%' ESCAPE '\')
UNION ALL
SELECT 'HIDDEN'
AS "GENERAL_HIDDEN_SE"
, A.KSPPINM AS "PARAMETER"
, B.KSPPSTVL AS "VALUE"
, B.KSPPSTDVL AS "DISPLAY_VALUE"
, B.KSPPSTDFL AS "DEFAULT_VALUE"
, B.KSPPSTDF AS "IS_DEFALUT"
, DECODE (BITAND (A.KSPPIFLG / 256, 1), 1, 'TRUE', 'FALSE') AS "세션변경"
, DECODE (BITAND (a.ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3,
'IMMEDIATE', 'FALSE') AS "시스템변경"
, DECODE (BITAND (a.ksppiflg, 4), 4, 'FALSE', DECODE (BITAND (ksppiflg / 65536,
3), 0, 'FALSE', 'TRUE')) AS "인스턴스변경"
, DECODE (BITAND (a.ksppiflg / 524288, 1), 1, 'TRUE', 'FALSE') AS "PDB변경"
, A.KSPPDESC AS "설명"
, '' AS "UPDATE_COMMENT"
FROM
X$KSPPI A, X$KSPPCV B
WHERE
A.INDX = B.INDX
AND A.KSPPINM LIKE '\_%' ESCAPE '\'
) A
,
(
SELECT
'db_block_size'
AS "PARAMETER",
'8192'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'db_file_multiblock_read_count'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'db_cache_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'shared_pool_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'log_buffer'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'java_pool_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'large_pool_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('Dependent on PX Useage ') AS
NOTE FROM DUAL
UNION ALL SELECT
'streams_pool_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'sga_max_size'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'sga_target'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'memory_target'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'result_cache_max_size'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'pga_aggregate_target'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_add_col_optim_enabled'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_partition_large_extents'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('Dependent on System Env') AS
NOTE FROM DUAL
UNION ALL SELECT
'_index_partition_large_extents'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('Dependent on System Env') AS
NOTE FROM DUAL
UNION ALL SELECT
'db_files'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('Dependent on System Env') AS
NOTE FROM DUAL
UNION ALL SELECT
'deferred_segment_creation'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'resource_manager_plan'
AS "PARAMETER",
'''FORCE:'''
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_highest_priority_processes'
AS "PARAMETER",
'''VKTM|LG*|LMS*'''
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_use_adaptive_log_file_sync'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_use_single_log_writer'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_rollback_segment_count'
AS "PARAMETER",
'1000'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_cleanup_rollback_entries'
AS "PARAMETER",
'2000'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_undo_autotune'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'session_cached_cursors'
AS "PARAMETER",
'100'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_pga_max_size'
AS "PARAMETER",
'1073741824'
AS "STANDARD_VALUE", TRIM('Greater Than
1G ') AS NOTE
FROM DUAL
UNION ALL SELECT
'_PX_use_large_pool'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_enable_shared_pool_durations'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'pga_aggregate_limit'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE", TRIM('If Need Hard Limit, set') AS
NOTE FROM DUAL
UNION ALL SELECT
'_dlm_stats_collect'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_drop_stat_segment'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_sys_logon_delay'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE", TRIM('Substitute 28401 trace ') AS
NOTE FROM DUAL
UNION ALL SELECT
'audit_trail'
AS "PARAMETER",
'NONE'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_fatalprocess_redo_dump_time_limit'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_nonfatalprocess_redo_dump_time_limit' AS
"PARAMETER",
'0'
AS "STANDARD_VALUE", TRIM('Substitute 10555 trace ') AS
NOTE FROM DUAL
UNION ALL SELECT
'_cursor_obsolete_threshold'
AS "PARAMETER",
'1024'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_cursor_reload_failure_threshold'
AS "PARAMETER",
'5'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_report_capture_cycle_time'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_plans'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_statistics'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_reporting_only'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optim_peek_user_binds'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_use_feedback'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_adaptive_cursor_sharing'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing'
AS "PARAMETER",
'none'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing_rel' AS
"PARAMETER",
'none'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_load'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('Dependent on Func Usage') AS
NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_conventional_dml' AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('Dependent on Func Usage') AS
NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_use_stats_on_conventional_dml' AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('Dependent on Func Usage') AS
NOTE FROM DUAL
UNION ALL SELECT
'_gby_hash_aggregation_enabled'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_connect_by_cost_based'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_cost_based_transformation'
AS "PARAMETER",
'off'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_push_pred_cost_based'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_null_aware_antijoin'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_dynamic_sampling'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_complex_view_merging'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'query_rewrite_enabled'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'star_transformation_enabled'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_securefiles_concurrency_estimate'
AS "PARAMETER",
'50'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'control_file_record_keep_time'
AS "PARAMETER",
'15'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_buddy_instance'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_sql_plan_directive_mgmt_control'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kks_parse_error_warning'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kks_obsolete_dump_threshold'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'allow_group_access_to_sga'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('if using Maxgauge, true') AS
NOTE FROM DUAL
UNION ALL SELECT
'_in_memory_undo'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_bloom_filter_enabled'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_aggr_groupby_elim'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_secure_view_merging'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_disable_system_state'
AS "PARAMETER",
'10'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'diagnostic_dest'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE",
TRIM('/oradiag
') AS NOTE FROM DUAL
UNION ALL SELECT
'audit_file_dest'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('/oradiag/admin/DBNAME/adump') AS NOTE FROM DUAL
UNION ALL SELECT
'resource_limit'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'event'
AS "PARAMETER", '''450502 trace name context forever, level 1''' AS
"STANDARD_VALUE", TRIM('pcor
only
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_policy_time'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_undo_affinity'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_bypass_readers'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_read_mostly_locking'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_dump_remote_lock'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_persistent_read_mostly'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_policy_minimum'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_lm_sync_timeout'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_lm_tickets'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_clusterwide_global_transactions'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE", TRIM('if RAC and XA, true
') AS NOTE FROM DUAL
UNION ALL SELECT
'parallel_force_local'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'audit_sys_operations'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'compatible'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'db_cache_advice'
AS "PARAMETER",
'on'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'heat_map'
AS "PARAMETER",
'off'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'job_queue_processes'
AS "PARAMETER",
'10'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'open_cursors'
AS "PARAMETER",
'1000'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'optimizer_mode'
AS "PARAMETER",
'ALL_ROWS'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'parallel_max_servers'
AS "PARAMETER",
'100'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'parallel_min_servers'
AS "PARAMETER",
'0'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'parallel_threads_per_cpu'
AS "PARAMETER",
'2'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'processes'
AS "PARAMETER",
'3000'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'remote_login_passwordfile'
AS "PARAMETER",
'exclusive'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'resumable_timeout'
AS "PARAMETER",
'7200'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'undo_management'
AS "PARAMETER",
'auto'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'undo_retention'
AS "PARAMETER",
'3600'
AS "STANDARD_VALUE", TRIM('Dependent on System Env') AS
NOTE FROM DUAL
UNION ALL SELECT
'uniform_log_timestamp_format'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_enable_spacebg'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_library_cache_advice'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_optimizer_reduce_groupby_key'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_rowsets_enabled'
AS "PARAMETER",
'false'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'max_string_size'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'gcs_server_processes'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_ges_server_processes'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_high_priority_processes'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_highthreshold_undoretention'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'temp_undo_enabled'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'use_large_pages'
AS "PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('if Linux,
ONLY ')
AS NOTE FROM DUAL
UNION ALL SELECT
'_memory_imm_mode_without_autosga'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kghdsidx_count'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_override_force_cr'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_gc_integrity_checks'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_client_enable_auto_unregister'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_emon_send_timeout'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_external_scn_logging_threshold_seconds' AS
"PARAMETER",
'user_define'
AS "STANDARD_VALUE", TRIM('Dependent on System Env') AS
NOTE FROM DUAL
UNION ALL SELECT
'_ktb_debug_flags'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_disable_ilm_internal'
AS "PARAMETER",
'true'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_disable_last_successful_login_time'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kdis_reject_ops'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kdis_reject_level'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'_kdis_reject_limit'
AS "PARAMETER",
'default'
AS "STANDARD_VALUE",
TRIM('
') AS NOTE FROM DUAL
UNION ALL SELECT
'fast_start_mttr_target'
AS "PARAMETER",
'300'
AS "STANDARD_VALUE", TRIM('First 300 and tune
') AS NOTE FROM DUAL
) B
WHERE
UPPER(B."PARAMETER") = UPPER(A."PARAMETER"(+))
ORDER BY
CASE WHEN SUBSTR(B.PARAMETER, 1, 1) = '_' THEN 9 ELSE 0 END
, A.GENERAL_HIDDEN_SE,
B.PARAMETER
)
WHERE SCRIPT IS
NOT NULL
;
--> sql문에서 나온 alter system문을 실행
시킴
10. 리눅스 Huge Page 설정 (ol8ora19rf1 및 ol8ora19rf2 모두, root os user)
10-1. huge page 설정 스크립트 저장
Oracle Linux: Shell
Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration
(Doc ID 401749.1)
위의 공식문서에서 다운로드 받은 파일을 /root/hugepages_settings.sh 로
저장함
10-2. 실행 권한 부여
[root@ol8ora19rf1][/root]$ chmod +x
hugepages_settings.sh
[root@ol8ora19rf1][/root]$ ls -l
hugepages_settings.sh
-rwxr-xr-x. 1 root root
2809 Oct 1 23:28 hugepages_settings.sh
[root@ol8ora19rf2][/root]$ chmod +x
hugepages_settings.sh
[root@ol8ora19rf2][/root]$ ls -l
hugepages_settings.sh
-rwxr-xr-x. 1 root root 2815 Oct 1 23:29
hugepages_settings.sh
10-3. huge page 산정 (오라클 인스턴스 기동한 상태에서
실행해야함)
[root@ORA19RF1][/root]$ ./hugepages_settings.sh
This script is
provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the
current shared
memory segments on Oracle Linux. Before
proceeding with the execution please note following:
* For ASM instance, it needs to configure ASMM
instead of AMM.
* The 'pga_aggregate_target' is
outside the SGA and
you should accommodate
this while calculating the overall size.
* In case
you changes the DB SGA size,
as the new SGA
will not fit in the previous HugePages configuration,
it had better disable the whole HugePages,
start the DB with new SGA size and run the
script again.
And make sure that:
* Oracle Database instance(s) are up and running
* Oracle Database Automatic Memory Management (AMM)
is not setup
(See Doc ID 749851.1)
* The shared memory segments can be listed by
command:
# ipcs
-m
Press Enter to proceed...
Recommended setting: vm.nr_hugepages = 2402
-->
2402가 나왔으나 2500으로 넉넉하게 세팅할 것임
[root@ORA19RF2][/root]$ ./hugepages_settings.sh
This script is
provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the
current shared
memory segments on Oracle Linux. Before
proceeding with the execution please note following:
* For ASM instance, it needs to configure ASMM
instead of AMM.
* The 'pga_aggregate_target' is
outside the SGA and
you should accommodate
this while calculating the overall size.
* In case
you changes the DB SGA size,
as the new SGA
will not fit in the previous HugePages configuration,
it had better disable the whole HugePages,
start the DB with new SGA size and run the
script again.
And make sure that:
* Oracle Database instance(s) are up and running
* Oracle Database Automatic Memory Management (AMM)
is not setup
(See Doc ID 749851.1)
* The shared memory segments can be listed by
command:
# ipcs
-m
Press Enter to proceed...
Recommended setting: vm.nr_hugepages = 2402
-->
2402가 나왔으나 2500으로 넉넉하게 세팅할 것임
10-4. huge page 설정 (ORA19RF1 및 ORA19RF2
모두)
[root@ol8ora19rf1][/root]$ vi /etc/sysctl.conf
[root@ol8ora19rf1][/root]$ cat /etc/sysctl.conf | grep -i
huge
vm.nr_hugepages = 2500
[root@ol8ora19rf1][/root]$ sysctl
-p
[root@ol8ora19rf2][/root]$ vi /etc/sysctl.conf
[root@ol8ora19rf2][/root]$ cat /etc/sysctl.conf | grep -i
huge
vm.nr_hugepages = 2500
[root@ol8ora19rf2][/root]$ sysctl -p
10-5. huge page 사용량 조회
[root@ol8ora19rf1][/root]$ grep Huge
/proc/meminfo
AnonHugePages:
0 kB
ShmemHugePages: 0
kB
FileHugePages:
0 kB
HugePages_Total: 2500
HugePages_Free: 2500
HugePages_Rsvd:
0
HugePages_Surp:
0
Hugepagesize: 2048
kB
Hugetlb:
5120000 kB
--> 2500 으로 정확하게 할당했는지 확인,
할당이 안되어 있다면 db내린 후 다시 할당(sysctl -p 및 meminfo 다시 확인)
[root@ol8ora19rf2][/root]$ grep Huge
/proc/meminfo
AnonHugePages:
0 kB
ShmemHugePages: 0
kB
FileHugePages:
0 kB
HugePages_Total: 2500
HugePages_Free: 2500
HugePages_Rsvd:
0
HugePages_Surp:
0
Hugepagesize: 2048
kB
Hugetlb:
5120000 kB
--> 2500 으로 정확하게 할당했는지 확인, 할당이 안되어 있다면 db내린 후 다시
할당(sysctl -p 및 meminfo 다시 확인)
10-6. use_large_pages 파라미터 변경
SYS@ORA19RF1> alter system
set use_large_pages=only scope=spfile;
SYS@ORA19RF2> alter system
set use_large_pages=only scope=spfile;
11. grid/oracle 각종 로그 자동 삭제 설정
11-1. oracle 각종 로그 제거 및 alert 로그 mv 스크립트 (oracle os user)
--1번 노드
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Oct 1 23:42:14 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$ show parameter audit_file_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
audit_file_dest
string
/u01/app/oracle/admin/ORA19RF/adump
[ol8ora19rf1]<SYS@ORA19RF1>$ show parameter background_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
background_dump_dest
string
/u01/app/oracle/product/19c/db_1/rdbms/log
[ol8ora19rf1]<SYS@ORA19RF1>$ show parameter user_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
user_dump_dest
string
/u01/app/oracle/product/19c/db_1/rdbms/log
col name for
a20
col value for a50
select
a.name, a.value from v$diag_info a where name in ('Diag Trace', 'Diag
Alert');
NAME
VALUE
--------------------
--------------------------------------------------
Diag
Trace
/u01/app/oracle/diag/rdbms/ora19rf/ORA19RF1/trace
Diag
Alert
/u01/app/oracle/diag/rdbms/ora19rf/ORA19RF1/alert
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv
/home/oracle/os_scripts
mkdir: created directory
'/home/oracle/os_scripts'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/delete_oracle_log.sh <<'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
echo "\$ORACLE_UNQNAME : ""["$ORACLE_UNQNAME"]"
echo "\$ORACLE_HOME :
""["$ORACLE_HOME"]"
find
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trm -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trc -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/admin/$ORACLE_UNQNAME/adump/*.aud -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/log/*.trc -mtime +14 -exec rm {}
\;
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/modify_oracle_alert_log.sh <<'EOF'
#!/bin/bash
echo
"$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "$ORACLE_SID : ""["$ORACLE_SID"]"
echo "$ORACLE_UNQNAME :
""["$ORACLE_UNQNAME"]"
save_datetime=`date +"%Y%m%d%H%M%S"`
echo $save_datetime
cp -p
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml.$save_datetime
rm -f
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
save_datetime2=`date +"%Y%m%d%H%M%S"`
echo $save_datetime2
cp -p
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log.$save_datetime2
rm -f
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744 /home/oracle/os_scripts/delete_oracle_log.sh /home/oracle/os_scripts/modify_oracle_alert_log.sh
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ ls -l
/home/oracle/os_scripts/
-rwxr--r--. 1 oracle oinstall 553 Oct 1 23:55
delete_oracle_log.sh
-rwxr--r--. 1 oracle oinstall 825
Oct 1 23:57 modify_oracle_alert_log.sh
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ crontab
-e
no crontab for oracle - using an empty one
crontab: installing new crontab
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ crontab
-l
0 2 * * * /home/oracle/os_scripts/delete_oracle_log.sh
&>/dev/null
10 2 1 * *
/home/oracle/os_scripts/modify_oracle_alert_log.sh
&>/dev/null
--2번
노드
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Oct 1 23:42:14 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf2]<SYS@ORA19RF2>$ show parameter audit_file_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
audit_file_dest
string
/u01/app/oracle/admin/ORA19RF/adump
[ol8ora19rf2]<SYS@ORA19RF2>$ show parameter background_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
background_dump_dest
string
/u01/app/oracle/product/19c/db_1/rdbms/log
[ol8ora19rf2]<SYS@ORA19RF2>$ show parameter user_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- -------------------------------------------
user_dump_dest
string
/u01/app/oracle/product/19c/db_1/rdbms/log
col name for
a20
col value for a50
select
a.name, a.value from v$diag_info a where name in ('Diag Trace', 'Diag
Alert');
NAME
VALUE
--------------------
--------------------------------------------------
Diag
Trace
/u01/app/oracle/diag/rdbms/ora19rf/ORA19RF2/trace
Diag
Alert
/u01/app/oracle/diag/rdbms/ora19rf/ORA19RF2/alert
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ mkdir -pv
/home/oracle/os_scripts
mkdir: created directory
'/home/oracle/os_scripts'
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ cat >
/home/oracle/os_scripts/delete_oracle_log.sh <<'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
echo "\$ORACLE_UNQNAME : ""["$ORACLE_UNQNAME"]"
echo "\$ORACLE_HOME :
""["$ORACLE_HOME"]"
find
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trm -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trc -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/admin/$ORACLE_UNQNAME/adump/*.aud -mtime +14 -exec rm {} \;
find $ORACLE_HOME/rdbms/log/*.trc -mtime +14 -exec rm {}
\;
EOF
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ cat >
/home/oracle/os_scripts/modify_oracle_alert_log.sh <<'EOF'
#!/bin/bash
echo "$ORACLE_UNQNAME_LOWER :
""["$ORACLE_UNQNAME_LOWER"]"
echo "$ORACLE_SID :
""["$ORACLE_SID"]"
echo "$ORACLE_UNQNAME :
""["$ORACLE_UNQNAME"]"
save_datetime=`date +"%Y%m%d%H%M%S"`
echo $save_datetime
cp -p
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml.$save_datetime
rm -f
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
save_datetime2=`date +"%Y%m%d%H%M%S"`
echo $save_datetime2
cp -p
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log.$save_datetime2
rm -f
/u01/app/oracle/diag/rdbms/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
EOF
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ chmod 744 /home/oracle/os_scripts/delete_oracle_log.sh /home/oracle/os_scripts/modify_oracle_alert_log.sh
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ ls -l
/home/oracle/os_scripts/
-rwxr--r--. 1 oracle oinstall 553 Oct 1 23:55
delete_oracle_log.sh
-rwxr--r--. 1 oracle oinstall 825
Oct 1 23:57 modify_oracle_alert_log.sh
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ crontab
-e
no crontab for
oracle - using an empty one
crontab: installing new
crontab
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ crontab
-l
0 2 * * *
/home/oracle/os_scripts/delete_oracle_log.sh &>/dev/null
10 2 1 * *
/home/oracle/os_scripts/modify_oracle_alert_log.sh
&>/dev/null
11-2. grid 각종 로그 제거 및 alert 로그 mv 스크립트(grid os user)
--1번 노드
[+ASM1:grid@ol8ora19rf1][/home/grid]$ alias sa
alias sa='rlwrap sqlplus "/as
sysasm"'
[+ASM1:grid@ol8ora19rf1][/home/grid]$
sa
SQL*Plus: Release
19.0.0.0.0 - Production on Thu Oct 2 00:08:42 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1] SYS@+ASM1> show
parameter audit_file_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
audit_file_dest
string
/u01/app/19c/grid/rdbms/audit
[ol8ora19rf1] SYS@+ASM1> show parameter background_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
background_dump_dest
string
/u01/app/19c/grid/rdbms/log
[ol8ora19rf1] SYS@+ASM1> show parameter user_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
user_dump_dest
string
/u01/app/19c/grid/rdbms/log
[ol8ora19rf1]
SYS@+ASM1>
col name for a20
col value for a50
select a.name,
a.value from v$diag_info a where name in ('Diag Trace', 'Diag
Alert');
NAME
VALUE
--------------------
--------------------------------------------------
Diag
Trace
/u01/app/oracle/diag/asm/+asm/+ASM1/trace
Diag
Alert
/u01/app/oracle/diag/asm/+asm/+ASM1/alert
2 rows selected.
Elapsed: 00:00:00.00
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/home/grid/os_scripts
mkdir: created directory
‘/home/grid/os_scripts’
[+ASM1:grid@ol8ora19rf1][/home/grid/os_scripts]$ vi /home/grid/os_scripts/delete_grid_log.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >
/home/grid/os_scripts/delete_grid_log.sh << 'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$GRID_HOME : ""["$GRID_HOME"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
find
$GRID_HOME/rdbms/audit/*.aud -mtime +14 -exec rm {} \;
find $GRID_HOME/rdbms/log/*.trc -mtime +14 -exec rm {}
\;
find
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trm -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trc -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/crs/`hostname`/crs/trace/*.trm -mtime +14 -exec rm {} \;
find /u01/app/oracle/diag/crs/`hostname`/crs/trace/*.trc
-mtime +14 -exec rm {} \;
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >
/home/grid/os_scripts/delete_listener_log.sh << 'EOF'
#!/bin/bash
find
/u01/app/oracle/diag/tnslsnr/`hostname` -name *.log -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/`hostname` -name log.xml
-exec rm {} \;
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >
/home/grid/os_scripts/modify_grid_alert_log.sh << 'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$ORACLE_UNQNAME : ""["$ORACLE_UNQNAME"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
save_datetime=`date +"%Y%m%d%H%M%S"`
echo $save_datetime
cp -p
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml.$save_datetime
rm -f
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
save_datetime2=`date +"%Y%m%d%H%M%S"`
echo $save_datetime2
cp -p
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log.$save_datetime2
rm -f
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
save_datetime3=`date +"%Y%m%d%H%M%S"`
echo $save_datetime3
cp -p
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml.$save_datetime3
rm -f
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml
save_datetime4=`date +"%Y%m%d%H%M%S"`
echo $save_datetime4
cp -p
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log.$save_datetime4
rm -f
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ ls -l
/home/grid/os_scripts/
-rw-r--r--. 1 grid oinstall 641 Oct 2 00:12
delete_grid_log.sh
-rw-r--r--. 1 grid oinstall 158
Oct 2 00:13 delete_listener_log.sh
-rw-r--r--. 1
grid oinstall 1321 Oct 2 00:15
modify_grid_alert_log.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ readlink -f
/home/grid/os_scripts/*.sh
/home/grid/os_scripts/delete_grid_log.sh
/home/grid/os_scripts/delete_listener_log.sh
/home/grid/os_scripts/modify_grid_alert_log.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ chmod 744 /home/grid/os_scripts/delete_grid_log.sh /home/grid/os_scripts/delete_listener_log.sh /home/grid/os_scripts/modify_grid_alert_log.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ ls -l
/home/grid/os_scripts/
-rwxr--r--. 1 grid oinstall 641 Oct 2 00:12
delete_grid_log.sh
-rwxr--r--. 1 grid oinstall 158
Oct 2 00:13 delete_listener_log.sh
-rwxr--r--. 1
grid oinstall 1321 Oct 2 00:15
modify_grid_alert_log.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crontab
-e
no crontab for grid - using an empty one
crontab: installing new crontab
[+ASM1:grid@ol8ora19rf1][/home/grid]$ crontab
-l
0 2 * * * /home/grid/os_scripts/delete_grid_log.sh
&>/dev/null
10 2 1 * *
/home/grid/os_scripts/modify_grid_alert_log.sh &>/dev/null
20 2 1 * * /home/grid/os_scripts/delete_listener_log.sh
&>/dev/null
--2번 노드
[+ASM2:grid@ol8ora19rf2][/home/grid]$ alias
sa
alias sa='rlwrap
sqlplus "/as sysasm"'
[+ASM2:grid@ol8ora19rf2][/home/grid]$
sa
SQL*Plus: Release
19.0.0.0.0 - Production on Thu Oct 2 00:08:42 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf2] SYS@+ASM2> show
parameter audit_file_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
audit_file_dest
string
/u01/app/19c/grid/rdbms/audit
[ol8ora19rf2] SYS@+ASM2> show parameter background_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
background_dump_dest
string
/u01/app/19c/grid/rdbms/log
[ol8ora19rf2] SYS@+ASM2> show parameter user_dump_dest
NAME
TYPE
VALUE
------------------------------------
--------------------------------- ------------------------------
user_dump_dest
string
/u01/app/19c/grid/rdbms/log
[ol8ora19rf2]
SYS@+ASM2>
col name for a20
col value for a50
select a.name,
a.value from v$diag_info a where name in ('Diag Trace', 'Diag
Alert');
NAME
VALUE
--------------------
--------------------------------------------------
Diag
Trace
/u01/app/oracle/diag/asm/+asm/+ASM2/trace
Diag
Alert
/u01/app/oracle/diag/asm/+asm/+ASM2/alert
2 rows selected.
Elapsed: 00:00:00.00
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/home/grid/os_scripts
mkdir: created directory
‘/home/grid/os_scripts’
[+ASM2:grid@ol8ora19rf2][/home/grid/os_scripts]$ vi /home/grid/os_scripts/delete_grid_log.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >
/home/grid/os_scripts/delete_grid_log.sh << 'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$GRID_HOME : ""["$GRID_HOME"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
find
$GRID_HOME/rdbms/audit/*.aud -mtime +14 -exec rm {} \;
find $GRID_HOME/rdbms/log/*.trc -mtime +14 -exec rm {}
\;
find
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trm -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/*.trc -mtime
+14 -exec rm {} \;
find
/u01/app/oracle/diag/crs/`hostname`/crs/trace/*.trm -mtime +14 -exec rm {} \;
find /u01/app/oracle/diag/crs/`hostname`/crs/trace/*.trc
-mtime +14 -exec rm {} \;
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >
/home/grid/os_scripts/delete_listener_log.sh << 'EOF'
#!/bin/bash
find
/u01/app/oracle/diag/tnslsnr/`hostname` -name *.log -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/`hostname` -name log.xml
-exec rm {} \;
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >
/home/grid/os_scripts/modify_grid_alert_log.sh << 'EOF'
#!/bin/bash
echo
"\$ORACLE_UNQNAME_LOWER : ""["$ORACLE_UNQNAME_LOWER"]"
echo "\$ORACLE_UNQNAME : ""["$ORACLE_UNQNAME"]"
echo "\$ORACLE_SID : ""["$ORACLE_SID"]"
save_datetime=`date +"%Y%m%d%H%M%S"`
echo $save_datetime
cp -p
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml.$save_datetime
rm -f
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/alert/log.xml
save_datetime2=`date +"%Y%m%d%H%M%S"`
echo $save_datetime2
cp -p
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log.$save_datetime2
rm -f
/u01/app/oracle/diag/asm/$ORACLE_UNQNAME_LOWER/$ORACLE_SID/trace/alert_$ORACLE_SID.log
save_datetime3=`date +"%Y%m%d%H%M%S"`
echo $save_datetime3
cp -p
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml.$save_datetime3
rm -f
/u01/app/oracle/diag/crs/`hostname`/crs/alert/log.xml
save_datetime4=`date +"%Y%m%d%H%M%S"`
echo $save_datetime4
cp -p
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log.$save_datetime4
rm -f
/u01/app/oracle/diag/crs/`hostname`/crs/trace/alert.log
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ ls -l
/home/grid/os_scripts/
-rw-r--r--. 1 grid oinstall 641 Oct 2 00:12
delete_grid_log.sh
-rw-r--r--. 1 grid oinstall 158
Oct 2 00:13 delete_listener_log.sh
-rw-r--r--. 1
grid oinstall 1321 Oct 2 00:15
modify_grid_alert_log.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ readlink -f
/home/grid/os_scripts/*.sh
/home/grid/os_scripts/delete_grid_log.sh
/home/grid/os_scripts/delete_listener_log.sh
/home/grid/os_scripts/modify_grid_alert_log.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ chmod 744 /home/grid/os_scripts/delete_grid_log.sh /home/grid/os_scripts/delete_listener_log.sh /home/grid/os_scripts/modify_grid_alert_log.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ ls -l
/home/grid/os_scripts/
-rwxr--r--. 1 grid oinstall 641 Oct 2 00:12
delete_grid_log.sh
-rwxr--r--. 1 grid oinstall 158
Oct 2 00:13 delete_listener_log.sh
-rwxr--r--. 1
grid oinstall 1321 Oct 2 00:15
modify_grid_alert_log.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ crontab
-e
no crontab for grid - using an empty one
crontab: installing new crontab
[+ASM2:grid@ol8ora19rf2][/home/grid]$ crontab
-l
0 2 * * * /home/grid/os_scripts/delete_grid_log.sh
&>/dev/null
10 2 1 * *
/home/grid/os_scripts/modify_grid_alert_log.sh &>/dev/null
20 2 1 * * /home/grid/os_scripts/delete_listener_log.sh
&>/dev/null
11-3. adrci 로그 자동 삭제 기간을 100일로 설정 (grid 및 oracle os
user)
11-3-1. oracle user 기준 (ol8ora19rf1, ol8ora19rf2)
--1번 노드
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv
/home/oracle/os_scripts
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh << 'EOF'
echo "$ORACLE_SID : ""["$ORACLE_SID"]"
for i in `adrci
exec="show homes"|egrep "$ORACLE_SID|user_oracle" | grep -v "asmcmd" | grep -v
"ADR Homes"`;do
echo "adrci exec=\"set home $i;\""
adrci exec="set home $i;set control
\(SHORTP_POLICY=2400\);set control \(LONGP_POLICY=2400\)";
echo "complete adrci set control SHORTP_POLICY and
LONGP_POLICY is 2400;\""
done
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ sh
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh
ORA19RF1 :
[ORA19RF1]
adrci exec="set home
diag/asmtool/user_oracle/host_3429632378_110;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
adrci exec="set home diag/rdbms/ora19rf/ORA19RF1;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
adrci exec="set home
diag/clients/user_oracle/RMAN_3429632378_110;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
--2번 노드
--> 아래와 같은 에러가 날 경우
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle/os_scripts]$
adrci
ADRCI: Release 19.0.0.0.0 - Production on Thu Oct 2 16:18:28 2025
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
No ADR base is set
adrci> show
alert;
DIA-48494: ADR home is not set, the corresponding operation
cannot be done
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle/os_scripts]$
mkdir -p $ORACLE_HOME/log/diag
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle/os_scripts]$
printf "%s" "/u01/app/oracle" > $ORACLE_HOME/log/diag/adrci_dir.mif
--> 위의
조치를 취해줌
--작업 시작
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ mkdir -pv
/home/oracle/os_scripts
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ cat >
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh << 'EOF'
echo "$ORACLE_SID : ""["$ORACLE_SID"]"
for i in `adrci
exec="show homes"|egrep "$ORACLE_SID|user_oracle" | grep -v "asmcmd" | grep -v
"ADR Homes"`;do
echo "adrci exec=\"set home $i;\""
adrci exec="set home $i;set control
\(SHORTP_POLICY=2400\);set control \(LONGP_POLICY=2400\)";
echo "complete adrci set control SHORTP_POLICY and
LONGP_POLICY is 2400;\""
done
EOF
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ chmod 744
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh
[ORA19RF2:oracle@ol8ora19rf2][/home/oracle]$ sh
/home/oracle/os_scripts/delete_oracle_log_auto_adrci.sh
ORA19RF2 :
[ORA19RF2]
adrci exec="set home
diag/rdbms/ora19rf/ORA19RF2;"
complete adrci set control
SHORTP_POLICY and LONGP_POLICY is 2400;"
adrci exec="set
home diag/asmtool/user_oracle/host_2884398641_110;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
11-3-2. grid user
기준 (ol8ora19rf1, ol8ora19rf2)
--1번 노드
[+ASM1:grid@ol8ora19rf1][/home/grid]$ mkdir -pv
/home/grid/os_scripts
[+ASM1:grid@ol8ora19rf1][/home/grid]$ cat >
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh << 'EOF'
echo "\$ORACLE_USER_ORACLE_SID:
""["$ORACLE_USER_ORACLE_SID"]"
for i in `adrci
exec="show homes"|grep -v $ORACLE_USER_ORACLE_SID|grep -v user_oracle|grep -v
user_root|grep -v asmcmd | grep -v "ADR Homes"`;do
echo ""
echo
"---------------------------------------------------------------------------------------"
echo "START : ["$i"]"
echo "adrci
exec=\"set home $i;\""
adrci exec="set home $i;set
control \(SHORTP_POLICY=2400\);set control \(LONGP_POLICY=2400\)";
echo "complete adrci set control SHORTP_POLICY and
LONGP_POLICY is 2400;\""
echo "END : ["$i"]"
echo
"---------------------------------------------------------------------------------------"
echo ""
sleep 1
done
EOF
[+ASM1:grid@ol8ora19rf1][/home/grid]$ chmod 744
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh
[+ASM1:grid@ol8ora19rf1][/home/grid]$ sh
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh
$ORACLE_USER_ORACLE_SID: [ORA19RF1]
---------------------------------------------------------------------------------------
START : [diag/asmtool/user_grid/host_3429632378_110]
adrci exec="set home
diag/asmtool/user_grid/host_3429632378_110;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/asmtool/user_grid/host_3429632378_110]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/asm/+asm/+ASM1]
adrci
exec="set home diag/asm/+asm/+ASM1;"
complete adrci set
control SHORTP_POLICY and LONGP_POLICY is 2400;"
END :
[diag/asm/+asm/+ASM1]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/crs/ol8ora19rf1/crs]
adrci exec="set home diag/crs/ol8ora19rf1/crs;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/crs/ol8ora19rf1/crs]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf1/asmnet1lsnr_asm]
adrci exec="set home
diag/tnslsnr/ol8ora19rf1/asmnet1lsnr_asm;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/tnslsnr/ol8ora19rf1/asmnet1lsnr_asm]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf1/listener_scan1]
adrci exec="set home
diag/tnslsnr/ol8ora19rf1/listener_scan1;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf1/listener_scan1]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf1/listener_scan2]
adrci exec="set home
diag/tnslsnr/ol8ora19rf1/listener_scan2;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf1/listener_scan2]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf1/listener_scan3]
adrci exec="set home
diag/tnslsnr/ol8ora19rf1/listener_scan3;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf1/listener_scan3]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf1/listener]
adrci exec="set home diag/tnslsnr/ol8ora19rf1/listener;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/tnslsnr/ol8ora19rf1/listener]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/kfod/ol8ora19rf1/kfod]
adrci exec="set home diag/kfod/ol8ora19rf1/kfod;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/kfod/ol8ora19rf1/kfod]
---------------------------------------------------------------------------------------
--2번 노드
[+ASM2:grid@ol8ora19rf2][/home/grid]$ mkdir -pv
/home/grid/os_scripts
[+ASM2:grid@ol8ora19rf2][/home/grid]$ cat >
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh << 'EOF'
echo "\$ORACLE_USER_ORACLE_SID:
""["$ORACLE_USER_ORACLE_SID"]"
for i in `adrci
exec="show homes"|grep -v $ORACLE_USER_ORACLE_SID|grep -v user_oracle|grep -v
user_root|grep -v asmcmd | grep -v "ADR Homes"`;do
echo ""
echo
"---------------------------------------------------------------------------------------"
echo "START : ["$i"]"
echo "adrci
exec=\"set home $i;\""
adrci exec="set home $i;set
control \(SHORTP_POLICY=2400\);set control \(LONGP_POLICY=2400\)";
echo "complete adrci set control SHORTP_POLICY and
LONGP_POLICY is 2400;\""
echo "END : ["$i"]"
echo
"---------------------------------------------------------------------------------------"
echo ""
sleep 1
done
EOF
[+ASM2:grid@ol8ora19rf2][/home/grid]$ chmod 744
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh
[+ASM2:grid@ol8ora19rf2][/home/grid]$ sh
/home/grid/os_scripts/delete_grid_log_auto_adrci.sh
$ORACLE_USER_ORACLE_SID:
[ORA19RF2]
---------------------------------------------------------------------------------------
START : [diag/asm/+asm/+ASM2]
adrci
exec="set home diag/asm/+asm/+ASM2;"
complete adrci set
control SHORTP_POLICY and LONGP_POLICY is 2400;"
END :
[diag/asm/+asm/+ASM2]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/crs/ol8ora19rf2/crs]
adrci exec="set home diag/crs/ol8ora19rf2/crs;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/crs/ol8ora19rf2/crs]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf2/asmnet1lsnr_asm]
adrci exec="set home
diag/tnslsnr/ol8ora19rf2/asmnet1lsnr_asm;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/tnslsnr/ol8ora19rf2/asmnet1lsnr_asm]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf2/listener_scan1]
adrci exec="set home
diag/tnslsnr/ol8ora19rf2/listener_scan1;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf2/listener_scan1]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf2/listener]
adrci exec="set home diag/tnslsnr/ol8ora19rf2/listener;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/tnslsnr/ol8ora19rf2/listener]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf2/listener_scan2]
adrci exec="set home
diag/tnslsnr/ol8ora19rf2/listener_scan2;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf2/listener_scan2]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora19rf2/listener_scan3]
adrci exec="set home
diag/tnslsnr/ol8ora19rf2/listener_scan3;"
complete adrci
set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END
: [diag/tnslsnr/ol8ora19rf2/listener_scan3]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/asmtool/user_grid/host_2884398641_110]
adrci exec="set home
diag/asmtool/user_grid/host_2884398641_110;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/asmtool/user_grid/host_2884398641_110]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/kfod/ol8ora19rf2/kfod]
adrci exec="set home diag/kfod/ol8ora19rf2/kfod;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/kfod/ol8ora19rf2/kfod]
---------------------------------------------------------------------------------------
12. RMAN 백업 설정 (ol8ora19rf1, oracle os user)
12-1. 백업 디렉토리 생성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -pv /home/oracle/rman_backup/
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias cdrb
alias cdrb='cd
/home/oracle/rman_backup/$ORACLE_DBNAME'
12-2. RMAN 초기 설정 스크립트 작성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
cdoss
alias cdoss='cd
/home/oracle/os_scripts'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cdoss
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/os_scripts]$
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/rman_init.sh << 'EOF2'
#!/bin/bash
export
ORACLE_HOME=/u01/app/oracle/product/19c/db_1
export
ORACLE_SID=ORA19RF1
export ORACLE_DBNAME=ORA19RF
export ORACLE_UNQNAME=ORA19RF
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
mkdir -p /home/oracle/rman_backup/$ORACLE_DBNAME/autobackup/
$ORACLE_HOME/bin/rman target / << EOF
run {
CONFIGURE RETENTION POLICY TO
REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE
DISK TO '/home/oracle/rman_backup/$ORACLE_DBNAME/autobackup/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+DATA1/$ORACLE_UNQNAME/CONTROLFILE/snapcf_CA.f';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO
BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE
TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR
DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG DELETION
POLICY TO NONE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 7
DAYS;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128';
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE
'DEFAULT' OPTIMIZE FOR LOAD TRUE;
#CONFIGURE ARCHIVELOG
DELETION POLICY TO APPLIED ON ALL STANDBY; #For ADG
}
exit;
EOF
EOF2
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744 /home/oracle/os_scripts/rman_init.sh
12-3. RMAN 데이터 백업 스크립트 작성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/rman_data.sh << 'EOF2'
#!/bin/bash
export
ORACLE_HOME=/u01/app/oracle/product/19c/db_1
export
ORACLE_SID=ORA19RF1
export ORACLE_DBNAME=ORA19RF
export ORACLE_UNQNAME=ORA19RF
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
mkdir -p /home/oracle/os_scripts/rman_log/
save_datetime=`date +"%Y%m%d%H%M%S"`
RMAN_SCRIPT="/home/oracle/os_scripts/rman_data_exec.sh"
rm -f $RMAN_SCRIPT
echo "RUN {" >
$RMAN_SCRIPT
echo "export ORACLE_HOME=$ORACLE_HOME"
echo "export ORACLE_SID=$ORACLE_SID"
echo "sql 'alter system archive log current';" >>
$RMAN_SCRIPT
echo "sql 'alter system checkpoint';"
>> $RMAN_SCRIPT
echo "crosscheck backupset;"
>> $RMAN_SCRIPT
echo "crosscheck backup;" >>
$RMAN_SCRIPT
echo "crosscheck copy;" >>
$RMAN_SCRIPT
echo "crosscheck archivelog all;" >>
$RMAN_SCRIPT
TABLESPACE_NAME_FILE="/home/oracle/os_scripts/tablespaces_list.txt"
sqlplus -S / as
sysdba <<EOF > $TABLESPACE_NAME_FILE
SET
HEADING OFF
SET FEEDBACK OFF
SET
PAGESIZE 0
SET LINESIZE 100
SET
TIMING OFF
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES
WHERE CONTENTS IN ('PERMANENT', 'UNDO');
EXIT;
EOF
while read -r tablespace_name; do
if [ -n "$tablespace_name" ]; then
echo "BACKUP AS
COMPRESSED BACKUPSET TABLESPACE $tablespace_name FORMAT
'/home/oracle/rman_backup/${ORACLE_DBNAME}/${tablespace_name}_%U.bkp';" >>
$RMAN_SCRIPT
fi
done < $TABLESPACE_NAME_FILE
#echo "backup current controlfile format
'/home/oracle/rman_backup/$ORACLE_DBNAME/CTL_%U';" >> $RMAN_SCRIPT
echo "BACKUP SPFILE FORMAT
'/home/oracle/rman_backup/$ORACLE_DBNAME/autobackup/SP_%U'";
echo "delete noprompt obsolete;" >> $RMAN_SCRIPT
echo "delete noprompt expired backup;" >>
$RMAN_SCRIPT
echo "}" >> $RMAN_SCRIPT
rm -f $TABLESPACE_NAME_FILE
chmod
775 $RMAN_SCRIPT
rman target / cmdfile=$RMAN_SCRIPT > /home/oracle/os_scripts/rman_log/rman_data_exec_log_${save_datetime}.log
echo "Backup completed."
EOF2
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744 /home/oracle/os_scripts/rman_data.sh
12-4. RMAN 아카이브로그 백업 스크립트 작성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat
> /home/oracle/os_scripts/rman_arch.sh << 'EOF2'
export ORACLE_SID=ORA19RF1
export
ORACLE_DBNAME=ORA19RF
export ORACLE_UNQNAME=ORA19RF
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
mkdir -pv /home/oracle/rman_backup/$ORACLE_DBNAME
$ORACLE_HOME/bin/rman target / << EOF
run {
crosscheck archivelog all;
backup archivelog all format
'/home/oracle/rman_backup/$ORACLE_DBNAME/%d_ARCHIVE_%T_%u_s%s_p%p' delete
input;
delete backup of archivelog all completed before
'SYSDATE-14';
}
exit;
EOF
EOF2
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744 /home/oracle/os_scripts/rman_arch.sh
12-5. RMAN 백업 수행 스크립트 작성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
cdrbl
alias cdrbl='cd
/home/oracle/os_scripts/rman_log'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/os_scripts/rman_backup.sh << 'EOF'
mkdir -pv
/home/oracle/os_scripts/rman_log
save_datetime=`date +"%Y%m%d%H%M%S"`
/home/oracle/os_scripts/rman_init.sh >
/home/oracle/os_scripts/rman_log/rman_init_log_${save_datetime}.log
/home/oracle/os_scripts/rman_data.sh >
/home/oracle/os_scripts/rman_log/rman_data_log_${save_datetime}.log
/home/oracle/os_scripts/rman_arch.sh >
/home/oracle/os_scripts/rman_log/rman_arch_log_${save_datetime}.log
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod 744 /home/oracle/os_scripts/rman_backup.sh
12-6. RMAN 백업 수행 스크립트를 CRON에 등록
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ crontab -e
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ crontab -l |
grep rman_backup
20 2 * * *
/home/oracle/os_scripts/rman_backup.sh
&>/dev/null
13. 오라클 각종 스크립트 설치
--1번 및 2번 노드 동일하게
수행
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -p
/home/oracle/oracle_scripts
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias cdos
alias cdos='cd
/home/oracle/oracle_scripts'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
cat <<EOF
> /home/oracle/oracle_scripts/al.sql
COL NAME FOR
A70
SELECT A.RECID
, A.NAME
, A.THREAD#
, A.SEQUENCE#
, A.RESETLOGS_CHANGE#
, A.RESETLOGS_TIME
, A.RESETLOGS_ID
,
ROUND((A.BLOCKS*A.BLOCK_SIZE)/1024/1024, 2) AS FILE_SIZE_MB
, A.STATUS
FROM V\$ARCHIVED_LOG A
ORDER BY A.THREAD#, A.SEQUENCE#
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/asm.sql
SET LINESIZE
300
COL GROUP_NAME FOR A8
COL
GROUP_STATE FOR A10
COL GROUP_TYPE FOR A8
COL DISK_MOUNT_STATUS FOR A8
COL
DISK_MODE_STATUS FOR A8
COL DISK_LABEL FOR A8
COL DISK_NAME FOR A8
COL DISK_PATH
FOR A30
SELECT B.GROUP_NUMBER
, B.NAME AS GROUP_NAME
, B.STATE AS GROUP_STATE
, B.TYPE AS GROUP_TYPE
, B.TOTAL_MB AS GROUP_TOTAL_MB
, B.FREE_MB AS GROUP_FREE_MB
, B.TOTAL_MB-B.FREE_MB AS
GROUP_USED_MB
,
ROUND(((B.TOTAL_MB-B.FREE_MB)/B.TOTAL_MB)*100, 2) AS GROUP_USED_RATIO
, A.DISK_NUMBER AS DISK_NUMBER
-- , A.MOUNT_STATUS AS
DISK_MOUNT_STATUS
,
A.MODE_STATUS AS DISK_MODE_STATUS
, A.TOTAL_MB AS DISK_TOTAL_MB
, A.FREE_MB AS DISK_FREE_MB
, A.TOTAL_MB-A.FREE_MB AS
DISK_USED_MB
,
ROUND(((A.TOTAL_MB-A.FREE_MB)/A.TOTAL_MB)*100, 2) AS DISK_USED_RATIO
, A.LABEL AS DISK_LABEL
-- , A.NAME AS DISK_NAME
, A.PATH AS DISK_PATH
FROM V\$ASM_DISK A
, V\$ASM_DISKGROUP B
WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
ORDER BY B.GROUP_NUMBER, A.DISK_NUMBER
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/asmf.sql
COL GROUP_NAME
FOR A10
COL TYPE FOR A20
SELECT
A.GROUP_NUMBER
, (SELECT L.NAME
FROM V\$ASM_DISKGROUP L WHERE L.GROUP_NUMBER = A.GROUP_NUMBER) AS GROUP_NAME
, A.FILE_NUMBER
, ROUND(A.BYTES/1024/1024, 2) AS
SIZE_MB
, A.TYPE
, A.REDUNDANCY
, A.STRIPED
, A.PERMISSIONS
FROM V\$ASM_FILE A
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/cf.sql
COL NAME FOR
A60
SELECT A.NAME
FROM
V\$CONTROLFILE A
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/fra.sql
COL "FRA NAME"
FOR A10
SELECT NAME AS FRA_NAME
, ROUND(SPACE_LIMIT/1024/1024, 2)
AS SPACE_LIMIT_MB
,
ROUND(SPACE_USED/1024/1024, 2) AS SPACE_USED_MB
, SPACE_RECLAIMABLE AS
SPACE_RECLAIMABLE_MB
,
ROUND((SPACE_LIMIT-SPACE_USED)/1024/1024, 2) AS FREE_SPACE_MB
FROM V\$RECOVERY_FILE_DEST
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/hpara.sql
col
"Parameter" format a40
col "Session Value" format a15
col "Instance Value" Format a15
select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx =
c.indx
and a.ksppinm like
'%¶m_name%'
;
EOF
cat <<EOF > /home/oracle/oracle_scripts/para.sql
col NAME for
a40
col NAME for a20
col VAL for
a10
col DIS_VAL for a10
col
DFT_VAL for a10
col IS_DFT for a10
col ISSES_MODI for a10
col
ISSYS_MODI for a10
col ISINS_MODI for a10
col ISPDB_MODI for a10
col SP_VAL
for a10
col SP_DIS_VAL for a10
SELECT A.INST_ID
, A.NAME AS NAME
, A.VALUE AS VAL
, A.DISPLAY_VALUE AS DIS_VAL
, A.DEFAULT_VALUE AS DFT_VAL
, A.ISDEFAULT AS IS_DFT
, A.ISSES_MODIFIABLE AS
ISSES_MODI
, A.ISSYS_MODIFIABLE
AS ISSYS_MODI
,
A.ISINSTANCE_MODIFIABLE AS ISINS_MODI
, A.ISPDB_MODIFIABLE AS
ISPDB_MODI
, (SELECT B.VALUE
FROM GV\$SPPARAMETER B WHERE A.INST_ID = B.INST_ID AND A.NAME = B.NAME AND
ROWNUM <= 1) AS SP_VAL
,
(SELECT B.DISPLAY_VALUE FROM GV\$SPPARAMETER B WHERE A.INST_ID = B.INST_ID AND
A.NAME = B.NAME AND ROWNUM <= 1) AS SP_DIS_VAL
FROM GV\$PARAMETER A
WHERE UPPER(A.NAME) LIKE '%'
|| UPPER('&PARAMETER') ||'%'
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/rdl.sql
col group# for
999
col thread# for 999
col
member for a50
SELECT A.GROUP#
, B.THREAD#
, B.SEQUENCE#
, A.MEMBER
, A.STATUS
, A.TYPE
, B.BYTES/1024/1024 AS MB
, B.STATUS
FROM V\$LOGFILE A
, V\$LOG B
WHERE A.GROUP# = B.GROUP#
ORDER BY 1
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/tbs.sql
COL
TABLESPACE_NAME FOR A10
COL USED_RATIO FOR A10
SELECT T.TABLESPACE_NAME
, ROUND(T.TOTAL_SIZE/1024/1024, 2)
AS TOTAL_SIZE_MB
,
ROUND((T.TOTAL_SIZE-F.FREE_SIZE)/1024/1024, 2) AS USED_SIZE_MB
,
ROUND(ROUND((T.TOTAL_SIZE-F.FREE_SIZE)/1024/1024,
2)/ROUND(T.TOTAL_SIZE/1024/1024, 2) * 100, 2) || '%' AS USED_RATIO
, ROUND(F.FREE_SIZE/1024/1024, 2)
AS FREE_SIZE_MB
FROM (SELECT TABLESPACE_NAME
, SUM(BYTES) AS TOTAL_SIZE
FROM
DBA_DATA_FILES
GROUP BY
TABLESPACE_NAME) T
, (SELECT
TABLESPACE_NAME
, SUM(BYTES) AS FREE_SIZE
FROM
DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME) F
WHERE T.TABLESPACE_NAME =
F.TABLESPACE_NAME
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/tsdf.sql
col file_name
for a50
col TABLESPACE_NAME for a20
col TS_STATUS for
a10
col LOGGING for a10
col
CONTENTS for a10
SELECT A.TABLESPACE_NAME
, A.STATUS AS TS_STATUS
, A.CONTENTS
, A.LOGGING
, B.FILE_NAME
, B.FILE_ID
, B.BYTES/1024/1024 AS MB
, B.STATUS AS FILE_STATUS
, B.AUTOEXTENSIBLE
, TRUNC(B.MAXBYTES/1024/1024) AS
MAX_MB
, B.ONLINE_STATUS AS
FILE_ONLINE_STATUS
FROM DBA_TABLESPACES A
, DBA_DATA_FILES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
UNION ALL
SELECT A.TABLESPACE_NAME
,
A.STATUS AS TS_STATUS
,
A.CONTENTS
, A.LOGGING
, B.FILE_NAME
, B.FILE_ID
, B.BYTES/1024/1024 AS MB
, B.STATUS AS FILE_STATUS
, B.AUTOEXTENSIBLE
, TRUNC(B.MAXBYTES/1024/1024) AS
MAX_MB
, '' AS
FILE_ONLINE_STATUS
FROM DBA_TABLESPACES A
, DBA_TEMP_FILES B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1
;
EOF
cat <<EOF
> /home/oracle/oracle_scripts/mys.sql
COL USERNAME
FOR A10
COL SID FOR 9999999999
COL SERIAL# FOR 9999999999
COL SPID
FOR A10
SELECT S.USERNAME
, S.SID
, S.SERIAL#
, P.SPID
FROM V\$SESSION S
, V\$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SID IN (SELECT DISTINCT SID FROM
V\$MYSTAT)
;
EOF
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ chmod -R 775 /home/oracle/oracle_scripts
14. SQL 실습 환경 구축
14-1. 사용자 계정 및 테이블 스페이스 생성 스크립트 파일 생성
--> 아래의 스크립트를 sqlplus로
실행
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/oracle_scripts/1.USER_TABLESPACE_SYS_SYSTEM_v1_0.sql <<
'EOF'
----------------------------------------------------------------------------------------------------------------------------------------
--1. 사용자 계정 생성 및 테이블 스페이스 생성
----------------------------------------------------------------------------------------------------------------------------------------
DROP USER TUNER CASCADE;
CREATE
USER TUNER IDENTIFIED BY "oracle";
GRANT RESOURCE, DBA,
CONNECT TO TUNER;
ALTER USER TUNER ACCOUNT
UNLOCK;
DROP TABLESPACE
TUNER_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE TUNER_DATA1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE TUNER_IDX1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE
TEMPORARY TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1' SIZE
128M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;
ALTER USER TUNER
DEFAULT TABLESPACE TUNER_DATA1;
ALTER USER TUNER
TEMPORARY TABLESPACE TUNER_TEMP;
EOF
14-2. 테이블 생성 및 데이터 입력 스크립트 파일 생성
--> 아래의 스크립트를 sqlplus로
실행
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ cat >
/home/oracle/oracle_scripts/2.TABLE_INDEX_MERGE_STATISTICS_TUNER_v1_1.sql
<< 'EOF'
----------------------------------------------------------------------------------------------------------------------------------------
--2. 바인드 변수 세팅 (tuner 계정으로 실행할 것)
--SYS@PTDB> conn tuner
--Enter password:
--Connected.
--TUNER@PTDB>
----------------------------------------------------------------------------------------------------------------------------------------
--conn tuner;
VAR V_TB_CUST_CNT
NUMBER;
EXEC :V_TB_CUST_CNT :=
10000;
VAR V_TB_ITEM_CNT
NUMBER;
EXEC :V_TB_ITEM_CNT := 1000;
--:V_TB_CUST_CNT 1000000 String
--:V_TB_ITEM_CNT 100000 String
--예시
--V_TB_CUST_CNT가 100만인 경우
----TB_CUST : 100만
----TB_CUST_ADDR
: 200만
----TB_ORD : 1000만
----TB_ORD_DTL : 5000만
----TB_DLVY:
3000만
--V_TB_ITEM_CNT가 10만인 경우
----TB_ITEM : 10만
----------------------------------------------------------------------------------------------------------------------------------------
--3. 테이블, 인덱스, 데이터입력, 통계정보 생성
----------------------------------------------------------------------------------------------------------------------------------------
ALTER SESSION SET "_px_hybrid_TSM_HWMB_load"=FALSE;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS';
----------------------------------------------------------------------------------------------------------------------------------------
--시작시간
COLUMN START_TIME NEW_VALUE
START_TIME
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD
HH24:MI:SS.FF') AS START_TIME FROM DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE TUNER.TB_CUST PURGE;
DROP TABLE TUNER.TB_CUST_ADDR PURGE;
DROP TABLE TUNER.TB_DLVY PURGE;
DROP TABLE TUNER.TB_ITEM PURGE;
DROP TABLE TUNER.TB_ORD PURGE;
DROP
TABLE TUNER.TB_ORD_DTL PURGE;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID VARCHAR2(20) NOT NULL
,
CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
,
JOIN_DT VARCHAR2(14) NOT NULL
, CUST_STS_CD VARCHAR2(6)
NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
,
UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_ID IS '고객아이디';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_NM IS '고객명';
COMMENT ON COLUMN
TUNER.TB_CUST.BRTHDY IS '생년월일';
COMMENT ON COLUMN
TUNER.TB_CUST.SEX_CD IS '성별코드';
COMMENT ON COLUMN
TUNER.TB_CUST.JOIN_DT IS '가입일시';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_DT IS '입력일시';
COMMENT ON COLUMN
TUNER.TB_CUST.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN
TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON TABLE
TUNER.TB_CUST IS '고객';
ALTER TABLE TUNER.TB_CUST NOLOGGING;
INSERT /*+ APPEND
*/
INTO TUNER.TB_CUST
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4,
'0') AS SEX_CD
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10),
'YYYYMMDDHH24MISS') AS JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
:V_TB_CUST_CNT;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_CUST ON TUNER.TB_CUST(CUST_NO) TABLESPACE TUNER_IDX1 PARALLEL
4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_CUST PARALLEL 1;
ALTER INDEX TUNER.PK_TB_CUST LOGGING;
ALTER TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST PRIMARY
KEY (CUST_NO);
CREATE INDEX
TUNER.IDX_TB_CUST_01 ON TUNER.TB_CUST(CUST_STS_CD) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_01
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_01
LOGGING;
CREATE INDEX
TUNER.IDX_TB_CUST_02 ON TUNER.TB_CUST(BRTHDY) TABLESPACE TUNER_IDX1 PARALLEL 4
NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_02 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_CUST_02
LOGGING;
CREATE INDEX
TUNER.IDX_TB_CUST_03 ON TUNER.TB_CUST(CUST_STS_CD, BRTHDY) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_CUST_03
LOGGING;
ALTER TABLE
TUNER.TB_CUST LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_CUST_ADDR
(
CUST_NO VARCHAR2(10) NOT
NULL
, ADDR_SN NUMBER(6) NOT NULL
, POST_NO VARCHAR2(10) NOT NULL
,
ADDR VARCHAR2(500) NOT NULL
, REG_DT VARCHAR2(14) NOT
NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
,
UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.ADDR_SN IS '주소순번';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.POST_NO IS '우편번호';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.ADDR IS '주소';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.REG_DT IS '등록일시';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.INPUT_ID IS '입력아이디';
COMMENT ON
COLUMN TUNER.TB_CUST_ADDR.INPUT_DT IS '입력일시';
COMMENT ON
COLUMN TUNER.TB_CUST_ADDR.UPDT_ID IS '수정아이디';
COMMENT ON
COLUMN TUNER.TB_CUST_ADDR.UPDT_DT IS '수정일시';
COMMENT ON
TABLE TUNER.TB_CUST_ADDR IS '고객주소';
ALTER TABLE TUNER.TB_CUST_ADDR NOLOGGING;
INSERT /*+ APPEND
*/
INTO TUNER.TB_CUST_ADDR
SELECT A.CUST_NO
, B.LVL AS ADDR_SN
, TRUNC(DBMS_RANDOM.VALUE(11111,
99999)) AS POST_NO
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(50, 100))) AS ADDR
, TO_CHAR(TO_DATE(A.JOIN_DT,
'YYYYMMDDHH24MISS') + TRUNC(DBMS_RANDOM.VALUE(1, 240))/24, 'YYYYMMDDHH24MISS')
AS REG_DT
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM TUNER.TB_CUST A
, (SELECT LEVEL AS LVL FROM DUAL
CONNECT BY LEVEL <= 2) B
;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_CUST_ADDR ON TUNER.TB_CUST_ADDR (CUST_NO, ADDR_SN) TABLESPACE
TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.PK_TB_CUST_ADDR PARALLEL 1;
ALTER INDEX
TUNER.PK_TB_CUST_ADDR LOGGING;
ALTER TABLE
TUNER.TB_CUST_ADDR ADD CONSTRAINT PK_TB_CUST_ADDR PRIMARY KEY
(CUST_NO,ADDR_SN);
ALTER TABLE
TUNER.TB_CUST_ADDR LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_ORD
(
CUST_NO VARCHAR2(10) NOT NULL
, ORD_DT VARCHAR2(14) NOT NULL
, ORD_TOT_AMT NUMBER(15, 2) NOT NULL
, ORD_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
PARTITION BY RANGE(ORD_DT)
(
PARTITION P_BEF_2020 VALUES LESS THAN
('20200101')
, PARTITION P2020 VALUES LESS THAN
('20210101')
, PARTITION P2021 VALUES LESS THAN
('20220101')
, PARTITION P2022 VALUES LESS THAN
('20230101')
, PARTITION P2023 VALUES LESS THAN
('20240101')
, PARTITION P2024 VALUES LESS THAN
('20250101')
, PARTITION P2025 VALUES LESS THAN
('20260101')
, PARTITION P2026 VALUES LESS THAN
('20270101')
, PARTITION P2027 VALUES LESS THAN
('20280101')
, PARTITION P2028 VALUES LESS THAN
('20290101')
, PARTITION P2029 VALUES LESS THAN
('20300101')
, PARTITION P2030 VALUES LESS THAN
('20310101')
, PARTITION P_AFT_2030 VALUES LESS
THAN (MAXVALUE)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_ORD.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_ORD.ORD_DT IS '주문일시';
COMMENT ON COLUMN
TUNER.TB_ORD.ORD_TOT_AMT IS '주문총금액';
COMMENT ON COLUMN
TUNER.TB_ORD.ORD_STS_CD IS '주문상태코드';
COMMENT ON COLUMN
TUNER.TB_ORD.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_ORD.INPUT_DT IS '입력일시';
COMMENT ON COLUMN
TUNER.TB_ORD.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN
TUNER.TB_ORD.UPDT_DT IS '수정일시';
COMMENT ON TABLE
TUNER.TB_ORD IS '주문';
ALTER TABLE TUNER.TB_ORD NOLOGGING;
INSERT /*+ APPEND
*/
INTO TUNER.TB_ORD
SELECT /*+ PARALEL(4) ENABLE_PARALLEL_DML */
A.CUST_NO
, TO_CHAR(TO_DATE(A.JOIN_DT,
'YYYYMMDDHH24MISS') + B.LVL/24/60, 'YYYYMMDDHH24MISS') AS ORD_DT
, 0 AS ORD_TOT_AMT
, CASE MOD(ROWNUM, 4) WHEN 0 THEN
'OSC001' WHEN 1 THEN 'OSC002' WHEN 2 THEN 'OSC003' WHEN 3 THEN 'OSC004'
END AS ORD_STS_CD
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM TUNER.TB_CUST A
, (SELECT LEVEL AS LVL FROM DUAL
CONNECT BY LEVEL <= 10) B
;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_ORD ON TUNER.TB_ORD(CUST_NO, ORD_DT) TABLESPACE TUNER_IDX1
PARALLEL 4 LOCAL NOLOGGING;
ALTER INDEX TUNER.PK_TB_ORD
PARALLEL 1;
ALTER INDEX TUNER.PK_TB_ORD
LOGGING;
ALTER TABLE TUNER.TB_ORD ADD CONSTRAINT PK_TB_ORD PRIMARY
KEY (CUST_NO, ORD_DT);
CREATE INDEX
TUNER.IDX_TB_ORD_01 ON TUNER.TB_ORD(ORD_STS_CD) TABLESPACE TUNER_IDX1 LOCAL
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_01
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_ORD_01
LOGGING;
ALTER TABLE
TUNER.TB_ORD LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_DLVY
(
CUST_NO VARCHAR2(10) NOT NULL
, ORD_DT VARCHAR2(14) NOT NULL
,
DLVY_SN NUMBER(6) NOT NULL
, DLVY_DT VARCHAR2(14) NOT
NULL
, DLVY_STS_CD VARCHAR2(6) NOT NULL
, HDRY_CMPNY_CD VARCHAR2(6)
,
WAYBIL_NO VARCHAR2(50)
, ADDR_SN NUMBER(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
,
INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
PARTITION BY RANGE (ORD_DT)
SUBPARTITION BY HASH (CUST_NO)
SUBPARTITIONS 8
(
PARTITION P_BEF_2020 VALUES LESS THAN
('20200101')
, PARTITION P2020 VALUES LESS THAN
('20210101')
, PARTITION P2021 VALUES LESS THAN
('20220101')
, PARTITION P2022 VALUES LESS THAN
('20230101')
, PARTITION P2023 VALUES LESS THAN
('20240101')
, PARTITION P2024 VALUES LESS THAN
('20250101')
, PARTITION P2025 VALUES LESS THAN
('20260101')
, PARTITION P2026 VALUES LESS THAN
('20270101')
, PARTITION P2027 VALUES LESS THAN
('20280101')
, PARTITION P2028 VALUES LESS THAN
('20290101')
, PARTITION P2029 VALUES LESS THAN
('20300101')
, PARTITION P2030 VALUES LESS THAN
('20310101')
, PARTITION P_AFT_2030 VALUES LESS THAN
(MAXVALUE)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_DLVY.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_DLVY.ORD_DT IS '주문일시';
COMMENT ON COLUMN
TUNER.TB_DLVY.DLVY_SN IS '배송순번';
COMMENT ON COLUMN
TUNER.TB_DLVY.DLVY_DT IS '배송일시';
COMMENT ON COLUMN
TUNER.TB_DLVY.DLVY_STS_CD IS '배송상태코드';
COMMENT ON COLUMN
TUNER.TB_DLVY.HDRY_CMPNY_CD IS '택배사코드';
COMMENT ON
COLUMN TUNER.TB_DLVY.WAYBIL_NO IS '운송장번호';
COMMENT ON
COLUMN TUNER.TB_DLVY.ADDR_SN IS '주소순번';
COMMENT ON
COLUMN TUNER.TB_DLVY.INPUT_ID IS '입력아이디';
COMMENT ON
COLUMN TUNER.TB_DLVY.INPUT_DT IS '입력일시';
COMMENT ON
COLUMN TUNER.TB_DLVY.UPDT_ID IS '수정아이디';
COMMENT ON
COLUMN TUNER.TB_DLVY.UPDT_DT IS '수정일시';
COMMENT ON TABLE
TUNER.TB_DLVY IS '배송';
ALTER TABLE TUNER.TB_DLVY NOLOGGING;
INSERT /*+
ENABLE_PARALLEL_DML APPEND */
INTO
TUNER.TB_DLVY
SELECT /*+ PARALLEL(4) LEADING(A B L)
USE_HASH(L) NO_MERGE(L) NO_SWAP_JOIN_INPUTS(L) */
A.CUST_NO
, A.ORD_DT
, B.LVL AS DLVY_SN
, TO_CHAR(TO_DATE(A.ORD_DT,
'YYYYMMDDHH24MISS') + TRUNC(DBMS_RANDOM.VALUE(1, 240))/24, 'YYYYMMDDHH24MISS')
AS DLVY_DT
, 'DSC' ||
LPAD(MOD(ROWNUM, 5), 3, '0') AS DLVY_STS_CD
, 'HCC' || LPAD(MOD(ROWNUM, 20),
3, '0') AS HDRY_CMPNY_CD
,
LPAD(ROWNUM, 20, '0') AS WAYBIL_NO
, L.ADDR_SN
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM TUNER.TB_ORD A
INNER JOIN (SELECT LEVEL LVL FROM DUAL CONNECT BY
LEVEL <= 3) B ON (1=1)
LEFT OUTER JOIN (
SELECT *
FROM
(
SELECT L.CUST_NO, L.ADDR_SN, ROW_NUMBER() OVER (PARTITION BY L.CUST_NO ORDER BY
DBMS_RANDOM.VALUE) AS RN
FROM TUNER.TB_CUST_ADDR L
) L
WHERE L.RN = 1
) L
ON (A.CUST_NO = L.CUST_NO)
;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_DLVY ON TUNER.TB_DLVY(CUST_NO, ORD_DT, DLVY_SN) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.PK_TB_DLVY PARALLEL 1;
ALTER INDEX
TUNER.PK_TB_DLVY LOGGING;
ALTER TABLE TUNER.TB_DLVY ADD
CONSTRAINT PK_TB_DLVY PRIMARY KEY (CUST_NO, ORD_DT,
DLVY_SN);
CREATE INDEX
TUNER.IDX_TB_DLVY_01 ON TUNER.TB_DLVY(CUST_NO, ADDR_SN) TABLESPACE TUNER_IDX1
LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_DLVY_01 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_DLVY_01 LOGGING;
CREATE INDEX
TUNER.IDX_TB_DLVY_02 ON TUNER.TB_DLVY(DLVY_STS_CD, DLVY_DT) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_DLVY_02 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_DLVY_02 LOGGING;
CREATE INDEX
TUNER.IDX_TB_DLVY_03 ON TUNER.TB_DLVY(HDRY_CMPNY_CD, WAYBIL_NO) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_DLVY_03 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_DLVY_03 LOGGING;
ALTER TABLE
TUNER.TB_DLVY LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_ITEM
(
ITEM_NO VARCHAR2(10) NOT NULL
, ITEM_NM VARCHAR2(500) NOT NULL
,
ITEM_PRC NUMBER(15, 2) NOT NULL
, REG_DT VARCHAR2(14)
NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
,
UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_ITEM.ITEM_NO IS '상품번호';
COMMENT ON COLUMN
TUNER.TB_ITEM.ITEM_NM IS '상품명';
COMMENT ON COLUMN
TUNER.TB_ITEM.ITEM_PRC IS '상품가격';
COMMENT ON COLUMN
TUNER.TB_ITEM.REG_DT IS '등록일시';
COMMENT ON COLUMN
TUNER.TB_ITEM.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_ITEM.INPUT_DT IS '입력일시';
COMMENT ON COLUMN
TUNER.TB_ITEM.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN
TUNER.TB_ITEM.UPDT_DT IS '수정일시';
COMMENT ON TABLE
TUNER.TB_ITEM IS '상품';
ALTER TABLE
TUNER.TB_ITEM NOLOGGING;
INSERT /*+ APPEND */
INTO TUNER.TB_ITEM
SELECT
'I' || LPAD(ROWNUM, 9, '0') AS ITEM_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(50, 150))) AS ITEM_NM
, TRUNC(DBMS_RANDOM.VALUE(5000,
100000), -3) AS ITEM_PRC
,
TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10),
'YYYYMMDDHH24MISS') AS REG_DT
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(1, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
:V_TB_ITEM_CNT;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_ITEM ON TUNER.TB_ITEM ( ITEM_NO ) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_ITEM
PARALLEL 1;
ALTER INDEX TUNER.PK_TB_ITEM LOGGING;
ALTER TABLE TUNER.TB_ITEM ADD CONSTRAINT PK_TB_ITEM PRIMARY
KEY (ITEM_NO);
ALTER TABLE
TUNER.TB_ITEM LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE TUNER.TB_ORD_DTL
(
CUST_NO VARCHAR2(10) NOT NULL
, ORD_DT VARCHAR2(14) NOT NULL
,
ORD_DTL_SN NUMBER(6) NOT NULL
, ORD_DTL_STS_CD
VARCHAR2(6) NOT NULL
, ORD_AMT NUMBER(15, 2) NOT NULL
, ITEM_NO VARCHAR2(10) NOT NULL
,
DLVY_SN VARCHAR2(14)
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
,
UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
PARTITION BY RANGE (ORD_DT)
SUBPARTITION BY HASH (CUST_NO)
SUBPARTITIONS 8
(
PARTITION P_BEF_2020 VALUES LESS THAN
('20200101')
, PARTITION P2020 VALUES LESS THAN
('20210101')
, PARTITION P2021 VALUES LESS THAN
('20220101')
, PARTITION P2022 VALUES LESS THAN
('20230101')
, PARTITION P2023 VALUES LESS THAN
('20240101')
, PARTITION P2024 VALUES LESS THAN
('20250101')
, PARTITION P2025 VALUES LESS THAN
('20260101')
, PARTITION P2026 VALUES LESS THAN
('20270101')
, PARTITION P2027 VALUES LESS THAN
('20280101')
, PARTITION P2028 VALUES LESS THAN
('20290101')
, PARTITION P2029 VALUES LESS THAN
('20300101')
, PARTITION P2030 VALUES LESS THAN
('20310101')
, PARTITION P_AFT_2030 VALUES LESS THAN
(MAXVALUE)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_DT IS '주문일시';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_DTL_SN IS '주문상세순번';
COMMENT ON
COLUMN TUNER.TB_ORD_DTL.ORD_DTL_STS_CD IS '주문상세상태코드';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.ORD_AMT IS '주문금액';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.ITEM_NO IS '상품번호';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.DLVY_SN IS '배송순번';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.INPUT_DT IS '입력일시';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN TUNER.TB_ORD_DTL.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_ORD_DTL IS
'주문상세';
ALTER TABLE TUNER.TB_ORD_DTL NOLOGGING;
INSERT /*+
ENABLE_PARALLEL_DML APPEND */
INTO
TUNER.TB_ORD_DTL
SELECT /*+ PARALLEL(4) NO_MERGE(A)
LEADING(A) USE_HASH(B) */A.CUST_NO
, A.ORD_DT
, A.ORD_DTL_SN
, 'ODSC' || LPAD(MOD(ROWNUM, 5),
2, '0') AS ORD_DTL_STS_CD
,
B.ITEM_PRC AS ORD_AMT
,
A.ITEM_NO
-- , CASE WHEN
A.ORD_DTL_SN > A.MAX_DLVY_SN THEN MOD(A.ORD_DTL_SN, A.MAX_DLVY_SN)
-- ELSE
A.DLVY_SN
-- END AS DLVY_SN
, CASE WHEN A.ORD_DTL_SN >
A.MAX_DLVY_SN THEN NULL
ELSE
A.DLVY_SN
END AS DLVY_SN
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
, TO_CHAR(SYSDATE,
'YYYYMMDDHH24MISS') AS INPUT_DT
, NULL AS UPDT_ID
, NULL AS
UPDT_DT
FROM
(
SELECT /*+
LEADING(A B L) USE_HASH(L) NO_MERGE(L) NO_SWAP_JOIN_INPUTS(L) */ A.CUST_NO
, A.ORD_DT
, B.LVL AS ORD_DTL_SN
, 0 AS ORD_AMT
, 'I' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, :V_TB_ITEM_CNT)), 9, '0') AS ITEM_NO
, L.DLVY_SN
, MAX(L.DLVY_SN) OVER (PARTITION BY A.CUST_NO, A.ORD_DT ORDER BY L.DLVY_SN
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_DLVY_SN
FROM
TUNER.TB_ORD A
INNER JOIN (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL <= 5) B ON (1=1)
LEFT
OUTER JOIN (
SELECT L.CUST_NO, L.ORD_DT, L.DLVY_SN
FROM
(
SELECT L.CUST_NO, L.ORD_DT, L.DLVY_SN
FROM TUNER.TB_DLVY L
) L
) L
ON
(L.CUST_NO = A.CUST_NO AND L.ORD_DT = A.ORD_DT AND L.DLVY_SN = B.LVL)
) A
INNER
JOIN TUNER.TB_ITEM B
ON (A.ITEM_NO = B.ITEM_NO)
;
COMMIT;
CREATE UNIQUE
INDEX TUNER.PK_TB_ORD_DTL ON TUNER.TB_ORD_DTL( CUST_NO,ORD_DT,ORD_DTL_SN )
TABLESPACE TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER
INDEX TUNER.PK_TB_ORD_DTL PARALLEL 1;
ALTER INDEX
TUNER.PK_TB_ORD_DTL LOGGING;
ALTER TABLE
TUNER.TB_ORD_DTL ADD CONSTRAINT PK_TB_ORD_DTL PRIMARY KEY (
CUST_NO,ORD_DT,ORD_DTL_SN );
CREATE INDEX
TUNER.IDX_TB_ORD_DTL_01 ON TUNER.TB_ORD_DTL(ITEM_NO) TABLESPACE TUNER_IDX1 LOCAL
PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_ORD_DTL_01 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_ORD_DTL_01 LOGGING;
CREATE INDEX
TUNER.IDX_TB_ORD_DTL_02 ON TUNER.TB_ORD_DTL(ORD_DTL_STS_CD) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_ORD_DTL_02 PARALLEL 1;
ALTER INDEX
TUNER.IDX_TB_ORD_DTL_02 LOGGING;
ALTER TABLE
TUNER.TB_ORD_DTL LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE
TUNER.TB_ORD NOLOGGING;
ALTER INDEX TUNER.PK_TB_ORD
NOLOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_01 NOLOGGING;
--MERGE문이라 REDO LOG가 쌓이는건 막을수 없음 ARCHIVE LOG가 과도하게 쌓이는 것을
방지하려면 NO ARCHIVE LOG 모드로 설정 후 돌려야함
MERGE /*+ PARALLEL(4)
ENABLE_PARALLEL_DML LEADING(A Z) USE_NL(Z) */ INTO TUNER.TB_ORD Z
USING (
SELECT /*+ LEADING(A B) USE_HASH(B) */ A.CUST_NO, A.ORD_DT, SUM(B.ORD_AMT) AS
ORD_TOT_AMT
FROM TUNER.TB_ORD A
, TUNER.TB_ORD_DTL B
WHERE 1=1
AND A.CUST_NO = B.CUST_NO
AND A.ORD_DT = B.ORD_DT
GROUP BY A.CUST_NO, A.ORD_DT
) A
ON (Z.CUST_NO = A.CUST_NO AND Z.ORD_DT =
A.ORD_DT)
WHEN MATCHED THEN UPDATE SET Z.ORD_TOT_AMT =
A.ORD_TOT_AMT
;
COMMIT;
ALTER TABLE TUNER.TB_ORD LOGGING;
ALTER INDEX TUNER.PK_TB_ORD LOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_01 LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST',
method_opt => 'for all indexed columns' , cascade => true, DEGREE=>
4);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER',
'TB_CUST_ADDR', method_opt => 'for all indexed columns' , cascade => true,
DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_DLVY', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_ITEM',
method_opt => 'for all indexed columns' , cascade => true, DEGREE=>
4);
EXEC DBMS_STATS.GATHER_TABLE_STATS('TUNER',
'TB_ORD', method_opt => 'for all indexed columns' , cascade => true,
DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_ORD_DTL', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
----------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ PARALLEL(4) */
A.*
FROM
(
SELECT
'TUNER.TB_CUST' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_CUST A
UNION ALL
SELECT
'TUNER.TB_CUST_ADDR' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_CUST_ADDR A
UNION ALL
SELECT /*+
INDEX_FFS(A) */ 'TUNER.TB_DLVY' AS TABLE_NAME, COUNT(*) AS CNT FROM
TUNER.TB_DLVY A
UNION ALL
SELECT
'TUNER.TB_ITEM' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_ITEM A
UNION ALL
SELECT
'TUNER.TB_ORD' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_ORD A
UNION ALL
SELECT /*+
INDEX_FFS(A) */ 'TUNER.TB_ORD_DTL' AS TABLE_NAME, COUNT(*) AS CNT FROM
TUNER.TB_ORD_DTL A
) A
;
----------------------------------------------------------------------------------------------------------------------------------------
--종료시간
COLUMN END_TIME NEW_VALUE
END_TIME
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD
HH24:MI:SS.FF') AS END_TIME FROM DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
--경과시간
SELECT
(TO_TIMESTAMP('&END_TIME', 'YYYY-MM-DD HH24:MI:SS.FF')
-
TO_TIMESTAMP('&START_TIME', 'YYYY-MM-DD HH24:MI:SS.FF')) AS ELAPSED_TIME
FROM DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
EOF
14-3. SQL 실습 환경 구축 스크립트 실행
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Thu Oct 2 17:35:22 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$
@/home/oracle/oracle_scripts/1.USER_TABLESPACE_SYS_SYSTEM_v1_0.sql
[ol8ora19rf1]<SYS@ORA19RF1>$
@/home/oracle/oracle_scripts/2.TABLE_INDEX_MERGE_STATISTICS_TUNER_v1_1.sql