HighLight in your life~

-- 실전 오라클 백업과 복구(생능출판사, 서진수 지음), p.224~p.229
-- 일자별로 자동으로 begin backup을 수행하는 백업 스크립트
-- 로그 확인: /u01/app/oracle/total.log
-- 실행: sh main_backup.sh
-- main_backup.sh를 crontab에 등록해 두면 아주 쉽게 주기적으로 백업을 수행 할 수 있습니다.

-- 1. main_backup.sh

-- 2. begin_backup.sh

-- 3. copy_backup.sh

-- 4. end_backup.sh

-- 5. status.sh

-- 1. main_backup.sh
export LANG=C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

touch /u01/app/oracle/total.log
echo ""
echo "set begin backup mode~~"
time sh /u01/app/oracle/begin_backup.sh >> /u01/app/oracle/total.log
echo ""
echo "end begin backup mode~~"
echo ""
echo "start file copy......................"
time sh /u01/app/oracle/copy_backup.sh >> /u01/app/oracle/total.log
echo "end file copy~"
echo ""
echo "set end backup mode~~"
time sh /u01/app/oracle/end_backup.sh >> /u01/app/oracle/total.log
echo "complete hot backup~!"

-- 2. begin_backup.sh
-- 테이블스페이스를 조회해서 begin backup 상태로 만드는 script입니다.
export LANG=C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
sqlplus /nolog <<EOF1
conn / as sysdba

set head off
set feedback off
set time off
set timing off
set echo off
spool /tmp/online.tmp
select 'alter tablespace '|| tablespace_name ||' begin backup;' \
from dba_tablespaces \
where status='ONLINE'
and contents != 'TEMPORARY';
spool off
!cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > /u01/app/oracle/begin.sh
@/u01/app/oracle/begin.sh
!sh /u01/app/oracle/status.sh
exit
EOF1

-- 3. copy_backup.sh
-- 일자별로 디렉토리를 만들어 파일을 복사하는 script입니다.
export LANG=C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

sqlplus /nolog << EOF3
conn / as sysdba
set head off
set time off
set timing off
set feedback off
set echo off
set line 200
col name for a100
spool /u01/app/oracle/cp.tmp
select 'mkdir /data/backup/open/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') from dual;
select 'cp -av '||name||' /data/backup/open/'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') "name" from v\$datafile;
spool off

spool /u01/app/oracle/control.tmp
alter session set nls_date_format='YYYY-MM-DD-HH24-MI-SS';
select 'alter database backup controlfile to ' '/data/backup/open/'||sysdate||\
       '\'||sysdate||'.ctl' ' ; ' from dual;
spool off

!cat /u01/app/oracle/cp.tmp | egrep -v SQL > /u01/app/oracle/cp.sh
!cat /u01/app/oracle/control.tmp | egrep -v SQL > /u01/app/oracle/control.sql

!sh /u01/app/oracle/cp.sh
@/u01/app/oracle/control.sql
exit
EOF3

-- 4. end_backup.sh
-- 백업이 끝나고 end backup으로 변경하는 script입니다.
export LANG=C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

sqlplus /nolog << EOF4
conn / as sysdba

set head off
set feedback off
set time off
set timing off
set echo off
spool /tmp/online.tmp
select 'alter tablespace '|| tablespace_name ||' end backup;'\
from dba_tablespaces \
where status='ONLINE' \
and contents != 'TEMPORARY';
spool off
!cat /tmp/online.tmp | egrep -v spool | egrep -v SQL | egrep -v [2-4] > /u01/app/oracle/end.sh
@/u01/app/oracle/end.sh
!sh /u01/app/oracle/status.sh
exit
EOF4

-- 5. status.sh
-- begin backup 모드 상태를 보는 script입니다.
export LANG=C
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

sqlplus /nolog << EOF2
conn / as sysdba

set head on
set echo off
set feedback off
spool /tmp/status.tmp
set line 200
col name for a50
col status for a15
select a.file#, a.name, b.status, to_char(b.time, 'YYYY-MM-DD:HH24:Mi:SS') "Time" \
from v\$datafile a, v\$backup b \
where a.file#=b.file#;
spool off
exit
EOF2