1. Oracle Linux 8.10 Minimal Install
1-1. ISO 파일 다운로드
https://yum.oracle.com/ISOS/OracleLinux/OL8/u10/x86_64/OracleLinux-R8-U10-x86_64-dvd.iso
1-2. 설치
VM 생성 :
ol8ora23s1, 어댑터에 브리지 설정, 플로피 디스크 끄기, 사운드 끄기, 디스크 공간은 256기가 (미리 할당 X), 램 16기가, CPU
4개
언어 : English, English
DATE & TIME : Asia/Seoul
KEBOARD : 한국어
추가
LANGUAGE SUPPORT : 한국어 추가
SOFTWARE SELECTION : Minimal Install ->
Standard
INSTALLATION DESTINATION : Storage Configuration -> Custom
-> Done -> "+" -> (/boot, 500MB), (swap, 16384MB), (/, 나머지공간) 각각 Add
Mount Point -> Done -> Accecpt Changes
KDUMP : OFF
SECURITY POLICY :
OFF
NETWORK & HOSTNAME
Host name : ol8ora23s1
으로 하고 Apply
enp0s3 :
ON
IPv4 Settings :
Manual
주소 :
192.168.0.71
넷마스크 :
255.255.255.0
게이트웨이 :
192.168.0.1
DNS : 8.8.8.8,8.8.4.4
MTU
: 1500
IPv6
Settings : Ignore
Begin
Install
ROOT PASSWORD : 1234로 할것
USER CREATION : 관리자로 지정하고, admin/1234로
할것
설치가 완료되면 putty, moba xterm등의 툴로 192.168.0.71:22/root/1234 로 ssh 접속할 것
--ssh 접속 확인
┌──────────────────────────────────────────────────────────────────────┐
│
? MobaXterm Professional Edition v22.2
?
│
│
(SSH client, X server and network
tools)
│
│
│
│ ? SSH session to root@192.168.0.71
│
│ ? 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@ol8ora23s1
~]#
2. OS
설정
2-1. OS 정보 확인
[root@ol8ora23s1 ~]# cat
/etc/oracle-release
Oracle Linux Server release
8.10
[root@ol8ora23s1 ~]# uname
-a
Linux
ol8ora23s1 5.15.0-206.153.7.1.el8uek.x86_64 #2 SMP Wed May 22 20:49:34 PDT 2024
x86_64 x86_64 x86_64 GNU/Linux
--> 자동으로 부팅되는 커널을 바꿔줌 (Oracle Linux 8버전에서 asmlib 2.0 설치를 위해서는 레드헷 커널을 사용해야함)
[root@ol8ora23s1][/root]$ ls -l
/boot/vmlinuz-*
-rwxr-xr-x. 1 root root 13213776 Mar 29 10:45
/boot/vmlinuz-0-rescue-3e491e45630a46038bda8c79fd0cb721
-rwxr-xr-x. 1 root
root 10877104 May 23 2024 /boot/vmlinuz-4.18.0-553.el8_10.x86_64
-rwxr-xr-x. 1 root root 13213776 May
23 2024 /boot/vmlinuz-5.15.0-206.153.7.1.el8uek.x86_64
[root@ol8ora23s1][/root]$ grubby --set-default /boot/vmlinuz-4.18.0-553.el8_10.x86_64
The default is
/boot/loader/entries/3e491e45630a46038bda8c79fd0cb721-4.18.0-553.el8_10.x86_64.conf
with index 1 and kernel /boot/vmlinuz-4.18.0-553.el8_10.x86_64
[root@ol8ora23s1][/root]$ shutdown -Fr now
--> 즉시 재부팅
2-2. root user의 .bash_profile 설정
[root@ol8ora23s1 ~]# vi .bash_profile
[root@ol8ora23s1 ~]# cat
.bash_profile
#
.bash_profile
# Get the aliases and functions
if [
-f ~/.bashrc ]; then
.
~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export
PATH
set -o
vi
export PS1='[\u@\h][$PWD]$ '
stty erase
^H
export
ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/23ai/grid
export
DB_HOME=$ORACLE_BASE/product/23ai/db_1
export
BASE_PATH=/usr/sbin:$PATH
export
PATH=$GRID_HOME/bin:$BASE_PATH
alias csrt='crsctl stat
res -t'
[root@ol8ora23s1 ~]# . ./.bash_profile
2-3. 필수
패키지 설치
[root@ol8ora23s1][/root]$ yum -y install
net-tools
[root@ol8ora23s1][/root]$ ifconfig -a
enp0s3:
flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu
1500
inet 192.168.0.71
netmask 255.255.255.0 broadcast
192.168.0.255
inet6
fe80::a00:27ff:feb7:1931 prefixlen 64 scopeid
0x20<link>
ether
08:00:27:b7:19:31 txqueuelen 1000
(Ethernet)
RX packets 179581
bytes 270444477 (257.9 MiB)
RX
errors 0 dropped 0 overruns 0 frame
0
TX packets 12115 bytes
922006 (900.3 KiB)
TX errors
0 dropped 0 overruns 0 carrier 0 collisions
0
lo: flags=73<UP,LOOPBACK,RUNNING>
mtu 65536
inet 127.0.0.1
netmask 255.0.0.0
inet6 ::1
prefixlen 128 scopeid
0x10<host>
loop
txqueuelen 1000 (Local
Loopback)
RX packets 36
bytes 3060 (2.9 KiB)
RX errors
0 dropped 0 overruns 0 frame
0
TX packets 36 bytes 3060
(2.9 KiB)
TX errors 0
dropped 0 overruns 0 carrier 0 collisions
0
[root@ol8ora23s1][/root]$ yum -y install
wget
[root@ol8ora23s1][/root]$ wget
[root@ol8ora23s1][/root]$ yum -y
install lsof vim unzip
--rlwrap 설치
[root@ol8ora23s1][/root]$ dnf -y install epel-release
[root@ol8ora23s1][/root]$ dnf -y install rlwrap
[root@ol8ora23s1][/root]$ rlwrap
-version
rlwrap
0.46.1
[root@ol8ora23s1][/root]$ yum -y install
NetworkManager
[root@ol8ora23s1][/root]$ systemctl enable
NetworkManager
[root@ol8ora23s1][/root]$ systemctl status
NetworkManager
[root@ol8ora23s1][/root]$ nmcli general status
[root@ol8ora23s1][/root]$ nmcli device
show
2-4. /etc/hosts 수정
[root@ol8ora23s1][/root]$ vi
/etc/hosts
[root@ol8ora23s1][/root]$ cat
/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1
localhost localhost.localdomain localhost6
localhost6.localdomain6
192.168.0.71
ol8ora23s1
2-5.
selinux 설정
[root@ol8ora23s1][/root]$ vi
/etc/selinux/config
[root@ol8ora23s1][/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@ol8ora23s1][/root]$ setenforce
permissive
2-5. fstab 설정
[root@ol8ora23s1][/root]$ vi
/etc/fstab
[root@ol8ora23s1][/root]$ cat /etc/fstab
#
# /etc/fstab
# Created by
anaconda on Sat Mar 29 01:35:50 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=1dfe01fd-a49a-49b9-ba70-9f586ca8f878
/boot
xfs defaults 0
0
/dev/mapper/ol-swap
none
swap defaults 0
0
tmpfs
/dev/shm
tmpfs size=16g
00
--> 추가
[root@ol8ora23s1][/root]$ mount -o remount /dev/shm
mount: (hint) your fstab has been modified,
but systemd still uses
the old version;
use 'systemctl daemon-reload' to reload.
[root@ol8ora23s1][/root]$
systemctl daemon-reload
[root@ol8ora23s1][/root]$ mount -o remount
/dev/shm
[root@ol8ora23s1][/root]$ df
-h
Filesystem
Size Used Avail Use% Mounted
on
devtmpfs
7.9G 0 7.9G 0% /dev
tmpfs
16G 0 16G 0%
/dev/shm
tmpfs
7.9G 8.5M 7.9G 1%
/run
tmpfs
7.9G 0 7.9G 0%
/sys/fs/cgroup
/dev/mapper/ol-root 241G 4.9G
236G 3%
/
/dev/sda1
471M 253M 218M 54%
/boot
tmpfs
1.6G 0 1.6G 0% /run/user/0
2-6-1. 필수 패키지 검증 작업
[root@ol8ora23s1][/root]$ vi
check_pakage.sh
[root@ol8ora23s1][/root]$ cat check_pakage.sh
rpm -q bc
rpm -q binutils
rpm -q
elfutils-libelf
rpm -q gcc
rpm -q gcc-c++
rpm -q glibc
rpm -q
glibc-devel
rpm -q ksh
rpm -q libaio
rpm -q libaio-devel
rpm -q
libgcc
rpm -q libgfortran
rpm -q libibverbs
rpm -q libnsl
rpm -q
libnsl2
rpm -q libstdc++
rpm -q libstdc++-devel
rpm -q libxcb
rpm -q
libX11
rpm -q libXau
rpm -q libXi
rpm -q libXrender
rpm -q
libXtst
rpm -q make
rpm -q policycoreutils
rpm -q
policycoreutils-python-utils
rpm -q smartmontools
rpm -q
sysstat
[root@ol8ora23s1][/root]$ chmod 775
check_pakage.sh
[root@ol8ora23s1][/root]$ ./check_pakage.sh | grep "not
installed"
package binutils is not installed
package gcc is not
installed
package gcc-c++ is not installed
package glibc-devel is not
installed
package ksh is not installed
package libaio-devel is not
installed
package libgfortran is not installed
package libnsl is not
installed
package libstdc++-devel is not installed
package libXi is not
installed
package libXtst is not installed
package make is not
installed
package sysstat is not installed
[root@ol8ora23s1][/root]$ dnf -y install binutils gcc gcc-c++ glibc-devel ksh libaio-devel libgfortran libnsl libstdc++-devel libXi libXtst make sysstat
[root@ol8ora23s1][/root]$ ./check_pakage.sh | grep "not installed"
[root@ol8ora23s1][/root]$ yum install -y
compat-openssl10-1.0.2o-4.el8_6.x86_64
2-6. Oracle 23ai Pre Install 설치
[root@ol8ora23s1][/root]$ wget https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-23ai-1.0-4.el8.x86_64.rpm
[root@ol8ora23s1][/root]$ rpm -ivh
oracle-database-preinstall-23ai-1.0-4.el8.x86_64.rpm
error: Failed
dependencies:
nfs-utils >=
2.3.3 is needed by
oracle-database-preinstall-23ai-1.0-4.el8.x86_64
xorg-x11-utils is needed by
oracle-database-preinstall-23ai-1.0-4.el8.x86_64
xorg-x11-xauth
is needed by oracle-database-preinstall-23ai-1.0-4.el8.x86_64
--> 추가로 설치해야 하는 필수 패키지 목록을 확인
[root@ol8ora23s1][/root]$ dnf -y install nfs-utils xorg-x11-utils xorg-x11-xauth
--> 필수 패키지 설치
[root@ol8ora23s1][/root]$ rpm -ivh
oracle-database-preinstall-23ai-1.0-4.el8.x86_64.rpm
Verifying...
#################################
[100%]
Preparing...
################################# [100%]
Updating /
installing...
1:oracle-database-preinstall-23ai-1#################################
[100%]
-- preinstall 패키지가 무슨일을 했는지 확인하기!
[root@ol8ora23s1][/root]$ cat
/var/log/oracle-database-preinstall-23ai/results/orakernel.log
Adding group oinstall with gid
54321
Adding group dba with gid 54322
Adding group oper with gid
54323
Adding group backupdba with gid 54324
Adding group dgdba with gid
54325
Adding group kmdba with gid 54326
Adding group racdba with gid
54330
Adding user oracle with user id 54321, initial login group oinstall,
supplementary groups oinstall,dba,oper,backupdba,dgdba,kmdba,racdba and
home directory /home/oracle
Changing ownership of /home/oracle to
oracle:oinstall
Please set password for user - oracle
uid=54321(oracle)
gid=54321(oinstall)
groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
User
creation passed
Saving a copy of
the initial sysctl.conf
Verifying kernel
parameters as per Oracle recommendations...
Trying to
remove instances of - setting for fs.file-max is
Adding fs.file-max = 6815744
Trying
to remove instances of - setting for kernel.sem is
Adding kernel.sem = 250 32000 100 128
Trying to remove instances of - setting for
kernel.shmmni is
Adding kernel.shmmni = 4096
Trying to remove instances of - setting for
kernel.shmall is
Adding kernel.shmall = 1073741824
Trying to remove instances of - setting for
kernel.shmmax is
Adding kernel.shmmax = 4398046511104
Trying to remove instances of - setting for
kernel.panic_on_oops is
Adding kernel.panic_on_oops =
1
Trying to remove instances of - setting for
net.core.rmem_default is
Adding net.core.rmem_default =
262144
Trying to remove instances of - setting for
net.core.rmem_max is
Adding net.core.rmem_max =
4194304
Trying to remove instances of - setting
for net.core.wmem_default is
Adding
net.core.wmem_default = 262144
Trying to remove
instances of - setting for net.core.wmem_max is
Adding net.core.wmem_max = 1048576
Trying to remove instances of - setting for
net.ipv4.conf.all.rp_filter is
Adding
net.ipv4.conf.all.rp_filter = 2
Trying to remove
instances of - setting for net.ipv4.conf.default.rp_filter is
Adding net.ipv4.conf.default.rp_filter = 2
Trying to remove instances of - setting for
fs.aio-max-nr is
Adding fs.aio-max-nr = 1048576
Trying to remove instances of - setting for
net.ipv4.ip_local_port_range is
Adding
net.ipv4.ip_local_port_range = 9000 65535
Trying to
remove instances of - setting special parameters BEGIN
Trying to remove instances of - setting for
kernel.panic is
Adding kernel.panic = 10
Trying to remove instances of - setting special
parameters END
Setting kernel parameters as per oracle
recommendations...
Altered file /etc/sysctl.conf
Saved a copy of the current file in
/etc/sysctl.d/99-oracle-database-preinstall-23ai-sysctl.conf
Check /etc/sysctl.d for backups
Verification & setting of kernel parameters
passed
Setting user limits using /etc/security/limits.d/oracle-database-preinstall-23ai.conf
Verifying oracle
user OS limits as per Oracle recommendations...
Adding
oracle soft nofile 1024
Adding oracle hard
nofile 65536
Adding oracle soft nproc
16384
Adding oracle hard nproc 16384
Adding oracle soft stack 10240
Adding oracle hard stack 32768
Adding oracle hard memlock 134217728
Adding oracle soft memlock 134217728
Adding oracle soft data unlimited
Adding oracle hard data unlimited
Setting oracle user OS limits as per Oracle
recommendations...
Altered file
/etc/security/limits.d/oracle-database-preinstall-23ai.conf
Original file backed up at
/var/log/oracle-database-preinstall-23ai/backup/Jul-29-2025-19-31-16
Verification & setting of user limits
passed
Saving a copy of
/etc/default/grub file in /etc/default/grub-initial.orabackup
Saving a copy of /etc/default/grub in
/var/log/oracle-database-preinstall-23ai/backup/Jul-29-2025-19-31-16...
Verifying kernel boot parameters as per Oracle
recommendations...
INFO: Current Kernel configuration
index=0
kernel="/boot/vmlinuz-5.15.0-206.153.7.1.el8uek.x86_64"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet $tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-5.15.0-206.153.7.1.el8uek.x86_64.img
$tuned_initrd"
title="Oracle Linux Server 8
(5.15.0-206.153.7.1.el8uek.x86_64) "
id="6deace17f4ac4ec38e592f4d5e84b48b-5.15.0-206.153.7.1.el8uek.x86_64"
index=1
kernel="/boot/vmlinuz-4.18.0-553.el8_10.x86_64"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet $tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-4.18.0-553.el8_10.x86_64.img
$tuned_initrd"
title="Oracle Linux Server
(4.18.0-553.el8_10.x86_64) 8.10"
id="6deace17f4ac4ec38e592f4d5e84b48b-4.18.0-553.el8_10.x86_64"
index=2
kernel="/boot/vmlinuz-0-rescue-6deace17f4ac4ec38e592f4d5e84b48b"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet $tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-0-rescue-6deace17f4ac4ec38e592f4d5e84b48b.img
$tuned_initrd"
title="Oracle Linux Server 8
(0-rescue-6deace17f4ac4ec38e592f4d5e84b48b) "
id="6deace17f4ac4ec38e592f4d5e84b48b-0-rescue"
INFO: Executing grubby --update-kernel= ALLargs=numa=off
INFO: numa=off already present in
/etc/default/grub
Setting /sys/kernel/mm/transparent_hugepage/enabled to madvise
Setting /sys/kernel/mm/transparent_hugepage/defrag to madvise
INFO: Executing
grubby --update-kernel= ALLargs=transparent_hugepage=madvise
INFO: transparent_hugepage=madvise already present in
/etc/default/grub
INFO: grubby
--info=ALL
index=0
kernel="/boot/vmlinuz-5.15.0-206.153.7.1.el8uek.x86_64"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=madvise
$tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-5.15.0-206.153.7.1.el8uek.x86_64.img
$tuned_initrd"
title="Oracle Linux Server 8
(5.15.0-206.153.7.1.el8uek.x86_64) "
id="6deace17f4ac4ec38e592f4d5e84b48b-5.15.0-206.153.7.1.el8uek.x86_64"
index=1
kernel="/boot/vmlinuz-4.18.0-553.el8_10.x86_64"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=madvise
$tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-4.18.0-553.el8_10.x86_64.img
$tuned_initrd"
title="Oracle Linux Server
(4.18.0-553.el8_10.x86_64) 8.10"
id="6deace17f4ac4ec38e592f4d5e84b48b-4.18.0-553.el8_10.x86_64"
index=2
kernel="/boot/vmlinuz-0-rescue-6deace17f4ac4ec38e592f4d5e84b48b"
args="ro resume=/dev/mapper/ol-swap rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=madvise
$tuned_params"
root="/dev/mapper/ol-root"
initrd="/boot/initramfs-0-rescue-6deace17f4ac4ec38e592f4d5e84b48b.img
$tuned_initrd"
title="Oracle Linux Server 8
(0-rescue-6deace17f4ac4ec38e592f4d5e84b48b) "
id="6deace17f4ac4ec38e592f4d5e84b48b-0-rescue"
INFO: Boot parameters will be effected on next reboot
INFO: Altered file /etc/default/grub
INFO: Copy of the changed file is in -
/etc/default/grub-oracle-database-preinstall-23ai.orabackup
INFO: Copy of the original file is in -
/var/log/oracle-database-preinstall-23ai/backup/Jul-29-2025-19-31-16
Verification & setting of boot parameters
passed
Setting parameters
in /etc/sysconfig/network is not relevant for 23
Setting
/etc/sysconfig/network parameters passed
Taking a backup of old config files under
/var/log/oracle-database-preinstall-23ai/backup/Jul-29-2025-19-31-16
--Preinstall 패키지 없이 Preinstall 패키지가 하는일을 하기 (23ai기준) (preinstall 패키지를 사용하지 않는다면)
Oracle23ai Pre Install을 사용하지 않고 수동 설치 시작 (Preinstall 쓰지 말자!)
(1) Oracle User 생성
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54330 racdba
useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle
[root@rdb01d][/root]$ id oracle
uid=54321(oracle)
gid=54321(oinstall)
groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
(2) /etc/sysctl.conf 설정
[root@rdb01d][/etc]$ vi
/etc/sysctl.conf
[root@rdb01d][/etc]$ 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).
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
#kernel.shmall
= 1073741824
#kernel.shmall = 1310720 # for 10GB(50%)
Physical Memory
kernel.shmall = 2097152 # for 16GB(50%)
Physical Memory
#kernel.shmmax = 4398046511104
#kernel.shmmax = 5368709120 # for 10GB(50%) Physical
Memory
kernel.shmmax = 8589934592 # for 16GB(50%)
Physical Memory
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
#Added by KOLEE
kernel.pid_max = 10239
vm.min_free_kbytes = 524288
vm.swappiness=1
kernel.randomize_va_space = 0
kernel.sysrq = 1
[root@rdb01d][/etc]$
sysctl -p
fs.file-max = 6815744
kernel.sem =
250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 2097152 # for 16GB(50%) Physical Memory
kernel.shmmax = 8589934592 # for 16GB(50%) Physical
Memory
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) limits.d 설정
[root@rdb01d][/etc]$ vi /etc/security/limits.d/oracle-database-preinstall-23ai.conf
[root@rdb01d][/etc]$ cat /etc/security/limits.d/oracle-database-preinstall-23ai.conf
-->
23ai인 경우 vi /etc/security/limits.d/oracle-database-preinstall-23ai.conf
파일로함
oracle soft nofile
1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
#oracle hard
memlock 134217728
#oracle hard memlock
15099494 # For 16GB(90%) Physical Memory
oracle hard memlock
9437184 # For 10GB(90%) Physical Memory
#oracle soft
memlock 134217728
#oracle soft memlock
15099494 # For 16GB(90%) Physical Memory
oracle soft memlock 9437184 # For 10GB(90%) Physical Memory
(4) /etc/default/grub 설정
[root@rdb01d][/etc/default]$ cat
/proc/cmdline
BOOT_IMAGE=/vmlinuz-5.4.17-2102.201.3.el7uek.x86_64
root=/dev/mapper/ol-root ro rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet
LANG=en_US.UTF-8
[root@rdb01d][/etc/default]$ vi /etc/default/grub
[root@rdb01d][/etc/default]$ 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="rd.lvm.lv=ol/root rd.lvm.lv=ol/swap
rhgb quiet numa=off
transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"
[root@rdb01d][/etc/default]$ grub2-mkconfig -o
/boot/grub2/grub.cfg
Generating grub
configuration file ...
Found linux image:
/boot/vmlinuz-5.4.17-2102.201.3.el7uek.x86_64
Found
initrd image: /boot/initramfs-5.4.17-2102.201.3.el7uek.x86_64.img
Found linux image: /boot/vmlinuz-3.10.0-1160.el7.x86_64
Found initrd image:
/boot/initramfs-3.10.0-1160.el7.x86_64.img
Found linux
image: /boot/vmlinuz-0-rescue-48f4886082c55e4583f6e3a8feea311b
Found initrd image:
/boot/initramfs-0-rescue-48f4886082c55e4583f6e3a8feea311b.img
done
[root@rdb01d][/etc/default]$ grep -i numa
/boot/grub2/grub.cfg
linux16
/vmlinuz-5.4.17-2102.201.3.el7uek.x86_64 root=/dev/mapper/ol-root ro
rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off
transparent_hugepage=never
linux16
/vmlinuz-3.10.0-1160.el7.x86_64 root=/dev/mapper/ol-root ro rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never
linux16
/vmlinuz-0-rescue-48f4886082c55e4583f6e3a8feea311b root=/dev/mapper/ol-root ro
rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off
transparent_hugepage=never
[root@rdb01d][/etc/default]$ grep -i hugepage
/boot/grub2/grub.cfg
linux16
/vmlinuz-5.4.17-2102.201.3.el7uek.x86_64 root=/dev/mapper/ol-root ro
rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off
transparent_hugepage=never
linux16
/vmlinuz-3.10.0-1160.el7.x86_64 root=/dev/mapper/ol-root ro rd.lvm.lv=ol/root
rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never
linux16
/vmlinuz-0-rescue-48f4886082c55e4583f6e3a8feea311b root=/dev/mapper/ol-root ro
rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off
transparent_hugepage=never
[root@rdb01d][/etc/default]$ reboot
[root@rdb01d][/root]$ cat
/proc/cmdline
BOOT_IMAGE=/vmlinuz-5.4.17-2102.201.3.el7uek.x86_64
root=/dev/mapper/ol-root ro rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet
numa=off transparent_hugepage=never
[root@rdb01d][/root]$ cat
/sys/kernel/mm/transparent_hugepage/enabled
always madvise
[never]
(5) /etc/sysconfig/network 설정
[root@rdb01d][/root]$ vi
/etc/sysconfig/network
[root@rdb01d][/root]$ cat
/etc/sysconfig/network
# Created by anaconda
NOZEROCONF=yes
[root@rdb01d][/root]$ sudo
systemctl restart NetworkManager
[root@rdb01d][/root]$ cat
/etc/sysconfig/network | grep NOZEROCONF
NOZEROCONF=yes
Oracle23ai Pre Install을 사용하지 않고 수동 설치 종료 (Preinstall 쓰지 말자!)
2-7. sysctl.conf
설정 변경 (kernel.shmall, kernel.shmmax를 물리메모리의 50%로 변경)
[root@ol8ora23s1][/etc/security/limits.d]$ vi
/etc/sysctl.conf
[root@ol8ora23s1][/etc/security/limits.d]$ 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-23ai setting for fs.file-max is 6815744
fs.file-max = 6815744
#
oracle-database-preinstall-23ai setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128
#
oracle-database-preinstall-23ai setting for kernel.shmmni is 4096
kernel.shmmni = 4096
#
oracle-database-preinstall-23ai setting for kernel.shmall is 1073741824 on
x86_64
#kernel.shmall = 1073741824
kernel.shmall = 2097152
--> 16GB/2/4KB=2097152(4kb단위)
#
oracle-database-preinstall-23ai setting for kernel.shmmax is 4398046511104 on
x86_64
#kernel.shmmax = 4398046511104
kernel.shmmax = 8589934592
--> 16GB/2=8589934592 (바이트 단위)
#
oracle-database-preinstall-23ai setting for kernel.panic_on_oops is 1 per Orabug
19212317
kernel.panic_on_oops = 1
#
oracle-database-preinstall-23ai setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144
#
oracle-database-preinstall-23ai setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304
#
oracle-database-preinstall-23ai setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144
#
oracle-database-preinstall-23ai setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576
#
oracle-database-preinstall-23ai setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2
#
oracle-database-preinstall-23ai setting for net.ipv4.conf.default.rp_filter is
2
net.ipv4.conf.default.rp_filter =
2
#
oracle-database-preinstall-23ai setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576
#
oracle-database-preinstall-23ai setting for net.ipv4.ip_local_port_range is 9000
65500
net.ipv4.ip_local_port_range = 9000
65535
#
oracle-database-preinstall-23ai setting special parameters BEGIN
# oracle-database-preinstall-23ai setting for kernel.panic
is 10
kernel.panic = 10
# oracle-database-preinstall-23ai setting special parameters END
# Added by KOLEE
kernel.pid_max = 10239
vm.min_free_kbytes = 524288
vm.swappiness=1
kernel.randomize_va_space = 0
kernel.sysrq = 1
[root@ol8ora23s1][/etc/security/limits.d]$ 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 65535
kernel.panic = 10
kernel.pid_max =
10239
vm.min_free_kbytes = 524288
vm.swappiness = 1
kernel.randomize_va_space = 0
kernel.sysrq = 1
2-8. limits 설정 변경 (memlock을 물리메모리의 90%로 변경)
[root@ol8ora23s1][/etc/security/limits.d]$ vi
/etc/security/limits.d/oracle-database-preinstall-23ai.conf
[root@ol8ora23s1][/etc/security/limits.d]$ cat
/etc/security/limits.d/oracle-database-preinstall-23ai.conf
# oracle-database-preinstall-23ai setting for nofile soft
limit is 1024
oracle soft
nofile 1024
# oracle-database-preinstall-23ai setting for nofile hard
limit is 65536
oracle hard
nofile 65536
# oracle-database-preinstall-23ai setting for nproc soft
limit is 16384
# refer orabug15971421 for more info.
oracle soft nproc
16384
# oracle-database-preinstall-23ai setting for nproc hard
limit is 16384
oracle hard
nproc 16384
# oracle-database-preinstall-23ai setting for stack soft
limit is 10240KB
oracle soft
stack 10240
# oracle-database-preinstall-23ai setting for stack hard
limit is 32768KB
oracle hard
stack 32768
# oracle-database-preinstall-23ai 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 9437184 # 10GB
oracle hard
memlock 15099494 # 16GB
--> 물리메모리의 90%이며 kb단위
# oracle-database-preinstall-23ai 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 9437184 # 10GB
oracle soft
memlock 15099494 # 16GB
--> 물리메모리의 90%이며 kb단위
#
oracle-database-preinstall-23ai setting for data soft limit is 'unlimited'
oracle soft data
unlimited
#
oracle-database-preinstall-23ai setting for data hard limit is 'unlimited'
oracle hard data
unlimited
2-9. 그룹 및 유저 확인
[root@ol8ora23s1][/etc/security/limits.d]$ 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
[root@ol8ora23s1][/etc/security/limits.d]$ cat
/etc/passwd | grep oracle
oracle:x:54321:54321::/home/oracle:/bin/bash
2-10. 그룹 추가 및 권한 설정
-> grid 유저
생성
[root@ol8ora23s1][/etc/security/limits.d]$ groupadd
asmadmin
[root@ol8ora23s1][/etc/security/limits.d]$
groupadd asmdba
[root@ol8ora23s1][/etc/security/limits.d]$ groupadd
asmoper
[root@ol8ora23s1][/etc/security/limits.d]$
useradd -g oinstall -G dba,asmadmin,asmdba,asmoper,racdba grid
--> oracle 유저는
수정
[root@ol8ora23s1][/etc/security/limits.d]$ usermod -g
oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba,asmdba,asmadmin oracle
2-11. 불필요한 데몬 비활성화
[root@ol8ora23s1][/root]$ systemctl stop firewalld
[root@ol8ora23s1][/root]$ systemctl disable firewalld
Removed
/etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed
/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@ol8ora23s1][/root]$
systemctl stop bluetooth
Failed to stop
bluetooth.service: Unit bluetooth.service not loaded.
[root@ol8ora23s1][/root]$
systemctl stop avahi-daemon.socket
Failed to stop
avahi-daemon.socket: Unit avahi-daemon.socket not loaded.
[root@ol8ora23s1][/root]$
systemctl disable libvirtd
Failed to disable unit:
Unit file libvirtd.service does not exist.
[root@ol8ora23s1][/root]$
virsh net-destroy default
-bash: virsh: command not
found
2-12. grid, oracle
유저의 패스워드를 1234로 설정
[root@ol8ora23s1][/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@ol8ora23s1][/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.
2-13. 오라클 홈, 오라클 베이스, 그리드 디렉토리 생성
[root@ol8ora23s1][/root]$
mkdir -p /u01/app/23ai/grid
[root@ol8ora23s1][/root]$
mkdir -p /u01/app/oracle/product/23ai/db_1
[root@ol8ora23s1][/root]$ chown -R grid:oinstall /u01
[root@ol8ora23s1][/root]$ chown -R oracle:oinstall
/u01/app/oracle/product
[root@ol8ora23s1][/root]$ chmod
-R 775 /u01
2-14. grid의 limit
설정
[root@ol8ora23s1][/root]$
awk 'NF && $1 !~ /^#/'
/etc/security/limits.d/oracle-database-preinstall-23ai.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@ol8ora23s1][/root]$ vi /etc/security/limits.conf
--> 위 파일을 연 후 맨 아래에 grid 관련
설정을 추가
[root@ol8ora23s1][/root]$ grep grid
/etc/security/limits.conf
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
2-15. grid, oracle, root 의 bash_profile 및 bashrc 설정
[root@ol8ora23s1][/root]$ vi ~grid/.bash_profile
[root@ol8ora23s1][/root]$ cat ~grid/.bash_profile
# .bash_profile
# Get the aliases and
functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific
environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export TMP=/tmp
export
TMPDIR=$TMP
export EDITOR=vi
export ORACLE_HOSTNAME=ol8ora23s1
export ORACLE_UNQNAME=+ASM
export
ORACLE_UNQNAME_LOWER=+asm
export
ORACLE_BASE=/u01/app/oracle
export
GRID_HOME=/u01/app/23ai/grid
export
DB_HOME=$ORACLE_BASE/product/23ai/db_1
export
ORACLE_HOME=$GRID_HOME
export ORACLE_SID=+ASM
export ORACLE_SID_LOWER=+asm
export
ORACLE_USER_ORACLE_SID=ORA23S1
export
ORACLE_USER_ORACLE_SID_LOWER=ora23s1
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 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
^?
[root@ol8ora23s1][/root]$ vi ~oracle/.bash_profile
[root@ol8ora23s1][/root]$ cat
~oracle/.bash_profile
#
.bash_profile
# Get the aliases and
functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific
environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export TMP=/tmp
export
TMPDIR=$TMP
export EDITOR=vi
export ORACLE_HOSTNAME=ol8ora23s1
export ORACLE_UNQNAME=ORA23S1
export ORACLE_UNQNAME_LOWER=ora23s1
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/23ai/grid
export DB_HOME=$ORACLE_BASE/product/23ai/db_1
export ORACLE_HOME=$DB_HOME
export
ORACLE_DBNAME=ORA23S1
export
ORACLE_DBNAME_LOWER=ora23s1
export ORACLE_SID=ORA23S1
export ORACLE_SID_LOWER=ora23s1
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 ssp='rlwrap sqlplus
sys/oracle@ORA23S11 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 ^?
[root@ol8ora23s1][/root]$ vi ~root/.bash_profile
[root@ol8ora23s1][/root]$ cat ~root/.bash_profile
# .bash_profile
# Get the aliases and functions
if
[ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
set -o vi
export PS1='[\u@\h][$PWD]$ '
stty
erase ^H
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/23ai/grid
export DB_HOME=$ORACLE_BASE/product/23ai/db_1
export BASE_PATH=/usr/sbin:$PATH
export PATH=$GRID_HOME/bin:$BASE_PATH
alias csrt='crsctl stat res -t'
[root@ol8ora23s1][/root]$
vi ~oracle/.bashrc
[root@ol8ora23s1][/root]$ cat
~oracle/.bashrc
#
.bashrc
#
Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
#
User specific environment
if ! [[ "$PATH" =~
"$HOME/.local/bin:$HOME/bin:" ]]
then
PATH="$HOME/.local/bin:$HOME/bin:$PATH"
fi
export PATH
#
Uncomment the following line if you don't like systemctl's auto-paging
feature:
# export SYSTEMD_PAGER=
#
User specific aliases and functions
alias sqlplus='rlwrap
sqlplus'
alias rman='rlwrap rman'
alias adrci='rlwrap adrci'
alias
dgmgrl='rlwrap dgmgrl'
alias lsnrctl='rlwrap
lsnrctl'
[root@ol8ora23s1][/root]$
vi ~grid/.bashrc
[root@ol8ora23s1][/root]$ cat
~grid/.bashrc
#
.bashrc
# Source global
definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific
environment
if ! [[ "$PATH" =~
"$HOME/.local/bin:$HOME/bin:" ]]
then
PATH="$HOME/.local/bin:$HOME/bin:$PATH"
fi
export PATH
# Uncomment the following
line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases
and functions
alias sqlplus='rlwrap
sqlplus'
alias rman='rlwrap rman'
alias adrci='rlwrap adrci'
alias
asmcmd='rlwrap asmcmd -p'
alias lsnrctl='rlwrap
lsnrctl'
2-16.
transparent_hugepage 끄기
[root@ol8ora23s1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/enabled
always madvise
[never]
[root@ol8ora23s1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/defrag
always defer defer+madvise madvise [never]
[root@ol8ora23s1][/root]$
vi /etc/systemd/system/disable-thp.service
[root@ol8ora23s1][/root]$ cat
/etc/systemd/system/disable-thp.service
[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
[root@ol8ora23s1][/root]$ systemctl daemon-reload
[root@ol8ora23s1][/root]$ systemctl start disable-thp
[root@ol8ora23s1][/root]$ systemctl enable
disable-thp
Created symlink
from /etc/systemd/system/multi-user.target.wants/disable-thp.service to
/etc/systemd/system/disable-thp.service.
[root@ol8ora23s1][/root]$
systemctl status disable-thp
● disable-thp.service -
Disable Transparent Huge Pages (THP)
Loaded: loaded (/etc/systemd/system/disable-thp.service; enabled; vendor preset:
disabled)
Active: inactive (dead)
Sep 18 23:04:03 ttdb
systemd[1]: Started Disable Transparent Huge Pages (THP).
[root@ol8ora23s1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/enabled
always madvise
[never]
[root@ol8ora23s1][/root]$ cat
/sys/kernel/mm/transparent_hugepage/defrag
always defer
defer+madvise madvise [never]
[root@ol8ora23s1][/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="rd.lvm.lv=ol/root rd.lvm.lv=ol/swap
rhgb quiet numa=off transparent_hugepage=never"
GRUB_DISABLE_RECOVERY="true"
-->
transparent_hugepage=never가 아니라면 transparent_hugepage=never로 고친 후 아래의 명령을
실행
[root@ol8ora23s1][/root]$
grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub
configuration file ...
done
--> 실행 후
재부팅후 다시 확인해야함
--> 재부팅 후 확인
[root@ol8ora23s1][/root]$ grep
AnonHugePages /proc/meminfo
AnonHugePages:
0 kB
--> 0 kB여야함
2-17. RemoveIPC=no 확인
[root@ttdb][/root]$ cat /etc/systemd/logind.conf | grep
RemoveIPC
#RemoveIPC=no
RemoveIPC=no
--> 만약
해당 설정이 no가 아니라면 no로 바꾸고 아래의 명령 수행으로 적용해줌
systemctl restart
systemd-logind
systemctl status
systemd-logind
2-18. NOZEROCONF=yes 확인
[root@ol8ora23s1][/root]$ vi /etc/sysconfig/network
[root@ol8ora23s1][/root]$ cat /etc/sysconfig/network
# Created by anaconda
NOZEROCONF=yes
2-19. pam_limits.so 설정
[root@ol8ora23s1][/root]$ vi /etc/pam.d/login
[root@ol8ora23s1][/root]$ cat /etc/pam.d/login | grep
pam_limits.so
session required
pam_limits.so
2-20. tuned-adm
설정
[root@ol8ora23s1][/root]$ tuned-adm active
Current active profile: virtual-guest
[root@ol8ora23s1][/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@ol8ora23s1][/root]$ tuned-adm
profile throughput-performance
[root@ol8ora23s1][/root]$
tuned-adm active
Current active profile: throughput-performance
2-21. cio 옵션 확인 (cio 옵션이 있다면 제거할 것)
[root@ol8ora23s1][/root]$ cat
/etc/fstab
#
# /etc/fstab
# Created by anaconda
on Sat Mar 29 01:35:50 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=1dfe01fd-a49a-49b9-ba70-9f586ca8f878
/boot
xfs defaults 0
0
/dev/mapper/ol-swap
none
swap defaults 0 0
tmpfs
/dev/shm
tmpfs size=16g 00
[root@ol8ora23s1][/root]$ findmnt -o TARGET,OPTIONS | grep
cio
[root@ol8ora23s1][/root]$ mount | grep cio
만약 cio 옵션이 있다면,
/etc/fstab 에서 cio 옵션 설정 제거 후
다시 마운트 시키고
mount -o remount 마운트위치
리부트 시킴
2-22. MTU 사이즈를
4088으로 변경 (이 설정은 하지 않음)
제어판 -> 네트워크 및 공유 센터
-> 어댑터 설정 변경 -> 이더넷 -> 속성 -> 구성 -> 고급 -> Jumbo Frame ->
4088Bytes
[root@ol8ora23s1][/root]$ vi
/etc/sysconfig/network-scripts/ifcfg-enp0s3
[root@ol8ora23s1][/root]$ cat /etc/sysconfig/network-scripts/ifcfg-enp0s3
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=none
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=no
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=enp0s3
UUID=d0293055-57a3-43f6-8939-1b6b8ad8edb7
DEVICE=enp0s3
ONBOOT=yes
IPV6_PRIVACY=no
MTU=4088
IPADDR=10.0.2.13
PREFIX=24
GATEWAY=10.0.2.1
DNS1=8.8.8.8
[root@ol8ora23s1][/root]$
systemctl restart NetworkManager
[root@ol8ora23s1][/root]$ ip link show enp0s3
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 4088
qdisc pfifo_fast state UP mode DEFAULT group default qlen 1000
link/ether 08:00:27:8d:eb:d4 brd
ff:ff:ff:ff:ff:ff
[root@ol8ora23s1][/root]$ nmcli device disconnect enp0s3
--> VM에서 nmcli device connect enp0s3 로 다시 올림
[root@ol8ora23s1][/root]$ ip link show enp0s3
2: enp0s3:
<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 4088 qdisc pfifo_fast state UP mode
DEFAULT group default qlen 1000
link/ether 08:00:27:8d:eb:d4 brd ff:ff:ff:ff:ff:ff
--> mtu가
4088인 것을 확인함
--> 전원을 off후 다시 부팅 시킴
2-23. mask 가
0022인지 확인
[root@ol8ora23s1][/root]$ umask
0022
[root@ol8ora23s1][/root]$ su - grid
[+ASM:grid@ol8ora23s1][/home/grid]$ umask
0022
[+ASM:grid@ol8ora23s1][/home/grid]$ exit
logout
[root@ol8ora23s1][/root]$ su - oracle
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ umask
0022
2-24. java jdk 1.8
설치
[root@ol8ora23s1][/root]$
yum install -y java-1.8.0-openjdk-devel
[root@ol8ora23s1][/root]$
java -version
openjdk version "1.8.0_442"
OpenJDK Runtime Environment (build 1.8.0_442-b06)
OpenJDK 64-Bit Server VM (build 25.442-b06, mixed mode)
2-27. OSW 설치 (oracle os user에 설치할 것)
OSWatcher (Doc ID
301137.1) 문서 참조할 것
oswbb840.tar 파일 다운 로드 후 /home/oralce 디렉토리에 저장
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ tar xvf
oswbb840.tar
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cp
/home/oracle/oswbb/Exampleprivate.net /home/oracle/oswbb/private.net
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ vi
/home/oracle/oswbb/private.net
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cat /home/oracle/oswbb/private.net
######################################################################
# 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 <node1-priv>
#traceroute -r -F
<node2-priv>
######################################################################
#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
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd oswbb
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ nohup ./startOSWbb.sh 5 960 gzip
nohup: ignoring input and appending
output to ‘nohup.out’
--> 5초 간격으로 데이터를 수집하고 960시간(2주) 동안의 데이터를 보관한뒤 파일을 자동으로
압축 저장
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ ps -ef | grep -i osw
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd
/home/oracle/oswbb
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ java -jar oswbba.jar -i /home/oracle/oswbb/archive
--> OSW로 시각화 그래프를 볼 수 있음
OSW 자동 기동 (root os user로 접속해서 작업해야함)
[root@ol8ora23s1][/root]$ vi
/etc/systemd/system/oswbb.service
[root@ol8ora23s1][/root]$ cat
/etc/systemd/system/oswbb.service
[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
[root@ol8ora23s1][/root]$ cd
/home/oracle/oswbb
[root@ol8ora23s1][/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@ol8ora23s1][/root]$ systemctl status oswbb.service
[root@ol8ora23s1][/root]$
systemctl start oswbb.service
[root@ol8ora23s1][/root]$ systemctl enable oswbb.service
3. ASM 설정
3-1. 공유 스토리지 생성
Oracle VM -> 파일 ->
도구 -> 가상 미디어 관리자 -> 만들기 -> VDI -> 미리 전체 크기 할당
E:\vm\ora23s\11.ol8ora23s1\ol8ora23s1_DATA1_1.vdi ->
64GB
E:\vm\ora23s\11.ol8ora23s1\ol8ora23s1_FRA1_1.vdi -> 64GB
VM (ttdb) -> 설정 ->
저장소
컨트롤러:SATA에 아래와 같이 디스크를 추가 --> 순서에 주의
DATA1_1 (sdb)
FRA1_1 (sdc)
--> 추가 후 머신 시작 (OS 부팅)
[root@ol8ora23s1][/root]$
fdisk -l
Disk /dev/sda: 256 GiB, 274877906944 bytes, 536870912
sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk
identifier: 0x0a9f2de3
Device
Boot Start End
Sectors Size Id Type
/dev/sda1
* 2048 976895
974848 476M 83 Linux
/dev/sda2 976896
536870911 535894016 255.5G 8e Linux LVM
-- 추가한 /dev/sdb 이것은 DATA1_1 이 될 것임
Disk /dev/sdb: 64 GiB,
68719476736 bytes, 134217728 sectors
Units: sectors of 1
* 512 = 512 bytes
Sector size (logical/physical): 512
bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes
/ 512 bytes
-- 추가한 /dev/sdc 이것은 FRA1_1 이 될
것임
Disk /dev/sdc: 64 GiB,
68719476736 bytes, 134217728 sectors
Units: sectors of 1
* 512 = 512 bytes
Sector size (logical/physical): 512
bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes
/ 512 bytes
Disk /dev/mapper/ol-root:
240.3 GiB, 257987444736 bytes, 503881728 sectors
Units:
sectors of 1 * 512 = 512 bytes
Sector size
(logical/physical): 512 bytes / 512 bytes
I/O size
(minimum/optimal): 512 bytes / 512 bytes
Disk /dev/mapper/ol-swap: 15.3 GiB, 16387145728 bytes,
32006144 sectors
Units: sectors of 1 * 512 = 512
bytes
Sector size (logical/physical): 512 bytes / 512
bytes
I/O size (minimum/optimal): 512 bytes / 512
bytes
3-2-1. udev 설정 시
[root@ol8ora23s1][/root]$ /usr/lib/udev/scsi_id --whitelisted --replace-whitespace
--device=/dev/sdb
1ATA_VBOX_HARDDISK_VBdec47c31-08651e50
[root@ol8ora23s1][/root]$ /usr/lib/udev/scsi_id --whitelisted --replace-whitespace
--device=/dev/sdc
1ATA_VBOX_HARDDISK_VBa6a60dbf-4854b3ec
[root@ol8ora23s1][/root]$ vi
/etc/udev/rules.d/99-oracle-asmdevices.rules
[root@ol8ora23s1][/root]$ cat
/etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sdb",
ENV{ID_SERIAL}=="VBOX_HARDDISK_VBdec47c31-08651e50",
SYMLINK+="oracleasm/disks/DATA1_1", OWNER="grid", GROUP="asmadmin",
MODE="0660"
KERNEL=="sdc",
ENV{ID_SERIAL}=="VBOX_HARDDISK_VBa6a60dbf-4854b3ec",
SYMLINK+="oracleasm/disks/FRA1_1",
OWNER="grid", GROUP="asmadmin", MODE= "0660"
--> 부팅 시 자동으로 인식되도록 설정하는 것임
--> 여기서 중요!! ID_SERIAL 방식이 아닌 ID_PATH로 할수도 있음
udevadm info --query=all --name=/dev/sdb | grep ID_PATH | grep -v ID_PATH_TAG
--> ID_PATH 값을 조회 한후
-- ID_PATH값으로 설정
KERNEL=="sdb",
ENV{ID_PATH}=="pci-0000:00:0d.0-ata-2.0",
SYMLINK+="oracleasm/disks/DATA1_1", OWNER="grid", GROUP="asmadmin",
MODE="0660"
--> ID_PATH로 하는것이 Oracle VM환경에서 더욱 더 안정적임
--> 재부팅 후 다시 확인
[root@ol8ora23s1][/root]$ ls -l /dev/oracleasm/disks/*
lrwxrwxrwx. 1 root root 9 Aug
11 02:09 /dev/oracleasm/disks/DATA1 -> ../../sdb
lrwxrwxrwx. 1 root root 9 Aug 11 02:09
/dev/oracleasm/disks/FRA1 -> ../../sdc
[root@ol8ora23s1][/root]$ ls -l /dev/sd*
brw-rw----. 1 root
disk 8, 0 Aug 11 02:09 /dev/sda
brw-rw----. 1 root disk 8, 1
Aug 11 02:09 /dev/sda1
brw-rw----. 1 root
disk 8, 2 Aug 11 02:09 /dev/sda2
brw-rw----. 1 grid asmadmin 8, 16 Aug 11 02:09
/dev/sdb
brw-rw----. 1 grid asmadmin 8, 32 Aug 11 02:09
/dev/sdc
-- 재부팅 없이 즉시 적용
[root@ol8ora23s1][/root]$
udevadm control --reload-rules
[root@ol8ora23s1][/root]$ udevadm trigger
3-2-2. asmlib 설정 시
VM 설정에서 저장소 -> 컨트롤러 : IDE에 OracleLinux-R8-U10-x86_64-dvd.iso 파일 연결
-- cdrom 확인
[root@ol8ora23s1][/root]$
ls -l /dev/cdrom
lrwxrwxrwx. 1 root root 3
Mar 29 14:33 /dev/cdrom -> sr0
-- 마운트 시킬 디렉토리 생성
[root@ol8ora23s1][/root]$
mkdir -pv /run/media
mkdir: created directory
'/run/media'
-- 마운트
작업
[root@ol8ora23s1][/root]$
mount /dev/cdrom /run/media
mount: /run/media:
WARNING: device write-protected, mounted read-only.
-- 마운트 완료
확인
[root@ttdb][/root]$ ls /run/media/
addons EFI EULA GPL images
isolinux LiveOS Packages RELEASE-NOTES-U9-en
RELEASE-NOTES-U9-en.html repodata RPM-GPG-KEY
RPM-GPG-KEY-oracle TRANS.TBL
[root@ol8ora23s1][/run/media]$ cd /run/media/BaseOS/Packages/
[root@ol8ora23s1][/run/media/BaseOS/Packages]$ rpm -ivh kmod-redhat-oracleasm-2.0.8-18.0.1.el8.x86_64.rpm
언마운트 작업방법은 아래와 같음
umount /run/media
3-2-2-1. asmlib 설정 시
-- /root위치에 oracleasmlib-2.0.17-1.el8.x86_64.rpm 파일 업로드
[root@ol8ora23s1][/root]$
rpm -ivh oracleasmlib-2.0.17-1.el8.x86_64.rpm
[root@ol8ora23s1][/root]$
wget https://yum.oracle.com/repo/OracleLinux/OL8/addons/x86_64/getPackage/oracleasm-support-2.1.12-1.el8.x86_64.rpm
[root@ol8ora23s1][/root]$
rpm -ivh oracleasm-support-2.1.12-1.el8.x86_64.rpm
[root@ol8ora23s1][/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
Default group to own
the driver interface []: asmadmin
Start Oracle ASM
library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks
on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration:
done
[root@ol8ora23s1][/root]$ oracleasm init
[root@ol8ora23s1][/root]$ oracleasm
status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted:
yes
[root@ol8ora23s1][/root]$ oracleasm configure
ORACLEASM_ENABLED=true
ORACLEASM_UID=grid
ORACLEASM_GID=asmadmin
ORACLEASM_SCANBOOT=true
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
ORACLEASM_SCAN_DIRECTORIES=""
ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"
3-2-2-1. asmlib 설정 시
[root@ol8ora23s1][/root]$
ls -l /dev/sd*
brw-rw----. 1 root disk
8, 0 Jul 29 20:21 /dev/sda
brw-rw----. 1 root disk
8, 1 Jul 29 20:21 /dev/sda1
brw-rw----. 1 root
disk 8, 2 Jul 29 20:21 /dev/sda2
brw-rw----. 1
root disk 8, 16 Jul 29 20:21 /dev/sdb
brw-rw----. 1 root
disk 8, 32 Jul 29 20:21 /dev/sdc
[root@ol8ora23s1][/root]$ fdisk /dev/sdb
Welcome to fdisk
(util-linux 2.32.1).
Changes will remain in memory only,
until you decide to write them.
Be careful before using
the write command.
Device does not contain a
recognized partition table.
Created a new DOS disklabel
with disk identifier 0xc59cc2fb.
Command (m for help):
n
Partition type
p primary (0 primary, 0 extended, 4 free)
e extended (container for logical
partitions)
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-100663295, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-100663295,
default 100663295):
Created a new partition 1 of type 'Linux' and of size 64 GiB.
Command (m for help):
w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.
[root@ol8ora23s1][/root]$ fdisk /dev/sdc
Welcome to fdisk
(util-linux 2.32.1).
Changes will remain in memory only,
until you decide to write them.
Be careful before using
the write command.
Device does not
contain a recognized partition table.
Created a new DOS
disklabel with disk identifier 0x871942cd.
Command (m for help): n
Partition type
p primary (0 primary, 0 extended, 4 free)
e extended (container for logical
partitions)
Select (default p): p
Partition number (1-4,
default 1):
First sector (2048-134217727, default
2048):
Last sector, +sectors or +size{K,M,G,T,P}
(2048-134217727, default 134217727):
Created a new partition 1 of type 'Linux' and of size 64 GiB.
Command (m for help): w
The partition table has
been altered.
Calling ioctl() to re-read partition
table.
Syncing disks.
[root@ol8ora23s1][/root]$ oracleasm createdisk DATA1_1 /dev/sdb1
[root@ol8ora23s1][/root]$ oracleasm createdisk FRA1_1
/dev/sdc1
--만약 초기화 한다면 (초기화 할일 없으면 돌리지 말것)
[root@ol8ora23s1][/root]$ dd if=/dev/zero of=/dev/sdb bs=1M count=100
[root@ol8ora23s1][/root]$ dd if=/dev/zero of=/dev/sdc bs=1M count=100
[root@ol8ora23s1][/root]$ oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Setting up iofilter map for ASM disks: done
Scanning system for ASM disks...
[root@ol8ora23s1][/root]$ oracleasm listdisks
DATA1_1
FRA1_1
[root@ol8ora23s1][/root]$
chown -R grid:asmadmin /dev/oracleasm
4. GRID Install (23ai)
4-1. grid 설치 준비
--> 아래의 사이트에서 Oracle 23ai를 다운로드 할수 있음
--> 아래의 사이트에서 Oracle 23ai를 다운로드 할수 있음
--> 여기서 23ai의 base release인 23.5 버전을 다운 받을 수 있음
--> 이번 작업은 23.7 골드이미지로 설치함
-------------------------------------------------------------------
--23.7
골드이미지
--/home/grid 위치에 grid23702501.zip 파일 업로드
-------------------------------------------------------------------
[+ASM:grid@ol8ora23s1][/home/grid]$ cd ~
[+ASM:grid@ol8ora23s1][/home/grid]$ echo
$GRID_HOME
/u01/app/23ai/grid
[+ASM:grid@ol8ora23s1][/home/grid]$ mkdir -pv
$GRID_HOME
--> 이미 존재한다면 만들어지지 않을 것임
[+ASM:grid@ol8ora23s1][/home/grid]$ ls -l
$GRID_HOME
total
0
[+ASM:grid@ol8ora23s1][/home/grid]$ unzip grid23702501.zip -d $GRID_HOME
[root@ol8ora23s1][/root]$ cd
$GRID_HOME/cv/rpm
[root@ol8ora23s1][/u01/app/23ai/grid/cv/rpm]$ ll
total 24
-rw-r--r--. 1 grid oinstall 24528 Oct 8 16:31
cvuqdisk-1.0.10-1.rpm
[root@ol8ora23s1][/u01/app/23ai/grid/cv/rpm]$ rpm
-ivh 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%]
4-2. grid 설치 작업
--> RU 입히는 작업은 2025-03-29 현재 19c버전 그대로 둠, 나중에 정식 버전 나오고 RU입히게 되면 정리할 것 - 시작
How To Download
And Install The Latest OPatch(6880880) Version (Doc ID 274526.1)
--> 여기서 최신의 opatch 다운로드
[root@ttdb][/root]$ mkdir -p /tmp/patch
--> /tmp/patch 위치에 최신 opatch 업로드
[root@ttdb][/root]$ chmod 777 -R /tmp/patch
[root@ttdb][/root]$ cd /tmp/patch
[root@ttdb][/tmp/patch]$ ll
total 28752
-rw-r--r--. 1 root root 29439665 Sep 19 22:00
p6880880_190000_Linux-x86-64.zip
[+ASM:grid@ttdb][/home/grid]$
cd $GRID_HOME
[+ASM:grid@ttdb][/u01/app/19c/grid]$
clear
[+ASM:grid@ttdb][/u01/app/19c/grid]$ mv
/u01/app/19c/grid/OPatch /u01/app/19c/grid/OPatch.bak.20240919
[root@tdb01t][/u01/app/19c/grid]$ unzip
/tmp/patch/p6880880_190000_Linux-x86-64.zip -d $GRID_HOME
[+ASM:grid@ttdb][/u01/app/19c/grid]$ opatch version -oh
$GRID_HOME
OPatch Version: 12.2.0.1.43
OPatch succeeded.
Assistant: Download Reference
for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches,
Patchsets and Base Releases (Doc ID 2118136.2)
--> 여기서 최신 19c RU 다운로드
--> /tmp/patch 위치에 최신 19c RU 파일 업로드
[root@ttdb][/tmp/patch]$ chmod
777 /tmp/patch/p36582629_190000_Linux-x86-64.zip
[root@ttdb][/tmp/patch]$ ls -l
total 3393752
-rwxrwxrwx. 1 root root 3445758071 Sep 19 22:08
p36582629_190000_Linux-x86-64.zip
-rw-r--r--. 1 root
root 29439665 Sep 19 22:00
p6880880_230000_LINUX.zip
--> "p36582629_190000_Linux-x86-64.zip" 파일 존재 확인
-- /tmp/patch로
가서 실행할것
[root@ttdb][/tmp/patch]$ cd /tmp/patch
[root@ttdb][/tmp/patch]$ unzip
p36582629_190000_Linux-x86-64.zip
[root@ttdb][/tmp/patch]$ chmod 777 -R /tmp/patch/36582629
[+ASM:grid@ttdb][/u01/app/19c/grid]$ cd ~
[+ASM:grid@ttdb][/home/grid]$ cd
$GRID_HOME
[+ASM:grid@ttdb][/u01/app/19c/grid]$ ./gridSetup.sh
-applyRU /tmp/patch/36582629
--> RU 입히는 작업은 2025-03-29 현재 19c버전 그대로 둠, 나중에 정식 버전 나오고 RU입히게 되면 정리할 것 - 종료
----------------------------------------------------------------------------------------------------------------
--Nonexa에서 grid 설치 시 아래와 같이 스크립트 한줄 추가
[+ASM:grid@ol8ora23s1][/home/grid]$ cp
$GRID_HOME/crs/install/oraasm.pm
$GRID_HOME/crs/install/oraasm.pm.bak
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid]$ vi $GRID_HOME/crs/install/oraasm.pm
if ($CFG->params('ASM_DISCOVERY_STRING')) {
push @runasmca,
'-diskString', "\"$ASM_DISCOVERY_STRING\"";
push @runasmca, '-param',
"\"_exadata_feature_on=true\""; -->
추가
라인
}
----------------------------------------------------------------------------------------------------------------
--export CV_ASSUME_DISTID=OL7 --> 이거 할필요 없음
--ui로 설치 시작 (non-Exadata환경에서 exadata 전용 23.7 버전 설치 시 반드시 grid를 ui로 설치할것!!!!)
[+ASM:grid@ol8ora23s1][/home/grid]$
$GRID_HOME/gridSetup.sh
Preparing the home to
patch...
Configure Oracle Grid
Infrastructure for a Standalone Server(Oracle Restart) 선택 후 Next
Configure an Oracle Standalone Cluster 선택 후 Next
Disk Group name : DATA1
Allocation Unit Size : 4MB
Change Discovery Path : /dev/oracleasm/disks
설정 후 Next
Use Same Passwords For
these Accounts -> oracle, oracle -> Next
EM 체크 해제
후 Next
순서대로 asmadmin, asmdba, asmoper로 지정 후
Next
Oracle base : /u01/app/oracle로
한 후 Next
Inventory Directory : /u01/app/oraInventory 로 한
후 Next
Automatically run configuration scritps 체크 ->
Next
Save Response File : /home/grid/grid.rsp
만약 silent 모드로 설치한다면 - 시작----------------------------------------------------
--> /home/grid/grid.rsp 파일의 내용
[+ASM:grid@ol8ora23s1][/home/grid]$ awk 'NF && $1
!~ /^#/' /home/grid/grid.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v23.0.0
INVENTORY_LOCATION=/u01/app/oraInventory
installOption=HA_CONFIG
ORACLE_BASE=/u01/app/oracle
clusterUsage=RAC
zeroDowntimeGIPatching=false
skipDriverUpdate=false
OSDBA=asmdba
OSOPER=asmoper
OSASM=asmadmin
scanType=LOCAL_SCAN
scanClientDataFile=
scanName=
scanPort=
configureAsExtendedCluster=false
clusterName=
configureGNS=false
configureDHCPAssignedVIPs=false
gnsSubDomain=
gnsVIPAddress=
sites=
clusterNodes=
networkInterfaceList=
storageOption=FLEX_ASM_STORAGE
votingFilesLocations=
ocrLocations=
clientDataFile=
useIPMI=false
bmcBinpath=
bmcUsername=
bmcPassword=
sysasmPassword=
diskGroupName=DATA1
redundancy=EXTERNAL
auSize=4
failureGroups=
disksWithFailureGroupNames=/dev/oracleasm/disks/DATA1_1,
diskList=/dev/oracleasm/disks/DATA1_1
quorumFailureGroupNames=
diskString=/dev/oracleasm/disks
asmsnmpPassword=
configureAFD=false
ignoreDownNodes=false
configureBackupDG=false
backupDGName=RECO
backupDGRedundancy=NORMAL
backupDGAUSize=1
backupDGFailureGroups=
backupDGDisksWithFailureGroupNames=
backupDGDiskList=
backupDGQuorumFailureGroups=
managementOption=NONE
omsHost=
omsPort=0
emAdminUser=
emAdminPassword=
executeRootScript=false
configMethod=
sudoPath=/usr/local/bin/sudo
sudoUserName=grid
batchInfo=
nodesToDelete=
enableAutoFixup=false
[+ASM:grid@ol8ora23s1][/home/grid]$ awk -F= 'NF &&
$1 !~ /^#/ && $2!=""' /home/grid/grid.rsp >
/home/grid/grid_new.rsp
[+ASM:grid@ol8ora23s1][/home/grid]$ cat
/home/grid/grid_new.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v23.0.0
INVENTORY_LOCATION=/u01/app/oraInventory
installOption=HA_CONFIG
ORACLE_BASE=/u01/app/oracle
clusterUsage=RAC
zeroDowntimeGIPatching=false
skipDriverUpdate=false
OSDBA=asmdba
OSOPER=asmoper
OSASM=asmadmin
scanType=LOCAL_SCAN
configureAsExtendedCluster=false
configureGNS=false
configureDHCPAssignedVIPs=false
storageOption=FLEX_ASM_STORAGE
useIPMI=false
diskGroupName=DATA1
redundancy=EXTERNAL
auSize=4
disksWithFailureGroupNames=/dev/oracleasm/disks/DATA1_1,
diskList=/dev/oracleasm/disks/DATA1_1
diskString=/dev/oracleasm/disks
configureAFD=false
ignoreDownNodes=false
configureBackupDG=false
backupDGName=RECO
backupDGRedundancy=NORMAL
backupDGAUSize=1
managementOption=NONE
omsPort=0
executeRootScript=false
sudoPath=/usr/local/bin/sudo
sudoUserName=grid
enableAutoFixup=false
sysasmPassword=oracle
asmsnmpPassword=oracle
[+ASM:grid@ol8ora23s1][/home/grid]$ cd $GRID_HOME
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid]$ ./gridSetup.sh -silent -ignorePrereqFailure -responseFile /home/grid/grid_new.rsp -waitForCompletion
Launching Oracle Grid Infrastructure Setup Wizard...
[WARNING] [INS-30011] The SYS
password entered does not conform to the Oracle recommended standards.
CAUSE: Oracle recommends that the password
entered should be at least 8 characters in length, contain at least 1 uppercase
character, 1 lower case character and 1 digit [0-9].
ACTION: Provide a password that conforms to
the Oracle recommended standards.
[WARNING] [INS-30011]
The ASMSNMP password entered does not conform to the Oracle recommended
standards.
CAUSE: Oracle recommends that
the password entered should be at least 8 characters in length, contain at least
1 uppercase character, 1 lower case character and 1 digit [0-9].
ACTION: Provide a password that conforms to
the Oracle recommended standards.
[WARNING] [INS-32047]
The location (/u01/app/oraInventory) specified for the central inventory is not
empty.
ACTION: It is recommended to provide
an empty location for the inventory.
The response file
for this session can be found at:
/u01/app/23ai/grid/install/response/grid_2025-07-29_08-52-04PM.rsp
You can
find the log of this install session at:
/tmp/GridSetupActions2025-07-29_08-52-04PM/gridSetupActions2025-07-29_08-52-04PM.log
--> 여기 위치에 로그 남음
As a root user, run the
following script(s):
1.
/u01/app/oraInventory/orainstRoot.sh
2.
/u01/app/23ai/grid/root.sh
Run
/u01/app/23ai/grid/root.sh on the following nodes:
[ol8ora23s1]
Successfully Setup
Software.
As install user, run the following command to
complete the configuration.
/u01/app/23ai/grid/gridSetup.sh -executeConfigTools -responseFile
/home/grid/grid_new.rsp [-silent]
Moved the install session logs to:
/u01/app/oraInventory/logs/GridSetupActions2025-07-29_08-52-04PM
--root os user로 돌리라고 하는 스크립트를 돌려줌
[root@ol8ora23s1][/root]$
/u01/app/oraInventory/orainstRoot.sh
Changing permissions of
/u01/app/oraInventory.
Adding read,write permissions for
group.
Removing read,write,execute permissions for
world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@ol8ora23s1][/root]$
/u01/app/23ai/grid/root.sh
Check
/u01/app/23ai/grid/install/root_ol8ora23s1_2025-07-29_20-54-21-235901045.log for
the output of root script
[root@ol8ora23s1][/root]$ tail -f
/u01/app/23ai/grid/install/root_ol8ora23s1_2025-07-29_20-54-21-235901045.log
Executing command '/u01/app/23ai/grid/perl/bin/perl
-I/u01/app/23ai/grid/perl/lib -I/u01/app/23ai/grid/crs/install
/u01/app/23ai/grid/crs/install/roothas.pl '
Using
configuration parameter file: /u01/app/23ai/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/crsdata/ol8ora23s1/crsconfig/roothas_2025-07-29_08-54-21PM.log
2025/07/29 20:54:34 CLSRSC-363: User ignored prerequisites
during installation
Redirecting to /bin/systemctl
restart rsyslog.service
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
2025/07/29
20:54:48 CLSRSC-330: Adding Clusterware entries to file
'oracle-ohasd.service'
ol8ora23s1 2025/07/29
20:55:15
/u01/app/oracle/crsdata/ol8ora23s1/olr/backup_20250729_205515.olr
2303155306
2025/07/29 20:55:16 CLSRSC-327: Successfully
configured Oracle Restart for a standalone server
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid]$
/u01/app/23ai/grid/gridSetup.sh -executeConfigTools -responseFile
/home/grid/grid_new.rsp -silent
[root@ol8ora23s1][/u01/app/oraInventory/logs/GridSetupActions2025-07-29_08-56-31PM]$
tail -f gridSetupActions2025-07-29_08-56-31PM.log
-- 이건 추후 applyRU 할때 테스트 해볼것 시작
-nApplyOneOffs - apply multiple patches from the given
location to the Oracle home. --> 이 옵션으로 바뀐거 같은데?
./gridSetup.sh -silent -ignorePrereqFailure -responseFile
/home/grid/grid_new.rsp -waitForCompletion -applyRU
/tmp/patch/36582629
--> applyRU 작업이 끝나면 root 패스워드 한번 쳐주면 설치 시작됨
-- 이건 추후 applyRU 할때 테스트 해볼것 종료
만약 silent 모드로 설치한다면 - 종료---------------------------------------------------
--ui로 설치 중 아래와 같에 에러 발생(Nonexa환경에서 발생함) oraasm.pm 파일을 수정했는데도 해당 에러 발생
INFO: [Jul 30, 2025 5:56:48 PM] Command [/bin/sh, -c,
/u01/app/23ai/grid/bin/asmca -silent -oui_internal -configureASM -diskString
'/dev/oracleasm/disks' -diskGroupName DATA1 -diskList
'/dev/oracleasm/disks/DATA1_1' -redundancy EXTERNAL -au_size 4 ]
INFO: [Jul 30, 2025 5:56:48 PM] ...
CommandExecutionJob: starting read loop.
INFO:
[Jul 30, 2025 5:57:02 PM] SYS_PASSWORD_PROMPT
INFO: [Jul 30, 2025 5:57:02 PM]
SYS_PASSWORD_PROMPT
INFO: [Jul 30, 2025 5:57:02
PM] Processing: SYS_PASSWORD_PROMPT for secret argument
INFO: [Jul 30, 2025 5:57:02 PM]
ASMSNMP_PASSWORD_PROMPT
INFO: [Jul 30, 2025
5:57:02 PM] ASMSNMP_PASSWORD_PROMPT
INFO: [Jul 30,
2025 5:57:02 PM] Processing: ASMSNMP_PASSWORD_PROMPT for secret argument
INFO: [Jul 30, 2025 5:57:27 PM]
INFO: [Jul 30, 2025 5:57:27 PM]
INFO: [Jul 30, 2025 5:57:27 PM] Configuring ASM
failed.
INFO: [Jul 30, 2025 5:57:27 PM]
Configuring ASM failed.
INFO: [Jul 30, 2025
5:57:27 PM] Configuring HA resource failed.
INFO:
[Jul 30, 2025 5:57:27 PM] Configuring HA resource failed.
INFO: [Jul 30, 2025 5:57:27 PM] PRCR-1079 : Failed to
start resource ora.asm
INFO: [Jul 30, 2025 5:57:27
PM] PRCR-1079 : Failed to start resource ora.asm
INFO: [Jul 30, 2025 5:57:27 PM] CRS-5017: The
resource action "ora.asm start" encountered the following error:
INFO: [Jul 30, 2025 5:57:27 PM] CRS-5017: The
resource action "ora.asm start" encountered the following error:
INFO: [Jul
30, 2025 5:57:27 PM] ORA-27350: This version of the Oracle Database software
cannot be run on this platform
INFO: [Jul 30, 2025
5:57:27 PM] ORA-27350: This version of the Oracle Database software cannot be
run on this platform
INFO: [Jul 30, 2025
5:57:27 PM] Help: https://docs************com/error-help/db/ora-27350/
INFO: [Jul 30, 2025 5:57:27 PM] Help: https://docs************com/error-help/db/ora-27350/
INFO: [Jul 30, 2025 5:57:27 PM] . For details refer
to "(:CLSN00107:)" in
"/u01/app************diag/crs/ol8ora23s1/crs/trace/ohasd_oraagent_grid.trc".
INFO: [Jul 30, 2025 5:57:27 PM] . For details refer
to "(:CLSN00107:)" in
"/u01/app************diag/crs/ol8ora23s1/crs/trace/ohasd_oraagent_grid.trc".
--> 위와 같이 에러가 발생함
--> 에러가 발생한 상태에서 grid 설치 ui를 종료하지 말고 아래와 같이 수동으로 asmca를 해줌 (새로운 ssh 세션 하나 열어서)
[+ASM:grid@ol8ora23s1][/home/grid]$ /u01/app/23ai/grid/bin/asmca -silent -oui_internal -configureASM -sysAsmPassword oracle -asmsnmpPassword oracle -param _exadata_feature_on=true -diskString '/dev/oracleasm/disks' -diskGroupName DATA1 -diskList '/dev/oracleasm/disks/DATA1_1' -redundancy EXTERNAL -au_size 4
ASM has been created and
started successfully.
--> 위의 작업이 성공하면 grid ui에서 asmca 작업을 skip하고 다음 작업으로 넘어감 그럼 후속 cvu 작업까지 완료한 후 grid 설치가 성공하게 됨
[+ASM:grid@ol8ora23s1][/home/grid]$ csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE
ol8ora23s1
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol8ora23s1
STABLE
ora.asm
ONLINE ONLINE
ol8ora23s1
Started,STABLE
ora.ons
OFFLINE OFFLINE
ol8ora23s1
STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE
ONLINE
ol8ora23s1
STABLE
ora.diskmon
1 OFFLINE
OFFLINE
STABLE
ora.evmd
1 ONLINE
ONLINE
ol8ora23s1
STABLE
--------------------------------------------------------------------------------
[+ASM:grid@ol8ora23s1][/home/grid]$ crsctl query has
releaseversion
Oracle High Availability
Services release version on the local node is [23.0.0.0.0]
[+ASM:grid@ol8ora23s1][/home/grid]$ crsctl query has
releasepatch
Oracle Clusterware
release patch level is [2303155306] and the complete list of patches [37366180
37369888 37369896 37369900 37369904 ] have been applied on the local node. The
release patch string is [23.7.0.25.01].
[+ASM:grid@ol8ora23s1][/home/grid]$ crsctl query has
softwareversion
Oracle High Availability
Services version on the local node is [23.0.0.0.0]
[+ASM:grid@ol8ora23s1][/home/grid]$ crsctl query has
softwarepatch
Oracle Clusterware patch
level on node ol8ora23s1 is [2303155306].
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid]$ opatch
lspatches -oh $GRID_HOME
37369888;MICRONAUT
RELEASE UPDATE 23.7.0.25.01 (37369888) Gold Image
37369904;RHP RELEASE UPDATE 23.7.0.25.01 (37369904) Gold
Image
37369896;ACFS RELEASE UPDATE 23.7.0.25.01
(37369896) Gold Image
37369900;OCW RELEASE UPDATE
23.7.0.25.01 (37369900) Gold Image
37366180;Database
Release Update : 23.7.0.25.01 (37366180) Gold Image
OPatch
succeeded.
-- 이제 ASM DISK 생성 작업할 차례임
-- grid 설치가 모두 완료되면 아래 절차에
따라서 ASM 디스크를 구성해야함
4-3-1. asmca로 FRA 생성 (UDEV인 경우)
[+ASM:grid@ol79o12cr2sa][/home/grid]$ asmca
ASM -> Disk Groups ->
Create
Disk Group name : FRA1
Redundancy : External
/dev/sdc
(64GB) 선택 후 OK
4-3-2. asmca로 FRA 생성 (asmlib인 경우)
[+ASM:grid@ttdb][/home/grid]$ asmca
ASM -> Disk Groups
-> Create
Disk Group name : FRA1
Redundancy : External
/dev/oracleasm/disks/FRA1_1 (64GB)
선택 후 OK
[+ASM:grid@ttdb][/home/grid]$
asmcmd lsdg
State
Type Rebal Sector Logical_Sector
Block AU Total_MB Free_MB
Req_mir_free_MB Usable_file_MB Offline_disks
Voting_files Name
MOUNTED EXTERN
N
512
512 4096 4194304
65532
65428
0
65428
0
N DATA1/
MOUNTED EXTERN
N
512
512 4096 4194304
65532
65436
0
65436
0
N FRA1/
[+ASM:grid@ttdb][/home/grid]$
csrt | grep -i dg
ora.DATA1.dg
ora.FRA1.dg
--FRA1 생성을 수동으로 하자!!!! asmca ui 방식 쓰지 말자!!!! - 시작
sqlplus "/as sysasm"
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/FRA1_1
0 0
1 0
DATA1_0000
CACHED
/dev/oracleasm/disks/DATA1_1
65532
65428
create diskgroup FRA1 external redundancy disk '/dev/oracleasm/disks/FRA1_1' ATTRIBUTE 'au_size'='4M', 'compatible.asm'='23.0';
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
---------- ---------- ----------
DATA1 DATA1_0000 MOUNTED
FRA1 FRA1_0000
MOUNTED
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
DATA1
23.0.0.0.0 19.0.0.0.0
2
FRA1
23.0.0.0.0 19.0.0.0.0
--> 만약 DATA1의 compatible.asm 값이 다르다면 맞춰주기
SQL> alter diskgroup DATA1 SET ATTRIBUTE
'compatible.asm' = '23.0';
--FRA1 생성을 수동으로 하자!!!! asmca ui 방식 쓰지 말자!!!! - 종료
4-4. ASM 인스턴스
sqlplus로 붙을 때 glogin.sql 설정
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid]$ cd
$GRID_HOME/sqlplus/admin
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/sqlplus/admin]$
vi glogin.sql
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/sqlplus/admin]$
cat 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
#COLUMN CON_NAME NEW_VALUE _CON_NAME
#SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
SELECT SYS_CONTEXT('USERENV',
'HOST') AS HOSTNAME FROM DUAL;
#SELECT
SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
SET SQLPROMPT
'[&_DATE][&_HOSTNAME]<&_CONNECT_IDENTIFIER@&_USER.> '
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
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/sqlplus/admin]$
alias sa
alias sa='rlwrap sqlplus "/as sysasm"'
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/sqlplus/admin]$ sa
SQL*Plus: Release
23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Jul 30 20:23:27
2025
Version 23.7.0.25.01
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0
- for Oracle Cloud and Engineered Systems
Version
23.7.0.25.01
[2025-07-30:20:23:27][ol8ora23s1]<+ASM@SYS>
4-5. ASM 인스턴스 필수 설정
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS>
col GROUP_KFENV for 99999
col
NAME_KFENV for a20
col VALUE_KFENV for a10
SELECT A.GROUP_KFENV
, A.NAME_KFENV
, A.VALUE_KFENV
FROM X$KFENV A
WHERE A.NAME_KFENV LIKE '%compact%'
;
GROUP_KFENV
NAME_KFENV
VALUE_KFEN
----------- --------------------
----------
1
_rebalance_compact TRUE
2
_rebalance_compact TRUE
2 rows selected.
Elapsed: 00:00:00.01
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS>
col name for a15
SELECT
GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------
---------------
1 DATA1
2 FRA1
2 rows selected.
Elapsed: 00:00:00.04
Diskgroup altered.
Elapsed:
00:00:00.01
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS> ALTER DISKGROUP FRA1 SET ATTRIBUTE '_REBALANCE_COMPACT'='FALSE';
Diskgroup altered.
Elapsed:
00:00:00.01
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS> ALTER SYSTEM SET "_enable_shared_pool_durations"=false scope= spfile;
System altered.
Elapsed:
00:00:00.05
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS> ALTER SYSTEM SET "audit_sys_operations"=false scope= spfile;
System altered.
Elapsed:
00:00:00.05
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS> ALTER SYSTEM SET "MEMORY_MAX_TARGET"=4096M SCOPE= SPFILE;
System altered.
Elapsed:
00:00:00.04
[2025-03-29:23:34:39][ol8ora23s1]<+ASM@SYS> ALTER SYSTEM SET "MEMORY_TARGET"=1536M SCOPE= SPFILE;
System altered.
Elapsed: 00:00:00.03
5. 오라클 엔진
설치
5-1. 오라클 엔진 설치 준비
--grid os user
[+ASM:grid@ol8ora23s1][/u01/app/oracle]$ chmod 775 /u01/app/oracle
[+ASM:grid@ol8ora23s1][/u01/app/oracle]$ chmod 775 /u01/app/oraInventory
[+ASM:grid@ol8ora23s1][/u01/app/oracle]$ chmod 775
/u01/app/oracle/admin
[+ASM:grid@ol8ora23s1][/u01/app/oracle]$ chmod 775
/u01/app/oracle/audit
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd
~
해당 위치에 db23702501.zip 파일 업로드
(23.7 골드이미지임)
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ mkdir -pv
$ORACLE_HOME
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ unzip db23702501.zip -d $ORACLE_HOME
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd
$ORACLE_HOME
--> 이건 나중에 RU 입힐때 해볼것
- 시작------------------------------------------------------------------
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1]$
mv OPatch OPatch.bak.20240919
[TTDB:oracle@tdb01t][/u01/app/oracle/product/19c/db_1]$
unzip /tmp/patch/p6880880_190000_Linux-x86-64.zip
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1]$
opatch version -oh $ORACLE_HOME
OPatch Version:
12.2.0.1.43
OPatch
succeeded.
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1]$
ls -l /tmp/patch
total 3396172
drwxrwxrwx. 8 root root
4096 Jul 13 23:20 36582629
-rwxrwxrwx. 1 root root
3445758071 Sep 19 22:08 p36582629_190000_Linux-x86-64.zip
-rw-r--r--. 1 root root 29439665 Sep 19 22:00
p6880880_230000_LINUX.zip
-rw-rw-r--. 1 root
root 2470333 Jul 16 20:34 PatchSearch.xml
--> 이건 나중에 RU 입힐때 해볼것 - 종료------------------------------------------------------------------
5-2. 오라클 엔진 설치
--> 이건 나중에 RU 입힐때 해볼것
- 시작
[PTDB:oracle@ttdb][/u01/app/oracle/product/19c/db_1]$ ./runInstaller -applyRU /tmp/patch/36582629/
--> 이건 나중에 RU 입힐때 해볼것 - 종료
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$ ./runInstaller
Set Up Software Only ->
Next
Single Instance database installation
-> Next
Enterprise Edition -> Next
Oracle base : /u01/app/oracle
dba, oper, backupdba, dgdba, kmdba, racdba -> Next
Automatically run Configuration scritps -> Root 패스워드
-> 1234 -> Next
Save Respons File : /home/oracle/db.rsp
-- db.rsp
파일 내용
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ awk 'NF
&& $1 !~ /^#/' /home/oracle/db.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v23.0.0
installOption=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
installEdition=EE
OSDBA=dba
OSOPER=oper
OSBACKUPDBA=backupdba
OSDGDBA=dgdba
OSKMDBA=kmdba
OSRACDBA=racdba
executeRootScript=
configMethod=
sudoPath=
sudoUserName=
clusterNodes=
dbType=GENERAL_PURPOSE
gdbName=
dbSID=
pdbName=
charSet=
enableAutoMemoryManagement=false
memoryLimit=
allSchemaPassword=
sysPassword=
systemPassword=
dbsnmpPassword=
pdbadminPassword=
managementOption=
omsHost=
omsPort=0
emAdminUser=
emAdminPassword=
enableRecovery=false
storageType=
dataLocation=
recoveryLocation=
diskGroup=
asmsnmpPassword=
Silent 모드로 설치한다면 - 시작-------------------------------------------------------------------------------------------------------------------------------------------------------------
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
awk -F= 'NF && $1 !~ /^#/ && $2!=""' /home/oracle/db.rsp >
/home/oracle/db_new.rsp
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
cat /home/oracle/db_new.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v23.0.0
installOption=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
installEdition=EE
OSDBA=dba
OSOPER=oper
OSBACKUPDBA=backupdba
OSDGDBA=dgdba
OSKMDBA=kmdba
OSRACDBA=racdba
dbType=GENERAL_PURPOSE
enableAutoMemoryManagement=false
omsPort=0
enableRecovery=false
cd $ORACLE_HOME
./runInstaller -silent -ignorePrereqFailure -responseFile /home/oracle/db_new.rsp -executePrereqs
--> 이걸로 일단 검사해봄
Launching Oracle Database Setup Wizard...
Prerequisite checks ran successfully.
--실제 오라클 엔진 설치 시작
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
./runInstaller -silent -ignorePrereqFailure -responseFile
/home/oracle/db_new.rsp -waitForCompletion
Launching Oracle Database Setup Wizard...
The
response file for this session can be found at:
/u01/app/oracle/product/23ai/db_1/install/response/db_2025-07-30_08-45-04PM.rsp
You
can find the log of this install session at:
/u01/app/oraInventory/logs/InstallActions2025-07-30_08-45-04PM/installActions2025-07-30_08-45-04PM.log
As a
root user, run the following script(s):
1.
/u01/app/oracle/product/23ai/db_1/root.sh
Run
/u01/app/oracle/product/23ai/db_1/root.sh on the following nodes:
[ol8ora23s1]
Successfully Setup
Software.
-- 로그 모니터링
[root@ol8ora23s1][/root]$
tail -f
/u01/app/oraInventory/logs/InstallActions2025-07-30_08-45-04PM/installActions2025-07-30_08-45-04PM.log
[root@ol8ora23s1][/root]$
/u01/app/oracle/product/23ai/db_1/root.sh
Check /u01/app/oracle/product/23ai/db_1/install/root_ol8ora23s1_2025-07-30_20-47-10-121739818.log
for the output of root script
-- 로그 확인
[root@ol8ora23s1][/root]$ cat /u01/app/oracle/product/23ai/db_1/install/root_ol8ora23s1_2025-07-30_20-47-10-121739818.log
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=
/u01/app/oracle/product/23ai/db_1
Copying
dbhome to /usr/local/bin ...
Copying oraenv
to /usr/local/bin ...
Copying coraenv to
/usr/local/bin ...
Entries will be added to the /etc/oratab file as needed
by
Database Configuration Assistant when a database is
created
Finished running generic part of root script.
Now product-specific root actions will be performed.
--> 이건 나중에 RU 입힐때 해볼것
-
시작-----------------------------------------------------------------------------------------------------------------------------------------------------
[TTDB:oracle@tdb01t][/u01/app/oracle/product/19c/db_1]$ ./runInstaller -silent -ignorePrereqFailure -responseFile /home/oracle/db_new.rsp -waitForCompletion -applyRU /tmp/patch/37257886/
--> 이건 나중에 RU 입힐때 해볼것 - 종료-----------------------------------------------------------------------------------------------------------------------------------------------------
Silent 모드로 설치한다면 - 종료-------------------------------------------------------------------------------------------------------------------------------------------------------------
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
opatch lspatches -oh $ORACLE_HOME
37369900;OCW RELEASE
UPDATE 23.7.0.25.01 (37369900) Gold Image
37366180;Database Release Update : 23.7.0.25.01 (37366180)
Gold Image
OPatch
succeeded.
6. 오라클 DB
설치
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
ls -al $GRID_HOME/bin | egrep "extjob$|jssu$|oracle$|oradism"
-rwsr-s--x. 1 grid oinstall 503565904 Jul 30 17:55
oracle
-rwsr-x---. 1 root oinstall
1138016 Jan 16 2025 oradism
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
ls -al $ORACLE_HOME/bin | egrep "extjob$|jssu$|oracle$|oradism"
-rwsr-x---. 1 root oinstall
1922736 Jul 30 20:45 extjob
-rwsr-x---. 1
root oinstall 1914136 Jan 16 2025 jssu
-rwsr-s--x. 1 oracle asmdba 522673184 Jul
30 20:46 oracle
-rwsr-x---. 1 root
oinstall 1138016 Jan 16 2025 oradism
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$ dbca
Create a database
Advanced Configuration
Database type : Oracle
Single Instance Database
Oracle Single Instance database
-> General Purpose or Transaction Processing
Global
database name : ORA23S1, SID : ORA23S1
create as
Container database -> check , Use Local Undo Tablespace for PDBs
체크
Create a Container database with one or more PDBS 선택, pdb 1개, PDB name : ORA23S11
ASM
선택(+DATA1/{DB_UNIQUE_NAME}) 및 OMF 선택
Multiplex
redo logs and controlfiles : +DATA1, +FRA1
Specify Fast Recovery Area : 체크 (+FRA1, 32767 MB)
Enable Archive 체크 :
%t_%s_%r.ARC, +FRA1/ARCH
grid에 있는 리스너 사용 체크
Oracle Database Vault 구성 체크 해제
Oracle Label Security 구성 체크 해제
Memory 설정
Use Automatic Shared
Memory Managent 선택
물리 메모리의 40%를 SGA와 PGA할당함
물리 메모리의 40%는 16384*0.4 = 6553MB
SGA
Size : 4915MB (6553MB * 0.75)
PGA Size : 1638MB
(6553MB * 0.25)
Sizing 설정
Processes : 1432 (oracle ulimit의 max user processes설정보다
작아야함. 2047*0.7=1432)
Chracter sets 설정
Choose from the list of character sets 선택 : AL32UTF8
National character set : AL16UTF16
Default Language : American
Default
territory : Korea
접속모드 : 전용 서버 모드
EM 체크 해제
계정 비빌번호 설정 -> oracle로
설정
Generate database creation scripts 체크
({ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/scripts)
--파라미터 설정 부분 (설정한 후 spfile 에 체크할 것)
memory_target=0
optimizer_adaptive_reporting_only = true
optimizer_adaptive_plans=false
optimizer_dynamic_sampling=0
parallel_min_servers=0
parallel_threads_per_cpu=2
use_large_pages=true
pga_aggregate_limit=0
Customize Storage Locations 클릭
Control Files
Maximum
Datafiles : 1024
Save Response File ->
/home/oracle/dbca.rsp
Finish하면 설치 시작!
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
awk 'NF && $1 !~ /^#/' /home/oracle/dbca.rsp
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v23.0.0
gdbName=ORA23S1
sid=ORA23S1
databaseConfigType=SI
RACOneNodeServiceName=
sehaServiceName=
policyManaged=false
managementPolicy=AUTOMATIC
createServerPool=false
serverPoolName=
cardinality=
force=false
pqPoolName=
pqCardinality=
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=ORA23S11
useLocalUndoForPDBs=true
pdbAdminPassword=
nodelist=
sehaNodeList=
templateName=/u01/app/oracle/product/23ai/db_1/assistants/dbca/templates/General_Purpose.dbc
sysPassword=
systemPassword=
serviceUserPassword=
emConfiguration=
runCVUChecks=FALSE
dbsnmpPassword=
omsHost=
omsPort=0
emUser=
emPassword=
dvConfiguration=false
dvUserName=
dvUserPassword=
dvAccountManagerName=
dvAccountManagerPassword=
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=+DATA1/{DB_UNIQUE_NAME}/
recoveryAreaDestination=+FRA1
recoveryAreaSize=32767MB
configureWithOID=
pdbOptions=OMS:true,IMEDIA:false,JSERVER:true,SPATIAL:true,CWMLITE:true,ORACLE_TEXT:true,SAMPLE_SCHEMA:false,DV:true
dbOptions=OMS:true,IMEDIA:false,JSERVER:true,SPATIAL:true,CWMLITE:true,ORACLE_TEXT:true,SAMPLE_SCHEMA:false,DV:true
storageType=ASM
diskGroupName=+DATA1/{DB_UNIQUE_NAME}/
asmsnmpPassword=
recoveryGroupName=+FRA1
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
dirServiceUserName=
dirServicePassword=
walletPassword=
listeners=LISTENER
skipListenerRegistration=false
variablesFile=
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/23ai/db_1,DB_UNIQUE_NAME=ORA23S1,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=ORA23S1,ORACLE_HOME=/u01/app/oracle/product/23ai/db_1,SID=ORA23S1
initParams=undo_tablespace=UNDOTBS1,enable_pluggable_database=true,optimizer_adaptive_reporting_only=TRUE,sga_target=4915MB,parallel_min_servers=0,db_block_size=8192BYTES,optimizer_dynamic_sampling=0,log_archive_dest_1='LOCATION=+FRA1',nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP)
(SERVICE=ORA23S1XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest=+DATA1/{DB_UNIQUE_NAME}/,db_create_online_log_dest_2=+FRA1,db_create_online_log_dest_1=+DATA1,processes=1432,pga_aggregate_target=1638MB,nls_territory=AMERICA,parallel_threads_per_cpu=2,local_listener=LISTENER_ORA23S1,db_recovery_file_dest_size=32767MB,optimizer_adaptive_plans=FALSE,open_cursors=300,log_archive_format=%t_%s_%r.ARC,compatible=23.6.0,db_name=ORA23S1,db_recovery_file_dest=+FRA1
enableArchive=true
useOMF=true
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0
Silent 모드로 설치 한다면 - 시작-------------------------------------------------------------------------------------------------------------------------
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
awk -F= 'NF && $1 !~ /^#/ && $2!=""' /home/oracle/dbca.rsp >
/home/oracle/dbca_new.rsp
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
vi /home/oracle/dbca_new.rsp
--> 파라미터 부분에 _exadata_feature_on=true 추가
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
cat /home/oracle/dbca_new.rsp
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v23.0.0
gdbName=ORA23S1
sid=ORA23S1
databaseConfigType=SI
policyManaged=false
managementPolicy=AUTOMATIC
createServerPool=false
force=false
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=ORA23S11
useLocalUndoForPDBs=true
templateName=/u01/app/oracle/product/23ai/db_1/assistants/dbca/templates/General_Purpose.dbc
systemPassword=
runCVUChecks=FALSE
omsPort=0
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=+DATA1/{DB_UNIQUE_NAME}/
recoveryAreaDestination=+FRA1
recoveryAreaSize=32767MB
pdbOptions=OMS:true,IMEDIA:false,JSERVER:true,SPATIAL:true,CWMLITE:true,ORACLE_TEXT:true,SAMPLE_SCHEMA:false,DV:true
dbOptions=OMS:true,IMEDIA:false,JSERVER:true,SPATIAL:true,CWMLITE:true,ORACLE_TEXT:true,SAMPLE_SCHEMA:false,DV:true
storageType=ASM
diskGroupName=+DATA1/{DB_UNIQUE_NAME}/
recoveryGroupName=+FRA1
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/app/oracle/product/23ai/db_1,DB_UNIQUE_NAME=ORA23S1,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=ORA23S1,ORACLE_HOME=/u01/app/oracle/product/23ai/db_1,SID=ORA23S1
initParams=undo_tablespace=UNDOTBS1,enable_pluggable_database=true,optimizer_adaptive_reporting_only=TRUE,sga_target=4915MB,parallel_min_servers=0,db_block_size=8192BYTES,optimizer_dynamic_sampling=0,log_archive_dest_1='LOCATION=+FRA1',nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP)
(SERVICE=ORA23S1XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest=+DATA1/{DB_UNIQUE_NAME}/,db_create_online_log_dest_2=+FRA1,db_create_online_log_dest_1=+DATA1,processes=1432,pga_aggregate_target=1638MB,nls_territory=AMERICA,parallel_threads_per_cpu=2,local_listener=LISTENER_ORA23S1,db_recovery_file_dest_size=32767MB,optimizer_adaptive_plans=FALSE,open_cursors=300,log_archive_format=%t_%s_%r.ARC,compatible=23.6.0,db_name=ORA23S1,db_recovery_file_dest=+FRA1,_exadata_feature_on=true
enableArchive=true
useOMF=true
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0
dbca -silent -ignorePrereqFailure -createDatabase -responseFile /home/oracle/dbca_new.rsp
Enter SYS user password: oracle 입력
Enter SYSTEM user password: oracle 입력
Enter PDBADMIN User
Password: oracle 입력
Silent 모드로 설치 한다면 - 종료 -------------------------------------------------------------------------------------------------------------------------------
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
csrt
--------------------------------------------------------------------------------
Name
Target State
Server
State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA1.dg
ONLINE ONLINE
ol8ora23s1
STABLE
ora.FRA1.dg
ONLINE ONLINE
ol8ora23s1
STABLE
ora.LISTENER.lsnr
ONLINE ONLINE
ol8ora23s1
STABLE
ora.asm
ONLINE ONLINE
ol8ora23s1
Started,STABLE
ora.ons
OFFLINE OFFLINE
ol8ora23s1
STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE
ONLINE
ol8ora23s1
STABLE
ora.diskmon
1 OFFLINE
OFFLINE
STABLE
ora.evmd
1 ONLINE
ONLINE
ol8ora23s1
STABLE
ora.ora23s1.db
1 ONLINE
ONLINE
ol8ora23s1
Open,HOME=/u01/app/o
racle/product/23ai/d
b_1,STABLE
ora.ora23s1.ora23s11.pdb
1 ONLINE
ONLINE
ol8ora23s1
READ WRITE,STABLE
--------------------------------------------------------------------------------
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
alias ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1]$
ss
SQL*Plus: Release
23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Jul 30 21:19:02
2025
Version 23.7.0.25.01
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai
Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered
Systems
Version 23.7.0.25.01
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA23S11
READ WRITE NO
--클라이언트의 tnsnames.ora 추가
ORA23S1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.71)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ora23s1)
)
)
ORA23S11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.71)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA23S11)
)
)
--서버에서 tnsnames.ora에 추가
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1/network/admin]$
vi tnsnames.ora
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1/network/admin]$
cat tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/23ai/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration
tools.
ORA23S1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol8ora23s1)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA23S1)
)
)
LISTENER_ORA23S1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol8ora23s1)(PORT
= 1521))
ORA23S11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
ol8ora23s1)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= ORA23S11)
)
)
[2025-07-31:22:52:23][ol8ora23s1]<ORA23S1@SYS> show pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA23S11
READ WRITE NO
[2025-07-31:22:52:23][ol8ora23s1]<ORA23S1@SYS> alter pluggable database ora23s11 save
state;
7. 최신 패치 적용 (12cr2에서 작업했던 내역임 23ai는 추후 정리할것)
7-1. OPATCH 파일 다운로드
How To Download And
Install The Latest OPatch(6880880) Version (Doc ID 274526.1)
Select a Release : OPatch 12.2.0.1.0
Platform : Linux x86-64
--> p6880880_122010_Linux-x86-64.zip 파일 다운 로드
[root@ol79o12cr2sa][/tmp]$ mkdir -p /tmp/patch
[root@ol79o12cr2sa][/tmp]$ chmod 777 -R /tmp/patch
--> p6880880_122010_Linux-x86-64.zip 파일을 /tmp/patch 위치에
업로드
[root@ol79o12cr2sa][/tmp]$ cd /tmp/patch
[root@ol79o12cr2sa][/tmp/patch]$ ls -l /tmp/patch
total 130408
-rw-r--r--. 1
root root 133535622 Aug 11 09:52 p6880880_122010_Linux-x86-64.zip
7-2. OPATCH 적용
[+ASM:grid@ol79o12cr2sa][/home/grid]$ cd
$GRID_HOME
[+ASM:grid@ol79o12cr2sa][/u01/app/12c/grid]$ mv OPatch
OPatch.bak.20240811
[+ASM:grid@ol79o12cr2sa][/u01/app/12c/grid]$ unzip
/tmp/patch/p6880880_122010_Linux-x86-64.zip
[+ASM:grid@ol79o12cr2sa][/u01/app/12c/grid]$ opatch version
-oh $GRID_HOME
OPatch Version: 12.2.0.1.42
OPatch succeeded.
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ cd
$ORACLE_HOME
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
mv OPatch OPatch.bak.20240811
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
unzip /tmp/patch/p6880880_122010_Linux-x86-64.zip
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
opatch version -oh $ORACLE_HOME
OPatch Version:
12.2.0.1.42
OPatch succeeded.
7-3. 패치 다운로드
Assistant: Download
Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle
Patches, Patchsets and Base Releases (Doc ID 2118136.2)
--> 여기서 최신 12cR2 RU 다운로드
Oracle Database
Release Updates (RUs) -> 12.2.0.1 -> GI Update : 33583921 -> Linux
x86-64
--> p33583921_122010_Linux-x86-64.zip 파일
다운로드
--> /tmp/patch 위치에 최신 12cR2 RU 업로드 (p33583921_122010_Linux-x86-64.zip)
--> 해당 패치의 readme 참고할 것 (어떤것이 grid전용인지, oracle전용인지, 둘다용인지)
[root@ol79o12cr2sa][/tmp/patch]$ ll
total 3276072
-rw-r--r--. 1 root
root 2393137641 Aug 11 10:01 p33583921_122010_Linux-x86-64.zip
-rw-r--r--. 1 root root 133535622 Aug 11 09:52
p6880880_122010_Linux-x86-64.zip
[root@ol79o12cr2sa][/tmp/patch]$ chmod 777
p33583921_122010_Linux-x86-64.zip
[root@ol79o12cr2sa][/tmp/patch]$ unzip
p33583921_122010_Linux-x86-64.zip
[root@ol79o12cr2sa][/tmp/patch]$ ll
total 3276076
drwxr-x---. 8
root root 4096 Jan 15 2022 33583921
-rwxrwxrwx. 1 root root 2393137641 Aug 11 10:01
p33583921_122010_Linux-x86-64.zip
-rw-r--r--. 1 root
root 133535622 Aug 11 09:52 p6880880_122010_Linux-x86-64.zip
[root@ol79o12cr2sa][/tmp/patch]$ chmod 777 -R /tmp/patch/33583921
7-4. grid 패치 적용 전 패치 충돌 확인
$GRID_HOME/OPatch/opatch
prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/26839277
$GRID_HOME/OPatch/opatch
prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33116894
$GRID_HOME/OPatch/opatch
prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33610989
$GRID_HOME/OPatch/opatch
prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33678030
--> 지금부터 위의 명령을 실행 것임 (grid os user)
[+ASM:grid@ol79o12cr2sa][/home/grid]$
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/26839277
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/12c/grid
Central Inventory : /u01/app/oraInventory
from :
/u01/app/12c/grid/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/12c/grid/cfgtoollogs/opatch/opatch2024-08-11_14-14-40PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch
succeeded.
[+ASM:grid@ol79o12cr2sa][/home/grid]$
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33116894
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/12c/grid
Central Inventory : /u01/app/oraInventory
from :
/u01/app/12c/grid/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/12c/grid/cfgtoollogs/opatch/opatch2024-08-11_14-14-40PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch
succeeded.
[+ASM:grid@ol79o12cr2sa][/home/grid]$
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33610989
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/12c/grid
Central Inventory : /u01/app/oraInventory
from :
/u01/app/12c/grid/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/12c/grid/cfgtoollogs/opatch/opatch2024-08-11_14-14-41PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch
succeeded.
[+ASM:grid@ol79o12cr2sa][/home/grid]$
$GRID_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33678030
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/12c/grid
Central Inventory : /u01/app/oraInventory
from :
/u01/app/12c/grid/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/12c/grid/cfgtoollogs/opatch/opatch2024-08-11_14-14-42PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch
succeeded.
7-5. grid 패치 적용 전 용량이 충분한지 확인
[+ASM:grid@ol79o12cr2sa][/home/grid]$ vi
/home/grid/patch_list_gihome.txt
[+ASM:grid@ol79o12cr2sa][/home/grid]$ cat
/home/grid/patch_list_gihome.txt
/tmp/patch/33583921/26839277
/tmp/patch/33583921/33116894
/tmp/patch/33583921/33610989
/tmp/patch/33583921/33678030
[+ASM:grid@ol79o12cr2sa][/home/grid]$
$GRID_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile
/home/grid/patch_list_gihome.txt
Oracle Interim
Patch Installer version 12.2.0.1.42
Copyright (c) 2024,
Oracle Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/12c/grid
Central Inventory : /u01/app/oraInventory
from :
/u01/app/12c/grid/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/12c/grid/cfgtoollogs/opatch/opatch2024-08-11_14-16-27PM_1.log
Invoking prereq "checksystemspace"
Prereq "checkSystemSpace" passed.
OPatch succeeded.
7-6. oracle 패치 적용 전 패치 충돌 확인
$ORACLE_HOME/OPatch/opatch prereq
CheckConflictAgainstOHWithDetail -phBaseDir /tmp/patch/33583921/33587128
$ORACLE_HOME/OPatch/opatch prereq
CheckConflictAgainstOHWithDetail -phBaseDir /tmp/patch/33583921/33678030
--> 지금부터 위의 명령을 실행 것임 (oracle os user)
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33587128
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from :
/u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2024-08-11_14-17-39PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir
/tmp/patch/33583921/33678030
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from :
/u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2024-08-11_14-17-42PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
7-7. oracle 패치 적용 전 용량이 충분한지 확인
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
cd ~
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ vi
patch_list_dbhome.txt
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ cat
patch_list_dbhome.txt
/tmp/patch/33583921/33587128
/tmp/patch/33583921/33678030
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile
patch_list_dbhome.txt
Oracle Interim Patch
Installer version 12.2.0.1.42
Copyright (c) 2024, Oracle
Corporation. All rights reserved.
PREREQ session
Oracle
Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from :
/u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch
version : 12.2.0.1.42
OUI
version : 12.2.0.1.4
Log file location :
/u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2024-08-11_14-18-33PM_1.log
Invoking prereq "checksystemspace"
Prereq "checkSystemSpace" passed.
OPatch succeeded.
7-8. grid/oracle 패치 적용 엔진 백업
[root@ol79o12cr2sa][/root]$ cd $GRID_HOME
[root@ol79o12cr2sa][/u01/app/12c/grid]$ cd ../
[root@ol79o12cr2sa][/u01/app/12c]$ tar cvpzf grid.tar grid
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ cd
$ORACLE_HOME
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c/db_1]$
cd ../
[PTDB:oracle@ol79o12cr2sa][/u01/app/oracle/product/12c]$
tar cvpzf db_1.tar db_1
7-9. grid 패치 적용
[root@ol79o12cr2sa][/u01/app/12c]$
$GRID_HOME/crs/install/roothas.sh -prepatch
Using
configuration parameter file: /u01/app/12c/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/crsdata/ol79o12cr2sa/crsconfig/hapatch_2024-08-11_02-26-40PM.log
2024/08/11 14:27:03 CLSRSC-347: Successfully unlock
/u01/app/12c/grid
$GRID_HOME/OPatch/opatch
apply -oh $GRID_HOME /tmp/patch/33583921/26839277
$GRID_HOME/OPatch/opatch apply -oh $GRID_HOME
/tmp/patch/33583921/33116894
$GRID_HOME/OPatch/opatch
apply -oh $GRID_HOME /tmp/patch/33583921/33610989
$GRID_HOME/OPatch/opatch apply -oh $GRID_HOME
/tmp/patch/33583921/33678030
--> 지금부터 위의 명령을 실행해서 패치 적용
7-10. oralce 패치 적용
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME
/tmp/patch/33583921/33587128
$ORACLE_HOME/OPatch/opatch
apply -oh $ORACLE_HOME /tmp/patch/33583921/33678030
--> 지금부터 위의 명령을 실행해서 패치 적용
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
/tmp/patch/33583921/33587128/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME
/tmp/patch/33583921/33587128
OPatch found the word "error"
in the stderr of the make command.
Please look at this
stderr. You can re-run this make command.
Stderr
output:
chmod: changing permissions of
‘/u01/app/oracle/product/12c/db_1/bin/extjobO’: Operation not permitted
make: [iextjob] Error 1 (ignored)
--> 위의 에러는 무시해도됨
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
/tmp/patch/33583921/33678030/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
$ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME
/tmp/patch/33583921/33678030
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ /tmp/patch/33583921/33678030/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME
7-11. 패치 적용 후 설정
[root@ol79o12cr2sa][/u01/app/12c]$ chown
root $DB_HOME/bin/extjob
[root@ol79o12cr2sa][/u01/app/12c]$ chmod 4750
$DB_HOME/bin/extjob
[root@ol79o12cr2sa][/u01/app/12c]$ $GRID_HOME/rdbms/install/rootadd_rdbms.sh
[root@ol79o12cr2sa][/u01/app/12c]$ $GRID_HOME/crs/install/roothas.sh -postpatch
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$
$ORACLE_HOME/OPatch/datapatch -verbose
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ ss
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
7-12. 패치 적용 확인
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ opatch lspatches
-oh $GRID_HOME
33678030;OCW JAN 2022 RELEASE
UPDATE 12.2.0.1.220118 (33678030)
33610989;TOMCAT
RELEASE UPDATE 12.2.0.1.0(ID:RELEASE) (33610989)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720
(33116894)
26839277;DBWLM RELEASE UPDATE
12.2.0.1.0(ID:170913) (26839277)
OPatch succeeded.
[PTDB:oracle@ol79o12cr2sa][/home/oracle]$ opatch lspatches
-oh $ORACLE_HOME
33678030;OCW JAN 2022 RELEASE
UPDATE 12.2.0.1.220118 (33678030)
33587128;Database Jan
2022 Release Update : 12.2.0.1.220118 (33587128)
OPatch succeeded.
SELECT PATCH_ID
, PATCH_UID
, VERSION
, STATUS
, ACTION
, ACTION_TIME
, BUNDLE_SERIES
, BUNDLE_ID
,
LOGFILE
FROM
DBA_REGISTRY_SQLPATCH
;
8. 오라클 설치 후
설정
8-1. gloin 설정
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$
cd $ORACLE_HOME/sqlplus/admin
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1/sqlplus/admin]$
vi glogin.sql
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1/sqlplus/admin]$
cat 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
#COLUMN CON_NAME NEW_VALUE _CON_NAME
#SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;
#ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD:HH24:MI:SS';
SELECT SYS_CONTEXT('USERENV',
'HOST') AS HOSTNAME FROM DUAL;
#SELECT
SYS_CONTEXT('USERENV', 'CON_NAME') AS CON_NAME FROM DUAL;
SET SQLPROMPT
'[&_DATE][&_HOSTNAME]<&_CONNECT_IDENTIFIER@&_USER.> '
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
8-2. Autotask OFF
--CDB 및 PDB 각각 모두 설정해줘야함
col client_name for a40
SELECT CLIENT_NAME
, STATUS
FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME
STATUS
----------------------------------------
--------
auto optimizer stats
collection ENABLED
auto space
advisor
ENABLED
sql tuning
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 => 'auto optimizer stats
collection', operation => null, window_name => null);
exec dbms_auto_task_admin.disable(client_name => 'sql
tuning advisor', operation => null, window_name =>
null);
col client_name for a40
SELECT CLIENT_NAME
, STATUS
FROM DBA_AUTOTASK_CLIENT;
CLIENT_NAME
STATUS
----------------------------------------
--------
auto optimizer stats
collection DISABLED
auto space
advisor
DISABLED
sql tuning
advisor
DISABLED
8-3. 스케줄러 세팅
--CDB 및 PDB 각각 모두 설정해줘야함
COL OWNER FOR A10
COL WINDOW_NAME FOR A20
COL
RESOURCE_PLAN FOR A30
COL REPEAT_INTERVAL FOR A70
COL ENABLED FOR A10
SELECT OWNER,
WINDOW_NAME, RESOURCE_PLAN, REPEAT_INTERVAL, ENABLED FROM
DBA_SCHEDULER_WINDOWS;
OWNER
WINDOW_NAME
RESOURCE_PLAN
REPEAT_INTERVAL
ENABLED
---------- --------------------
------------------------------
----------------------------------------------------------------------
----------
SYS
MONDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=MON;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
TUESDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=TUE;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
WEDNESDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=WED;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
THURSDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=THU;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
FRIDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=FRI;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
SATURDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=SAT;byhour=6;byminute=0;
bysecond=0
TRUE
SYS
SUNDAY_WINDOW
DEFAULT_MAINTENANCE_PLAN
freq=daily;byday=SUN;byhour=6;byminute=0;
bysecond=0
TRUE
SYS
WEEKNIGHT_WINDOW
(NULL)
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
FALSE
SYS
WEEKEND_WINDOW
(NULL)
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
FALSE
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',
'');
COL OWNER FOR A10
COL WINDOW_NAME FOR A20
COL
RESOURCE_PLAN FOR A30
COL REPEAT_INTERVAL FOR A70
COL ENABLED FOR A10
SELECT OWNER,
WINDOW_NAME, RESOURCE_PLAN, REPEAT_INTERVAL, ENABLED FROM
DBA_SCHEDULER_WINDOWS;
OWNER
WINDOW_NAME
RESOURCE_PLAN
REPEAT_INTERVAL
ENABLED
---------- --------------------
------------------------------
----------------------------------------------------------------------
----------
SYS
MONDAY_WINDOW
(NULL)
freq=daily;byday=MON;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
TUESDAY_WINDOW
(NULL)
freq=daily;byday=TUE;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
WEDNESDAY_WINDOW
(NULL)
freq=daily;byday=WED;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
THURSDAY_WINDOW
(NULL)
freq=daily;byday=THU;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
FRIDAY_WINDOW
(NULL)
freq=daily;byday=FRI;byhour=22;byminute=0;
bysecond=0
TRUE
SYS
SATURDAY_WINDOW
(NULL)
freq=daily;byday=SAT;byhour=6;byminute=0;
bysecond=0
TRUE
SYS
SUNDAY_WINDOW
(NULL)
freq=daily;byday=SUN;byhour=6;byminute=0;
bysecond=0
TRUE
SYS
WEEKNIGHT_WINDOW
(NULL)
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
FALSE
SYS
WEEKEND_WINDOW
(NULL)
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
FALSE
BEGIN
DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('TUESDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('WEDNESDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('THURSDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('FRIDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('SATURDAY_WINDOW');
DBMS_SCHEDULER.DISABLE('SUNDAY_WINDOW');
END;
/
COL OWNER FOR A10
COL WINDOW_NAME FOR A20
COL
RESOURCE_PLAN FOR A30
COL REPEAT_INTERVAL FOR A70
COL ENABLED FOR A10
SELECT OWNER,
WINDOW_NAME, RESOURCE_PLAN, REPEAT_INTERVAL, ENABLED FROM
DBA_SCHEDULER_WINDOWS;
OWNER
WINDOW_NAME
RESOURCE_PLAN
REPEAT_INTERVAL
ENABLED
---------- --------------------
------------------------------
----------------------------------------------------------------------
----------
SYS
MONDAY_WINDOW
(NULL)
freq=daily;byday=MON;byhour=22;byminute=0;
bysecond=0
FALSE
SYS
TUESDAY_WINDOW
(NULL)
freq=daily;byday=TUE;byhour=22;byminute=0;
bysecond=0
FALSE
SYS
WEDNESDAY_WINDOW
(NULL)
freq=daily;byday=WED;byhour=22;byminute=0;
bysecond=0
FALSE
SYS
THURSDAY_WINDOW
(NULL)
freq=daily;byday=THU;byhour=22;byminute=0;
bysecond=0
FALSE
SYS
FRIDAY_WINDOW
(NULL)
freq=daily;byday=FRI;byhour=22;byminute=0;
bysecond=0
FALSE
SYS
SATURDAY_WINDOW
(NULL)
freq=daily;byday=SAT;byhour=6;byminute=0;
bysecond=0
FALSE
SYS
SUNDAY_WINDOW
(NULL)
freq=daily;byday=SUN;byhour=6;byminute=0;
bysecond=0
FALSE
SYS
WEEKNIGHT_WINDOW
(NULL)
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
FALSE
SYS
WEEKEND_WINDOW
(NULL)
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
FALSE
8-4. HEAT_MAP 명시적
OFF
--CDB만 설정
CREATE OR REPLACE TRIGGER
SYS.HEAT_MAP_OFF
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE
IMMEDIATE 'ALTER SYSTEM SET HEAT_MAP = OFF';
END;
/
;
8-5. AWR 수집 및 보관 주기
설정
--CDB 및 PDB 각각 모두 설정해줘야함
exec dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 14*24*60);
8-6. SYSAUX 용량
산정
--CDB 및 PDB 각각 모두 설정해줘야함
COL
CON_ID FOR 999
COL
CON_NAME FOR A15
COL
FILE# FOR 99999
COL NAME
FOR A80
COL STATUS
FOR A10
COL
MB FOR
99999999
SELECT DF.CON_ID
, C.NAME AS CON_NAME
, DF.FILE#
, DF.NAME
, DF.STATUS
, DF.BYTES/1024/1024 AS MB
FROM V$DATAFILE DF JOIN V$CONTAINERS C ON DF.CON_ID
= C.CON_ID
ORDER BY DF.CON_ID,
DF.FILE#;
CON_ID
CON_NAME FILE#
NAME
STATUS MB
------ --------------- ------
--------------------------------------------------------------------------------
---------- ---------
1
CDB$ROOT
1
+DATA1/ORA23S1/DATAFILE/system.258.1207861901
SYSTEM 1060
1
CDB$ROOT
3 +DATA1/ORA23S1/DATAFILE/sysaux.260.1207861943
ONLINE 730
1
CDB$ROOT
7
+DATA1/ORA23S1/DATAFILE/users.262.1207861965
ONLINE
7
1
CDB$ROOT 11
+DATA1/ORA23S1/DATAFILE/undotbs1.257.1207861899
ONLINE 40
2
PDB$SEED
2
+DATA1/ORA23S1/2BD45B2C420728BBE06337D35E643DB0/DATAFILE/system.259.1207861935
SYSTEM 310
2
PDB$SEED
4
+DATA1/ORA23S1/2BD45B2C420728BBE06337D35E643DB0/DATAFILE/sysaux.261.1207861957
ONLINE 400
2
PDB$SEED
9
+DATA1/ORA23S1/2BD45B2C420728BBE06337D35E643DB0/DATAFILE/undotbs1.263.1207861967
ONLINE 100
3
ORA23S11 12
+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/system.272.1207862139
SYSTEM 310
3
ORA23S11 13
+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/sysaux.273.1207862139
ONLINE 470
3
ORA23S11 14
+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/undotbs1.271.1207862139
ONLINE 100
3
ORA23S11 15
+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/users.275.1207862159
ONLINE
7
[2025-07-31:21:46:14][ol8ora23s1]<ORA23S1@SYS> show con_name;
CON_NAME
------------------------------
CDB$ROOT
[2025-03-30:20:01:50][ol8ora23s1]<ORA23S1@SYS> @?/rdbms/admin/utlsyxsz.sql
--> CDB SYSAUX의 용량을 산정함
[2025-03-30:20:01:50][ol8ora23s1]<ORA23S1@SYS> ALTER DATABASE DATAFILE '+DATA1/ORA23S1/DATAFILE/sysaux.260.1207861943' resize 2048M;
[2025-07-31:21:46:14][ol8ora23s1]<ORA23S1@SYS> show pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA23S11
READ WRITE NO
[2025-07-31:21:46:14][ol8ora23s1]<ORA23S1@SYS> alter session set container=ORA23S11;
Session altered.
Elapsed: 00:00:00.00
[2025-07-31:21:46:14][ol8ora23s1]<ORA23S1@SYS>
@?/rdbms/admin/utlsyxsz.sql
--> PDB SYSAUX의 용량을 산정함
[2025-03-30:20:01:50][ol8ora23s1]<ORA23S1@SYS> ALTER DATABASE DATAFILE '+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/sysaux.273.1207862139'
resize 2048M;
Elapsed: 00:00:03.86
8-7. alert log 표기 방식
변경
[2025-07-31:21:50:51][ol8ora23s1]<ORA23S1@SYS> show con_name;
CON_NAME
------------------------------
CDB$ROOT
[2025-07-31:21:50:51][ol8ora23s1]<ORA23S1@SYS> alter system set uniform_log_timestamp_format=false scope=both;
System altered.
Elapsed: 00:00:00.10
8-8. dbms statistics 수집
--CDB 및 PDB 각각 모두 설정해줘야함
exec
dbms_stats.gather_fixed_objects_stats;
exec
dbms_stats.gather_dictionary_stats;
8-9. LOCAL_TEMP_TABLESPACE 설정 확인
--CDB 및 PDB 각각 모두 설정해줘야함
SELECT USERNAME, LOCAL_TEMP_TABLESPACE FROM
DBA_USERS;
--> 이것이
TEMP가 아닌 경우 아래와 같이 설정함
ALTER USER <USER_NAME> LOCAL TEMPORARY TABLESPACE
TEMP;
8-10. SUPPLEMENTAL_LOG 설정
--CDB만
SELECT
SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
alter
database add supplemental log data;
SELECT
SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
8-11. DBMS PROFILE 설정
--CDB 및 PDB 각각 모두 설정해줘야함
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
;
8-12. UTL_SMTP, UTP_HTTP에서 권한 회수 (이건 작업 보류함)
--CDB 및 PDB
각각 모두 설정해줘야함
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');
8-13. INVALID OBJECT
COMPILE
--CDB 및 PDB 각각 모두 설정해줘야함
SELECT OBJECT_NAME
, OBJECT_TYPE
, LAST_DDL_TIME
, 'ALTER PACKAGE ' || OWNER || '.'
|| OBJECT_NAME || ' COMPILE;'
,
'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;'
FROM DBA_OBJECTS
WHERE
OWNER = 'SYS'
AND OBJECT_TYPE IN
('PACKAGE', 'PACKAGE BODY')
AND STATUS =
'INVALID';
8-14. Audit_trail 및 Audit_sys_operation 파라미터
설정
--CDB만
ALTER SYSTEM SET audit_trail = os scope=spfile; -->
Secure OS 및 DB Safer가 둘다 도입된 경우 NONE 그렇지 않고 하나라도 누락이면 OS
ALTER SYSTEM SET audit_sys_operations = true scope=spfile;
--> Secure OS 및 DB Safer가 둘다 도입된 경우 FALSE 그렇지 않고 하나라도 누락이면
TRUE
8-15. disk i/o 및 파일 시스템
i/o 관련 파라미터 설정
--CDB만
ALTER SYSTEM SET
disk_asynch_io = true scope=spfile;
ALTER SYSTEM SET
filesystemio_options = setall scope=spfile;
8-16. 파라미터 설정
--CDB만 (ISPDB_MODIFIABLE이 true인 것은 향후 나중에 pdb별로
설정하면됨)
--> 파라미터
생성 전 백업 한번 받기
[2025-03-30:20:10:53][ol8ora23s1]<ORA23S1@SYS> create pfile from
spfile;
--23ai는 추후에 권장 파라미터 정리할 것 (2025-03-30)
물리메모리가 10기가인 경우
SELECT A."구분"
, A.PARAMETER
, B.PARAMETER
, A.DEFAULT_VALUE
, A."VALUE"
, B.RECOMMENDED_VALUE
, CASE WHEN A."구분" = '일반'
THEN 'ALTER SYSTEM SET ' || A.PARAMETER || '=' || ''||B.RECOMMENDED_VALUE ||''
|| ' SCOPE=SPFILE'
--
|| ' SID=''*'''
||';'
WHEN A."구분" = '히든'
THEN 'ALTER SYSTEM SET ' || '"'||A.PARAMETER||'"' || '=' ||
''||B.RECOMMENDED_VALUE ||'' || ' SCOPE=SPFILE'
--
|| ' SID=''*'''
||';'
END AS SCRIPT
FROM
(
SELECT '일반' AS
"구분"
, 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 '히든' AS
"구분"
, 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 '_add_col_optim_enabled' AS
"PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_partition_large_extents' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_index_partition_large_extents' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'db_files' AS
"PARAMETER"
, '1000' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'deferred_segment_creation' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'max_string_size' AS
"PARAMETER"
, 'standard' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'resource_manager_plan' AS
"PARAMETER" ,
'''FORCE:''' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'gcs_server_processes' AS
"PARAMETER"
, '2' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_ges_server_processes' AS
"PARAMETER" ,
'2' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_high_priority_processes' AS
"PARAMETER" ,
'''LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9*''' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_highest_priority_processes' AS
"PARAMETER" , '''VKTM|LG*|LMS*''' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_os_sched_high_priority' AS
"PARAMETER" , '1' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_os_sched_highest_priority' AS "PARAMETER"
, '1' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_use_adaptive_log_file_sync' AS "PARAMETER" ,
'FALSE' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_use_single_log_writer' AS
"PARAMETER" , 'TRUE'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_rollback_segment_count' AS
"PARAMETER" , '1000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cleanup_rollback_entries' AS
"PARAMETER" , '2000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_undo_autotune' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'session_cached_cursors' AS
"PARAMETER" , '100'
AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_highthreshold_undoretention' AS "PARAMETER" , '4294967294'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'temp_undo_enabled' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_plans' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_statistics' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_reporting_only' AS "PARAMETER", 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optim_peek_user_binds' AS
"PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_use_feedback' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_adaptive_cursor_sharing' AS "PARAMETER" , 'false'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing' AS "PARAMETER" , 'none'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing_rel' AS "PARAMETER", 'none' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_aggr_groupby_elim' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_load' AS
"PARAMETER" , 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_rowsets_enabled' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'memory_target' AS
"PARAMETER"
, '0' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_PX_use_large_pool' AS
"PARAMETER"
, 'true' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_memory_imm_mode_without_autosga' AS "PARAMETER"
, 'true' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_enable_shared_pool_durations' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'result_cache_max_size' AS "PARAMETER", '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'pga_aggregate_limit' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_policy_time' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_undo_affinity' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE" FROM
DUAL
-- UNION
ALL SELECT '_gc_bypass_readers' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
--
UNION ALL SELECT '_gc_read_mostly_locking' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_dump_remote_lock' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'parallel_force_local' AS "PARAMETER", 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_sys_logon_delay' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'enable_pluggable_database' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_fatalprocess_redo_dump_time_limit' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE"
FROM DUAL
--
UNION ALL SELECT '_nonfatalprocess_redo_dump_time_limit' AS "PARAMETER", '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_client_enable_auto_unregister' AS "PARAMETER", 'true' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_emon_send_timeout' AS "PARAMETER", '10000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_clusterwide_global_transactions' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_verify_fg_log_checksum' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT '_drop_stat_segment' AS "PARAMETER", '1' AS "RECOMMENDED_VALUE" FROM
DUAL
-- UNION
ALL SELECT 'parallel_adaptive_multi_user' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL --deprecated
UNION ALL SELECT
'sga_target' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'sga_max_size' AS "PARAMETER", '3221225472' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'db_cache_size' AS "PARAMETER" , '1275068416' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'shared_pool_size' AS "PARAMETER" , '1073741824' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'large_pool_size' AS "PARAMETER" , '536870912' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'streams_pool_size' AS "PARAMETER", '167772160' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'java_pool_size' AS "PARAMETER" , '167772160' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'pga_aggregate_target' AS "PARAMETER" , '1073741824' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_pga_max_size' AS "PARAMETER" , '1073741824' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_dlm_stats_collect' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_nonfatalprocess_redo_dump_time_limit' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cursor_obsolete_threshold' AS "PARAMETER" , '1024' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cursor_reload_failure_threshold' AS "PARAMETER" , '5' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_report_capture_cycle_time' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_conventional_dml' AS "PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_gby_hash_aggregation_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_connect_by_cost_based' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_null_aware_antijoin' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_dynamic_sampling' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'star_transformation_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_securefiles_concurrency_estimate' AS "PARAMETER" , '50' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'control_file_record_keep_time' AS "PARAMETER" , '15' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_buddy_instance' AS "PARAMETER" , '0' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT '_sql_plan_directive_mgmt_control' AS "PARAMETER" , '0'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_kks_parse_error_warning' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_kks_obsolete_dump_threshold' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_in_memory_undo' AS "PARAMETER" , 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_bloom_filter_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_secure_view_merging' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_disable_system_state' AS "PARAMETER" , '0' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'resource_limit' AS "PARAMETER" , 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'disk_asynch_io' AS "PARAMETER" , 'true' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'filesystemio_options' AS "PARAMETER" , 'setall' AS
"RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'use_large_pages' AS "PARAMETER" , 'ONLY' AS
"RECOMMENDED_VALUE" FROM DUAL --huge page 설정 후 변경
) B
WHERE
B."PARAMETER" = A."PARAMETER"(+)
ORDER BY 1, 2
;
물리메모리가 16기가인 경우
SELECT A."구분"
, A.PARAMETER
, B.PARAMETER
, A.DEFAULT_VALUE
, A."VALUE"
, B.RECOMMENDED_VALUE
, CASE WHEN A."구분" = '일반'
THEN 'ALTER SYSTEM SET ' || A.PARAMETER || '=' || ''||B.RECOMMENDED_VALUE ||''
|| ' SCOPE=SPFILE'
--
|| ' SID=''*'''
||';'
WHEN A."구분" = '히든'
THEN 'ALTER SYSTEM SET ' || '"'||A.PARAMETER||'"' || '=' ||
''||B.RECOMMENDED_VALUE ||'' || ' SCOPE=SPFILE'
--
|| ' SID=''*'''
||';'
END AS SCRIPT
FROM
(
SELECT '일반' AS
"구분"
, 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 '히든' AS
"구분"
, 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 '_add_col_optim_enabled' AS
"PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_partition_large_extents' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_index_partition_large_extents' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'db_files' AS
"PARAMETER"
, '1000' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'deferred_segment_creation' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'max_string_size' AS
"PARAMETER"
, 'standard' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'resource_manager_plan' AS
"PARAMETER" ,
'''FORCE:''' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'gcs_server_processes' AS
"PARAMETER"
, '2' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_ges_server_processes' AS
"PARAMETER" ,
'2' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_high_priority_processes' AS
"PARAMETER" ,
'''LMS*|LM1*|LM2*|LM3*|LM4*|LM5*|LM6*|LM7*|LM8*|LM9*''' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_highest_priority_processes' AS
"PARAMETER" , '''VKTM|LG*|LMS*''' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_os_sched_high_priority' AS
"PARAMETER" , '1' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_os_sched_highest_priority' AS "PARAMETER"
, '1' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_use_adaptive_log_file_sync' AS "PARAMETER" ,
'FALSE' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_use_single_log_writer' AS
"PARAMETER" , 'TRUE'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_rollback_segment_count' AS
"PARAMETER" , '1000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cleanup_rollback_entries' AS
"PARAMETER" , '2000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_undo_autotune' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'session_cached_cursors' AS
"PARAMETER" , '100'
AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_highthreshold_undoretention' AS "PARAMETER" , '4294967294'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'temp_undo_enabled' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_plans' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_statistics' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_adaptive_reporting_only' AS "PARAMETER", 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optim_peek_user_binds' AS
"PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_use_feedback' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_adaptive_cursor_sharing' AS "PARAMETER" , 'false'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing' AS "PARAMETER" , 'none'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_extended_cursor_sharing_rel' AS "PARAMETER", 'none' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_aggr_groupby_elim' AS
"PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_load' AS
"PARAMETER" , 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_rowsets_enabled' AS
"PARAMETER"
, 'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'memory_target' AS
"PARAMETER"
, '0' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_PX_use_large_pool' AS
"PARAMETER"
, 'true' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_memory_imm_mode_without_autosga' AS "PARAMETER"
, 'true' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_enable_shared_pool_durations' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'result_cache_max_size' AS "PARAMETER", '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'pga_aggregate_limit' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_policy_time' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_undo_affinity' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE" FROM
DUAL
-- UNION
ALL SELECT '_gc_bypass_readers' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
--
UNION ALL SELECT '_gc_read_mostly_locking' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT '_gc_dump_remote_lock' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'parallel_force_local' AS "PARAMETER", 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_sys_logon_delay' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'enable_pluggable_database' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_fatalprocess_redo_dump_time_limit' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE"
FROM DUAL
--
UNION ALL SELECT '_nonfatalprocess_redo_dump_time_limit' AS "PARAMETER", '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_client_enable_auto_unregister' AS "PARAMETER", 'true' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_emon_send_timeout' AS "PARAMETER", '10000' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_clusterwide_global_transactions' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_verify_fg_log_checksum' AS "PARAMETER", 'false' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT '_drop_stat_segment' AS "PARAMETER", '1' AS "RECOMMENDED_VALUE" FROM
DUAL
-- UNION
ALL SELECT 'parallel_adaptive_multi_user' AS "PARAMETER", 'false' AS
"RECOMMENDED_VALUE" FROM DUAL --deprecated
UNION ALL SELECT
'sga_target' AS "PARAMETER", '0' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'sga_max_size' AS "PARAMETER", '5153960755' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'db_cache_size' AS "PARAMETER" , '3207803699' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'shared_pool_size' AS "PARAMETER" , '1073741824' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'large_pool_size' AS "PARAMETER" , '536870912' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'streams_pool_size' AS "PARAMETER", '167772160' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'java_pool_size' AS "PARAMETER" , '167772160' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'pga_aggregate_target' AS "PARAMETER" , '1717986918' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_pga_max_size' AS "PARAMETER" , '1073741824' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_dlm_stats_collect' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_nonfatalprocess_redo_dump_time_limit' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cursor_obsolete_threshold' AS "PARAMETER" , '1024' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_cursor_reload_failure_threshold' AS "PARAMETER" , '5' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_report_capture_cycle_time' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_gather_stats_on_conventional_dml' AS "PARAMETER" ,
'false' AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_gby_hash_aggregation_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_connect_by_cost_based' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_optimizer_null_aware_antijoin' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_dynamic_sampling' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'star_transformation_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_securefiles_concurrency_estimate' AS "PARAMETER" , '50' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'control_file_record_keep_time' AS "PARAMETER" , '15' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_buddy_instance' AS "PARAMETER" , '0' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT '_sql_plan_directive_mgmt_control' AS "PARAMETER" , '0'
AS "RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_kks_parse_error_warning' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_kks_obsolete_dump_threshold' AS "PARAMETER" , '0' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_in_memory_undo' AS "PARAMETER" , 'false' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT '_bloom_filter_enabled' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'optimizer_secure_view_merging' AS "PARAMETER" , 'false' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'_disable_system_state' AS "PARAMETER" , '0' AS "RECOMMENDED_VALUE"
FROM DUAL
UNION ALL SELECT 'resource_limit' AS "PARAMETER" , 'true' AS
"RECOMMENDED_VALUE" FROM DUAL
UNION ALL SELECT
'disk_asynch_io' AS "PARAMETER" , 'true' AS "RECOMMENDED_VALUE" FROM
DUAL
UNION
ALL SELECT 'filesystemio_options' AS "PARAMETER" , 'setall' AS
"RECOMMENDED_VALUE" FROM DUAL
-- UNION ALL
SELECT 'use_large_pages' AS "PARAMETER" , 'ONLY' AS
"RECOMMENDED_VALUE" FROM DUAL --huge page 설정 후 변경
) B
WHERE
B."PARAMETER" = A."PARAMETER"(+)
ORDER BY 1, 2
;
8-17. sqlnet.ora 설정
tcp.validnode도
설정
--> 이건 테스트 서버라 설정하지 않음
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/network/admin]$
vi /u01/app/23ai/grid/network/admin/sqlnet.ora
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/network/admin]$
cat /u01/app/23ai/grid/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File:
/u01/app/23ai/grid/network/admin/sqlnet.ora
# Generated
by Oracle configuration tools.
NAMES.DIRECTORY_PATH=
(TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=9
--리스너 재시작
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/network/admin]$
srvctl stop listener -listener LISTENER
[+ASM:grid@ol8ora23s1][/u01/app/23ai/grid/network/admin]$
srvctl start listener -listener LISTENER
8-18. 아카이브 로그는 파일시스템에
저장
--> 이건 테스트 서버라 설정하지 않음
8-19. HugePage 설정 (23.7에서 성공하지 못한 상태 추후 다시
해볼것 - 20250731)
Oracle Linux: Shell
Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration
(Doc ID 401749.1)
/root 위치에
hugepages_settings.sh 파일 업로드 (root os user)
[root@ol8ora23s1][/root]$
chmod +x hugepages_settings.sh
hugepages_settings.sh: line
38: $'read\r': command not found
hugepages_settings.sh:
line 39: $'\r': command not found
hugepages_settings.sh:
line 42: $'\r': command not found
hugepages_settings.sh:
line 53: syntax error near unexpected token `$'do\r''
'ugepages_settings.sh: line 53: `do
--> 이러한
에러나면 아래와 같이 sed 명령 날려줌
[root@ttdb][/root]$ sed -i 's/\r//'
hugepages_settings.sh
[root@ttdb][/root]$
cat hugepages_settings.sh | grep 5.4
'5.4') echo "Recommended setting:
vm.nr_hugepages = $NUM_PG" ;;
--> 해당
위치에 위의 한줄 추가
[root@ol8ora23s1][/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 11g 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 = 2463
[root@ol8ora23s1][/root]$ vi /etc/sysctl.conf
[root@ol8ora23s1][/root]$ cat /etc/sysctl.conf | grep
vm.nr_hugepages
vm.nr_hugepages =
2463
[root@ol8ora23s1][/root]$ sysctl -p
[root@ol8ora23s1][/root]$ cat /proc/meminfo | grep -i huge
AnonHugePages:
0 kB
ShmemHugePages: 0
kB
FileHugePages:
0 kB
HugePages_Total: 1562
HugePages_Free: 1562
HugePages_Rsvd:
0
HugePages_Surp:
0
Hugepagesize: 2048
kB
Hugetlb:
3198976 kB
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> create pfile
from spfile;
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> alter system set
use_large_pages=only scope=spfile;
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> shutdown
immediate;
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> startup
8-20. PDB 자동 OPEN
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ ss
SQL*Plus: Release 23.0.0.0.0 - Production on
Sun Mar 30 20:44:10 2025
Version
23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle
Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.4.0.24.05
[2025-03-30:20:44:10][ol8ora23s1]<ORA23S1@SYS> show pdbs;
CON_ID
CON_NAME
OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2
PDB$SEED
READ ONLY NO
3
ORA23S11
MOUNTED (NULL)
[2025-03-30:20:44:10][ol8ora23s1]<ORA23S1@SYS> ALTER
PLUGGABLE DATABASE ORA23S11 open;
Pluggable database altered.
Elapsed: 00:00:03.48
[2025-03-30:20:44:10][ol8ora23s1]<ORA23S1@SYS> ALTER
PLUGGABLE DATABASE ORA23S11 SAVE
STATE;
Pluggable database altered.
Elapsed: 00:00:00.08
[2025-03-30:20:44:10][ol8ora23s1]<ORA23S1@SYS>
COL CON_NAME FOR A10
COL STATE FOR A10
SELECT CON_NAME,
STATE FROM DBA_PDB_SAVED_STATES;
CON_NAME STATE
---------- ----------
ORA23S11
OPEN
1 row selected.
--> 이렇게 하면 CDB 내렸다가 올릴 때 자동으로 pdb가 오픈됨
8-21. 테이블 스페이스
확장
--PDB만
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
;
ALTER DATABASE DATAFILE
'+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/system.272.1207862139'
resize 2048M;
ALTER DATABASE DATAFILE
'+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/DATAFILE/undotbs1.271.1207862139'
resize 2048M;
ALTER DATABASE TEMPFILE
'+DATA1/ORA23S1/3B257B81511816A8E0634700A8C0985C/TEMPFILE/temp.274.1207862157'
RESIZE 1024M;
8-22. 실습 데이터 구축
--PDB만
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ alias ssp
alias ssp='rlwrap sqlplus sys/oracle@ORA23S11 as sysdba'
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$
ssp
SQL*Plus: Release
23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 1 21:57:45
2025
Version 23.7.0.25.01
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai
Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered
Systems
Version 23.7.0.25.01
[2025-08-01:21:57:45][ol8ora23s1]<ORA23S11@SYS>
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 12G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE TUNER_IDX1
DATAFILE '+DATA1' SIZE 12G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE
TEMPORARY TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1' SIZE
4G
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;
ALTER USER TUNER DEFAULT
TABLESPACE TUNER_DATA1;
ALTER USER TUNER TEMPORARY
TABLESPACE TUNER_TEMP;
----------------------------------------------------------------------------------------------------------------------------------------
--2. 바인드 변수 세팅 (tuner 계정으로 실행할 것)
--SYS@PTDB> conn tuner
--Enter password:
--Connected.
--TUNER@PTDB>
----------------------------------------------------------------------------------------------------------------------------------------
conn tuner@ORA23S11;
VAR V_TB_CUST_CNT
NUMBER;
EXEC :V_TB_CUST_CNT :=
1000000;
VAR V_TB_ITEM_CNT
NUMBER;
EXEC :V_TB_ITEM_CNT :=
100000;
--: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;
----------------------------------------------------------------------------------------------------------------------------------------
8-23. 각종 로그 삭제 주기에 따른 자동 삭제 설정
8-23-1. oracle 각종 로그 제거 및
alert 로그 mv 스크립트
[ORA23S1:oracle@ol8ora23s1][/u01/app/oracle/product/23ai/db_1/network/admin]$ ss
SQL*Plus: Release
23.0.0.0.0 - Production on Sun Mar 30 20:22:03 2025
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0
- Production
Version 23.4.0.24.05
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> show parameter audit_file_dest
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
audit_file_dest
string
/u01/app/oracle/product/23ai/db_1/rdbms/audit
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> show parameter
background_dump_dest
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
background_dump_dest
string
/u01/app/oracle/product/23ai/db_1/rdbms/log
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> show parameter
user_dump_dest
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
user_dump_dest
string
/u01/app/oracle/product/23ai/db_1/rdbms/log
[2025-03-30:20:17:17][ol8ora23s1]<ORA23S1@SYS> select value from
v$diag_info;
VALUE
----------------------------------------------------------------------------------------------------
TRUE
/u01/app/oracle
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/trace
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/alert
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/incident
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/cdump
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/hm
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/trace/ORA23S1_ora_2397.trc
0
0
/u01/app/oracle/product/23ai/db_1
/u01/app/oracle/diag/rdbms/ora23s1/ORA23S1/trace/attention_ORA23S1.log
13
rows selected.
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$
mkdir -pv /home/oracle/os_scripts
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd
/home/oracle/os_scripts
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
delete_oracle_log.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
delete_oracle_log.sh
#!/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 {}
\;
--> 23ai에서는 adump 디렉토리가 존재하지 않는데? 추후
확인해볼것(2025-03-30)
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
modify_oracle_alert_log.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
modify_oracle_alert_log.sh
#!/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
[PTDB:oracle@ttdb][/home/oracle/os_scripts]$ chmod 775
delete_oracle_log.sh modify_oracle_alert_log.sh
[PTDB:oracle@ttdb][/home/oracle/os_scripts]$ crontab
-e
no crontab for oracle - using an empty one
crontab: installing new crontab
[PTDB:oracle@ttdb][/home/oracle/os_scripts]$ 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
8-23-2
grid 각종 로그 제거 및 alert 로그
mv 스크립트
[+ASM:grid@ol8ora23s1][/home/grid]$ mkdir -pv
/home/grid/os_scripts
mkdir: created directory
'/home/grid/os_scripts'
[+ASM:grid@ol8ora23s1][/home/grid]$ cd /home/grid/os_scripts
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ vi
delete_grid_log.sh
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ cat
delete_grid_log.sh
#!/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 {} \;
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ vi
modify_grid_alert_log.sh
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ cat
modify_grid_alert_log.sh
#!/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
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ chmod 775
delete_grid_log.sh modify_grid_alert_log.sh
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ crontab
-e
no crontab for grid - using an empty one
crontab: installing new crontab
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ crontab
-l
0 2 * * * /home/grid/os_scripts/delete_grid_log.sh
&>/dev/null
0 2 1 * *
/home/grid/os_scripts/modify_grid_alert_log.sh &>/dev/null
8-23-3. adrci 로그 자동 삭제 기간을 100일로 설정
[ORA23S1:oracle@ol8ora23s1][/home/oracle]$ cd
/home/oracle/os_scripts
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
delete_oracle_log_auto_adrci.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
delete_oracle_log_auto_adrci.sh
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 ""
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
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
chmod 775 delete_oracle_log_auto_adrci.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ sh
delete_oracle_log_auto_adrci.sh
$ORACLE_SID:
[ORA23S1]
---------------------------------------------------------------------------------------
START : [diag/rdbms/ora23s1/ORA23S1]
adrci exec="set home diag/rdbms/ora23s1/ORA23S1;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/rdbms/ora23s1/ORA23S1]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/clients/user_oracle/RMAN_672332489_115]
adrci exec="set home
diag/clients/user_oracle/RMAN_672332489_115;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/clients/user_oracle/RMAN_672332489_115]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/clients/user_oracle/host_672332489_115]
adrci exec="set home
diag/clients/user_oracle/host_672332489_115;"
complete
adrci set control SHORTP_POLICY and LONGP_POLICY is 2400;"
END : [diag/clients/user_oracle/host_672332489_115]
---------------------------------------------------------------------------------------
[+ASM:grid@ol8ora23s1][/home/grid]$ cd
/home/grid/os_scripts
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ vi
delete_grid_log_auto_adrci.sh
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ cat
delete_grid_log_auto_adrci.sh
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
[+ASM:grid@ol8ora23s1][/home/grid/os_scripts]$ sh
delete_grid_log_auto_adrci.sh
$ORACLE_USER_ORACLE_SID:
[ORA23S1]
---------------------------------------------------------------------------------------
START : [diag/asm/+asm/+ASM]
adrci
exec="set home diag/asm/+asm/+ASM;"
complete adrci set
control SHORTP_POLICY and LONGP_POLICY is 2400;"
END :
[diag/asm/+asm/+ASM]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/crs/ol8ora23s1/crs]
adrci exec="set home diag/crs/ol8ora23s1/crs;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/crs/ol8ora23s1/crs]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/tnslsnr/ol8ora23s1/listener]
adrci exec="set home diag/tnslsnr/ol8ora23s1/listener;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/tnslsnr/ol8ora23s1/listener]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/kfod/ol8ora23s1/kfod]
adrci exec="set home diag/kfod/ol8ora23s1/kfod;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/kfod/ol8ora23s1/kfod]
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
START : [diag/orapwd/ol8ora23s1/kuzr]
adrci exec="set home diag/orapwd/ol8ora23s1/kuzr;"
complete adrci set control SHORTP_POLICY and LONGP_POLICY
is 2400;"
END : [diag/orapwd/ol8ora23s1/kuzr]
---------------------------------------------------------------------------------------
8-24. 자동 백업 설정
(이건 추후
23ai방식으로 바꿀것, 2025-03-30)
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ mkdir
-pv /home/oracle/rman_backup/
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
cdoss
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
rman_init.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
rman_init.sh
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/23ai/db_1
export ORACLE_SID=ORA23S1
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
mkdir -p /home/oracle/rman_backup/ORA23S1/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/ORA23S1/autobackup/%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'+DATA1/ORA23S1/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
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
chmod 775 rman_init.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
vi rman_data.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
rman_data.sh
#!/bin/bash
mkdir -p
/home/oracle/os_scripts/rman_log/
export
ORACLE_HOME=/u01/app/oracle/product/23ai/db_1
export
ORACLE_SID=ORA23S1
export
ORACLE_DBNAME=ORA23S1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
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=/u01/app/oracle/product/23ai/db_1"
echo
"export ORACLE_SID=ORA23S1"
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
(SELECT L.NAME FROM V\$CONTAINERS L
WHERE L.CON_ID = A.CON_ID) || ':' || A.TABLESPACE_NAME
FROM CDB_TABLESPACES A
WHERE A.CONTENTS IN ('PERMANENT', 'UNDO')
UNION ALL
SELECT
'PDB\$SEED:SYSTEM' FROM DUAL UNION ALL
SELECT
'PDB\$SEED:SYSAUX' FROM DUAL UNION ALL
SELECT
'PDB\$SEED:UNDOTBS1' FROM DUAL
;
EXIT;
EOF
while read -r entry;
do
if [ -n "$entry" ]; then
con_name=$(echo
"$entry" | cut -d':' -f1)
ts_name=$(echo
"$entry" | cut -d':' -f2)
if [ "$con_name"
== "CDB\$ROOT" ]; then
echo "BACKUP AS COMPRESSED BACKUPSET TABLESPACE $ts_name FORMAT
'/home/oracle/rman_backup/${ORACLE_DBNAME}/${ts_name}_%U.bkp';" >>
$RMAN_SCRIPT
elif [ "$con_name" == "PDB\$SEED" ]; then
echo "BACKUP AS COMPRESSED BACKUPSET TABLESPACE \"${con_name}\":${ts_name}
FORMAT '/home/oracle/rman_backup/${ORACLE_DBNAME}/PDBSEED_${ts_name}_%U.bkp';"
>> $RMAN_SCRIPT
else
echo "BACKUP AS COMPRESSED BACKUPSET TABLESPACE ${con_name}:${ts_name} FORMAT
'/home/oracle/rman_backup/${ORACLE_DBNAME}/${con_name}_${ts_name}_%U.bkp';"
>> $RMAN_SCRIPT
fi
fi
done <
$TABLESPACE_NAME_FILE
#echo "backup current
controlfile format '/home/oracle/rman_backup/PRDB/CTL_%U';" >>
$RMAN_SCRIPT
echo "BACKUP SPFILE FORMAT
'/home/oracle/rman_backup/$ORACLE_DBNAME/autobackup/SP_%U'"; >>
$RMAN_SCRIPT
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."
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
chmod 775 rman_data.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
rman_arch.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
rman_arch.sh
export
ORACLE_HOME=/u01/app/oracle/product/23ai/db_1
export
ORACLE_SID=ORA23S1
export
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$GRID_HOME/bin:$BASE_PATH
$ORACLE_HOME/bin/rman target /
<< EOF
run {
crosscheck
archivelog all;
backup archivelog all format
'/home/oracle/rman_backup/ORA23S1/%d_ARCHIVE_%T_%u_s%s_p%p' delete input;
delete backup of archivelog all completed before
'SYSDATE-14';
}
exit;
EOF
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ chmod
775 rman_arch.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ vi
rman_backup.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ cat
rman_backup.sh
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
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ chmod
775 rman_backup.sh
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
crontab -e
crontab: installing new crontab
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$
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
20 2 * * * /home/oracle/os_scripts/rman_backup.sh
&>/dev/null
[ORA23S1:oracle@ol8ora23s1][/home/oracle/os_scripts]$ chmod
775 rman_arch.sh rman_backup.sh rman_data.sh rman_init.sh
8-25. OSW 가동 (이건 할 필요 없음 osw 설치 이미 자동 기동되도록 해놓은
상태임)
[root@ttdb][/root]$ vi
/etc/systemd/system/oswbb.service
[root@ttdb][/root]$
cat /etc/systemd/system/oswbb.service
[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
[root@ttdb][/root]$ cd
/home/oracle/oswbb
[root@ttdb][/home/oracle/oswbb]$ vi
startOSWbb.sh
--> 아래와 같이 수정
######################################################################
# Start OSW
######################################################################
nohup ./OSWatcher.sh $1 $2 $3 $4 &
echo $! >
/home/oracle/oswbb/oswbb.pid
[root@ol79o12cr2sa][/home/oracle/oswbb]$ systemctl
daemon-reload
[root@ol79o12cr2sa][/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@ol79o12cr2sa][/home/oracle/oswbb]$ systemctl restart
oswbb.service