Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#!/bin/bash
# script: prod1_closed_db_backup_job_1.sh (renamed from 31_macosx_prod1_closed_db_backup_job_1.sh)
# Features: This shell script performs a closed database backup to the /Volumes/backup location.
# This script builds the closed database restore script when it builds
# the shell script for the backup.
# This script also backs up the init.ora, orapw, alert.log,
# backup and restore scripts and creates the prod1_disaster_recovery.txt file.
# Note: This script only needs scheduled if the same task has not
# been scheduled via a OEM job.
#
# Script Sequence#: 31
# Used By: CRON job via oracle UNIX account
# Usage:
# ******** Closed Oracle Database backup nightly at 4:03 ********
# 03 4 * * 0-6 /Volumes/u01/server_scripts/prod1_closed_db_backup_job_1.sh
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 10-7-2001 dsimpson Initial Release
# 05-10-2002 dsimpson Updated to use oracle UNIX account.
# 10-14-2002 dsimpson Added backup of vfstab file on Solaris.
# This output file was created by Installgen version 1.38 on Sat Nov 23 07:13:32 2002. By .com Solutions Inc. www.dotcomsolutionsinc.net
# define globally used paths/variables
PATH=/bin:/sbin:/usr/bin:/usr/sbin:.
ORACLE_BASE=/Users/oracle/v920
ORACLE_HOME=/Users/oracle/v920
ORACLE_OWNER=oracle
ORACLE_OWNER_PATH=/Users/oracle
ORACLE_GROUP=dba
PATH=$PATH:$ORACLE_HOME/bin
SRCHOME=$ORACLE_HOME
ORACLE_SID=prod1
CLASSPATH=$ORACLE_HOME/DBCreate/oradev/classes/orapts.jar:$ORACLE_HOME/jdbc/lib/classes12.zip:$CLASSPATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$DYLD_LIBRARY_PATH
ORAENV_ASK=NO
TNS_ADMIN=$ORACLE_HOME/network/admin
TERM=vt100
NLS_LANG=AMERICAN_AMERICA.US7ASCII
export PATH ORACLE_BASE ORACLE_HOME SRCHOME ORACLE_SID CLASSPATH LD_LIBRARY_PATH DYLD_LIBRARY_PATH ORACLE_OWNER TNS_ADMIN TERM NLS_LANG
# the following ulimit parameters are required by the Oracle database on Mac OS X
ulimit -c unlimited
ulimit -d unlimited
ulimit -s 65536
ulimit -u 500
ulimit -n 10000
# write the SQL file which obtains the list of datafiles for backup
exec 3>/Volumes/u01/server_scripts/prod1_closed_db_backup_files_get_1.sql
echo "-- script: prod1_closed_db_backup_files_get_1.sql">&3
echo "-- Features: This sql script obtains a list of files to be backed up. ">&3
echo "-- Output File: prod1_closed_db_backup_files_1.sh">&3
echo "--">&3
echo "-- Copyright 2002 by .com Solutions Inc.">&3
echo "--">&3
echo "-- ---------------------- Revision History ---------------">&3
echo "-- Date By Changes">&3
echo "-- 10-7-2001 dsimpson Initial Release">&3
echo " ">&3
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 150">&3
echo "spool /Volumes/u01/server_scripts/prod1_closed_db_backup_files_1.sh">&3
echo "select '#!/bin/bash ' from dual;">&3
echo "select '# script: prod1_closed_db_backup_files_1.sh ' from dual;">&3
echo "select '# Features: This shell script performs a closed database backup to' from dual;">&3
echo "select '# the /Volumes/backup location. ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# Used By: executed by prod1_closed_db_backup_job_1.sh ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# Copyright 2002 by .com Solutions Inc. ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# ---------------------- Revision History --------------- ' from dual;">&3
echo "select '# Date By Changes ' from dual;">&3
echo "select '# 10-7-2001 dsimpson Initial Release ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select 'ORACLE_BASE=/Users/oracle/v920' from dual;">&3
echo "select 'ORACLE_HOME=/Users/oracle/v920' from dual;">&3
echo "select 'ORACLE_OWNER=oracle' from dual;">&3
echo "select 'ORACLE_OWNER_PATH=/Users/oracle' from dual;">&3
echo "select 'ORACLE_GROUP=dba' from dual;">&3
echo "select 'PATH=/bin:/sbin:/usr/bin:/usr/sbin:.:/Users/oracle/v920/bin' from dual;">&3
echo "select 'SRCHOME=/Users/oracle/v920' from dual;">&3
echo "select 'ORACLE_SID=prod1' from dual;">&3
echo "select 'CLASSPATH=/Users/oracle/v920/DBCreate/oradev/classes/orapts.jar:/Users/oracle/v920/jdbc/lib/classes12.zip' from dual;">&3
echo "select 'LD_LIBRARY_PATH=/Users/oracle/v920/lib' from dual;">&3
echo "select 'DYLD_LIBRARY_PATH=/Users/oracle/v920/lib:/Users/oracle/v920/rdbms/lib' from dual;">&3
echo "select 'ORAENV_ASK=NO' from dual;">&3
echo "select 'TNS_ADMIN=/Users/oracle/v920/network/admin' from dual;">&3
echo "select 'TERM=vt100' from dual;">&3
echo "select 'NLS_LANG=AMERICAN_AMERICA.US7ASCII' from dual;">&3
echo "select 'USER=oracle ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select 'export USER PATH ORACLE_BASE ORACLE_HOME SRCHOME ORACLE_SID CLASSPATH LD_LIBRARY_PATH DYLD_LIBRARY_PATH ORACLE_OWNER TNS_ADMIN TERM NLS_LANG' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# the following ulimit parameters are required by the Oracle database on Mac OS X' from dual;">&3
echo "select 'ulimit -a # show all ulimit settings' from dual;">&3
echo "select 'ulimit -c unlimited # max size of core dump file is unlimited' from dual;">&3
echo "select 'ulimit -d unlimited # maximum data file size ' from dual;">&3
echo "select 'ulimit -s 65536 # set stacksize to 64Mb' from dual;">&3
echo "select 'ulimit -u 500 # max number of processes' from dual;">&3
echo "select 'ulimit -n 10000 # max number of open files' from dual;">&3
echo "select '#ulimit -Hv # list hard limit for virtual memory (unlimited)' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select 'cp '|| name ||' /Volumes/backup/prod1_'|| substr(name,instr(name,'/',-1,1)+1) from V\$DATAFILE">&3
echo "union">&3
echo "select 'cp ' || member || ' /Volumes/backup/prod1_' || substr(member,instr(member,'/',-1,1)+1) from V\$LOGFILE">&3
echo "union">&3
echo "select 'cp ' || name || ' /Volumes/backup/prod1_' || substr(name,instr(name,'/',-1,1)+1) from V\$CONTROLFILE;">&3
echo "select '# check each backed up datafile with DBVERIFY' from dual;">&3
echo "select '/Users/oracle/v920/bin/dbv file=/Volumes/backup/prod1_' || substr(name,instr(name,'/',-1,1)+1) || ' blocksize=16384 logfile=prod1_' || substr(name,instr(name,'/',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;">&3
echo "select 'mv prod1_' || substr(name,instr(name,'/',-1,1)+1) || '_dbverify.log' || ' /backup' from V\$DATAFILE;">&3
echo "select 'cat /Volumes/backup/prod1_' || substr(name,instr(name,'/',-1,1)+1) || '_dbverify.log' from V\$DATAFILE;">&3
echo "spool off">&3
# write the SQL file which obtains the list of datafiles for restore
exec 3>/Volumes/u01/server_scripts/prod1_closed_db_restore_files_get_1.sql
echo "-- script: prod1_closed_db_restore_files_get_1.sql">&3
echo "-- Features: This sql script obtains a list of files to be restored. ">&3
echo "-- Output File: prod1_closed_db_restore_files_1.sh">&3
echo "--">&3
echo "-- Copyright 2002 by .com Solutions Inc.">&3
echo "--">&3
echo "-- ---------------------- Revision History ---------------">&3
echo "-- Date By Changes">&3
echo "-- 10-5-2001 dsimpson Initial Release">&3
echo " ">&3
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 150">&3
echo "spool /Volumes/u01/server_scripts/prod1_closed_db_restore_files_1.sh">&3
echo "select '#!/bin/bash ' from dual;">&3
echo "select '# script: prod1_closed_db_restore_files_1.sh ' from dual;">&3
echo "select '# Features: This shell script performs a closed database restore from' from dual;">&3
echo "select '# the /backup location. ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# Used By: executed manually ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# Copyright 2002 by .com Solutions Inc. ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select '# ---------------------- Revision History --------------- ' from dual;">&3
echo "select '# Date By Changes ' from dual;">&3
echo "select '# 10-5-2001 dsimpson Initial Release ' from dual;">&3
echo "select '# ' from dual;">&3
echo "select 'cp /Volumes/backup/prod1_' || substr(name,instr(name,'/',-1,1)+1) || ' ' || name from V\$DATAFILE">&3
echo "union">&3
echo "select 'cp /Volumes/backup/prod1_' || substr(member,instr(member,'/',-1,1)+1) || ' ' || member from V\$LOGFILE">&3
echo "union">&3
echo "select 'cp /Volumes/backup/prod1_' || substr(name,instr(name,'/',-1,1)+1) || ' ' || name from V\$CONTROLFILE;">&3
echo "spool off">&3
# write the SQL file which obtains the list of disaster recovery info
FORMAT_VAR=\'99,999,990.90\';
TS_FREE='SM$TS_FREE'
TS_AVAIL='SM$TS_AVAIL'
exec 3>/Volumes/u01/server_scripts/prod1_disaster_recovery_get.sql
echo "set echo off">&3
echo "set feedback off">&3
echo "set verify off">&3
echo "set pagesize 0">&3
echo "set linesize 100">&3
echo "spool /Volumes/backup/prod1_disaster_recovery.txt">&3
echo "select '-- file: prod1_disaster_recovery.txt ' from dual;">&3
echo "select '-- Features: This text file provides a listing of tablespaces' from dual;">&3
echo "select '-- data file names,sizes and user account info. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Used By: DBA for disaster recovery purposes ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- Copyright 2002 by .com Solutions Inc. ' from dual;">&3
echo "select '-- ' from dual;">&3
echo "select '-- ---------------------- Revision History --------------- ' from dual;">&3
echo "select '-- Date By Changes ' from dual;">&3
echo "select '-- 10-6-2001 dsimpson Initial Release ' from dual;">&3
echo "select ' ' from dual;">&3
echo "select 'Report produced on: ' || sysdate from dual;">&3
echo "select 'Database name: prod1' from dual;">&3
echo "select 'Database server hostname: g4' from dual;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || 'File' || CHR(9) || CHR(9) || CHR(9) || CHR(9) || 'Size (bytes)' from dual;">&3
echo "select tablespace_name || CHR(9) || CHR(9) || file_name || CHR(9) || CHR(9) || bytes from dba_data_files order by tablespace_name;">&3
echo "select ' ' from dual;">&3
echo "select 'Username' || CHR(9) || 'Status' || CHR(9) || 'Tablespace'|| CHR(9) || 'Temp' || CHR(9) || 'Created' from dual;">&3
echo "select username || CHR(9) || CHR(9) || account_status || CHR(9) || default_tablespace || CHR(9) || CHR(9) || temporary_tablespace || CHR(9) || created from dba_users order by username;">&3
echo "select ' ' from dual;">&3
echo "select 'Tablespace' || CHR(9) || CHR(9) || CHR(9) || 'Status' || CHR(9) || CHR(9) || 'Size (Mb)' || CHR(9) || 'Used (Mb)' || CHR(9) || 'Free (Mb)' from dual;">&3
echo "SELECT D.TABLESPACE_NAME,D.STATUS,TO_CHAR((A.BYTES/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR),TO_CHAR(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),$FORMAT_VAR) FROM DBA_TABLESPACES D,SYS.$TS_AVAIL A,SYS.$TS_FREE F WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME;">&3
echo "spool off">&3
#su - oracle -c "$ORACLE_HOME/bin/sqlplus "<< EOF
$ORACLE_HOME/bin/sqlplus << EOF
connect / as SYSDBA
set echo on
-- temporarily change the user dump dest so that
-- the text copy of the control file rebuilding sql commands
-- go into the /backup directory
ALTER SYSTEM SET USER_DUMP_DEST='/Volumes/backup';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER SYSTEM SET USER_DUMP_DEST='/Volumes/u01/udump';
-- get list of datafiles for shell script
@/Volumes/u01/server_scripts/prod1_closed_db_backup_files_get_1.sql
-- get list of datafiles for restore shell script
@/Volumes/u01/server_scripts/prod1_closed_db_restore_files_get_1.sql
-- output the disaster recovery info to /Volumes/backup/prod1_disaster_recovery.txt
@/Volumes/u01/server_scripts/prod1_disaster_recovery_get.sql
spool off
set echo on
set feedback on
set verify on
set pagesize 24
shutdown immediate;
exit;
EOF
# set ownership of .sql files to oracle account
chown $ORACLE_OWNER:$ORACLE_GROUP /Volumes/u01/server_scripts/prod1_closed_db_backup_files_get_1.sql
chown $ORACLE_OWNER:$ORACLE_GROUP /Volumes/u01/server_scripts/prod1_closed_db_restore_files_get_1.sql
# make the shell scripts executable
chmod +x /Volumes/u01/server_scripts/*.sh
# make backup copies of the backup scripts so that they get put onto tape
cp /Volumes/u01/server_scripts/prod1_closed_db_backup_files_get_1.sql /Volumes/backup
cp /Volumes/u01/server_scripts/prod1_closed_db_restore_files_get_1.sql /Volumes/backup
cp /Volumes/u01/server_scripts/prod1_closed_db_restore_files_1.sh /Volumes/backup
cp /Volumes/u01/server_scripts/prod1_closed_db_backup_files_1.sh /Volumes/backup
# make a backup copy of the init.ora file
cp $ORACLE_HOME/dbs/initprod1.ora /Volumes/backup/initprod1.ora
# make a backup copy of the orapwd file
cp $ORACLE_HOME/dbs/orapw /Volumes/backup/prod1_orapw
# insure that the spooled list of database files is executable
chmod +x /Volumes/u01/server_scripts/prod1_closed_db_backup_files_1.sh
chmod +x /Volumes/u01/server_scripts/prod1_closed_db_restore_files_1.sh
# copy the database files to the /backup directory
/Volumes/u01/server_scripts/prod1_closed_db_backup_files_1.sh
# set file ownership of the copied files to the
# oracle UNIX account (otherwise they will be owned by root)
chown $ORACLE_OWNER:$ORACLE_GROUP /Volumes/backup/*
# make a copy of alert.log file into /Volumes/backup directory
cp $ORA_BDUMP_DIR/alert_prod1.log /Volumes/backup
# Rotate the Oracle /u01/bdump/alertSID.log before the database starts
LOG=alert_$ORACLE_SID.log
cd $ORA_BDUMP_DIR
test -f $LOG.5 && mv $LOG.5 $LOG.6
test -f $LOG.4 && mv $LOG.4 $LOG.5
test -f $LOG.3 && mv $LOG.3 $LOG.4
test -f $LOG.2 && mv $LOG.2 $LOG.3
test -f $LOG.1 && mv $LOG.1 $LOG.2
test -f $LOG.0 && mv $LOG.0 $LOG.1
mv $LOG $LOG.0
# start up the database again once the files have been copied
# su - oracle -c "$ORACLE_HOME/bin/sqlplus /nolog"<< EOF
$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect / as SYSDBA
set echo on
startup;
exit;
EOF
