00 20 * * 6 /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh -t L0 -i PMCRCS20 -d SBT
00 8,20,23 * * 1-5 /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh -t AO -i PMCRCS20 -d SBT
00 8,12,16 * * 6 /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh -t L1 -i PMCRCS20 -d SBT
pbpmccracora5:/opt/oracle/DBATOOLS/sql/general>>cat /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh
#!/bin/ksh
# Modified on 1-23-2007 MT/TT de-linked catalog for main backup job
# Modified on 2-6-2008 MT/TT added ASM size differences, NLS date format
# MOfified on 2-9-2008 SS added SBT portion
# MOfified on 9-30-2010 AA added resync after backup
# RMAN plus backup for Oracle Database
# ---------------------------------------------------------------
Usage="\nUsage: rman_backup.ksh -t BACKUP_TYPE -i ORA_DBNAME {-d device_type} \n \
\n \
parameters :- \n \
\n \
\t -t backup_type\t\t--- Backup Type L0 (lev 0) or L1 (lev 1)or AO (Archive Only) \n \
\t -i ora_dbname\t\t--- DB_NAME \n\
\t -d device_type\t\t--- SBT"
# Process the options passed on the command line
backup_type=""
ora_dbname=""
while getopts ":t:i:d:" opt; do
case ${opt} in
t ) backup_type=${OPTARG};;
i ) ora_dbname=${OPTARG};;
d ) backup_device=${OPTARG};;
\? ) echo "`date '+%Y/%m/%d %H:%M:%S'` rmanbackup: illegal option \n"
echo "${Usage} \n"
return 1;;
* ) echo "${Usage} \n"
return 1;;
esac
done
valid_sw=0
# ----------------------------------------------
# echo "SID= ${ora_dbname}"
if [ "${ora_dbname}" = "" ]; then
echo "${Usage} \n"
exit 1
else
echo "** ORACLE_DBNAME is set to ${ora_dbname} "
fi
# ----------------------------------------------
# ----Used profile setup only in $HOME/.setup_${ora_dbname}.ksh
# ----------------------------------------------
if [ -f $HOME/.setup_${ora_dbname}.ksh ]; then
echo "** .setup_${ora_dbname}.ksh file is found at $HOME dir .."
. $HOME/.setup_${ora_dbname}.ksh
else
echo "Error: .setup_${ora_dbname}.ksh file is not at $HOME .. exiting .. \n"
exit 1
fi
# ----------------------------------------------
if [ "${backup_type}" = "L0" -o "${backup_type}" = "L1" -o "${backup_type}" = "AO" ]; then
echo "** Backup Type is set to $backup_type"
else
if [ "$NB_ORA_FULL" = "1" ]; then
backup_type="L0"
echo "** Backup Type is set to $backup_type through NB_ORA_FULL"
else
if [ "$NB_ORA_INCR" = "1" -o "$NB_ORA_CINC" = "1" ]; then
backup_type="L1"
echo "** Backup Type is set to $backup_type through NB_ORA_INCR"
else
echo "** Backup Type must be L0 or L1 or AO .. exiting .. \n"
exit 1
fi
fi
fi
# ----------------------------------------------
# Now set the varibles ..
O_SID=$ORACLE_SID
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# ====================================================================
# ** 1. Now Set up the RMAN RCV file (config and command file)
# ====================================================================
if [ "${backup_type}" = "L0" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev0_sbt.rcv"
BKP_TYPE="Level 0 SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev0_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev0.rcv"
BKP_TYPE="Level 0 backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev0.log
fi
else
if [ "${backup_type}" = "L1" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev1_sbt.rcv"
BKP_TYPE="Level 1 SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev1_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev1.rcv"
BKP_TYPE="Level 1 backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev1.log
fi
else
if [ "${backup_type}" = "AO" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_arch_sbt.rcv"
BKP_TYPE="Archive Only SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_arch_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_arch.rcv"
BKP_TYPE="Archive Only backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_arch.log
fi
else
msg1="Error: BackupType must be L0 or L1 or AO \n"
valid_sw=1
fi
fi
fi
# --------- Check for the availability of the RCV file .. -----------------
if [ -f $RCV_FILE ]; then
echo "**RMAN config. ${RCV_FILE} file is found at local dir .."
else
msg1="Error: RMAN Config RCV File ${RCV_FILE} is missing at local .. exiting .. \n"
valid_sw=1
fi
# --------- Check for get_asm_info.sql .. -----------------
if [ -f ${ORA_RMAN}/get_asm_info.sql ]; then
echo "${ORA_RMAN}/get_asm_info.sql file is found .."
else
msg1="Error: ${ORA_RMAN}/get_asm_info.sql file is missing .. exiting .. \n"
valid_sw=1
fi
# --------- Check for get_asm_info.sql .. -----------------
if [ -f ${ORA_RMAN}/get_dbinfo.sql ]; then
echo "${ORA_RMAN}/get_dbinfo.sql file is found .."
else
msg1="Error: ${ORA_RMAN}/get_dbinfo.sql file is missing .. exiting .. \n"
valid_sw=1
fi
# --------------------------------------------------------------------------
echo "Valid Switch value = $valid_sw ; Non-Zero is error .."
# -------------------------------------------------------------------------
rmanrun=`ps -ef | grep "rman target" |grep -v grep | grep ${ora_dbname} |wc -l`
rmanstr=`ps -ef | grep "rman target" |grep -v grep | grep ${ora_dbname}`
echo $rmanrun
if [ $rmanrun -gt 0 ]; then
echo "--------------------------------------------- " >> ${ORA_LOG}/${RMANLOG}
echo "Another session of Rman is already running" >> ${ORA_LOG}/${RMANLOG}
echo "Unable to continue backup, at `date` " >> ${ORA_LOG}/${RMANLOG}
echo "Exitng the job ...." >> ${ORA_LOG}/${RMANLOG}
echo "--------------------------------------------- " >> ${ORA_LOG}/${RMANLOG}
echo "Currently Running RMAN Job : " >> ${ORA_LOG}/${RMANLOG}
echo "$rmanstr" >> ${ORA_LOG}/${RMANLOG}
# below is withdrawn on 10/16/2007 by MT/TT
#mail -s "RMAN+ Backup is already running on : `uname -n` for $O_SID" $MAILLIST < /dev/null
exit 1
else
echo "Run RMAN backup on server \n"
fi
#===============================================================
# ** 3. Now run the RMAN backup ..
# ====================================================================
export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
if [ "$CUSER" = "root" ]
then
if [ -f /opt/CA/eac/bin/sesu ]
then
/opt/CA/eac/bin/sesu - oracle -c "$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE"
else
/bin/su - oracle -c "$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE"
fi
exit_code=$?
else
$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE
exit_code=$?
fi
echo "----------------------------------------------------------------\n" >> ${ORA_LOG}/${RMANLOG}
export TN=`date '+%b-%d %H:%M'`
if [ $exit_code -eq 0 ]; then
$ORACLE_HOME/bin/rman target / catalog $RMANCAT log /opt/oracle/DBATOOLS/rman/resync.log cmdfile=/opt/oracle/DBATOOLS/rman/resync.rcv
Message4="RMAN Backup $BKP_TYPE at $TN Done.. Success "
else
Message4="RMAN Backup $BKP_TYPE at $TN Error Failed* "
fi
exit 0
pbpmccracora5:/opt/oracle/DBATOOLS/sql/general>>cd $ORA_LOCAL
pbpmccracora5:/opt/oracle/DBATOOLS/local>>ls -lrt *.rcv
-rw-r--r-- 1 oracle oinstall 870 Sep 16 2010 PMCRCS20_lev1.rcv
-rw-r--r-- 1 oracle oinstall 868 Sep 16 2010 PMCRCS20_lev0.rcv
-rw-r----- 1 oracle oinstall 2106 Sep 17 2010 PMCRCS20_lev1_sbt.rcv
-rwxrwxrwx 1 oracle oinstall 806 Sep 24 2010 r.rcv
-rw-r--r-- 1 oracle oinstall 2767 Sep 26 2010 PMCRCS20_lev0_sbt.rcv
-rw-r----- 1 oracle oinstall 1021 Oct 29 2010 PMCRCS20_arch_sbt.rcv
pbpmccracora5:/opt/oracle/DBATOOLS/local>>cat PMCRCS20_lev0.rcv
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY 1 ;
CONFIGURE BACKUP OPTIMIZATION ON ;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 8G ;
CONFIGURE CONTROLFILE AUTOBACKUP ON ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$RMANOUT/%d_AUTOCF_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET ;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 ;
SHOW ALL;
REPORT SCHEMA ;
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
backup incremental level 0
database format '$RMANOUT/%d_%s_%t_%c_DB0' tag="DB_LEV0"
plus
archivelog format '$RMANOUT/%d_%s_%t_%c_ARCH0' tag "ARCH_LEV0" ;
backup current controlfile format '$RMANOUT/%d_%s_%t_CONTROL' tag "CNTRLFL" ;
backup spfile format '$RMANOUT/%d_%s_%t_SPFILE' tag "SPFILE" ;
}
pbpmccracora5:/opt/oracle/DBATOOLS/local>>
pbpmccracora5:/opt/oracle/DBATOOLS/local>>
pbpmccracora5:/opt/oracle/DBATOOLS/local>>cat PMCRCS20_lev0_sbt.rcv
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch06 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch07 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
CONFIGURE RETENTION POLICY TO REDUNDANCY 1 ;
CONFIGURE BACKUP OPTIMIZATION ON ;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 75G ;
CONFIGURE CONTROLFILE AUTOBACKUP ON ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
sql 'alter system archive log current';
backup incremental level 0
DATABASE
FORMAT '%d_%s_%T_%c_DB0'
TAG "DB_LEV0"
PLUS ARCHIVELOG DELETE INPUT
FILESPERSET 20
FORMAT '%d_%s_%T_%c_ARCH0'
TAG "ARCH_LEV0"
;
sql 'alter system archive log current';
sql 'alter system archive log current';
BACKUP
ARCHIVELOG ALL DELETE INPUT
FILESPERSET 20
FORMAT '%d_%s_%T_%c_ARCH0'
TAG "ARCH_LEV0"
;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
RELEASE CHANNEL ch06;
RELEASE CHANNEL ch07;
#
# Note: During the process of backing up the database, RMAN also backs up the
# control file. This version of the control file does not contain the
# information about the current backup because "nocatalog" has been specified.
# To include the information about the current backup, the control file should
# be backed up as the last step of the RMAN section. This step would not be
# necessary if we were using a recovery catalog.
#
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
BACKUP
# recommended format
FORMAT '%d_%s_%t_CONTROL'
TAG "CNTRLFL"
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
pbccrlracora1:/home/oracle/DBATOOLS/local>>cd $ORA_RMAN
pbccrlracora1:/home/oracle/DBATOOLS/rman>>ls -lrt get_asm_info.sql
-rw-r--r-- 1 oracle oinstall 2921 May 21 2006 get_asm_info.sql
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cat get_asm_info.sql
spool /tmp/ASM_metadata.info
rem create pfile='tmp/ASM_pfile.ora' from spfile ;
set linesize 120
col host_name for a16
col instance_name for a10 head 'Instance|Name'
PROMPT "**** ASM Insatance Details ****"
select * from v$active_instances
/
select INSTANCE_NAME, HOST_NAME, VERSION,
to_char(STARTUP_TIME, 'YY-MON-DD HH24:MI') Start_Time
from v$instance
/
SET PAGESIZE 100
SET LINESIZE 140
COL full_path FORMAT a80
COL dir FORMAT a3
COL sys FORMAT a3
col bytes for 99,999,999 head 'Size_KBs'
col type for a14
col filenum for 99999
PROMPT "**** Files in the ASM control at this time ****"
SELECT fiLenum, full_path, dir, sys, round(BYTES/1024,0) Bytes, TYPE from v$asm_file a,
( SELECT FILE_INCARNATION, fiLenum, full_path, dir, sys FROM
( SELECT FILE_INCARNATION, filenum, CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT a.FILE_INCARNATION, a.file_number filenum , g.name gname, a.parent_index pindex,
a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE full_path LIKE UPPER('%')
) b
where filenum = file_number and FILE_INCARNATION = incarnation
order by type , full_path
/
col name for a20 head 'ASM_DISK|Name'
col g_name for a12 head 'ASM_Group|Name'
col path for a20 head 'Disk|Path'
col DISK_NUMBER head 'DISK|NUM#' for 99999
col GROUP_NUMBER head 'Grp|NUM' for 999
col HEADER_STATUS head 'HEADER|STATUS'
col MOUNT_STATUS head 'MOUNT|STATUS' for a8
col total_mb for 999,999
col free_mb for 999,999
col ALLOC for 99999 head 'Alloc|Unit_KB'
col SECTOR_SIZE head 'Sector|Size'
col Block_Size head 'Block|Size'
col offline_disks head 'Offline|Disks'
set linesize 140
PROMPT ***** LIST DISKS Under the control of ASM ****
select DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, STATE, PATH, NAME,
REDUNDANCY, TOTAL_MB, FREE_MB
from v$asm_disk order by Path
/
PROMPT ***** LIST DISKS with Disk Groups under ASM control ****
select A.NAME G_NAME, A.GROUP_NUMBER, b.DISK_NUMBER,
b.MOUNT_STATUS, b.HEADER_STATUS,
b.PATH, b.NAME, b.TOTAL_MB, b.FREE_MB,
round(b.FREE_MB/b.TOTAL_MB * 100,1) PCT_FREE
from v$asm_disk B, v$asm_diskgroup A
where A.GROUP_NUMBER = B.GROUP_NUMBER order by A.NAME
/
PROMPT ***** Disk Group Details under ASM control ****
select NAME, TYPE, TOTAL_MB, FREE_MB, round(FREE_MB/TOTAL_MB * 100,1) PCT_FREE,
BLOCK_SIZE, SECTOR_SIZe, OFFLINE_DISKS, ALLOCATION_UNIT_SIZE/1024 Alloc,
STATE from v$asm_diskgroup order by name
/
set linesize 132
set pagesize 120
col name for a40 head 'Parameter|Name'
col DISPLAY_VALUE for a60
PROMPT "**** ASM Instance Parameter List ****"
select name,
DISPLAY_VALUE ,
ISDEFAULT ,
ISSES_MODIFIABLE
from v$parameter
where DISPLAY_VALUE is not null order by name
/
spool off
exit
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cd $ORA_RMAN
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cat get_dbinfo.sql
spool /tmp/db_info.txt
set echo on
SELECT 'Now Time : ' || to_char(sysdate, 'YYYY-MON-DD HH24:MI') Now_Time from dual;
col FIRST_TIME for a16 head 'First|Time'
col COMPL_TIME for a16 head 'Completed|Time'
col FIRST_CHANGE# head 'FIRST|CHANGE#'
col NEXT_CHANGE# head 'NEXT|CHANGE#'
col THREAD# for 9999 head 'THRD|No.'
set linesize 100
PROMPT ****** List Last Archived Log Details ******
SELECT a.recid, a.THREAD#, a.SEQUENCE#,
to_char(a.FIRST_TIME, 'YY-MON-DD HH24:MI') First_Time,
to_char(a.COMPLETION_TIME, 'YY-MON-DD HH24:MI') COMPL_Time,
a.ARCHIVED, a.STATUS , FIRST_CHANGE# , NEXT_CHANGE#
from v$archived_log a,
(select thread#, max(SEQUENCE#) SEQ from v$archived_log group by thread# ) b
where a.SEQUENCE# = b.SEQ and a.thread# = b.thread# ;
col instance for a12
col CURRENT_GROUP# head 'Current|Group'
col CKPT_TIME for a16 head 'CHECKPOINT|Time'
col groups for 9999
col CHECKPOINT_CHANGE# head 'CHECKPOINT|CHANGE#'
PROMPT ****** List Thread Details ******
SELECT THREAD#, STATUS, INSTANCE, GROUPS, CURRENT_GROUP#,
SEQUENCE#, CHECKPOINT_CHANGE# ,
to_char(CHECKPOINT_TIME, 'YY-MON-DD HH24:MI') CKPT_TIME
from v$thread ;
prompt "****** Controlfile backup completed.... "
alter database backup controlfile to trace as '/tmp/CF_TRACE.trc' ;
prompt "****** PFILE creation as pfile_from_spfile.ora .... "
create pfile='/tmp/pfile_from_spfile.ora' from spfile ;
spool off
exit
00 8,20,23 * * 1-5 /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh -t AO -i PMCRCS20 -d SBT
00 8,12,16 * * 6 /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh -t L1 -i PMCRCS20 -d SBT
pbpmccracora5:/opt/oracle/DBATOOLS/sql/general>>cat /opt/oracle/DBATOOLS/rman/ofr_rman_backup.ksh
#!/bin/ksh
# Modified on 1-23-2007 MT/TT de-linked catalog for main backup job
# Modified on 2-6-2008 MT/TT added ASM size differences, NLS date format
# MOfified on 2-9-2008 SS added SBT portion
# MOfified on 9-30-2010 AA added resync after backup
# RMAN plus backup for Oracle Database
# ---------------------------------------------------------------
Usage="\nUsage: rman_backup.ksh -t BACKUP_TYPE -i ORA_DBNAME {-d device_type} \n \
\n \
parameters :- \n \
\n \
\t -t backup_type\t\t--- Backup Type L0 (lev 0) or L1 (lev 1)or AO (Archive Only) \n \
\t -i ora_dbname\t\t--- DB_NAME \n\
\t -d device_type\t\t--- SBT"
# Process the options passed on the command line
backup_type=""
ora_dbname=""
while getopts ":t:i:d:" opt; do
case ${opt} in
t ) backup_type=${OPTARG};;
i ) ora_dbname=${OPTARG};;
d ) backup_device=${OPTARG};;
\? ) echo "`date '+%Y/%m/%d %H:%M:%S'` rmanbackup: illegal option \n"
echo "${Usage} \n"
return 1;;
* ) echo "${Usage} \n"
return 1;;
esac
done
valid_sw=0
# ----------------------------------------------
# echo "SID= ${ora_dbname}"
if [ "${ora_dbname}" = "" ]; then
echo "${Usage} \n"
exit 1
else
echo "** ORACLE_DBNAME is set to ${ora_dbname} "
fi
# ----------------------------------------------
# ----Used profile setup only in $HOME/.setup_${ora_dbname}.ksh
# ----------------------------------------------
if [ -f $HOME/.setup_${ora_dbname}.ksh ]; then
echo "** .setup_${ora_dbname}.ksh file is found at $HOME dir .."
. $HOME/.setup_${ora_dbname}.ksh
else
echo "Error: .setup_${ora_dbname}.ksh file is not at $HOME .. exiting .. \n"
exit 1
fi
# ----------------------------------------------
if [ "${backup_type}" = "L0" -o "${backup_type}" = "L1" -o "${backup_type}" = "AO" ]; then
echo "** Backup Type is set to $backup_type"
else
if [ "$NB_ORA_FULL" = "1" ]; then
backup_type="L0"
echo "** Backup Type is set to $backup_type through NB_ORA_FULL"
else
if [ "$NB_ORA_INCR" = "1" -o "$NB_ORA_CINC" = "1" ]; then
backup_type="L1"
echo "** Backup Type is set to $backup_type through NB_ORA_INCR"
else
echo "** Backup Type must be L0 or L1 or AO .. exiting .. \n"
exit 1
fi
fi
fi
# ----------------------------------------------
# Now set the varibles ..
O_SID=$ORACLE_SID
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# ====================================================================
# ** 1. Now Set up the RMAN RCV file (config and command file)
# ====================================================================
if [ "${backup_type}" = "L0" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev0_sbt.rcv"
BKP_TYPE="Level 0 SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev0_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev0.rcv"
BKP_TYPE="Level 0 backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev0.log
fi
else
if [ "${backup_type}" = "L1" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev1_sbt.rcv"
BKP_TYPE="Level 1 SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev1_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_lev1.rcv"
BKP_TYPE="Level 1 backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_lev1.log
fi
else
if [ "${backup_type}" = "AO" ]; then
if [ "${backup_device}" = "SBT" ]; then
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_arch_sbt.rcv"
BKP_TYPE="Archive Only SBT backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_arch_sbt.log
else
RCV_FILE="${ORA_LOCAL}/${ora_dbname}_arch.rcv"
BKP_TYPE="Archive Only backup"
RMANLOG=${TD}_${ORACLE_SID}_rman_arch.log
fi
else
msg1="Error: BackupType must be L0 or L1 or AO \n"
valid_sw=1
fi
fi
fi
# --------- Check for the availability of the RCV file .. -----------------
if [ -f $RCV_FILE ]; then
echo "**RMAN config. ${RCV_FILE} file is found at local dir .."
else
msg1="Error: RMAN Config RCV File ${RCV_FILE} is missing at local .. exiting .. \n"
valid_sw=1
fi
# --------- Check for get_asm_info.sql .. -----------------
if [ -f ${ORA_RMAN}/get_asm_info.sql ]; then
echo "${ORA_RMAN}/get_asm_info.sql file is found .."
else
msg1="Error: ${ORA_RMAN}/get_asm_info.sql file is missing .. exiting .. \n"
valid_sw=1
fi
# --------- Check for get_asm_info.sql .. -----------------
if [ -f ${ORA_RMAN}/get_dbinfo.sql ]; then
echo "${ORA_RMAN}/get_dbinfo.sql file is found .."
else
msg1="Error: ${ORA_RMAN}/get_dbinfo.sql file is missing .. exiting .. \n"
valid_sw=1
fi
# --------------------------------------------------------------------------
echo "Valid Switch value = $valid_sw ; Non-Zero is error .."
# -------------------------------------------------------------------------
rmanrun=`ps -ef | grep "rman target" |grep -v grep | grep ${ora_dbname} |wc -l`
rmanstr=`ps -ef | grep "rman target" |grep -v grep | grep ${ora_dbname}`
echo $rmanrun
if [ $rmanrun -gt 0 ]; then
echo "--------------------------------------------- " >> ${ORA_LOG}/${RMANLOG}
echo "Another session of Rman is already running" >> ${ORA_LOG}/${RMANLOG}
echo "Unable to continue backup, at `date` " >> ${ORA_LOG}/${RMANLOG}
echo "Exitng the job ...." >> ${ORA_LOG}/${RMANLOG}
echo "--------------------------------------------- " >> ${ORA_LOG}/${RMANLOG}
echo "Currently Running RMAN Job : " >> ${ORA_LOG}/${RMANLOG}
echo "$rmanstr" >> ${ORA_LOG}/${RMANLOG}
# below is withdrawn on 10/16/2007 by MT/TT
#mail -s "RMAN+ Backup is already running on : `uname -n` for $O_SID" $MAILLIST < /dev/null
exit 1
else
echo "Run RMAN backup on server \n"
fi
#===============================================================
# ** 3. Now run the RMAN backup ..
# ====================================================================
export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
if [ "$CUSER" = "root" ]
then
if [ -f /opt/CA/eac/bin/sesu ]
then
/opt/CA/eac/bin/sesu - oracle -c "$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE"
else
/bin/su - oracle -c "$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE"
fi
exit_code=$?
else
$ORACLE_HOME/bin/rman target / log ${ORA_LOG}/${RMANLOG} cmdfile=$RCV_FILE
exit_code=$?
fi
echo "----------------------------------------------------------------\n" >> ${ORA_LOG}/${RMANLOG}
export TN=`date '+%b-%d %H:%M'`
if [ $exit_code -eq 0 ]; then
$ORACLE_HOME/bin/rman target / catalog $RMANCAT log /opt/oracle/DBATOOLS/rman/resync.log cmdfile=/opt/oracle/DBATOOLS/rman/resync.rcv
Message4="RMAN Backup $BKP_TYPE at $TN Done.. Success "
else
Message4="RMAN Backup $BKP_TYPE at $TN Error Failed* "
fi
exit 0
pbpmccracora5:/opt/oracle/DBATOOLS/sql/general>>cd $ORA_LOCAL
pbpmccracora5:/opt/oracle/DBATOOLS/local>>ls -lrt *.rcv
-rw-r--r-- 1 oracle oinstall 870 Sep 16 2010 PMCRCS20_lev1.rcv
-rw-r--r-- 1 oracle oinstall 868 Sep 16 2010 PMCRCS20_lev0.rcv
-rw-r----- 1 oracle oinstall 2106 Sep 17 2010 PMCRCS20_lev1_sbt.rcv
-rwxrwxrwx 1 oracle oinstall 806 Sep 24 2010 r.rcv
-rw-r--r-- 1 oracle oinstall 2767 Sep 26 2010 PMCRCS20_lev0_sbt.rcv
-rw-r----- 1 oracle oinstall 1021 Oct 29 2010 PMCRCS20_arch_sbt.rcv
pbpmccracora5:/opt/oracle/DBATOOLS/local>>cat PMCRCS20_lev0.rcv
run
{
CONFIGURE RETENTION POLICY TO REDUNDANCY 1 ;
CONFIGURE BACKUP OPTIMIZATION ON ;
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 8G ;
CONFIGURE CONTROLFILE AUTOBACKUP ON ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$RMANOUT/%d_AUTOCF_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET ;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 ;
SHOW ALL;
REPORT SCHEMA ;
allocate channel c1 device type disk ;
allocate channel c2 device type disk ;
backup incremental level 0
database format '$RMANOUT/%d_%s_%t_%c_DB0' tag="DB_LEV0"
plus
archivelog format '$RMANOUT/%d_%s_%t_%c_ARCH0' tag "ARCH_LEV0" ;
backup current controlfile format '$RMANOUT/%d_%s_%t_CONTROL' tag "CNTRLFL" ;
backup spfile format '$RMANOUT/%d_%s_%t_SPFILE' tag "SPFILE" ;
}
pbpmccracora5:/opt/oracle/DBATOOLS/local>>
pbpmccracora5:/opt/oracle/DBATOOLS/local>>
pbpmccracora5:/opt/oracle/DBATOOLS/local>>cat PMCRCS20_lev0_sbt.rcv
RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch04 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch05 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch06 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
ALLOCATE CHANNEL ch07 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
CONFIGURE RETENTION POLICY TO REDUNDANCY 1 ;
CONFIGURE BACKUP OPTIMIZATION ON ;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 75G ;
CONFIGURE CONTROLFILE AUTOBACKUP ON ;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
sql 'alter system archive log current';
backup incremental level 0
DATABASE
FORMAT '%d_%s_%T_%c_DB0'
TAG "DB_LEV0"
PLUS ARCHIVELOG DELETE INPUT
FILESPERSET 20
FORMAT '%d_%s_%T_%c_ARCH0'
TAG "ARCH_LEV0"
;
sql 'alter system archive log current';
sql 'alter system archive log current';
BACKUP
ARCHIVELOG ALL DELETE INPUT
FILESPERSET 20
FORMAT '%d_%s_%T_%c_ARCH0'
TAG "ARCH_LEV0"
;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
RELEASE CHANNEL ch04;
RELEASE CHANNEL ch05;
RELEASE CHANNEL ch06;
RELEASE CHANNEL ch07;
#
# Note: During the process of backing up the database, RMAN also backs up the
# control file. This version of the control file does not contain the
# information about the current backup because "nocatalog" has been specified.
# To include the information about the current backup, the control file should
# be backed up as the last step of the RMAN section. This step would not be
# necessary if we were using a recovery catalog.
#
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_POLICY=PRD04_PBPMCCRACORA_9999SC0,NB_ORA_CLIENT=pbpmccracora5,NB_ORA_SCHED=APPL_365_3)';
BACKUP
# recommended format
FORMAT '%d_%s_%t_CONTROL'
TAG "CNTRLFL"
CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
pbccrlracora1:/home/oracle/DBATOOLS/local>>cd $ORA_RMAN
pbccrlracora1:/home/oracle/DBATOOLS/rman>>ls -lrt get_asm_info.sql
-rw-r--r-- 1 oracle oinstall 2921 May 21 2006 get_asm_info.sql
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cat get_asm_info.sql
spool /tmp/ASM_metadata.info
rem create pfile='tmp/ASM_pfile.ora' from spfile ;
set linesize 120
col host_name for a16
col instance_name for a10 head 'Instance|Name'
PROMPT "**** ASM Insatance Details ****"
select * from v$active_instances
/
select INSTANCE_NAME, HOST_NAME, VERSION,
to_char(STARTUP_TIME, 'YY-MON-DD HH24:MI') Start_Time
from v$instance
/
SET PAGESIZE 100
SET LINESIZE 140
COL full_path FORMAT a80
COL dir FORMAT a3
COL sys FORMAT a3
col bytes for 99,999,999 head 'Size_KBs'
col type for a14
col filenum for 99999
PROMPT "**** Files in the ASM control at this time ****"
SELECT fiLenum, full_path, dir, sys, round(BYTES/1024,0) Bytes, TYPE from v$asm_file a,
( SELECT FILE_INCARNATION, fiLenum, full_path, dir, sys FROM
( SELECT FILE_INCARNATION, filenum, CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,
dir, sys FROM
(SELECT a.FILE_INCARNATION, a.file_number filenum , g.name gname, a.parent_index pindex,
a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY dir,
a.SYSTEM_CREATED sys
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
ORDER BY dir desc, full_path asc)
WHERE full_path LIKE UPPER('%')
) b
where filenum = file_number and FILE_INCARNATION = incarnation
order by type , full_path
/
col name for a20 head 'ASM_DISK|Name'
col g_name for a12 head 'ASM_Group|Name'
col path for a20 head 'Disk|Path'
col DISK_NUMBER head 'DISK|NUM#' for 99999
col GROUP_NUMBER head 'Grp|NUM' for 999
col HEADER_STATUS head 'HEADER|STATUS'
col MOUNT_STATUS head 'MOUNT|STATUS' for a8
col total_mb for 999,999
col free_mb for 999,999
col ALLOC for 99999 head 'Alloc|Unit_KB'
col SECTOR_SIZE head 'Sector|Size'
col Block_Size head 'Block|Size'
col offline_disks head 'Offline|Disks'
set linesize 140
PROMPT ***** LIST DISKS Under the control of ASM ****
select DISK_NUMBER, MOUNT_STATUS, HEADER_STATUS, STATE, PATH, NAME,
REDUNDANCY, TOTAL_MB, FREE_MB
from v$asm_disk order by Path
/
PROMPT ***** LIST DISKS with Disk Groups under ASM control ****
select A.NAME G_NAME, A.GROUP_NUMBER, b.DISK_NUMBER,
b.MOUNT_STATUS, b.HEADER_STATUS,
b.PATH, b.NAME, b.TOTAL_MB, b.FREE_MB,
round(b.FREE_MB/b.TOTAL_MB * 100,1) PCT_FREE
from v$asm_disk B, v$asm_diskgroup A
where A.GROUP_NUMBER = B.GROUP_NUMBER order by A.NAME
/
PROMPT ***** Disk Group Details under ASM control ****
select NAME, TYPE, TOTAL_MB, FREE_MB, round(FREE_MB/TOTAL_MB * 100,1) PCT_FREE,
BLOCK_SIZE, SECTOR_SIZe, OFFLINE_DISKS, ALLOCATION_UNIT_SIZE/1024 Alloc,
STATE from v$asm_diskgroup order by name
/
set linesize 132
set pagesize 120
col name for a40 head 'Parameter|Name'
col DISPLAY_VALUE for a60
PROMPT "**** ASM Instance Parameter List ****"
select name,
DISPLAY_VALUE ,
ISDEFAULT ,
ISSES_MODIFIABLE
from v$parameter
where DISPLAY_VALUE is not null order by name
/
spool off
exit
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cd $ORA_RMAN
pbccrlracora1:/home/oracle/DBATOOLS/rman>>cat get_dbinfo.sql
spool /tmp/db_info.txt
set echo on
SELECT 'Now Time : ' || to_char(sysdate, 'YYYY-MON-DD HH24:MI') Now_Time from dual;
col FIRST_TIME for a16 head 'First|Time'
col COMPL_TIME for a16 head 'Completed|Time'
col FIRST_CHANGE# head 'FIRST|CHANGE#'
col NEXT_CHANGE# head 'NEXT|CHANGE#'
col THREAD# for 9999 head 'THRD|No.'
set linesize 100
PROMPT ****** List Last Archived Log Details ******
SELECT a.recid, a.THREAD#, a.SEQUENCE#,
to_char(a.FIRST_TIME, 'YY-MON-DD HH24:MI') First_Time,
to_char(a.COMPLETION_TIME, 'YY-MON-DD HH24:MI') COMPL_Time,
a.ARCHIVED, a.STATUS , FIRST_CHANGE# , NEXT_CHANGE#
from v$archived_log a,
(select thread#, max(SEQUENCE#) SEQ from v$archived_log group by thread# ) b
where a.SEQUENCE# = b.SEQ and a.thread# = b.thread# ;
col instance for a12
col CURRENT_GROUP# head 'Current|Group'
col CKPT_TIME for a16 head 'CHECKPOINT|Time'
col groups for 9999
col CHECKPOINT_CHANGE# head 'CHECKPOINT|CHANGE#'
PROMPT ****** List Thread Details ******
SELECT THREAD#, STATUS, INSTANCE, GROUPS, CURRENT_GROUP#,
SEQUENCE#, CHECKPOINT_CHANGE# ,
to_char(CHECKPOINT_TIME, 'YY-MON-DD HH24:MI') CKPT_TIME
from v$thread ;
prompt "****** Controlfile backup completed.... "
alter database backup controlfile to trace as '/tmp/CF_TRACE.trc' ;
prompt "****** PFILE creation as pfile_from_spfile.ora .... "
create pfile='/tmp/pfile_from_spfile.ora' from spfile ;
spool off
exit
No comments:
Post a Comment