오라클 온라인백업 스크립트 (ksh 용)

2010.05.12 08:48

조인상 조회 수:109156

원문 : http://www.ischo.net -- 조인상 // 시스템 엔지니어

Writer : http://www.ischo.net -- ischo // System Engineer in Replubic Of Korea

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

#!/bin/ksh

############################################
# Programmed by Cho in-sang  (c)DSN    #
# chosim1@naver.com  http://www.ischo.net    #
#
# ORACLE ONLINE BACKUP SCRIPT          #
############################################

 


############################################
#  ENVIRONMENT SETTING              #
############################################

# 1. ORACLE SID #########################################
OSID=ORCL
export ORACLE_SID=${OSID}

# 2. BACKUP DESTINATION DIRECTORY #######################
DEST=/DBMS/PSFA/test
DEST_DF=${DEST}/datafile/${OSID}
DEST_CF=${DEST}/control/${OSID}
DEST_ARC=${DEST}/arch/${OSID}


# 3. ARCHIVE LOG BACKUP MODE ############################
# archived log backup enable
ARCenable=yes

# archived log retention
ARCretention=5

########################################################
################ END OF ENV SETTING ####################
########################################################

########################################################
# Make Directory if not exist
########################################################

if [ ! -d ${DEST_DF} ];
then
        mkdir -p ${DEST_DF}
fi

if [ ! -d ${DEST_CF} ];
then
        mkdir -p ${DEST_CF}
fi

if [ ! -d ${DEST_ARC} ];
then
        mkdir -p ${DEST_ARC}
fi

########################################################
########################################################

rm -rf $DEST/*lst*
rm -rf $DEST/*.sql
rm -rf $DEST/datafile.sh


############################################
#  TRIMMING BACKUP FILES            #
############################################


# export Archived-log PATH name
function Func_TrimArchLog
{
cd ${DEST}
sqlplus '/ as sysdba' << !

spool arc.lst
archive log list;
spool off

!

ARCPATH=`cat ${DEST}/arc.lst | grep "Archive destination" | grep -v grep | awk '{print $3}'`
}

 

# Function - export and trim tablespaces and datafiles
function Func_TrimTSandDF
{

cd ${DEST}
sqlplus '/ as sysdba' << !

spool begin_ts.lst
select distinct 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_data_files;
spool off

spool end_ts.lst
select distinct 'alter tablespace ' || tablespace_name || ' end backup;' from dba_data_files;
spool off


spool datafile.lst
select 'FILENAME: ' || file_name from dba_data_files;
spool off


!

# TRIM TABLESPACE LIST
cat ${DEST}/begin_ts.lst | grep alter | grep -v SQL > ${DEST}/beginbackup.sql
cat ${DEST}/end_ts.lst | grep alter | grep -v SQL > ${DEST}/endbackup.sql


# TRIM DATAFILE LIST
cat ${DEST}/datafile.lst | grep "FILENAME:" | grep -v SQL | grep -v FILE_NAME | awk '{print $2}' > ${DEST}/datafile.lst2
for DATAFILES in `cat ${DEST}/datafile.lst2`
do


        TARGET_DIR=`echo ${DATAFILES%/*}`
        TARGET_FILE=`echo ${DATAFILES##/*/}`

        if [ ! -d ${DEST_DF}/${TARGET_DIR} ];
        then
                mkdir -p ${DEST_DF}/${TARGET_DIR}
        fi

        echo "cp -p ${DATAFILES} ${DEST_DF}/${TARGET_DIR}" >> ${DEST}/datafile.sh
done

}


# make controlfile backup shell
function Func_TrimControlFile
{
rm ${DEST_CF}/control01.ctl
rm ${DEST_CF}/control.trc
echo "alter database backup controlfile to '${DEST_CF}/control01.ctl';" > ${DEST}/control.sql
echo "alter database backup controlfile to trace as '${DEST_CF}/control.trc';" >> ${DEST}/control.sql
}


# DATAFILE,CONTROLFILE BACKUP
function Func_BackupStart
{
cd ${DEST}

sqlplus '/ as sysdba' << !

alter system switch logfile;

@beginbackup.sql

!/bin/ksh datafile.sh

@endbackup.sql

@control.sql

alter system switch logfile;

!
}

# Archived log backup & delete
function Func_BackupArc
{
if [ ${ARCenable} = yes ]
then
        find ${ARCPATH} -type f -mtime +${ARCretention} -exec rm {} \;
        cd ${ARCPATH}
        tar cf - . | (cd ${DEST_ARC};tar xf -)
        find ${DEST_ARC} -type f -mtime +${ARCretention} -exec rm {} \;
fi
}

###############################################################################
###############################################################################

 

###############################################################################
# Backup Start
###############################################################################

# 1. Trim Archived-log Path Name
Func_TrimArchLog

# 2. Trim TableSpace,DataFile Names and make backup script
Func_TrimTSandDF
Func_TrimControlFile

# 3. Start Oracle Online Backup(datafile,controlfile)
Func_BackupStart

# 4. Backup Archived-log files and delete old files.
Func_BackupArc

 

 

 

 

 

번호 제목 글쓴이 날짜 조회 수
115 온라인 백업/리스토어 (imp/exp) 조인상 2010.05.09 43621
114 아카이브 모드로 변환하기 조인상 2010.05.12 16425
113 OS재설치 후 오라클 복구(솔라리스) 조인상 2010.05.12 10359
112 백업에 관한 조언 조인상 2010.05.12 8781
111 필수로 암기해야 할 view 들... 조인상 2010.05.12 9231
110 오라클 뷰들... 조인상 2010.05.12 9492
109 9i 에서의 언두세그먼트 관리 조인상 2010.05.12 11011
108 오라클 재시작시에 롤백세그먼트 OFFLINE 조인상 2010.05.12 9475
107 오라클 버전업 후 exp할때 ORA-00904에러 조인상 2010.05.12 14088
106 아카이브 로그 강제로 생성하는 명령어 조인상 2010.05.12 14729
105 오라클 패치 후 export가 안된다! [1] 조인상 2010.05.12 14517
104 control file 재생성하기 조인상 2010.05.12 15349
103 온라인 백업방법 begin~end backup 조인상 2010.05.12 22780
102 64비트 Solaris에 오라클 9.2.0 설치 하기 조인상 2010.05.12 14097
101 처음 오라클 설치시 SGA 영역크기 정하는 규칙 조인상 2010.05.12 12067
100 오라클에 접속하는 최대 인스턴스개수(세션개수)를 늘리는 방법 조인상 2010.05.12 10772
99 인텔 펜티엄4 기반 Windows에 설치시 오류에 대해서 조인상 2010.05.12 8710
98 오라클 8.1.7 -> 7.3.4 로 DB data 이전 조인상 2010.05.12 10491
97 캐릭터셋 변경방법 조인상 2010.05.12 11101
96 [기타] 디비튜닝에 대한 질문과답변내용... 조인상 2010.05.12 9398
서버에 요청 중입니다. 잠시만 기다려 주십시오...