Delete archivelogs files without using RMAN

asmcmd
++++++
(asmcmd) rm file_name

If you use a wildcard (*) – rm deletes all matches except non-empty directories (unless the -r flag is used).

sqlplus
+++++
(sqlplus) alter diskgroup drop file

Drawback:
The asmcmd and sqlplus command will not update the database views (V$ARCHIVED_LOG, V$FLASH_RECOVERY_AREA_USAGE), controlfile, recovery catalog that the files have been removed.

To update the database views, control file or RMAN catalog you need to run following commands:
(rman) crosscheck archivelog all;
(rman) delete expired archivelog all;

Alternatively you can also set a ‘new’ db_recovery_file_dest if your disk or diskgroup is out of space to have some time to fix your problems:
(sqlplus) alter system set db_recovery_file_dest = ‘/database/disk1/recovery_file_1’ SCOPE=BOTH;
(sqlplus) alter system set db_recovery_file_dest_size = 50G SCOPE=BOTH;

RMAN delete information about old backups from the catalog without a tape device

Delete the old entries in the backup catalog with touch the tapes containing the old backups, so we need a ‘dummy channel’ for the tape:

— check how many backups we have
list backup summary;

— create a dummy channel for tape
allocate channel for maintenance device type sbt
parms ‘SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)’;

— delete entries in catalog from tape backups
report obsolete;
delete obsolete;

RMAN Configuration Examples

RMAN Setup

create tablespace cat_ts
datafile ‘/opt/apps/database/oradata/emrep/cat_ts.dbf’
size 100M
autoexetend on next 100M
maxsize unlimited
segment space management auto
extent management local autoallocate; create user rman identified by blabla
temporary tablespace temp
default tablespace cat_ts
quota unlimited on cat_ts
;

RMAN Configure

(sqlplus / as sysdba) GRANT RECOVERY_CATALOG_OWNER TO rman;
rman TARGET /
(rman) CONNECT CATALOG rman/blabla
(rman) CREATE CATALOG;
rman TARGET / CATALOG rman/blabla
(rman) REGISTER DATABASE;
(rman) REPORT SCHEMA;

RMAN Setup on the Nodes

(edit tnsnames.ora for emrep resolving)
rman target / catalog rman/blabla@emrep
(rman) REGISTER DATABASE;
(rman) REPORT SCHEMA;

DB Setup on Nodes

GENERAL:
Steps when the database state is down:
1. Login to sqlplus
-sqlplus /nolog
-connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
-startup mount;
3. Check the log mode status of the database
-select log_mode from v$database;
4. Setting it to noarchivelog mode
-alter database noarchivelog;
5. Setting db open for user operation
-alter database open;
6. Reverting back to archivelog mode
-alter database archivelog; In our case:
1. srvctl stop database -d rac1
2. srvctl start instance -d rac1 -i rac11 -o mount
3. connect / as sysdba
4. alter database archivelog;
5. alter database flashback on;
6. select log_mode from v$database;
7. srvctl stop database -d rac1
8. srvctl start database -d rac1
Alternative to point 8 (on each node):1. /opt/apps/grid/product/11.2.0/grid/bin/crsctl stop crs
Alternative to point 8 (on each node):2. /opt/apps/grid/product/11.2.0/grid/bin/crsctl start crs

TEST the backup

rman target / catalog rman/blabla@emrep

show all;
RMAN configuration parameters for database with db_unique_name RAC1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/opt/apps/database/product/11.2.0/db1/dbs/snapcf_rac11.f’; # default

backup database;

USE FULL backup scripts
rman_archive_backup.sh
+++++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to backup archived redo log files
#
####################################################################
#
# Check arguments
#
if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

#
# Check if rman is still running; If not create the lockfile
#
if [ -f /home/oracle/scripts/var/lock/rman.lock ]
then
echo ” rman is still running, because the lockfile exists in /home/oracle/scripts/var/lock/ ”
exit 2
else
touch /home/oracle/scripts/var/lock/rman.lock
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/archivebackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/archivebackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.ArchBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.ArchBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup tag “${ORACLE_SID}_AR” ## filesperset 8
archivelog all
format ‘%d_%T_%U_arch’
not backed up 2 times;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”
rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
## report obsolete;
## delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

#
# After successfull backup job, remove the lockfile
#
if [ -f /home/oracle/scripts/var/lock/rman.lock ]
then
rm -f /home/oracle/scripts/var/lock/rman.lock
fi

rman_full_backup.sh
++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to full backup the database – incremental level 0
#
####################################################################
#
# Check arguments
#

echo on

if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/fullbackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/fullbackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.FullBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.FullBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup force tag “${ORACLE_SID}_0T” incremental level 0 check logical ## filesperset 8
database force format ‘%d_%T_%U_0T’
plus archivelog format ‘%d_%T_%U_0Tarch’;
sql “alter database backup controlfile to trace”;
sql “alter database backup controlfile to trace as ”/home/oracle/$SID/cf.bak” reuse”;
sql “create pfile=”/home/oracle/$SID/pfile-ora10g-$SID.ora” from spfile”;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
report obsolete;
delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

