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

2010.05.12 08:48

조인상 조회 수:100758

원문 : 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

 

 

 

 

 

번호 제목 글쓴이 날짜 조회 수
75 10g ORA-32004 : obsolete and/or deprecated parameter(s) specified 조인상 2010.05.12 10739
74 centOS 5.2 에 오라클 10g 설치 조인상 2010.05.12 109915
73 히든 파라미터 확인하는 쿼리 조인상 2010.05.12 62419
72 오라클 복구 시나리오 조인상 2010.05.12 16648
71 골든 5.7 버전 데모 file 조인상 2010.05.12 7846
» 오라클 온라인백업 스크립트 (ksh 용) [1] file 조인상 2010.05.12 100758
69 [9i feature] DB_CACHE_ADVICE / V$DB_CACHE_ADVICE 에 대해서 조인상 2010.05.12 8068
68 exp error - ORA-19206: Invalid value for query or REF CURSOR parameter 조인상 2010.05.12 156614
67 exp실행시 에러. ORA-06512 SYS.DBMS_METADATA_INT 조인상 2010.05.12 32633
66 OPN 가입시의 장점 file 조인상 2010.05.12 6705
65 오라클에 접속하는 최대 인스턴스개수(세션개수)를 늘리는 방법 조인상 2010.05.12 24616
64 Red Hat Enterprise Linux AS release 3 (Taroon) 기반에서 오라클 9.2.0 설치하기 조인상 2010.05.12 8063
63 Unix 에서 Raw Device 사용법 조인상 2010.05.12 14090
62 오라클 10g OCP 라이센스 업그레이드 정보 조인상 2010.05.12 7500
61 .net 과 오라클 연동에 대하여 조인상 2010.05.12 10321
60 [장애처리] 메모리 풀로 인한 OS hang-up 조인상 2010.05.12 9761
59 [설치장애기] 64비트 windows 2003에 오라클 10.2.0.1 32비트 설치시 ORA-12564에러 조인상 2010.05.12 16499
58 Redhat Linux 8.0 에 Oracle 9i install 조인상 2010.05.12 7601
57 레드햇리눅스9에 오라클9i설치중 hang 조인상 2010.05.12 8774
56 OS의 파일사이즈가 2GB로 제한될때 split으로 나누기 조인상 2010.05.12 6174
서버에 요청 중입니다. 잠시만 기다려 주십시오...