스토리지 BCV 환경 Split 을 이용한 Oracle DB Hot backup 스크립트 입니다.
begin_backup -> BCV, split -> end_backup 순으로 진행 합니다.
BEGIN BACKUP 부문
begin_scr.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
set pause off SET HEADING OFF SET SERVEROUTPUT OFF SET LINESIZE 500 SET PAGESIZE 10000 SET SPACE 0 SET FEEDBACK OFF SET TRIMSPOOL ON SET TERM OFF SET TIMING OFF SPOOL /tmp/begin_bk_exec.sql select 'alter database backup controlfile to trace;'||chr(10) from dual; select 'alter system switch logfile;'||chr(10) from dual ; select 'alter system switch logfile;'||chr(10) from dual ; select 'alter system switch logfile;'||chr(10) from dual ; SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' BEGIN BACKUP;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT LIKE '%TEMP%'; SPOOL OFF |
begin_backup.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
#!/bin/ksh ########################################################################### # haisins begin_backup.sh # # # # ORACLE LOG SWITCH SYNTAX # # (*) Last Archived file on BCV devices before DG2(Archived FS) split # # Single DB : alter system switch logfile; /* 1 Redo log switch */ # # RAC : alter system archive log current; /* 2 Redo log switch */ # # # ########################################################################### export ORACLE_BASE=/oracle export ORACLE_HOME=$ORACLE_BASE/product/102 export ORA_CRS_HOME=$ORACLE_BASE/crs export ORACLE_TERM=vt100 export ORACLE_OWNER=oraORADB export ORACLE_SID=ORADB1 export TEMP=$ORACLE_BASE/tmp export TMPDIR=$ORACLE_BASE/tmp export DATA_PUMP_DIR=/oracle export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data export TNS_ADMIN=$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/lib64:$ORACLE_HOME/network/lib:/usr/lib export DISPLAY=1.1.1.1:0.0 export NLS_LANG=American_america.KO16KSC5601 export PATH=$ORA_CRS_HOME/bin:$ORACLE_HOME/bin:/bin:/usr/bin:/sbin:/usr/ccs/bin:.:$PATH:$ORACLE_HOME/OPatch export AIXTHREAD_SCOPE=S export LOG_DIR="/opt/emcscripts/BCV/log" export DATE_TIME=`date +%Y%m%d%H%M` export LOG_FILE="${LOG_DIR}/${DATE_TIME}.oracle.beginbackup.log" # Check for the status of log directory touch ${LOG_DIR}/ok.tmp if [ $? -ne 0 ] then echo "\nBegin Backup cannot be started. Check the Directory: ${LOG_DIR}\n" return 1 fi # Make sure all the tablespace is not in the backup mode sqlplus -s "/ as sysdba" <<AAA > /dev/null spool ${LOG_DIR}/bk1.tmp select decode(count(*),0,'None','SomeInBackupMode') as BackupInProgress from v\$backup where status = 'ACTIVE'; spool off AAA # Check if the sqlplus command succeeded if [ $? -ne 0 ] then echo "\nERROR: SQLPLUS Failed!!!\n" return 1 fi # Log error if some tablespaces are already in Backup mode grep SomeInBackupMode ${LOG_DIR}/bk1.tmp > /dev/null if [ $? -eq 0 ] then sqlplus -s "/ as sysdba" <<BBB > /dev/null spool ${LOG_DIR}/${DATE_TIME}.SomeInBackupMode.err select distinct a.tablespace_name, b.file#, b.status, to_char(b.time, 'YYYY/MM/DD HH24:MI:SS') as time from dba_data_files a, v\$backup b where a.file_id = b.file# and b.status = 'ACTIVE'; spool off BBB echo "\nSome tablespaces are already in Backup Mode Before BEGIN BACKUP!!!\n" return 1 fi # Begin Backup Script sqlplus -s "/ as sysdba" <<CCC >> ${LOG_FILE} alter system archive log current; spool ${LOG_DIR}/begin_backup.tmp select distinct 'alter tablespace '|| a.tablespace_name ||' begin backup;' from dba_data_files a, v\$backup b where a.file_id = b.file# and b.status='NOT ACTIVE'; spool off !grep "^alter" ${LOG_DIR}/begin_backup.tmp > ${LOG_DIR}/begin_backup.sql @${LOG_DIR}/begin_backup.sql CCC |
END BACKUP 부문
end_scr.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
set pause off SET HEADING OFF SET SERVEROUTPUT OFF SET LINESIZE 500 SET PAGESIZE 10000 SET SPACE 0 SET FEEDBACK OFF SET TRIMSPOOL ON SET TERM OFF SET TIMING OFF SPOOL /tmp/end_bk_exec.sql SELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' END BACKUP;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT LIKE '%TEMP%'; select 'alter database backup controlfile to trace;'||chr(10) from dual; select 'alter system switch logfile;'||chr(10) from dual ; select 'alter system switch logfile;'||chr(10) from dual ; select 'alter system switch logfile;'||chr(10) from dual ; SPOOL OFF |
end_backup.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
#!/bin/ksh ########################################################################### # haisins end_backup.sh # # # # ORACLE LOG SWITCH SYNTAX # # (*) Last Archived file on BCV devices before DG2(Archived FS) split # # Single DB : alter system switch logfile; /* 1 Redo log switch */ # # RAC : alter system archive log current; /* 2 Redo log switch */ # # # ########################################################################### export ORACLE_BASE=/oracle export ORACLE_HOME=$ORACLE_BASE/product/102 export ORA_CRS_HOME=$ORACLE_BASE/crs export ORACLE_TERM=vt100 export ORACLE_OWNER=oraORADB export ORACLE_SID=ORADB1 export TEMP=$ORACLE_BASE/tmp export TMPDIR=$ORACLE_BASE/tmp export DATA_PUMP_DIR=/oracle export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data export TNS_ADMIN=$ORACLE_HOME/network/admin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/lib64:$ORACLE_HOME/network/lib:/usr/lib export DISPLAY=172.18.185.36:0.0 export NLS_LANG=American_america.KO16KSC5601 export PATH=$ORA_CRS_HOME/bin:$ORACLE_HOME/bin:/bin:/usr/bin:/sbin:/usr/ccs/bin:.:$PATH:$ORACLE_HOME/OPatch export AIXTHREAD_SCOPE=S export LOG_DIR="/opt/emcscripts/BCV/log" export DATE_TIME=`date +%Y%m%d%H%M` export LOG_FILE="${LOG_DIR}/${DATE_TIME}.oracle.endbackup.log" sqlplus -s "/ as sysdba" <<ABC >> ${LOG_FILE} #alter system switch logfile; alter system archive log current; spool ${LOG_DIR}/end_backup.tmp select distinct 'alter tablespace '|| a.tablespace_name ||' end backup;' from dba_data_files a, v\$backup b where a.file_id = b.file# and b.status='ACTIVE'; spool off !grep "^alter" ${LOG_DIR}/end_backup.tmp > ${LOG_DIR}/end_backup.sql @${LOG_DIR}/end_backup.sql spool ${LOG_DIR}/bk2.tmp select decode(count(*),0,'None','SomeInBackupMode') as BackupInProgress from v\$backup where status = 'ACTIVE'; spool off alter database backup controlfile to trace; alter database backup controlfile to '${LOG_DIR}/${DATE_TIME}.control.backup' reuse; #alter system switch logfile; alter system archive log current; select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') from dual; ABC if [ $? -ne 0 ] then echo "\nERROR: SQLPLUS Failed!!!\n" return 1 fi grep SomeInBackupMode ${LOG_DIR}/bk2.tmp > /dev/null if [ $? -eq 0 ] then sqlplus -s "/ as sysdba" <<EOF > /dev/null spool ${LOG_DIR}/${DATE_TIME}.StillInBackupMode.err select distinct a.tablespace_name, b.file#, b.status, to_char(b.time, 'YYYY/MM/DD HH24:MI:SS') as time from dba_data_files a, v\$backup b where a.file_id = b.file# and b.status = 'ACTIVE'; spool off EOF echo "\nERROR: Some Tablespaces are still in Backup Mode After END BACKUP!!!\n" return 1 fi rm ${LOG_DIR}/ok.tmp ${LOG_DIR}/bk1.tmp ${LOG_DIR}/bk2.tmp ${LOG_DIR}/begin_backup.tmp ${LOG_DIR}/end_backup.tmp ${LOG_DIR}/begin_backup.sql ${LOG_DIR}/end_backup.sql |