Job to delete log and trace files from OEM

I use this script to clean up all the log and trace file from each host..  You can schedule a job in OEM to go and execute this in all hosts you want.

$cat /etc/oratab

ESRTSP:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
ESRTSS:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
RESTST:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
LISTENER_10g:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
AGENT_10g:/oracle/app/EPW/agent/v10203_ee_suse10:N

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

###############################################
###############################################
##  ADD ALL INSTANCES TO THE FOR LOOK LIST   ##
###############################################
###############################################

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do

find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

done

Script to delete arch logs from the Standby database host after applied

#!/usr/bin/ksh

##################################################################################################################
#
# This script is to delete the arch logs for the standby database after it has applied the logs to the instance.
#
##################################################################################################################

script=`basename $0`

export ORACLE_SID=$1
dir=/oracle/EPW/${ORACLE_SID}/temp/arch

tmpf=$dir/.$script.tmp

logcount=50

function GetAppliedLogfileSequenceNumber
{
sqlplus -S /nolog <<EOF > $tmpf
connect / as sysdba
set head off
set pages 0
select max(sequence#) from v\$archived_log where applied = ‘YES’;
select resetlogs_id from v\$database_incarnation where status = ‘CURRENT’;
exit
EOF
return
}

if [ -d $dir ]
then
cd $dir
GetAppliedLogfileSequenceNumber

if [ -s $tmpf ]
then
count=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘1p’;`
db_incarnation=`cat $tmpf | awk ‘{print $1}’ | sed -n ‘3p’;`

if [ ${#count} -ne 0 ]
then
let count=$count-$logcount

if ((count <= 0))
then
echo “$script: log count is set to (non)zero no log(s) to remove”
exit 0
fi
else
exit 0
fi
else
echo “$script: no archive log(s) to remove”
exit 0
fi

while [ -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc ]
do
rm -f ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
#ls ${ORACLE_SID}_${count}_1_${db_incarnation}.arc
rcode=$?

if ((rcode != 0))
then
echo “$script: cannot remove: ${ORACLE_SID}_${count}_1_${db_incarnation}.arc”
exit 1
else
let count=$count-1
fi
done

rm -f $tmpf

else
print “$script: $dir no such file or directory”
exit 1
fi
exit 0

How to find and fix block corruption using RMAN?

One of the scenario we ran into when one of the data file reported there was block corruption as RMAN reported the following message in its logs. Note: This scenario was done on 9.2.0.1 running Linux.

RMAN-03009: failure of backup command on ch1 channel at 07/02/2009 04:27:06
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/oradata/TESTDB/TEST_data_01.dbf

And the alert.log (alert_TESTDB.log) also had the following message by RMAN
Reread of blocknum=443343, file=/u01/oradata/TESTDB/TEST_data_01.dbf. found same corrupt data
***
Corrupt block relative dba: 0×0346c3cf (file 13, block 443343)
Bad check value found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0×0346c3cf
last change scn: 0×0000.32a8f165 seq: 0×1 flg: 0×04
consistency value in tail: 0xf1650601
check value in block header: 0xeb4f, computed block checksum: 0xec16
spare1: 0×0, spare2: 0×0, spare3: 0×0

So to double check one can ran dbverify (dbv) or RMAN to validate the datafile which confirmed data corruption and the datafile# and the block#.
$ dbv blocksize=8192 file=/u01/oradata/TESTDB/TEST_data_01.dbf > /tmp/TEST_data_01_file.log 2>&1

DBVERIFY – Verification starting : FILE = /u01/oradata/TESTDB/TEST_data_01.dbf
Page 443343 is marked corrupt
***
Corrupt block relative dba: 0×0346c3cf (file 13, block 443343)
Bad check value found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0×0346c3cf
last change scn: 0×0000.32a8f165 seq: 0×1 flg: 0×04
consistency value in tail: 0xf1650601
check value in block header: 0xeb4f, computed block checksum: 0xec16
spare1: 0×0, spare2: 0×0, spare3: 0×0
***

DBVERIFY – Verification complete

Total Pages Examined : 486400
Total Pages Processed (Data) : 473439
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 10
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12950
Total Pages Marked Corrupt : 1
Total Pages Influx : 0

And using RMAN one can validate the same thing by checking the view v$database_block_corruption after running the script below.

RMAN> connect target /

RMAN> backup validate check logical database;
…

It reported the same block as show below:

SQL> SELECT * FROM v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
13 443343 1 0 FRACTURED

After it was confirmed there was only one datafile with one block corruption using block recover we were able to recover the datafile without shutting down the database. The channel allocated was the same that was specified when backing up the data file.

connect target /
connect catalog rmancatalog/rmancatalog@catalog
run {
allocate channel…;
blockrecover datafile 13 block 443343;
release channel …;
}

Log file:
Starting blockrecover at 02-JUL-09

channel ch1: restoring block(s)
channel ch1: specifying block(s) to restore from backup set
restoring blocks of datafile 00013
channel ch1: restored block(s) from backup piece 1
piece handle=g0kirlhe_1_1 tag=TAG20090630T040048 params=NULL
channel ch1: block restore complete

starting media recovery

archive log thread 1 sequence 30644 is already on disk as file /u01/oradata/TESTDB/arch/1_30644.dbf
archive log thread 1 sequence 30645 is already on disk as file /u01/oradata/TESTDB/arch/1_30645.dbf
channel ch1: starting archive log restore to default destination
channel ch1: restoring archive log
archive log thread=1 sequence=30643
channel ch1: restored backup piece 1
piece handle=hlkj27vr_1_1 tag=TAG20090701T160827 params=NULL
channel ch1: restore complete
media recovery complete
Finished blockrecover at 02-JUL-09
released channel: ch1

Recovery Manager complete.

And after the block was restored we validated the datafile through RMAN and v$database_block_corruption reported no records found.

RMAN> connect target /
RMAN> backup validate check logical datafile 13;

Starting backup at 02-JUL-09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=22 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/u01/oradata/TESTDB/TEST_data_01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:35
Finished backup at 02-JUL-09

sys@TESTDB> select * FROM v$database_block_corruption;

no rows selected

How to find success/failure status of a command in different shells?

Ksh/Borne/Bash shell
$ ls /usr/bin
….
# when success value is 0
$ echo $?
0
$ ls /usr/bin/blah
ls: 0653-341 The file /usr/bin/blah does not exist
# when failure non-zero status
$ echo $?
1

c/Tcsh
$ ls /usr/bin
….
# when success value is 0
$ echo $status
0
$ ls /usr/bin/blah
ls: 0653-341 The file /usr/bin/blah does not exist
# when failure non-zero status
$ echo $status
1

Clean up script with oratab

A simple script to delete trace files and arc logs from remote location, based on oratab instances.

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

########################################
#  Copy TNS Names file to local disk   #
########################################
/bin/ksh /work/dba/monora/scripts/update_local_tns.ksh > /tmp/update_local_tns.log 2>&1
#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:'|egrep -v  ‘LISTENER|AGENT’`
do
find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

## To delete ach logs from remote online location for dev/test/training env that are in arch log mode
find /oracle/EPW/arch/${SID} -name ‘${SID}*.arc’ -mtime +3 -exec rm {} \;
done

How to change logical name of SQL Server database?

To change the logical name of the SQL Server databse one can follow the steps below to change the logical name of database data file and log file. You may want to take a backup of the database before applying the change to ensure you have a rollback.

Example:
Use test
select fileid, name, filename from sysfiles
fileid name filename
1 test C:\MSSQL\Data\test.mdf
2 test_Log C:\MSSQL\Data\test_log.LDF

– rename data file
ALTER DATABASE Monitor MODIFY FILE (NAME = test, NEWNAME = App_Data)
GO
– rename log file
ALTER DATABASE Monitor MODIFY FILE (NAME = test_Log, NEWNAME = App_Log)
GO

– verify change
select fileid, name, filename from sysfiles
fileid name filename
1 App_Data C:\MSSQL\Data\test.mdf
2 App_Log C:\MSSQL\Data\test_log.LDF

Set title of a window

Using the script one can set the title of a window, one of the reason for doing would be distinguish a window from another. The following example was tried on AIX.

$ cat settitle.ksh
TITLEBAR=”33]0;${1} 07″
echo $TITLEBAR

# in this example the title of the window will change to “Hi”
$ ./settitle.ksh Hi

How to get list of tables in SQLServer?

Using the SQL below one can list SQL Server tables

Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

table_name
———–
ExceptionLog
Employee
…

How to view table of contents of a compress tar file?

Using the example below one can view the contents a compress tar file without un-compressing the file.
# if the file is compressed using compress
$ uncompress -c file.Z | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

$ uncompress < file.tar.Z | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

# if the file is compressed using gzip
$ cat file.tar.gz | gunzip | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

How to find blocking session?

In 10g, there is a column in V$session called blocking_session to find the session id that is blocking.

Session 1) sid # 145

SQL> select userenv(’sid’) from dual;

USERENV(’SID’)
————–
145

SQL> create table t ( x number );

Table created.

SQL> create unique index t_idx on t(x);

Index created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

Session #2) sid – 159

SQL> select userenv(’sid’) from dual;

USERENV(’SID’)
————–
159

– hung as 145 is blocking as i haven’t committed as the table t has primary key
SQL> insert into t values ( 1 );

Session 1) to find the blocking_session

– shows both session are active, the second session# 159 is blocked by 145

SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);

SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE