일자별 자동으로 백업수행 스크립트

main_backup.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

touch /home/oracle/total.log

echo ""

echo "set begin backup mode~~"

time sh /home/oracle/begin_backup.sh >> /home/oracle/total.log

echo ""

echo "end begin backup mode~~"

echo ""

echo "start file copy........................."

time sh /home/oracle/copy_backup.sh >> /home/oracle/total.log

echo ""

echo "end file copy~~"

echo ""

echo "set end backup mode~~"

time sh /home/oracle/end_backup.sh >> /home/oracle/total.log

echo ""

echo "complete hot backup~!"

begin_backup.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

sqlplus /nolog << E0F1

conn sys/sys 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] > /home/oracle/begin.sh

@/home/oracle/begin.sh

!sh /home/oracle/status.sh

exit

E0F1

copy_backup.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

sqlplus /nolog << E0F3

conn sys/sys as sysdba

set head off

set feedback off

set time off

set timing off

set echo off

set line 200

col name for a100

spool /home/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 /home/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 /home/oracle/cp.tmp | egrep -v SQL > /home/oracle/cp.sh

!cat /home/oracle/control.tmp | egrep -v SQL > /home/oracle/control.sql

!sh /home/oracle/cp.sh

@/home/oracle/control.sql

exit

E0F3

end_backup.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

sqlplus /nolog << E0F4

conn sys/sys 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] > /home/oracle/end.sh

@/home/oracle/end.sh

!sh /home/oracle/status.sh

exit

E0F4

status.sh

export LANG=C

export ORACLE_BASE=/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11g

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_SID=testdb

sqlplus /nolog << E0F2

conn sys/sys as sysdba

set head off

set feedback off

set echo 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

E0F2

실행

$ sh main_backup.sh

※ 하기전에!

vi /home/oracle/login.sql

에서 set sqlprompt "_USER>" 이 설정을

set sqlprompt "SQL>"

로 바꾸고 실행시켜야 함...

없으면 패스.

[실습]

주기적으로 백업을 수행하고 싶으면 

main_backup.sh를 crontab에 등록해 주면 됨

관련 데몬(서비스)는 crond

/etc/crontab

수정방법은 crontab -e 또는 vi 로 수정 가능

Backup_script.txt