오라클 19c redo log 크기 자동 변경 스크립트

OS환경 : Oracle Linux 8.1 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c redo log 크기 자동 변경 스크립트

오라클 환경에서 redo log 크기를 변경하려면 
oracle redo log 삭제 및 추가 및 리사이즈 하기 ( https://positivemh.tistory.com/284 ) 방법처럼
inactive 상태인 redo를 제거하고 새로운 사이즈로 추가하는 절차를 반복해야함

 

테스트 환경에서 매번 이렇게 변경하기가 귀찮은 사람들을 위해 간편한 스크립트를 만듬
먼저 대략적인 방식을 설명하자면 oracle 유저로 쉘스크립트를 실행하면

sysdba 권한으로 접속한 다음 먼저 변경할(tobe) 사이즈를 MB 단위로 물어봄
값을 입력하면 현재 redo를 확인한 뒤 inactive 상태인 redo는 제거 후 신규 사이즈로 변경하고
이후 log switch, checkpoint 명령을 수행해 다른 redo들을 inactive 상태로 만듦
이후 동일한 절차를 반복함
* 참고로 RAC 및 이중화된 redo 그룹은 고려하지 않음
* 테스트 환경에서만 사용하길 권고함

* redo 크기 변경 전 디스크 여유공간을 확인하는등의 절차도 넣으면 좋을것 같지만 당장 나의 테스트 환경에선 필요없어서 넣지 않음

 

 

스크립트

$ cat redo_size_change.sh
#!/bin/bash
 
# redo_size_change.sh
# Shell for Change Redo Size by NMG
# https://positivemh.tistory.com
# Must be used only in Single and Test DB
 
function check_redo {
    sqlplus -S / as sysdba << EOF
    set feedback off verify off heading off echo off trimspool on
    spool /tmp/temp_redo.tmp
    set lines 200
    set pages 1000
    col member for a60
    select l.group#, member, archived, l.status, (bytes/1024/1024) MB
    from v\$log l, v\$logfile f
    where f.group# = l.group#
    order by 1;
    spool off;
    exit;
EOF
    sed -i '1d' /tmp/temp_redo.tmp
}
 
function log_switch_ckp {
sqlplus -S / as sysdba <<EOF
  set feedback off verify off heading off echo off trimspool on
  ALTER SYSTEM SWITCH LOGFILE;
  ALTER SYSTEM CHECKPOINT; 
  EXIT;
EOF
}
 
echo "Redo Size Change Script"
echo ""
echo "Current Redo Status"
check_redo
 
# Receiving input from the user to change the redo log size
echo ""
read -p "Please Enter the TOBE_REDO_SIZE(MB): " tobe_redo_size
 
# Extract redo log size in CURRENT state using awk
current_size=$(awk '$4 == "CURRENT" {print $5; exit}' /tmp/temp_redo.tmp)
 
# Compare the entered size with the current size
if [[ "$current_size" == "$tobe_redo_size" ]]; then
    echo "Current_size($current_sizeIt) and tobe_redo_size($tobe_redo_size) is the same size."
    exit 0
fi
 
# Initialize
all_match=0
 
while [[ $all_match -eq 0 ]]; do
  log_switch_ckp
  check_redo
  awk '$4 == "INACTIVE" {print $1, $2, $5}' /tmp/temp_redo.tmp > /tmp/inactive_redo_groups.txt
  while IFS=' ' read -r group_num member_name current_size
  do
    if [ "$current_size" != "$tobe_redo_size" ]; then
    # Dropping and adding redo log groups
    echo "Drop Redo Group"
    sqlplus -S / as sysdba <<EOF
    ALTER DATABASE DROP LOGFILE GROUP $group_num;
EOF
    rm -v $member_name
    echo ""
    echo "Add Redo Group"
    sqlplus -S / as sysdba <<EOF
    ALTER DATABASE ADD LOGFILE GROUP $group_num '$member_name' SIZE ${tobe_redo_size}M;
EOF
    fi
  done < /tmp/inactive_redo_groups.txt
 
  # Check if all redo log sizes match input values
    all_match=$(awk -v tobe_size="$tobe_redo_size" 'BEGIN {is_match=1} {if ($5 != tobe_size) {is_match=0}} END {print is_match}' /tmp/temp_redo.tmp)
    if [[ $all_match -eq 1 ]]; then
      echo ""
      echo "Changed the size of all Redo log groups to $tobe_redo_size."
      echo "Current Redo Status"
      check_redo
      rm /tmp/inactive_redo_groups.txt
      exit 0
    else
      echo "The sizes of some redo log groups do not match the values entered."
      echo "Repeat again"
      # Repeat again
    fi
done

스크립트 상세 설명
9번째 줄 : check_redo 함수는 redo 상태를 조회하고 /tmp/temp_redo.tmp에 저장함
26번째 줄 : log_switch_ckp 함수는 log switch 와 checkpoint 명령을 수행하는 함수임
38번째 줄 : check_redo 함수를 실행함
42번째 줄 : 변경할 redo 크기를 입력받음
45번째 줄 : 기존 redo 사이즈를 확인하여 current_size 변수에 저장함
48번째 줄 : 기존 redo 사이즈와 변경할 redo 사이즈가 같은지 확인 후 같다면 스크립트를 종료시킴
54번째 줄 : 반복문을 위한 변수를 초기화함
56번째 줄 : 반복문을 시작함
57번째 줄 : log_switch_ckp 함수를 실행함
58번째 줄 : check_redo 함수를 실행함
59번째 줄 : 현재 상태가 inactive 인 redo를 정보를 /tmp/inactive_redo_groups.txt 에 저장함
60번쨰 줄 : 반복문을 시작하면서 IFS 명령으로 그룹번호, 멤버이름(경로), 현재크기를 가져옴(inactive 상태의 redo 그룹 갯수만큼 반복함)
62번째 줄 : redo의 현재 크기와 변경할 크기가 다른 경우 해당 redo를 drop 하고 변경할 사이즈로 add 함
78번째 줄 : /tmp/temp_redo.tmp 파일을 읽어서 현재 모든 redo가 사이즈가 변경이 되었다면 1을 출력하고, 아니면 0을 출력해서 all_match 변수에 담음
79번째 줄 : all_match 변수 값이 1이면 모든 redo 사이즈가 변경된 것이기 때문에 스크립트를 종료함
86번째 줄 : all_match 변수 값이 0이면 모든 redo 사이즈가 변경되지 않은 것이기 때문에 while문을 반복해 실행함

 

 

실행(기존 250MB에서 300MB로 변경)

$ sh redo_size_change.sh
Redo Size Change Script
 
Current Redo Status
 
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                NO  INACTIVE                250
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                NO  CURRENT                 250
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                YES UNUSED                  250
 
Please Enter the TOBE_REDO_SIZE(MB): 300 <<<<< 300 입력 후 엔터
 
#이후 이렇게 자동으로 사이즈를 250MB에서 300으로 변경해줌
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                NO  INACTIVE                250
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                NO  INACTIVE                250
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                NO  CURRENT                 250
Drop Redo Group
 
Database altered.
 
removed '/ORA19/app/oracle/oradata/ORACLE19/redo01.log'
 
Add Redo Group
 
Database altered.
 
Drop Redo Group
 
Database altered.
 
removed '/ORA19/app/oracle/oradata/ORACLE19/redo02.log'
 
Add Redo Group
 
Database altered.
 
The sizes of some redo log groups do not match the values entered.
Repeat again
 
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                NO  CURRENT                 300
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                YES UNUSED                  300
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                NO  INACTIVE                250
Drop Redo Group
 
Database altered.
 
removed '/ORA19/app/oracle/oradata/ORACLE19/redo03.log'
 
Add Redo Group
 
Database altered.
 
The sizes of some redo log groups do not match the values entered.
Repeat again
 
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                NO  INACTIVE                300
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                NO  CURRENT                 300
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                YES UNUSED                  300
 
Changed the size of all Redo log groups to 300.
Current Redo Status
 
         1 /ORA19/app/oracle/oradata/ORACLE19/redo01.log                NO  INACTIVE                300
         2 /ORA19/app/oracle/oradata/ORACLE19/redo02.log                NO  CURRENT                 300
         3 /ORA19/app/oracle/oradata/ORACLE19/redo03.log                YES UNUSED                  300

300MB로 변경이 완료됨

 

 

참조 :

https://positivemh.tistory.com/284

oracle redo log 삭제 및 추가 및 리사이즈 하기