BCV 환경 Split 을 이용한 Oracle DB Hot backup 스크립트

haisins

7년 ago

스토리지 BCV 환경 Split 을 이용한 Oracle DB Hot backup 스크립트 입니다.

begin_backup -> BCV, split -> end_backup 순으로 진행 합니다.

BEGIN BACKUP 부문

begin_scr.sql

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

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 TABLESPACE '||TABLESPACE_NAME||' BEGIN BACKUP;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT LIKE '%TEMP%';

SPOOL OFF

begin_backup.sh

#!/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

#!/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

# 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

# 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

# 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

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

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 ;

SPOOL OFF

end_backup.sh

#!/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

#!/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

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

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