cat rman_incr_backup.sh
+++++++++++++++++++

#! /bin/ksh
#
####################################################################
#
# THE INPUT PARAMETERS ARE :
#
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
#
# Script to backup cumulative the database and also the archive files
# blocks changed since level 0 – level 1 cumulative
#
######################################################################
#
# Check arguments
#

echo on

if [[ $# -ne 4 ]]
then
echo “$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB”
exit 1
fi

###################
# RMAN variables #
###################

export SID=$1
export RMAN_OWNER=$2
export RMAN_PWD=$3
export RMAN_DB=$4
export LOGIN_PATH=$PATH
export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/scripts/etc/ora11$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/incrbackup${SID}.`date +%d%m%y%H%M%S`.log
export HBdellog=/home/oracle/$SID/rmanlogs/incrbackup${SID}.`date +%d%m%y%H%M%S`.del.log
export emailsubfail=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.IncrBackup.Failure
export emailsubok=$HOSTNAME.$ORACLE_SID.`date +%d%m%y%H%M%S`.IncrBackup.OK.[info]
export mailreciever=”hosting@foo.com”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBlog} \
<
show all;
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
backup force tag “${ORACLE_SID}_1D” incremental level 1 cumulative check logical ## filesperset 8
database force format ‘%d_%T_%U_1D’
plus archivelog format ‘%d_%T_%U_1Darch’;
sql “alter database backup controlfile to trace”;
sql “alter database backup controlfile to trace as ”/home/oracle/$SID/cf.bak” reuse”;
sql “create pfile=”/home/oracle/$SID/pfile-ora10g-$SID.ora” from spfile”;
resync catalog;
release channel ‘t_1’;
}
Exit

SQL

oraerror=`cat $HBlog | grep “ORA-“`
rmanerror=`cat $HBlog | grep “RMAN-“`

echo $oraerror
echo $rmanerror
if [ -z “$oraerror” -a -z “$rmanerror” ]
then
echo “No Errors”

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log ${HBdellog} \
<
run
{
allocate channel ‘t_1’ type ‘sbt_tape’;
set limit channel ‘t_1’ maxopenfiles 4;
crosscheck archivelog all;
delete noprompt archivelog all backed up 2 times to device type ‘sbt_tape’ completed before ‘sysdate – 2’;
resync catalog;
release channel ‘t_1’;
}
## report obsolete;
## delete noprompt obsolete;
## list backup;
exit

DELSQL

oradelerror=`cat $HBdellog | grep “ORA-“` | grep -v -e “ORA-19921:”
rmandelerror=`cat $HBdellog | grep “RMAN-“` | grep -v -e “RMAN-06900:” -e “RMAN-06901:”

echo $oradelerror
echo $rmandelerror
if [ -z “$oradelerror” -a -z “$rmandelerror” ]
then
echo “No Errors”
mailx -s $emailsubok $mailreciever
## cat $HBlog $HBdellog |mailx -s $emailsubok $mailreciever
else
echo “Errors Found !!!!”
cat $HBlog $HBdellog |mailx -s $emailsubfail $mailreciever
fi
else
echo “Errors Found !!!!”
cat $HBlog |mailx -s $emailsubfail $mailreciever
fi

crontab -l -u root
++++++++++++

##
#
# M H DoM MoY DoW Command
#
####
# THE INPUT PARAMETERS ARE :
##
# $1 – Target DB
# $2 – RMAN owner
# $3 – RMAN password
# $4 – RMAN DB
##

00 6-23 * * * su – oracle -c “/home/oracle/scripts/rman_archive_backup.sh rac11 rman blabla emrep >/dev/null 2>&1”
15 00 * * * su – oracle -c “/home/oracle/scripts/rman_full_backup.sh rac11 rman blabla emrep >/dev/null 2>&1”

##
# set the variables for the rman log dir
##

RMAN_LOGDIR_1=/home/oracle/rac11/rmanlogs

##
# delete the rman logs older than 31 days
##

30 20 * * * find ${RMAN_LOGDIR_1} -name “*.log” -mtime +31 -exec rm -f \{\} \; > /dev/null 2>&1

ora11rac11.sh
++++++++++

##
## settings for using shellscripts over crontab
##

SHELL=/bin/sh
ORACLE_SID=rac11
ORACLE_HOME=/opt/apps/database/product/11.2.0/db1
PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/oracle/bin

export SHELL PATH ORACLE_SID ORACLE_HOME

DB settings for TSM
******************

sqlnet.ora
++++++++++

## Timeout Parameters
##
## Specially for tsm connection aka long running but idle backups via rman/tivoli
## and the catalog database located at an other server (eg gridcontrol)
##
SQLNET.INBOUND_CONNECT_TIMEOUT = 10 # Timeout in seconds (60 = default)
SQLNET.SEND_TIMEOUT = 5 # Timeout in seconds (none = default)
SQLNET.RECV_TIMEOUT = 5 # Timeout in seconds (none = default)
SQLNET.EXPIRE_TIME = 90 # Idle time in minutes (none = default)