STANDBY DB 구축 및 ARCHIVE LOG FILE 자동 적용 SCRIPT
=====================================================

1. Standby db 구축을 위한 전제조건

1) primary db (main server) 와 standby db는 같은 환경이어야 한다.
즉, H/W 와 O/S가 동일해야 한다.
2) primary db (main server) 와 standby db는 oracle7.3이상으로 archive log
mode로 운영하여야 한다.
3) primary db (main server) 와 standby db는 가능한 물리적구조를 같게하되
$ORACLE_HOME은 같아야한다.
4) standby site는 primary site와 자원 공유가 없고 독립된 system을 사용한다.

2. 최초생성방법

1) 환경정의
- H/W : RS6000
- O/S : Aix 4.1
- Oracle :7.3.3
- primary hostname : prim_host
- standby hostname : stnd_host
- ORACLE_SID=ORA7
- initSID.ora에 정의 사항 : compatible= 7.3.0.0 or above
archive log mode이어야하므로 아래의 parameter정의
log_archive_start=true
log_archive_dest=/oracle/app/oracle/admin/ORA7/arch
log_archive_format=log%t_%s.log
2) primary db를 archive log mode로 정의
svrmgr> startup mount
svrmgr> alter database archivelog;
svrmgr>alter database open;
3) standby db생성을 위한 primary db backup( cold copy나 hot backup으로)
- primary db shutdown
- all datafile,all redo log file copy, parameter file (initSID.ora,conifgSID.ora)copy
ex) rcp /oracle/oradata/ORA7/* stnd_host:/oracle/oradata/ORA7
4) primary db쪽에서 standby db의 controlfile 생성
svrmgr> startup mount;
svrmgr> alter database create standby controlfile as '/oracle/oradata/ORA7/stby.ctl'
위의 결과로 만들어진 comtrolfile을 standby db쪽으로 copy
ex) rcp /oracle/oradata/ORA7/stby.ctl stnd_host:/oracle/oradata/ORA7
5) primary db쪽의 현재 redo log archive및 적용되지않은 archive log file이 있
다면 standby db쪽으로copy
svrmgr> alter database archive log current;
cd /oracle/app/oracle/admin/ORA7/arch
rcp * stnd_host:/oracle/app/oracle/admin/ORA7/arch
6) standby db의 parameter file수정및 archive log file적용
standby db의 configORA7.ora의 controlfile name변경
controlfiles=( /oracle/oradata/ORA7/stby.ctl)
svrmgr> startup nomount
svrmgr> alter database mount standby database;
svrmgr> recover standby database; ß---이 작업대신 자동적용 script수행.
(stby.ksh)
7) 만일 standby db의 물리적구조가 primary db와 다를경우
standby db의 initSID.ora에 다음을 추가한다.
db_file_standby_name_convert=("/oracle/app/oradata/ORA7",
"/home1/oradata/ORA7")
log_file_standby_name_convert=("/oracle/app/oradata/ORA7",
"/home1/oradata/ORA7")

3. primary site fail로 standby site를 primary로 변경
svrmgr> alter database activate standby database; ß-- primary db로 변경
svrmgr> alter database mount
svrmgr> alter database open;

4. primary site에서 물리적구조 변경시 standby site에서 할일
1) primary site에서 datafile add한경우
prim_host
svrmgr.>create tablespace aaa datafile '/oracle/oradata/ORA7/aaa.dbf'
size 10M;
stnd_host
svrmgr> alter database create datafile '/oracle/oradata/ORA7/aaa.dbf';
2) primary site에서 datafile drop한경우
prim_host
svrmgr.>drop tablespace aaa including contents;
stnd_host
svrmgr> alter database datafile '/oracle/oradata/ORA7/aaa.dbf' offline drop;
svrmgr>!rm /oracle/oradata/ORA7/aaa.dbf
svrmgr> recover database;
3) 그외의 DML,DDL,DCL은 No Action

5. primary db의 archive log file을 standby db쪽으로 자동 적용하는 script(stby.ksh : standby site에서 수행한다)
1) oracle login user의 환경정의file (.profile or.cshrc)에 다음을 추가
(stby.ksh을 놓을 위치)
STB_HOME=/oracle/app/stb/test
2) stby.ksh구동을 위한 directory만들기
mkdir $STB_HOME/log
mkdir $STB_HOME/tmp
3) standby site쪽의 tnsnames.ora에 primary db쪽 정의
DB_CONNECT=ORA7 ß- tnsnames.ora에 정의된 alias name
4) archive log file자동적용script

#! /bin/ksh
###############################################
# #
# Log_Archive_Format = log%t_%s.log #
# #
###############################################
# #
# 변수 초기화 #
# - Log_Archive_Format = log%t_%s.log #
# #
# STB_HOME=$HOME/stand_by #
###############################################
STB_HOME=/oracle/app/stb/test
LOG_STB_DIR=$STB_HOME/log
TMP_STB_DIR=$STB_HOME/tmp

PRIMARY_HOST=prim_host
STB_HOST=stnd_host

INTERVAL=60

LOG_ARCHIVE_DEST=$ORACLE_BASE/admin/$ORACLE_SID/arch
EXT_NAME=*.log
ARCHIVE_FILES=$LOG_ARCHIVE_DEST/$EXT_NAME

DB_CONNECT=ORA7

STANDBY_OUT=$LOG_STB_DIR/standby.out
STANDBY_LOG=$LOG_STB_DIR/standby.log
PRIMARY_OUT=$LOG_STB_DIR/primary.out
PRIMARY_LOG=$LOG_STB_DIR/primary.log
SVRMGR_OUT=$LOG_STB_DIR/srvrmgr.out
SVRMGR_LOG=$LOG_STB_DIR/srvrmgr.log

###################################################
# #
# help() #
# #
# Input: #
# None: #
# #
###################################################
help ()
{
cat < Usage) stby.ksh [-t interval] [-h]

-t interval: sleeping time interval (default = 60 seconds)
-h: help for commands

EOF
exit
}
###################################################
# error(err_code, ft_name, stmt) #
# #
# Input: #
# err_code: error code #
# ft_name: error가 발생한 함수 #
# stmt:error가 발생한 함수내에서 수행된 문장 #
# Process: #
# shell program을 종료 #
###################################################
error ()
{
prompt
prompt "ERROR: NameOf(function) = $2 ()"
prompt "\tStatement : $3"
prompt "\tReturn Code = $1"
prompt

exit 1
}
###############################################
# #
# prompt(text) #
# #
# Input: #
# text: text to display #
# Process: #
# #
################################################
prompt ()
{
if (( $# == 0 ))
then
echo
else
echo "STB> $*"
fi
}

###############################################
# #
# parse_command() #
# Input: #
# $* #
# #
################################################
parse_command ()
{
typeset option_flag


(( option_flag = 0 ))
for arg in $*
do
case $arg in
"-h")
help
;;
"-t")
(( option_flag = 1 ))
;;
-*)
echo "\t$arg is an illegal option"
help
;;
*)
if (( option_flag == 0 ))
then
echo "Unknown option: $arg"
help
fi
let INTERVAL=$arg
(( option_flag = 0 ))
;;
esac
done

if (( option_flag == 1 ))
then
echo "Expected interval"
help
fi
}

#################################################### #
# init_standby() #
# Input: #
# None #
# #
################################################
init_standby ()
{
PS1="STAND-BY> "
# Check if directory exists
if [[ ! (-d $LOG_STB_DIR) ]]
then
'mkdir' $LOG_STB_DIR
fi
if [[ ! (-d $TMP_STB_DIR) ]]
then
'mkdir' $TMP_STB_DIR
fi

# Check if Standby DB Instance is started
is_staddbyDB
NO_SRVR_PRROCESS=$?
if (( NO_SRVR_PRROCESS < 6 ))
then
start_staddbyDB
fi
}

###############################################
# #
# is_staddbyDB() #
# #
# Input: #
# None #
# Process: #
# Return: #
# Number of Standby Server Processes #
# #
################################################
is_staddbyDB ()
{
typeset -i no_srvr_process

# Check if Standby DB Instance is started
no_srvr_process=`ps -ef|grep ora_.*$ORACLE_SID|grep -v grep|wc -l`

return $no_srvr_process
}

###############################################
# #
# start_staddbyDB() #
# #
# Input: #
# None #
# Process: #
# #
################################################
start_staddbyDB ()
{
typeset -i no_srvr_process

svrmgrl <$SVRMGR_OUT
connect internal
spool $SVRMGR_LOG
startup nomount;
alter database mount standby database;
exit
EOF

is_staddbyDB
no_srvr_process=$?
prompt "no_srvr_process = $no_srvr_process"
if (( no_srvr_process < 6 ))
then
prompt "Can't START Standby DB !"
error $no_srvr_process start_staddbyDB svrmgrl
else
prompt "Standby DB is started now !"
fi

return 1
}

###############################################
# #
# get_standby_maxseq() #
# #
# Input: #
# None #
# Process: #
# Return: #
# Stand-by DB의 최대 archive log sequence #
###############################################

get_standby_maxseq ()
{
# Local 변수:
typeset -i max_seq

# 초기화
(( max_seq = 0 ))
(( stb_maxseq = 0 ))

# Directory가 존재하는가 ?
if [[ ! (-d $LOG_ARCHIVE_DEST) ]]
then
prompt "Error : $LOG_ARCHIVE_DEST NOT exists !"
error $? get_standby_maxseq "if [[ ! (-d $LOG_ARCHIVE_DEST) ]]"
fi

# 화일이 존재하는가 ?
if (( `ls $ARCHIVE_FILES|wc -l` < 1 ))
then
prompt "Error : $ARCHIVE_FILES NOT exists !"
svrmgrl <$STANDBY_OUT
connect internal
spool $STANDBY_LOG
SELECT nvl(max(sequence#), 0) as seq
FROM v\$log
WHERE archived = 'YES';
spool off
exit
EOF
# max(archive_log_sequence)를 가져온다.
max_seq=`grep -E "^[ ]*[0-9][0-9]*$" $STANDBY_LOG`
else
# max(archive_log_sequence)를 가져온다.
# Format : log%t_%s.log
max_seq=`ls $ARCHIVE_FILES|cut -f2 -d'_'|cut -f1 -d'.'|sort -nr|head -1`
fi
stb_maxseq=$max_seq
return stb_maxseq
}

###############################################
# #
# get_primary_maxseq() #
# #
# Input: #
# None #
# Process: #
# Return: #
# Primary DB의 최대 archive log sequence #
# #
#################################################
get_primary_maxseq ()
{
# Local 변수:
typeset -i max_seq

# 초기화
(( max_seq = 0 ))
(( prm_maxseq = 0 ))

sqlplus -s system/manager@$DB_CONNECT <$PRIMARY_OUT
set pages 0
spool $PRIMARY_LOG
SELECT nvl(max(sequence#), 0) as seq
FROM v\$log
WHERE archived = 'YES';
spool off
exit
EOF

# max(archive_log_sequence)를 가져온다.
max_seq=`grep -E "^[ ]*[0-9][0-9]*[ ]*$" $PRIMARY_LOG`
prm_maxseq=$max_seq
return $prm_maxseq
}

###############################################
# #
# copy_arch(from_seq_no, to_seq_no) #
# #
# Input: #
# from_seq_no: 복사할 archive log file의 start no #
# to_seq_no: 복사할 archive log file의 last no #
# Process: #
# Return: #
# 복사한 archive log files의 수 #
# #
################################################
copy_arch ()
{
typeset -i from_seq
typeset -i to_seq
typeset -i i
typeset -i rc
typeset -i cnt
typeset filename

# 초기화
(( from_seq = 0 ))
(( to_seq = 0 ))

(( from_seq = $1 )) # max(sequence) of stand-by server
(( to_seq = $2 )) # max(sequence) of primary server


(( i = from_seq ))
while (( i <= to_seq ))
do
filename=$LOG_ARCHIVE_DEST/\*_$i.log

# rcp를 이용하여 Remote의 archive 화일을 복사해온다.
prompt "Coping $filename..."
rcp $PRIMARY_HOST:$filename $LOG_ARCHIVE_DEST
rc=$?
if (( rc != 0 ))
then
prompt "Error : Can't copy !"
error $rc copy_arch "rcp $PRIMARY_HOST:$filename $LOG_ARCHIVE_DEST"
fi

prompt "rcp: return code = $rc"
`chmod g+w $filename`
(( i = i + 1 ))
done

(( cnt = to_seq - from_seq + 1 ))
return $cnt
}

################################################
# #
# apply_arch() #
# #
# Input: #
# None #
# Process: #
# Stand-by DB에 archives을 적용 #
# Return: #
# #
################################################
apply_arch ()
{
# Archive화일을 이용하여 recovery를 수행한다.
prompt "Applying Archive Log Files..."
prompt
svrmgrl <$SVRMGR_OUT
connect internal
recover standby database;
auto
cancel
exit
EOF

return 1
}
###############################################
# #
# main() #
# #
# Input: #
# None #
# Process: #
# Return: #
# Stand-by DB의 최대 archive log sequence #
# #
################################################

parse_command $*
init_standby
#################################################
# #
# Main Loop for Recovery #
# #
##################################################
while (( 1 ))
do
# Local의 stand-by의 최대 archive log sequence를 가져온다.
prompt "Getting max archive log sequence for STANDBY DB..."
get_standby_maxseq
stb_maxseq=$?
prompt

# Remote의 primary의 최대 log sequence를 가져온다.
prompt "Getting max archive log sequence for PRIMARY DB..."
get_primary_maxseq
prm_maxseq=$?
prompt

# print stb_maxseq, prm_maxseq
prompt "MaxSeq(StandbyDB) = $stb_maxseq, MaxSeq(PrimaryDB) = $prm_maxseq"
prompt

# archive될 화일들을 복사한다.
if (( stb_maxseq < prm_maxseq ))
then
copy_arch $stb_maxseq+1 $prm_maxseq
no_files=$?
prompt "$no_files 개의 화일을 복사했습니다."
prompt
apply_arch
else
prompt "Archive될 화일이 하나도 없습니다."
prompt
fi

sleep $INTERVAL
done