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)

Oracle 11g – R1 11.1.0.1 Gridcontrol installation fails with “Configuration assistant “OMS Configuration” Failed”

I tried to install gridcontrol 11.1.0.1 on a weblogic server 10.3.2 and got this in the middle of my installation process (centos 5 + 11gR2 11.2.0.2).

On “OMS Configuration” i got the status “Failed” and in my logs i found this message about securing oms:

Sep 21, 2010 2:28:42 PM oracle.sysman.omsca.adapter.wls.OMSWLSAdapter adapterCleanup
FINE: Got the Host URL as: null
Sep 21, 2010 2:28:42 PM oracle.sysman.omsca.framework.OMSGenericAdapter postDeployAndReposSetup
SEVERE: OMSCA-ERR:Securing of OMS failed. Check the trace file:/opt/apps/oracle/middleware/oms11g/cfgtoollogs/omsca/omsca_20100921142512.log
Sep 21, 2010 2:28:42 PM oracle.sysman.omsca.framework.OMSCAFreshInstall execute
SEVERE: Securing of OMS failed.
Sep 21, 2010 2:28:42 PM oracle.sysman.omsca.framework.OMSConfigAssistantDriver main
FINE: Got resultfalse

After some research i found out that i am using the wrong weblogic server version (10.3.3. instead 10.3.2).

Found this on metalink: [ID 1096766.1]
10.3.3 is being referenced instead of 10.3.2, as required.
Only the 10.3.2 version of WebLogic is supported for 11.1.0.1 Grid Control.

So, back to start, do it again … but now with the proper version 🙂

I have installed oracle 11.2.0.2, weblogic 10.3.2, gridcontrol 11.1.0.1 in about 2 hours and the installation was quite easy yet.

Lessons learned – it’s not always the best using fresh (aka the latest release) of software 🙂

Oracle 11g – R2 11.2.0.2 – dbca fails with ORA-01031: insufficient privileges

I had a strange problem regarding a hash (#) in a password.

Created a new database 11.2.0.2 under linux (centos5) as software only installation, no asm, only filesystem as storage for my gridcontrol server.

Then started dbca (as many times before with 11.2.0.1 and 10.2.0.4) – checked some values and select as many times before a password for sys/system with an hash (#) at the end.

No problems with the oracle checks (only that is no a oracle standard pw and so one), clicked to next, next …

Then at the end (finish) i got an error ‘ORA-01031: insufficient privileges’ … checked all logs, find nothing special. Try the installation again, checked some user rights, group names … all that stuff what normally worked without problems.

Same error again, i could explode … 🙂

And then, as the last possible thing what i can do, i changed the password to an string without the hash … and viola – it works.

That really sucks, all previous installations had no problem with an hash in the password only 11.2.0.2 make now troubles.

For me the worst thing – if oracle make an password check at installation time, they should also say ‘sorry, no hash please’ and not blame you with this stupid ‘ORA-01031: insufficient privileges’ error at the last step of the installation without further debug information in their logs.

Lessons learned – qa and testing is still a problem in every software company 🙂