Tablespace point in time recovery

I'm glad it was just a test for this TSPITR....

RMAN> run

2> { recover tablespace USERS

3> until logseq 23 thread 1

4> auxiliary destination '/u04/oradata/';

5> }

Starting recover at 02-DEC-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=146 devtype=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/02/2009 11:41:25

RMAN-06455: point-in-time tablespace recovery requires Enterprise Edition

Ooops.... running SE.

Kill all processes - the rough way

On certain occasions, it might be needed to kill several hunderds of processes on a linux machine. Of course you should NOT do this on a production site, but the use of xargs might be handy:

ps -ef | grep oracle | grep -v bash | awk '{ print $2}' | xargs kill -9

With the grep -v you exclude your own putty session.

Sqlplus connect on HP-UX

I had a problem connecting with a script on HP-UX. Somehow it felt like I had seen it earlier....

sqlplus myuser/mypassword@mydb

The HP-UX does not like the @ sign. You'll need to escape it:

sqlplus myuser/mypassword\@mydb

This solved my problem.

Correct NLS_LANG for exports

It's important to set the right NLS_LANG environment variable when doing exports/imports.
The following query makes it easy getting the syntax and values right.

set heading off
set feedback off
select 'export NLS_LANG=' || lan.value || '_' || ter.value || '.' || chr.value
from v$nls_parameters lan,
v$nls_parameters ter,
v$nls_parameters chr
where lan.parameter='NLS_LANGUAGE'
and ter.parameter='NLS_TERRITORY'
and chr.parameter='NLS_CHARACTERSET';
set heading on
set feedback on

This gives the line (e.g.)
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

which can easily be copied before the imp/exp is run.
For windows environments, you would need to replace 'export' by 'set' in the query.

Enjoy!

determine the Oracle version in a nutshell

On a host with multiple Oracle versions in multiple Oracle homes, it might be prove hard to quickly determine the Oracle version in a shell script. I came across the following method and like to share it.

First, grep the line from /etc/oratab which contains the ORACLE_SID at the beginning of that line:

grep -i ^$ORACLE_SID: /etc/oratab

Then cut everything behind the semicolon away:

cut -f2 -d:

this gives you the Oracle home path.

Now cut out everything behind the first dot:

cut -f1 -d.

Taking the basename, will remove the path and leave the Oracle version.
In one statement it will become:

VERSION=$( basename $(grep -i ^$ORACLE_SID: /etc/oratab | cut -f2 -d: | cut -f1 -d. ) )

I know that there are different ways. Suggestions welcome!

Index montoring

You easily forget about all the options you have. I came across a situation, where 39 indexes where created on a single table. This extreme number was grown over the years. There was no administration on which indexes were needed for what purpose. Good luck!

Luckily, you have index monitoring that can be turned on.

alter index MY_IDX_1 monitoring usage;

If you wait for a considerable amount of time, you will see that v$object_usage will contain information on whether the index was used:

select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;

Note that it is important to log on as the index owner and not as sysdba to see any information in this view. The USED column will probably (maybe) switch from NO to YES if the index is being used.

If you turn off the monitoring:

alter index MY_IDX_1 nomonitoring usage;

the END_MONITORING date field will be filled.

You should not turn off monitoring too soon, there might be monthly reports or hardly used screens that incidentally need the index.

There is a drawback on this. During statististics the index will be used. This will blurr your results. A solution might be to lock statistics during monitoring, using

exec dbms_stats.lock_table_stats(ownname=>'SCOTT',tabname=>'DEPT');

but on heavily changing tables locking statistics might be no such a good idea.

hidden parameter checking

You might want to check the value of hidden parameters in Oracle.... Here's how to do this. You can enter a (part of) the parametername (in lowercase) to find what you're looking for.

set pages 8888
set lines 120
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%&Part_Of_Parametername%' escape '\'
order by
name
;

Always working on backups....

Today I tried to improve a backup. On a host with a large number of databases running, which do change now and then, you would like that your backup more or less manages itself. So it would have to pick up all running databases, and backup these.

The backup script is stored in a RMAN catalog. The script picks up all databases on a (linux) system and selects them on the running pmon process:

ps -ef | grep pmon | grep -v 'grep pmon' | awk '{print "backupdb " substr($8,10,8) ";"}'

With the awk command, you extract the instance name from the grep output. The result of the grep is put into a tmpwrk.lst file, and looks likes this:

backupdb TEST01;
backupdb PROD01;
backupdb PROD02;
etc.

The total script I use is the following:

#!/bin/sh
#
# Script for backup all databases on the server
#
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/usr/local/sbin:$PATH

export WRKDIR=/home/oracle/scripts
export LOGDIR=/backup/$(hostname)
export JOBLOG=$LOGDIR/backup_$(hostname).log
echo "Starting backup $(hostname) databases at $(date)" > $JOBLOG

backupdb()
{
if [ -z $1 ]
then
echo "No parameters passed to function."
return 0
else
export ORACLE_SID=$1
rman target / catalog rman/rmanpswd@RMANCAT CMDFILE=$WRKDIR/rman_backup.rcv LOG=$LOGDIR/$ORACLE_SID.backup.log;
FOUTEN=$(grep 'ERROR MESSAGE' $LOGDIR/$ORACLE_SID.backup.log | wc -l);
if [ $FOUTEN -ne 0 ]
then
cat LOG=$LOGDIR/$ORACLE_SID.backup.log | nail -s "BACKUP FAILED $ORACLE_SID" mailme@myisp.com;
echo "$(date) : backup $ORACLE_SID FAILED!!!" >> $JOBLOG
else
echo "$(date) : backup $ORACLE_SID completed" >> $JOBLOG
fi
fi
}

#-----------------------------------------
# MAIN
#-----------------------------------------

#--
# Make list of all active databases
# and create tmpbck.lst file
#---------
ps -ef | grep pmon | grep -v 'grep pmon' | awk '{print "backupdb " substr($8,10,8) ";"}' > $WRKDIR/tmpbck.lst

#--
# run database cold backup for this list
#---------
source $WRKDIR/tmpbck.lst

echo "Backup $(hostname) finished at $(date)" >> $JOBLOG

exit