Installgen
Features and Benefits
Installgen
Demo Available for download...
Bookmark This Page
#!/bin/bash
# script: prod1_rman_backup_job_1.sh (renamed from 29_macosx_920_rman_backup_job_1.sh)
# Features: This shell script performs an RMAN full database backup of all
# datafiles, archivelogs, control file, init.ora and the orapw file.
# The prod1_disaster_recovery.txt file is also created every time a backup is
# performed for disaster recovery purposes.
# This RMAN backup procedure uses the controlfile for storage
# of information about backup sets.
#
# Output Files: /Volumes/backup/prod1_disaster_recovery.txt
#
# Script Sequence#: 29
# Used By: CRON job via oracle UNIX account
# Usage:
# ******** RMAN Oracle database full backup at 4:03 ********
# 03 4 * * 0-6 /Volumes/u01/server_scripts/prod1_rman_backup_job_1.sh
# Copyright 2002 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 10-10-2001 dsimpson Initial Release
# 05-11-2002 dsimpson Added separate backup of controlfile after
# completion of full backup, changed maxpiecesize
# from 10G to 1900M to prevent any possible 32bit issues.
# Added NOEXCLUDE to insure that Read-Only tablespaces
# always get backed up.
# Updated to use oracle UNIX account.
# Fixed controlfile backup location to /Volumes/backup
# Added snapshot controlfile backup path
# to prevent file from being written into
# ORACLE_HOME/dbs.
# 09-30-2002 dsimpson Added "TO directoryname" clause to restore controlfile
# validate command for Oracle 9.2.0.
# Removed "delete obsolete" command for backup sets.
# 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
# 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
# start the rman backup
#su - oracle -c "$ORACLE_HOME/bin/rman 'target /'"<< EOF
$ORACLE_HOME/bin/rman target / << EOF
configure retention policy to redundancy 3;
configure retention policy to recovery window of 3 days;
configure backup optimization on;
configure controlfile autobackup off;
configure default device type to Disk;
configure device type Disk parallelism 1;
configure datafile backup copies for device type Disk to 1;
configure channel device type Disk format '/Volumes/backup/%d_datafile_%s_%p.bak';
configure maxsetsize to unlimited;
configure snapshot controlfile name to '/Volumes/backup/prod1_snapcf.f';
set controlfile autobackup format for device type Disk to '/Volumes/backup/prod1_controlfile_%F';
show all;
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup full database noexclude
include current controlfile
format '/Volumes/backup/%d_datafile_%s_%p.bak'
tag 'prod1_datafile_daily';
}
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup archivelog all
delete all input
format '/Volumes/backup/%d_archivelog_%s_%p.bak'
tag 'prod1_archivelog_daily';
}
run {
allocate channel ch1 type Disk maxpiecesize = 1900M;
backup format '/Volumes/backup/%d_controlfile_%s.bak' current controlfile;
}
crosscheck backup;
restore controlfile to '/Volumes/backup' validate;
restore tablespace SYSTEM validate;
restore archivelog all validate;
list backup of database;
report unrecoverable;
report schema;
report need backup;
report obsolete;
delete noprompt expired backup of database;
delete noprompt expired backup of controlfile;
quit
EOF
# 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
# check for corrupt blocks found during RMAN backup
echo "check for corrupt blocks found during RMAN backup"
# only use the next line if this script needs run via root CRON job
#su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/sqlplus /nolog"<< EOF
$ORACLE_HOME/bin/sqlplus /nolog<< EOF
connect / as SYSDBA
set echo on
set verify on
set feedback on
-- check for corruption found by RMAN
select * from V\$BACKUP_CORRUPTION;
-- temporarily change the user dump dest so that
-- the text copy of the control file rebuilding sql commands
-- go into the /u01/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';
-- output the disaster recovery info to /Volumes/backup/prod1_disaster_recovery.txt
@/Volumes/u01/server_scripts/prod1_disaster_recovery_get.sql
exit;
# ---------------------- Troubleshooting ---------------
# to solve RMAN 6089 error - archivelog file not found, after file deleted
# after being logged into RMAN
# CHANGE ARCHIVELOG ALL VALIDATE;
