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