[Oracle]백업스크립트

일전에 만들어 놓고 테스트 까지 완료 하였던 기억은 있으나, 

혹시 보르니 사용 하실 분들은 테스트 및 쿼리문 확인후 사용 하시기 바랍니다. 

백업 스크립트

---

#!/bin/bash

echo ======== HOT BACKUP or Cold backup ======== 

while [ : ]

do 

echo -n "HOT BACKUP or Cold backup : "

read input 

echo $input 

case $input in 

coldbackup|COLDBACKUP) 

sqlplus "/ ***** " << EOF  //sqlplus 비밀번호 입력//

startup

set echo off

set term off

set head off

set feedback off

set termout off

set time off

set line 200

set feed off 

spool /home/oracle/clodbackup.log

select 'mkdir -p /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD') from dual;

select 'cp -av '||name||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD') from v\$controlfile;

select 'cp -av '||name||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD') from v\$datafile;

select 'cp -av '||member||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD') from v\$logfile;

select 'cp -av '||name||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD') from v\$controlfile;

select 'mkdir -p /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD')||'/arch'  from dual;

select 'cp -av '||name||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD')||'/arch' from v\$archived_log where first_time>=to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD');

select 'alter database backup controlfile to ''/home/oracle/backup/'||sysdate||'/'||sysdate||'.ctl'' ; ' from dual;

spool off

!cat /home/oracle/clodbackup.log | egrep -v SQL > /home/oracle/cp.sh

shutdown abort

!sh /home/oracle/cp.sh

quit

EOF

break

;;

HOTBACKUP|hotbackup)

sqlplus "/ ***** " << EOF  //sqlplus 비밀번호 입력//

startup

set echo off

set term off

set head off

set feedback off

set termout off

set time off

set line 200

set feed off

spool /home/oracle/hotbackup.log

select '!mkdir -p /home/oracle/backup/'||to_char(sysdate, 'YYYY-MM-DD:HH24:MI/') from dual ;

SELECT * 

  FROM (SELECT 1 gb#

             , ts#

             , 0 file#

             , 'alter tablespace ' || name || ' begin backup;' script

          FROM v\$tablespace

         UNION ALL

        SELECT 2 gb#

             , ts#

             , file#

             , '!cp ' || name || ' /home/oracle/backup/'||to_char(sysdate, 'YYYY-MM-DD:HH24:MI/') "name"

          FROM v\$datafile

         UNION ALL

        SELECT 3 gb#

             , ts#

             , 0 file#

             , 'alter tablespace ' || name || ' end backup;' script

          FROM v\$tablespace)

 ORDER BY ts#, gb#, file#;

select 'cp -av '||name||' /home/oracle/backup/'||to_char(sysdate,'YYYY-MM-DD')||'/arch' from v\$archived_log where first_time>=to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD');

spool off

spool /tmp/control2.tmp

alter session set nls_date_format='YYYY-MM-DD:HH24:MI' ;

select 'alter database backup controlfile to ''/home/oracle/backup/'||sysdate||'/'||sysdate||'.ctl'' ; ' from dual;

spool off

!cat /home/oracle/hotbackup.log | egrep -v SQL | egrep -v FROM | egrep -v , | egrep -v UNION | egrep -v gb# > /tmp/backup11.sql

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

@/tmp/backup11.sql

@/home/oracle/control2.sql

EOF

break

;;

*)

break;;

esac

done 

꼭!!! 다시한번 쿼리문 및 경로 확인 or 수정후 사용 바랍니다. 

감사합니다.